Modular SQL Ctes

Modular SQL with Layered CTEs

When to use this skill

Use when transforming a working-but-unreadable SQL query into a maintainable, testable model. Triggers:

• "Refactor this SQL"
• "Make this query more readable"
• "Build a dbt model for…"
• "Modularize this query"
• "This SQL is hard to follow"

Don't use for one-off exploration queries that won't be reused. Refactoring exploratory code is over-engineering.

Required inputs

Workflow

1. Restate the grain in plain English. "Each row is one user-day" or "one order" or "one experiment-variant-day." If you can't say this clearly, the query has a grain bug.

2. Identify the layers. Every analytics query has 3 logical layers:

   • Staging (stg_): rename columns, cast types, light filters. One staging CTE per source table.
   • Intermediate (int_): business logic — joins, derived columns, aggregations to intermediate grain.
   • Fact / Final (fct_/dim_ or final select): the output at target grain, with only the columns consumers need.

3. One purpose per CTE. If a CTE name needs "and" ("users_and_orders_and_revenue"), split it.

4. Filter early. Apply WHERE clauses in staging where possible to reduce data scanned downstream.

5. Use explicit JOIN types — inner join, left join, full join. Never bare join.

6. No SELECT * in production layers. Only allowed in staging if every source column is intentionally used. List columns explicitly in int_ and fct_.

7. Add inline comments ONLY for non-obvious business rules. Don't narrate what the SQL does.

8. Add a final-select preamble. A 1-line comment block at the top of the file stating: grain, primary key, source tables, and refresh cadence.

Output format

-- =========================================================
-- Model: fct_user_daily_activity
-- Grain: one row per (user_id, activity_date) in UTC
-- Primary key: (user_id, activity_date)
-- Sources:
--   raw.events.product_events
--   raw.users.dim_user
-- Refresh: daily at 06:00 UTC
-- Owner: @sarah.kim
-- =========================================================

with stg_events as (
    select
        cast(user_id as varchar) as user_id,
        lower(event_name) as event_name,
        cast(event_at as timestamp_ntz) as event_ts,
        cast(event_at as date) as event_date
    from raw.events.product_events
    where event_at >= '2024-01-01'
      and user_id is not null
),

stg_users as (
    select
        cast(user_id as varchar) as user_id,
        country,
        plan_tier,
        signup_at
    from raw.users.dim_user
    where is_internal_user = false
),

int_events_enriched as (
    select
        e.user_id,
        e.event_ts,
        e.event_date,
        e.event_name,
        case when e.event_name = 'purchase' then 1 else 0 end as is_purchase,
        case when e.event_name = 'session_start' then 1 else 0 end as is_session
    from stg_events e
),

int_user_daily as (
    select
        ee.user_id,
        ee.event_date,
        count(*) as total_events,
        sum(ee.is_purchase) as purchase_count,
        sum(ee.is_session) as session_count
    from int_events_enriched ee
    group by ee.user_id, ee.event_date
),

fct_user_daily_activity as (
    select
        ud.user_id,
        ud.event_date as activity_date,
        u.country,
        u.plan_tier,
        ud.total_events,
        ud.purchase_count,
        ud.session_count,
        case when ud.purchase_count > 0 then 1 else 0 end as did_purchase
    from int_user_daily ud
    inner join stg_users u using (user_id)
)

select * from fct_user_daily_activity;

Validation checks

• Grain stated in header and matches actual output
• Primary key uniqueness can be verified (one row per stated key)
• No select * outside staging
• All joins have explicit type
• CTE names follow stg_ / int_ / fct_ convention
• No CTE has both "and" in its name and >50 lines
• Recent partitions filtered early (no full-table scans without reason)

Edge cases & failure modes

• Snowflake views vs tables: views recompute every time; for expensive logic, materialize as table or dynamic table.
• Joins changing grain: joining a 1:N table without aggregating first inflates row counts. Add qualify row_number() over ... = 1 or aggregate before joining.
• Window functions over too-large partitions: Snowflake spills to disk. Pre-aggregate, then window.
• Date filter in WHERE vs ON: filtering an outer-joined table's date in WHERE converts the join to inner. Put it in ON instead.

dbt-specific notes

• One model per file, named after the final table
• Source declarations in sources.yml
• Column-level docs in <model>.yml
• Tests: at minimum unique and not_null on primary key columns
• Use {{ ref('stg_users') }} for cross-model dependencies
• Materialize stg_ as views, int_ and fct_ as tables

Cross-warehouse notes

Related skills

• warehouse-query-optimization — when the refactored query needs to be fast
• data-quality-audit — verify the output meets grain/uniqueness expectations
• metric-definition — wrap the final model in a metric spec