Joins That Tell the Truth

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 sides
SELECT 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 predicted

State 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.

Prompt first: the constrained build

Build a monthly circuit metric from circuit_master (1,847 rows, one
per circuit, unique on circuit_id - verified) and circuit_faults
(3.24M rows, NOT unique on circuit_id+event_ts, real grain is
circuit-event-crew).

Requirements:
- aggregate faults to circuit-month BEFORE joining, to prevent fan-out
- event_ts is UTC; month boundaries are America/Chicago
- fault_duration is in SECONDS despite its name; name the output column
  so the unit is unambiguous
- a circuit with no telemetry must produce NULL, not 0

Before the SQL, tell me the row count you expect the result to have
and how you derived it. After the SQL, list every assumption you made
that my requirements did not settle.

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, 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.

Aggregate first, then join

The reliable structural fix is to collapse the many-side to your target grain before joining:

-- 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_count
FROM circuit_master m
LEFT 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 downstream
SUM(fault_duration) AS total_duration_seconds

Two 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 BASIS line for exactly this reason.

Nulls mean different things per column

SQL has one null. Your data has several distinct meanings wearing it:

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 faults
END AS fault_count

The 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.