Lab: Build and explore the database
Generate the database and answer a few questions in raw SQL — feeling the pain the semantic layer will remove.
A working percolate.db on your machine, and firsthand experience writing the raw SQL you'll soon never write again.
Setup — the starter kit and a virtual environment
First, cd into the starter-kit folder you downloaded/cloned (semantic-agent/) and create a virtual environment — an isolated, per-project copy of Python that keeps this course's packages separate from your system install:
cd course-assets/semantic-agent # the starter-kit folder you downloaded/cloned
python3 -m venv .venv && source .venv/bin/activate
pip install -r requirements.txtOn macOS (and many Linux distros), the system Python refuses bare pip install commands outside a virtual environment with an "externally-managed-environment" error. Creating and activating .venv first is what makes pip install work — and every lab that follows assumes this venv is active.
Step 1 — Build the database
cd data
python3 generate_data.py
# → Built percolate.db, with a summary of row counts and total revenueNote: the starter kit ships with percolate.db pre-built, so this step is optional — running the generator is your proof that the data is reproducible (fixed seed, identical output every time), not a prerequisite.
Step 2 — Ask it questions in raw SQL
Open the database with the sqlite3 CLI (or any SQLite browser) and run these. Notice how much you have to know to get each one right.
-- Total completed revenue (net of discounts)
SELECT ROUND(SUM(oi.quantity * oi.unit_price - oi.discount), 2)
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE o.status = 'completed'; -- → 743772.8
-- Revenue by acquisition channel (requires a second join to customers)
SELECT c.channel, ROUND(SUM(oi.quantity * oi.unit_price - oi.discount), 2) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.channel
ORDER BY revenue DESC;Step 3 — Feel the friction
- 1Try "average order value by channel." You'll need revenue ÷ distinct orders — and get the DISTINCT wrong and the join fans out your order count. Write it and check against $85–$94 per channel.
- 2Try "customer acquisition cost by channel." Now you're dividing marketing_spend (one table) by new customers (another table) — two separate aggregations glued together. This one is genuinely fiddly.
- 3Note how each answer required you to remember: which tables join, which rows to exclude, whether to use DISTINCT, how to avoid double-counting.
Every rule you had to remember — refunds excluded, DISTINCT on orders, the right join path — is a rule you'll encode once in the semantic layer in the next module. After that, "AOV by channel" becomes a one-line request that's correct by construction.
Keep your raw-SQL answers. In Module 3 you'll verify the semantic layer produces exactly the same numbers — proof that your governed definitions match reality.