dbt Model with Tests and Schema

-- models/marts/fct_orders.sql
{{
  config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='sync_all_columns'
  )
}}

WITH source_orders AS (
    SELECT
        id AS order_id,
        customer_id,
        amount,
        status,
        created_at
    FROM {{ source('raw', 'orders') }}
    {% if is_incremental() %}
    WHERE created_at > (SELECT MAX(created_at) FROM {{ this }})
    {% endif %}
),

customers AS (
    SELECT * FROM {{ ref('dim_customers') }}
)

SELECT
    o.order_id,
    o.customer_id,
    c.customer_name,
    c.region,
    o.amount,
    o.status,
    o.created_at,
    CURRENT_TIMESTAMP AS loaded_at
FROM source_orders o
LEFT JOIN customers c USING (customer_id)
WHERE o.status != 'cancelled'

-- models/marts/schema.yml
-- version: 2
-- models:
--   - name: fct_orders
--     description: Fact table for completed orders
--     columns:
--       - name: order_id
--         tests: [unique, not_null]
--       - name: amount
--         tests: [not_null, positive_values]