sawrus/areas/software/data-engineering/skills/dbt-patterns

Skill: dbt Development Patterns

When to load

When building dbt models, writing macros, or configuring materializations.

Materialization Strategy

| Layer | Materialization | Why | |:---|:---|:---| | staging/ | view | Always reflects source | | intermediate/ | ephemeral | Reduce clutter | | marts/ | table or incremental | Pre-computed for BI |

Incremental Model

{{
    config(
        materialized='incremental',
        unique_key='order_id',
        incremental_strategy='merge',
        partition_by={'field': 'order_date', 'data_type': 'date'},
    )
}}

SELECT order_id, user_id, order_date, total_amount
FROM {{ ref('stg_orders') }}

{% if is_incremental() %}
    WHERE order_date >= (SELECT MAX(order_date) FROM {{ this }}) - INTERVAL '3 days'
{% endif %}

Audit Columns Macro

{% macro audit_columns() %}
    CURRENT_TIMESTAMP AS loaded_at,
    '{{ invocation_id }}' AS dbt_invocation_id,
    '{{ this.name }}' AS dbt_model
{% endmacro %}