Lesson 11 of 11 · 20 min
Documenting analytical queries
By the end of this lesson you can document an analytical query so that a colleague can verify it without asking you: a header block, CTEs named after what they contain, comments that record decisions, and COMMENT ON for published objects.
Documentation for an analytical query is not a description of the SQL (the SQL already says what it does). It records what the result means and why the query makes the choices it makes. Four layers do most of the work:
- A header block at the top: purpose and requester, the grain of the result, sources, the KPI definitions, filters and exclusions, assumptions and known limitations, and the date it was last reviewed.
- CTE names that say what each step contains:
paying_accounts_at_year_end, nott1;first_purchase_per_customer, notx. A reader can follow the logic from the names alone. - Inline comments on decisions, not on syntax:
-- ended_at is exclusiveor-- guest checkout (key 0) is not a customeris useful;-- join the tablesis noise. - Column names with meaning and units:
net_sales_sar,return_rate_pct,region_at_sale.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons