Naming Conventions That Scale

A naming convention only pays off once more than one person (or future-you, in six months) reads the SQL, but it's cheap to establish and expensive to retrofit, so it's worth getting right from the first table. Three rules cover most of it: pick snake_case (the overwhelming SQL convention) and never mix it with camelCase in the same schema; prefix by pipeline layer, not by data type — stg_/fct_/dim_ (Parts 2 and 10) tells a reader where in the pipeline a table sits, which is genuinely useful, while Hungarian-style prefixes like tbl_/vw_ just restate what the schema already shows; and name booleans as questions — is_current (Part 8), is_terminal_status (Part 10's dbt macro) read unambiguously as true/false, where a name like status_flag or current leaves a reader guessing what true actually means.

CTE vs. Subquery: The Decision

Part 4 introduced both; this is the rule of thumb for choosing between them once you already know how to write either one.

Reminder from Part 9: don't assume a CTE referenced twice is computed only once — engines differ, and some decide based on cost. Check the plan.

The Hard Rule from Part 1

Every UPDATE/DELETE in this entire series was preceded by a SELECT using the identical WHERE clause. This is not optional style — it's the difference between a scoped change and an accidental full-table wipe.

SQL · preview, then act, with the same WHERE both times

-- 1. Preview exactly which rows will be touched
SELECT id, name, membership_level
FROM customers
WHERE membership_level = 'vip';

-- 2. Only then run the change, with the identical WHERE
UPDATE customers SET membership_level = 'vip' WHERE membership_level = 'vip';

-- Multi-statement changes: wrap in a transaction so a partial failure can't
-- leave the data half-changed (this is exactly Part 8's SCD Type 2 upsert):
BEGIN TRANSACTION;
UPDATE dim_customer_scd2 SET end_date = '2024-06-15', is_current = 0
    WHERE customer_id = 7 AND is_current = 1;
INSERT INTO dim_customer_scd2 (customer_id, name, city, effective_date, is_current)
    VALUES (7, 'Grace Kim', 'Montreal', '2024-06-15', 1);
COMMIT;

Comment the Why, Not the What

A comment that restates what the SQL already says in plain English adds nothing — -- select all customers above SELECT * FROM customers tells a reader nothing they couldn't already read directly. A comment earns its place when it captures something the SQL itself structurally can't express: a hidden constraint, a business reason, a workaround for a specific known issue. "Grace moved from Vancouver to Montreal on 2024-06-15" (Part 8) is exactly that — the SQL shows that a row changed, but only a comment can say why, and why is usually what the next person reading it actually needs to know.

SQL · naming + comments, good vs. not

-- GOOD: snake_case, layer-prefixed, boolean named as a question, comment explains WHY
-- Grace moved from Vancouver to Montreal on 2024-06-15 -- see Part 8 for the full SCD history
SELECT customer_id, city, is_current
FROM dim_customer_scd2
WHERE is_current = 1;

-- NOT: mixed casing, type-prefixed name, ambiguous boolean, comment restates the obvious
-- select all customers
SELECT customerId, city, currentFlag
FROM tbl_customer_snapshot;

Common Errors & Fixes

Two genuinely new traps, plus a map to where every other recurring error in this series already gets its full explanation — scattered by design, next to the concept it belongs to, but consolidated here for a quick lookup.

SQL · JOIN fan-out (the most common silent wrong number)

-- WRONG: COUNT(o.id) after joining order_items counts each order ONCE PER LINE ITEM
SELECT c.name, COUNT(o.id) AS orders_wrong, SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id   = o.id
GROUP BY c.name;
-- Measured on 1 customer, 3 orders, 6 line items total: orders_wrong = 6, not 3.
-- The join to order_items multiplied every order row by its own line-item count --
-- SUM(revenue) is still correct (it's summing the right column), but COUNT(o.id) isn't.

-- RIGHT: COUNT(DISTINCT o.id) counts each order once, no matter how many line items it has
SELECT c.name, COUNT(DISTINCT o.id) AS orders_right, SUM(oi.quantity * oi.unit_price) AS revenue
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id   = o.id
GROUP BY c.name;
-- Measured, same data: orders_right = 3 -- the actual order count.

SQL · ambiguous column reference

-- WRONG: both tables have an "id" column -- the engine can't tell which one you mean
SELECT id FROM customers c JOIN orders o ON o.customer_id = c.id;
-- Error: ambiguous column name: id  (verified against this exact query)

-- RIGHT: qualify every column that exists on more than one side of a JOIN
SELECT c.id FROM customers c JOIN orders o ON o.customer_id = c.id;

General strategy when a query errors or looks wrong: read where the engine's error message actually points (not just the first line); remove JOINs/clauses one at a time until the result matches expectations again, to isolate which one introduced the problem; and check the schema (PRAGMA table_info, or the platform's equivalent) before assuming a column means what its name suggests.

A Short Code-Review Checklist

Every habit above, condensed into what to actually check before merging any SQL change:

• Every UPDATE/DELETE has a matching SELECT preview, tested against a non-production copy first.
• No SELECT * in anything that runs repeatedly in production (Part 9 — costs both performance and, on consumption-priced platforms, money).
• Multi-statement changes are wrapped in a transaction.
• New tables/models have at least unique/not_null tests on their primary key (Part 10).
• Any hard-coded date or threshold has a comment explaining why that specific value.
• If touching a dimension table, confirm whether SCD Type 1 or Type 2 (Part 8) is the right call for the changed attribute — don't assume the existing pattern still applies.