Sources and the Staging Layer

Staging is where messy raw data becomes a clean foundation. One stg_ model per source table. Light transformations only.

What goes in staging

For each source table, a stg_<source>__<table>.sql:

-- models/staging/shopify/stg_shopify__orders.sql

WITH source AS (
    SELECT * FROM {{ source('shopify', 'orders') }}
),

renamed AS (
    SELECT
        id           AS order_id,
        user_id      AS customer_id,
        status       AS order_status,
        total_price  AS order_total,
        currency,
        created_at   AS order_placed_at,
        updated_at   AS order_updated_at
    FROM source
    WHERE NOT is_test_order
)

SELECT * FROM renamed

Two CTEs: source (grab the raw data) and renamed (light cleanup). Community-standard pattern.

What belongs in staging

YES:

• Column renames (camelCase → snake_case, source-specific → canonical).
• Type casts (string IDs to integers, ISO strings to timestamps).
• Light filtering (remove test rows, internal accounts).
• Simple deduplication (one row per primary key via ROW_NUMBER).
• Timezone normalization (always UTC).

NO:

• Joins to other tables — that's intermediate or marts.
• Business logic — that's marts.
• Aggregations — that's marts.

Naming conventions

Pattern	Example
stg_<source>__<table>	stg_shopify__orders, stg_stripe__charges
Column: <entity>_id	order_id, customer_id
Column: <entity>_<attribute>	order_total, customer_email
Timestamps: <event>_at	order_placed_at, payment_completed_at

Materialization

Staging is almost always views:

models:
  my_project:
    staging:
      +materialized: view

Cheap, always fresh, no storage. Switch to tables only if source transformations are expensive (regex, JSON parsing).

Source-level tests

sources:
  - name: shopify
    schema: raw_shopify
    tables:
      - name: orders
        columns:
          - name: id
            tests:
              - unique
              - not_null
        freshness:
          warn_after: {count: 24, period: hour}
          error_after: {count: 48, period: hour}
        loaded_at_field: created_at

Catches Fivetran double-loads (uniqueness) and broken extracts (freshness) early.

Catching duplicates

A common CDC issue: same row arrives twice. Defense:

WITH source AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY _fivetran_synced DESC) AS rn
    FROM {{ source('shopify', 'orders') }}
)
SELECT id AS order_id, ... FROM source WHERE rn = 1

Common mistakes

1. Joining in staging — moves logic out of scope.
2. Computing measures in staging — that's marts territory.
3. Skipping staging and using source() directly in marts.
4. One stg model joining multiple sources — split into per-source staging.

Takeaway

One stg per source. Rename, cast, filter, dedupe. No joins, no business logic. Views. Add source tests + freshness checks.