Extract-Load-Transform (ELT) loads data into the target database first and runs the transformations directly inside that database. Etlworks supports two patterns for ELT: Before / After SQL on a regular ETL flow, and a dedicated SQL flow.
When should I use ELT instead of ETL?
Use ELT when the target database is fast enough to do the heavy work itself — typically a modern warehouse (Snowflake, Redshift, BigQuery, Synapse) or a well-resourced relational database. ELT keeps the transformation logic close to the data and avoids round-tripping rows through the Etlworks engine.
ELT using Before and After SQL
- Create a flow to migrate data from one database to another. If the staging table does not exist, the flow creates it.
- In the transformation, use the Before SQL and After SQL fields to run SQL in the target database before and after the load. Multiple DML statements separated by ; are supported.
- Optionally, configure the flow to ignore errors when running Before / After SQL.
- Schedule the flow to run periodically.
ELT using an SQL flow
As an alternative, build a dedicated SQL flow that runs after the load. Multiple DML statements separated by ; are supported.
Combine the load flow and the SQL flow into a single nested flow.