The query & join engine: turn a stakeholder ask into an auditable query, and two files into one trustworthy dataset

Your team already has the query-and-learn and clean-and-join playbooks — the worked recipes for getting one query explained clause by clause until you can defend it, and combining two messy files into one dataset without a row going missing. Each is a good move, done once, on one file. This module is the layer that turns those separate moves into a production engine: queries that tie back to a number your team already trusts, and joins that prove — to the row and the dirham — that nothing got lost between the raw files and the report.

It’s Module 2 of the certifiable Data & Analysts track, and it inherits everything you built in Module 1. Your dataset-profile.md and metrics.md aren’t background reading here — they’re the contract this module’s queries are graded against: every WHERE clause should match a definition you already wrote down, not one reinvented mid-query. M1 mastered what your data contains and what your numbers mean; M2 builds the engine that answers real stakeholder requests on top of that foundation — and assesses the engine, not one lucky correct answer.

A query is not just an answer — it’s a claim. “Active customers by plan tier: 9,240” is a sentence someone repeats in a leadership meeting without ever rereading the SQL behind it. The free playbooks teach you to write a query you can explain and a join you can trust once. This module is where that becomes the standard every query meets, every time — because the stakeholder who asked the question can’t tell a defensible number from a lucky one, and it isn’t their job to.

The engine, not the one-off query

Most analysts use Claude as a faster query-writer: paste a question, get SQL, run it, move on. That’s not a system — it’s a faster way to ship a number nobody re-derives. The query that answered last month’s ask gets copy-pasted for this month’s, the join that “worked” gets rerun without rechecking whether the source files still line up, and the first time someone challenges a figure, nobody can walk back to why it’s right.

A query-and-join engine is two named moves that run the same way every time, on every real request:

• Query — pin down the fuzzy question, write the SQL, walk through every clause in plain English, and reconcile the result against a number metrics.md (or a second source) already trusts.
• Join — when an answer needs two files, standardize the keys, join on the right id, surface every row that doesn’t match, and reconcile the combined total back to both inputs before anyone builds a report on it.

Each move has one job and one output, which is what lets you name what’s broken when a number looks off: a query problem (the definition drifted, the reconciliation was skipped) or a join problem (a key didn’t standardize cleanly, an unmatched row got dropped instead of flagged). A system you can diagnose beats a flood of one-off SQL you can only hope is still right.

You run both moves in the chat. Open your data folder in Claude Desktop, approve each read in the “Ask permissions” prompt, and work one move at a time — no terminal needed on the main path.

Query-and-learn — a number a stranger could audit

The playbook gives you the steps: state the question, get the query and the walkthrough, reconcile, stress-test. Mastery is the judgment that makes the result something a stranger — a new analyst, an auditor, a skeptical VP — could pick up and verify without calling you.

• The definition comes from metrics.md, not memory. If a metric already has a definition, the query’s WHERE clause should be that definition, verbatim, not a fresh guess at what “active” means this time. A query that reinvents a definition metrics.md already settled is how two analysts get two numbers for the same metric — the exact drift Module 1 exists to prevent.
• Walk the clause, don’t just paste it. If a reviewer can’t reproduce what a WHERE filter or a GROUP BY does from your walkthrough alone, the query isn’t explained yet — it’s just typed. Plain English, one line per clause, assuming the reader isn’t a SQL expert.
• Reconcile to a number people already trust — not to itself. A query that only checks its own subtotal proves nothing. Tie the result to metrics.md’s number, a prior report, or an independently-computed total, and say explicitly what you compared it to and whether it matched. This is the RECONCILE gate, and it’s non-negotiable before a result leaves the chat.
• Profile a new column before you trust it in a WHERE clause. Even inside a file Module 1 already profiled, a new request can pull in a column nobody’s looked at yet — a different table, a new dimension. Read real values before you filter on it. PROFILE FIRST applies every time a query touches something unprofiled, not just on day one.

Clean-and-join — proving it didn’t lose a row or a dirham

A bad join is the most dangerous kind of data error, because the output looks exactly as clean as a correct one. The discipline that separates a trustworthy join from a quietly broken one is treating the unmatched rows as the deliverable, not as noise to clean up before anyone sees the file.

• Standardize before you compare, not after. Mismatched casing, prefixes, and padding are why “the join lost half my rows” — show the before/after on real values so you can see exactly what changed, and catch anything that goes blank or collides once cleaned.
• The unmatched rows are the point. Orders with no matching customer, customers with no orders this period — list them, count them, and don’t drop a single one silently. A join that “just worked” and quietly dropped 250 rows is worse than no join at all.
• Reconcile counts and dollars — both, not just one. Matched plus orphan should equal the original row count, exactly. The financial total should tie out between the raw input and the joined output, to the cent. A row-count match with a dollar mismatch (or the reverse) means something broke during the join, not after it.
• Which row wins is a named decision, not whatever Claude picked. When two records plausibly describe the same company under two different ids, or two files disagree, that’s a governance call, not a default. Flag it as “needs review” and route it to the person who owns the answer — never auto-merge a fuzzy match. This is the same DATA SAFETY discipline as the rest of the track: identity-bearing decisions get a named human, not a silent algorithm.

A note on Arabic data

Most of this module’s discipline is language-agnostic — a WHERE clause and a join key behave the same in Arabic-majority data as anywhere else. Two things genuinely change. First, when a join key is a name rather than an id — matching on company or contact name because no shared id exists — Arabic text adds real variants a naive string match misses (أ vs ا, optional diacritics, more than one defensible transliteration of the same name into Latin script); normalize before comparing, and route anything uncertain to the same “needs review” list a fuzzy English match would get, never a silent merge. Second, if this query or join feeds a bilingual stakeholder readout, it inherits Module 1’s standard regardless: numbers stay Western numerals, and both the English and Arabic narrative cite the same reconciled figures — never two separately-built totals that can quietly disagree.

Your assignment

Build the query-and-join engine for one real request — your own data (recommended: the output becomes a query and a dataset your team actually reuses) or the sample brand Mizan, the GCC bookkeeping SaaS whose Business Analyst, Maya Haddad, runs throughout this track. Everything here inherits the foundation files from M1; if you don’t have dataset-profile.md and metrics.md yet, do Module 1 first. Open your data folder in Claude Desktop, approve each read in the “Ask permissions” prompt, and work in the chat — no terminal needed.

Module 2 deliverable — the query & join engine

Inherits from M1: dataset-profile.md + metrics.md

1. query.sql + walkthrough.md   (the query-and-learn output)
   - the precise question, with every fuzzy term pinned down before any
     SQL — citing metrics.md's exact logic for any metric already defined
   - the query, with a one-line comment above each clause
   - a plain-English walkthrough a non-SQL reader could follow and verify
   - reconciliation: the result ties to a number from metrics.md or a
     second source — name the source and state the match (or the gap,
     investigated and explained, not waved away)

2. joined-dataset.csv + join-notes.md   (the clean-and-join output)
   - key standardization shown: 5 before/after examples, with anything
     that goes blank or collides after cleaning flagged
   - a match_status column on every row (matched / orphan / no_match)
   - reconciliation: matched + orphan equals the original row count for
     the file you joined onto; a financial or count total ties out
     between the raw input and the joined output
   - any ambiguous match (same entity, two ids; a likely duplicate)
     flagged as "needs review" — never silently merged

PII: identity-bearing columns — personal names, emails, account-level
data tied to one individual — stay in the workspace. What you submit is
the SQL, the walkthrough, the joined file, and the reconciled counts —
not a raw export.

The Foundation toolkit holds the metrics.md and dataset-profile.md templates this module’s queries and joins build directly on top of.

How it’s graded — the rubric

This is the part the free playbooks don’t have, and the part that makes the credential mean something. Your two deliverables are scored against five criteria. Each is meets / nearly / not yet, and a “nearly” on any one is a revise, not a pass.

Query & join engine rubric

1. The query reconciles to a number that already exists
   The result ties to metrics.md's figure or a stated second source — not
   just to its own subtotal. A mismatch is investigated and explained,
   never silently accepted or rounded away.

2. The walkthrough is real, not a paraphrase
   A non-SQL reader could reproduce what each clause does from the
   walkthrough alone — the WHERE filter, the GROUP BY, and any date
   logic each get a plain-English line, not a one-word gloss.

3. Every join key was standardized and shown, not assumed
   Before/after examples prove the transformation didn't mangle a real
   value. Any key that goes blank or collides after cleaning is named,
   not silently dropped.

4. Unmatched rows are surfaced, not hidden
   Orphan rows and no-match rows are both listed with verified counts.
   Matched + orphan equals the original row count for the file with the
   match_status column — exactly, not approximately.

5. Both totals reconcile, and ambiguous matches are flagged
   Row counts and a financial or quantity total both tie out between
   input and joined output. Any ambiguous case (two ids, a likely
   duplicate) is a "needs review" line — not a default Claude picked,
   and not a default the analyst picked alone without naming it.

The discipline is deliberately what a skeptical data lead would demand: a query that only checks itself, or a join that quietly drops 250 rows, fails silently on everything built on top of it — so it has to be caught here, not in a board meeting three weeks later.

The bar, shown — a worked model answer (Mizan)

You don’t have to guess what “meets” looks like. Here are passing excerpts for the sample brand — these are Maya Haddad’s actual files, built for two real requests in the same week. Yours doesn’t need to look identical; it needs to clear the same bar.

active-customers-by-plan.sql + walkthrough.md
(Maya Haddad, for Nadia Al-Sayed's churn dashboard — the active-customers
denominator side of the brief from Module 1; the country cut for the same
dashboard joins this against the customers table, the clean-and-join half
of this same module.)

Question, pinned down before any SQL: "Active customers by plan, trailing
6 months." Active means metrics.md's exact logic (status = 'active' AND
plan != 'trial'). Trailing 6 months means Oct 2025 through Mar 2026,
snapshotted at the LAST DAY of each month — matching how MRR is already
measured, so the two metrics stay comparable month to month.

SELECT
  DATE_TRUNC('month', snapshot_date)::date AS month,  -- one row per month
  plan,                                                -- one row per plan
  COUNT(DISTINCT account_id) AS active_customers       -- accounts, not rows
FROM subscription_monthly_snapshots                    -- the history table;
                                                         -- see note below
WHERE status = 'active'                                -- metrics.md's exact
  AND plan <> 'trial'                                  -- Active Customers logic
  AND snapshot_date IN ('2025-10-31','2025-11-30','2025-12-31',
                         '2026-01-31','2026-02-28','2026-03-31')
GROUP BY 1, 2
ORDER BY 1, 2;

Walkthrough:
  WHERE status = 'active' AND plan <> 'trial' — exactly metrics.md's Active
    Customers logic, not a reinvented filter. A busy trial isn't a customer
    yet, however active the usage looks.
  subscription_monthly_snapshots, not subscriptions — PROFILE FIRST catch:
    the live `subscriptions` table metrics.md's instant logic queries holds
    today's state only, no history. This is a separate table the warehouse
    populates each month-end specifically for trend queries like this one —
    same business logic, different table, because a trend needs history a
    live table doesn't keep. Profiled it before trusting it: same columns,
    same definitions, one snapshot row per account per month-end.
  snapshot_date IN (six explicit dates) — not a BETWEEN range, because this
    table holds one row per account per day; a range would count every day
    in the window, not just the six month-end snapshots metrics.md uses for
    "active as of."
  GROUP BY month, plan — one row per plan per month, so an account that
    upgraded mid-period appears once per month, under whichever plan was
    active on that month's snapshot date — never counted under both.
  COUNT(DISTINCT account_id) — counts each account once even if it carries
    more than one subscription line (a seat add-on, for example), matching
    Active Customers as accounts, not line items.

Result (trailing 6 months):
  month        starter   pro    business   total
  2025-10-31     5,580   2,520      610     8,710
  2025-11-30     5,650   2,560      620     8,830
  2025-12-31     5,730   2,600      630     8,960
  2026-01-31     5,790   2,620      640     9,050
  2026-02-28     5,850   2,640      660     9,150
  2026-03-31     5,920   2,650      670     9,240

Reconciliation: March's total of 9,240 matches the Active Customers figure
metrics.md already reports for the month — same logic, independently
recomputed from the historical snapshot table instead of the live table
the rollup normally reads. The two sources agreeing is itself a check.

Stress-tested: an account that upgraded Starter → Pro mid-March shows once,
under Pro, on the 3/31 snapshot — not double-counted across both plans,
matching metrics.md's stated edge case for mid-period plan changes
("counted once, under the new plan").

join-notes.md — customers.csv + orders.csv
(Maya Haddad, for Youssef Hamdan's AR / revenue cross-check, Q1 2026)

Files, raw:
  customers.csv   9,640 rows, 9,640 distinct customer_id
                  (format: "MZ-00482" — prefixed, zero-padded)
  orders.csv      28,460 rows (AR invoice lines, Jan-Mar 2026),
                  9,510 distinct account_id
                  (format: "482" — bare digits, no prefix, no padding)
Estimated overlap before cleaning: ~9,400 of the 9,510 order-side accounts
look like they'll match a customer row once formats align.

Key standardization (5 of the transformations, before/after):
  MZ-00482  -> 482     strip "MZ-" prefix and leading zeros
  MZ-00071  -> 71      same rule
  MZ-09003  -> 9003    same rule
  mz-00482  -> 482     2 rows use lowercase "mz-" — normalized the same
                        way; flagged for Youssef as a likely two-intake-
                        path issue in the CRM, not touched further here
  MZ-00000  -> BLANK   4 rows; placeholder test accounts left over from
                        the 2025 billing migration — excluded from the
                        join, not silently kept as a real customer

Actual overlap after standardizing: 9,450 distinct accounts match (60 more
than the pre-cleaning estimate — hidden by the casing and padding above).

Join: LEFT JOIN orders onto customers on the standardized key, one row
per order, match_status on every row.
  Matched order rows:              28,210
  Orphan order rows (no match):       250  -> trace to 60 distinct
                                              account_ids; see flag 1 below
  Customers with 0 orders this Q:     190  (paused or trial-converted
                                            accounts not yet billed —
                                            expected, not a defect)

Reconciliation:
  Rows:     28,210 matched + 250 orphan = 28,460 -- equals orders.csv exactly.
  Accounts: 9,450 matched + 60 orphan   = 9,510  -- equals orders.csv's
                                                      distinct account_id exactly.
  Dollars:  sum(order_amount) in orders.csv         = AED 1,498,650.00
            sum(order_amount) in joined-dataset.csv = AED 1,498,650.00
            Ties to the cent. No order amount dropped or duplicated.

Flags — needs review, never auto-merged:
  1. NAJD LOGISTICS (customer_id MZ-00482) — 64 of the 250 orphan rows sit
     under account_id 1188, a legacy billing id from before the 2025 CRM
     migration that was never relinked. Without this flag, 64 orders from
     a real, current customer read as "orphan." Routed to Youssef to
     confirm before relinking — merging changes which AR-aging bucket
     those 64 invoices count against.
  2. AL HOSN TRADING — two rows in customers.csv: MZ-03114
     ("ops@alhosntrading.ae") and MZ-03212 ("Ops@AlHosnTrading.ae") — same
     company, same domain, different casing, entered by two sales reps
     eight months apart. Both matched their own orders correctly, so the
     join isn't broken — but kept as two separate rows and flagged rather
     than merged. Auto-merging a near-duplicate on a name match is exactly
     the call Module 1 reserves for a human; merging the wrong way would
     understate one of the two ids' AR balance in the cross-check.

joined-dataset.csv — excerpt (5 of 28,650 rows: 28,460 order rows
[matched + orphan] plus 190 zero-order customer rows; header + data)
  customer_id,company,order_id,order_amount,order_date,match_status
  482,Najd Logistics,ORD-55012,4250.00,2026-01-14,matched
  ,UNMATCHED-1188,ORD-44210,3100.00,2025-09-02,orphan [see flag 1]
  3114,Al Hosn Trading,ORD-56210,9800.00,2026-03-02,matched [see flag 2]
  3212,Al Hosn Trading,ORD-56344,2100.00,2026-03-09,matched [see flag 2]
  7780,,,,,no_orders

What you’ve proven — and what’s next

Clear the rubric and you’ve proven something the free playbooks alone can’t certify: that you can turn a stakeholder’s vague ask into a query a stranger could audit, and two messy files into one dataset reconciled to the row and the dirham. That’s the Query & Join Engine stage of “Certified Data & Analysts with Claude.”

From here the track turns a trustworthy engine into a full analytics operation, each module assessed the same way:

• Module 3 — quality & root cause: auditing a dataset before it feeds something that matters, and finding out why a number moved without guessing.
• Module 4 — experiments & charts: reading an A/B test honestly under pressure to ship, and turning a CSV into a chart that survives a second look.
• Module 5 — the reporting system: building the recurring report that comes out the same way every month, then the capstone — Insight-in-a-Box, the full investigation taken from raw export to certificate.

First, make this reusable: the commented .sql file and the standardization rules behind the join are the permanent artifacts. Save the query as a saved prompt or a /mau-style custom command and the cleaning rules as a /merge skill (see the playbooks’ Power Track variation) so next month’s version runs the same way without re-deriving it. And if you’re rolling this across a team, the operating guide is the data-safety and verification layer that goes underneath the whole system.