Exasol configurations

Incremental materialization strategies

In dbt-exasol, the following incremental materialization strategies are supported:

• append (default when unique_key is not defined)
• merge
• delete+insert (default when unique_key is defined)
• microbatch

All of these strategies are inherited from dbt-core. For more information on incremental strategies, refer to the incremental strategy documentation.

Performance optimizations

Table distribution and partitioning

Starting from dbt-exasol 1.8.1, you can configure table distribution and partitioning strategies to optimize query performance in Exasol. These configurations are available for models materialized as table or incremental.

Exasol supports the following performance optimization configurations:

Parameter	Type	Required	Description
partition_by_config	<string> or [<string>]	no	Partitions the table by specified column(s) for improved query performance
distribute_by_config	<string>	no	Distributes data across cluster nodes by specified column
primary_key_config	[<string>]	no	Defines primary key constraint(s)

Loading table...

Project YAML file

dbt_project.yml

models:
  <resource-path>:
    +materialized: table
    +partition_by_config: <column-name>
    +distribute_by_config: <column-name>
    +primary_key_config: [<column-name>]

Properties YAML file

models/properties.yml

models:
  - name: [<model-name>]
    config:
      materialized: table
      partition_by_config: <column-name>
      distribute_by_config: <column-name>
      primary_key_config: [<column-name>]

SQL file config

models/<model_name>.sql

{{ config(
    materialized="table",
    partition_by_config="<column-name>",
    distribute_by_config="<column-name>",
    primary_key_config=["<column-name>"]
) }}

Single column example

The following example creates a table partitioned by order_date, distributed by customer_id, with a primary key on customer_id:

models/orders.sql

{{
    config(
        materialized='table',
        primary_key_config=['customer_id'],
        partition_by_config='order_date',
        distribute_by_config='customer_id'
    )
}}

select
    customer_id,
    order_date,
    order_total,
    order_status
from {{ source('sales', 'orders') }}

Multiple columns example

When configuring multiple columns for primary_key_config, provide them as a list:

models/order_items.sql

{{
    config(
        materialized='incremental',
        primary_key_config=['order_id', 'item_id'],
        partition_by_config='order_date',
        distribute_by_config='order_id',
        unique_key=['order_id', 'item_id']
    )
}}

select
    order_id,
    item_id,
    order_date,
    quantity,
    price
from {{ source('sales', 'order_items') }}

info

When configuring multiple columns for primary_key_config, always provide them as a list: ['column1', 'column2']

For more information about Exasol's table distribution and partitioning, refer to the Exasol documentation.

Model contracts

Exasol supports model contracts with the following database constraints:

Constraint Type	Support Status	Description
not_null	✅ Enforced	Prevents NULL values in the column
primary_key	✅ Enforced	Enforces uniqueness and NOT NULL
foreign_key	✅ Enforced	References another table's primary key
check	❌ Not supported	Custom validation expressions not supported
unique	❌ Not supported	Unique constraints not supported

Loading table...

Example with enforced constraints

models/customers.yml

models:
  - name: customers
    config:
      contract:
        enforced: true
    columns:
      - name: customer_id
        data_type: integer
        constraints:
          - type: not_null
          - type: primary_key
      - name: email
        data_type: varchar(255)
        constraints:
          - type: not_null
      - name: country_id
        data_type: integer
        constraints:
          - type: foreign_key
            expression: countries (country_id)

For more information on model contracts, refer to the model contracts documentation.

Timestamp format

Starting from dbt-exasol 1.2.2, the default timestamp format is YYYY-MM-DDTHH:MI:SS.FF6.

You can customize the timestamp format in your profile configuration:

profiles.yml

outputs:
  dev:
    type: exasol
    timestamp_format: 'YYYY-MM-DD HH24:MI:SS.FF3'
    # ... other settings

Microbatch strategy considerations

When using the microbatch incremental strategy, Exasol requires timestamps without timezone suffix in model definitions:

-- ✅ Correct (Exasol compatible)
TIMESTAMP '2024-01-01 10:00:00'

-- ❌ Incorrect (will cause parse errors)
TIMESTAMP '2024-01-01 10:00:00-0'

The dbt-exasol adapter automatically handles timestamp formatting for microbatch boundaries.

For more information about the microbatch strategy, refer to the microbatch documentation.

Materialized views

Exasol does not support materialized views. If you attempt to use materialized='materialized_view', the operation will fail with an error.

Workarounds

• Use materialized='table' with appropriate refresh logic
• Use materialized='incremental' with suitable incremental strategies

Clone operations

Exasol does not support table cloning operations. This affects dbt features that rely on CLONE functionality.

Unit test limitations

Exasol has specific limitations with unit tests:

Empty string handling

In Exasol, empty strings are treated as NULL. This affects test fixtures that use empty string literals to simulate empty values. When writing unit tests with seed data, be aware that:

# This seed data
id,name,value
1,test,""  # Empty string

# Will be interpreted as
id,name,value
1,test,NULL  # NULL value in Exasol

Cross-database testing

Unit tests that rely on sources in a database different from the models are not supported. All test fixtures and models must exist in the same database.

Aggregate functions in CTEs

Exasol does not support certain aggregate functions (LISTAGG, MEDIAN, PERCENTILE_CONT) when used within common table expressions (CTEs) created from dbt's unit test fixtures. These functions require user-created tables.

Workaround: Create actual tables for test fixtures rather than using inline CTEs when testing models with these functions.

If you are interested in supporting materialized test fixtures, we encourage you to participate in this issue in GitHub: dbt-labs/dbt-core#8499

Connection configuration

For information about connection parameters such as encryption, SSL/TLS validation, OpenID authentication, and other profile settings, refer to the Exasol setup documentation.

Was this page helpful?

Privacy policyCreate a GitHub issue

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.