Lesson 13 of 13 · 25 min
Preparing an analysis-ready dataset
By the end of this lesson you can design an analysis-ready dataset: state its grain, build it step by step with CTEs (clean, deduplicate, aggregate, join), avoid join fan-out, and validate the output before handing it over.
An analysis-ready dataset is a table (or view, or query result) built for answering questions rather than for running the business. It has four properties:
- A stated grain: exactly what one row represents. One row per order, one row per person, one row per completed visit. Every column must make sense at that grain.
- Clean, typed columns: standardized categories, real dates and numbers, NULLs only where they are honest, and readable names.
- Business rules applied once: what counts as revenue, which status counts as delivered, which registration survives a merge.
- Evidence that it is right: the key is unique, totals reconcile with the source, and no rows were lost or duplicated along the way.
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