ELT with Google BigQuery

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 Google BigQuery, which greatly improves the performance and reliability of the data ingestion.

ELT using Before and After SQL

Read the steps below to learn about ELT using Before and After SQL:

Step 1. Create a Flow to load data into the staging table(s) in Google BigQuery.

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

Step 2. When configuring a transformation, use fields Before LOAD SQL  and After LOAD SQL to execute complex SQL scripts in BigQuery.

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:

Step 1. Create an SQL Flow to update the dimensions from the staging tables.

Execute multiple DML statements by separating them with ;.

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