dbt Production Fact Model Template

Replace {{PLACEHOLDERS}} with your environment values, then deploy to your stack.

-- =============================================================================
-- DBT PRODUCTION FACT MODEL TEMPLATE
-- Copy to models/marts/fct_{{ENTITY}}.sql and replace {{PLACEHOLDERS}}
-- =============================================================================

{{ config(
    materialized='incremental',
    unique_key='{{PK_COLUMN}}',
    incremental_strategy='merge',
    on_schema_change='sync_all_columns',
    tags=['{{DOMAIN}}', 'marts'],
    meta={
        'owner': '{{OWNER_TEAM}}',
        'sla_hours': {{SLA_HOURS}},
        'grain': 'one row per {{GRAIN_DESCRIPTION}}'
    }
) }}

/*
  Model: fct_{{ENTITY}}
  Domain: {{DOMAIN}}
  Grain:  one row per {{GRAIN_DESCRIPTION}}
  Source: {{SOURCE_SCHEMA}}.stg_{{SOURCE_TABLE}}
*/

WITH source AS (
    SELECT *
    FROM {{ ref('stg_{{SOURCE_TABLE}}') }}
    {% if is_incremental() %}
    WHERE _loaded_at > (SELECT COALESCE(MAX(_loaded_at), '1900-01-01') FROM {{ this }})
    {% endif %}
),

deduplicated AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY {{PK_COLUMN}}
            ORDER BY _loaded_at DESC
        ) AS _row_num
    FROM source
),

enriched AS (
    SELECT
        {{PK_COLUMN}},
        -- {{METRIC_COLUMNS}} - add your business columns here
        {{DATE_COLUMN}},
        {{AMOUNT_COLUMN}},
        CASE
            WHEN {{AMOUNT_COLUMN}} < 0 THEN TRUE
            ELSE FALSE
        END AS is_anomaly_flag,
        _loaded_at,
        CURRENT_TIMESTAMP() AS _dbt_updated_at
    FROM deduplicated
    WHERE _row_num = 1
)

SELECT * FROM enriched

-- ── Companion schema.yml tests (add to models/marts/schema.yml) ──────────────
-- models:
--   - name: fct_{{ENTITY}}
--     description: "{{DESCRIPTION}}"
--     columns:
--       - name: {{PK_COLUMN}}
--         tests: [unique, not_null]
--       - name: {{DATE_COLUMN}}
--         tests: [not_null]
--       - name: {{AMOUNT_COLUMN}}
--         tests:
--           - dbt_expectations.expect_column_values_to_be_between:
--               min_value: 0
--               max_value: {{MAX_AMOUNT}}