Automatically create or alter a destination table

Overview

ETL flows can automatically create, modify, or match the structure of destination tables based on the selected configuration.

Target Table Creation & Matching

The Target Table Creation & Matching dropdown defines how the system handles the destination table when the flow runs. You can find this parameter under:

Transformation > Mapping > Parameters > Handling schema changes.

Available Options:

• Auto-Create & Match Target Table – Creates the table if it does not exist, adjusts column data types, and excludes extra columns in the source that do not exist in the destination. If ‘Alter Target Table’ is enabled, missing columns are added instead of excluded.
• Auto-Create Target Table, Fail on Mismatch – Creates the table if it does not exist. If it exists, the flow fails if the source has extra columns that do not exist in the destination (including mapping). Extra columns in the destination are allowed.
• Match Existing Target Table, Do Not Create – Uses the existing table and adjusts column data types but does not create a new table if it does not exist. Extra columns in the destination are allowed.
• Use Existing Target Table Without Modifications – Uses the existing table as-is. The table structure must match the source (including mapping); otherwise, the flow may fail.
• Auto-Create & Strict Schema Match, Fail on Mismatch - Creates the table if it does not exist. If it exists, the flow fails if the source does not match the destination exactly in terms of column names and number of columns. The order and data types do not matter, but if the source has fewer columns than the destination or mismatched column names, the flow fails.
• Strict Schema Match, Fail on Mismatch, Do not create - Fails if the table does not exist. If it exists, the flow fails if the source does not match the destination exactly in terms of column names and number of columns. The order and data types do not matter, but if the source has fewer columns than the destination or mismatched column names, the flow fails.
• True (Legacy – Auto-Create & Match Target Table) – Equivalent to Auto-Create & Match Target Table (for backward compatibility).
• False (Legacy – No Auto-Creation or Matching) – Disables automatic table creation and column matching (for backward compatibility).

Create Table

If the selected Target Table Creation & Matching option allows table creation, Etlworks will automatically generate a CREATE TABLE SQL statement based on the fields in the source.

This behavior is enabled by default for new transformations.

Ignoring Errors on Table Creation

In scenarios where multiple threads might attempt to create the same table simultaneously, enabling the Ignore Errors on Table Creation option ensures that any errors encountered during table creation (such as “table already exists”) are ignored. This allows the flow to continue without interruption, even if the table creation fails due to concurrent operations.

Create Indexes when creating table

If the source is a database and the source table has indexes, you can enable the Create Indexes parameter. If enabled, Etlworks will replicate indexes from the source to the destination.

Keep order of columns in mapping when creating table

By default, when a flow creates a table and mapping is defined, the system maintains the original order of columns in the source and adds new columns at the end, ignoring the order in the mapping.

For example, given the following mapping:

the generated CREATE TABLE SQL will look like below (same order as in the source, new column added to the end):

create table patient_test2 (id  INTEGER ,
   firstName  TEXT ,
   lastName  TEXT ,
   email  TEXT ,
   birthday  TEXT ,
   new_column  TEXT 
)

And the Test transformation will generate the output like below.

In most cases it is good enough (order of columns typically doesn't matter in SQL) but if for any reason you want to keep the order of columns as defined in mapping just enable the Keep order of columns in mapping when creating table.

When it is enabled (using the example above) the generated CREATE TABLE SQL will look like this:

create table patient_test2 (id  INTEGER ,
   new_column  TEXT ,
   lastName  TEXT ,
   firstName  TEXT ,
   email  TEXT ,
   birthday  TEXT 
)

And the Test transformation will generate the output like this:

Create temporary table

If you need to create a temporary table, enable Create temporary table.

A common use case for this option is loading data into a staging table before applying SQL transformations to populate the actual table.

Alter table 

If  Alter target table if the source has columns that the target table doesn't have is enabled the selected Target Table Creation & Matching option supports modifying an existing table, the system will automatically create and execute ALTER TABLE SQL statement for each column that exists in the source and does not exist in the destination.

This behavior is disabled by default.

You can find this parameter under Transformation > Mapping > Parameters > Handling schema changes.

Programmatically change  auto-generated CREATE and ALTER table SQL

In all flows where the destination is a database Etlworks automatically generates an SQL for creating a new table if it does not exist and [optionally] altering an existing table if the source dataset has changed. 

It automatically translates data types and column names in the source to the corresponding data types and column names in the destination. It also applies overrides defined in the mapping. 

In rare cases, the automatically generated SQL needs to be modified to correct some of the assumptions made by the ETL engine. Read how to override the auto-generated SQL.

Log CREATE and ALTER table DDL statement

If you want to log CREATE and ALTER TABLE DDL statements generated and executed by the flow enable option Log DDL SQL statements.