We use Alteryx to extract data from Salesforce and then share the data source in yxdb/tde format. The initial extraction was simple and brute: extract data fully everyday. Now the data volume is huge and the extraction is taking hours. It consumes bandwidth and especially the extraction may be timed out because Salesforce server may issue an error of something like "the query is taking too long time".
An incremental extraction becomes imperative. But it is not something straightforward to implement. With the help of Carlos Avila from Alteryx, I am able to put together a solution.
It consists of the following steps:
1.Extract the last timestamp (createdDate) from existing data file.
2.Send the timestamp to a macro and the macro will extract data after the timestamp.
3.Union the new data with the existing data.
The module ends up like this:
The existing data is in a .xydb file. The result is written to the same file after union. The Summarize tool extracts the last createdDate and feeds it to a filter to format the date. Then the formatted date is then fed to a macro. Note that it is a good practice to have a 'Block until Done' tool prior to write the results to the source file, because we are reading data from it.
Note that we need to select the control parameter that goes into the macro.
Then right click the macro and select open it for configurations.
The macro is as follows where we need to configure two tools.
1.Salesforce connector
The macro is as follows where we need to configure two tools.
1.Salesforce connector
Besides the credentials, we need to add a SOQL query:
- CreatedDate > LastCreatedDate
2.Update value
We only need to highlight the text string where a replacement will take place.
This reduces the extraction from hours to minutes!
.png)
Add a comment