modular-sql-cteslisted

# 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 | Input | Why it matters | |---|---| | Current SQL | The query to refactor | | Target grain | What one row in the final output represents | | Source tables | Where the data is coming from | | Warehouse | Snowflake / BigQuery / Postgres / Redshift | | dbt or raw SQL | Affects model layout | ## 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 possi