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 Azure Synapse Analytics or Microsoft Fabric Warehouse, 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 Azure Synapse Analytics or Microsoft Fabric Warehouse; Before/After SQL fires on the Azure Synapse Analytics or Microsoft Fabric Warehouse connection around the load.
- Create a load flow that lands data into a staging table in Azure Synapse Analytics or Microsoft Fabric Warehouse. 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 Azure Synapse Analytics or Microsoft Fabric Warehouse 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.