ELT with Vertica

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

ELT using Before and After SQL

Here are the steps for executing ELT using Before and After SQL:

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

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 the Vertica database.

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 SQL Flow to update the dimensions from the staging tables.

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

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