An AI agent for the 2021 Australian Census.
Ask the Census anything in plain English and get a sourced, charted, verifiable answer. Built end-to-end to keep my hands on the implementation reality of the agentic systems I increasingly advise on.
absai.com.au is a working AI agent over the entire 2021 Australian Bureau of Statistics Census — all five profiles, 22 GB, 409 tables, around 17,000 columns semantically indexed. You ask a question in plain English (“which state has the highest median age?”, “how multicultural is Marrickville?”) and it answers with an editorial sentence, a chart, the underlying data, and a citation back to the exact ABS table. It’s free, no paywall, civic-tech — live since May 2026 and still in active development as I keep extending what it can answer.
Two reasons. First, reps. The fastest way to understand agentic data products is to build a real one end-to-end — schema, ingestion, natural-language-to-SQL, a production frontend, a public deploy. Each layer surfaces tradeoffs you can’t get from reading docs. As a CTO advising on these systems, strategy is sharper when you’ve shipped something recently. The first version took ten days and about 150 commits; it’s had a lot of attention since.
Second, the data deserved it. The ABS publishes excellent DataPacks and a TableBuilder UI — but neither is “ask in plain English.” That gap is exactly where a well-built agent earns its keep, provided every answer stays sourced and verifiable rather than a confident guess.
The retrieval is two-stage: shortlist the relevant tables by keyword coverage, then run pgvector cosine similarity over column-description embeddings to pick the ~30 most relevant columns for the question. A hand-curated semantic layer pins the canonical measures so the model doesn’t drift to near-synonyms. Gemini 3.5 flash writes the SQL; a deterministic Python chart builder picks the visual; a structured response schema carries the answer, data, chart and citation to the frontend as one typed contract.
It runs across three cloud regions because that’s what the platforms allow: the agent on Vertex AI Agent Engine in Melbourne, the database on Supabase in Tokyo, and Gemini routed globally. A one-command in-place redeploy keeps the agent’s resource id stable, so the frontend’s configuration never has to change.
A question in plain English
/api/chat — JWT-verified, rate-limited, streamed
call_postgres_agent — deterministic Python orchestration
postgres_nl2sql — semantic layer YAML + pgvector cosine
run_postgres_query — read-only, then verified before it's shown
census_2021 — 409 tables, immutable
metadata — columns + embeddings (retrieval index)
Text-to-SQL over 17,000 columns is the hard part, and the failure mode is nasty: a query that runs and returns rows can still be answering a different question than the one that was asked. Nothing about it looks broken. So most of the interesting engineering sits between “the model wrote SQL” and “the user sees a number”.
Three layers do that work. A verifierruns deterministic checks after the SQL executes, aimed at the exact ways this went wrong in production — a “top 10” that came back with one row, ABS residual categories like “No usual address” polluting a ranking, a percentage rendered as 18,253% by a double-scaling bug, a “median” question answered with a column that isn’t a median. Repairs happen in Python; only a genuine shape violation triggers one targeted regeneration. Answer grounding then checks the headline against the verified data — every number the sentence states has to appear in the result, or the sentence is dropped. And an answer-first clarifierreplaced a 56% clarification rate: instead of asking “which did you mean?”, it answers with a sensible default and says so — “I’ve read Sydney as Greater Sydney; switch to the council area?” It saves a flat refusal for questions the Census genuinely can’t answer, like voting or crime.
The benchmark is 38 questions with hand-verified canonical SQL, and it went 33 → 36 over that work, with a fresh sample of 75 unseen geographies scoring 73. Worth calibrating against something real: published text-to-SQL results from Snowflake, LinkedIn and the Swiss statistics office land between 45% and 53%. Every deploy is coupled to a fresh green benchmark run. The binding constraint now is speed, not correctness — a median answer takes about 26 seconds, and that’s the next thing to fix.
Deterministic Python beats LlmAgents for orchestration. The biggest accuracy win in the whole project was a refactor that removed an LLM. A wrapper agent was paraphrasing the user's question on its way to the SQL layer — and getting it wrong often enough to score 1-in-3 on a consistency probe. Replacing it with plain Python that forwards the verbatim question took the same probe to 5-in-5. Let LLMs be creative where creativity matters; let code be deterministic everywhere else.
A hand-curated semantic layer beats better embeddings. pgvector retrieval gets you ~85% of the way to the right column. The last 15% — the canonical measures like median age or average household size — keeps drifting to near-synonyms whose text descriptions are almost identical. A small YAML file pinning ~47 measures to their exact column, matched by alias, fixed it. Maybe 30 minutes to author a measure; pays back the first time the right answer pops out instead of a plausible wrong one.
An evaluation that flatters you is worse than none. My factuality judge once reported 86% — off six of the twenty questions it was asked to grade. It had quietly given up on the other fourteen and scored the rest. It was also grading the agent's “which did you mean?” clarifications as if they were answers. Both bugs pointed the same way: toward a number I wanted to see. The judge now fails a run on coverage when it can't confidently score more than 30% of the questions, and it refuses to grade a clarification at all. The score dropped. It started being useful.
A version range in a dependency file killed every turn in production. A redeploy silently broke the whole agent. The deployment wheel asked for google-adk>=1.5.0, the fresh cloud environment resolved that to the newest 2.x, and 2.x reads a field on the agent object that the 1.x-built artefact doesn't have. Zero streamed text on every single question — while the health check stayed green, because it only probed session creation, never an actual turn. Twenty-five minutes to find. The two rules that came out of it: pin the exact versions you built against, and make the health check exercise the thing users do.
The response schema is the contract. After the third “let me regex this out of the streamed prose” hack, I made the agent's response a real Pydantic schema, hand-mirrored in TypeScript on the client. Every UI iteration became “add a field” instead of “find the right pattern in the text.” And when the model occasionally streams nothing, the structured payload still drives the chart, the data table and the citation — the user sees a partial answer, not a blank one.
Error and clarification aren't “answers with missing data.” A single enum — answer | clarification | error — drives three distinct card layouts on the frontend. Before that, greetings and failures rendered with the full data scaffold (chart slot, table, citation row) all empty, and looked like broken answers. The smallest schema change paid back the most.
Serverless lifecycle is a primary design constraint. Three production bugs traced to the same wrong assumption: that a Vercel function is alive whenever you want it to be. It isn't — it lives only as long as something consumes its response. The rules that stuck: wrap every stream write in a guard that survives a closed connection, drain the upstream agent to completion regardless of the client, and await every database write that matters before the handler returns. Never fire-and-forget.
RLS and GRANT are a pair, always. Postgres checks role-level GRANTs before row-level security policies even run, and applies the SELECT policy to an INSERT…RETURNING. Two debugging sessions went to tables that had correct RLS but no GRANT, surfacing as cryptic “permission denied” and “new row violates policy” errors. Every new-table migration now ships enable-RLS, the policies, and the grant as one template.