BigQuery as the Agent's Structured Memory: Medallion Pipelines, CDC and Row-Level Security

Recall is not the same as truth

An enterprise agent needs two kinds of memory. One retrieves unstructured policies, notes, emails and contracts. The other resolves structured, transactional truth: balances, settlement dates and claim states. A balance is not a fuzzy semantic object. Retrieving a similar passage cannot substitute for querying its authoritative record.

BigQuery is a strong home for this second kind of memory in a Google Cloud estate. The reason is not simply that it is a database; AlloyDB or Cloud SQL could hold the same tables. The reason is the operating envelope: economical queries over very large event histories, point-in-time reconstruction, change-data-capture integration and controls that a risk function can inspect. Those controls include row-level security, column masking and Dataplex classification tags. The medallion pattern makes the arrangement legible. Bronze, silver and gold each carry a different contract about what “correct” means before the serving layer exposes data to agent tools.

The medallion pattern, restated for an agentic consumer

The medallion architecture predates generative AI. Bronze holds raw landed data, silver cleans and conforms it, and gold serves business-ready views. The pattern was designed for dashboards, scheduled reports and data-science notebooks. An agent is a different consumer, so the guarantees at each layer must change.

An analytics consumer may tolerate latency measured in hours. A nightly refresh can be adequate for a quarterly review. A relationship manager asking about a client's current position cannot accept an answer that is quietly a day stale, especially if a large withdrawal occurred two hours ago. Schema requirements differ too. An analyst can scan a wide table and choose among its columns. An agent tool needs a narrow, typed and well-documented interface because the schema forms part of the model's decision context. Eight clearly named fields are safer than eighty ambiguous ones.

The medallion pattern does not need to be abandoned. Its service levels need to be renegotiated against the agent's latency and schema requirements. This is a governance decision as well as an engineering one because the data platform team is accepting a tighter freshness commitment than a traditional analytics consumer requires.

Bronze, landing raw events without losing anything

The bronze contract should stay simple: land every event as it arrived and add only ingestion metadata. That metadata includes the arrival time, source-system identifier and a monotonic sequence number where the source provides one. Cleaning at this layer weakens bronze as a recovery point. If the raw record remains intact, silver and gold can be rebuilt after a downstream transformation defect.

For agentic memory, bronze must capture change events rather than only current-state snapshots. An account-balance update should land as a discrete row containing the old value, new value, change time and causal transaction. It should not overwrite a single current-balance row. This event history lets an investigator ask months later why the balance changed on a particular date. A snapshot-only pipeline cannot answer because it discarded the trail.

For sizing, assume forty million daily change events across account, transaction and case systems. Partition the bronze tables by ingestion date and cluster them by source system and account identifier. The corresponding storage estimate must be calculated from the institution's event size, compression, retention policy, region and current BigQuery price. A single dollar figure without those inputs creates false precision.

Silver, conformance and the agent-facing schema

Silver turns raw events into typed, deduplicated and business-conformed records. It should produce one canonical account_balance_history table rather than three subtly different source representations. Currency handling, null semantics and business keys must be consistent. A maintained identity table resolves local identifiers to the same canonical client. This is unglamorous work, but it deserves most of the pipeline's engineering effort. Every downstream tool inherits whatever ambiguity survives into silver.

Silver also needs a property that analytics pipelines often omit: explicit effective dates on every conformed record. A valid_from and valid_to pair lets a query ask what was true at a past time, not only what is true now. A model-risk reviewer will eventually ask whether an answer given on a particular date can be reconstructed from the data layer alone. Effective dating turns that question into a query. Current-state overwrites can make it impossible by destroying the earlier value.

Deduplication at silver deserves explicit tests. Change-data-capture streams often provide at-least-once delivery, so a retry can deliver the same event twice. A pipeline that does not deduplicate on a stable event identifier can double-count transactions. The defect may remain invisible until a client disputes a balance.

Gold, the serving layer agent tools actually call

Gold should consist of narrow, purpose-built views and materialised tables rather than a general analytics mart. Each object should back one or two agent tools. Examples include current_account_balance, case_status_summary and entitlement_check. Each needs a plain-language description because that description is shown to the model as part of the tool schema. A wide “client 360” table makes tool selection harder: the model must interpret the table column by column. A narrow schema reduces the number of plausible mistakes.

Materialisation strategy controls latency. A SQL view over silver is simple to build but recomputes work on every query. Its response time becomes difficult to predict as the underlying tables grow. For synchronously queried tools, an incrementally refreshed materialised view is the stronger default. In the worked sizing model, it turns a multi-second aggregation into a point lookup below 200 milliseconds. That target is an acceptance criterion to test, not a reported client result.

Change data capture in practice

Datastream can provide the bronze layer's freshness without a bespoke CDC pipeline. It reads the transaction logs of supported sources, including PostgreSQL-family, Oracle and MySQL systems, and lands committed changes in BigQuery. End-to-end latency depends on connector type, source load and downstream processing. CDC lag sets the upper bound on memory freshness. No optimisation in silver or gold can recover time already lost at ingestion.

CDC lag should be a first-class operational metric rather than something inferred from agent behaviour. A large source-system batch can saturate transaction-log throughput without producing a clean failure. Gold then becomes progressively stale until someone notices a discrepancy. A scheduled control should compare the latest event time in bronze with wall-clock time and alert against a table-specific threshold. This signal arrives much earlier than a client-facing error.

The worked failure scenario assumes a nightly settlement job briefly locks the table Datastream reads, pushing CDC lag to 47 minutes. A balance-lookup tool then answers from stale data and misses a transaction posted nine minutes earlier. The control response is an explicit as_of timestamp in the gold-layer view and a policy requiring the agent to disclose data older than two minutes. The timings are illustrative; the design principle is to turn silent staleness into a visible condition.

Row-level security and column-level masking

BigQuery row policies and Dataplex policy tags bring access enforcement into the data layer. Application filtering alone is weak when the downstream consumer is a model whose tool choice may be mistaken or adversarially influenced.

A practical row policy derives the calling service account's scope from the human user's entitlement. A relationship manager, for example, should see only the assigned client book. The shared table can then serve many managers while the database prevents an out-of-scope row from being returned. Column masking adds a second layer for sensitive fields. A national identifier may be available to a KYC-remediation identity but masked for a general servicing identity. A shared classification taxonomy lets the restriction travel with the data.

The database layer also changes the assurance task. A validation team can inspect IAM bindings, row policies and policy tags directly. It does not have to infer access control from every possible tool-calling path. Static configuration evidence does not replace behavioural security testing, but it gives the second line a stable object to review.

Cost control at the volumes agents actually generate

A servicing tool called on every conversational turn creates many small point lookups, not the few large aggregations typical of a dashboard. On-demand pricing can still work if each lookup scans little data. Poor partitioning can turn a trivial lookup into a multi-gigabyte scan repeated throughout the day. Partition by the time dimension used in queries and cluster by the entity identifier used for access. Then measure scanned bytes from actual tool traces.

Capacity pricing becomes a candidate once agent and analytics workloads are large enough to share a reservation. There is no universal crossover point. It depends on scanned bytes, clustering quality, concurrency, region, commitment terms and the amount of analytical work already present. The worked model tests a baseline reservation with autoscaling against the on-demand bill. The decision rule is the measured blended cost and p95 latency, recalculated as the estate grows.

Query caching is another lever. BigQuery caches identical results for a limited period, but parameterised tool calls rarely repeat verbatim across clients. A more useful cache can sit in the agent runtime and key a short-lived result to the exact tool parameters. Its time to live must be shorter than the data's own freshness contract. Measure the reduction in repeated calls and disable the cache for facts that cannot tolerate even brief staleness.

Data contracts and testing the pipeline as code

A medallion pipeline feeding agent tools deserves the same test discipline as the orchestration code above it. It also needs an artefact that analytics platforms often leave implicit: a data contract for each gold table. The contract specifies names, types and nullability; value constraints such as permitted status values, and the maximum acceptable freshness lag. Dataplex data-quality scans can evaluate these rules on a schedule. A violation should stop promotion and alert the owning team rather than reach a tool silently.

An analyst may notice an implausible number on a dashboard and pause. An agent has no equivalent instinct. If a contract violation admits an impossible balance or unknown status, the model may reason over it with the same fluency it applies to correct data. Automated contract enforcement is therefore the load-bearing control at this layer. Human review is too late for every synchronous lookup.

Transformation logic also needs unit tests against fixture data. The fixtures should cover unresolved source identities, out-of-order transactions and currency conversion at period boundaries. Run them in continuous integration before promoting a transformation change. The number of tests is less important than coverage of the failure modes exposed by production traces and reconciliations.

Failure modes

The most damaging failure mode is schema drift that silently breaks a tool contract. A source adds an enum value or renames a column without notice. Without explicit validation, gold may receive a null that hides a real state change or a string the tool schema does not recognize. Validate schemas and enums before writing to silver. A stalled pipeline is visible and repairable; silently wrong data is neither.

A second failure is treating materialised views as finished once built. Complex joins or aggregation can force more recomputation as data grows and degrade latency. Load-test gold views at projected volumes as well as current volumes. Repeat the test when query shape or source volume changes materially.

A third failure is organisational. A team changes a gold schema for one agent without checking the other consumers. Once several tools depend on a view, treat its schema like a public API: version it, announce deprecations and identify an owner who reviews breaking changes.

Worked example, client 360 memory for a servicing agent

The worked scenario is a retail and commercial bank whose servicing staff need one answer across balances, recent transactions, open cases and product holdings. The source estate includes a core banking platform, separate card processor, claims system and CRM. They do not share a canonical client identifier.

Three sources support direct CDC. The older claims platform arrives through a scheduled micro-batch, but lands in the same event-shaped bronze schema. Silver therefore does not need source-specific logic. A conformance job maps each local identifier to a canonical client key. The full mapping is rebuilt overnight and patched incrementally for newly onboarded clients.

Gold exposes five narrow views: current balance, recent transactions, open cases, product holdings and entitlement. Older transaction history sits behind a separate on-demand tool. Every business tool invokes the entitlement check before returning a row. Each view backs one ADK tool whose schema description uses the same language as analyst onboarding material.

Acceptance testing should measure end-to-end freshness, p95 lookup latency, entitlement leakage, identity coverage and reconciliation against the source systems. It should also test duplicate identities created by historical migrations. If a client maps to multiple active CRM records, the gold view should withhold the incomplete holding list and route the identity for repair. The worked design targets data below the two-minute disclosure threshold, but the bank must set that threshold from its own journey and risk appetite.

Temporal truth, corrections and restatement

An event history is useful only when its time semantics are explicit. Banking data usually carries at least three clocks. Business time records when an event took effect for the client. System time records when the source committed it. Pipeline time records when the event reached BigQuery. A single updated_at field collapses those distinctions and makes late corrections difficult to explain.

The silver layer should therefore preserve business-effective and system-recorded intervals. A correction does not erase the earlier record. It closes the earlier system-time interval and opens a corrected version with the same business-effective date. This bitemporal pattern supports two different questions: what the bank now believes happened, and what the servicing agent could reasonably have known when it answered.

This distinction matters after a back-dated fee correction or fraud reversal. A reviewer may ask whether the original answer was defective. The correct reconstruction uses the versions visible at the original answer time, not today's corrected value. If the answer was faithful to the then-current record, the issue belongs to source-data timeliness. If the correct record was available but ignored, the issue belongs to the agent path.

Restatement should trigger downstream work. The correction job identifies affected gold rows, invalidates matching cache entries and marks prior answers that relied on the superseded value. Material impact can create a review case. This is stronger than silently making the next answer correct because it identifies people who may still act on the earlier answer.

From source row to answer evidence

Lineage is often discussed at table level: gold view A depends on silver table B. An agent needs finer evidence. Each returned fact should carry a source key, source version, transformation version and as_of time. The tool response can keep this envelope separate from the concise value shown to the model. The runtime writes the envelope to the answer ledger and exposes only the fields needed for a citation or freshness disclosure.

This design prevents a common audit failure. A trace may show that the agent called get_balance, yet omit which version of the view supplied the value. The tool name proves activity, not provenance. A defensible claim needs the exact fact version behind it. Google Cloud data lineage can document table and job relationships. The application evidence envelope completes the final hop from the view row to the answer claim.

Evidence retention should follow the business record, not the conversational transcript by default. A full prompt may contain unnecessary personal data and retrieved text. The ledger can instead retain the claim identifier, tool parameters after approved redaction, source keys, policy decision, model and tool versions, timestamps and outcome. Investigators can then reconstruct the path while limiting uncontrolled duplication.

Operating the memory product

The structured-memory layer needs an owner who can negotiate both data and journey service levels. A central data team may operate pipelines, but it cannot decide whether five minutes of staleness is acceptable for a disputed-card journey. That decision belongs to the product and risk owners. Conversely, a journey team should not redefine canonical identities or currency semantics for one agent.

The operating model works best with two linked contracts. The data-product contract covers source completeness, conformance, lineage and retention. The agent-tool contract covers freshness, latency, entitlement, fallback and response schema. An incident can then be assigned to the violated contract rather than debated as a generic “AI issue.”

Monitor distributions, not averages. The p50 freshness may remain healthy while one source partition stalls. Break metrics down by source, entity cohort and tool. A small premium-client segment can otherwise breach its contract while the estate-level dashboard stays green. The error budget must be slice-aware whenever the client consequence is slice-specific.

The release process should also test replay. Take a bounded set of production-like events, run them through the proposed transformation and compare every gold value with the current version. A schema migration should be promoted only when unexplained differences are zero. Expected differences need an approved mapping and a list of affected tools.

Google documents scheduled and continuous quality controls through Knowledge Catalog data quality. The platform feature supplies measurements, not ownership. Every rule still needs a severity, response and accountable team.

Notes for practitioners

Do not blur vector retrieval and structured memory into one “agent memory” component. They solve different problems. A fact owned by a transactional system should reach the agent through a governed structured pipeline, not an approximate document search. Renegotiate freshness and schema contracts before onboarding the first agent tool; analytics-oriented service levels are usually too slow and too broad for reliable tool use.

Preserve the full change history in bronze and effective dates in silver from the outset. Retrofitting historical reconstruction after retaining only current snapshots is often impossible. Materialise synchronous gold views and load-test them at projected volumes. Enforce row policies and column masking in BigQuery with a classification taxonomy shared across the estate. That makes access control a configuration fact that reviewers can inspect, rather than a promise embedded in prompt and tool logic.

A memory acceptance matrix

Freshness is part of the answer, not a hidden pipeline metric. Every agent-facing fact should carry an as_of value and a declared source. The response should stop or qualify itself when either falls outside the journey's contract.

A null entitlement result is a denial, not an invitation to retry with broader scope. A stale value is an operational state, not a model-confidence problem. These distinctions keep deterministic data controls outside the model's discretion.

Platform references

• Google Cloud, BigQuery time travel for point-in-time access and retention limits.
• Google Cloud, Datastream for BigQuery for supported CDC behaviour and destination configuration.
• Google Cloud, Introduction to BigQuery row-level security.
• Google Cloud, Column-level access control.

Freshness, retention, supported sources and cost depend on the region and product configuration. The worked figures in this article are architecture assumptions, not substitutes for the current product documentation or a measured vertical slice.