Business Data Layer for Agents
The governed data layer the business agents answer from — replacing a reporting stack that ran off one contractor's laptop.
- dashboards migrated to parity with the tool being replaced
- 19dashboards migrated to parity with the tool being replaced
- serving tables after pruning to what is actually consumed
- 32 → 12serving tables after pruning to what is actually consumed
- production environments consolidated into one governed lake
- 4production environments consolidated into one governed lake
- external sources unified behind a single queryable layer
- 10+external sources unified behind a single queryable layer
Agents serving support, customer success, sales, and the executive office needed one trustworthy source for business questions. I replaced a vendor reporting tool and the local notebooks feeding it with a self-hosted platform: a consolidated lake, a modelled analytics database, row-level access control, and an interface exposed over the tool protocol so agents query the same governed data humans see in dashboards.
The problem
The reporting pipeline ran on a contractor's local machine — notebooks pulling from production databases, transforming locally, pushing to a hosted vendor tool. It was unautomated, a data availability risk, and expensive per seat. More importantly for the agent work, there was no queryable business data layer at all: agents could reason about product and code, but not about accounts, support load, or usage, because that data only existed inside dashboards.
Constraints
- Deployments in different regions used colliding integer organization identifiers, so no natural single tenant key existed across environments.
- The standard internal database proxy supported neither the relational store nor the search cluster the extraction needed.
- Access control had to reach row level — teams see their own accounts, not everyone's — because the same layer serves agents used across functions.
- Parity with the tool being replaced was the acceptance bar, so a partial migration was not a viable end state.
Decisions
What I chose, why, and what I turned down to get there.
A composite tenant key
Region-qualified keys resolved the collision between deployments that independently issued the same integer identifiers. This table is built before every other pipeline, because every downstream join depends on identity being unambiguous. Getting identity wrong in a warehouse is not a bug you find in one query — it is wrong answers everywhere, quietly.
A conventional relational store, with the condition for changing it written down
Chosen over two column-oriented alternatives as the right fit for this data mix and volume, with an explicit threshold recorded: revisit if snapshot facts pass roughly fifty million rows. A technology choice that names the condition under which it becomes wrong is a decision; one that does not is a preference.
Considered and rejected
- Two column-oriented analytical engines — better at scales this data has not reached, and more operational surface to own today
Prune the schema to what dashboards actually consume
The design started over-scoped at more than thirty tables and was cut to twelve, with every remaining column required to trace to a surface that reads it. Unused warehouse columns are not free — they are ETL that can break, documentation that goes stale, and joins someone will eventually trust by mistake.
Considered and rejected
- The original larger schema — comprehensive, and most of it fed nothing
Bypass the standard internal proxy deliberately
The conventional path did not support two of the required sources, so extraction uses direct clients with explicit pool management instead. Deviating from a convention is fine when the reason is documented and specific; the failure mode to avoid is deviating silently and leaving the next person to rediscover why.
Gate loads on a manifest
Processing only begins once a manifest is present, so a partially written batch cannot be read as complete. Partial loads are the worst class of data bug: they do not error, they just produce numbers that are slightly wrong and get reported.
Why this belongs to the agent work
Exposed over the tool protocol, this became the data source behind the support, customer success, sales, and executive-office agents. It is the reason those agents can answer a question about an account rather than only about the product. Retrieval gave agents product knowledge; this gave them business context, under the same access controls a human would face.
- Row-level security plus dashboard-level access control means an agent inherits the caller's scope rather than querying with unrestricted access.
- The same modelled tables serve both dashboards and agents, so a human and an agent asking the same question get the same answer — which is what makes the agent's answer auditable.
Shape of the pipeline
Per-environment extracts plus global sources land in a consolidated lake through cross-account replication, then a scheduled pipeline models them into an analytics database — identity first, then per-surface loads, then derived views — with dashboards and the tool interface reading only from the modelled layer. Development runs against a local storage emulator with a single configuration swap to production.
Stack
- Python
- PostgreSQL
- Apache Superset
- AWS S3
- FastAPI
- Docker
- Jenkins
- Model Context Protocol
What was mine
The architecture, schema design, ETL, access-control model, and the tool interface are mine, delivered end to end. The dashboards it replaced were originally built by an external contractor.