The medallion architecture has a default shape that most of us reach for without thinking. Bronze is raw rows, landed as-is. Silver is those rows cleaned and conformed. Gold is business aggregates. You pull the data, you store it, you query your own copy.
When I started building Data-Citizen, an agentic layer over NYC Open Data, I reached for the same shape. Then I measured something, and the whole design inverted.
280 bytes from 21.9 million rows#
NYC Open Data runs on Socrata, which exposes every dataset through an API with server-side filtering and aggregation (SoQL). I ran a within_polygon filter over the 311 service-request dataset, about 21.9 million rows. It returned 280 bytes in 2.4 seconds.
The portal did the work and sent back the answer, not the data. That is the defining property of a source you should not copy. A bronze layer assumes the source is a firehose you have to land somewhere before you can use it. Socrata is not a firehose. It is already a queryable engine, and a very large one.
So the rule became pushdown by default:
| Query shape | Where it runs |
|---|---|
| One dataset, filter and aggregate | pushed to Socrata |
| One dataset, large result | pushed, then paged |
| Cross-dataset join on a shared key | a SODA 3 join on Socrata |
| Two datasets compared by region | each side aggregated, lined up by key |
| Joins the API cannot express | a DuckDB box (provisioned, not public yet) |
And the silver layer holds no source rows at all. It holds derived, expensive-to-compute facts about datasets: what exists, what can be asked of it, whether it is any good, and what it joins to. The raw portal is the bronze layer, and it stays where it is.
What the layer actually holds#
| Table | Rows | Answers |
|---|---|---|
datasets | 2,401 | what exists |
dataset_columns | 56,335 | what can be asked: every column typed and profiled |
quality_scores | 2,400 | whether it is worth asking: five dimensions and a grade |
dataset_relationships | 17,434 | what it joins to, and on which column |
dataset_geo | 496 mappable | whether it can be drawn, and how |
dataset_embeddings | 2,401 | what it is about |
That is roughly 61,000 derived facts standing in front of about six billion source rows that are never copied.
The inclusion test#
A layer without a rule for what belongs in it turns into a junk drawer. I wrote down a three-part test. A fact belongs only if it is:
- Expensive to derive. It costs a crawl, a sample, an all-pairs comparison or a model call, not one query.
- Stable relative to query time. It changes daily at most, so a cached answer is not a lie.
- Wanted by more than one consumer. Otherwise it is that caller's cache, not a layer.
The counter-test is the one I actually use: if Socrata can answer it with a predicate in a couple of seconds, it does not belong. The analytics page groups 311 complaints by borough over 21.9 million rows live, rather than storing borough counts. Materialising those would buy nothing and start going stale immediately.
Embeddings were the interesting test case, because "vectors" sounds like exactly the kind of thing that would violate the rule. It passes. A dataset's vector is derived from its name, description, tags, column names and sample values, all of which the catalog already holds. It is a fact about a dataset, like a quality score. And it answers a question the upstream cannot: Socrata's discovery API matches keywords and has no notion of "about the same thing in different words". Push down what the source expresses well; materialise what it cannot express at all.
Why this shape is better here#
Nothing goes stale, because nothing is copied. A mirror of NYC on a two-vCPU box is stale by lunchtime, and no schedule fixes that. Here, the answer to a data question is computed against the live portal at query time. What is cached is metadata, and metadata is allowed to be a day old. A dataset's column types do not change between breakfast and lunch.
Cost is flat in the size of the data. The layer keeps about 60,000 small rows whether the portal holds six billion rows or sixty. The entire middleware tier is one small VPS.
It makes a public query API tractable. This is the least obvious benefit and probably the most valuable. Because dataset_columns knows every column's real name and type, the planner can allowlist identifiers and quote literals before a query is built. Only the catalog's own spelling of a column is ever emitted, never the caller's string. Users compose questions in a UI without writing SoQL, and the app never accepts arbitrary query text. You cannot do that from a catalog that only knows dataset titles.
It degrades instead of failing. If the database is down, pushdown still answers, and the catalog falls back to Socrata's own discovery API. A bronze architecture has the opposite property: when the warehouse is down there is no answer, because the warehouse was the data.
What it costs#
I do not trust a design document that only lists advantages, so here are the trade-offs.
- You inherit the upstream's availability and rate limits. If Socrata is slow, you are slow, and there is no local copy to fall back to. An unfiltered group-by over 22 million rows blows the 50-second budget, and is supposed to.
- Quality scores are estimates. They come from a 500-row sample, not a full scan. A full scan to grade a dataset would cost more than every query anyone asks of it, but a score is a signal, not a census.
- Vocabulary gaps bite silently. Knowing a column exists is not knowing what values it holds. A filter for community board
'104 MANHATTAN'returns zero rows when the stored value is'04 MANHATTAN', and zero rows reads exactly like a quiet neighbourhood. Filter values in the composer autocomplete from observed data for that reason. - There is no history. The layer cannot answer "what did this dataset say last March", because it never kept last March.
The failure mode this codebase is built against#
Not breakage. Inertness that reads as success.
It has happened twice. The nightly quality-scoring pass picked its batch by popularity, so it re-scored the same 300 datasets every night. About 4,200 operations moved coverage by three datasets, and every run reported scored: 300, status: ok. Coverage sat at 13% for fourteen nights before I noticed.
Then two new layers (column search and published endpoints) shipped to production against tables nobody had created. The column search answered 200 {"count":0}, which is also exactly what a healthy, empty layer answers.
Three countermeasures came out of that:
- Batches select by staleness, never popularity. That is not a preference. It is the only order in which coverage converges.
- One list of expected tables, and a missing table raises. The route returns a 503 naming the command that creates it, instead of an empty result. The list used to live in three places, and three separate commits added tables to the schema and to none of them.
- Every pass writes its counters to a runs table. A pass whose progress is not visible cannot be seen to be stuck, and before this, the only record was in container logs.
The general rule I took from it: a stored answer must carry its provenance. Every published endpoint exposes the exact SoQL that produced its number, so you can paste it into the portal and get the same figures. A cached figure you cannot re-derive is indistinguishable from a wrong one.
Where the cost actually lives#
One more lesson, because it was expensive. Quality scoring is one of the few paths where rows genuinely must cross the wire: about 600 KB of sample per dataset. Running 300 of those a night through a serverless function put the Vercel team at 310% of its data transfer allowance and paused every project on it.
The lesson was not that sampling is wrong. It was that the handful of row-moving paths are exactly where the cost lives, so they belong on a box you rent by the month, not a function you rent by the second. The scoring moved to the VPS, and the front end only ever handles answers measured in kilobytes.
The agent#
The first consumer of the layer is an agent that answers questions about the city. It has five tools: search datasets, get a dataset's schema, run a query, assess quality, and find related datasets. It runs on NVIDIA NIM's free tier, which is capable and also brittle across a multi-step tool chain.
I measured it on five repeats of the same question. The natural chain (search, then read the schema, then write a query) got the right answer once in five. Putting a shortlist of common datasets with their key columns into the system prompt, so a typical question becomes a single query call, got it right five times in five. Everything else I tried was secondary:
- Pre-ranking aggregate results (
1. BRONX = 278956) so the model cannot pair a label from one row with a count from another. - Accepting
groupandorderas aliases for the parameter names the model kept getting wrong. - Retracting a premature "I cannot determine that" when the model never actually ran a query.
- Backing off properly on rate limits.
Model choice was empirical, and single-turn tests lied. One model looked best on a single tool call and fell apart mid-conversation, emitting tool calls as plain text. Test the multi-turn shape, not the first call.
The analyst mode, which answers questions about the catalog (what is stale, what is undocumented, which agencies have the lowest grades), writes real SQL against the silver layer. An allowlist cannot cover that, because the whole point is joins and CTEs nobody anticipated. So nothing inspects the SQL. The database does the work: a dedicated login whose only role is read-only, a read-only transaction, a statement timeout, and a row cap. An earlier version ran through a more privileged role, and agent SQL could set its way back out of the restricted one. A login with nowhere else to go was the fix. The definitions the model must use ("stale" means not updated in a year, an unscored dataset is unscored rather than bad) are written down in named views with comments, because text-to-SQL fails by inventing a plausible definition and running it successfully.
What is still open#
The interface is finished and callable. Anyone can query the layer without a key:
What has not happened yet is a consumer that is not this project. That, not the next feature, is the open question. A layer is only a layer once something else depends on it.