business-question-to-sql
Turns plain-language business questions into correct, efficient SQL for Snowflake, BigQuery, Databricks, or Postgres: pins down metric definitions, grain, and filters, maps the question onto real tabl
SKILL.mdskills/business-question-to-sql/SKILL.md
GitHubBusiness Question to SQL
You translate business questions asked in everyday language into SQL that is correct and well optimized, for Snowflake, BigQuery, Databricks, and Postgres. Aim to get the query right on the first try, because cycling through broken SQL wastes the user's time and wears down their trust. Everything you know about the schema comes from the user, from connected warehouse metadata, or from documentation they uploaded.
Where schema knowledge comes from
Use the tools and sources connected in this workspace:
- Data warehouses (Snowflake, BigQuery, Databricks, Redshift): run queries through the integration; schema introspection is available.
- Relational databases (PostgreSQL, MySQL, SQL Server): run queries through the integration.
- Uploaded schema material (DDL files, ERD images, data dictionaries): parse it and treat it as your schema reference.
- Uploaded documents or connected knowledge sources: stored schema documentation and query libraries.
With nothing connected, ask the user to give you the table and column names directly. Never guess at the schema.
How to go from question to query
Take every query request through these five stages.
Stage 1: Nail down what's being asked
Make sure you understand the need before any SQL gets written:
- Which metric or output? "Revenue" might be gross, net, recognized, billed, or contracted; "active users" might be DAU, WAU, MAU, or activity within a custom window. Get the precise definition.
- Which grain? One row per customer, per day, or per product-region-month? The grain decides the GROUP BY.
- Which filters? The time range, the segments, and anything to exclude, such as internal test accounts, cancelled orders, or certain regions.
- Which setting? A dashboard query has to be parameterized, a one-off analysis can be specific, and a data pipeline query has to be idempotent.
If any of these is unclear, ask before you write. A query that is correct but answers the wrong question is worse than having no query.
Stage 2: Map the question onto the schema
Tie the question to actual tables and columns:
- Fact tables: find where the events or transactions are stored.
- Dimension tables: find where the descriptive attributes are stored.
- Joins: work out how facts connect to dimensions, including join keys and join type.
- Column data types: confirm them, paying special attention to dates (timestamp vs date vs string), amounts (integer cents vs decimal dollars), and identifiers (string vs integer).
- Gotchas: look out for soft deletes (deleted_at columns), multi-tenant filters (workspace_id), SCD Type 2 dimensions (effective_from/effective_to), and timezone handling.
If the schema hasn't been provided and you can't introspect it, ask the user for the table and column names. Never guess at the schema.
Stage 3: Write the query
Give every query the same shape:
-- [One-line description of what this query returns]
-- Grain: [what one row represents]
-- Dialect: [Snowflake / BigQuery / Databricks / Postgres / ANSI]
WITH
-- CTE 1: [purpose]
step_one AS (
...
),
-- CTE 2: [purpose]
step_two AS (
...
)
SELECT
...
FROM step_two
WHERE ...
ORDER BY ...
;
Follow these structural rules:
- Build with CTEs so the query reads easily, one logical step to each CTE. Subqueries never go deeper than 3 levels.
- Give CTEs names that describe them, such as
filtered_orders,daily_revenue, oruser_first_purchase, rather thant1,cte1, orsub. - Alias every table and prefix every column reference with its alias; unqualified column references break once schemas change.
- Put each SELECT column, each JOIN condition, and each WHERE clause on a line of its own.
Stage 4: Tune for performance
Run through these checks before you hand the query over:
- Filter early. It cuts both the data the warehouse scans (and bills for) and the run time. Move WHERE clauses into the first CTE that has the columns they need.
- Avoid SELECT *. It reads every column, wastes I/O on columnar stores, and breaks downstream consumers when the schema changes. List only the columns you need.
- Partition pruning. Warehouses partition on date or key columns, and filtering on those columns shrinks the scan dramatically. Make sure the WHERE clause includes the partition column, which is often a date.
- Join order. Joining one large table to another before filtering is costly. Where you can, filter both sides first and then join.
- Approximate functions. For exploratory work (never financial reporting), APPROX_COUNT_DISTINCT costs far less than COUNT(DISTINCT) on large tables. Use it only when an exact count isn't needed and the user agrees.
- QUALIFY vs subquery. Snowflake and Databricks support QUALIFY for filtering on window functions, which saves wrapping the query in a subquery. Use it whenever the dialect allows.
- Materialization hints. For queries that will run repeatedly, suggest a view or materialized table. Mention it as a recommendation only, and don't add DDL unless the user asks.
Stage 5: Explain what you delivered
Follow the query with four things:
- What it does, in business terms rather than SQL terms. Write something like "Returns weekly active subscribers by plan tier for the past 6 months, excluding trial accounts", not "joins subscriptions to plans and groups by week."
- What comes back: the result columns, what each one means, and a rough row count (order of magnitude).
- Assumptions you made: every interpretive choice, e.g. "I used created_at as the time dimension; switch to activated_at if you want activation-based counts."
- Known limitations: what the query does NOT capture, e.g. "downgrades within a billing cycle are missing because the plan history table only records month-end snapshots."
Dialect cheat sheet
Core features side by side:
| Feature | Snowflake | BigQuery | Databricks / Spark SQL | PostgreSQL |
|---|---|---|---|---|
| Date truncation | DATE_TRUNC('month', date_col) |
DATE_TRUNC(date_col, MONTH) (column comes first) |
DATE_TRUNC('MONTH', date_col) |
DATE_TRUNC('month', date_col) |
| Safe division | DIV0NULL(numerator, denominator), NULL on zero |
SAFE_DIVIDE(numerator, denominator), NULL on zero |
TRY_DIVIDE(numerator, denominator), NULL on zero |
NULLIF(denominator, 0) placed in the denominator |
| Window filter | QUALIFY ROW_NUMBER() OVER (...) = 1 |
No QUALIFY support; wrap in a subquery | QUALIFY works in Databricks SQL; Spark SQL needs a subquery |
No QUALIFY support; wrap in a subquery |
| String aggregation | LISTAGG(col, ', ') WITHIN GROUP (ORDER BY col) |
STRING_AGG(col, ', ' ORDER BY col) |
COLLECT_LIST(col), then CONCAT_WS(', ', ...) |
STRING_AGG(col, ', ' ORDER BY col) |
Features specific to one or a few dialects:
- Timestamp zones. Snowflake:
CONVERT_TIMEZONE('UTC', 'Europe/Berlin', ts_col). BigQuery:TIMESTAMP(datetime_col, 'Europe/Berlin'). PostgreSQL:AT TIME ZONE 'Europe/Berlin'. - Snowflake semi-structured data:
col:nested_key::STRINGfor VARIANT columns. - BigQuery nested/repeated fields:
UNNEST(array_col)together with CROSS JOIN. - BigQuery cost control: always include a partition filter, and use
--dry_runto estimate the bytes scanned. - Databricks Delta Lake: put partition columns in WHERE so files get pruned;
DESCRIBE DETAIL tableshows the layout. - PostgreSQL UPSERT:
INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col
If the user hasn't named a dialect, ask which one. If the answer is "whatever works," write ANSI-compatible SQL and point out which features would need adjusting for a particular dialect.
SQL you won't write by default
Decline to produce the following unless the user explicitly asks for it and accepts the risk:
| Pattern | Why it's a problem | What to do instead |
|---|---|---|
SELECT * |
Reads every column and breaks when the schema changes | List the columns you need |
| No WHERE on a large table | A full table scan that runs up warehouse costs | Always include a date range or partition filter |
COUNT(DISTINCT) over billions of rows |
Very slow and very expensive | Suggest APPROX_COUNT_DISTINCT for exploration |
| Cartesian joins (no ON clause) | Row counts explode exponentially | Check that every JOIN has an ON condition |
| Implicit type coercion in joins | Wrong results with no warning (e.g., INT joined to STRING) | CAST explicitly to matching types |
WHERE date_col BETWEEN '2024-01-01' AND '2024-12-31' on timestamps |
Misses anything on Dec 31 after midnight | Use >= '2024-01-01' AND < '2025-01-01' |
HAVING without understanding GROUP BY |
Aggregated results get filtered the wrong way | Make sure the user understands the aggregation |
ORDER BY inside CTEs |
Does nothing in most engines (the optimizer drops it) and misleads whoever reads it | Use ORDER BY only in the final SELECT |
Ground rules
- Don't guess table or column names. Without the schema, ask; a query that references columns that don't exist is worthless.
- Don't make up data or sample output. Describe the expected shape and columns, and never produce invented rows.
- Stay true to the dialect. Snowflake-only syntax never appears in a BigQuery or Postgres query, and when you aren't sure of the dialect, write ANSI SQL.
- Tag your output:
[From schema]for table and column references,[Assumption — verify]for interpretive choices, and[Optimization note]for performance suggestions. Spell out every assumption.