SQL INSERT File Format

This file format contains an INSERT...VALUES statement to be executed on the destination during the load stage.

Additional data types are stored as follows:

• datetime and date are stored as ISO strings;
• decimal is stored as a text representation of a decimal number;
• binary storage depends on the format accepted by the destination;
• json storage also depends on the format accepted by the destination.

This file format is compressed by default.

Supported Destinations

This format is used by default by: DuckDB, Postgres, Redshift, Synapse, MSSQL, Motherduck

It is also supported by: Filesystem if you'd like to store INSERT VALUES statements for some reason

How to configure

There are several ways of configuring dlt to use insert_values file format for normalization step and to store your data at the destination:

1. You can set the loader_file_format argument to insert_values in the run command:

info = pipeline.run(some_source(), loader_file_format="insert_values")

2. You can set the loader_file_format in config.toml or secrets.toml:

[normalize]
loader_file_format="insert_values"

3. You can set the loader_file_format via ENV variable:

export NORMALIZE__LOADER_FILE_FORMAT="insert_values"

4. You can set the file type directly in the resource decorator.

@dlt.resource(file_format="insert_values")
def generate_rows(nr):
    pass