Marts: dim and fact tables for the star schema

The marts layer is where queries actually run. Six models. Four dim tables (customers, orders, products, sellers) and two fact tables (order_items, order_payments). Each one is a single SELECT that pulls from stg/ and produces a clean, reusable model.

cloud_run_dbt/dbt/models/marts/fact_order_items.sql
sql
{{
    config(
        materialized='incremental',
        incremental_strategy='merge',
        unique_key=['order_id','order_item_id'],
        on_schema_change='fail',
        cluster_by = ["order_id","order_item_id"],
    )
}}

with source_stg as (
    SELECT * FROM {{ ref('fact_order_items_stg') }}
    {% if is_incremental() %}
        where last_extract_ts > (SELECT max(last_extract_ts) FROM {{ this }})
    {% endif %}
)
SELECT S.* FROM source_stg S

Incremental materialization with merge strategy on the composite key. Cluster by the join keys for downstream speed. The is_incremental block uses last_extract_ts to filter to new rows only.

Incremental materialization saves money. Daily, dbt processes only new rows since last run, not the whole table. The merge strategy handles updates to existing rows. on_schema_change=fail makes column drift loud, not silent.

Matching exercise: Match the mart to its grain

Loading practice…

For small dim tables (under a million rows), full table refresh is simpler and more reliable. For large slowly-changing dims, snapshot tables (dbt snapshot) handle history. Incremental is the right default for high-volume fact tables only.