# Querying this data without fooling yourself

Seven recipes, one per trap that has actually produced a confidently wrong answer — each of
these fooled a careful reader before it was documented. The queries are DuckDB, runnable
verbatim (`INSTALL httpfs` once for the remote URLs), and every snippet is executed with pinned
results in this repo's test suite, so if the data changes shape the docs fail loudly instead of
rotting.

This file is the *mechanics* companion; `/data/MEASURES.md` is the *semantics* one (what each
measure means and what it cannot support). Column-by-column reference:
`/data/schemas/assessments_columns.json` and `/data/schemas/mcap.schema.json`. What a missing
year *means*: `/data/schemas/assessments_coverage.json` (including `known_bad_slices` — slices
that exist but must not be used naively). Warehouse domain shapes + ready-made pivots:
`/data/schemas/warehouse_domains.json`. Change history: `/data/schemas/CHANGELOG.md`.

Prefer `school_year.parquet` (one row per school × year, headline measures pre-joined — recipe
7) for cross-school questions, and `assessments.parquet` (typed, one row per result) over the
raw warehouse whenever it has what you need — every trap below except #1 is milder there.

---

## 1. The warehouse is EAV — pivot by `record_id`, and coalesce the missing 2019 metric

**Trap:** `mcps_consolidated.parquet` melts each source row into sibling rows (one per metric)
sharing a `record_id`. Filtering `metric='Proficient Pct'` alone gives you values with no
identity; joining identity naively double-counts. And the obvious pivot silently drops a
vintage: **all 1,349 of the 2019 MCAP records carry no `Student Group` metric at all** (MSDE
published no subgroup breakdowns that year), so `HAVING grp='All Students'` without a coalesce
returns 2021–2026 and quietly loses 2019.

```sql
-- recipe: eav-pivot
SELECT year,
       max(CASE WHEN metric='Assessment'     THEN value_text END) AS assessment,
       coalesce(max(CASE WHEN metric='Student Group' THEN value_text END),
                'All Students') AS grp,        -- 2019 records omit this metric entirely
       max(CASE WHEN metric='Tested Count'   THEN value END) AS tested,
       max(CASE WHEN metric='Proficient Pct' THEN value END) AS pct
FROM read_parquet('https://mocoparents.org/data/mcps_consolidated.parquet')
WHERE source='msde' AND domain='mcap' AND level='district'
GROUP BY record_id, year
HAVING assessment='Algebra 1' AND grp='All Students'
ORDER BY year
```

Returns seven years, 2019 included: tested 18,702 (2019) then 12,147 / 12,531 / 12,731 /
13,325 / 11,793 / 13,122. (And note what the 2019 number itself means — see the closing
coverage note: 2019 is PARCC-era administration; don't pool it into trends.)

## 2. `suppressed = false` throws away the most informative number in the math data

**Trap:** suppression is not one thing. Every suppressed row carries a `bound`: `<=5%` and
`>=95%` are *informative* — the value truly lies in that range (the district Math 8 proficiency
floor, `<=5%` on ~5,000 tested students, holds in every published year and exists **only** on
suppressed rows); `suppressed` is withheld outright; `suppressed:inferred` is reserved for
cells never fetched because their marginals already proved them suppressed. Filtering
`suppressed = false` discards the floors and ceilings along with the noise.

```sql
-- recipe: suppression-populations
SELECT bound, source, count(*) AS rows
FROM read_parquet('https://mocoparents.org/data/assessments.parquet')
WHERE suppressed
GROUP BY bound, source ORDER BY bound, source
```

| bound | source | rows |
|---|---|---|
| `<=5%` | bulk | 3,044 |
| `<=5%` | chart_api | 12,019 |
| `>=95%` | bulk | 83 |
| `>=95%` | chart_api | 740 |
| `suppressed` | bulk | 13,048 |
| `suppressed` | chart_api | 113,380 |

When you need point values, filter `proficient_pct IS NOT NULL`; keep bounded rows for floors,
ceilings, and trends — recipe 5 shows the bound arithmetic in action.

## 3. `grade != 'All'` is not "per-grade" — spans are 5× a single grade

**Trap:** the original consumer-reported bug. MSDE publishes multi-grade spans (`3-5`, `3-8`,
`6-8`) alongside single grades, and `'All'` is a string sentinel, not NULL. Three rows tell the
whole story:

```sql
-- recipe: grade-span-all
SELECT assessment, grade, tested_count
FROM read_parquet('https://mocoparents.org/data/assessments.parquet')
WHERE level='district' AND year=2026 AND student_group='All Students'
  AND NOT is_alt AND source='bulk'
  AND assessment IN ('ELA 8', 'ELA 3-8', 'ELA All')
ORDER BY tested_count
```

ELA 8 = **11,425** tested; ELA 3-8 = **67,979**; ELA All = **80,494**. To select one grade,
match `grade` against `^[0-9]+$` or an exact value — never `!= 'All'`.

## 4. Which "middle school math" number do you mean?

**Trap:** the one a journalist needs most and is least likely to know they need. `Math 6-8`
counts only students taking grade-level math — every accelerated student (about half of MCPS
middle schoolers, taking Algebra 1 or Geometry instead) is excluded, so it is a *selected
residual*, not "middle school math."

```sql
-- recipe: math-residual
SELECT assessment, proficient_pct
FROM read_parquet('https://mocoparents.org/data/assessments.parquet')
WHERE level='district' AND year=2026 AND student_group='All Students'
  AND NOT is_alt AND source='bulk' AND assessment IN ('Math 6-8', 'Math All')
```

`Math 6-8` = **21.9%**; `Math All` = **36.9%**. Same district, same year. Quote the first as
"MCPS middle school math" and you have measured who takes which test, not who can do math.

## 5. Course assessments need a school-type join — and dropping suppressed schools drops the worst performers

**Trap:** two traps, actually. Algebra 1 is taken by accelerated middle schoolers AND by high
schoolers who reached the course later, so the district pooled rate mixes two very different
populations — split by school type. But the *obvious* split, filtering
`proficient_count IS NOT NULL`, answers a different question than it appears to: six high
schools (1,099 tested — Seneca Valley, Richard Montgomery, Blake, Magruder, Damascus,
Northwood) sit at the `<=5%` floor, so excluding them removes exactly the worst performers and
returns **11.3%** — the rate *among schools that publish*, 1–2pp above the true HS rate. This
is recipe 2's lesson resurfacing: bounded rows are information. Pin the bracket instead —
`SUM` ignores their NULL `proficient_count` (lower bound: floor schools' passers = 0), and the
upper bound grants them their full 5%:

```sql
-- recipe: school-type-split
SELECT s.school_type,
       sum(a.tested_count) AS tested,
       round(100.0 * sum(a.proficient_count) / sum(a.tested_count), 1) AS pct_lo,
       round(100.0 * (sum(a.proficient_count)
             + 0.05 * sum(CASE WHEN a.bound = '<=5%' THEN a.tested_count ELSE 0 END))
             / sum(a.tested_count), 1) AS pct_hi
FROM read_parquet('https://mocoparents.org/data/assessments.parquet') a
JOIN read_parquet('https://mocoparents.org/data/schools.parquet') s USING (school_code_str)
WHERE a.level='school' AND a.assessment='Algebra 1' AND a.year=2026
  AND a.student_group='All Students' AND NOT a.is_alt AND a.source='bulk'
  AND a.tested_count IS NOT NULL
GROUP BY s.school_type ORDER BY tested DESC
```

2026: MS **49.1%** (6,877 tested; no suppressed MS, so the bounds coincide) vs HS
**9.3–10.2%** (6,123 tested). Join gotcha: both published parquets store `school_code_str`
zero-padded (`'051'`), so they join directly — but the raw scrape archives (`*.jsonl`) store
codes un-padded (`'51'`); pad before joining those, or you silently drop schools.

## 6. 90% of rows carry no counts — volume-weighting silently uses 10%

**Trap:** `chart_api` rows (84% of the table — all school × subgroup detail for 2022+) have
**zero** numeric `tested_count`, because MSDE's chart endpoint publishes rates only; and a
third of the `bulk` rows are suppressed with no count either. Any volume-weighted computation
runs on the 10% of rows that carry counts, whether you meant that or not.

```sql
-- recipe: counts-by-source
SELECT source, count(*) AS rows, count(tested_count) AS rows_with_counts
FROM read_parquet('https://mocoparents.org/data/assessments.parquet')
GROUP BY source
```

bulk: 42,669 rows, 27,306 with counts. chart_api: 228,060 rows, **0** with counts — so 27,306
of 270,729 rows (10.1%) can carry a weight. If your aggregate needs weights, restrict to
`source='bulk'` explicitly and say so.

## 7. `school_year` is the start-here table — and its nulls are not zeros

**Not a trap so much as the exit from the first six**: `school_year.parquet` (CSV twin ~230 KB,
fits an LLM context whole) is one row per school × year with the headline measures pre-joined,
so cross-school questions stop requiring the pivots above. Two rules keep it honest. A null
means *not published for that school/year*, never zero — `accel_pct` in particular is NULL
where its Math-8 basis is absent or suppressed (the underlying convention would otherwise
report an exact-looking 100.0). And acceleration is *placement*, not performance:

```sql
-- recipe: school-year-accel
SELECT school_name, accel_pct, math_all_pct, farms_pct
FROM read_parquet('https://mocoparents.org/data/school_year.parquet')
WHERE school_type = 'MS' AND year = 2025 AND accel_pct IS NOT NULL
ORDER BY accel_pct DESC
LIMIT 5
```

| school_name | accel_pct | math_all_pct | farms_pct |
|---|---|---|---|
| Hoover MS | 93.3 | 55.4 | 11.8 |
| White Oak MS | 89.8 | 7.4 | 72.2 |
| Cabin John MS | 87.6 | 54.6 | 12.4 |
| Frost MS | 83.7 | 57.1 | 16.1 |
| Hallie Wells MS | 82.9 | 43.8 | 18.5 |

Row 2 is the lesson: White Oak placed 259 of 275 8th graders in Algebra 1 (5.4% proficient) —
near-total acceleration, single-digit proficiency. Ranking schools by `accel_pct` (or any raw
outcome) without `farms_pct` and a proficiency column alongside mostly recovers demographics
(accel vs FARMS r ≈ −0.7 to −0.85 by year). Definitions and the full do-not-conclude list:
`/data/MEASURES.md`; per-column coverage (which columns exist for which years/school types):
`/data/schemas/school_year_columns.json`.

---

Also load `/data/schemas/assessments_coverage.json` before trending anything: it flags the
slices that will mislead you even with perfect SQL (2021 ALT race splits are misclassified at
source; 2021 bundled two test administrations; the 2023–2026 science swings are statewide
assessment-level events, not instruction; 2019 is a different testing regime).

Remote-querying note: this host serves `Cache-Control: max-age=600`, so during a republish a
client can pair a cached parquet footer with freshly published row groups and get a baffling
read error. It's rare and transient — retry, or verify you got a coherent snapshot against the
`sha256` for each file in `/data/manifest.json` (its `path` values are repo-relative: strip the
leading `public/` to get the served path, so `public/data/msde.parquet` is served at
`/data/msde.parquet`).