dbt — Data Build Tool

dbt — Data Transformation

# dbt transforms raw data in your warehouse using SQL
# Models are SELECT statements, dbt handles table creation

# Project structure
models/
  staging/
    stg_orders.sql        # clean raw data
    stg_customers.sql
  intermediate/
    int_orders_enriched.sql  # join and enrich
  marts/
    fct_orders.sql        # fact table (analytics ready)
    dim_customers.sql     # dimension table

# stg_orders.sql
{{ config(materialized='view') }}

SELECT
    id                              AS order_id,
    user_id                         AS customer_id,
    CAST(created_at AS TIMESTAMP)   AS order_timestamp,
    UPPER(status)                   AS order_status,
    amount / 100.0                  AS order_amount_usd
FROM {{ source('raw', 'orders') }}
WHERE status != 'test'

# fct_orders.sql
{{ config(materialized='incremental', unique_key='order_id') }}

SELECT
    o.order_id,
    o.customer_id,
    o.order_timestamp,
    o.order_amount_usd,
    c.country,
    c.customer_segment
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('dim_customers') }} c ON o.customer_id = c.customer_id
{% if is_incremental() %}
WHERE o.order_timestamp > (SELECT MAX(order_timestamp) FROM {{ this }})
{% endif %}