ELT with Azure Synapse Analytics

Overview

Extract-Load-Transform (ELT) is a technique, in which the transformation step is moved to the end of the workflow, and data is immediately loaded to a destination upon extraction. 

Etlworks supports executing complex ELT scripts directly in Azure Synapse Analytics which greatly improves the performance and reliability of the data ingestion.

ELT using Before and After SQL

Step 1. Create a Flow to load data into the staging table(s) in Azure Synapse Analytics.

If the staging table does not exist, the Flow will automatically create it.

Step 2. When configuring a transformation, use fields Before COPY SQL  and After COPY SQL to execute complex SQL scripts in Azure Synapse Analytics.

You can execute multiple DML statements by separating them with ;.

Step 3. Schedule Flow to be executed periodically.

ELT using SQL Flow

As an alternative to Before and After SQL, you can use SQL Flow as explained below.

Create SQL Flow to update the dimensions from the staging tables.

You can execute multiple DML statements by separating them with ;.

Combine load Flow and SQL Flow into the single nested Flow.