🔗 Sources, Refs & Lineage — Connecting the Dots

-- ❌ DON'T DO THIS — hardcoded schema and table names!

SELECT
    c.id        AS customer_id,
    c.first_name,
    o.order_id,
    o.amount
FROM
    raw_production.public.customers c    -- What if the schema changes?
LEFT JOIN
    raw_production.public.orders o       -- What if we move to a new database?
    ON c.id = o.customer_id

version: 2

sources:
  - name: raw_shop              # A friendly name for this group
    database: raw_production     # The actual database name
    schema: public              # The actual schema name
    description: "Raw data from our online shop's PostgreSQL database"

    tables:
      - name: customers
        description: "One row per registered customer"
        columns:
          - name: id
            description: "Primary key"

      - name: orders
        description: "One row per order placed"
        loaded_at_field: _etl_loaded_at    # For freshness checks!
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}

      - name: payments
        description: "One row per payment transaction"

SELECT
    id          AS customer_id,
    first_name,
    last_name,
    email_addr  AS email,
    created_at::DATE AS signup_date
FROM
    {{ source('raw_shop', 'customers') }}
    --  ↑ friendly name   ↑ table name

$ dbt source freshness

Running source freshness checks...
PASS  source raw_shop.customers   # Last loaded 2 hours ago ✅
WARN  source raw_shop.orders      # Last loaded 14 hours ago ⚠️
ERROR source raw_shop.payments    # Last loaded 30 hours ago ❌

-- This model DEPENDS on stg_customers and stg_orders
-- ref() tells dbt about these dependencies!

SELECT
    c.customer_id,
    c.first_name,
    c.email,
    o.order_id,
    o.order_date,
    o.amount
FROM
    {{ ref('stg_customers') }} c
LEFT JOIN
    {{ ref('stg_orders') }} o
    ON c.customer_id = o.customer_id

# Generate the documentation site (including the lineage graph)
$ dbt docs generate

# Open it in your browser
$ dbt docs serve

Running docs generate...
Catalog written to /target/catalog.json
Serving docs at http://localhost:8080
Press Ctrl+C to exit.

version: 2

sources:
  - name: raw_shop
    database: raw_production
    schema: public
    tables:
      - name: customers
      - name: orders

SELECT
    id              AS customer_id,
    first_name,
    last_name,
    email_addr      AS email,
    created_at::DATE AS signup_date
FROM
    {{ source('raw_shop', 'customers') }}
    -- ↑ Points to RAW data (external)

SELECT
    order_id,
    customer_id,
    order_date,
    amount,
    status
FROM
    {{ source('raw_shop', 'orders') }}
    -- ↑ Points to RAW data (external)

SELECT
    c.customer_id,
    c.first_name,
    c.email,
    o.order_id,
    o.order_date,
    o.amount
FROM
    {{ ref('stg_customers') }} c
    -- ↑ Points to ANOTHER dbt model
LEFT JOIN
    {{ ref('stg_orders') }} o
    -- ↑ Points to ANOTHER dbt model
    ON c.customer_id = o.customer_id

SELECT
    customer_id,
    first_name,
    email,
    COUNT(order_id)  AS total_orders,
    SUM(amount)      AS lifetime_value,
    MIN(order_date)  AS first_order_date,
    MAX(order_date)  AS most_recent_order
FROM
    {{ ref('int_customer_orders') }}
    -- ↑ Points to the intermediate model
GROUP BY
    customer_id, first_name, email

-- In the "marketing" project, reference a model from the "core" project

SELECT
    customer_id,
    lifetime_value,
    marketing_channel
FROM
    {{ ref('core', 'fct_customer_ltv') }}
    -- ↑ project name  ↑ model name
LEFT JOIN
    {{ ref('marketing_attribution') }}
    ON ...