I am quite new to Matillion and am struggling to create a pipeline that can loop through a table to extract data in batches. Each batch should then uploaded from the S3 bucket to a SFTP folder as a separate csv file with a future date stamp.
Example: Extract 100 000 records in batches of 25 000. Each batch is exported in a separate csv file with a date stamp. The first file date is today's date, the second file is dated tomorrow, the third file is dated the day after tomorrow etc.
The pipe components are as follows:
Query Result To Scalar (to count records) > If (to check if any of them are valid) > Create Table (create a temp table for the specific batch) > SQL Script (insert the batch to the temp table and to set the flag on the extracted batch) > Query Result To Scalar (to determine the date stamp of the file) > S3 Unload (pull the data from the temp table into the csv file) > Data Transfer (push the file to the SFTP).
Currently, there is a pipeline for each csv file (more than 10 of them). This delivers the desired results but I believe there must be a much better way to accomplish this than the "work around" of 1 pipe per file.
Is there any way that I can run all 10 batches through just the one pipeline, such as extract the first batch and upload, extract the second batch and upload, until all files have been created?