Course
Design the Model
A hands-on 15-lesson intermediate SQL course. Turn ambiguous questions into reviewable, governed data models with your AI coding agent.
You can write SQL. Now make the decisions that decide whether its answer holds up.
- Turn vague requests into a model contract before an agent picks the grain, metric, dates, and exclusions for you.
- Profile data before you model it, then make duplicate handling, joins, and currency basis visible in the project.
- Leave an agent context it can use: descriptions, a glossary, asset metadata, and rules that match the data.
- The habits transfer to dbt, Dataform, SQLMesh, or an in-house SQL stack. Bruin is the local environment for the work.
How it works
Your coding agent teaches the course. It reads the project, teaches one lesson at a time, asks the quiz questions, and reviews the task after you finish it. These pages are a companion you can use alongside that conversation.
1. Paste this setup prompt into your agent in an empty folder.
You are going to set up and then teach me an interactive SQL course. Do this in order, and show me each command before you run it:
- Check that Git and Bruin are installed (
git --version,bruin version). If Bruin is missing, install it withcurl -LsSf https://getbruin.com/install/cli | sh, then check the version again. - Run
bruin init academy-sql-intermediatein this folder. - Generate the sample data with
bruin run academy-sql-intermediate/pipeline, then confirmordershas 1,212 rows,order_itemshas 2,895 andfx_rateshas 5,480. - Read
academy-sql-intermediate/AGENTS.mdandacademy-sql-intermediate/course/README.mdso you know how to run the course. - Greet me, show me the 15-lesson syllabus, and tell me to say "next lesson" to begin and "review my work" whenever I finish a task.
Do not teach lesson one yet - just get set up and hand me the controls. If any command fails, stop and show me the error instead of trying something else.
2. Say next lesson. Your agent teaches the first unfinished lesson, asks its questions one at a time, then gives you the task.
3. Do the task and say review my work. The agent reads the file or answer you produced, runs the checks required by the lesson, and gives one concrete correction if it does not pass yet.
The first three lessons resolve ambiguity and profile the source. The next four make queries reviewable. Lessons eight through ten build a layered model. The final five turn documentation into measured context, then ask you to defend a churn-risk answer under scrutiny.
What you will build
You will work in a small retail project with seven generated source tables. The data is deterministic, deliberately under-documented, and contains exact duplicate lines, changed-status order resends, orphan keys, inconsistent text values, NULLs, and daily FX rates. Those conditions make each choice reproducible rather than theoretical.
By the capstone, you will have a model contract, staged source assets, core and mart assets, column descriptions, a glossary, and a written defence. The target is not a clever query. It is an answer another analyst can read, rerun, and challenge.
Before you start
Take Ask the Data first if you have not written joins, aggregates, and CTEs by hand. This course starts from model design, not SQL syntax.
Before you start
- The beginner course, Ask the Data, or equivalent experience writing and reviewing SQL joins, aggregates, and CTEs.
- An AI coding agent, a terminal, and a code editor. The setup prompt installs the local project and data.
15 steps
Course outline
From question to specification
Resolve ambiguity, write the contract, and profile the source before modeling it.
- 1The question is the hard partTurn an ambiguous request into a specification before an agent chooses for you.7 min
- 2Write the model contractFix the question, grain, keys, metric, filters, and exclusions before the SQL exists.10 min
- 3Profile before you modelRun six checks that reveal the source assumptions a model would otherwise inherit.12 min
Design queries that survive review
Use readable stages, windows, and grain annotations to expose silent failures.
- 4Stage the workGive each query step a clear name and grain so another reader can review it.10 min
- 5Window functionsChoose a window by the question it answers, with explicit partitions and boundaries.12 min
- 6Patterns agents get wrongDiagnose duplicate rows, missing zeroes, date boundaries, and FX fan-out.12 min
- 7Read a query fastAnnotate grain, locate the fan-out, and quantify what it changed.10 min
Build the model
Layer the project, choose materializations, and keep the metric with the asset.
- 8Layer the projectSeparate staging, core, and mart work so every model has a reviewable lineage.12 min
- 9Views and tablesChoose storage based on freshness and cost, then change it without changing the data.10 min
- 10Metric in the assetPut the definition, owner, currency basis, and limitations beside the model.8 min
Governance as agent context
Write durable context, measure it, and defend a finished answer.
- 11Descriptions and tagsWrite context that tells an agent what the column name alone cannot.12 min
- 12Glossary and READMEPut definitions, operating rules, and project orientation in their proper homes.10 min
- 13Measure your contextScore an agent before and after your context changes instead of assuming they helped.12 min
- 14Capstone: defend the answerBuild a churn-risk model, verify it three ways, and answer the strongest objections.30 min
- 15Recap and next stepsChoose the habits and the real work you will carry into the next course.5 min
Additional resources
Get help & contribute