Nate Swenson

Your API has no way to tell you the query was wrong

City Report · No. 077 · 10 min read ·v0.4.0

Shipped

city-report loads any US city’s Census data once, answers follow-up questions from that loaded context without touching the network again, and renders a self-contained HTML report. It covers 22 metrics across five sections, benchmarked against the state and the nation, using nothing but the Python standard library.

The interesting part isn’t the report. It’s that no query in it is built at runtime. Every figure comes from a pinned record in a manifest, and a test checks each record against the API’s own schema before anything runs. That design exists because of a specific failure mode worth knowing about: an API that responds to a wrong question with a right-looking answer.

See the trap first

Data USA publishes US Census data through tesseract, a ROLAP engine that serves data as OLAP cubes. You query it by naming a cube, the dimensions you want to break the data down by (drilldowns), and the numbers you want (measures). The API docs state the rule that matters here plainly: “measures will automatically be aggregated by the cube’s default aggregation function.”

Read that again with an adversarial eye. Whatever you don’t drill down on, it aggregates over. Silently. Ask the household income cube for a city without drilling the income bucket:

curl -s "https://api.datausa.io/tesseract/data.jsonrecords?cube=acs_yg_household_income_5&drilldowns=Place,Year&measures=Household+Income&include=Year:2023" \
  | python3 -c "
import sys, json
for r in json.load(sys.stdin)['data']:
    if r['Place'] == 'Minneapolis, MN':
        print(r['Place'], r['Year'], 'Household Income =', r['Household Income'])
"

Minneapolis, MN 2023 Household Income = 188944.0

A measure called “Household Income” returning 188944 for a large city looks like a median household income. It isn’t. Add the drilldown the first query left off and the same number falls out as a sum:

curl -s "https://api.datausa.io/tesseract/data.jsonrecords?cube=acs_yg_household_income_5&drilldowns=Place,Year,Household+Income+Bucket&measures=Household+Income&include=Year:2023" \
  | python3 -c "
import sys, json
rows = [r for r in json.load(sys.stdin)['data'] if r['Place'] == 'Minneapolis, MN']
print('buckets:', len(rows), ' sum of all buckets =', sum(r['Household Income'] for r in rows))
"

buckets: 16  sum of all buckets = 188944.0

188944 is the count of households in Minneapolis, added up across all 16 income brackets. The first query didn’t fail. It returned HTTP 200 and a number three orders of magnitude away from the thing its own measure name suggests, and nothing in the response says an aggregation happened. If you build a report on that, you publish a wrong number with total confidence.

The general shape: when an API infers part of your question from what you left out, a malformed query is indistinguishable from a correct one at the response layer. You can’t catch that with error handling, because there’s no error. You catch it by making the query itself something you can test.

Make the query a record, not a string

The fix starts by refusing to build query strings at call time. Each query becomes a frozen record that names its cube, its exact drilldowns, and its measure. Save this as metrics.py:

"""Pinned queries. Nothing here is built at runtime."""
from __future__ import annotations

from dataclasses import dataclass, field
from urllib.parse import urlencode

BASE = "https://api.datausa.io/tesseract"


@dataclass(frozen=True)
class Metric:
    key: str
    cube: str
    drilldowns: tuple[str, ...]
    measure: str
    #: True for a median/mean/index, which must never be aggregated across members.
    is_median: bool = False
    #: Dimensions deliberately summed over, each with a written reason.
    summed: dict[str, str] = field(default_factory=dict)

    def url(self, geo_level: str) -> str:
        """Dimension and measure names contain spaces; let urlencode handle them."""
        query = urlencode({
            "cube": self.cube,
            "drilldowns": ",".join((geo_level,) + self.drilldowns),
            "measures": self.measure,
        })
        return f"{BASE}/data.jsonrecords?{query}"


METRICS: tuple[Metric, ...] = (
    Metric(
        key="households_by_income",
        cube="acs_yg_household_income_5",
        drilldowns=("Year", "Household Income Bucket"),
        measure="Household Income",
    ),
    Metric(
        key="median_age",
        cube="acs_ygs_median_age_total_5",
        drilldowns=("Year", "Gender"),
        measure="Median Age",
        is_median=True,
    ),
)

Two fields carry the whole design. is_median marks measures where aggregation is not merely the wrong number but a meaningless operation; the average of three medians isn’t a statistic. summed is the escape hatch, and it’s deliberately awkward: you may leave a dimension un-drilled, but only by naming it and writing down why.

That distinction matters because summing isn’t always wrong. Census crosstabs partition their universe, so every household sits in exactly one income bracket and the sum across brackets is a legitimate marginal total. The problem in the example above isn’t double counting, it’s that the marginal total answers a different question than the measure name implies.

Record the schema, then guard against it

A pinned query is only checkable if you know what dimensions its cube actually has. Fetch that once and keep it on disk, so the check runs offline and for free. Save as record_schemas.py:

"""Record each pinned cube's schema to disk, so the guard test runs offline."""
from __future__ import annotations

import json
import urllib.request

from metrics import BASE, METRICS

OUT = "schemas.json"


def fetch_schema(cube: str) -> dict:
    with urllib.request.urlopen(f"{BASE}/cubes/{cube}", timeout=30) as resp:
        raw = json.load(resp)
    return {
        "dimensions": [
            {
                "name": dim["name"],
                "levels": sorted(
                    lvl["name"] for h in dim["hierarchies"] for lvl in h["levels"]
                ),
            }
            for dim in raw["dimensions"]
        ]
    }


def main() -> None:
    schemas = {m.cube: fetch_schema(m.cube) for m in METRICS}
    with open(OUT, "w", encoding="utf-8") as fh:
        json.dump(schemas, fh, indent=2, sort_keys=True)
    for cube, schema in schemas.items():
        dims = [d["name"] for d in schema["dimensions"]]
        print(f"{cube}: {', '.join(dims)}")


if __name__ == "__main__":
    main()

Run it:

python3 record_schemas.py

acs_yg_household_income_5: Geography, Year, Household Income Bucket
acs_ygs_median_age_total_5: Geography, Year, Gender

Now the guard. For each pinned query, work out which dimensions its drilldowns actually reach, and assert that nothing is left over. Geography is excluded because the caller supplies the geographic level at query time. Save as test_metrics.py:

"""The guard: no pinned query may leave a dimension silently aggregated."""
from __future__ import annotations

import json
import urllib.request

import pytest

from metrics import BASE, METRICS

with open("schemas.json", encoding="utf-8") as fh:
    SCHEMAS = json.load(fh)


def uncovered_dimensions(metric) -> set[str]:
    """Dimensions of the cube that this metric's drilldowns do not reach."""
    covered = set()
    for dim in SCHEMAS[metric.cube]["dimensions"]:
        if dim["name"] == "Geography":
            continue
        if set(metric.drilldowns) & set(dim["levels"]):
            covered.add(dim["name"])
    named = {d["name"] for d in SCHEMAS[metric.cube]["dimensions"]}
    return named - covered - {"Geography"}


def test_every_dimension_is_drilled_or_declared():
    for metric in METRICS:
        undeclared = uncovered_dimensions(metric) - set(metric.summed)
        assert not undeclared, (
            f"{metric.key}: {sorted(undeclared)} of {metric.cube} is neither "
            f"drilled nor declared in `summed`; the measure will be aggregated "
            f"across it silently"
        )


def test_medians_leave_nothing_uncovered():
    """Aggregating a median is meaningless, and the API will do it anyway."""
    for metric in METRICS:
        if not metric.is_median:
            continue
        missing = uncovered_dimensions(metric)
        assert not missing, (
            f"{metric.key} is a median but leaves {sorted(missing)} uncovered; "
            f"drill it and pin a member instead of aggregating"
        )


def test_declared_sums_carry_a_reason():
    """`summed` stays an escape hatch only while it is expensive to use."""
    for metric in METRICS:
        for dim, reason in metric.summed.items():
            assert len(reason) > 20, f"{metric.key}/{dim} needs a real reason"


@pytest.mark.live
@pytest.mark.parametrize("metric", METRICS, ids=lambda m: m.key)
def test_pinned_query_still_returns_rows(metric):
    """HTTP 200 is not evidence; count the rows."""
    with urllib.request.urlopen(metric.url("Nation"), timeout=30) as resp:
        payload = json.load(resp)
    assert payload["page"]["total"] > 0, f"{metric.key}: cube returned no rows"

The last test is a separate concern from the other three, and it’s the one people skip. The offline guard proves your query is well formed. It says nothing about whether the cube still has data in it. Mark it so it stays out of the normal run:

[pytest]
markers =
    live: hits the real API

Run it, then break it

The offline guard needs no network:

python3 -m pytest -q -m "not live"

...                                                                      [100%]
3 passed, 2 deselected in 0.01s

A passing test you’ve never seen fail isn’t worth much, so break one on purpose. Change median_age’s drilldowns from ("Year", "Gender") to ("Year",) and run it again:

E  AssertionError: median_age: ['Gender'] of acs_ygs_median_age_total_5 is neither
   drilled nor declared in `summed`; the measure will be aggregated across it silently

E  AssertionError: median_age is a median but leaves ['Gender'] uncovered; drill it
   and pin a member instead of aggregating

Both guards fire, and the message names the dimension. That’s the whole point: the failure tells you which axis is about to be quietly averaged, at the moment you introduce it, rather than three weeks later when someone questions a number on a report. In city-report, this test caught three real bugs while the manifest was being written, including a median age whose Gender dimension had been left loose.

Put the network check back in and run everything:

python3 -m pytest -q

.....                                                                    [100%]
5 passed in 0.50s

Gotchas

A cube that works today can return an empty 200 tomorrow, and recover on its own. When city-report’s field notes were written on 2026-07-28, acs_yg_total_population_5 returned page.total = 0 at every geography level, so population had to be sourced from the race cube instead. Checking that same cube while writing this post, one day later, it returned 354,055 rows at Place level. The outage was real and so is the recovery, which is exactly why the live contract test asserts on row counts rather than on status codes, and why a one-time manual verification is not a design. Symptom: a metric silently disappears from your output with no error anywhere. Escape: assert page.total > 0 per pinned query, on a schedule, and let CI tell you instead of a reader.

The docs’ own example can be the broken one. The cube in that outage is the one the Data USA API documentation uses in its worked example. Copying the example query verbatim during that window would have taught you the API was fine and your city had no population data. Symptom: your first integration test passes against the docs’ sample and fails against every real query you write. Escape: verify example queries against your own data before you build on them, and treat documentation as a starting hypothesis.

Not every dead cube is polite enough to return 200. acs_yg_housing_median_value_5, the cube that would publish median home value directly, returns HTTP 500 on every query, and still did while this post was written. That one at least announces itself. city-report interpolates the figure from the value-bucket histogram and labels it an estimate. Symptom: a single metric 5xx’s while every neighbouring metric is fine. Escape: when a measure has no working cube, derive it from one that works and say plainly in the output that it’s derived.

A one-year survey is a coverage cliff, not a precision setting. Census ACS 1-year estimates only cover areas with populations of 65,000 or more, while the 5-year estimates cover all areas. Build against the _1 cubes and you get something that works in every city you test with and fails in every small town. Symptom: your tool works perfectly for Minneapolis and returns nothing for Hawley. Escape: pick the 5-year cubes everywhere and accept the lag.

Encode your query parameters. The Metric.url method above uses urlencode for a reason. The first version of it built the query string by hand, which works right up until a drilldown name contains a space, and OLAP dimension names contain spaces constantly. Python’s HTTP client raises InvalidURL: URL can't contain control characters rather than sending it. Symptom: two of your pinned queries work and the one with a multi-word dimension throws before it reaches the network. Escape: never concatenate query strings, even for a two-parameter request.

Sources

Changelog

  • feat(city-report): Data USA city profile skill v0.4.0 (#93) (b474fae)