Joins That Tell the Truth
Updated
IntermediateWhat you'll learn
~20 min- Predict and detect fan-out before it silently multiplies a total
- Reconcile units and time zones across sources at the join, not after
- Choose null handling deliberately per column rather than inheriting SQL's defaults
Fan-out is the one that gets everybody
You have a circuit table with 1,847 rows, one per circuit. You join it to fault events. You aggregate. Your customer count is now 2.4 times too high, and nothing errored.
This is fan-out: joining a one-row-per-thing table to a many-rows-per-thing table multiplies the first table’s values by the match count. It is the single most common way a data product produces a confidently wrong number, and it is invisible unless you look for it, because every row involved is legitimate.
The habit that prevents it:
-- BEFORE the join, know both sidesSELECT COUNT(*), COUNT(DISTINCT circuit_id) FROM circuit_master;SELECT COUNT(*), COUNT(DISTINCT circuit_id) FROM circuit_faults;
-- AFTER the join, the row count should equal the count you expect-- from the many-side, not something in between that nobody predictedState the expected row count out loud before you run it. “This should return one row per fault event, so about 3.2 million.” If the result is 7.8 million, you have found fan-out on both sides — a many-to-many join, which is almost never what anyone intends.
It rarely announces itself in a total that is obviously wrong. It announces itself in a total that is plausibly wrong — customer counts inflated 15% because a handful of circuits have two rows in a dimension table that was supposed to be unique. Your profile in Lesson 2.2 is what tells you which tables are actually unique on the key you believe in.
Prompt first: the constrained build
Switch your workspace agent to Plan mode with your metric definition and profiling artifact, and ask it to plan the join before writing the final query (if you do not have live exports or files handy, ask it to generate a small Meridian practice sample from the lesson’s scenario first):
Using our metric definition and profile notes in this folder, plan the SQL build for the monthly circuit metric from circuit_master and circuit_faults. State the expected output row count first and flag any unsettled assumptions.Check that the plan or draft covers:
- Aggregating
circuit_faultsto circuit-month before joining tocircuit_master(1,847 rows, unique oncircuit_id) so the circuit-event-crew grain cannot fan out - Converting UTC
event_tstimestamps toAmerica/Chicagobefore truncating to month boundaries - Naming the duration output column explicitly in seconds (
total_duration_seconds) to fix the misleading source unit name - Preserving
NULL(rather than0) for circuits with no telemetry, plus a falsifiable row-count prediction and an explicit list of unsettled assumptions
If the draft uses COALESCE(fault_count, 0) blindly across the left join, follow up with: Keep circuits with no telemetry as NULL rather than zero, and list every join assumption you made.
Both bookends matter. The predicted row count is a falsifiable claim you can check in one query. The assumption list is where the agent surfaces the choices it made in silence: which JOIN type, what happens to circuits energized mid-month, and whether decommissioned circuits were excluded.
Stop and escalate when a table that should be unique on its key is not — that is the table owner’s defect, and it is not yours to dedupe silently, because a silent fix leaves every other consumer fanning out.
You LEFT JOIN circuits to monthly fault aggregates and wrap the result in COALESCE(fault_count, 0). What have you asserted about circuits with no matching fault rows?
Aggregate first, then join
The reliable structural fix is to collapse the many-side to your target grain before joining. Here is a reference SQL snippet showing that pre-aggregation pattern:
-- Instead of joining then aggregating (fan-out risk):WITH faults_by_circuit_month AS ( SELECT circuit_id, DATE_TRUNC('month', event_ts AT TIME ZONE 'America/Chicago') AS month, COUNT(*) AS fault_count, SUM(fault_duration_seconds) AS total_duration_seconds FROM circuit_faults GROUP BY 1, 2)SELECT m.circuit_id, f.month, m.customers_served, f.fault_countFROM circuit_master mLEFT JOIN faults_by_circuit_month f USING (circuit_id);Now both sides are one row per circuit-month. customers_served cannot be multiplied because there is nothing to multiply it by. This pattern costs you a CTE and removes an entire class of error.
Units, reconciled at the join
Lesson 2.2 found that fault_duration holds seconds despite the source documentation calling it minutes. The place to fix that is here, at the boundary, with the correction visible:
-- Named so the unit cannot be mistaken again downstreamSUM(fault_duration) AS total_duration_secondsTwo rules that pay for themselves:
- Put the unit in the column name.
total_duration_seconds,load_kw,distance_miles. Every downstream reader gets the unit for free, and nobody re-derives it from a catalog description that was wrong. - Convert once, at the earliest point where both sources meet. Converting in three downstream queries means one of them will eventually be missed.
Time zones shift a day, quietly
Your fault timestamps are UTC. Your month boundaries are local. A fault at 2026-03-01 02:00 UTC is 2026-02-28 20:00 in America/Chicago — February, not March.
Get this wrong and roughly 5 to 6 hours of every month’s events land in the wrong bucket. The monthly total is off by a fraction of a percent, which is small enough that nobody notices and large enough that your numbers never quite match the operational system’s.
DATE_TRUNC('month', event_ts AT TIME ZONE 'America/Chicago')Three things to hold onto:
- Convert, then truncate. Truncating in UTC and labeling the result as local is the error, and it looks identical to correct code.
- Daylight saving means some local days have 23 or 25 hours. An average-per-hour calculation across a transition day is wrong unless you account for it.
- Write the time zone into the definition — Module 1’s definition template has a
TIME BASISline for exactly this reason.
Nulls mean different things per column
SQL has one null. Your data has several distinct meanings wearing it:
| Column | Null means | Correct handling |
|---|---|---|
cause_code | Fault occurred, cause never recorded | Count the fault, exclude from cause breakdowns |
fault_duration | Telemetry failed | Count the fault, exclude from duration averages, and report how many were excluded |
| no row at all in faults | No faults that month — or no telemetry | These are not the same and must be distinguished |
That last row is the one from Module 1’s definition: a circuit with no telemetry must not be scored zero. In SQL terms, a LEFT JOIN producing null fault_count needs a deliberate decision, not a COALESCE(fault_count, 0) reflex:
CASE WHEN t.has_telemetry IS NOT TRUE THEN NULL -- not scored; status "no data" ELSE COALESCE(f.fault_count, 0) -- genuinely zero faultsEND AS fault_countThe COALESCE reflex is what produced the version of Meridian’s list where circuits with dead monitoring ranked healthiest.
Key takeaway
Fan-out is the most common source of a confidently wrong total, and the structural fix is to aggregate the many-side to your target grain before joining. Put units in column names and convert once, at the boundary where sources meet. Convert to local time before truncating, never after. And treat every null as a per-column decision: the COALESCE(x, 0) reflex is what turns “we did not measure” into “we measured zero,” which inverts a metric precisely for the cases that matter most. Lesson 3.2 makes this run more than once without doubling anything.