query-optimization.md §7 tells models to filter no-data before aggregating, which is correct for the arithmetic. It does not say that the same filter silently redefines the population being measured, and that missing values are rarely missing at random. That omission produced a confidently-wrong published answer in the ca-30x30 app; a partner caught it, not us.
The general rule that's missing
WHERE col IS NOT NULL does not select a subset of the question. It selects a subset of the data — and incomplete columns are usually incomplete for a reason that correlates with something: geography, feature type, source vintage, resolution, the region a partner happened to process first. So the surviving rows are a biased sample, and any percentage computed from them silently answers a different question than the one asked.
This is distinct from §7's concern. §7 stops a NaN from poisoning a SUM. The missing half is: having filtered, you no longer know what population you are describing, and the filter's bias is not visible in the result. Both halves are needed and only one is documented. §2's example at line 51 (WHERE l.lc_class IS NOT NULL) currently models the pattern with no caveat attached.
Worked example (measured, reproducible)
usgs-nhd-streams-by-order carries Strahler STREAMORDER joined from USGS's NHDFlowlineVAA. It is populated for 11.4% of flowlines nationally, because USGS computed it for only some subbasins. A model asked "what share of California's streams by order is conserved?" wrote structurally perfect SQL whose only flaw was WHERE STREAMORDER IS NOT NULL:
|
|
| California in-network flowline |
1,083,964 km |
| …with a usable stream order |
94,505 km (8.7%) |
| …of which HUC4 1801 (Klamath) |
92,393 km — 97.8% |
The model reported a statewide figure that was one basin, with GAP 3+4 at 42.9% against a statewide 25.5% — because that basin is heavily national forest. Nothing in the result hinted at it.
The missingness was structured on two axes at once, which is why a geography-only framing would be too narrow:
- Region: 15 of 22 HUC2 regions are at literally zero populated flowlines (millions of rows each, not rounding). Best-covered is HUC2 17 at 66.1%.
- Feature type: within a covered basin, coverage still varies by
FCODE — perennial 98.0%, intermittent 97.6%, ephemeral 94.0%, and coastline 0.3%. (The raw per-FCODE spread looks far larger — perennial 87.3% vs ephemeral 65.6% — but most of that is composition: ephemeral channels concentrate in the less-covered subwatersheds. I initially misread that as a feature-type effect, which is itself an argument for checking coverage against more than one grouping.)
Related, same family: absence encoded as a value
IS NOT NULL can also overstate coverage. In this dataset 1,652,617 rows carry STREAMORDER = 0, which is not a Strahler order. Counting non-null, HUC2 12 looks 96.7% covered; counting > 0 it is 0.0%. Two whole regions would pass a naive coverage audit while having no usable data at all. §7 already tells models to take sentinel codes from the STAC description — worth connecting the two: sentinels don't just break averages, they fake coverage.
Proposed addition to §7 (or a new §8)
Filtering no-data changes what you measured — say so.
WHERE col IS NOT NULL (or excluding sentinels) fixes the arithmetic but replaces the population. Missing values are rarely missing at random: incomplete columns are usually incomplete in patterns that track geography, feature type, or source vintage, so the rows that survive are a biased sample and any share computed from them describes that sample, not the whole.
Before using a partially-populated column as a filter, a class selector, or a denominator:
- Quantify coverage — what fraction of rows, and of the relevant measure (area, length, count), survive the filter?
- Check it against at least one independent grouping — a partition key, region, category, or date. Uniform coverage is safe to filter on; coverage that is 0% in some groups and 90% in others means the filter is a group selector wearing an attribute's clothes.
- Confirm how absence is encoded.
IS NOT NULL misses sentinel values (0, -9999, ''); take them from the dataset's STAC description and exclude them before measuring coverage.
- Report it. If coverage is materially incomplete, state the covered fraction and what it covers alongside the number. If coverage is concentrated in part of the study area, the honest answer is that the breakdown is unavailable for the whole — not a number with a caveat.
Done when
query-optimization.md carries the population/bias half of the no-data rule, including the coverage-by-grouping check and the sentinel-vs-NULL distinction, and §2's IS NOT NULL example either carries the caveat or stops modelling the bare pattern.
Runnable SQL
-- Coverage is not uniform: 15 of 22 regions at exactly 0.0%, best is 66.1%.
-- Also shows the sentinel faking coverage: HUC2 12 -> 96.7% non-null, 0.0% usable.
SELECT substr(REACHCODE,1,2) AS huc2, COUNT(*) n,
ROUND(100.0*COUNT(STREAMORDER)/COUNT(*),1) pct_notnull,
ROUND(100.0*COUNT(*) FILTER (WHERE STREAMORDER > 0)/COUNT(*),1) pct_usable
FROM read_parquet('s3://public-usgs-nhd/streams-by-order.parquet')
WHERE INNETWORK = 1 GROUP BY 1 ORDER BY 1;
-- The bias the filter introduces in one state: 97.8% of usable length is one basin.
WITH ca AS (SELECT DISTINCT h8, h0 FROM read_parquet('s3://public-ca30x30/ecoregion/hex/h0=*/data_0.parquet')),
nhd AS (
SELECT DISTINCT s.PERMANENT_IDENTIFIER pid, s.LENGTHKM len, s.STREAMORDER so, s.REACHCODE rc
FROM read_parquet('s3://public-usgs-nhd/streams-by-order/hex/h0=*/data_0.parquet') s
SEMI JOIN ca ON s.h8 = ca.h8 AND s.h0 = ca.h0 WHERE s.INNETWORK = 1
)
SELECT ROUND(SUM(len),0) ca_in_network_km,
ROUND(SUM(CASE WHEN so > 0 THEN len ELSE 0 END),0) usable_km,
ROUND(SUM(CASE WHEN so > 0 AND substr(rc,1,4)='1801' THEN len ELSE 0 END),0) usable_km_one_basin
FROM nhd; -- expect 1,083,964 / 94,505 / 92,393Context / related
The underlying data gap is data-workflows#518 (the ingest is faithful; USGS's own VAA table is sparse, and the STAC note misattributes the cause) with the replacement source at data-workflows#205. The app-layer guardrail is ca-30x30#111. This issue is only the general, cross-app guidance half — the app prompt can tell one agent about one dataset, but every consumer of every partially-populated column has this hazard, which is why it belongs here. Line-length metric guidance for the same dataset is in #354.
query-optimization.md§7 tells models to filter no-data before aggregating, which is correct for the arithmetic. It does not say that the same filter silently redefines the population being measured, and that missing values are rarely missing at random. That omission produced a confidently-wrong published answer in the ca-30x30 app; a partner caught it, not us.The general rule that's missing
WHERE col IS NOT NULLdoes not select a subset of the question. It selects a subset of the data — and incomplete columns are usually incomplete for a reason that correlates with something: geography, feature type, source vintage, resolution, the region a partner happened to process first. So the surviving rows are a biased sample, and any percentage computed from them silently answers a different question than the one asked.This is distinct from §7's concern. §7 stops a
NaNfrom poisoning aSUM. The missing half is: having filtered, you no longer know what population you are describing, and the filter's bias is not visible in the result. Both halves are needed and only one is documented. §2's example at line 51 (WHERE l.lc_class IS NOT NULL) currently models the pattern with no caveat attached.Worked example (measured, reproducible)
usgs-nhd-streams-by-ordercarries StrahlerSTREAMORDERjoined from USGS'sNHDFlowlineVAA. It is populated for 11.4% of flowlines nationally, because USGS computed it for only some subbasins. A model asked "what share of California's streams by order is conserved?" wrote structurally perfect SQL whose only flaw wasWHERE STREAMORDER IS NOT NULL:The model reported a statewide figure that was one basin, with GAP 3+4 at 42.9% against a statewide 25.5% — because that basin is heavily national forest. Nothing in the result hinted at it.
The missingness was structured on two axes at once, which is why a geography-only framing would be too narrow:
FCODE— perennial 98.0%, intermittent 97.6%, ephemeral 94.0%, and coastline 0.3%. (The raw per-FCODE spread looks far larger — perennial 87.3% vs ephemeral 65.6% — but most of that is composition: ephemeral channels concentrate in the less-covered subwatersheds. I initially misread that as a feature-type effect, which is itself an argument for checking coverage against more than one grouping.)Related, same family: absence encoded as a value
IS NOT NULLcan also overstate coverage. In this dataset 1,652,617 rows carrySTREAMORDER = 0, which is not a Strahler order. Counting non-null, HUC2 12 looks 96.7% covered; counting> 0it is 0.0%. Two whole regions would pass a naive coverage audit while having no usable data at all. §7 already tells models to take sentinel codes from the STAC description — worth connecting the two: sentinels don't just break averages, they fake coverage.Proposed addition to §7 (or a new §8)
Done when
query-optimization.mdcarries the population/bias half of the no-data rule, including the coverage-by-grouping check and the sentinel-vs-NULL distinction, and §2'sIS NOT NULLexample either carries the caveat or stops modelling the bare pattern.Runnable SQL
Context / related
The underlying data gap is data-workflows#518 (the ingest is faithful; USGS's own VAA table is sparse, and the STAC note misattributes the cause) with the replacement source at data-workflows#205. The app-layer guardrail is ca-30x30#111. This issue is only the general, cross-app guidance half — the app prompt can tell one agent about one dataset, but every consumer of every partially-populated column has this hazard, which is why it belongs here. Line-length metric guidance for the same dataset is in #354.