When one of your customers moves to a higher plan partway through a billing period, several decisions come with the change. Is the first period prorated or charged in full? Does the contract term restart? Does the churn report count a retained customer, or one cancellation and one new sale? If the customer downgrades a few weeks later, does the original discount come back? Each answer is a commercial decision, and each one changes the revenue finance reports and the bill the customer receives.
Cases like the downgrade after an upgrade usually turn up once real customers start doing them, and surfacing the sequences a specification didn't cover is part of the engineering work. How the data platform handles them decides what your analysts can trust. As more organisations put agents to work on their data, it also decides what an agent can explain and what it can safely do.
In short: much of the risk in putting agents on your data sits in the data platform, alongside the model. Agents need the history of your business rules, corrections that are attributed when they're captured, and limits on what they can change. A staged readiness bar (read, supervised write, then write within limits) gives a way to report progress dataset by dataset, and a handful of measures, such as time to explain a variance and restatements after close, show whether the work is paying off.
An agent gets it wrong
Take an illustrative case. A customer contacts support asking why their bill went up, and an AI agent working for the support team looks it up.
It reads last month's invoice total and the current price table, which holds this month's prices and no history of when they changed, and concludes the customer was overcharged. They weren't: a price change took effect between the two invoices. The agent apologises and issues a credit. The credit is larger than the agent's approval threshold, so it issues two smaller credits instead. Neither credit says which product line it belongs to, so both land in a catch-all bucket in revenue reporting. The customer's message also included a pasted line of text that read like an instruction, and the agent followed it, adding a note to the account that the customer was owed a further refund.
Most of what made that sequence costly comes from the platform: no rule history, no attribution at capture, an approval rule that looks at one credit at a time, and free text passed to the agent unscreened. The rest comes from what the agent was allowed to do at all. Each is a design decision, and the first is who decides what the rules are.
Who owns the rules
Business definitions belong to the business. Finance, the commercial teams, and product each have a view on what counts as revenue or an active customer, and settling who has the final say is often the hardest part of the work. Around that decision, analysts work with the business to write definitions down and find where they conflict, analytics engineers turn agreed definitions into models and tests, and data engineers own ingestion, history, and the guarantees the platform makes: whether a definition can be represented in the data, enforced when events arrive, and rebuilt later. The lines vary, and in smaller teams one person may cover all three. Gaps tend to appear where each role assumes another has a case covered, and making sure none goes unowned is part of the engineering job.
One practice that helps is a set of worked examples, agreed before a rule change goes live. The business describes the change. The analyst, usually the person closest to how the rule will be used, works with the engineers to write out a handful of customers' event sequences with the figures the business expects at each step: a customer who upgrades and then downgrades, a customer whose activity straddles the change date, a record that arrives late. The engineers turn those into tests the pipeline has to pass. Everyone works from the same examples, which is usually where disagreements about a rule come out, well before a customer or an auditor finds them.
History is part of the rule
Business rules rarely stay fixed. Prices change, a tier is added, the definition of an active customer is tightened. When a rule changes, someone has to decide whether it applies only from now on or to the past as well. Google Cloud's description of the medallion pattern gives the case for reprocessing: if finance changes how it calculates profit, old data can be re-run to match the new rule (Google Cloud, n.d.e). That fits when the goal is comparability across years. Where the old rule is what customers were actually billed under, a rebuild would describe a past that never happened. Both cases come up in most businesses, so the choice belongs in the rule's metadata, recorded when the rule changes.
In practice that usually means rules live in versioned reference tables with effective dates, joined to events on the date each event happened, with the version of the transformation code recorded alongside the output. A rebuild then applies the rules that were in force at the time, unless the rule is marked to apply retrospectively. That's the history the support agent in the example was missing.
There's a second kind of history as well. Fowler distinguishes actual time, when something was true, from record time, when the business learned it (Fowler, 2021). A usage record that arrives a week late was true on the day it happened and recorded a week later. A price change backdated to the start of the month changes what was true for weeks that have already been invoiced. Keeping both times lets you answer what was actually true on a given date and what you believed when you acted on it, which is the question an auditor or a customer dispute usually turns on.
The familiar warehouse tools cover part of this. Kimball's type 2 approach adds a new row for each change to an attribute, with effective and expiration dates and a current-row indicator (Kimball Group, n.d.), so a report can ask what plan a customer was on when an event happened. That tracks one timeline. If a correction later changes when a plan actually started and the existing type 2 rows are edited to match, what the business believed before the correction is lost, so the record-time view needs its own columns, such as when each row was recorded and when it was superseded. Platform time travel doesn't fill the gap either. BigQuery's time travel window runs from two to seven days, with seven by default (Google Cloud, n.d.a). It's a recovery feature, and record history needs to be modelled explicitly.
Where the lakehouse decisions are made
Google Cloud describes the bronze layer of a medallion lakehouse as the landing zone that keeps incoming data unchanged, silver as the cleaned and standardised source of truth, and gold as refined data tailored to specific business needs (Google Cloud, n.d.e). Within that pattern there are real choices about where each kind of rule goes, and teams make them differently.
Bronze keeps raw data so it can be reprocessed when rules change. That's a reason to keep source records complete, including fields that aren't modelled yet, within limits. Personal data in bronze needs a stated purpose, a retention period, and an erasure process that reaches every layer, and data minimisation applies to it as much as anywhere else. Erasure and history pull against each other. Common approaches, to be confirmed with your privacy counsel, are to keep direct identifiers out of history tables and join on a pseudonymous key instead (pseudonymised data is still personal data, so it still needs a retention rule), or to encrypt personal fields with a key per person and delete the key when the person is erased. Deciding what bronze keeps, for how long, and why, is an early decision that depends on understanding the business.
For rules, a split we often see working is to put anything that changes what an event is into silver, and metric logic into gold. Linking a refund to the sale it reverses, merging duplicate accounts, and assigning the rule version that applied are silver concerns, because every downstream table needs the same answer. How revenue is aggregated for a board report is gold, published with its definition. Some teams keep more business logic in gold, which works well when each definition has one owner. Where several teams build on the same events, pushing event-level rules down into silver tends to reduce the number of slightly different answers.
Reprocessing also has a cost. Rebuilding years of history every time a rule changes can be expensive, so most pipelines process incrementally, with a lookback window that picks up late-arriving records, and keep full rebuilds for rule changes marked as retrospective. Backfills need to be idempotent, so running one twice doesn't double-count. Much of the medallion work we deliver is written in SQL, with whichever orchestration your team already runs, and most of the effort goes into the decisions above.
Corrections and adjustments
Manual corrections show how a decision made at capture affects everything downstream. Every industry has its own version: fee reversals in banking, seller and refund adjustments on a marketplace, virtual-currency grants in a game, goodwill credits and billing corrections almost everywhere. Each one is usually recorded correctly against the account, so the balance is right. What can be missing is the attribute downstream reporting needs, such as the product line, channel, or cost centre the correction belongs to. If an adjustment doesn't reference the record it corrects, reporting has to infer where it belongs, or the amount lands in a catch-all bucket without a clear owner.
The capture screen belongs to the team that owns the source application, usually billing or CRM. The data team's route is a data contract agreed with that team: the fields every adjustment carries, the allowed reason codes, which attribution options each reason code permits, and the approvals required. Attribution needs explicit options because there often isn't one obviously correct answer. A correction might belong to the unit of the original record, the unit that owns the account, the unit that raised it, or a central bucket the business has chosen deliberately. Which option applies to which reason code is a decision for finance and the analyst, written into the contract. In our experience these contracts hold best when a business owner, often in finance, sponsors them, because finance owns the figures the adjustments move.
The source system enforces the parts of the contract that have to hold before money moves: approvals, limits, and idempotency. An approval rule that looks at each credit on its own can be sidestepped by splitting one credit into two, which is what the agent in the example did. A cumulative rule is designed to close that gap: a credit needs approval if it, added to the credits issued or pending on the same account over the previous 24 hours by anyone, would reach the threshold, with the check and the write handled one at a time per account so parallel requests can't both slip under it. Daily limits per creator, set per currency or converted to one reporting currency, cap the total. An idempotency key on every write stops a retried request from issuing the same credit twice; for agents, the key works best when it's derived from the case and the type of action, because an agent that retries by re-planning will often generate a new request, sometimes with a different amount.
The data engineer's part is to check every adjustment again as it lands. An adjustment the billing system has already issued is a fact, so the lakehouse loads it whatever the check finds, and silver keeps reconciling to billing. What the check produces is a set of flags. Rows with attribution flags are reported as unattributed until they're fixed, rather than being assigned to a unit by inference, and every flag is counted.
In plain terms, Listing 1 flags an adjustment if its amount is zero; its reason code is unknown or doesn't allow the attribution chosen; its sign doesn't match the reason; it's held centrally but names a unit, or, if not central, has a missing or unknown unit; it's attributed to an original record that doesn't exist or belongs to another account or unit; its currency isn't covered; a large amount lacks an approval from an authorised approver other than its creator; or its effective date fell in a period finance had already closed when it was recorded. The approver comes from the approval system, joined on the adjustment's ID and version, so an approval for one version doesn't carry over to a larger correction, and the comparison with the creator flags an adjustment approved by whoever raised it.
Python
from dataclasses import dataclass from datetime import date from enum import Enum class Basis(Enum): ORIGINATING_RECORD = "originating_record" # unit of the record being corrected ACCOUNT_OWNER = "account_owner" # unit that owns the account RAISED_BY = "raised_by" # unit that raised the adjustment CENTRAL = "central" # held centrally, by decision @dataclass(frozen=True) class Adjustment: adjustment_id: str version: int # a correction arrives as a new version account_key: str amount_minor: int # signed, in minor units of `currency` currency: str # ISO 4217 code, e.g. "EUR" reason_code: str basis: Basis unit_key: str | None # product line, channel, or cost centre; None if central original_record_id: str | None created_by: str # person, service, or per-agent identity approved_by: str | None # joined from the approval system on id and version effective_date: date # actual time; may be backdated @dataclass(frozen=True) class OriginalRecord: account_key: str unit_key: str @dataclass(frozen=True) class Contract: """Agreed with the source system's owners; versioned like any other rule.""" allowed_bases: dict[str, frozenset[Basis]] # reason code -> bases it permits allowed_sign: dict[str, int] # reason code -> -1 credit, 1 debit, 0 either approval_at_or_above: dict[str, int] # currency -> minor units authorised_approvers: frozenset[str] units: frozenset[str] # valid unit keys; whether the unit is the # account's or raiser's own is checked elsewhere def contract_flags( adj: Adjustment, contract: Contract, originals: dict[str, OriginalRecord], # record id -> record, already loaded closed_through_at_recording: date, # from the close calendar, as of the date the # source recorded the adjustment, so reruns agree ) -> list[str]: """Reasons an adjustment breaks the contract. The row is loaded either way.""" flags: list[str] = [] if adj.amount_minor == 0: flags.append("amount must be non-zero") threshold = contract.approval_at_or_above.get(adj.currency) if threshold is None: flags.append(f"currency {adj.currency} not in contract") bases = contract.allowed_bases.get(adj.reason_code) if bases is None: flags.append(f"unknown reason code {adj.reason_code}") else: if adj.basis not in bases: flags.append(f"reason does not permit basis {adj.basis.value}") sign = contract.allowed_sign.get(adj.reason_code, 0) if sign and adj.amount_minor * sign < 0: flags.append("sign does not match reason code") unit = (adj.unit_key or "").strip() if adj.basis is Basis.CENTRAL: if unit: flags.append("central adjustments carry no unit") elif unit not in contract.units: flags.append("missing or unknown unit") if adj.basis is Basis.ORIGINATING_RECORD: original = originals.get((adj.original_record_id or "").strip()) if original is None: flags.append("original record not found") else: if original.account_key != adj.account_key: flags.append("original record belongs to another account") if original.unit_key != unit: flags.append("unit does not match the original record") if threshold is not None and abs(adj.amount_minor) >= threshold: if adj.approved_by is None: flags.append("needs an approval") elif adj.approved_by == adj.created_by: flags.append("approver must differ from creator") elif adj.approved_by not in contract.authorised_approvers: flags.append("approver not authorised") if adj.effective_date <= closed_through_at_recording: flags.append("effective date falls in a closed period") return flags Rust
use chrono::NaiveDate; use std::collections::{HashMap, HashSet}; #[derive(Debug, Clone, Copy, PartialEq, Eq, Hash)] pub enum Basis { OriginatingRecord, // unit of the record being corrected AccountOwner, // unit that owns the account RaisedBy, // unit that raised the adjustment Central, // held centrally, by decision } impl Basis { pub fn as_str(self) -> &'static str { match self { Basis::OriginatingRecord => "originating_record", Basis::AccountOwner => "account_owner", Basis::RaisedBy => "raised_by", Basis::Central => "central", } } } #[derive(Debug, Clone)] pub struct Adjustment { pub adjustment_id: String, pub version: u32, // a correction arrives as a new version pub account_key: String, pub amount_minor: i64, // signed, in minor units of `currency` pub currency: String, // ISO 4217 code, e.g. "EUR" pub reason_code: String, pub basis: Basis, pub unit_key: Option<String>, // product line, channel, or cost centre; None if central pub original_record_id: Option<String>, pub created_by: String, // person, service, or per-agent identity pub approved_by: Option<String>, // joined from the approval system on id and version pub effective_date: NaiveDate, // actual time; may be backdated } #[derive(Debug, Clone)] pub struct OriginalRecord { pub account_key: String, pub unit_key: String, } /// Agreed with the source system's owners; versioned like any other rule. #[derive(Debug)] pub struct Contract { pub allowed_bases: HashMap<String, HashSet<Basis>>, // reason code -> bases it permits pub allowed_sign: HashMap<String, i8>, // reason code -> -1 credit, 1 debit, 0 either pub approval_at_or_above: HashMap<String, u64>, // currency -> minor units pub authorised_approvers: HashSet<String>, pub units: HashSet<String>, // valid unit keys; whether the unit is the // account's or raiser's own is checked elsewhere } /// Reasons an adjustment breaks the contract. The row is loaded either way. pub fn contract_flags( adj: &Adjustment, contract: &Contract, originals: &HashMap<String, OriginalRecord>, // record id -> record, already loaded closed_through_at_recording: NaiveDate, // from the close calendar, as of the date the // source recorded the adjustment, so reruns agree ) -> Vec<String> { let mut flags = Vec::new(); if adj.amount_minor == 0 { flags.push("amount must be non-zero".to_string()); } let threshold = contract.approval_at_or_above.get(&adj.currency).copied(); if threshold.is_none() { flags.push(format!("currency {} not in contract", adj.currency)); } match contract.allowed_bases.get(&adj.reason_code) { None => flags.push(format!("unknown reason code {}", adj.reason_code)), Some(bases) => { if !bases.contains(&adj.basis) { flags.push(format!("reason does not permit basis {}", adj.basis.as_str())); } let sign = contract.allowed_sign.get(&adj.reason_code).copied().unwrap_or(0); if sign != 0 && adj.amount_minor.signum() == -i64::from(sign) { flags.push("sign does not match reason code".to_string()); } } } let unit = adj.unit_key.as_deref().map(str::trim).unwrap_or(""); if adj.basis == Basis::Central { if !unit.is_empty() { flags.push("central adjustments carry no unit".to_string()); } } else if !contract.units.contains(unit) { flags.push("missing or unknown unit".to_string()); } if adj.basis == Basis::OriginatingRecord { let id = adj.original_record_id.as_deref().map(str::trim).unwrap_or(""); match originals.get(id) { None => flags.push("original record not found".to_string()), Some(original) => { if original.account_key != adj.account_key { flags.push("original record belongs to another account".to_string()); } if original.unit_key != unit { flags.push("unit does not match the original record".to_string()); } } } } if threshold.is_some_and(|t| adj.amount_minor.unsigned_abs() >= t) { match &adj.approved_by { None => flags.push("needs an approval".to_string()), Some(a) if *a == adj.created_by => { flags.push("approver must differ from creator".to_string()) } Some(a) if !contract.authorised_approvers.contains(a) => { flags.push("approver not authorised".to_string()) } Some(_) => {} } } if adj.effective_date <= closed_through_at_recording { flags.push("effective date falls in a closed period".to_string()); } flags } Listing 1. Checking an adjustment against its data contract as it lands in silver. The adjustment is loaded either way; the check returns the reasons it breaks the contract.
Both implementations apply the same rules and return the same messages. Listing 1 looks at one adjustment at a time, so it can't see a credit split into two. Catching splits after the fact needs a separate aggregate test, for example summing credits per account over a rolling 24 hours across every creator.
On a SQL-first platform, the rules that compare an adjustment with reference tables are usually written as queries that return the failing rows. A Dataform assertion is a data quality query that fails if it returns any rows (Google Cloud, n.d.d), and dbt's tests work the same way, selecting the failing rows (dbt Labs, n.d.). Failing the run suits rules that should stop a pipeline. Contract flags are expected in normal operation, so Listing 2 is better kept as a view of open flags whose count is monitored. It covers the reason, basis, and unit rules for the latest version of each adjustment. The refer...
