dbt-clickhouse Adapter

Starting from dbt-clickhouse version 1.10, you can use the explicit target feature to decouple the target table from the materialized views (MVs).

In the default (implicit) approach, dbt manages the target table and all MVs within a single model using UNION ALL. The explicit target approach allows you to define the target table as a standalone table materialization and then reference it from multiple separate materialized_view models. This provides better resource separation, individual configuration for each resource, and easier iteration using dbt tooling.

Note: This feature is in Beta. The API may change.

-- Step 1: Define target table (events_daily.sql)
{{ config(materialized='table', engine='SummingMergeTree()', ...) }}
SELECT ... WHERE 0

-- Step 2: Define MV (page_events_aggregator.sql)
{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}
SELECT ...

Refreshable materialized views periodically re-execute a query and store the result, making them suitable for periodic snapshots or aggregations rather than real-time triggers. This feature works with both implicit and explicit target approaches.

To use them, add a refreshable configuration object to your model.

Note on Updates: Since dbt-clickhouse 1.10.2, changes to interval, randomize, and depends_on are applied in-place using ALTER TABLE ... MODIFY REFRESH during a regular dbt run. However, converting between a regular MV and a refreshable one, or changing the append setting, requires a dbt run --full-refresh.

{{
    config(
        materialized='materialized_view',
        engine='MergeTree()',
        order_by='(event_date)',
        refreshable={
            "interval": "EVERY 5 MINUTE",
            "randomize": "1 MINUTE",
            "append": True,
            "depends_on": ['schema.depend_on_model'],
            "depends_on_validation": True
        }
    )
}}

SELECT
    toStartOfDay(event_time) AS event_date,
    count() AS total
FROM {{ source('raw', 'events') }}
GROUP BY event_date

In dbt, a model is a SQL SELECT statement. A materialization defines the strategy used to persist that model in ClickHouse. dbt-clickhouse supports the following core materialization types:

• view (default): Creates a standard ClickHouse view.
• table: Creates a standard ClickHouse table.
• ephemeral: Does not create an object in the database; instead, it injects the model's logic as a Common Table Expression (CTE) into dependent models.
• incremental: Materializes as a table initially, then performs incremental inserts/updates in subsequent runs to capture new data.
• materialized view: Creates a ClickHouse materialized view. Note that creation with an explicit target is currently in Beta.

-- Example mental model of a dbt model
SELECT 
    user_id, 
    count(*) as event_count
FROM raw_events
GROUP BY user_id

The append strategy is the most efficient incremental method because it performs a direct INSERT INTO without creating temporary tables or copying existing data.

When to use: Use this for immutable, event-style data where rows are never updated.

Warning: Since there is no deduplication, if your incremental filter selects a row that already exists in the target table, you will have duplicates. To avoid this, ensure your filter uses a strict inequality (e.g., > instead of >=) and that your data is truly new.

Configuration: Set incremental_strategy='append' in your model's config block.

{{
    config(
        materialized='incremental',
        incremental_strategy='append',
        unique_key='order_id',
        engine='MergeTree()',
        order_by='(ordered_at, order_id)',
        partition_by='toYYYYMM(ordered_at)'
    )
}}

...

{% if is_incremental() %}

-- use '>' to avoid duplicates with append strategy
where ordered_at > (select max(ordered_at) from {{ this }})

{% endif %}

The insert_overwrite strategy replaces entire partitions rather than individual rows. This is highly efficient and follows the 'partition immutability' best practice.

Advantages:

• Faster: It doesn't copy the entire table (unlike the default strategy).
• Safer: It is atomic. The original table is only modified via ALTER TABLE ... REPLACE PARTITION after the new data has been successfully inserted into a staging table. If the process fails, the original table remains untouched.

Requirements:

• You must have a partition_by configuration defined in your model.

Workflow:

1. Create a staging table with the same structure as the target.
2. Insert the new data into the staging table.
3. Identify which partitions are present in the staging table.
4. Use ALTER TABLE ... REPLACE PARTITION ... FROM to swap those specific partitions in the target table with the ones from the staging table.
5. Drop the staging table.

Configuration: Set incremental_strategy='insert_overwrite' in your model's config block.

{{
    config(
        materialized='incremental',
        incremental_strategy='insert_overwrite',
        unique_key='order_id',
        engine='MergeTree()',
        order_by='(ordered_at, order_id)',
        partition_by='toYYYYMM(ordered_at)'
    )
}}

The materialized_view materialization supports two management approaches: Implicit target and Explicit target (Beta).

Implicit Target

In this mode, dbt manages the target table and all associated materialized views as a single unit.

• Management: Individual resources cannot be managed separately; all changes happen together.
• Schema Changes: Managed via the standard on_schema_change setting (defaults to ignore).
• Full Refresh: Recreates the target table empty and recreates all MVs. Uses catchup=True by default to backfill data using the SQL of all MVs combined.

Explicit Target (Beta)

This approach allows for individual management of the target table and the materialized views.

• Management: Changes can be applied to the target table or specific MVs independently.
• Schema Changes: The target table automatically detects if it is a target for a dbt-defined MV. Column evolution is managed by the mv_on_schema_change setting, which defaults to fail to provide a protection layer against unexpected schema changes.
• Full Refresh: The target table is recreated normally, while MVs are dropped and recreated. catchup=True is available for an initial backfill.