Unit 11.03: Keeping SQL in reviewable files, not buried in code

Unit ID: SQL-M11-U04 - Estimated active time: 12-15 minutes Objective: organise queries so they can be reviewed, diffed, and run independently.

A query embedded in code stops being reviewable

df = con.execute("SELECT c.country, COUNT(*) AS orders, SUM(o.order_total) AS revenue FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE o.status = 'completed' GROUP BY c.country").df()

That is a real analytical claim compressed into one unreadable line. Nobody will review it, and a diff will show the whole line changed when one word did.

Keep the query readable in the file

REVENUE_BY_COUNTRY = """
SELECT COALESCE(c.country, 'Unknown') AS country,
       COUNT(*)                       AS orders,
       SUM(o.order_total)             AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY 1
ORDER BY revenue DESC
"""

df = con.execute(REVENUE_BY_COUNTRY).df()

Named, formatted, and diffable. A reviewer can read the SQL without reading the Python.

Better: a .sql file

from pathlib import Path

query = Path("queries/revenue_by_country.sql").read_text()
df = con.execute(query).df()

Now the query can be opened in a SQL editor, run against the database directly, and reviewed by someone who does not read Python at all. It also means the validation query from Module 10 can live in the same file as a comment:

-- queries/revenue_by_country.sql
-- VALIDATION (expect 988 orders | 2685905.00 revenue in total)
SELECT COALESCE(c.country, 'Unknown') AS country,
       COUNT(*)                       AS orders,
       SUM(o.order_total)             AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY 1
ORDER BY revenue DESC;

Do not rebuild query text conditionally

# Fragile: the actual SQL executed is now invisible
sql = "SELECT * FROM orders"
if only_completed:
    sql += " WHERE status = 'completed'"
if limit:
    sql += f" LIMIT {limit}"

You can no longer read the query that ran, which makes debugging guesswork. Prefer one complete query with parameters, or a small number of explicit named queries.

Practice

Take the revenue-by-country query and say where you would store it, plus the one comment you would add.

-- VALIDATION (expect 988 | 2685905.00)
-- SELECT COUNT(*), SUM(order_total) FROM orders WHERE status = 'completed';

Takeaway

Keep SQL formatted, named, and ideally in its own file. A query nobody can read is a query nobody will review.

---

Mark complete