Extract-Load-Transform (ELT) moves the transformation step to the end of the pipeline: data is extracted and loaded into the destination first, then transformed there. Etlworks runs ELT scripts directly inside Amazon Redshift, which is faster and more reliable than transforming in-flight for most analytical workloads.
ELT using Before and After SQL
The simplest pattern. The load flow stages data into a table inside Amazon Redshift; Before/After SQL fires on the Amazon Redshift connection around the load.
- Create a load flow that lands data into a staging table in Amazon Redshift. If the staging table doesn't exist, the flow creates it automatically.
- When configuring the transformation, fill in Before COPY SQL and After COPY SQL with the SQL you want to run before or after the load. Separate multiple statements with ;.
- Schedule the flow to run periodically.
ELT using SQL Flow
Alternative pattern when you want the post-load SQL in its own flow rather than wired to a transformation.
- Create an SQL flow that runs the transformation SQL on the Amazon Redshift connection. Separate multiple statements with ;.
- Combine the load flow and the SQL flow into a single nested flow so they run together as one unit.