dbt-transformation-patterns

Organize dbt models into staging, intermediate, and marts layers. Staging models map 1:1 to sources with light cleaning, intermediate models hold business logic and joins, and marts contain final dimension and fact tables for analytics.

Set materialized='incremental' with a unique_key in the model config, then use the is_incremental() macro to filter new rows, for example where _fivetran_synced exceeds the max loaded timestamp. Choose delete+insert, merge, or insert_overwrite as the strategy.

Use stg_ for staging models like stg_stripe__payments, int_ for intermediate models like int_payments_pivoted, and dim_ or fct_ for marts like dim_customers and fct_orders. Double underscores separate the source system from the entity name.

Use delete+insert as the default for most warehouses, merge when handling late-arriving data with specific update columns, and insert_overwrite for partition-based tables on date fields. The choice depends on warehouse support and data arrival patterns.

Define tests in YAML files alongside models using built-in tests like unique, not_null, relationships, and accepted_values, plus dbt_utils tests like expression_is_true and recency. Run them with dbt test or combine runs and tests with dbt build.

Avoid incremental models for small tables under roughly one million rows where full refreshes are cheap, and for logic requiring full historical recomputation. Incremental models add complexity and can drift if late-arriving data falls outside the lookback window.