Articles in this section

See more

Automatically create or alter a destination table

  • Updated
Follow

Overview

ETL flows can automatically create, alter, or match the structure of the destination table when the flow runs. This article covers every option.

Target Table Creation & Matching

The Target Table Creation & Matching dropdown defines what the system does with the destination table when the flow runs. The parameter lives at:

Transformation > Mapping > Parameters > Handling schema changes.

Option Behavior
Auto-Create & Match Target Table Creates the table if it doesn't exist, adjusts column data types, and excludes source columns that don't exist in the destination. With Alter Target Table enabled, missing columns are added instead of excluded.
Auto-Create Target Table, Fail on Mismatch Creates the table if it doesn't exist. If it exists, the flow fails when the source has extra columns the destination doesn't have (including in the mapping). Extra destination columns are allowed.
Match Existing Target Table, Do Not Create Uses the existing table and adjusts column data types but does not create the table if it's missing. Extra destination columns are allowed.
Use Existing Target Table Without Modifications Uses the existing table as-is. The structure must match the source (including the mapping) or the flow may fail.
Auto-Create & Strict Schema Match, Fail on Mismatch Creates the table if it doesn't exist. If it exists, the flow fails unless source and destination have identical column names and column counts. Order and types don't matter; missing columns or name mismatches do.
Strict Schema Match, Fail on Mismatch, Do Not Create Same strict match rules as above, but fails immediately if the table doesn't exist.
True (Legacy) Equivalent to Auto-Create & Match Target Table. Kept for backward compatibility.
False (Legacy) Disables automatic creation and matching. Kept for backward compatibility.

Create the table

When the selected mode allows table creation, Etlworks generates a CREATE TABLE statement based on the source fields. Enabled by default for new transformations.

Ignore errors on table creation

When multiple threads might try to create the same table at the same time, enable Ignore Errors on Table Creation. Errors like “table already exists” are ignored so the flow continues.

Create indexes when creating the table

If the source is a database and the source table has indexes, enable Create Indexes. Etlworks replicates the indexes on the destination.

Keep the order of columns in mapping when creating the table

By default, when the flow creates a table from a mapping, it preserves the column order from the source and appends new columns to the end — ignoring the order set in the mapping.

For example, given this mapping:

the generated CREATE TABLE looks like this (source order, new column at the end):

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

And the Test transformation output:

To use the mapping order instead, enable Keep order of columns in mapping when creating table.

The generated CREATE TABLE now reflects the mapping order:

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

And the Test transformation output:

Create a temporary table

Enable Create temporary table to create the destination as a temp table. Common use case: loading into a staging table before applying SQL transformations to populate the real table.

Alter the table

When Alter target table if the source has columns that the target table doesn't have is enabled (and the selected mode supports altering), Etlworks generates an ALTER TABLE statement for every source column missing from the destination. Disabled by default.

The parameter lives at Transformation > Mapping > Parameters > Handling schema changes.

Programmatically change the auto-generated CREATE and ALTER table SQL

Etlworks translates source data types and column names into destination data types and column names automatically, and applies overrides defined in the mapping.

In rare cases the auto-generated SQL needs to be tweaked. See how to override the auto-generated CREATE and ALTER TABLE SQL and how to override data types globally for a connection.

Log CREATE and ALTER table DDL

To log the CREATE and ALTER TABLE statements the flow generates and executes, enable Log DDL SQL statements.

Related articles