How to Build a Data Model

Start by identifying the business event: “an order was placed”, “a page was viewed”, “a payment was processed”. Then declare the grain explicitly — it goes in the model file header as a comment and in your dbt model description.

-- models/marts/fct_orders.sql
-- Business process: order placed
-- Grain: one row per ORDER LINE ITEM (not per order)
-- Primary key: order_item_id
-- Foreign keys: customer_id, product_id, date_key

-- schema.yml
models:
  - name: fct_orders
    description: >
      One row per order line item. Grain: order_item_id.
      Use for revenue, quantity, and discount analysis.

Measures are numeric facts you will aggregate (SUM, AVG, COUNT). Dimensions are attributes you will filter and group by. This determines which tables you need.

Staging models (stg_) clean and rename raw source columns with no business logic. One stg_ per source table.

-- models/staging/stg_orders.sql
SELECT
    id              AS order_item_id,
    order_id,
    cust_id         AS customer_id,
    prod_id         AS product_id,
    qty             AS quantity,
    price           AS unit_price,
    disc            AS discount_amount,
    created_at      AS ordered_at
FROM    {{ source('app_db', 'order_items') }}

Dimension tables are wide, flat, and denormalized — every attribute an analyst might want to filter or group by is in one table. Use dbt_utils.generate_surrogate_key() for stable primary keys.

-- models/marts/dim_customers.sql
SELECT
    {{ dbt_utils.generate_surrogate_key(['customer_id']) }}  AS customer_key,
    customer_id,
    full_name,
    email,
    country,
    customer_segment,
    first_order_date,
    lifetime_revenue
FROM    {{ ref('stg_customers') }}

-- models/marts/fct_orders.sql
SELECT
    o.order_item_id,
    o.order_id,
    o.ordered_at,
    c.customer_key,
    p.product_key,
    d.date_key,
    o.quantity,
    o.unit_price,
    o.quantity * o.unit_price            AS gross_revenue,
    o.discount_amount,
    o.quantity * o.unit_price
      - o.discount_amount               AS net_revenue
FROM    {{ ref('stg_orders') }}  o
LEFT JOIN  {{ ref('dim_customers') }}  c USING (customer_id)
LEFT JOIN  {{ ref('dim_products') }}   p USING (product_id)
LEFT JOIN  {{ ref('dim_date') }}       d ON d.date_day = o.ordered_at::date

-- schema.yml tests:
-- unique + not_null on order_item_id
-- relationships: customer_key → dim_customers.customer_key
-- relationships: product_key  → dim_products.product_key