DEV Community: Andrew Stevens

Mapping the Agent Before Anyone Attacks It

Andrew Stevens — Wed, 14 Oct 2026 07:20:44 +0000

An agent that reads tickets, queries BigQuery, and sends email usually gets reviewed one tool at a time, and each tool has an owner and an access review. What often has no owner is the chain between them: text a customer writes in a ticket can steer what the agent queries and where it sends the result. Much of the agent-specific risk sits in that chain.

This series takes one working agent from threat model to release. Each part picks one way to attack it, shows the attack in a generic form, and then adds controls, along with what each one costs and where it stops helping. Our Missing Primitives series covered the building blocks of trustworthy agents in general. Here I apply them, plus the platform features that have shipped since, to one system you could run.

This first part describes the agent, maps where data and instructions get in, lines the threats up against current OWASP and MITRE ATLAS material, and adds a first control: a policy check on every tool call the agent makes through ADK.

The agent this series attacks

The reference agent is an internal operations agent, a composite of a setup we often see in client work: Microsoft Entra ID for workforce identity, Google Cloud for data and agents. An employee signs in through Entra and uses the agent for three kinds of task:

Triage a ticket: read it, search internal documents for the relevant runbook, and add an internal comment.
Answer a data question: query BigQuery and email the answer to colleagues.
Reply to a ticket: read a customer's ticket, look up the facts in BigQuery, and send the customer a reply.

The agent is built with Google's Agent Development Kit (ADK) and runs on Agent Runtime (formerly Agent Engine), part of Gemini Enterprise Agent Platform (Google Cloud, n.d.c). Teams also run ADK agents on Cloud Run, and where that changes the answer, a short Cloud Run note says so. The agent reaches its tools over the Model Context Protocol (MCP):

BigQuery, through Google's remote MCP server at bigquery.googleapis.com/mcp. As of October 2026 its documentation names six tools, and "[t]he only MCP tool that isn't read-only is execute_sql". The agent is meant to use execute_sql_readonly, which doesn't support DML, DDL, or Python UDFs (Google Cloud, n.d.o).
Tickets, through an MCP server that the ticketing vendor operates.
Documents, through an MCP server the company runs in front of its document store.
Email, through an MCP server the company runs in front of its mail relay.

Keeping the agent off execute_sql is an IAM job, since the in-process controls later in this post only govern a misled model; the checks at the end of the post cover it.

Figure 1 numbers the flows. The tool results in flow 4 are where the design gets harder, because text written outside the company comes back to the agent inside them, while flow 5 sends mail to the same people who can write that text. The company backend matters as much as the runtime: it is the only caller the agent should accept, and it decides which employee and which task each session belongs to.

Two identity questions sit under every arrow in Figure 1: how an employee's Entra sign-in reaches the agent and binds to the session, which Part 2 covers, and whose authority the agent uses at each MCP server, which Part 3 covers. Google's Agent Identity gives each agent its own SPIFFE-based identity and can hold tokens that users delegate to it (Google Cloud, n.d.b). Until Part 3 shows how those tokens are handled, I treat code in the agent's process as able to use any credential the agent holds.

What is worth protecting

An agent's assets go beyond the data behind its tools. For this one:

The data behind the tools: BigQuery tables, internal documents, and ticket history, including customer details.
Credentials: tokens for each MCP server, refresh tokens held on the employee's behalf (by the agent or a credential broker), and wherever delegated tokens are stored for each user.
The ability to act: sending mail from a company domain and writing to tickets both carry the company's name, even when the content is wrong.
Session state and memory: what the agent carries from one turn or one session to the next.
The record of what happened: tool-call logs and the decisions around them, which incident response depends on.
Spend: model tokens and BigQuery bytes scanned, which a looping agent can burn through.

Where instructions get in

Web applications can keep code and data on separate paths, as parameterised queries do, and many injection defences rely on that separation. A language model gets its instructions and its data in the same context window, as text. Anything that reaches the context can be read by the model as a request, whatever channel it arrived through.

Entry point	Who controls the text	How it reaches the model
System instructions	The team that deploys the agent	Loaded at startup
The employee's request	The employee	The chat turn
Tool names and descriptions	Whoever operates each MCP server	Listed to the model so it can choose tools
Ticket bodies and comments	Anyone who can file or comment on a ticket, often outside the company	`get_ticket` results
Documents	Anyone who can write or share into the store	Search results
BigQuery rows	Whatever wrote the rows, including free-text fields that customers filled in	Query results
Memory and session state	Earlier turns and earlier sessions	Loaded with the session

Table 1. Where text enters the reference agent's context.

Only the first two rows are written by people the agent works for. The third is written by whoever runs each server: Google, the ticketing vendor, and the company's own teams. The next three are written by anyone who can get text into a ticket, a document, or a table, and the last by earlier sessions, which may themselves have read any of those. The BigQuery row is easy to miss: a table that stores ticket text or customer notes carries untrusted content even though the tool reading it is internal.

The employee's own request isn't always benign either. The confirmations later in this post are approved by the employee who asked, so they help against a misled model and do little against an employee who wants data out. Where that matters, external sends of query results need a second approver or the company's existing data loss prevention controls.

Three properties in one agent

Simon Willison names the combination that turns injected text into data loss: access to private data, exposure to untrusted content, and the ability to communicate externally, which he calls the lethal trifecta (Willison, 2025). An agent with all three can be steered by text it reads into sending what it can read to wherever the text says.

The reference agent has all three, and the ticket reply task uses all three in one session. It reads a ticket (untrusted content), queries BigQuery (private data), and sends email to a customer (external communication). It is often the task teams most want to automate, which is why this series attacks it.

One path, end to end

Figure 2 shows the path in its generic form. It needs no software vulnerability, and each step is a call the agent is allowed to make.

The ticket text asks, in ordinary language, for some extra context to be included in the reply. The model has been told to be helpful with tickets, the request reads like part of the job, and the query and the email are both tools it was given. Whether a given model follows the embedded text can't be assumed either way, and that is part of the problem. A few test runs without a leak don't show the path is closed.

Each component in Figure 2 behaves as designed. The ticketing system stores what customers write, the BigQuery MCP server runs a read-only query the credentials allow, and the email server sends mail. The leak comes from the order of the calls, so this post starts with a control that can see that order. It also comes to a simple recommendation for the ticket reply task: let the agent draft the reply and have the employee send it.

If the rows in Figure 2 hold customers' personal data, the outcome is a data breach, whatever the AI Act says about the agent. In the EU, GDPR Article 33(1) requires the controller to notify the supervisory authority "without undue delay and, where feasible, not later than 72 hours after having become aware of it", unless the breach is unlikely to result in a risk to people's rights and freedoms (European Union, 2016). In the US, every state has a breach notification law (National Conference of State Legislatures, 2022), and a financial firm regulated in New York has to notify the Department of Financial Services "no later than 72 hours after determining that a cybersecurity incident has occurred" (New York State Department of Financial Services, n.d.). An EU financial entity also reports major ICT-related incidents to its competent authority under DORA (European Union, 2022). Which of these applies depends on the data and the firm, so check with counsel, but I'd put the agent on the same breach runbook as any other system that holds customer data before it ships.

A first control: a policy check on each tool call

The first control sits where the sequence is visible: between the model proposing a tool call and the tool running. ADK provides the hook. A before_tool_callback is "[c]alled just before a specific tool's run_async method is invoked, after the LLM has generated a function call for it"; returning None lets the tool run, and returning a dictionary skips it (Google, n.d.b). ATLAS describes the same idea as mitigation AML.M0030, Restrict AI Agent Tool Invocation on Untrusted Data, with human approval as AML.M0029 (MITRE, 2026).

The policy check is deny by default and works from three inputs:

The task, fixed by the application when it creates the session, from the entry point the employee used. The model doesn't choose it, so text in a ticket shouldn't be able to promote a triage session into a reply session, provided nothing else can write that state (see Session state and credentials below).
The tool's effect class: reads untrusted content, reads sensitive data, writes internally, or sends.
What the session has already been exposed to: whether it has read untrusted content and whether it has read sensitive data.

From those it decides allow, confirm, or deny, checking the rows of Table 2 in order.

Situation	Decision
The tool is unknown, or isn't in the task's allowlist	Deny
An email with no recipients, recipient fields that aren't plain lists of addresses, an argument the policy check doesn't list, a subject or body that isn't text, or control or formatting characters in the subject	Deny
An email to any external address after the session has read untrusted content and sensitive data	Deny (the send in Figure 2)
Any other email to an external address	Confirm
An internal email or a ticket comment after the session has read untrusted content	Confirm
Anything else in the task	Allow, and record whether the call reads untrusted content or sensitive data

Table 2. How the policy check decides.

An address with anything unusual in it, including a plus tag or non-ASCII characters, counts as external. Recipient fields are denied rather than parsed when they aren't plain lists, an unexpected argument could carry an address or data, and control or bidirectional formatting characters in a subject could add mail headers or disguise what the approver sees. The policy check doesn't inspect what the body says.

Listing 1 keeps send_email in the ticket reply task so the deny is visible. For most teams a drafting tool is the better fit, as the section on costs explains.

Python

"""Deny-by-default policy check for the reference agent's tool calls."""
import re
import unicodedata
from dataclasses import dataclass
from enum import Enum

class Effect(Enum):
    READ_UNTRUSTED = "read_untrusted"  # content anyone can write: tickets, documents
    READ_SENSITIVE = "read_sensitive"  # internal data: BigQuery
    WRITE_INTERNAL = "write_internal"  # changes inside the company: ticket comments
    SEND = "send"                      # leaves the agent: email

TOOLS = {
    "get_ticket": Effect.READ_UNTRUSTED,
    "search_documents": Effect.READ_UNTRUSTED,
    "execute_sql_readonly": Effect.READ_SENSITIVE,
    "add_ticket_comment": Effect.WRITE_INTERNAL,
    "send_email": Effect.SEND,
}

# The application sets the task from the entry point the employee used.
# The model never chooses it.
TASKS = {
    "triage_ticket": {"get_ticket", "search_documents", "add_ticket_comment"},
    "data_question": {"execute_sql_readonly", "send_email"},
    "ticket_reply": {"get_ticket", "execute_sql_readonly", "send_email"},
}

# A strict local part: anything unusual counts as external, including a
# plus tag, which can route mail into a ticket or a list.
INTERNAL = re.compile(r"[a-z0-9._-]+@example\.com")

# The email tool's arguments. Any other key could carry an address or data.
SEND_FIELDS = {"to", "cc", "bcc", "subject", "body"}

@dataclass
class Session:
    untrusted_seen: bool = False
    sensitive_read: bool = False

@dataclass(frozen=True)
class Decision:
    outcome: str  # "allow", "confirm", or "deny"
    reason: str

def _recipients(args: dict):
    """Every address field, or None if any field isn't a list of strings."""
    found = []
    for field in ("to", "cc", "bcc"):
        value = args.get(field, [])
        if not isinstance(value, list) or not all(isinstance(r, str) for r in value):
            return None
        found += value
    return found

def _unsafe(c: str) -> bool:
    """Control characters, line separators, and bidirectional formatting."""
    return (unicodedata.category(c) == "Cc" or c in "\u2028\u2029\u200e\u200f"
            or "\u202a" <= c <= "\u202e" or "\u2066" <= c <= "\u2069")

def _external(recipients) -> bool:
    return any(not (r.isascii() and INTERNAL.fullmatch(r.lower())) for r in recipients)

def decide(task: str, tool: str, args: dict, s: Session) -> Decision:
    effect = TOOLS.get(tool)
    if effect is None:
        return Decision("deny", f"unknown tool {tool}")
    if tool not in TASKS.get(task, set()):
        return Decision("deny", f"{tool} is not allowed for task {task}")

    if effect is Effect.SEND:
        if set(args) - SEND_FIELDS:
            return Decision("deny", "unexpected argument")
        subject, body = args.get("subject", ""), args.get("body", "")
        if not (isinstance(subject, str) and isinstance(body, str)):
            return Decision("deny", "subject and body must be text")
        if any(_unsafe(c) for c in subject):
            return Decision("deny", "control or formatting character in subject")
        recipients = _recipients(args)
        if not recipients:
            return Decision("deny", "recipients missing or malformed")
        if _external(recipients):
            if s.untrusted_seen and s.sensitive_read:
                return Decision("deny", "external send after untrusted content and sensitive data")
            return Decision("confirm", "external recipient")
    if effect in (Effect.SEND, Effect.WRITE_INTERNAL) and s.untrusted_seen:
        return Decision("confirm", "side effect after untrusted content")

    # Record what the session has now been exposed to.
    if effect is Effect.READ_UNTRUSTED:
        s.untrusted_seen = True
    if effect is Effect.READ_SENSITIVE:
        s.sensitive_read = True
    return Decision("allow", "within task policy")

Rust

//! Deny-by-default policy check for the reference agent's tool calls.
use serde_json::Value;

#[derive(Clone, Copy, PartialEq, Debug)]
pub enum Effect {
    ReadUntrusted, // content anyone can write: tickets, documents
    ReadSensitive, // internal data: BigQuery
    WriteInternal, // changes inside the company: ticket comments
    Send,          // leaves the agent: email
}

pub fn effect_of(tool: &str) -> Option<Effect> {
    match tool {
        "get_ticket" | "search_documents" => Some(Effect::ReadUntrusted),
        "execute_sql_readonly" => Some(Effect::ReadSensitive),
        "add_ticket_comment" => Some(Effect::WriteInternal),
        "send_email" => Some(Effect::Send),
        _ => None,
    }
}

// The application sets the task from the entry point the employee used.
// The model never chooses it.
pub fn task_allows(task: &str, tool: &str) -> bool {
    let allowed: &[&str] = match task {
        "triage_ticket" => &["get_ticket", "search_documents", "add_ticket_comment"],
        "data_question" => &["execute_sql_readonly", "send_email"],
        "ticket_reply" => &["get_ticket", "execute_sql_readonly", "send_email"],
        _ => &[],
    };
    allowed.contains(&tool)
}

#[derive(Default, Debug)]
pub struct Session {
    pub untrusted_seen: bool,
    pub sensitive_read: bool,
}

#[derive(PartialEq, Debug)]
pub enum Decision {
    Allow,
    Confirm(&'static str),
    Deny(String),
}

/// Every address field, or None if any field isn't a list of strings.
fn recipients(args: &Value) -> Option<Vec<String>> {
    let mut found = Vec::new();
    for field in ["to", "cc", "bcc"] {
        match args.get(field) {
            None => {}
            Some(Value::Array(items)) => {
                for item in items {
                    found.push(item.as_str()?.to_string());
                }
            }
            Some(_) => return None,
        }
    }
    Some(found)
}

/// The email tool's arguments. Any other key could carry an address or data.
const SEND_FIELDS: [&str; 5] = ["to", "cc", "bcc", "subject", "body"];

/// Control characters, line separators, and bidirectional formatting.
fn unsafe_char(c: char) -> bool {
    c.is_control()
        || matches!(c, '\u{2028}' | '\u{2029}' | '\u{200e}' | '\u{200f}')
        || ('\u{202a}'..='\u{202e}').contains(&c)
        || ('\u{2066}'..='\u{2069}').contains(&c)
}

/// A strict local part: anything unusual counts as external, including a
/// plus tag, which can route mail into a ticket or a list.
fn is_internal(address: &str) -> bool {
    if !address.is_ascii() {
        return false;
    }
    let a = address.to_ascii_lowercase();
    match a.strip_suffix("@example.com") {
        Some(local) => {
            !local.is_empty()
                && local.chars().all(|c| c.is_ascii_lowercase() || c.is_ascii_digit() || "._-".contains(c))
        }
        None => false,
    }
}

pub fn decide(task: &str, tool: &str, args: &Value, s: &mut Session) -> Decision {
    let Some(effect) = effect_of(tool) else {
        return Decision::Deny(format!("unknown tool {tool}"));
    };
    if !task_allows(task, tool) {
        return Decision::Deny(format!("{tool} is not allowed for task {task}"));
    }

    if effect == Effect::Send {
        let known = args
            .as_object()
            .is_some_and(|o| o.keys().all(|k| SEND_FIELDS.contains(&k.as_str())));
        if !known {
            return Decision::Deny("unexpected argument".into());
        }
        if ["subject", "body"].iter().any(|f| args.get(*f).is_some_and(|v| !v.is_string())) {
            return Decision::Deny("subject and body must be text".into());
        }
        let subject = args.get("subject").and_then(Value::as_str).unwrap_or("");
        if subject.chars().any(unsafe_char) {
            return Decision::Deny("control or formatting character in subject".into());
        }
        let to = match recipients(args) {
            Some(r) if !r.is_empty() => r,
            _ => return Decision::Deny("recipients missing or malformed".into()),
        };
        if to.iter().any(|r| !is_internal(r)) {
            if s.untrusted_seen && s.sensitive_read {
                return Decision::Deny("external send after untrusted content and sensitive data".into());
            }
            return Decision::Confirm("external recipient");
        }
    }
    if matches!(effect, Effect::Send | Effect::WriteInternal) && s.untrusted_seen {
        return Decision::Confirm("side effect after untrusted content");
    }

    // Record what the session has now been exposed to.
    match effect {
        Effect::ReadUntrusted => s.untrusted_seen = true,
        Effect::ReadSensitive => s.sensitive_read = true,
        _ => {}
    }
    Decision::Allow
}

Listing 1. A deny-by-default policy check for the reference agent's tool calls. Tool names follow the MCP servers in Figure 1; example.com stands in for the company's domain.

Listing 2 wires the policy check into the agent as an ADK plugin. We read the ADK for Python 2.11.0 source for this, and our tests exercise the ordering against agent callbacks. Plugin callbacks registered on the app run before any agent's own before_tool_callback, and they cover the agents the app drives, unless a sub-agent is wrapped in an AgentTool with plugins excluded. They stop at the first plugin that returns a result, so the policy check goes first in the plugin list. Listing 2 is Python only, since it is wiring for ADK's Python SDK.

Python

"""The policy check as an ADK plugin, registered first on the App."""
from google.adk.plugins.base_plugin import BasePlugin

from policy_check import Session, decide

class PolicyCheck(BasePlugin):
    def __init__(self, raise_approval, log):
        super().__init__(name="policy_check")
        self.raise_approval = raise_approval  # the application's approval queue
        self.log = log

    async def before_tool_callback(self, *, tool, tool_args, tool_context):
        state = tool_context.state
        s = Session(
            untrusted_seen=state.get("policy_untrusted_seen", False),
            sensitive_read=state.get("policy_sensitive_read", False),
        )
        decision = decide(state.get("policy_task", ""), tool.name, tool_args, s)
        # The flags only ever go from False to True.
        if s.untrusted_seen:
            state["policy_untrusted_seen"] = True
        if s.sensitive_read:
            state["policy_sensitive_read"] = True
        self.log(tool_context, tool.name, tool_args, decision)

        if decision.outcome == "allow":
            return None  # ADK runs the tool
        if decision.outcome == "confirm":
            # The application records the employee's decision, and the runtime
            # releases the call after the policy check runs again. Nothing the
            # model writes counts as approval.
            self.raise_approval(tool_context, tool, tool_args)
            tool_context.actions.skip_summarization = True  # end the turn here
            return {"status": "awaiting_approval"}
        return {"status": "not_permitted"}  # the reason stays in the log

# app = App(name="ops", root_agent=agent, plugins=[PolicyCheck(...), *other_plugins])

Listing 2. The policy check as an ADK plugin. Returning None runs the tool; returning a dictionary skips it.

The backend sets the task in session state, and the policy check keeps its exposure flags there under keys of its own. The flags are set when a read is allowed, before it runs, so later calls in the same response see them.

When a call needs confirmation

"Confirm" means the call doesn't run. The application raises an approval request instead and shows the signed-in employee the real server, tool name, and arguments. The approval is bound to that employee, the session, the task, the server and tool, and a hash of the canonicalised arguments, and it expires and can be used once.

For external sends, asking the employee to sign in again helps confirm that the approver is the employee. In Entra, one option is a Conditional Access authentication context whose policy sets sign-in frequency to every time, requested with a claims challenge for each approval. Microsoft notes that "every time" allows five minutes of clock skew (Microsoft, n.d.b). The API opts in to the optional acrs claim, and the backend checks it along with when the user last authenticated, from the optional auth_time claim (Microsoft, n.d.a; Microsoft, 2026b). A token carrying acrs only shows that the policy was met when the token was issued, and Microsoft's pages don't describe this combination end to end, so test it before relying on it. Rate-limiting approval requests and denied calls per session keeps a looping model from flooding the employee.

The plugin also ends the model's turn when it raises a request, so a looping or misled model gets no further model steps in that turn. Other tool calls it proposed in the same response are each still checked, and each can raise its own request. In ADK 2.11.0 they are checked in the order the model listed them, as long as the policy check updates its flags before awaiting anything, as Listing 2 does. Order matters: a send listed before a query in the same response gets a confirm, while the reverse gets a deny. Running the policy check again at release is meant to close that gap, and Part 4 tests it. Our tests cover the read-then-send order, so rerun them on every ADK upgrade.

Once the employee approves, the backend resumes the session with a reference to the approval, which lives in a record outside session state. Runtime code outside the model loop then releases the call under the credential the agent would have used. That code runs decide again against the session's current flags and goes ahead only if the result is still confirm. It then calls the tool directly; sending the call back through the ADK pipeline would only raise a fresh request. Because the direct call skips ADK's after-tool callbacks, the release code also records the result in the session as a new event that references the approval, logs it, and runs any after-tool checks itself, such as the Model Armor scan described later in this post.

Where the call runs under the agent's own identity, the release should also check that the approving employee is entitled to the data and the action; Part 3 compares that with running under the employee's delegated authority.

ADK has a built-in tool confirmation feature. At the time of writing, its documentation marks it experimental, lists VertexAiSessionService among the session services it doesn't support (alongside DatabaseSessionService), and doesn't describe binding the confirmation to the signed-in user (Google, n.d.a). Check this against the ADK version you run. VertexAiSessionService is the ADK client for Agent Platform Sessions, which are accessed through an Agent Runtime instance (Google Cloud, n.d.h). The reference agent keeps its sessions in Agent Platform Sessions, so the built-in feature is out for now. Part 4 builds approvals into the policy check and the company backend instead.

We tested both listings. The release path is only described here; Part 4 builds and tests it. The Python tests run the policy check on its own and as a plugin inside an ADK 2.11.0 Runner with a scripted model, where the blocked send never reaches the tool, a confirm ends the model's turn, and the plugin denies a call before the agent's own callback runs. The Rust tests cover the same decision cases: a lookalike domain, addresses hidden in bcc, a recipient field passed as one comma-separated string, an unexpected reply_to argument, a subject or body passed as a list or object, a subject with an injected line break or a bidirectional override, a plus-tagged address, and local parts with percent signs, bang paths, encoded words, invisible characters, or non-ASCII letters.

What the policy check costs

The first cost lands on the ticket reply task. With the policy check in place, the agent can read a customer's ticket, look up the facts, and draft a reply, but it can't send that reply to the customer once both flags are set. The simplest design moves the send out of the agent: the agent drafts, and the employee sends from the ticketing system after reading the draft. For most teams, that is where I'd start. The draft can still carry the data the injected text asked for, so the review screen should show which queries fed the draft and highlight values that came from them; Part 6 covers checking outbound content. Teams that want the agent to send directly need finer-grained tracking than two session flags, which Part 4 covers.

The second cost is confirmation load. Session-level flags are coarse: one ticket read early in a conversation marks everything after it, and in the triage task every comment follows a get_ticket, so the task's main action always needs confirmation. If employees see a confirmation on most actions, they tend to start approving without reading. In our experience, narrower tasks and shorter sessions do more to reduce that load than prompt changes.

The third is upkeep. Every new tool needs an effect class and a place in each task. A tool that is added to an MCP server but not to the policy check is denied, so the table needs an owner who updates it whenever a server adds or changes tools.

Where the policy check stops helping

The policy check sees tool names, arguments, and session history, and nothing else. It closes the exact path in Figure 2, for that task, with those flags. The rest of this section is what it leaves open, and, where a later part picks a gap up, which one.

Routes data can still take

A deny stops one call. The model sees not_permitted and can try another route, such as an internal address that reaches the customer, which then arrives as a routine confirmation. Ending the session after a deny like the one in Figure 2, or denying every later side effect in it, closes that route within the session, and Part 4 adds it. That particular deny should rarely fire in normal use, so I'd alert on it from day one; Part 8 builds the detection around it.

An address on the company's domain can still reach outsiders: a ticketing system's intake address that posts mail to a customer-visible ticket, a group with external guests, or a mailbox that forwards. A colleague may also not be entitled to the rows the agent read. Resolving recipients against the directory and checking their own access belongs in Parts 3 and 6.

Another customer's ticket is a route too. In the ticket tasks, injected text can ask the agent to read a different customer's ticket and include it in the reply, which needs only a confirmation that looks routine. Binding the ticket ID when the session is created, and denying get_ticket or add_ticket_comment for any other ID, helps close that route.

Some channels have an outside audience without looking like sends. A ticket comment is classed as an internal write, but on a ticket filed by a customer, the customer may be able to read it. A shared document link works the same way. Effect classes have to follow who can read the result, which Part 6 works through.

Read tools can carry data out through their arguments. Reads are allowed even after sensitive data has been read, and a ticket ID or search query stuffed with query results reaches the vendor's server and its logs. Strict argument formats help, and so does treating calls to servers run by other organisations as sends once a session holds sensitive data.

The agent's own output never reaches the policy check. Text the model returns to the employee, including links and images the chat interface renders, never passes through a tool call, and the policy check doesn't read email bodies, so HTML and remote images in them pass. Remote images and links in mail the agent sends internally can do the same when a colleague's mail client loads them. Part 6 covers output handling.

What tool names and two flags can't capture

Some sources are sensitive and untrusted at once. If a BigQuery table holds customer-written notes, a query result is both sensitive and untrusted, and the policy check as written only sets the sensitive flag. Internal documents are the mirror case: the policy check classes them as untrusted only, though they hold internal material. Tickets are mixed too, since get_ticket returns the internal comments the triage task writes, so in the reply task it should set both flags, or the ticket server should return only customer-visible fields. Classifying by source rather than by tool helps with all three, though it needs a data catalogue to work from.

Memory loaded into a new session and text an employee pastes from a customer email set no flag. The application should set both flags when it creates a session that loads memory, since memory can carry earlier query results as well as earlier untrusted text, and the untrusted flag when a session takes pasted text or attachments.

The policy check allows execute_sql_readonly in the right tasks; it doesn't judge whether a particular query reads more than the task needs. That depends on which identity the query runs as, and on row-level controls in BigQuery, which narrow results per employee when the query runs as that employee. Part 3 compares the options.

What the policy check has to trust

The policy check trusts the approval it receives. The approver can only judge what the application shows them, so the application should render the arguments itself (ASI09 in Table 3).

The policy check also trusts that nothing changes a call after it allows it. Later plugins and the agent's own callbacks receive the same arguments and can change them after the policy check has allowed the call. In the ADK 2.11.0 source, built-in tools such as Google Search grounding are added to the model request rather than run by the agent's tool pipeline, so the policy check never sees them.

The policy check trusts tool names. With several MCP servers, two can expose tools with the same name, and a server can change what a tool does after it was approved. Tools denied by the policy check also still have their descriptions listed to the model. ADK's McpToolset accepts a tool_filter and a tool_name_prefix, which help in the meantime (the policy check's table then uses the prefixed names), and Part 5 pins tools to the authenticated server and the descriptions that were reviewed.

An in-process plugin governs what a misled model can do, and a compromised runtime can skip it.

Controls outside the process

Agent Gateway, which Google describes as the key enforcement component of Agent Platform, sits outside the agent. Its overview says that by default all connections are blocked unless an explicit IAM policy grants access, and that for MCP traffic it can parse requests so policies can restrict specific tools (Google Cloud, n.d.a). Calls that violate its Access policies are blocked in enforce mode and only logged in dry-run mode (Google Cloud, n.d.e). A destination that isn't registered in Agent Registry needs an explicit access policy, and Principal Access Boundary policies can also be enforced on the agent's identity (Google Cloud, n.d.a; Google Cloud, n.d.e). In enforce mode, that narrows what a compromised runtime can reach to what the agent's identity is allowed, provided egress can't bypass the gateway. Within that, any delegated user tokens the runtime holds remain usable.

Agent Gateway's egress policies are keyed on the agent's identity: "Agent principals are the members that you grant access to" (Google Cloud, n.d.e). The documentation we read doesn't show policy conditions on the end user, and as far as we can tell the gateway doesn't see session history, which is what the policy check works from. Google's pages also read differently on VPC Service Controls. The gateway overview lists enforcing perimeters for agent communications as a benefit (Google Cloud, n.d.a), and the release notes add that support from 9 September 2026 (Google Cloud, 2026a). It applies only to Agent Gateway deployments created after 8 September whose VPC connectivity uses the agent connectivity template in ALL_TRAFFIC egress mode. The IAM Access policies page still carries a note that Access policies don't support VPC Service Controls (Google Cloud, n.d.e). Part 6 works out what that means for a perimeter around this agent.

My read, while the documentation settles: treat Agent Gateway, and Model Armor below, as defence in depth, and treat the policy check as the control you own and can test today.

Cloud Run note. As of October 2026, Agent Gateway's documentation lists Agent Runtime (ingress and egress) and Gemini Enterprise (egress only), and doesn't list Cloud Run, although Agent Identity lists Cloud Run (Google Cloud, n.d.b; Google Cloud, n.d.a). A Cloud Run deployment keeps the in-process policy check but needs its egress controls from elsewhere, which Part 6 covers.

Where Model Armor fits

Filters that look for injected instructions are useful as a second layer. I wouldn't rely on one on its own, because it can let too much through. As of October 2026, Google's Model Armor can inspect tools/call requests and responses for Google's MCP servers, but not tools/list, and Google advises enabling its prompt injection and jailbreak filter only where MCP traffic carries natural language (Google Cloud, n.d.g). BigQuery is on the list of MCP servers it supports (Google Cloud, n.d.i). Google's BigQuery MCP page also warns that "When Model Armor is enabled with logging enabled, Model Armor logs the entire payload", so query results can end up in your logs (Google Cloud, n.d.o).

In this design, that integration would see the BigQuery traffic but not the ticket text in Figure 2, which arrives through the vendor's server.

On Agent Runtime, Model Armor can also be enabled on Agent Gateway. For egress MCP traffic to servers in general, Google's page says it sanitises tools/call and prompts/get requests and responses and tool execution errors, and it lists tools/list, resources/*, and "Streamable HTTP/SSE for MCP" among the payloads that pass without sanitization. Model Armor also has to be in the same region as the services it integrates with (Google Cloud, n.d.f). The page doesn't make clear whether a tools/call response that a server streams over SSE is inspected, so check this against the vendor's server before counting on it. Where that doesn't apply, including on Cloud Run, the application can send get_ticket results to Model Armor itself, as applications do for prompts and model responses (Google Cloud, n.d.k), for example from an after-tool callback.

Willison's point about guardrail products applies: vendors often claim to catch "95% of attacks", but "in web application security 95% is very much a failing grade" (Willison, 2025). A detector can lower the rate of successful injections, and the policy check denies the send in Figure 2 whether or not the detector flags the ticket. Part 4 tests both against the same set of tickets.

Mapping the threats to OWASP and ATLAS

I map against two catalogues, because they answer different questions. The OWASP GenAI Security Project published the Top 10 for Agentic Applications for 2026 in December 2025. It describes risk categories and mitigations, and refers to the 2025 edition of the OWASP LLM Top 10: ASI03, for example, is described as the agentic evolution of Excessive Agency (LLM06:2025) (OWASP GenAI Security Project, 2025). The project published a 2026 edition of the LLM Top 10 in August 2026 with renumbered entries, so Excessive Agency is now LLM03 (OWASP GenAI Security Project, 2026), and Table 3 uses the 2026 numbers. MITRE ATLAS describes adversary techniques and mitigations; the data release used here is 2026.09 (MITRE, 2026).

Threat to the reference agent	OWASP Agentic 2026	OWASP LLM 2026 (our mapping)	Part
Ticket or document text redirects the agent	ASI01 Agent Goal Hijack	LLM01 Prompt Injection	4
Allowed tools chained for an unintended purpose	ASI02 Tool Misuse and Exploitation	LLM03 Excessive Agency	1, 4
The agent's authority is wider than the user's or the task's	ASI03 Identity and Privilege Abuse	LLM03 Excessive Agency	2, 3
A caller poses as another employee, resumes someone else's session, or changes the task or flags in session state	ASI03 Identity and Privilege Abuse		2
A token is harvested through the agent or stolen, then replayed	ASI03 Identity and Privilege Abuse		3
A tool's description or behaviour is poisoned or changes	ASI04 Agentic Supply Chain Vulnerabilities	LLM04 Supply Chain	5
Data leaves through email, links, or rendered images	ASI02 Tool Misuse and Exploitation	LLM02 Sensitive Information Disclosure; LLM10 Improper Output Handling	6
Poisoned memory changes later sessions	ASI06 Memory & Context Poisoning		7
A misleading summary gets a human to approve an action	ASI09 Human-Agent Trust Exploitation		4
Looping, runaway cost, or an agent acting outside its purpose	ASI02 Tool Misuse and Exploitation; ASI08 Cascading Failures; ASI10 Rogue Agents	LLM06 Unbounded Consumption	8

Table 3. The reference agent's threats mapped to OWASP, with the part of the series that covers each. The LLM 2026 column is our mapping; OWASP's agentic document lists its own related LLM entries, in 2025 numbering, for each ASI item in an appendix. Blank cells are where we didn't map an entry.

Threat to the reference agent	MITRE ATLAS 2026.09
Ticket or document text redirects the agent	AML.T0051.001 LLM Prompt Injection: Indirect; AML.T0099 AI Agent Tool Data Poisoning
Allowed tools chained for an unintended purpose	AML.T0053 AI Agent Tool Invocation
The agent's authority is wider than the user's or the task's	Mitigations AML.M0026, AML.M0027, AML.M0028, and AML.M0037
A token is harvested through the agent or stolen, then replayed	AML.T0091.000 Use Alternate Authentication Material: Application Access Token; AML.T0098 AI Agent Tool Credential Harvesting
A tool's description or behaviour is poisoned or changes	AML.T0010.005 AI Supply Chain Compromise: AI Agent Tool; AML.T0110 AI Agent Tool Poisoning
Data leaves through email, links, or rendered images	AML.T0086 Exfiltration via AI Agent Tool Invocation; AML.T0077 LLM Response Rendering; AML.T0057 LLM Data Leakage
Poisoned memory changes later sessions	AML.T0080.000 AI Agent Context Poisoning: Memory
A misleading summary gets a human to approve an action	AML.T0067 LLM Trusted Output Components Manipulation
Looping, runaway cost, or an agent acting outside its purpose	AML.T0034.002 Cost Harvesting: Agentic Resource Consumption

Table 4. The same threats mapped to MITRE ATLAS. We didn't find an ATLAS technique that matches over-broad agent authority well, so that row names the ATLAS mitigations for agent and tool permissions instead, and we didn't map the session-tampering row.

Two OWASP entries get less attention here. ASI07, Insecure Inter-Agent Communication, applies once agents call other agents, and this is a single agent. ASI05, Unexpected Code Execution, is narrow while the agent's only code-like capability is read-only SQL. A query can still call a remote function, though. BigQuery documents remote functions as running code in Cloud Run or Cloud Run functions through a connection, used "in a query just like any other user-defined functions" (Google Cloud, n.d.p). I haven't found Google documentation saying whether execute_sql_readonly allows them, so the identity running the query shouldn't have access to routines, connections, or external data sources it doesn't need. I'd also test whether execute_sql_readonly accepts EXPORT DATA statements or the EXTERNAL_QUERY function before relying on it.

The mapping is also a useful artefact for governance work in the US and the EU. In NIST's AI Risk Management Framework, "The MAP function establishes the context to frame risks related to an AI system" (National Institute of Standards and Technology, 2023), and the Generative AI Profile lists information security, including direct and indirect prompt injection, among the risks it covers (National Institute of Standards and Technology, 2024).

In the EU, many internal operations agents won't fall into a high-risk category under the AI Act. Where one does, Article 15(5) requires such systems to be "resilient against attempts by unauthorised third parties to alter their use, outputs or performance by exploiting system vulnerabilities" (European Commission, n.d.), and the company that built it may count as its provider. For the rest, Article 15 is still a useful bar. The Commission's page notes that Article 15 has been amended by the Digital Omnibus on AI, so check the current text and dates; our Regulatory Stack post on Article 15 covers that side.

I'd keep the threat model as a versioned table beside the agent's code, so the engineers and the governance team work from the same file.

Launch stages as of 13 October 2026

Most of the platform features this post relies on shipped this year, so Table 5 records their launch stage as of the date we checked.

Feature	Launch stage	Source
Agent Identity	Generally available since 22 April 2026	Google Cloud, 2026c
Agent Identity auth manager	Generally available since 22 August 2026, after a preview from April	Google Cloud, 2026c
Agent Gateway	Generally available since 18 June 2026; VPC Service Controls support announced on 9 September 2026 without a stated stage, for deployments created after 8 September that use the agent connectivity template in `ALL_TRAFFIC` egress mode	Google Cloud, 2026a
IAM Unified Access Policies (Access policies)	Generally available since 31 August 2026	Google Cloud, 2026a
Model Armor for Agent Gateway	Generally available since 24 June 2026	Google Cloud, 2026a
Google's remote MCP servers	Generally available as a capability since 1 May 2026, with individual servers in Preview or GA	Google Cloud, 2026b
BigQuery remote MCP server	Not marked Preview in Google's list of supported MCP products, which the MCP release notes point to for launch status; no stage stated on the server's own page	Google Cloud, 2026b; Google Cloud, n.d.m; Google Cloud, n.d.o
Model Armor's MCP server integration	No stage stated on its page	Google Cloud, n.d.g
ADK tool confirmation	Experimental	Google, n.d.a

Table 5. Launch stages of the features this post relies on.

Where no stage is stated, I'd confirm it with Google before relying on the feature in production, since pre-GA terms and support differ. The design doesn't depend on ADK's experimental confirmation feature.

What I'd check in your own deployment

Most of what follows can't be settled in a post, because it depends on how your tenant, projects, and servers are configured. These are the checks I'd want to see pass, with evidence, before an agent like this goes near production. Four of them I'd treat as blocking a release: only the backend can call the agent, user_id comes from the validated token, the policy check's keys can't be changed after the session is created, and a tool missing from the policy check's table is denied.

Who can reach the agent

Owner: usually the platform team that runs Google Cloud IAM.

Only the company backend can call the agent, and only the backend and the agent's own identity hold permissions on its sessions. The backend gets a custom role with only aiplatform.reasoningEngines.query on the agent resource, as Google's sharing page suggests (Google Cloud, n.d.l), and nothing else inherits that permission from the project, folder, or organisation through a role such as roles/aiplatform.user or a basic role (on Cloud Run, the invoker role). Google notes that default ADK agents trust the frontend, "which gives the frontend full control of sessions and users" (Google Cloud, n.d.l).
Deploy rights on the agent are treated as the agent's authority: anyone who can change its code or configuration acts as it.
The backend keeps its own record of which session belongs to which user, and resumes a session only for the user it was created for. Anyone holding the query permission can call the agent's session operations directly, including creating sessions, so the IAM restriction above is what protects policy_task.

How the backend checks the employee

Owner: the backend's developers, with the identity team.

The backend validates the Entra token's signature, issuer, tenant, and lifetime (exp and nbf), and checks that the audience identifies its own API: in v2.0 tokens that is the client ID, and in v1.0 tokens the client ID or the resource URI the client requested (Microsoft, 2026a).
It accepts only delegated user tokens that carry its scope in scp, from client apps it expects (azp in v2.0 tokens, appid in v1.0), and rejects app-only tokens, for which the optional idtyp claim is app (Microsoft, 2026b).
It rejects guest accounts unless a task is meant for them, for example by requiring user assignment on the API's enterprise application and assigning only the intended users or groups, and by checking the optional acct claim, which is 1 for a guest (Microsoft, 2026b). Both idtyp and acct have to be configured as optional claims on the API's app registration. Once configured, idtyp is emitted on app-only tokens, which is all this check needs (Microsoft, 2026b).
It derives the ADK user_id from the tenant and object IDs (tid and oid; the client has to request the profile scope for these to be issued), never from a client-supplied field (Microsoft, 2026a). Agent Runtime accepts a user ID the caller chooses (its documentation says "[c]hoose your own user ID") (Google Cloud, n.d.n), so this binding is the backend's job.
The agent never sees the Entra token, so it takes the employee's identity entirely on the backend's word. If delegated tokens are held per user, a caller who can choose user_id can use another employee's tokens, which makes the backend's identity and code as sensitive as the tokens they can unlock. Part 2 covers this binding.

Session state and credentials

Owner: the agent's developers, with the platform team.

The policy check's keys (policy_task and the flags) can't be changed after the session is created. In practice that means:
- no initial state or events from the client, and no state_delta or extra session events on a query;
- no session PATCH through the ADK API server, and no session rewind outside the backend;
- no agent output_key, tool, or callback that writes those keys, and no user: or app: prefix on them.

Keeping the keys, and the approval records, in a store outside session state is stronger still.

Delegated tokens sit with the auth manager or another broker, not in session state that anything reading the session can see (ADK can keep them in session state, so this is a configuration choice), and each task gets its own toolsets, so credentials for servers outside the task aren't loaded.

Tools, arguments, and approvals

Owner: the agent's developers, with an application security review.

No plugin or callback that runs after the policy check modifies tool arguments, model-side built-in tools are off, and no MCP sampling or elicitation callbacks are registered on the toolsets, since server-initiated requests don't pass through the tool pipeline.
Every tool exposed by every MCP server the agent can reach appears in the policy check's table, and a tool that doesn't is denied in the deployed agent, which a test confirms.
The email tool's real argument names match SEND_FIELDS in Listing 1. A reply_to, attachment, or template field stays denied until the policy check knows how to check it, and the email server builds recipient headers only from to, cc, and bcc, and normalises line endings in the body so it can't end the message and start another (SMTP smuggling).
Approvals are bound as described above, single-use, shown by the application rather than summarised by the model, and checked again by the policy check at release.

Where data can go

Owner: the data owners and the teams that run mail and ticketing, with security.

The internal addresses that reach outsiders (intake addresses, groups with guests, and forwarding mailboxes) are listed and kept current.
The BigQuery tables that hold text written outside the company are listed, so queries against them can be classed as reading untrusted content.
Every principal that reaches BigQuery is denied read-write MCP tools and holds no BigQuery write permissions. Google documents a deny policy that limits its MCP servers to read-only tools; its example denies every principal in the project, folder, or organisation it is attached to. The denied tool still appears in the tool list, so a tool_filter on the toolset keeps it away from the model (Google Cloud, n.d.j). A VPC Service Controls perimeter around the BigQuery project, which Google documents for its own MCP servers with conditions such as read-only tool calls (Google Cloud, n.d.d), helps keep whatever statements execute_sql_readonly does allow from moving data outside it. Check how that perimeter and Agent Gateway's Access policies fit together, given the documentation notes above.

Platform support and logging

Owner: the platform team, with security operations.

Agent Gateway, Model Armor, and the Agent Identity auth manager are confirmed to support the runtime, region, and MCP servers in use, at a launch stage you have accepted for production, checked against Table 5 and Google's current documentation.
The policy check's decisions are logged with the session, task, tool, arguments, and outcome, with access and retention set as for the data the arguments carry. Part 8 builds on this.

What this series leaves out

Some common questions fall outside the reference agent. It is a single agent, so agent-to-agent delegation and multi-agent protocols are out of scope; our post on secure multi-agent protocols covers that ground. It has no code-execution tool or sandbox. Cost and resource exhaustion appear only as far as Part 8 detects and stops a looping agent, and our post on resource governance covers budgets and throttling. The model's own supply chain, meaning where its weights come from and how they were trained, is also out of scope, as are the web security of the chat front end, the model provider's data handling, and the ticketing vendor's own platform security.

The rest of the series

Part 2 follows the employee's identity into the agent: from an Entra sign-in through the company backend, how the session is bound to that employee, and how the employee's Entra identity is federated into Google Cloud for the calls that need it. Part 3 follows the agent's calls out to its tools and compares the authority each can run under: delegated user authority, the agent's own identity, or a combination. It also covers the current MCP authorization specification (version 2026-07-28), under which servers accept only tokens issued for them and never pass a client's token on to upstream APIs (Model Context Protocol, 2026a; Model Context Protocol, 2026b), and where the servers in this design depart from it.

Part 4 takes indirect injection through tickets, documents, and query results, with value-level tracking, the approval path, and a test of Model Armor and the policy check against the same tickets. Part 5 deals with MCP servers you didn't write, Part 6 with the ways data leaves, and Part 7 with memory. Part 8 is about detecting and containing an agent mid-task, and Part 9 is red-teaming before release.

Which of your agents reads text from outside the company in the same session as it reads your data, and what can it send?

References

European Commission, n.d. Article 15: Accuracy, robustness and cybersecurity. AI Act Service Desk. Available at: https://ai-act-service-desk.ec.europa.eu/en/ai-act/article-15 [Accessed 13 October 2026].

European Union, 2016. Regulation (EU) 2016/679 of the European Parliament and of the Council of 27 April 2016 on the protection of natural persons with regard to the processing of personal data and on the free movement of such data, and repealing Directive 95/46/EC (General Data Protection Regulation). Article 33. Available at: http://data.europa.eu/eli/reg/2016/679/oj [Accessed 13 October 2026].

European Union, 2022. Regulation (EU) 2022/2554 of the European Parliament and of the Council of 14 December 2022 on digital operational resilience for the financial sector. OJ L 333, 27.12.2022, Article 19. Available at: http://data.europa.eu/eli/reg/2022/2554/oj [Accessed 13 October 2026].

Google, n.d.a. Get action confirmation for ADK Tools. Agent Development Kit documentation. Available at: https://adk.dev/tools-custom/confirmation/ [Accessed 13 October 2026].

Google, n.d.b. Types of callbacks. Agent Development Kit documentation. Available at: https://adk.dev/callbacks/types-of-callbacks/ [Accessed 13 October 2026].

Google Cloud, 2026a. Gemini Enterprise Agent Platform release notes. Entries from 22 April to 9 September 2026. Available at: https://docs.cloud.google.com/gemini-enterprise-agent-platform/release-notes [Accessed 13 October 2026].

Google Cloud, 2026b. Google Cloud MCP servers release notes. Entry of 1 May 2026. Available at: https://docs.cloud.google.com/mcp/release-notes [Accessed 13 October 2026].

Google Cloud, 2026c. IAM release notes. Entries of 22 April and 22 August 2026. Available at: https://docs.cloud.google.com/iam/docs/release-notes [Accessed 13 October 2026].

Google Cloud, n.d.a. Agent Gateway overview. Gemini Enterprise Agent Platform documentation. Available at: https://docs.cloud.google.com/gemini-enterprise-agent-platform/govern/gateways/agent-gateway-overview [Accessed 13 October 2026].

Google Cloud, n.d.b. Agent Identity overview. Identity and Access Management documentation. Available at: https://docs.cloud.google.com/iam/docs/agent-identity-overview [Accessed 13 October 2026].

Google Cloud, n.d.c. Agent Platform overview. Gemini Enterprise Agent Platform documentation. Available at: https://docs.cloud.google.com/gemini-enterprise-agent-platform/overview [Accessed 13 October 2026].

Google Cloud, n.d.d. Control MCP use in a VPC Service Controls perimeter. Google Cloud MCP servers documentation. Available at: https://docs.cloud.google.com/mcp/control-mcp-use-vpc-sc-perimeter [Accessed 13 October 2026].

Google Cloud, n.d.e. IAM Access policies overview. Gemini Enterprise Agent Platform documentation. Available at: https://docs.cloud.google.com/gemini-enterprise-agent-platform/govern/policies/iam-overview-uap [Accessed 13 October 2026].

Google Cloud, n.d.f. Integrate Model Armor with Agent Gateway. Model Armor documentation. Available at: https://docs.cloud.google.com/model-armor/model-armor-agent-gateway-integration [Accessed 13 October 2026].

Google Cloud, n.d.g. Integrate with Google and Google Cloud MCP servers. Model Armor documentation. Available at: https://docs.cloud.google.com/model-armor/model-armor-mcp-google-cloud-integration [Accessed 13 October 2026].

Google Cloud, n.d.h. Manage sessions with Agent Development Kit. Gemini Enterprise Agent Platform documentation. Available at: https://docs.cloud.google.com/gemini-enterprise-agent-platform/scale/sessions/manage-with-adk [Accessed 13 October 2026].

Google Cloud, n.d.i. Model Armor supported products. Google Cloud MCP servers documentation. Available at: https://docs.cloud.google.com/mcp/model-armor-supported-products [Accessed 13 October 2026].

Google Cloud, n.d.j. Prevent read-write MCP tool use. Google Cloud MCP servers documentation. Available at: https://docs.cloud.google.com/mcp/prevent-read-write-tool-use [Accessed 13 October 2026].

Google Cloud, n.d.k. Protect AI applications with Model Armor. Security Command Center documentation. Available at: https://docs.cloud.google.com/security-command-center/docs/model-armor [Accessed 13 October 2026].

Google Cloud, n.d.l. Share an agent. Gemini Enterprise Agent Platform documentation. Available at: https://docs.cloud.google.com/gemini-enterprise-agent-platform/govern/share-agent [Accessed 13 October 2026].

Google Cloud, n.d.m. Supported products. Google Cloud MCP servers documentation. Available at: https://docs.cloud.google.com/mcp/supported-products [Accessed 13 October 2026].

Google Cloud, n.d.n. Use an Agent Development Kit agent. Gemini Enterprise Agent Platform documentation. Available at: https://docs.cloud.google.com/gemini-enterprise-agent-platform/scale/runtime/use-an-adk-agent [Accessed 13 October 2026].

Google Cloud, n.d.o. Use the BigQuery remote MCP server. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/use-bigquery-mcp [Accessed 13 October 2026].

Google Cloud, n.d.p. Work with remote functions. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/remote-functions [Accessed 13 October 2026].

Microsoft, 2026a. Access token claims reference. Microsoft identity platform documentation, Microsoft Learn. Available at: https://learn.microsoft.com/en-us/entra/identity-platform/access-token-claims-reference [Accessed 13 October 2026].

Microsoft, 2026b. Optional claims reference. Microsoft identity platform documentation, Microsoft Learn. Available at: https://learn.microsoft.com/en-us/entra/identity-platform/optional-claims-reference [Accessed 13 October 2026].

Microsoft, n.d.a. Developer guide to Conditional Access authentication context. Microsoft identity platform documentation, Microsoft Learn. Available at: https://learn.microsoft.com/en-us/entra/identity-platform/developer-guide-conditional-access-authentication-context [Accessed 13 October 2026].

Microsoft, n.d.b. Conditional Access adaptive session lifetime policies. Microsoft Entra ID documentation, Microsoft Learn. Available at: https://learn.microsoft.com/en-us/entra/identity/conditional-access/concept-session-lifetime [Accessed 13 October 2026].

MITRE, 2026. ATLAS data, release 2026.09. MITRE ATLAS. Available at: https://github.com/mitre-atlas/atlas-data/blob/main/dist/v6/ATLAS-2026.09.yaml [Accessed 13 October 2026].

Model Context Protocol, 2026a. Authorization. Specification, version 2026-07-28. Available at: https://modelcontextprotocol.io/specification/2026-07-28/basic/authorization [Accessed 13 October 2026].

Model Context Protocol, 2026b. Authorization security considerations. Specification, version 2026-07-28. Available at: https://modelcontextprotocol.io/specification/2026-07-28/basic/authorization/security-considerations [Accessed 13 October 2026].

National Conference of State Legislatures, 2022. 2022 Security Breach Legislation. Available at: https://www.ncsl.org/technology-and-communication/2022-security-breach-legislation [Accessed 13 October 2026].

National Institute of Standards and Technology, 2023. Artificial Intelligence Risk Management Framework (AI RMF 1.0). NIST AI 100-1. Available at: https://nvlpubs.nist.gov/nistpubs/ai/NIST.AI.100-1.pdf [Accessed 13 October 2026].

National Institute of Standards and Technology, 2024. Artificial Intelligence Risk Management Framework: Generative Artificial Intelligence Profile. NIST AI 600-1. Available at: https://nvlpubs.nist.gov/nistpubs/ai/NIST.AI.600-1.pdf [Accessed 13 October 2026].

New York State Department of Financial Services, n.d. 23 NYCRR Part 500: Cybersecurity Requirements for Financial Services Companies, section 500.17. Available at: https://www.dfs.ny.gov/cybersecurity/23-NYCRR-Part-500 [Accessed 13 October 2026].

OWASP GenAI Security Project, 2025. OWASP Top 10 for Agentic Applications for 2026. December 2025. Available at: https://genai.owasp.org/resource/owasp-top-10-for-agentic-applications-for-2026/ (document: https://genai.owasp.org/download/52117/?tmstv=1765059207) [Accessed 13 October 2026].

OWASP GenAI Security Project, 2026. OWASP GenAI LLM Top 10 2026. 3 August 2026. Available at: https://genai.owasp.org/home/resources/owasp-genai-llm-top-10-2026/ (document: https://genai.owasp.org/download/56857/?tmstv=1785822482) [Accessed 13 October 2026].

Willison, S., 2025. The lethal trifecta for AI agents: private data, untrusted content, and external communication. Simon Willison's Weblog, 16 June. Available at: https://simonwillison.net/2025/Jun/16/the-lethal-trifecta/ [Accessed 13 October 2026].

Keeping Raw Data You May Be Asked to Delete

Andrew Stevens — Tue, 13 Oct 2026 12:14:51 +0000

Your first job on an erasure request is a search: where does this person's data sit? For a customer with one online order, the example estate in this series holds it in more than twenty places across the source database, the lakehouse, and the records around it. Some are obvious, such as the orders table and its bronze copy. Others are easy to miss: the change tables that feed replication, a month-end snapshot, the text of a query someone ran to look into a refund, and the table that caught a pipeline error. Each has its own rule for how long data stays after a delete, and several keep it for weeks after the DELETE statement succeeds.

This series is for teams that have built a medallion lakehouse on Google Cloud and now have to run it. It assumes the layers are in place, as described in our post on the medallion architecture. Part 1 starts with bronze, because bronze is where replay comes from and also where personal data is often kept longest, in its least processed form. The aim is an erasure process that finishes by a date the platform can compute for its own stores. That date applies to erased data. A lot of order data is kept on purpose under legal obligations, and this post keeps the two apart.

The estate is a composite built for this series, close to the one in our post on multi-cloud retail: Microsoft Entra ID for workforce identity (which a later part on access uses), Google Cloud for the data platform, and Azure SQL Database under an online retailer's operational systems.

The storage options this series refers to

Several parts of this series depend on how bronze, silver, and gold are stored, so we give three storage options a letter here. A later part compares them in depth.

A. BigQuery native tables, stored and managed entirely by BigQuery.
B. Apache Iceberg managed tables (formerly BigLake tables for Apache Iceberg in BigQuery). Data files sit in your Cloud Storage bucket and BigQuery manages them. Google garbage-collects data files after the time travel window, but not the files of a dropped table, and recommends keeping the bucket's default soft delete policy (Google Cloud, n.d.b).
C. Iceberg in an open catalog, such as Google's Apache Iceberg REST catalog endpoint, with other engines writing the files and you running the file maintenance. BigQuery can't create or modify tables in that endpoint with DDL or DML (Google Cloud, n.d.y).

This part works through the design on options A and B, where BigQuery runs both the split job and the erasure job. On option C, those jobs run in Spark against the catalog instead. The file chain (delete files, compaction, snapshot expiry, and orphan cleanup) belongs with the storage comparison.

Option B carries one constraint that shapes the design. Google lists authorised views as unsupported for managed Iceberg tables (Google Cloud, n.d.b). In this design, readers reach bronze, the retained store, and the identity table only through authorised views. So on option B those three stay native tables, as do the stores those views read (the suppression entries, the reopen table, the hand-off table, and the kept list). Managed Iceberg holds silver and gold, whose readers have table access anyway. If you put bronze on managed Iceberg, you have to give silver and analysts read access on the tables, and that lets them read deleted rows during the time travel window. The option B rows in the ledger and the worked example cover that case's timing. That read exposure comes on top.

Where this design follows the textbook medallion, and where it departs

Google's guide to the medallion architecture describes four implementation steps (Google Cloud, n.d.ag). The first three build the layers. Ingestion moves data into bronze "without changing it", so no information is dropped or accidentally altered on the way, and bronze acts as a permanent, historical archive. Building silver uses Spark or SQL to apply data quality rules, such as converting timestamps to UTC or removing test accounts. Gold comes from SQL queries that join tables into metrics like revenue by category, often as high-performance views that a tool such as Looker can read directly.

The fourth step, orchestration and governance, names Managed Service for Apache Airflow for scheduling. Its governance example is letting only a few authorised service accounts write to silver and gold. The page's list of Google Cloud products also names Dataform for building and scheduling the transformations that turn silver into gold (Google Cloud, n.d.ag).

This series follows those steps, with SQL for every move between layers on options A and B, and Spark SQL on option C. It's one approach, and we've seen it work well where the data already sits in BigQuery: the work runs where the data is, with no separate cluster to run and no data to move out. On the estates we've worked on, it has usually been cheaper and faster to run, and quicker to build and change, than the same work in pipeline code. The right pattern depends on the data, team, and existing tooling, and another estate can call for another.

The tool that runs that SQL varies by engagement. We've used Airflow, Dataform, and dbt (scheduled by the existing orchestrator or by dbt Cloud), usually whatever you already run. The example estate uses Managed Service for Apache Airflow. It schedules every job in this post and submits each SQL script to BigQuery as a job under that job's own service account. The infrastructure account creates the gold view once. The hardening section sets out the access rules for Airflow and what changes under Dataform or dbt.

Personal data calls for one departure. Google's page doesn't cover it, and a bronze layer that keeps every customer's identifiers as a permanent archive makes the storage limitation and erasure duties hard to meet. So this series splits Google's bronze in two:

A restricted raw dataset takes the source rows as received, except the email (which in the main design never leaves the source), and keeps them only for a short retention period.
Bronze holds the longer-lived history for replay, kept for its purpose periods, with direct identifiers replaced by a token.

Google's steps leave every change to the data until silver. This design tokenises before bronze, so the history kept longest holds the least.

There are two smaller differences. Google says data scientists often want raw bronze data to train models. Here they get tokenised bronze through authorised views, and no person or analyst reads raw. Google also names Cloud Storage as the bronze landing zone. In this design raw lands in BigQuery through Datastream, and on option B, bronze's data files sit in Cloud Storage under BigQuery's management.

What bronze is kept for, and what has to be kept anyway

After launch, bronze usually serves three purposes: replaying history when a rule changes or a bug is found, answering audit questions about what arrived and when, and debugging pipelines. Each needs a different amount of history. In the estates we've seen, retention is easier to justify per purpose than as one number for the whole layer.

GDPR's storage limitation principle in Article 5(1)(e) asks that personal data be kept in a form that permits identification of people "for no longer than is necessary for the purposes for which the personal data are processed" (European Parliament and Council, 2016). In California, section 1798.100(a)(3) of the Civil Code requires businesses to disclose how long they intend to keep each category of personal information or, if that isn't possible, the criteria used to decide it (California Legislature, n.d.a), and the regulations require the same in the notice at collection (California Privacy Protection Agency, n.d.a).

Retention runs the other way too. Orders, invoices, payments, and returns are often subject to tax and accounting retention, and both regimes allow for this. Under Article 17(3)(b) and (e), GDPR's right to erasure doesn't apply to the extent processing is necessary "for compliance with a legal obligation" under Union or Member State law, or "for the establishment, exercise or defence of legal claims" (European Parliament and Council, 2016). Section 1798.105(d) of the CCPA has an exception for complying with a legal obligation (California Legislature, n.d.b). So the first step in this design is to sort event families into two groups. In the running example:

Erased: saved carts, recently viewed items stored by the application, wishlists, and reviews. Marketing preferences are erased too, with one exception covered below.
Kept under a stated obligation: orders, order lines, invoices, payments, and returns, with shipment events following their orders. They're access-restricted and minimised, and recorded in the erasure log with their retention and basis. Where the obligation allows, they lose their token-to-person mapping.

Where an obligation requires identifying fields, such as the name and address on an invoice, those fields stay in the retained record under the record's own access and retention. You can still trace retained records to the person through the source system's own retained records, so they stay personal data for as long as those exist.

That split is an illustration. Which records a business has to keep, and for how long, depends on where it trades. This post doesn't set out any country's retention periods.

Three ways to make bronze erasable

Rewrite on delete. Keep identifiers in the events and delete or rewrite the person's rows wherever they are. It needs nothing extra at landing, but every erasure scans every table that might hold the person, and a missed table means a missed erasure.

Separate identifiers with a random token. Split identifiers from events soon after landing. It costs a split job and an identity table, and each erasure then starts from a short, known list of tokens.

Per-subject encryption. Encrypt each person's fields with their own key, and delete the key to make the data unreadable. Google describes deleting a key to render data unreadable as crypto-shredding, and BigQuery's AEAD functions can use keysets wrapped by Cloud KMS (Google Cloud, n.d.a). Each person's keyset would usually sit in a table. Deleting its row is then subject to that table's time travel, with fail-safe assumed as an upper bound.

If the keysets are wrapped, destroying the wrapping KMS key version means no keyset wrapped by it can be unwrapped again. That suits shredding a whole dataset more than one person. The key material is destroyed only after a scheduled period of 30 days by default, and Google says it can then remain in its systems for up to 45 days from the scheduled destruction time (Google Cloud, n.d.j).

Encryption also covers only the copies that stayed encrypted. Values decrypted into silver, gold, caches, or extracts aren't affected. When the KMS resource name and wrapped keyset are passed to KEYS.KEYSET_CHAIN as constants, they can appear in the diagnostic query plan, though Google says the keys are never exposed in plaintext and decrypting the wrapped keyset needs permission on the Cloud KMS key (Google Cloud, n.d.e). NIST classes cryptographic erase as a purge technique for media sanitisation (National Institute of Standards and Technology, 2025). Its conditions include, as NIST says ISO/IEC 27040 specifies, being able to sanitise all copies of the key. In our reading, keyset rows held in time travel are copies. We haven't found EU guidance that treats key deletion as erasure, so this design uses crypto-shredding, where it uses it at all, alongside deletion rather than in place of it.

Each fits a different context. Where your estate has many event tables and erasure requests arrive regularly, we've seen the token approach work well, with rewrite-on-delete for the few stores that have to hold identifiers. That's the design the rest of this post builds. If yours has few tables, or identifiers that can't leave the events, rewriting on delete may serve you better. Per-subject encryption suits retiring a whole dataset more than erasing one person.

The design in brief

These terms carry most of the post.

Source key: the customer's account ID in the source system.
Customer key: a pseudonymous join key, an HMAC of the normalised email address, computed by one key service. Guest orders and carts carry one even though they have no source key. The split gives each guest customer key an identity row of its own, with no source key, so erasure finds a guest's records by customer key. Retained records carry the token in place of the customer key, for guests too.
Token: a random value that stands in for the person on bronze events.
Identity table: the mapping from token to source key and customer key. Only the split and erasure jobs, and any named scanner, read or write it directly. Analysts and silver jobs get tokens and never the mapping.
Suppression entries: the keys of people who have asked for erasure, checked by the split job before anything new is written.
Retained store: the restricted home of event families kept under an obligation.
Erasure time: when the source system's erasure of the person took effect, fixed once by the erasure job and used to tell the person's old events from a returning customer's new ones.
Request intake, queue, and erasure log: the service account that records verified requests, the table where a request's keys wait for the erasure job, and the job's record of what it did, which holds no keys or tokens.
Hand-off table and kept list: where the erasure job passes a person's tokens, with a pass number, to the jobs that erase silver and gold; and the identity rows an obligation requires to be kept, with their basis.
Horizon: the computed date by which erased copies have left the pipeline's own stores.
Cutoff and landing check: the last split run whose identity rows count as the person's own, recorded by the erasure job at its first step; and the test that the source erasure has reached the raw dataset.

Figure 1 shows where one customer's record lands, with the backfill path drawn separately from CDC.

One customer, end to end

Take one account holder with a wishlist and one order. In the application's customers table, their row holds their email next to the customer key the key service returned. Their wishlist and order rows hold their source key.

Datastream copies changes to all three rows into the restricted raw dataset, leaving out the email. From there the split job does three things:

It creates an identity row, one per source key, with a new token, the source key, and the customer key.
It writes the wishlist events to bronze with the token in place of the account ID.
It writes the order to the retained store, also with the token, because orders are kept under an obligation.

An analyst who counts wishlist items by category reads a gold metric with no token in it. That metric is built from a silver table that carries the token.

A month later, the customer asks for erasure. Request intake adds their source key and customer key to the suppression entries, so no new parent event keyed to them reaches bronze. Child rows of records already in bronze are removed in steps 1 and 3. The source system erases the customer row on its own schedule, and the erasure job works through its steps:

It deletes the wishlist events by token (step 1).
It waits for any split run already under way, and checks again (steps 2 and 3).
It hands the token to the jobs that erase silver and gold (step 4).
Once they confirm, it deletes the identity row and clears the token from the retained order (step 5).
When the source erasure has landed, it deletes the customer's rows from raw (step 6).

The order stays in the retained store, linked to its invoice by order ID. It has no token and no mapping back to the person, though it keeps any identifying fields its obligation requires. Bronze, the identity table, and raw still hold the deleted rows for their time travel and fail-safe windows, and the post works out when those windows close.

The tables, and the SQL between the layers

Table 1. Tables in the listings. It groups them by layer. We explain each column where its rule comes up: the erasure time and cutoff in stage 2, and split runs in the split job section. The names are illustrative. Each erased family, such as carts, has tables of the same shape.

Table	Layer	Holds	Written by	Read by
`raw.*`	Raw	Source rows as received, minus the email	Datastream	Split, erasure job, cleanup job
`staging.wishlist_changes`, `staging.wishlist_item_changes`	Raw	Raw rows the split hasn't processed yet	Split, as new tables each run, named per run (shown without the suffix), each with a one-day expiry	Split
`identity.identity_map`	Keys	Token, source key, customer key, and the split run that set the key (`key_set_run`): one row per source key, or for guests one per customer key and token, so a returning guest gets a second row	Split; the erasure job deletes rows	Split, erasure job, export view
`suppression.source_keys`, `suppression.customer_keys`	Keys	Erased keys with their request ID, request time (`requested_at`), and erasure time (`erasure_ts`)	Intake appends; the erasure job fixes the erasure time	Split, erasure job, export view
`suppression.record_keys`	Keys	Suppressed record keys with their family, request ID, and how far each reaches (`suppress_through`)	Split, erasure job	Split, erasure job
`erasure.queue_keys`	Erasure	The request's queue row: its keys and record keys, one table row each, every one carrying the request's cutoff (`cutoff_run`) once step 1 records it	Intake, erasure job	Erasure job
`split_marks.marked_rows`	Raw	Raw rows finished with, by row ID, reason, record key, and request ID	Split, erasure job	Split, cleanup job, erasure job
`ops.split_runs`, `ops.committed_split_runs`, `ops.merge_watermarks`	Operations	Run numbers and start times only	Split; silver merge	Split staging, silver merge, erasure job
`bronze.wishlists`, `bronze.wishlist_items`	Bronze	Events with the token, record keys, the raw row each came from (`raw_row_id`), the split run that wrote them (`split_run`), and an ordering field for changes in the same instant (`change_seq`)	Split; the erasure and bronze retention jobs delete	Split, erasure job; silver through `bronze_views`
`bronze_views.wishlist_items`	Bronze	An authorised view, storing nothing	Infrastructure account	Silver merge
`silver.wishlist_items`, `silver.orders`, `silver.products`	Silver	Cleaned rows; items and orders carry the token	Silver merges (only the items merge is shown); silver's erasure job deletes items and clears order tokens	Gold view (items and products); other gold jobs
`gold.wishlist_items_by_category`	Gold	A view of counts, storing nothing and carrying no token	Infrastructure account	Dashboards
`erasure_handoff.tokens`, `erasure_confirm_silver.confirmations`	Erasure	Tokens with request ID and pass number; confirmations with no token	Erasure job; silver's erasure job	Downstream erasure jobs; erasure job

Listing 1 traces the wishlist from bronze to gold, in the shape of Google's steps. A MERGE rebuilds each changed item in silver from its bronze history. Along the way it converts timestamps to UTC and drops test accounts. A view then joins silver tables into a gold metric. You'll find the split that writes bronze in Listing 2, in the split job section, and the erasure DELETEs in Listing 3, in stage 2. Each block runs as its own script. Silver reads and moves its watermark inside its own transaction, so it needs no per-run values.

SQL

-- Two scripts: the merge into silver, run after each committed split run,
-- and the gold view, created once.

-- Bronze to silver: recompute each changed item from its full bronze history,
-- read through an authorised view, up to the latest committed split run.
-- Timestamps become UTC, and an item whose latest change is a delete, or
-- comes from a test account, leaves silver. The watermark is read and moved
-- inside the transaction, so this script needs no per-run values.
DECLARE last_merged INT64;
DECLARE merge_to INT64;

BEGIN TRANSACTION;

SET last_merged = (SELECT IFNULL(MAX(last_merged_run), 0)
                   FROM ops.merge_watermarks
                   WHERE target = 'silver.wishlist_items');
SET merge_to = (SELECT IFNULL(MAX(split_run), 0)
                FROM ops.committed_split_runs);

MERGE silver.wishlist_items AS s
USING (
  SELECT token, wishlist_id, product_id, change_type, added_at_utc, is_test
  FROM (
    SELECT e.token, e.wishlist_id, e.product_id, e.change_type,
           TIMESTAMP(e.added_at_local, e.time_zone) AS added_at_utc,
           IFNULL(e.is_test_account, FALSE) AS is_test,
           ROW_NUMBER() OVER (PARTITION BY e.wishlist_id, e.product_id
                              ORDER BY e.source_ts DESC, e.change_seq DESC)
             AS latest
    FROM bronze_views.wishlist_items AS e
    JOIN (
      SELECT DISTINCT wishlist_id, product_id
      FROM bronze_views.wishlist_items
      WHERE split_run > last_merged
        AND split_run <= merge_to) AS changed
      ON changed.wishlist_id = e.wishlist_id
     AND changed.product_id = e.product_id
    WHERE e.split_run <= merge_to)
  WHERE latest = 1
) AS b
ON s.wishlist_id = b.wishlist_id AND s.product_id = b.product_id
WHEN MATCHED AND (b.change_type IN ('DELETE', 'UPDATE-DELETE') OR b.is_test)
  THEN DELETE
WHEN MATCHED THEN
  UPDATE SET token = b.token, added_at_utc = b.added_at_utc
WHEN NOT MATCHED AND b.change_type NOT IN ('DELETE', 'UPDATE-DELETE')
  AND NOT b.is_test THEN
  INSERT (token, wishlist_id, product_id, added_at_utc)
  VALUES (b.token, b.wishlist_id, b.product_id, b.added_at_utc);

MERGE ops.merge_watermarks AS m
USING (SELECT 'silver.wishlist_items' AS target) AS t
ON m.target = t.target
WHEN MATCHED THEN UPDATE SET last_merged_run = merge_to
WHEN NOT MATCHED THEN INSERT (target, last_merged_run)
  VALUES (t.target, merge_to);

COMMIT TRANSACTION;

-- Silver to gold: a plain view that joins silver tables into a metric for
-- dashboards. It stores nothing and carries no token or key. The
-- infrastructure account creates it once and authorises it on silver's
-- dataset, so its readers need no access to silver; no job recreates it.
CREATE OR REPLACE VIEW gold.wishlist_items_by_category AS
SELECT DATE(w.added_at_utc) AS day_added, p.category, COUNT(*) AS items
FROM silver.wishlist_items AS w
JOIN silver.products AS p
  ON p.product_id = w.product_id
GROUP BY day_added, p.category;

Listing 1. Bronze to silver and silver to gold for one wishlist, in BigQuery SQL.

On option A, the gold view is authorised on silver's dataset, so dashboard readers don't need access to the silver tables that carry the token. If silver is a managed Iceberg table, the view can't be authorised. In that case gold is a table built by its own registered job.

A plain view like this gold one stores nothing, so it needs no erasure job of its own. Silver can only be rebuilt from bronze, and by the time the token is handed off, bronze holds nothing for it. Gold is different when it's a table, or, on option A, a materialised view built for speed (our reading of Google's "high-performance views"). Then it holds aggregates computed from the person's rows until it's refreshed or rebuilt. A table has its own time travel, and a materialised view needs the same check. That gold job registers for the hand-off and confirms only after the refresh. Where aggregates are published widely, this design also suppresses small counts, because a count of one can single someone out.

Silver tables built from retained families, such as orders, keep their rows at erasure. Silver's erasure job clears their token, as step 5 does in the retained store, so revenue in gold doesn't move. A later part of this series covers what gold readers see when published numbers do change.

The rest of the post fills in each part: keeping the email out of capture, what the split writes, the two stages of erasure and their edge cases, marketing preferences, the stores to account for, and the date. The design leans on access controls throughout, so we've gathered them in one section near the end.

Keeping the email address out at source

Our post on multi-cloud retail computed the customer key in one key service, the only identity with sign and verify permission on the secret key in Cloud KMS. It also set out the choice between replicating raw addresses into the data platform and keying at source. This part takes the keying-at-source route and adds the mechanism for an estate fed by Datastream.

The email gets this treatment because it's the input to the join key. Other identifiers, such as names, phone numbers, delivery addresses, and free text, do land in the raw dataset. From there the split moves them to the identity side, or keeps them in a retained record whose obligation needs them.

The source application stores the customer key the key service returns. That post allows records to be stored first and keyed asynchronously. Neither the database nor the application holds the secret key, though the application can still request keys. Because the source database holds each email next to its customer key, its change tables and backups are on the ledger. The hardening section covers the controls that keep the key service's key to itself.

Two controls keep the email out of the lakehouse, one for each way data leaves the source.

Change data capture. Datastream supports Azure SQL Database only with the change tables CDC method, and lists it at service tier S3 and above (Google Cloud, n.d.aa). The @captured_column_list parameter limits which columns reach a change table, and Microsoft documents it for Azure SQL Database where only some columns should be tracked "for privacy or performance reasons" (Microsoft, 2025b). Primary key columns, or the columns of the unique index named with @index_name, must be captured (Microsoft, 2026h), so the email mustn't be part of either.
Backfill. A Datastream backfill streams a snapshot of the existing data (Google Cloud, n.d.o). We don't rely on the capture instance's column list to govern it. The stream's API schema allows columns to be listed under its included and excluded objects (Google Cloud, n.d.v). We'd list the allowed columns under included objects and treat that list as the control for backfills. A column added later then stays out until someone adds it.

A table over 500 million rows can be backfilled only if it has a unique index with no nullable columns and every index column is in the stream (Google Cloud, n.d.aa). That's one more reason to keep the email out of such indexes.

When a stream starts, Datastream selects the latest capture instance, doesn't switch to one created afterwards, and skips a table whose selected instance is deleted (Google Cloud, n.d.aa). So the instance that excludes the email has to exist before the stream starts.

If CDC is already running with every column, you recreate the stream against the new instance, usually with a backfill, and only then drop the original instance. Dropping it removes its change table (Microsoft, 2026g), but backups taken before then still contain it. SQL Server allows two capture instances per table (Microsoft, 2026h), so adding a source column becomes a planned change. A later part covers it.

This design leaves Datastream's enable_ddl_support_for_ct label off. With the label on, Datastream creates and switches to a new capture instance when a column is added (Google Cloud, n.d.aa), and the documentation doesn't say which columns that instance captures. Without the label, columns added after the stream starts don't replicate, and that's what this design wants. Masking the column isn't a workaround either. Datastream doesn't support tables with masked columns, and where they are replicated, the values arrive unmasked (Google Cloud, n.d.aa). Datastream's own login on the source is a privileged account, and the hardening section covers it.

If you can't change the source application, one fallback is a BigQuery remote function that calls a batch endpoint of the key service from the split job. It comes at a cost. Because the email is captured, it stays in the change tables and the raw dataset for their full retention. The schema check allowlists that one column, and the split job keys it and doesn't carry it forward. The worked example in "Computing the date" assumes keying at source. Under the fallback, its change-table and raw chains would also be chains for the email itself. The hardening section covers the endpoint's controls.

The split job

Datastream writes to BigQuery in merge mode by default, which keeps no history of change events (Google Cloud, n.d.f). A lakehouse that replays from bronze needs that history, so the example estate uses append-only mode. There, an update becomes a single UPDATE-INSERT row, an update that changes the primary key becomes an UPDATE-DELETE and UPDATE-INSERT pair, and a delete becomes one DELETE row (Google Cloud, n.d.f). Datastream applies these through the Storage Write API over gRPC (Google Cloud, n.d.f). Rows written that way can be changed by UPDATE, DELETE, or MERGE, with no waiting period stated (Google Cloud, n.d.ac). The erasure steps below rely on that.

Datastream can write every table into a single dataset (Google Cloud, n.d.f). The example estate creates that raw dataset beforehand, because otherwise a new BigQuery dataset gives READER, WRITER, and OWNER access to project Viewers, Editors, and Owners, and OWNER access to its creator (Google Cloud, n.d.c). Its access list names only the split job, the erasure job, the raw cleanup job, and any named scanner. Datastream writes through its project-level service agent. Raw, bronze, the identity table, the suppression entries, and the other restricted stores all follow one isolation rule, set out in the hardening section.

Datastream applies partitioning only when it creates a table, and you configure it in the stream (Google Cloud, n.d.r). So ingestion-time partitioning goes in before the stream first runs. Expiration alone doesn't give a date: an expired partition is removed by a background process that "can take several days to complete", and that deletion isn't recorded in audit logs (Google Cloud, n.d.p). A deleted partition's data is then kept under time travel and fail-safe (Google Cloud, n.d.p).

So the erasure job deletes the person's raw rows itself, and a scheduled cleanup job deletes everything else older than the raw retention period. The cleanup job skips rows that the split is holding back or that are waiting for a parent. A hold older than a stated threshold pages someone.

Expiration stays as a backstop, set longer than the raw retention period plus that hold threshold. The split records the raw rows it has finished with in its marks table, split_marks.marked_rows, with a reason: 'copied' for retained-family rows it has sent to the retained store, 'suppressed' for rows it dropped, 'orphaned' for erased-family children whose parent never arrived, and 'identity' for rows from identity sources, such as the customers table, once their fields are on the identity side. On its next daily run, the cleanup job removes marked rows, including the erasure job's 'erased' rows. It deletes a mark once its raw row is gone. Expiration set as a dataset default affects newly created tables (Google Cloud, n.d.ad), so it goes in before the stream creates its tables, or per table afterwards with ALTER TABLE ... SET OPTIONS (Google Cloud, n.d.p).

The split job is written in BigQuery SQL, runs from Airflow on options A and B, and writes three things. Airflow starts each split run one at a time, with a run timeout. Each run begins with a task that runs as the split account. It takes the next run number from a counter table, ops.split_runs, and records its start time there. Numbers therefore increase by start time, and a retry gets a new number. Without that, Airflow would run the retry under the same run. That task also waits until INFORMATION_SCHEMA.JOBS shows no earlier split job still pending or running, so two runs never overlap. A run that times out is cancelled when Airflow kills its task, and a job timeout set on the BigQuery script ends it even if the worker running the task dies.

The identity table maps a random token to the source key and the customer key. Clear identifiers from erased families are kept only where a named purpose needs them (for example a coarsened delivery postcode). Each has its own retention, which the erasure job enforces, and the purpose reads it through its own authorised view. Free text counts as an identifier. Erasure deletes the whole identity row, these identifiers included.
Bronze events carry the token in place of both the direct identifiers and the customer's source key. Record keys such as order and cart IDs stay, because deduplication and child lookups need them. Those keys can be joined back to the person through the source system while its records are kept. That's one more reason to treat the events as personal data. The events are clustered by token, and only the split job inserts them. A bronze retention job deletes erased-family events once they pass the longest of bronze's purpose periods, so bronze isn't kept indefinitely for people who never ask.
The retained store holds event families kept under an obligation, including any identifying fields the obligation requires. They don't go to bronze. While the mapping exists, they carry the token like bronze events. Silver reads them through a minimised view that returns the token as null for any token on the kept list or in the erasure hand-off table (the view is authorised on both datasets). That way, a retained record merged into silver after silver's erasure job has run doesn't bring the token back. A retention job deletes records when their obligation period ends.

Child records, such as cart lines, wishlist items, order lines, and shipments, get their token from the token-keyed parent event. A child that arrives before its parent waits in the raw dataset. One whose parent never arrives is counted. In an erased family, it's then marked 'orphaned' for the cleanup job. In a retained family it goes to the retained store unlinked.

When the split suppresses a parent in an erased family, it writes that parent's record key, with its family (cart or wishlist) and the request ID from the matching entry, to a table of suppressed record keys kept with the suppression entries. A source-key match suppresses every event of that record. A customer-key match covers events up to the erasure time, or up to the request while that time isn't fixed. The erasure job's own entries, from step 1, cover every event. A suppressed parent's children are then dropped and counted separately. Every suppression entry carries its request ID.

Listing 2 is the split for the wishlist family. It writes a wishlist event to bronze only when no suppression entry covers it. For each wishlist it suppresses, it records the key and marks those rows and their items for the cleanup job. The other items take their token from the wishlist's latest event in bronze. Each split run's statements share one transaction, so a failed run leaves nothing in bronze. The listing leaves out the 'orphaned' marks, the retained-store writes with their 'copied' marks, and the identity rows. The split writes those identity rows under the same suppression check.

Each bronze row carries the raw_row_id it came from. A raw row therefore counts as processed once it's in bronze, the retained store, or the marks table, from a committed split run or the erasure job. Because that column links bronze back to raw, only holders of raw access can read it. The raw retention period is set shorter than bronze's shortest purpose period, so a row the bronze retention job deletes is never copied back. A halted cleanup job breaks that ordering as well as the raw dates. So it pages someone, and its delay moves the date the same way a halted split does.

The split's run number is a named query parameter, written with @ (Google Cloud, n.d.w). The run's first task takes it from ops.split_runs and passes it to the script.

SQL

-- Raw to bronze (split job). The split's staging tables hold raw rows it
-- hasn't processed yet: rows not in bronze, the retained store, or the marks
-- table under any reason, including the erasure job's 'erased' marks, which
-- carry no split run. They're written as new tables before this transaction,
-- named per run (shown here without the suffix), each with a one-day expiry.
-- Each run's statements run in one transaction, so a failed run leaves nothing
-- in bronze. Not shown: identity rows and their 'identity' marks, guest
-- records keyed by customer key, retained-store writes with their 'copied'
-- marks, and 'orphaned' marks for children whose parent never arrives.
BEGIN TRANSACTION;

-- Parents: write a wishlist event only when no suppression entry covers it.
-- Rows it skips stay in raw, either suppressed or held back until the
-- person's erasure time is known. Identity rows are written under the
-- same check.
INSERT INTO bronze.wishlists
  (token, wishlist_id, change_type, change_seq, source_ts, raw_row_id,
   split_run)
SELECT i.token, w.wishlist_id, w.change_type, w.change_seq, w.source_ts,
       w.raw_row_id, @split_run
FROM staging.wishlist_changes AS w
JOIN identity.identity_map AS i
  ON i.source_key = w.customer_id
WHERE NOT EXISTS (
    SELECT 1 FROM suppression.source_keys AS s
    WHERE s.source_key = w.customer_id)
  AND NOT EXISTS (
    SELECT 1 FROM suppression.customer_keys AS c
    WHERE c.customer_key = i.customer_key
      AND (c.erasure_ts IS NULL OR w.source_ts <= c.erasure_ts))
  AND NOT EXISTS (
    SELECT 1 FROM suppression.record_keys AS r
    WHERE r.record_family = 'wishlist'
      AND r.record_key = w.wishlist_id
      AND (r.suppress_through IS NULL OR w.source_ts <= r.suppress_through));

-- Record the key of each wishlist this run suppressed, with the covering
-- entry's request ID and how far it reaches: a source-key match covers
-- every row (NULL), a customer-key match covers rows up to the erasure
-- time, or up to the request while that time isn't fixed. Held-back rows
-- aren't suppressed yet. A source-key match counts even after the
-- person's identity row has been deleted.
INSERT INTO suppression.record_keys
  (record_family, record_key, request_id, suppress_through, split_run)
SELECT DISTINCT 'wishlist', w.wishlist_id,
       COALESCE(s.request_id, c.request_id),
       IF(s.source_key IS NOT NULL, NULL,
          IFNULL(c.erasure_ts, c.requested_at)),
       @split_run
FROM staging.wishlist_changes AS w
LEFT JOIN identity.identity_map AS i
  ON i.source_key = w.customer_id
LEFT JOIN suppression.source_keys AS s
  ON s.source_key = w.customer_id
LEFT JOIN suppression.customer_keys AS c
  ON c.customer_key = i.customer_key
 AND ((c.erasure_ts IS NOT NULL AND w.source_ts <= c.erasure_ts)
      OR (c.erasure_ts IS NULL AND w.source_ts <= c.requested_at))
WHERE s.source_key IS NOT NULL OR c.customer_key IS NOT NULL;

-- Mark the suppressed parent and child rows once each, for counting and for
-- the cleanup job.
INSERT INTO split_marks.marked_rows (raw_row_id, reason, record_key,
                                     request_id, split_run)
SELECT raw_row_id, 'suppressed', record_key, MIN(request_id), @split_run
FROM (
  SELECT w.raw_row_id, w.wishlist_id AS record_key, r.request_id, w.source_ts,
         r.suppress_through
  FROM staging.wishlist_changes AS w
  JOIN suppression.record_keys AS r
    ON r.record_family = 'wishlist' AND r.record_key = w.wishlist_id
  UNION ALL
  SELECT w.raw_row_id, w.wishlist_id AS record_key, r.request_id, w.source_ts,
         r.suppress_through
  FROM staging.wishlist_item_changes AS w
  JOIN suppression.record_keys AS r
    ON r.record_family = 'wishlist' AND r.record_key = w.wishlist_id)
WHERE suppress_through IS NULL OR source_ts <= suppress_through
GROUP BY raw_row_id, record_key;

-- The other children take their token from the parent's latest event in
-- bronze, under the same customer-key check as the parent. Items whose parent
-- hasn't arrived stay in raw for a later run. A child written after intake but
-- before its account's identity row has a customer key is caught by steps 1
-- and 3.
INSERT INTO bronze.wishlist_items
  (token, wishlist_id, product_id, change_type, change_seq, added_at_local,
   time_zone, is_test_account, source_ts, raw_row_id, split_run)
SELECT p.token, w.wishlist_id, w.product_id, w.change_type, w.change_seq,
       w.added_at_local, w.time_zone, w.is_test_account, w.source_ts,
       w.raw_row_id, @split_run
FROM staging.wishlist_item_changes AS w
JOIN (
  SELECT wishlist_id, token
  FROM (
    SELECT wishlist_id, token,
           ROW_NUMBER() OVER (PARTITION BY wishlist_id
                              ORDER BY source_ts DESC, change_seq DESC)
             AS latest
    FROM bronze.wishlists)
  WHERE latest = 1) AS p
  ON p.wishlist_id = w.wishlist_id
WHERE NOT EXISTS (
    SELECT 1 FROM identity.identity_map AS i
    JOIN suppression.customer_keys AS c
      ON c.customer_key = i.customer_key
    WHERE i.token = p.token
      AND (c.erasure_ts IS NULL OR w.source_ts <= c.erasure_ts))
  AND NOT EXISTS (
    SELECT 1 FROM suppression.record_keys AS r
    WHERE r.record_family = 'wishlist'
      AND r.record_key = w.wishlist_id
      AND (r.suppress_through IS NULL OR w.source_ts <= r.suppress_through));

INSERT INTO ops.committed_split_runs (split_run) VALUES (@split_run);

COMMIT TRANSACTION;

Listing 2. Raw to bronze for the wishlist family, in BigQuery SQL: one split run, in one transaction.

A schema check runs on the raw tables every time. If a column's name suggests an email address, the run stops and pages someone. The same happens when a content check finds email addresses in a column not already routed to the identity side. Other unrecognised columns go to the identity side. A halted split job pages someone well inside the raw retention period, and it also holds up erasures, as step 6 below shows.

Downstream, silver jobs and analysts work with tokens. Some uses need the cross-system customer key, such as the campaign audiences our earlier post described leaving the platform as customer keys. For those, a named export job resolves tokens through one authorised view. The view returns token and customer key only, and it leaves out an erased person's own identity rows. The hardening section gives its rule. Whatever the job exports goes on the ledger with its recipient.

The events are still personal data. As our earlier post set out, Recital 26 says pseudonymised data that could be attributed to a person with additional information should be considered information on an identifiable person (European Parliament and Council, 2016). The EDPB's 2025 coordinated enforcement action found that some controllers relying on anonymisation in place of deletion applied only basic pseudonymisation or partial masking, which "would not fulfil the requirements of the GDPR regarding deletion" (European Data Protection Board, 2026).

California's regulations, by contrast, let a business comply with a deletion request by deidentifying or aggregating the information (California Privacy Protection Agency, n.d.b), so the bar differs from one regime to another. Section 1798.140(m) of the CCPA defines deidentified information as information that "cannot reasonably be used to infer information about, or otherwise be linked to, a particular consumer", provided the business also takes reasonable measures, commits publicly to keep it deidentified, and binds recipients by contract (California Legislature, n.d.d). As we read it, tokenised events with a live mapping and record keys wouldn't meet that. Where this post says a source system anonymises a record, it means the record can no longer identify the person. The split shortens the search for a person's events. The erasure job below still deletes them.

Erasure in two stages

Figure 2 shows the two stages.

Stage 1, at the request

Only the request-intake service account records verified requests. IAM can't tell whether a request is verified, so intake's application makes that check. It appends the person's keys to the suppression entries first. The queue row goes last, and only after the entries' appends are acknowledged, so the erasure job never sees a request whose entries aren't there.

The keys come from the source. At intake, the source application uses its existing path to the key service. It sends every address it holds or has held for the person and verified for the account, plus the verified address the requester supplied. It passes the returned customer keys, computed under every enabled key version, to intake. Stored addresses matter because the requester may type a different one, and older guest orders may carry an older address. The person's source key and customer keys then go on the suppression entries and in the queue. You need both, because a guest order carries a customer key but no account's source key.

The source erasure itself runs on its own clock. When it finishes, the source tells intake, and intake appends that signal to the queue. The source also clears the customer key from the records it keeps, so a later backfill can't link them to the person again.

The split job checks every event against the entries. The rules apply top to bottom, and the first match wins:

Event	Condition	Action
A change row from an identity source, such as the customers table, including its DELETE or anonymising UPDATE-INSERT row at source erasure	Any time	Identity fields go to the identity side, except that no identity row is written or updated for an erased source key or a covered customer key; the raw row is marked 'identity' either way
The DELETE row, or anonymising UPDATE-INSERT row, for the source erasure	Erased family, any timestamp	Suppressed
Other erased-family event for an erased source key	Dated after the erasure time	Suppressed, and raised with the source system's owner as a sign of a reused key or an incomplete source erasure
Erased-family event with an erased source key	Any time	Suppressed, and the source key's entry is kept while retained events for that key can still arrive
Retained-family event with an erased source key	Any time	Sent to the retained store without a token and with the source key and customer key dropped, with no new identity row
Erased-family event with an erased customer key	Dated at or before the request, or at or before the erasure time once it's fixed	Suppressed
Retained-family event with an erased customer key	Dated at or before the request, or at or before the erasure time once it's fixed	Sent to the retained store without a token, with the customer key and source key dropped
Any event with an erased customer key	Dated after the request, erasure time not yet known	Held back in raw
Event with the same customer key	Dated after the erasure time	Treated as a returning customer and given a new token

Two of these rules carry a cost. The source system's own retained records keep the account's source key for as long as the source keeps them, often years. Source-key entries last that long too, and it's worth saying so in the retention notice. Customer-key entries reveal that a returning customer was erased before, so they expire once no pre-erasure event can still land. That point depends on change-table retention, landing lag, and any backfill window. The erasure job deletes suppression entries when their retention ends.

Fixing the erasure time

The erasure job (its steps follow below) fixes the erasure time once. It does so on its first daily sweep after both of these are true: the landing check has passed for every table where the person has rows, and step 1 has recorded the request's cutoff. The job takes the latest source timestamp among the person's erasure change rows and sets it on their customer-key suppression entries, updating intake's rows. On the same entries it records the latest split run that finished before the erasure time was written. The split and the export view read that value. Both values stay on the entries after the queue row is gone.

Sometimes there's nothing to erase at source, because the source purged the person's records long ago while bronze kept them. In that case the source reports the time it checked, and the erasure job accepts that time as the erasure time once a heartbeat row has landed past it.

The landing check needs a watermark that always advances, because a table's latest source timestamp stops moving when nothing in it changes. The check runs only on tables where the person has rows. It reads Datastream's source timestamp metadata, which Google defines as the time the record changed on the source (Google Cloud, n.d.l).

Table	The check waits for
A table where the source erasure deleted the person's rows	Their DELETE rows
A table where the source erasure anonymised the person's rows	The change that clears the customer key, which touches a captured column and so produces a change row
Any other table where the person has rows	A heartbeat row, written every few minutes to a small captured table holding no personal data, landed with a source timestamp past the source's signal

Stage 2, the erasure job

The job picks up new requests on a daily sweep. From there it runs each request as a state machine that wakes when one of its waits ends. Fixing the erasure time and step 6 both happen on the daily sweep.

Check that every key in the queue row is present in the suppression entries. If any is missing, stop and page someone. Then record the request's cutoff: the latest split run that started before this step began. A run starts when its number is taken, and run IDs increase by start time. Look up the person's tokens. Copy their erased-family parent record keys (cart and wishlist IDs) from bronze into the queue and into the suppressed record keys, each with its family and no date limit, because every event of the person's own records goes. Copy their order IDs from the retained store into the queue only, since order lines and refunds must still reach the retained store. Then DELETE their events in erased families, marking their raw rows 'erased' (Listing 3).
Wait until every split run that began before step 1's record-key insert has finished. Split runs are serialised and time out. The job waits until INFORMATION_SCHEMA.JOBS shows no job by the split's service account, started before the insert, still pending or running, and until at least one split-run timeout has passed since the insert. That view's job states are pending, running, and done (Google Cloud, n.d.m). A timed-out run is cancelled, and its jobs can take a while to stop, so the wait ends only when the view shows them done. If it hasn't after a stated margin past the timeout, the job pages someone and keeps waiting. For this, the erasure job can read job metadata in the split's project.
Look up the tokens and record keys again, to catch anything a run in flight wrote. Copy any new record keys and run a confirming DELETE. If this found anything new, repeat steps 2 and 3 until a lookup finds nothing new.
Write the person's tokens to the hand-off table with a pass number. Each registered downstream erasure job (silver, gold, and feature stores) reads the table and writes its confirmation to its own table. The confirmation holds a request ID and pass number only. Without this step, those jobs would have no way to find the person once the mapping is gone.
Once every registered job has confirmed the current pass, or after a stated timeout that pages someone, delete the mapping and clear the token from the person's retained records. Where the obligation requires the mapping, it moves to the kept list with its basis instead. In that case the retained records keep their token, the minimised view that silver reads returns that token as null, and the mapping never passes through the export view. The hand-off rows go only when every job has confirmed, so a job that timed out can still find the person. They also always go after the retained token is cleared or moved to the kept list, so a merge into silver in between can't bring the token back.
Wait until step 5 has finished, the erasure time is fixed (as above), and the split has processed every row that landed before it. Then, in one transaction, DELETE the person's raw rows that steps 1 and 3 or the split have already processed, along with the marks of the rows it deletes. After that, delete the queue row. If the erasure time isn't fixed within a stated period of the request, the job pages someone, because the queue row keeps the person's keys until step 6.

Step 6 finds raw rows four ways: by source key, by customer key on rows dated at or before the erasure, by the record keys in the queue, and by the suppressed record keys carrying the request ID. That catches retained families' raw rows too, once the split has copied them to the retained store. Rows that land after the split's last processed point are left to the split. It routes retained rows to the retained store and marks suppressed ones for the cleanup job.

The job takes only a request ID as a parameter. It processes only request IDs that appear in intake's own record of verified requests, and it joins its DELETEs on the queue table, as Listing 3 shows. BigQuery reruns a DML statement that conflicts with a concurrent one up to three times (Google Cloud, n.d.ac), and the job retries beyond that. The bronze mark-and-DELETE pair runs as a transaction. BigQuery cancels it on a conflict, so the job retries the whole transaction. Silver's erasure job and the merge into silver both run in transactions, and BigQuery cancels conflicting transactions (Google Cloud, n.d.q), so each retries a cancelled transaction. The erasure log records the request ID, timestamps, row counts, and the computed horizon. It holds no keys or tokens.

Listing 3 shows two of the DELETEs erasure needs. The bronze DELETE in steps 1 and 3 is keyed by request ID alone, so no key or token appears in its query text. In the same transaction it marks the person's raw rows 'erased', so the split doesn't stage them again and step 6 can find them. Silver's DELETE is keyed by the tokens the erasure job hands off. For a reopen entry, the job runs the same bronze pair, but takes the token from the reopen table for that request in place of the identity lookup. A token found late can belong to a guest whose key was set after the cutoff, and neither branch of the lookup matches such a key. The erasure jobs run as Airflow tasks and pass the request ID and pass number as query parameters.

SQL

-- Two scripts: the erasure job's bronze DELETE, and silver's erasure job.

-- Erasure job, steps 1 and 3: delete the person's erased-family events from
-- bronze, and mark the raw rows behind them 'erased' in the same transaction,
-- so the split still counts those raw rows as processed and step 6 can delete
-- them. Raw rows that never reached bronze are left to step 6. The same pair
-- runs on bronze.wishlists and each erased-family table. The only parameter is
-- the request ID, and the keys come from the queue table, so no key or token
-- appears in the query text. Step 1 records cutoff_run before this runs; a
-- NULL cutoff would make the customer-key branch match nothing. For a reopen
-- entry the job runs the same pair with the token taken from the reopen table
-- in place of the identity lookup.
BEGIN TRANSACTION;

INSERT INTO split_marks.marked_rows (raw_row_id, reason, record_key,
                                     request_id, split_run)
SELECT e.raw_row_id, 'erased', e.wishlist_id, @request_id, NULL
FROM bronze.wishlist_items AS e
WHERE e.token IN (
  SELECT i.token
  FROM identity.identity_map AS i
  JOIN erasure.queue_keys AS q
    ON q.request_id = @request_id
  WHERE i.source_key = q.source_key
     OR (i.customer_key = q.customer_key
         AND i.key_set_run <= q.cutoff_run));

DELETE FROM bronze.wishlist_items AS e
WHERE e.token IN (
  SELECT i.token
  FROM identity.identity_map AS i
  JOIN erasure.queue_keys AS q
    ON q.request_id = @request_id
  WHERE i.source_key = q.source_key
     OR (i.customer_key = q.customer_key
         AND i.key_set_run <= q.cutoff_run));

COMMIT TRANSACTION;

-- Silver's erasure job, after step 4: delete by the hand-off tokens for this
-- request, up to the current pass, and confirm the pass in the same
-- transaction, so a failed DELETE is never confirmed. Silver tables from
-- retained families keep their rows and lose the token, so gold's revenue
-- doesn't move.
BEGIN TRANSACTION;

DELETE FROM silver.wishlist_items AS s
WHERE s.token IN (
  SELECT h.token
  FROM erasure_handoff.tokens AS h
  WHERE h.request_id = @request_id
    AND h.pass_number <= @pass_number);

UPDATE silver.orders AS o
SET token = NULL
WHERE o.token IN (
  SELECT h.token
  FROM erasure_handoff.tokens AS h
  WHERE h.request_id = @request_id
    AND h.pass_number <= @pass_number);

INSERT INTO erasure_confirm_silver.confirmations (request_id, pass_number)
VALUES (@request_id, @pass_number);

COMMIT TRANSACTION;

Listing 3. The erasure DELETEs in bronze and silver, in BigQuery SQL.

The customer-key branch of the bronze DELETE matches only identity rows whose key was set by split runs up to the request's cutoff (the latest run that started before the erasure job's first step). Rows set up to the cutoff can't belong to a returning customer. New tokens for that key wait until the erasure time is fixed, and that never happens before the cutoff is recorded. The edge cases below explain the ordering.

Late arrivals and edge cases

These cases are rare, but each one needs a defined path.

A token found late. If a row arrives before its customer key, the suppression check runs again when the key arrives, under the same date rules, so a returning customer's row isn't reopened. On a match, the split writes the reopen entry before it sets the key on the identity row, so the export view never sees the key unexcluded.

A match, or any token found after step 3, goes with its request ID to a reopen table kept alongside the suppression entries. The erasure job processes reopen entries whether or not the request is still open. If it's still open, the token joins the request under a new pass number. If step 6 has finished, the job rebuilds the request's queue row from the suppression entries that carry its request ID. If those entries have expired, the reopen pages someone and is handled by hand. Either way, the job deletes the token's events and marks their raw rows, hands the token downstream, deletes its mapping once the pass is confirmed, and logs a new horizon that includes those raw rows.

Returning customers and the customer-key lookups. Each identity row records the split run that set its customer key. The lookups match customer keys only on rows set by runs up to the request's cutoff. Intake's entries were visible before step 1 began, so every later run saw them. Each of those runs suppressed or held back the person's events, and any identity row it created for that key belongs to a returning customer. The cutoff can include a few runs that had already seen the entries. That only widens the search for the person's own rows, because the hold-back rule stops new identity rows for that key until the erasure time is fixed, and the erasure time is never fixed before the cutoff is recorded.

Rows that land after step 6. As we understand it, Datastream reads each change table separately, so the heartbeat shows that capture and landing are moving, but it doesn't prove that every other table has caught up. If a pre-erasure row lands after step 6, the split suppresses and marks it, and the cleanup job deletes it on its next run. Any such row moves the date, because its chain runs from when it landed. It's recorded against the request with a new horizon.

Backfilled rows. Datastream labels backfill events with a read_method starting sqlserver-backfill (Google Cloud, n.d.l), and as we understand it they don't carry the original change time. So the split treats backfilled erased-family rows for an erased key as pre-erasure. While customer-key entries live, that also drops a returning customer's new carts and wishlists from a backfill. You can accept that cost, or avoid it by not backfilling during that window.

Key rotation. The lakehouse holds no emails, so it can't re-key anything itself. A rotation of the customer-key secret isn't started while any request is open before step 6, because the suppression entries hold keys computed under the old version and a rotated key on a guest's identity row would no longer match them. The source recomputes its stored customer keys through the key service, and the split updates identity rows from the resulting change rows. It matches a guest's rotated key to the existing token by record key, so the row keeps its token and the erasure job can still find it. The row also keeps the run recorded as having first set the key. An old key version stays enabled until every customer-key suppression entry made under it has expired, because the addresses behind those entries are gone.

Marketing preferences

Article 21(2) and (3) give people the right to object to direct marketing at any time, after which their data "shall no longer be processed for such purposes" (European Parliament and Council, 2016). In the US, CAN-SPAM bars sending commercial email more than 10 business days after an opt-out request, unless the person later gives affirmative consent (United States Congress, n.d.). So when preferences are erased, a forward-looking do-not-contact marker stays, and we'd record a basis for keeping it. The UK ICO's direct marketing guidance describes a suppression list as keeping just enough information to respect people's preferences in future (Information Commissioner's Office, 2026).

The marker is an HMAC of the email under a separate suppression key that only the key service can use. It's stored in the key project, where only the key service can read it. The source application asks the key service to create the marker at erasure, and the consent system asks it to remove one when someone gives fresh consent. The sending system asks about an address and gets back allow or suppress. Customer-key rotation doesn't touch the marker, and the suppression key can be rotated without the erased addresses, as the hardening section describes. The marker covers email-keyed channels, and other channels need their own suppression.

The ledger

Figure 1 shows the main stores, and the ledger lists all of them across two tables. The first covers the pipeline's own stores, which the worked example below computes, with backups listed as beyond use. The second covers stores outside the computed date, each with its owner. Some are kept by design, some are owned elsewhere, and silver, gold, and other downstream stores are covered in later parts.

Stores within the horizon (backups listed as beyond use)

Where it sits	How it's removed	What sets when it's gone
CDC change tables	Azure SQL Database's CDC scheduler	Retention (three days by default) and hourly cleanup, plus measured cleanup lag (Microsoft, 2025b)
Restricted raw dataset	Erasure job, step 6; cleanup job for marked and older rows	Landing, erasure time fixed, split catch-up, job timing, then time travel and fail-safe
Identity table	Erasure job, step 5	Job timing and downstream confirmation, then time travel and fail-safe
Token hand-off table	Erasure job, step 5, once every downstream job confirms	Job timing and downstream confirmation, then time travel and fail-safe
Tokens in retained records	Erasure job, step 5 (token cleared)	Job timing and downstream confirmation, then time travel and fail-safe
Bronze events	Erasure job, steps 1 and 3; bronze retention job for the rest	Job timing, then time travel and fail-safe
Erasure queue table	Erasure job, step 6	The same as the raw dataset
Split job staging tables	Expiry set on each table	Split processing of the erasure rows, expiry, then time travel and fail-safe
Split marks	Erasure job, step 6, with the raw rows it deletes; cleanup job for the rest, once the marked raw rows are deleted	Job timing, then time travel and fail-safe
Reopen table	Erasure job, once the reopened request is done	Job timing, then time travel and fail-safe, for a request that reopens
Point-in-time restore backups	Expire on schedule (beyond use until then)	Restore window of 1 to 35 days (1 to 7 on Basic), 7 by default; files kept up to a week longer (Microsoft, 2026a)

Stores outside the horizon: kept by design, or owned elsewhere

Where it sits	Owner and rule
Source application tables	The source system's erasure or anonymisation
Original all-column change table	Removed when its capture instance is dropped (Microsoft, 2026g)
Long-term retention backups	Up to 10 years, if enabled (Microsoft, 2026f). Can't be deleted while any immutability applies: an unlocked time-based policy has to be disabled first, a locked one keeps each backup until its retention ends, and a legal hold, a preview feature, keeps it until the hold is removed. An immutable backup stays after its retention expires until every immutability is removed (Microsoft, 2026d). Beyond use throughout
Source query history and audit logs	Settings on the source database
Intake's record of verified requests	Kept by design, with its own basis, apart from the ticketing system; links the request ID to the requester
Ticketing	Kept by design, with its own basis
Suppression entries	Customer-key entries until no pre-erasure event can land; source-key entries while retained events can arrive; record-key entries until no child of that parent can land; then time travel and fail-safe
Do-not-contact markers	Until fresh consent; kept by design, with a stated basis
Kept list	Identity rows an obligation requires, for the obligation's period, then time travel and fail-safe; reached only through a named authorised view
Downstream confirmation tables	Request IDs and pass numbers only, kept with the erasure log; linkable to a person only through intake's record
Erasure log	Kept by design; holds no keys or tokens
Retained store	The obligation's period, recorded per family
Table snapshots and Iceberg tags	Google lists table snapshots as unsupported for managed Iceberg tables, so on option B this covers native tables only. Snapshot expiry, set early enough that expiry plus time travel and fail-safe, which we assume apply as for any table, ends by the computed horizon
Table clones, materialised views, search indexes, dev copies	Their owners, or rebuilt after the erasure
Query text in `INFORMATION_SCHEMA.JOBS`	Google's retention for the view, covered in a later part
Audit logs and log sinks	Log bucket retention and each sink's settings
Cached and anonymous query results	BigQuery's own expiry, covered in a later part
Error logs and dead-letter tables	Retention set per log bucket or table
Notebook outputs	Their owners
Profiling and data quality results	Retention set per tool
Sensitive Data Protection findings	Retention set per job
Silver and gold tables	Downstream erasure jobs reading the hand-off table, then time travel and fail-safe; covered in later parts
Exports, extracts, and audiences	Their owners, with each recipient listed; exported customer keys stay linkable to the person wherever they're kept
ML training sets and feature stores	Downstream erasure jobs reading the hand-off table, or their owners
Cross-region replicas	Replication of the delete, then the replica's own windows
Processors and other recipients	Notified under contract; their contractual deletion terms

A few notes apply to particular rows.

Time travel and fail-safe. BigQuery keeps changed and deleted data for a time travel window of two to seven days, seven by default, and Google says deleted data is then kept for a further seven days of fail-safe that can't be configured or extended (Google Cloud, n.d.i). Google describes fail-safe for deleted or replaced tables and deleted partitions, and doesn't list row-level DML, so for rows removed by DELETE we count it as a conservative upper bound.
Option B. Data files are garbage-collected after the time travel window (Google Cloud, n.d.b). We take it that deleted files then sit in the bucket's soft delete, since Google recommends keeping that policy for these buckets. Soft delete is seven days by default and configurable from 7 to 90 days, or disabled (Google Cloud, n.d.z). Google doesn't document fail-safe for these tables, so the worked example leaves it out pending confirmation from Google. A dropped option B table leaves its files in the bucket (Google Cloud, n.d.b). So in this design, personal-data tables on option B aren't dropped, including for rebuilds and dev copies, unless the bucket path is cleaned up afterwards.
Locks. A Bucket Lock retention policy stops objects being deleted until they reach the policy's age, and a locked policy can't be removed or shortened (Google Cloud, n.d.d). In this design, personal data stays out of locked buckets. Per Google, option B tables don't support buckets with Bucket Lock at all (Google Cloud, n.d.b).
Snapshots. Our post on multi-site pipelines suggested month-end snapshots or tags for finance. Either they expire early enough that their own time travel and fail-safe end by the computed horizon, or they're taken only of gold tables with no personal data.
Iceberg statistics. On option C, and possibly option B (Google doesn't say), Iceberg's default metrics mode stores lower and upper bounds for columns, truncated to 16 for string and binary values (Apache Iceberg, n.d.). By default it covers the first 100 columns, taking top-level fields first and then each nested struct in turn (Apache Iceberg, n.d.). A truncated bound can be enough of a name or delivery address to identify someone. The storage comparison covers the per-column setting that turns this off.
Queries. Query text can hold the values someone searched for. Google's AEAD guidance warns that query text may be logged, including any plaintext keyset in it, and recommends parameterised queries (Google Cloud, n.d.a). The erasure job's request-ID design applies the same habit.
Emails already in bronze. Many teams will already have emails in raw and bronze. Removing them means rewriting those tables without the column, or rebuilding them from a keyed copy. Each rewrite starts its own time travel and fail-safe clock, so the horizon for that one-off removal counts from the rewrite.

Computing the date

With the stores listed, you can compute the completion date for the pipeline's own stores. Listing 4 models each store as a chain of stages whose waits add up. Some chains start when the request is accepted, and others when the source system erases the record. A chain can also wait for a stage in another chain to end, and then starts at whichever comes later.

The horizon is the latest end across the chains. The result names the chain that sets it and that chain's longest adjustable stage, which is usually the place to start. Fixed windows, such as fail-safe, can't be that stage. When chains tie, the one listed first is named, so list them in a deliberate order. Backups get their own list, because each expires on its own schedule and needs re-erasure if it's restored. Records kept by design or obligation are listed with their retention rule and basis, so the date doesn't read as the moment every trace is gone.

Python

from collections.abc import Sequence
from dataclasses import dataclass
from datetime import datetime, timedelta
from enum import Enum

class Start(Enum):
    REQUEST = "request"          # clock starts when the erasure request is accepted
    SOURCE_ERASURE = "source"    # clock starts when the source system erases the record

@dataclass(frozen=True)
class Stage:
    name: str
    wait: timedelta              # worst-case wait before data moves to the next stage
    adjustable: bool = True      # False for fixed windows such as BigQuery fail-safe

@dataclass(frozen=True)
class Chain:
    store: str
    start: Start
    stages: tuple[Stage, ...]    # waits add up along a chain
    after: tuple[tuple[str, str], ...] = ()  # (store, stage): also wait for that stage to end

@dataclass(frozen=True)
class Backup:
    name: str
    retention: timedelta         # until the last backup taken before source erasure is gone

@dataclass(frozen=True)
class Kept:
    name: str
    retention_rule: str
    basis: str                   # by design, legal obligation, or fixed retention

@dataclass(frozen=True)
class Horizon:
    deleted_by: datetime         # live stores: the latest chain end
    store: str                   # the chain that sets the date
    stage: str | None            # that chain's longest adjustable stage (first on ties)
    beyond_use: tuple[tuple[str, datetime], ...]
    kept: tuple[Kept, ...]

def _stage_ends(
    chains: Sequence[Chain], requested: datetime, source_erased: datetime
) -> dict[str, list[datetime]]:
    by_store = {c.store: c for c in chains}
    if len(by_store) != len(chains):
        raise ValueError("store names must be unique")
    ends: dict[str, list[datetime]] = {}
    visiting: set[str] = set()

    def resolve(chain: Chain) -> list[datetime]:
        if chain.store in ends:
            return ends[chain.store]
        if chain.store in visiting:
            raise ValueError(f"dependency cycle at {chain.store}")
        visiting.add(chain.store)
        t = requested if chain.start is Start.REQUEST else source_erased
        for store, stage in chain.after:     # start at the latest of the clock and each dependency
            dep = by_store.get(store)
            names = [s.name for s in dep.stages] if dep else []
            if stage not in names:
                raise ValueError(f"{chain.store} waits for unknown stage {store}: {stage}")
            t = max(t, resolve(dep)[names.index(stage)])
        out = []
        for s in chain.stages:
            if s.wait < timedelta(0):
                raise ValueError(f"negative wait in {chain.store}: {s.name}")
            t += s.wait
            out.append(t)
        visiting.discard(chain.store)
        ends[chain.store] = out
        return out

    for c in chains:
        resolve(c)
    return ends

def horizon(
    requested: datetime,
    source_erased: datetime,
    chains: Sequence[Chain],
    backups: Sequence[Backup] = (),
    kept: Sequence[Kept] = (),
) -> Horizon:
    if requested.utcoffset() is None or source_erased.utcoffset() is None:
        raise ValueError("times must be timezone-aware")
    if not chains or any(not c.stages for c in chains):
        raise ValueError("need at least one chain, each with at least one stage")
    if any(len({s.name for s in c.stages}) != len(c.stages) for c in chains):
        raise ValueError("stage names must be unique within a chain")
    if any(b.retention < timedelta(0) for b in backups):
        raise ValueError("negative backup retention")
    stage_ends = _stage_ends(chains, requested, source_erased)
    ends = [(stage_ends[c.store][-1], c) for c in chains]
    end, chain = max(ends, key=lambda pair: pair[0])  # first chain wins on ties
    lever = None
    for s in chain.stages:       # first maximum wins, as in the Rust version
        if s.adjustable and (lever is None or s.wait > lever.wait):
            lever = s
    beyond_use = tuple((b.name, source_erased + b.retention) for b in backups)
    return Horizon(end, chain.store, lever.name if lever else None, beyond_use, tuple(kept))

Rust

use chrono::{DateTime, TimeDelta, Utc};
use std::collections::{HashMap, HashSet};

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum Start {
    Request,       // clock starts when the erasure request is accepted
    SourceErasure, // clock starts when the source system erases the record
}

#[derive(Debug, Clone)]
pub struct Stage {
    pub name: String,
    pub wait: TimeDelta,  // worst-case wait before data moves to the next stage
    pub adjustable: bool, // false for fixed windows such as BigQuery fail-safe
}

#[derive(Debug, Clone)]
pub struct Chain {
    pub store: String,
    pub start: Start,
    pub stages: Vec<Stage>, // waits add up along a chain
    pub after: Vec<(String, String)>, // (store, stage): also wait for that stage to end
}

#[derive(Debug, Clone)]
pub struct Backup {
    pub name: String,
    pub retention: TimeDelta, // until the last backup taken before source erasure is gone
}

#[derive(Debug, Clone)]
pub struct Kept {
    pub name: String,
    pub retention_rule: String,
    pub basis: String, // by design, legal obligation, or fixed retention
}

#[derive(Debug, Clone)]
pub struct Horizon {
    pub deleted_by: DateTime<Utc>, // live stores: the latest chain end
    pub store: String,             // the chain that sets the date
    pub stage: Option<String>,     // that chain's longest adjustable stage (first on ties)
    pub beyond_use: Vec<(String, DateTime<Utc>)>,
    pub kept: Vec<Kept>,
}

type Ends = HashMap<String, Vec<DateTime<Utc>>>;

fn resolve<'a>(
    chain: &'a Chain,
    by_store: &HashMap<&'a str, &'a Chain>,
    clocks: (DateTime<Utc>, DateTime<Utc>),
    ends: &mut Ends,
    visiting: &mut Vec<&'a str>,
) -> Result<Vec<DateTime<Utc>>, String> {
    if let Some(e) = ends.get(&chain.store) {
        return Ok(e.clone());
    }
    if visiting.contains(&chain.store.as_str()) {
        return Err(format!("dependency cycle at {}", chain.store));
    }
    visiting.push(&chain.store);
    let mut t = match chain.start {
        Start::Request => clocks.0,
        Start::SourceErasure => clocks.1,
    };
    // start at the latest of the clock and each dependency
    for (store, stage) in &chain.after {
        let dep = by_store.get(store.as_str());
        let idx = dep.and_then(|d| d.stages.iter().position(|s| &s.name == stage));
        let (Some(dep), Some(idx)) = (dep, idx) else {
            return Err(format!("{} waits for unknown stage {store}: {stage}", chain.store));
        };
        t = t.max(resolve(dep, by_store, clocks, ends, visiting)?[idx]);
    }
    let mut out = Vec::with_capacity(chain.stages.len());
    for stage in &chain.stages {
        if stage.wait < TimeDelta::zero() {
            return Err(format!("negative wait in {}: {}", chain.store, stage.name));
        }
        t += stage.wait;
        out.push(t);
    }
    visiting.pop();
    ends.insert(chain.store.clone(), out.clone());
    Ok(out)
}

/// Taking `DateTime<Utc>` means a time without a zone can't be passed in.
pub fn horizon(
    requested: DateTime<Utc>,
    source_erased: DateTime<Utc>,
    chains: &[Chain],
    backups: &[Backup],
    kept: &[Kept],
) -> Result<Horizon, String> {
    if chains.is_empty() || chains.iter().any(|c| c.stages.is_empty()) {
        return Err("need at least one chain, each with at least one stage".into());
    }
    let unique = |c: &Chain| c.stages.iter().map(|s| &s.name).collect::<HashSet<_>>().len() == c.stages.len();
    if !chains.iter().all(unique) {
        return Err("stage names must be unique within a chain".into());
    }
    if backups.iter().any(|b| b.retention < TimeDelta::zero()) {
        return Err("negative backup retention".into());
    }
    let by_store: HashMap<&str, &Chain> = chains.iter().map(|c| (c.store.as_str(), c)).collect();
    if by_store.len() != chains.len() {
        return Err("store names must be unique".into());
    }
    let (mut ends, mut visiting) = (Ends::new(), Vec::new());
    let mut latest: Option<(DateTime<Utc>, &Chain)> = None;
    for chain in chains {
        let stage_ends = resolve(chain, &by_store, (requested, source_erased), &mut ends, &mut visiting)?;
        let end = *stage_ends.last().expect("stages is not empty");
        match latest {
            Some((t, _)) if end <= t => {}
            _ => latest = Some((end, chain)),
        }
    }
    let (deleted_by, chain) = latest.expect("chains is not empty");
    // first maximum wins, as in the Python version (max_by_key would keep the last)
    let lever = chain
        .stages
        .iter()
        .filter(|s| s.adjustable)
        .fold(None::<&Stage>, |best, s| match best {
            Some(b) if b.wait >= s.wait => Some(b),
            _ => Some(s),
        });
    Ok(Horizon {
        deleted_by,
        store: chain.store.clone(),
        stage: lever.map(|s| s.name.clone()),
        beyond_use: backups
            .iter()
            .map(|b| (b.name.clone(), source_erased + b.retention))
            .collect(),
        kept: kept.to_vec(),
    })
}

Listing 4. Computing the horizon from chains of stages. Waits add along a chain, a chain can wait for a stage in another, and the horizon is the latest chain end.

The table runs the example estate through Listing 4. The erasure job runs daily, split runs time out after six hours, BigQuery's time travel window is at its default, rows take about an hour to land in the raw dataset, and the source system erases the record two days after the request. Microsoft gives no SLA for when changes reach the change tables (Microsoft, 2025b), so the landing lag and the change tables' cleanup lag are measured figures, watched against thresholds. The days count from the request.

Chain	Starts at	Stages	Ends
Bronze events (native)	Request	Job cadence 1 day, split-run timeout 6 hours, time travel 7 days, fail-safe 7 days	Day 15, plus 6 hours
Bronze events (option B variant)	Request	Job cadence 1 day, split-run timeout 6 hours, time travel 7 days, garbage-collection lag (measured), soft delete 7 days (fail-safe left out pending Google)	Day 15, plus 6 hours and the measured garbage-collection lag
Identity table, hand-off table, and retained-record tokens	Request	Job cadence 1 day, split-run timeout 6 hours, downstream confirmation timeout 1 day, time travel 7 days, fail-safe 7 days	Day 16, plus 6 hours
Raw landing (timing only)	Source erasure (day 2)	Landing 1 hour, erasure time fixed on the next job run 1 day, split catch-up 12 hours (a run already under way, then one more)	Day 3, plus 13 hours
Restricted raw dataset	The later of raw landing (day 3, plus 13 hours) and the end of step 5 (day 2, plus 6 hours)	Job cadence 1 day, time travel 7 days, fail-safe 7 days	Day 18, plus 13 hours
Erasure queue table	The same as the raw dataset	The same stages as the raw dataset	Day 18, plus 13 hours
Split staging tables	The end of raw landing (day 3, plus 13 hours)	Table expiry 1 day, time travel 7 days, fail-safe 7 days	Day 18, plus 13 hours
CDC change tables	Source erasure (day 2)	Retention 3 days, cleanup interval 1 hour, cleanup lag (measured)	Day 5, plus 1 hour and the measured cleanup lag
Point-in-time restore backups	Source erasure (day 2)	Restore window 7 days, files up to 7 days more, listed as beyond use	Restorable until day 9, files gone by day 16

Here the raw dataset, the queue table, and the split staging tables tie and set the horizon. The raw dataset is named because it comes first, and time travel is its longest adjustable stage. The staging tables are there because split runs still process the person's erasure rows after the source erasure. With a two-day table expiry, they would set the horizon on their own.

Step 6 can't run until the split has processed the landed erasure rows and step 5 has finished, so the raw and queue chains start at the later of those two points. The raw landing row is there only to time the first. The erasure rows land, the next daily sweep fixes the erasure time, and then the split processes what it was holding back. The split runs continuously, with each run starting as the last ends. The stages are summed as independent upper bounds, so the table overcounts by up to the split catch-up whenever that finishes before the next sweep, as it does here.

A longer downstream confirmation timeout pushes both chains later: with three days, the horizon moves to day 19, plus 6 hours. Listing 4 names a stage only from the chain that sets the date. When a dependency drives the date, as a long confirmation timeout would, that dependency's stage is the better place to start.

Some waits fall outside the table. The hand-off table's date holds only if every downstream job confirms within the timeout. After a timeout, its rows stay until the late job confirms, and the request's horizon is logged as provisional until then. A slow cancellation can stretch even the first round's wait to the margin past the timeout, which adds the margin to the bronze and identity chains. The table takes it as zero. A halted cleanup job holds raw rows the same way a halted split does, so its delay is added to the raw chain. Each extra round of steps 2 and 3 adds a split-run timeout, and its margin if cancellation is slow, to the bronze and identity chains, and to raw when step 5 is its later start. A halted split job holds up step 6 and moves the date for every open request.

Shortening the time travel window to the two-day minimum, where the recovery trade-off allows, moves every native BigQuery chain five days earlier. A shorter window also shortens how far back you can recover from a mistake, which a later part on recovery takes up.

Getting the source system to erase sooner helps only while raw landing is the later start. Here raw landing ends a day and seven hours after step 5, so an earlier source erasure gains up to that much before the confirmation timeout and job cadence become the levers. For the same reason, fixing the erasure time as soon as the rows land, rather than on the next daily run, would take a day off whenever raw landing is the later start, as here.

A soft delete period at the 90-day maximum would make option B set the horizon.

Our tests check the horizon in the worked example, the effect of a longer confirmation timeout, and three properties on randomised chains without dependencies: a longer wait never brings the date forward, removing a chain never pushes it back, and a later source erasure never brings it forward. Both suites also run the same tie case. The Python needs version 3.10 or later, and the Rust uses chrono.

Hardening the design

The sections above lean on access controls at almost every step. This section gathers them in one place. They're written for the example estate, and a later part on access goes further into Entra ID and BigQuery.

The key service and its keys

Only the key service can use the customer-key secret, under three conditions. Its rate limits, and logging without the addresses, are rules in its own code.

No person holds basic roles, Cloud KMS Admin, or IAM admin roles on the key's project or above it as standing access, and any elevation is just-in-time with approval. Basic roles don't separate managing keys from using them, and Cloud KMS Admin can set IAM policy on keys and key rings (Google Cloud, n.d.s), so either lets its holder grant themselves use of the key.
The one automation account that manages the key and deploys the key service works only through changes that pass two-person review. No person can impersonate it, create or upload keys for it, or run its pipeline outside those changes.
No person can impersonate the key service's service account, create keys for it, or deploy code that runs as it. Service account key creation and key upload are blocked by organisation policy, and nobody holds Organization Policy Administrator as standing access.

The same conditions apply to the suppression key, and the third applies to the batch endpoint's service account under the fallback, which the deployment account deploys. The first also applies to every isolated project, every project that holds a job or intake service account, the Airflow project, and the projects holding the deployment and infrastructure accounts, at that level and above. The last applies to every job and intake service account in this post.

The source database and Datastream's login

Creating a capture instance needs db_owner (Microsoft, 2026h), and Google's setup for Azure SQL Database adds Datastream's own login to db_owner and db_denydatawriter, with a password (Google Cloud, n.d.g). That login, and the connection profile holding its password, can create an all-column capture instance or read every column. Members of db_owner can manage fixed database role membership (Microsoft, 2026e), so we'd assume the login can also remove itself from db_denydatawriter.

Google's example enables CDC with no gating role (Google Cloud, n.d.g). Naming one limits which other users can read change data. It doesn't restrict db_owner members, including Datastream's own login. db_owner members (or sysadmin on SQL Server) can read changes outright; anyone else needs column permissions and, where one is set, the gating role (Microsoft, 2025a).

So automation generates the password and stores it in Secret Manager. A SQL Server connection profile can reference a secret in place of a password (Google Cloud, n.d.t), and Google's guide grants the Secret Manager Secret Accessor role to the Datastream service account (Google Cloud, n.d.ae), which we'd grant on that one secret only. The guide doesn't show the reference for Azure SQL Database specifically, so it's one of the checks in the testing section. The secret sits in the raw project. No person knows the password, and no person holds secret-access roles on that secret or its project as standing access. The automation account that sets it uses a SQL principal that can alter Datastream's login. That makes it admin-equivalent on the source, so it works only through reviewed changes, can't be impersonated, and can't read secret versions after writing them.

Alerts on that login come from auditing configured at the Azure resource level, outside the database because, as we understand it, a db_owner member could drop a trigger inside it. The SQL Security Manager role can change server and database audit settings (Microsoft, 2026b), and Owner and Contributor can manage all resources (Microsoft, 2026c). SQL Server Contributor's actions include Microsoft.Sql/servers/*. As we read them, those cover the server's administrators, though the role's excluded actions keep it from changing audit settings (Microsoft, 2026b). As we understand it, the server's admin login and its Microsoft Entra admin can do everything Datastream's login can.

So the key project's first condition applies here too. No person holds the admin login, the Entra admin, any of those Azure roles, User Access Administrator, or Role Based Access Control Administrator (Microsoft, 2026c) as standing access, and elevation is just-in-time. If the Entra admin is a group, it has no standing members or owners. The Entra directory roles that could change that group are a matter for the later part on access. Deletion in the audit destination is restricted in the same way. Restoring a backup is also a server-level action, so the same list, with SQL DB Contributor added, is what keeps backups beyond use. A database restore raises an alert.

The isolation rule

The raw dataset, split staging, the split's marks table, the identity table, the suppression entries, the erasure queue, the reopen and token hand-off tables, the downstream confirmation tables, bronze, the retained store, intake's record of verified requests, the kept list, and every authorised view's dataset follow one isolation rule. The queue, the suppression entries, the hand-off table, intake's record, and the kept list are on that list because each amounts to a list of erased or kept people. The do-not-contact markers sit in the key project, under its conditions. The retained store is there because it holds identifying fields the obligations require.

Creation. Each dataset is created by an infrastructure-as-code service account that sets the access list at creation, so no person picks up a creator's OWNER access. That account can create, change, and grant access to tables, but a custom role leaves it without read or write access to the data, so reaching the data through it needs an access change. Impersonating it and running its pipeline are restricted, and merges to its repository need two-person review.
Projects. Each sits in a project where no principal other than the named job service accounts and the infrastructure account holds BigQuery or basic roles at or above the project. A scanner that reads these datasets, such as Sensitive Data Protection, is named in the access list like any other job.
The raw project. Raw sits in its own project, apart from the identity and suppression datasets, holding only the raw dataset and the Datastream secret. The Datastream API is enabled only there, and the Restrict Resource Service Usage organisation policy constraint, which supports Datastream (Google Cloud, n.d.x), blocks it elsewhere. That matters because each project's Datastream agent gets BigQuery access in its own project. Datastream's permissions for writing to BigQuery come from the Datastream Service Agent role (Google Cloud, n.d.f), and the isolation rule gives that agent no access in the other projects. A role held at project level applies to every dataset in the project. So the agent is the only exception there, and split staging and the remote function's dataset live in the split's own project.
Streams and connection profiles. The connection profile logs in as a db_owner member, so anyone who can create or edit a stream can send every column, the email included, to a dataset of their choosing. Those permissions follow the key project's first condition: no standing access, and just-in-time elevation only.
Deployment. One deployment account deploys the job code. It can run code as the jobs it deploys, so it effectively has their data access, though it holds no table-data permission itself. It's held under the same rules: two-person review, no impersonation, no key creation or upload, and a pipeline that runs only from reviewed merges.
Views. Every view authorised on an isolated dataset, for bronze, the retained store, the identity table, or the export job, has its SQL kept in infrastructure as code with review.
Option B buckets. On option B the data files sit in Cloud Storage, where Storage roles reach them without any BigQuery permission. Google has the table's connection service account hold Storage Object User and Storage Legacy Bucket Reader on the bucket, and recommends restricting write and delete permissions for most users (Google Cloud, n.d.b). We'd go further: only that service account holds object access, and restoring soft-deleted objects is held as tightly. Each Iceberg connection sits in the isolated project of the tables it serves, and permission to create tables through it is held by the infrastructure account alone, so nobody can define a second table over the bucket's files.

Job roles, and what IAM can and can't enforce

The job accounts get custom roles: the table-data permission where they write, read access only where they read, and no permission to create, delete, or reconfigure tables. The roles are granted at dataset level for raw and split staging, whose tables are created at run time, and at table level elsewhere. The split job is the one exception to the table rules, with table creation in its staging dataset. The gold view in Listing 1 is created once by the infrastructure account, and no scheduled job recreates it.

Airflow is an execution path, so it falls under the deployment rule. Each job, the split, silver, any gold tables, the erasure job, silver's erasure job, and the cleanup and retention jobs, runs as Airflow tasks under its own service account, and no task runs with a person's own credentials.

Airflow's environment service account can act as every job account, and impersonates each job's account for that job's tasks. So it's held like the deployment account: no person can impersonate it or create or upload keys for it, and only the deployment account writes to the environment's DAG bucket or updates the environment. It is the only principal that can act as job accounts, and it holds no BigQuery role. The Airflow roles that can trigger DAGs or edit connections are just-in-time, and so are the Google Cloud permissions that reach the same account another way: running Airflow commands through the environment's API, and any access to the cluster that runs its workers.

If you already run your SQL in Dataform or dbt, the same rules apply: one service account per job, no run under a person's own credentials, and only the deployment account changing what runs. In Dataform that means a repository and custom service account per job. Under its strict act-as mode, anyone who creates or updates a repository or workflow configuration, creates a workflow invocation, or updates a release configuration needs permission to act as the effective service account, and the Dataform service agent needs the Service Account User and Token Creator roles on each job account (Google Cloud, n.d.af).

The Dataform service agent then takes the environment account's place as the principal that can act as job accounts, and changes to repositories or configurations are alerted like environment updates. With dbt, we'd give each job its own target and service account and keep the profiles with the deployment account, and the runner's own service account takes that place.

We'd assume, pending a test, that one table-data permission covers inserts, updates, and deletes alike. So "only the split job inserts bronze rows" rests on who holds that permission, plus alerts on which statements each account runs. The table below lists them. The retention, bronze retention, and cleanup jobs need read access, permission to run jobs, and the table-data permission on their tables, because a DELETE runs as a query job and reads the rows it matches.

Request intake's role holds the table-data permission on its own tables, the queue, the suppression entries, and its record of verified requests, and nothing else. With no permission to run jobs anywhere, it can, as we understand it, append through the Storage Write API and can't read, load, copy, or run DML. Those appends go to a stream rather than a query job, so there's no query text or job parameter to hold a key. The Storage Write API's change mode, which can delete, requires a primary key on the destination table (Google Cloud, n.d.ab), so none of intake's tables declares one.

Reads during the time travel window

Reading bronze during the time travel window is also reading rows the erasure job has deleted. As we understand it, a plain query with FOR SYSTEM_TIME AS OF needs only read access and permission to run jobs. Snapshotting a table's past state needs bigquery.tables.createSnapshot among the source permissions, with BigQuery Data Viewer listed among the roles that qualify, plus permission to create tables in a destination dataset (Google Cloud, n.d.h).

So analysts and silver jobs read bronze through authorised views and hold no read access on the tables themselves. Principals with direct table access, including the jobs named in this post, can still read deleted rows until the time travel window ends.

The export view

The export view returns token and customer key only. It compares split runs, the same clock as Listing 2. It leaves out an identity row whose source key has a suppression entry, and one with a matching customer-key entry if the entry has no erasure time yet, or if the row's key was set by a run no later than the one recorded when the erasure time was fixed. It also leaves out any token in the reopen table. A returning customer's later rows are included; marketing consent is checked separately. Because the view reads the suppression entries, it's also authorised on their dataset, and uses them only to exclude. The export job can read the view and nothing else in that dataset.

The remote-function fallback

Calling a remote function needs bigquery.routines.get on its dataset and the BigQuery Connection User role (Google Cloud, n.d.ah), granted to the split job's service account on that one connection. BigQuery calls the endpoint through the connection, in batches, and each request carries documented sessionUser and caller fields (Google Cloud, n.d.ah).

Those fields sit in the request body, so the endpoint can trust them only once it knows the caller. The batch endpoint runs as its own Cloud Run service whose only invoker is the connection's service account, and it forwards each batch to the key service, so it never uses the key itself. It checks that sessionUser is the split job's service account, and caps rows per job with headroom for retries.

The marker key

The suppression key can be rotated without the erased addresses. Cloud KMS rotation leaves earlier versions active (Google Cloud, n.d.n). Automatic rotation is supported only for keys with the ENCRYPT_DECRYPT purpose, and other keys have no primary version (Google Cloud, n.d.u), so this HMAC key is rotated by hand and the key service names the version it signs with. New markers use the new version, and the check computes the HMAC under every enabled version, writing a new-version marker when it matches an old one.

Checks alone won't move every marker, because an address that's never checked keeps its old-version marker. So re-marking runs in bulk, as its own named caller with its own rate limit and a log of each run: every address on the sending system's list goes through the check after a rotation, and again before any list import. The key service can't check that a caller already holds an address it asks about, so each caller's rate limit and logging are what limit it.

Google advises disabling a compromised version and revoking access to it as soon as possible (Google Cloud, n.d.n). Disabling this one would stop checks against existing opt-outs. IAM can't be set on a key version (Google Cloud, n.d.s), so limiting the old version to checks is a rule in the key service's code. On compromise, revoke the misused access first, then run the bulk re-marking straight away.

Markers for addresses on no list can't be moved that way. Email addresses are guessable, so a leaked key plus the marker store would show which of them opted out. The choice is then between keeping the old version enabled for checks, with that exposure, and disabling it and losing those opt-outs, and whichever you choose gets recorded.

Alerted events

Detection backs every rule above that IAM can't enforce on its own. Data Access audit logs are disabled by default for every service except some BigQuery services (Google Cloud, n.d.k), so they're enabled for Secret Manager on the project that holds the Datastream secret.

Event	Where it's seen
IAM policy change on a key, key ring, the Datastream secret, the Iceberg connection, isolated project, folder, or the organisation; on an isolated table; on the option B bucket; on the key service's or batch endpoint's Cloud Run service; or on a job, intake, key-service, batch-endpoint, or deployment service account; any change to organisation policy	Cloud Audit Logs
A Datastream stream or connection profile created or changed	Cloud Audit Logs
Change to a dataset access list, or to an authorised view's definition	Cloud Audit Logs
DML, load, or copy job on an isolated table from a principal outside its named writers; table deletion, settings change, or truncating write	Cloud Audit Logs
A named writer running the wrong statement: an insert into bronze from anyone but the split job, or into raw from anyone but Datastream's service agent; an insert into the retained store from anyone but the split job; a delete from bronze by anyone but the erasure and bronze retention jobs; a delete from the identity table by anyone but the erasure job, or an insert by anyone but the split job; a delete from the retained store by anyone but the retention job, or an update by anyone but the erasure job; an update or delete in raw by anyone but the erasure and cleanup jobs; an insert into the split's marks table by anyone but the split and erasure jobs, or a delete by anyone but the erasure and cleanup jobs; an insert into `ops.committed_split_runs` by anyone but the split job; an update or delete on the suppression entries by anyone but the erasure job; an insert or append into the suppression entries, queue, or intake's record by anyone but their named writers; any update to bronze; an update to the identity table by anyone but the split and erasure jobs	Cloud Audit Logs, for job-based writes; Storage Write API appends depend on one of the checks in the testing section
A primary key added to any of intake's tables	Cloud Audit Logs
An update to the Airflow environment; an Airflow command run through the environment's API; a grant of Service Account User or Token Creator on a job account	Cloud Audit Logs
A write to the Airflow environment's DAG bucket	Cloud Storage Data Access logs, enabled for that bucket
A change to Airflow connections or variables	Airflow's own audit log
A table created in an isolated dataset, or through an Iceberg connection, by anyone but the infrastructure account, the split job in its staging dataset, or Datastream in raw	Cloud Audit Logs
A query with `FOR SYSTEM_TIME AS OF`, or a snapshot, clone, or copy job, reading bronze, raw, the identity table, the retained store, the suppression entries, the queue, or the hand-off table	Cloud Audit Logs
A read of the Datastream password secret by any principal other than Datastream's service agent	Secret Manager Data Access logs
A new version of that secret	Cloud Audit Logs (Admin Activity)
A new capture instance on a captured table, or a change to database role membership	Azure SQL auditing at resource level, with the relevant audit action groups enabled
A change to audit settings, the admin password, or the Entra admin, or a new database on the server, which is how a restore appears	Azure Activity Log
A change to the Entra admin group's members or owners	Microsoft Entra audit logs
A password change on Datastream's login	Azure SQL auditing at resource level, with the relevant audit action groups enabled
An email-like column in raw, a halted split, a hold past its threshold, landing lag past its threshold, or a downstream confirmation timeout	Pipeline monitoring

What a data engineer still has to test

Google's and Microsoft's documentation, and an AI agent working from it, can take you a long way with this design. What neither can determine is how the services behave where the documentation is unclear or covers a point only in part, and whether this design's own scripts behave as intended. That part is the data engineer's job: run each check below against real services in a non-production project before anyone relies on the design. The checks fall into five groups.

How Datastream behaves on this source. Confirm that the stream's included-objects column list governs backfills as well as CDC, and that DELETE rows carry the deleted row's values. Check that the source timestamp on SQL Server change rows is the transaction's commit time, and find which Datastream field orders changes that share a timestamp (carried into bronze as change_seq in Listing 2). See how backfilled rows can be told apart in BigQuery and how their timestamps are set. Confirm that Datastream reads change tables independently, so one table landing doesn't prove another has, and that rows it writes over gRPC can be changed by DML straight away, which depends on it using that path. Check that partition expiration behaves as set on the tables it creates.
Whether the access boundaries hold. Test that an Azure SQL Database connection profile accepts a Secret Manager password reference, with Secret Accessor granted to the Datastream service account on that secret. Point a stream at the identity or suppression projects and expect it to fail, and expect enabling the Datastream API outside the raw project to be refused. Confirm that Datastream's login can remove itself from db_denydatawriter. Check whether one table-data permission covers insert, update, and delete in BigQuery. Check that intake's role allows nothing beyond Storage Write API appends, and what audit logs record for those appends on every isolated table. Confirm that a plain FOR SYSTEM_TIME AS OF query needs nothing beyond read access and permission to run jobs, and that time travel can't be applied through an authorised view.
Whether the jobs run the way the design assumes. Check that Airflow's Google provider runs each script as a BigQuery job under the job account it impersonates, with the run number passed as a query parameter, and find the narrowest grant on the environment service account that allows it. Confirm that INFORMATION_SCHEMA.JOBS shows a running split script, by the account Airflow impersonates, within a bounded delay. A job timeout on a BigQuery script should end the whole script, cancelling a timed-out Airflow task should stop its script, and the view should show it done before Airflow's retry starts. Measure how often the erasure job's bronze transaction cancels a concurrent split run's transaction, which shows up as a failed split run and a retry. Run Listings 1 to 3, with their variables substituted, as a BigQuery dry run against real tables, and confirm they pass.
What happens to deleted data afterwards. Confirm that an expired table snapshot goes through time travel and fail-safe like any table. Fail-safe for option B tables is a question for Google.
The split's rules and its race with an erasure. Test a child arriving before its parent, a customer key arriving after its row, and the DELETE row after source erasure. Then test the races: a long split run started before step 1's record-key insert, one that creates a new identity row, a suppression append that lands after the queue row, an erasure time that would be fixed before step 1, and a key rotation requested while an erasure is open.

Proving it, and the clocks that apply

The EDPB's report recommends that controllers map personal data and its storage locations. It also describes, as good practice, systems that generate records automatically as proof of deletion (European Data Protection Board, 2026). The ledger gives the map, and the erasure log, checked against the horizon, gives the evidence. Since BigQuery doesn't record the automatic deletion of expired partitions in its audit logs (Google Cloud, n.d.p), the erasure job and the raw cleanup job write their own records.

Several clocks apply, and they measure different things. Article 17(1) requires erasure "without undue delay", and Article 12(3) separately requires the controller to tell the person what action was taken within one month of the request, extendable by two further months where necessary (European Parliament and Council, 2016). Under section 1798.130(a)(2)(A), the CCPA gives businesses 45 days to respond to a verifiable request, extendable once by a further 45 days when reasonably necessary, if the consumer is told within the first 45 days (California Legislature, n.d.c). CAN-SPAM's 10 business days apply to email opt-outs (United States Congress, n.d.). Other US state privacy laws set their own deadlines, which this post doesn't cover.

The computed horizon is about when recoverable copies are gone, so we'd report it alongside those clocks. In this part it covers the pipeline's own stores only, which makes it a lower bound for the platform. We wouldn't give it to the person as a completion date until the silver, gold, and other downstream chains are added, which later parts do.

Backups are handled differently. The UK ICO's guidance, which the ICO notes is under review, asks that backup data be put "beyond use" even if it can't be overwritten immediately (Information Commissioner's Office, n.d.). The California regulations let a business delay deletion from archived or backup systems until that system is restored to an active system or next accessed or used for a sale, disclosure, or commercial purpose (California Privacy Protection Agency, n.d.b).

For backups, the EDPB report recommends that controllers verify and be able to demonstrate that erasure has been carried out. It found that many controllers had no specific procedure for backups, and that some relied on automatic deletion measures or on retention periods applied to backups (European Data Protection Board, 2026). A beyond-use list with a re-erasure rule on restore gives that procedure a concrete form, and the response to the person can say that backups are kept beyond use until they expire.

A checklist for the next request

For a token-based design like this one, these are worth having in place before the next erasure request arrives:

Event families sorted into erased and retained, with the retained ones' basis written down.
The email kept out of capture and out of backfills, with the capture instance created before the stream.
Raw rows deleted by the erasure job, a cleanup job for the rest, and expiration as a backstop.
The restricted stores under one isolation rule, with per-entry retention for suppression, and do-not-contact markers under their own key.
The erasure job's order: suppress at the request; then delete, wait, look up again until nothing new, hand tokens downstream, delete the mapping once downstream jobs confirm, and clear raw once the erasure time is fixed and processed.
A ledger row for every store, each with an owner, and a named request-intake identity that alone records verified requests, with the erasure job acting only on those.
A computed horizon and a beyond-use list for every request, recorded in the erasure log.
The data engineer's checks run in a test project.

A useful first exercise is to take one of last quarter's erasure requests, run it through the ledger, and check that every row has an owner.

Our Data & AI practice is glad to go through a ledger like this with your platform team.

This post describes engineering approaches. It isn't legal advice, and which obligations apply to a particular business is a question for its counsel.

References

Apache Iceberg, n.d. Configuration. Apache Iceberg documentation. Available at: https://iceberg.apache.org/docs/latest/configuration/ [Accessed 9 October 2026].

California Legislature, n.d.a. Civil Code section 1798.100. California Legislative Information. Available at: https://leginfo.legislature.ca.gov/faces/codes_displaySection.xhtml?lawCode=CIV&sectionNum=1798.100 [Accessed 9 October 2026].

California Legislature, n.d.b. Civil Code section 1798.105: Consumers' Right to Delete Personal Information. Text as published by Justia, 2025 California Code. Available at: https://law.justia.com/codes/california/code-civ/division-3/part-4/title-1-81-5/section-1798-105/ [Accessed 9 October 2026].

California Legislature, n.d.c. Civil Code section 1798.130: Notice, Disclosure, Correction, and Deletion Requirements. Text as published by Justia, 2025 California Code. Available at: https://law.justia.com/codes/california/code-civ/division-3/part-4/title-1-81-5/section-1798-130/ [Accessed 9 October 2026].

California Legislature, n.d.d. Civil Code section 1798.140. California Legislative Information. Available at: https://leginfo.legislature.ca.gov/faces/codes_displaySection.xhtml?lawCode=CIV&sectionNum=1798.140 [Accessed 9 October 2026].

California Privacy Protection Agency, n.d.a. Cal. Code Regs. tit. 11, § 7012: Notice at Collection of Personal Information. Text as published by the Legal Information Institute, Cornell Law School. Available at: https://www.law.cornell.edu/regulations/california/11-CCR-7012 [Accessed 9 October 2026].

California Privacy Protection Agency, n.d.b. Cal. Code Regs. tit. 11, § 7022: Requests to Delete. Text as published by the Legal Information Institute, Cornell Law School. Available at: https://www.law.cornell.edu/regulations/california/11-CCR-7022 [Accessed 9 October 2026].

European Data Protection Board, 2026. 2025 Coordinated Enforcement Action: Implementation of the right to erasure by controllers. Adopted 10 February 2026. Available at: https://www.edpb.europa.eu/system/files/2026-02/edpb_cef-report_2025_right-to-erasure_en.pdf [Accessed 9 October 2026].

European Parliament and Council, 2016. Regulation (EU) 2016/679 of the European Parliament and of the Council of 27 April 2016 on the protection of natural persons with regard to the processing of personal data and on the free movement of such data, and repealing Directive 95/46/EC (General Data Protection Regulation). Official Journal of the European Union, L 119, 4 May, pp. 1-88. Available at: https://eur-lex.europa.eu/eli/reg/2016/679/oj [Accessed 9 October 2026].

Google Cloud, n.d.a. AEAD encryption concepts. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/aead-encryption-concepts [Accessed 9 October 2026].

Google Cloud, n.d.b. Apache Iceberg managed tables. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/biglake-iceberg-tables-in-bigquery [Accessed 9 October 2026].

Google Cloud, n.d.c. Basic roles and permissions. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/access-control-basic-roles [Accessed 9 October 2026].

Google Cloud, n.d.d. Bucket Lock. Cloud Storage documentation. Available at: https://docs.cloud.google.com/storage/docs/bucket-lock [Accessed 9 October 2026].

Google Cloud, n.d.e. Column-level encryption with Cloud KMS. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/column-key-encrypt [Accessed 9 October 2026].

Google Cloud, n.d.f. Configure a BigQuery destination. Datastream documentation. Available at: https://docs.cloud.google.com/datastream/docs/configure-bigquery-destination [Accessed 9 October 2026].

Google Cloud, n.d.g. Configure an Azure SQL database for CDC. Datastream documentation. Available at: https://docs.cloud.google.com/datastream/docs/configure-azure-sqlserver [Accessed 9 October 2026].

Google Cloud, n.d.h. Create table snapshots. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/table-snapshots-create [Accessed 9 October 2026].

Google Cloud, n.d.i. Data retention with time travel and fail-safe. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/time-travel [Accessed 9 October 2026].

Google Cloud, n.d.j. Destroy and restore key versions. Cloud Key Management Service documentation. Available at: https://docs.cloud.google.com/kms/docs/destroy-restore [Accessed 9 October 2026].

Google Cloud, n.d.k. Enable Data Access audit logs. Cloud Logging documentation. Available at: https://docs.cloud.google.com/logging/docs/audit/configure-data-access [Accessed 9 October 2026].

Google Cloud, n.d.l. Events and streams. Datastream documentation. Available at: https://docs.cloud.google.com/datastream/docs/events-and-streams [Accessed 9 October 2026].

Google Cloud, n.d.m. JOBS view. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/information-schema-jobs [Accessed 9 October 2026].

Google Cloud, n.d.n. Key rotation. Cloud Key Management Service documentation. Available at: https://docs.cloud.google.com/kms/docs/key-rotation [Accessed 9 October 2026].

Google Cloud, n.d.o. Manage backfill for the objects of a stream. Datastream documentation. Available at: https://docs.cloud.google.com/datastream/docs/manage-backfill-for-the-objects-of-a-stream [Accessed 9 October 2026].

Google Cloud, n.d.p. Manage partitioned tables. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/managing-partitioned-tables [Accessed 9 October 2026].

Google Cloud, n.d.q. Multi-statement transactions. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/transactions [Accessed 9 October 2026].

Google Cloud, n.d.r. Partition and cluster BigQuery tables. Datastream documentation. Available at: https://docs.cloud.google.com/datastream/docs/partitioning-and-clustering [Accessed 9 October 2026].

Google Cloud, n.d.s. Permissions and roles. Cloud Key Management Service documentation. Available at: https://docs.cloud.google.com/kms/docs/reference/permissions-and-roles [Accessed 9 October 2026].

Google Cloud, n.d.t. REST Resource: projects.locations.connectionProfiles. Datastream API reference. Available at: https://docs.cloud.google.com/datastream/docs/reference/rest/v1/projects.locations.connectionProfiles [Accessed 9 October 2026].

Google Cloud, n.d.u. REST Resource: projects.locations.keyRings.cryptoKeys. Cloud Key Management Service API reference. Available at: https://docs.cloud.google.com/kms/docs/reference/rest/v1/projects.locations.keyRings.cryptoKeys [Accessed 9 October 2026].

Google Cloud, n.d.v. REST Resource: projects.locations.streams. Datastream API reference. Available at: https://docs.cloud.google.com/datastream/docs/reference/rest/v1/projects.locations.streams [Accessed 9 October 2026].

Google Cloud, n.d.w. Run parameterized queries. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/parameterized-queries [Accessed 9 October 2026].

Google Cloud, n.d.x. Services that support restricting service usage. Organization Policy documentation. Available at: https://docs.cloud.google.com/resource-manager/docs/organization-policy/restricting-resources-supported-services [Accessed 9 October 2026].

Google Cloud, n.d.y. Set up the Apache Iceberg REST catalog endpoint. Lakehouse documentation. Available at: https://docs.cloud.google.com/lakehouse/docs/set-up-lakehouse-iceberg-rest-catalog [Accessed 9 October 2026].

Google Cloud, n.d.z. Soft delete overview. Cloud Storage documentation. Available at: https://docs.cloud.google.com/storage/docs/soft-delete [Accessed 9 October 2026].

Google Cloud, n.d.aa. Stream data from SQL Server databases. Datastream documentation. Available at: https://docs.cloud.google.com/datastream/docs/sources-sqlserver [Accessed 9 October 2026].

Google Cloud, n.d.ab. Stream table updates with change data capture ingestion. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/change-data-capture [Accessed 9 October 2026].

Google Cloud, n.d.ac. Transform data with data manipulation language (DML). BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/data-manipulation-language [Accessed 9 October 2026].

Google Cloud, n.d.ad. Update dataset properties. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/updating-datasets [Accessed 9 October 2026].

Google Cloud, n.d.ae. Use Secret Manager to store sensitive data. Datastream documentation. Available at: https://docs.cloud.google.com/datastream/docs/use-secret-manager [Accessed 9 October 2026].

Google Cloud, n.d.af. Use strict act-as mode. Dataform documentation. Available at: https://docs.cloud.google.com/dataform/docs/strict-act-as-mode [Accessed 9 October 2026].

Google Cloud, n.d.ag. What is medallion architecture? Google Cloud. Available at: https://cloud.google.com/discover/what-is-medallion-architecture [Accessed 9 October 2026].

Google Cloud, n.d.ah. Work with remote functions. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/remote-functions [Accessed 9 October 2026].

Information Commissioner's Office, 2026. Respect people's preferences. Direct marketing guidance. Available at: https://ico.org.uk/for-organisations/direct-marketing-and-privacy-and-electronic-communications/direct-marketing-guidance/respect-peoples-preferences/ [Accessed 9 October 2026].

Information Commissioner's Office, n.d. Right to erasure. UK GDPR guidance and resources. Available at: https://ico.org.uk/for-organisations/uk-gdpr-guidance-and-resources/individual-rights/individual-rights/right-to-erasure/ [Accessed 9 October 2026].

Microsoft, 2025a. cdc.fn_cdc_get_all_changes_ (Transact-SQL). Microsoft Learn. Available at: https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/cdc-fn-cdc-get-all-changes-capture-instance-transact-sql [Accessed 9 October 2026].

Microsoft, 2025b. Change data capture (CDC) with Azure SQL Database. Microsoft Learn. Available at: https://learn.microsoft.com/en-us/azure/azure-sql/database/change-data-capture-overview?view=azuresql [Accessed 9 October 2026].

Microsoft, 2026a. Automated backups in Azure SQL Database. Microsoft Learn. Available at: https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?view=azuresql [Accessed 9 October 2026].

Microsoft, 2026b. Azure built-in roles for Databases. Microsoft Learn. Available at: https://learn.microsoft.com/en-us/azure/role-based-access-control/built-in-roles/databases [Accessed 9 October 2026].

Microsoft, 2026c. Azure built-in roles for Privileged. Microsoft Learn. Available at: https://learn.microsoft.com/en-us/azure/role-based-access-control/built-in-roles/privileged [Accessed 9 October 2026].

Microsoft, 2026d. Backup immutability for long-term retention backups in Azure SQL Database. Microsoft Learn. Available at: https://learn.microsoft.com/en-us/azure/azure-sql/database/backup-immutability [Accessed 9 October 2026].

Microsoft, 2026e. Database-level roles. Microsoft Learn. Available at: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles [Accessed 9 October 2026].

Microsoft, 2026f. Long-term retention backups: Azure SQL Database and Azure SQL Managed Instance. Microsoft Learn. Available at: https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-retention-overview?view=azuresql [Accessed 9 October 2026].

Microsoft, 2026g. sys.sp_cdc_disable_table (Transact-SQL). Microsoft Learn. Available at: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-disable-table-transact-sql?view=sql-server-ver17 [Accessed 9 October 2026].

Microsoft, 2026h. sys.sp_cdc_enable_table (Transact-SQL). Microsoft Learn. Available at: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql?view=sql-server-ver17 [Accessed 9 October 2026].

National Institute of Standards and Technology, 2025. Guidelines for Media Sanitization (NIST SP 800-88 Rev. 2). NIST Special Publication 800-88 Revision 2, September 2025. Available at: https://nvlpubs.nist.gov/nistpubs/SpecialPublications/NIST.SP.800-88r2.pdf [Accessed 9 October 2026].

United States Congress, n.d. 15 U.S. Code § 7704: Other protections for users of commercial electronic mail. Text as published by the Legal Information Institute, Cornell Law School. Available at: https://www.law.cornell.edu/uscode/text/15/7704 [Accessed 9 October 2026].

Sakura Sky and UpCloud: A European Cloud Partnership

Andrew Stevens — Mon, 12 Oct 2026 14:53:24 +0000

If your team builds for customers in the UK and Europe, where your platform runs is increasingly part of what you sell. Your customers ask where their data lives, who can reach it, and what it will cost to grow. I am delighted to say that Sakura Sky is now an UpCloud partner, which means we can answer those questions with a European provider and a single contract.

For a long time, when a customer asked us for a European option, the answer was a European region of a hyperscaler. Those regions work well, and we will keep building on them. What I have wanted is a European provider we could recommend with the same conviction, with flat pricing and in-house engineers behind its support. UpCloud is that provider for us. From today we resell UpCloud, and our teams are ready to design, build, and run platforms on it for organisations across the UK and the Benelux.

Why UpCloud

UpCloud was formed in 2011 and is headquartered in Helsinki (UpCloud, 2026a). UpCloud's legal team states that all of its European data centres are operated by the Finnish parent company, UpCloud Oy, under the relevant Finnish and EU member state laws, and that the group's US subsidiary has no control over customer account information or over data held by the parent outside the US, and so cannot be compelled to disclose it under the US CLOUD Act (UpCloud Legal Team, 2026). Each organisation will want its own advice, but for many teams that makes UpCloud a practical step towards sovereign cloud.

UpCloud offers a 99.999% uptime SLA, holds ISO 27001 certification, and describes its infrastructure as GDPR-compliant. Support is 24/7 and run by UpCloud's own people, and UpCloud quotes under 40 seconds to reach a human expert (UpCloud, 2026b). It runs fifteen data centres, ten of them in Europe, including London and Amsterdam (UpCloud, 2026c), which puts infrastructure close to the customers we work with most.

Here is what UpCloud offers that we think matters most, and what we will be testing as we build:

Pricing you can plan around. Flat-rate pricing (UpCloud, 2026b), with outbound transfer included under UpCloud's Fair Transfer Policy: the allowance scales with your cloud spend. There are no excess fees by default; if you go over, UpCloud may limit bandwidth for the rest of the month, or you can opt in to metered transfer above the allowance (UpCloud, 2026d). That lets data-heavy products treat egress as a planned line in the budget.
Fast storage and fast starts. Premium plans include UpCloud's MaxIOPS block storage by default, which UpCloud lists at 100k IOPS (UpCloud, 2026j), and UpCloud says new servers are running in under 45 seconds (UpCloud, 2026b). We will test both in our open benchmarks.
Managed services where you need them. Managed Kubernetes is available in every UpCloud location, with a free development tier and a highly available production tier (UpCloud, 2026e; UpCloud, 2026f). Managed Databases for PostgreSQL run in every location (UpCloud, 2026g), with the pgvector extension available for retrieval workloads (Piwosz, 2026), and UpCloud describes Managed Object Storage as fully compatible with the S3 API (UpCloud, 2026h).
GPUs for open models. GPU Servers with NVIDIA L4, L40S, RTX PRO 6000, H100, and B200 cards, currently offered from Helsinki (UpCloud, 2026i), give us a European home for running open-weight models on dedicated GPUs.
Built to stay portable. UpCloud describes its pricing as open and reversible (UpCloud, 2026b), which matches how we build: infrastructure as code on open interfaces, so you can move if your needs change.

What this brings our customers

Sakura Sky is headquartered in Amsterdam, so this is a European partnership on both sides. Where we resell UpCloud, the platform and our engineering come under one contract and one monthly bill. You work with a team that knows your platform well, with UpCloud's own support team behind the infrastructure. On UpCloud, compute and managed databases can run in London or Amsterdam (UpCloud, 2026c), and object storage runs in Helsinki, Stockholm, or Frankfurt (UpCloud, 2026h), so you can tell your customers or your regulator where each part of your data lives.

What we are launching

Alongside the partnership, we are introducing four services built on UpCloud:

European Landing Zone. An UpCloud foundation delivered as code, covering account and sub-account structure, networking, access control, security baselines, and observability, so your first production workloads go live on a foundation that is already reviewed and documented.
Managed European Platform. Your platform on UpCloud, run by us: Kubernetes, managed databases, object storage, monitoring, patching, and cost management, with the same operating discipline our Managed Services customers already rely on.
Managed Open Models. Open-weight models chosen for your workload and served on dedicated GPU capacity in UpCloud's Helsinki data centre (UpCloud, 2026i), with the retrieval layer, gateway, spend controls, and upgrades run by us. By default, inference runs on GPU capacity in Finland, inside your environment, and prompts are not sent to a third-party model API.
Exit Readiness Assessment. A practical review of how portable your workloads are across providers, wherever you run today, with a plan to make future moves simpler and cheaper.

Our Professional Services teams can also work alongside your engineers on migrations, architecture, and platform engineering on UpCloud.

Where UpCloud works well

If your cloud bill is hard to forecast, data residency and third-party risk keep coming up in your GDPR, NIS2, or DORA work, or your team spends more time running Kubernetes than building on it, UpCloud is worth a look. These are the workloads we expect to bring to it most often:

Data and AI. GPU servers in Helsinki and fast storage for training and inference, in a European location you can name.
Backup and disaster recovery. Off-site copies and a standby environment in another European country, on compute that is affordable to keep warm.
Regulated industries and the public sector. Fintech, healthtech, and public sector services that need European jurisdiction and an environment they can audit.
A second cloud alongside a hyperscaler. A European home for the workloads that have to stay in-region, next to an existing AWS, Google Cloud, or Azure estate.
Moving off VMware or on-premises. Lift-and-shift where that is the right first step, or modernisation onto Kubernetes and managed services where the business case supports it.

Sharing what we learn

We will be publishing a good deal of engineering work on UpCloud over the coming months: build-along guides, a series on running open models in production, field notes from migrations, and cloud benchmarks with an open methodology and harness that anyone can rerun. We will publish the results and the raw data together, so you can check our working.

Come and meet us

We are starting user groups in the Netherlands and the UK for people running, or thinking about running, workloads on UpCloud. Both groups will meet through Meetup, and we will announce the first sessions here on the blog. We are also planning monthly online office hours, where you can bring an architecture or a migration plan and talk it through with our engineers.

My thanks to the UpCloud team for the work they have put into getting us here. If you are weighing up European cloud for your next platform, or wondering whether it suits what you run today, I would be glad to talk.

Disclosure: Sakura Sky is an UpCloud reseller and partner. Our engineering series and benchmarks are written independently: UpCloud does not pay for or direct them, and does not review or approve them before publication. Managed Open Models and the other services named here are delivered by Sakura Sky.

References

Piwosz, P., 2026. Installing pgvector on UpCloud Managed PostgreSQL. UpCloud. Available at: https://upcloud.com/resources/tutorials/installing-pgvector-upcloud-managed-postgresql/ [Accessed 12 October 2026].

UpCloud, 2026a. About UpCloud – European Cloud Provider. Available at: https://upcloud.com/about/ [Accessed 12 October 2026].

UpCloud, 2026b. Global Cloud Platform. Available at: https://upcloud.com/ [Accessed 12 October 2026].

UpCloud, 2026c. UpCloud Data Center Information. Available at: https://upcloud.com/data-centres/ [Accessed 12 October 2026].

UpCloud, 2026d. Fair Transfer Policy: Zero-cost Egress. Available at: https://upcloud.com/fair-transfer-policy [Accessed 12 October 2026].

UpCloud, 2026e. Managed Kubernetes availability. UpCloud Documentation. Available at: https://upcloud.com/docs/products/managed-kubernetes/availability/ [Accessed 12 October 2026].

UpCloud, 2026f. UpCloud Managed Kubernetes Platform. Available at: https://upcloud.com/products/managed-kubernetes/ [Accessed 12 October 2026].

UpCloud, 2026g. Managed PostgreSQL availability. UpCloud Documentation. Available at: https://upcloud.com/docs/products/managed-postgresql/availability/ [Accessed 12 October 2026].

UpCloud, 2026h. The Perfect Home For Your Data: Managed Object Storage. Available at: https://upcloud.com/products/object-storage/ [Accessed 12 October 2026].

UpCloud, 2026i. GPU Servers availability. UpCloud Documentation. Available at: https://upcloud.com/docs/products/gpu-servers/availability/ [Accessed 12 October 2026].

UpCloud, 2026j. Premium Cloud Servers. Available at: https://upcloud.com/solutions/premium-cloud-servers/ [Accessed 12 October 2026].

UpCloud Legal Team, 2026. U.S. CLOUD Act and Why Corporate Structure Matters for Data Sovereignty. UpCloud, 22 January. Available at: https://upcloud.com/blog/us-cloud-act-corporate-structure-matters-data-sovereignty/ [Accessed 12 October 2026].

Multi-Cloud Retail Without the Mess

Andrew Stevens — Fri, 09 Oct 2026 11:04:52 +0000

Many multi-brand QSR and retail estates span clouds in a recognisable way. Staff sign in with Microsoft Entra ID, some back-office systems run on SQL Server in Azure, the data platform is on Google Cloud, and a loyalty vendor or an acquired brand brings AWS. If your estate looks like that, it probably isn't anyone's plan. Each piece was a sensible decision when it was made. The cost shows up later, in the connections between them: service account keys copied into another cloud's secret store, the same customer held under three different IDs, and nightly copies of the same tables in two places.

Our earlier post Multi-Cloud Versus Consolidation made the cross-industry case that most organisations are multi-cloud whether or not they chose to be, and that the work is to make the estate coherent. This post applies that to retail and QSR. It looks at how these estates come to span three clouds, where the extra engineering falls, and the identity and data layer that holds them together, with a diagram of the layer, the token exchange behind it, and code for the customer key at its centre.

Joining one customer across three clouds

Take an estate where the catering and gift card systems run on SQL Server in Azure, and a back-office sync job moves their changes into BigQuery on Google Cloud, which is the data platform for sales, operations, and marketing. The loyalty programme runs on a SaaS platform, with an integration in the retailer's AWS account that syncs member records, and a history export that the vendor writes to an S3 bucket in the same account. Delivery orders arrive as webhooks from each aggregator at an integration service on Google Cloud, which injects them into the POS and forwards them to the data platform. In our experience, what aggregators pass on about the customer varies by aggregator and by market, and some orders carry no usable email at all, while others carry a masked relay address that won't match anything else. Marketing wants to send a customer an offer, so the data platform needs to know that a catering order, a loyalty member, and a delivery order with the same email address belong to the same person, and to treat delivery orders without one as unmatched, flagging relay-address keys so they don't inflate customer counts.

Three teams own the three parts of that question, and each has solved its own part well. The back office stores the email typed in at checkout. The loyalty platform has a member ID. The delivery integration stores the aggregator's customer email, when the order carries one. Joining them means moving email addresses, or full customer tables, between clouds, and each team has a reasonable fix that makes the overall picture a little harder to manage.

How estates end up on three clouds

Most of the clouds in an estate like this arrive with a product or a business decision. Microsoft 365 often brings Entra ID with it, and Entra ID becomes the place staff identities live. Back-office applications built on SQL Server often move to Azure, a short step from the data centre they ran in. A data team picks Google Cloud for the data platform on technical grounds. A SaaS loyalty platform runs on whichever cloud its vendor chose, and its exports tend to land nearby. An acquired brand brings its own cloud accounts, its own POS integration, and a team that knows them.

Franchise networks add another layer, because franchisees may run systems the franchisor doesn't control and send data in whatever form the franchise agreement provides for.

Most of the cost comes from the default way of connecting the pieces. A workload on one cloud that needs something on another usually gets a long-lived credential, such as a service account key or an access key, stored in its own cloud's secret manager. Each team identifies customers its own way, so matching them means exporting email addresses or customer tables to wherever the match runs. And data needed in two clouds tends to get copied on a schedule, so there are two copies to secure, two to keep current, and a transfer bill each time it moves.

Sometimes the right answer is consolidation. Sakura Sky moved Craveable Brands' data platform to Google Cloud, bringing data from across its brands and outlets into one BigQuery warehouse. Where consolidation doesn't pay, because the loyalty vendor's cloud isn't yours to choose or the SQL Server estate works well where it is, the job is to make the connections between clouds deliberate.

Where the cross-cloud work falls

For a retail estate, most of that work falls in three places.

The first is machine identity. Services in one cloud need to call services and read data in another without anyone storing a long-lived key for the other cloud. AWS, Google Cloud, and Microsoft Entra ID all document ways to exchange one platform's credentials for another's short-lived tokens (AWS, n.d.a; Google Cloud, n.d.a; Microsoft, 2025), so the stored key can usually be retired.

The second is customer identity. Every system that holds customer records needs to agree on who a customer is, and it helps if they can do that without each one holding the others' raw contact details. That usually means one pseudonymous customer key, computed by one service.

The third is data placement. Each dataset of record lives in one place, with one owner, and other clouds read it where it is. Copies are made for a reason, such as latency, data residency, or resilience, and each copy is recorded with its source and refresh schedule.

Two smaller disciplines sit alongside them. Events crossing clouds, such as aggregator orders, use one envelope format, so each consumer doesn't write its own parser for each source. And consent travels with the customer key: an opt-out recorded in the loyalty platform has to reach the data platform before the next campaign runs, which Part 1 covered from the loyalty side.

The identity and data layer

Figure 1 shows one way the pieces fit together for the estate above, with Google Cloud as the data platform and the home of the customer key service. In your estate the clouds may play different roles, with AWS as the data platform, for example, or the loyalty platform on Azure, and the same pattern should carry over, though the services and their limits differ.

Machine identity with fewer stored keys

Google Cloud's Workload Identity Federation lets workloads on Azure and AWS exchange their own environment's credentials for short-lived Google Cloud credentials, so they can call Google Cloud APIs without a service account key (Google Cloud, n.d.a). Figure 2 shows the Azure case. The back-office sync job runs on an Azure VM with a managed identity. It asks the Azure Instance Metadata Service for an access token for a Microsoft Entra ID application set up for federation, identified by its Application ID URI. In Google Cloud, a provider in a workload identity pool takes the tenant's issuer as its issuer URL and that Application ID URI as its allowed audience. Google's example issuer is https://sts.windows.net/<tenant-id>, but the value can vary, so it's copied from the iss claim of a real token. The token's subject is the managed identity's object ID, which the provider maps to the federated identity's subject, so IAM roles can be granted to that one identity, and an attribute condition on the provider can reject tokens from any other identity in the tenant (Google Cloud, n.d.a). Google Cloud's Security Token Service then exchanges the Entra token for a short-lived Google Cloud access token, referred to here as a federated token. BigQuery's API has no known limitations with federated identities (Google Cloud, n.d.g), so the sync job can be granted access to its datasets directly.

The AWS side of Figure 1 works the same way with different credentials. One of the options Google documents for AWS uses the workload's ordinary temporary AWS credentials, which Google Cloud verifies through AWS's GetCallerIdentity API with no configuration changes in the AWS account. The other uses AWS outbound identity federation, where AWS acts as the identity provider and its workloads request short-lived JSON Web Tokens to present to external services such as Google Cloud (AWS, n.d.b; Google Cloud, n.d.a). Google recommends granting the federated identity access to resources directly, and falling back to service account impersonation only for products that don't support that (Google Cloud, n.d.a).

Calls in the other direction follow the same pattern. Entra ID's workload identity federation lets an app registration or user-assigned managed identity trust tokens from an external provider, with Google Cloud and AWS among the documented scenarios (Microsoft, 2025). AWS's AssumeRoleWithWebIdentity returns temporary AWS credentials, by default for one hour, in exchange for a token from an OpenID Connect provider such as Google, provided the role's trust policy names that provider (AWS, n.d.a). We'd also restrict the subject and the audience in the trust policy, because naming only the provider trusts every identity it issues tokens for. For Google tokens there's a trap: the accounts.google.com:aud condition key takes the token's azp claim when that claim is set, and the token's aud is then checked with accounts.google.com:oaud instead (AWS, n.d.c).

What changes in practice is where trust is configured. Instead of a key in a secret store, each cloud holds a trust relationship naming the other cloud's identity provider, plus conditions on which workloads it accepts. Those relationships need the same care a key did. Revoking access happens on the receiving side, because a token that's already been issued typically stays valid until it expires. In Google Cloud, that means removing the role granted on the resource itself, such as access to the BigQuery dataset or the Cloud Run invoker role held by the impersonated service account, because IAM checks it on each call. Disabling the provider stops new token exchanges, but tokens already issued run until they expire. In AWS, revoking the role's active sessions denies credentials issued before that point (AWS, n.d.d). A provider that accepts any token from an Entra tenant, or any identity in an AWS account, trusts far more workloads than the ones that need access, so its attribute condition names only the specific managed identities or AWS roles that do.

For people, the Entra ID that head-office staff already use can also sign analysts and engineers in to Google Cloud through Workforce Identity Federation, without copying their accounts into Google (Google Cloud, n.d.d), though not every Google Cloud product supports federated identities in the same way (Google Cloud, n.d.g).

One customer key, computed by one service

The customer key in Figure 1 is a pseudonymous join key: a value derived from a customer's normalised email address that every system can store and join on in place of the address itself. Listing 1 computes it with HMAC-SHA256 and a secret key. A plain hash such as SHA-256 of the email isn't enough, because anyone with a list of email addresses can hash them and compare. With HMAC, customer keys can only be produced by whoever holds the secret key.

That's also why the key service runs in one place. If the secret key were copied into every cloud so each could compute customer keys locally, it would become exactly the kind of shared secret federation removes. Instead, the back-office sync job and the loyalty integration authenticate to the key service through federation, the delivery integration calls it with its own Google Cloud identity, and each sends an email address and stores the customer key it returns. If the key service runs on Cloud Run, the federated callers can't be granted access directly, because Cloud Run doesn't support direct resource access for federated identities (Google Cloud, n.d.g). Instead, they use their federated token to impersonate a service account that may invoke the service, and request an ID token as that account, which Cloud Run requires (Google Cloud, n.d.a). Addresses still cross clouds, but only to one authenticated endpoint that doesn't store or log them, in place of whole customer tables copied to wherever the match runs.

The secret itself can live in Cloud KMS, which supports HMAC keys for MAC signing (Google Cloud, n.d.e), so the service calls KMS to compute each HMAC; Listing 1 takes the key as a parameter to keep the example self-contained. Any workload allowed to call the service can still check whether an address matches a stored customer key, so access is limited to named workloads, rate-limited, and logged without the addresses. For the same reason, only the key service's own identity should hold permission to sign and verify with the KMS key, because anyone who can compute or check the HMAC can test addresses against stored keys. Each key also costs a network round trip and a KMS request, so order and enrolment flows don't wait on it: a record can be stored first and keyed asynchronously, and bulk jobs such as backfills and re-keying go through a batch endpoint, where the extra time and cost are easiest to absorb. Campaign audiences leave the data platform as customer keys, and the system that holds the address, such as the loyalty platform or the email service, resolves them for sending.

Python

import hashlib
import hmac
import unicodedata

_EDGE_WHITESPACE = " \t\r\n"  # trimmed identically in every implementation

def _ascii_lower(s: str) -> str:
    # bytes.lower() changes only A-Z, so non-ASCII characters are left as-is
    return s.encode("utf-8").lower().decode("utf-8")

def normalise_email(raw: str) -> str:
    s = unicodedata.normalize("NFC", raw.strip(_EDGE_WHITESPACE))
    local, at, domain = s.rpartition("@")
    if not at or not local or not domain:
        raise ValueError("not an email address")
    # Policy choice: lowercase the local part too (see RFC 5321, section 2.4)
    return f"{_ascii_lower(local)}@{_ascii_lower(domain)}"

def customer_key(raw_email: str, key: bytes, key_version: str) -> str:
    """Pseudonymous join key: the same email always gives the same key."""
    if len(key) < 32:
        raise ValueError("key must be at least 32 bytes")
    message = normalise_email(raw_email).encode("utf-8")
    digest = hmac.new(key, message, hashlib.sha256).hexdigest()
    return f"{key_version}:{digest}"

Rust

use hmac::{Hmac, Mac};
use sha2::Sha256;
use unicode_normalization::UnicodeNormalization;

type HmacSha256 = Hmac<Sha256>;

// trimmed identically in every implementation
fn is_edge_whitespace(c: char) -> bool {
    matches!(c, ' ' | '\t' | '\r' | '\n')
}

pub fn normalise_email(raw: &str) -> Result<String, &'static str> {
    let s: String = raw.trim_matches(is_edge_whitespace).nfc().collect();
    let (local, domain) = s.rsplit_once('@').ok_or("not an email address")?;
    if local.is_empty() || domain.is_empty() {
        return Err("not an email address");
    }
    // Policy choice: lowercase the local part too (see RFC 5321, section 2.4)
    Ok(format!("{}@{}", local.to_ascii_lowercase(), domain.to_ascii_lowercase()))
}

/// Pseudonymous join key: the same email always gives the same key.
pub fn customer_key(raw_email: &str, key: &[u8], key_version: &str) -> Result<String, &'static str> {
    if key.len() < 32 {
        return Err("key must be at least 32 bytes");
    }
    let message = normalise_email(raw_email)?;
    let mut mac = HmacSha256::new_from_slice(key).map_err(|_| "invalid key")?;
    mac.update(message.as_bytes());
    let digest: String = mac
        .finalize()
        .into_bytes()
        .iter()
        .map(|b| format!("{b:02x}"))
        .collect();
    Ok(format!("{key_version}:{digest}"))
}

Listing 1. A pseudonymous customer key from a normalised email address. Both implementations must return the same bytes for the same input.

Most of the listing is normalisation, and that's where the hard decisions are. The key only works as a join if every implementation produces the same bytes for the same address. That can mean two languages even inside one service, for example when the service is rewritten, or when its batch endpoint runs in a different language from the online one, and the obvious library calls don't always agree. Python's str.strip() with no arguments, for example, removes a few control characters that Rust's trim() keeps. So the listing trims a fixed set of characters, applies Unicode NFC normalisation so that "é" typed as one character or as "e" plus an accent gives the same key, and lowercases only ASCII letters. Both implementations are tested against the same set of input and output vectors, with the library versions they were generated on pinned.

Lowercasing the local part, the bit before the @, is a policy decision. RFC 5321 says the local part must be treated as case-sensitive, while discouraging anyone from relying on that (Klensin, 2008). Lowercasing it accepts a small chance of merging two people's addresses in exchange for fewer split records. Leaving non-ASCII letters alone means "ÉLODIE" and "élodie" produce different keys, which is the cost of keeping the result identical in every language.

The version prefix on each key makes rotation possible. A new secret key gets a new version. Re-keying means recomputing from the email address, so it can only run where addresses are still held, such as the back office or the loyalty platform. Systems that hold only keys, such as the data platform, are re-keyed from an old-to-new key table produced where the addresses are, and that table should be held as tightly as the secret and deleted once re-keying finishes, because anyone holding it and the old secret can link new keys to addresses. Keeping an address-to-key mapping in the key service would make re-keying easier, but it turns the service into a central store of email addresses that needs the same protection as the secret. Because of that cost, some teams rotate only when a key may have been exposed. During a rotation both versions stay live, and the service issues both for new records until every stored record has been re-keyed.

A customer key isn't anonymous. Under the GDPR, pseudonymisation means data can't be attributed to a person without additional information that is kept separately and protected (European Parliament and Council, 2016, Art. 4(5)), and pseudonymised data that could be attributed using that information is still personal data (European Parliament and Council, 2016, Recital 26). California's CCPA defines pseudonymization along similar lines (California Legislature, n.d., §1798.140(aa)). The key cuts the number of systems that need raw addresses to join records, and makes erasure and access requests easier to trace, but the same privacy obligations apply to every system that stores it.

Data read where it lives

In Figure 1, the loyalty vendor's history export lands in an S3 bucket in the retailer's own AWS account, and the loyalty integration writes a member-to-customer-key map beside it. Keeping the map beside the export lets BigQuery Omni, covered in the next paragraph, join loyalty history to customer keys inside AWS, so only keyed, filtered results cross to Google Cloud. Vendor exports are often CSV or Parquet files. If the vendor writes Apache Iceberg tables, or the retailer converts the export, engines such as Spark, Trino, and Flink can work with the same tables at the same time (Apache Iceberg, n.d.).

BigQuery Omni is one way for the data platform on Google Cloud to use that data without copying it first. It runs the BigQuery query engine in AWS and Azure regions over data in S3 or Azure Blob Storage, through BigLake tables, so processing happens where the data sits (Google Cloud, n.d.b). Results can come back to Google Cloud or be written straight to S3. The options differ in practice: cross-cloud joins suit one-off queries over data that isn't large, with a limit of 60 GB per transfer, while materialised view replicas suit repeated queries such as a dashboard. Data moved out of AWS this way still incurs AWS egress charges (Google Cloud, n.d.b), so a filtered read, such as one store-day of loyalty redemptions for one brand, should cost less than moving the whole table. For Iceberg tables in S3 that are managed by a catalog such as AWS Glue, Google now points to cross-cloud data access through its Lakehouse runtime catalog instead (Google Cloud, n.d.c), so the right route depends on how the export is catalogued.

The SQL Server systems are a different case. Their data changes constantly and the data platform needs it alongside sales from every other source, so the sync job reads SQL Server's change data capture and merges changed rows into BigQuery. A managed service can do the same job. Google's Datastream reads change tables or transaction logs from self-managed SQL Server, including SQL Server on a cloud VM, and change tables from Azure SQL Database at service objective S3 (a Standard-tier size, unrelated to Amazon S3) or above (Google Cloud, n.d.f), and can stream them to BigQuery (Google Cloud, n.d.h). For SQL Server it doesn't support Windows Active Directory authentication (Google Cloud, n.d.f), and its setup creates a SQL Server login with a password for the connection (Google Cloud, n.d.h), so there's a stored credential to manage, which is worth weighing against a sync job that uses its managed identity. Replicating the email column as it is in the source means email addresses arrive in BigQuery unkeyed and need keying and removal there, which brings raw addresses back into the data platform. Excluding the email column avoids that, but then those rows have to be keyed at source. Either way it's a deliberate copy, with a known source, owner, and cadence.

A copy is worth making when it has a specific reason. A dashboard that refreshes every few minutes, a dataset that has to sit in a particular country, a replica kept for recovery, or operational data that has to be joined with sales and other sources, like the SQL Server feed, can each justify one. Each of those copies goes in the same record of copies, so that it can be found, refreshed, and eventually removed.

Events need the same treatment at a smaller scale. Aggregator webhooks arrive in each aggregator's own format. CloudEvents, a CNCF graduated specification for describing event data in a common way, gives every order event the same envelope, and its adopters include Amazon EventBridge, Azure Event Grid, and Google Cloud Eventarc (CloudEvents, n.d.). Wrapping each aggregator's order in that envelope at the integration service means downstream consumers in any cloud read one format, and once the integration service has a customer key for an order from the key service, the event can carry the key in place of the email address.

What the estate looks like with this in place

A few things change that teams outside the platform group will notice. Head-office staff use their Entra ID identity in Azure and Google Cloud. Long-lived credentials between clouds are the exception, kept only where a service such as Datastream, or a vendor integration with the S3 bucket that only supports access keys, requires one, and then recorded and rotated; elsewhere each cloud holds trust relationships with conditions narrowed to the workloads that need them. Every system that stores customers stores the same customer key, issued by one service, and an opt-out or an erasure request can be traced across all of them by that key. Each dataset of record has one owner and one location, and every copy has a recorded reason. Aggregator events reach every consumer in one envelope.

The estate also becomes easier to change. Moving the back office off SQL Server, replacing the loyalty platform, or bringing an acquired brand's data in becomes mostly a matter of re-pointing trust relationships and readers, while the customer key and event format can stay the same. Deciding which workloads stay where they are, and which are worth consolidating, is placement work Sakura Sky's Cloud practice does with retail and QSR teams.

Trust conditions and key versions drift as vendors and teams change, and reviewing them is ongoing work Sakura Sky's Managed Services team takes on.

References

Apache Iceberg, n.d. Apache Iceberg™: The open table format for analytic datasets. Apache Software Foundation. Available at: https://iceberg.apache.org/ [Accessed 9 October 2026].

AWS, n.d.a. AssumeRoleWithWebIdentity. AWS Security Token Service API Reference. Available at: https://docs.aws.amazon.com/STS/latest/APIReference/API_AssumeRoleWithWebIdentity.html [Accessed 9 October 2026].

AWS, n.d.b. Getting started with outbound identity federation. AWS Identity and Access Management User Guide. Available at: https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_providers_outbound_getting_started.html [Accessed 9 October 2026].

AWS, n.d.c. IAM and AWS STS condition context keys. AWS Identity and Access Management User Guide. Available at: https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_iam-condition-keys.html [Accessed 9 October 2026].

AWS, n.d.d. Revoke IAM role temporary security credentials. AWS Identity and Access Management User Guide. Available at: https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_use_revoke-sessions.html [Accessed 9 October 2026].

California Legislature, n.d. California Civil Code §1798.140 (California Consumer Privacy Act). California Legislative Information. Available at: https://leginfo.legislature.ca.gov/faces/codes_displaySection.xhtml?lawCode=CIV&sectionNum=1798.140 [Accessed 9 October 2026].

CloudEvents, n.d. CloudEvents: A specification for describing event data in a common way. Cloud Native Computing Foundation. Available at: https://cloudevents.io/ [Accessed 9 October 2026].

European Parliament and Council, 2016. Regulation (EU) 2016/679 of the European Parliament and of the Council of 27 April 2016 on the protection of natural persons with regard to the processing of personal data and on the free movement of such data (General Data Protection Regulation). Official Journal of the European Union, L 119, 4 May, pp. 1-88. Available at: https://eur-lex.europa.eu/eli/reg/2016/679/oj [Accessed 9 October 2026].

Google Cloud, n.d.a. Configure Workload Identity Federation with AWS or Azure VMs. Identity and Access Management documentation. Available at: https://docs.cloud.google.com/iam/docs/workload-identity-federation-with-other-clouds [Accessed 9 October 2026].

Google Cloud, n.d.b. Introduction to BigQuery Omni. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/omni-introduction [Accessed 9 October 2026].

Google Cloud, n.d.c. Create Apache Iceberg external tables. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/iceberg-external-tables [Accessed 9 October 2026].

Google Cloud, n.d.d. Workforce Identity Federation. Identity and Access Management documentation. Available at: https://docs.cloud.google.com/iam/docs/workforce-identity-federation [Accessed 9 October 2026].

Google Cloud, n.d.e. MAC signatures. Cloud Key Management Service documentation. Available at: https://docs.cloud.google.com/kms/docs/mac-signatures [Accessed 9 October 2026].

Google Cloud, n.d.f. Stream data from SQL Server databases. Datastream documentation. Available at: https://docs.cloud.google.com/datastream/docs/sources-sqlserver [Accessed 9 October 2026].

Google Cloud, n.d.g. Identity federation: products and limitations. Identity and Access Management documentation. Available at: https://docs.cloud.google.com/iam/docs/federated-identity-supported-services [Accessed 9 October 2026].

Google Cloud, n.d.h. Configure a self-managed SQL Server database for CDC. Datastream documentation. Available at: https://docs.cloud.google.com/datastream/docs/configure-self-managed-sqlserver [Accessed 9 October 2026].

Klensin, J., 2008. RFC 5321: Simple Mail Transfer Protocol. Internet Engineering Task Force. Available at: https://datatracker.ietf.org/doc/html/rfc5321 [Accessed 9 October 2026].

Microsoft, 2025. Workload identity federation concepts. Microsoft Entra Workload ID documentation. Available at: https://learn.microsoft.com/en-us/entra/workload-id/workload-identity-federation [Accessed 9 October 2026].

Thousands of Sites, One Pipeline

Andrew Stevens — Thu, 08 Oct 2026 09:00:31 +0000

When your operations team and your analytics team bring different sales figures for the same day to the same meeting, the discussion tends to turn to whose figure is right. A multi-brand QSR operator with two thousand sites might run three POS systems, two loyalty platforms, four delivery integrations, and an inventory layer built in-house. In that kind of network, analytics dashboards often get checked against other sources before anyone acts on them, and operations keeps its own dashboards built from its own extracts.

The usual response targets the dashboards: a new BI tool, a semantic layer, or a working group to agree definitions. Those can help, agreed definitions especially, but they act after the steps where data is collected, assigned to a day, and matched across sources. In many networks the disagreement is created at that earlier stage. This post works backwards from one disputed number to those causes, then forwards to the pipeline and the operating disciplines that reduce them. Our earlier post Telemetry Is Becoming the Business made the cross-industry case that operational telemetry now has to be engineered as a data product, and a network's sales record is a clear case.

The competing dashboards

Take one store, the fictional store 0412, on a Saturday. It takes delivery through one aggregator, which injects orders into its POS. At about 11pm on Saturday its broadband failed. The cellular failover carried only card payments and order injection, so the back-office upload stopped until Sunday morning. The operations dashboard shows net sales of $18,420. That's the figure in the POS end-of-day report the store manager signs off. The report arrived on Sunday morning when the upload resumed. The analytics dashboard, built from transaction feeds loaded overnight, shows $19,255.

Rather than argue over whose figure is right, the two teams can build a reconciliation bridge, which walks from one figure to the other, one cause at a time. The figures are illustrative, but in our experience the line items are the kind that turn up when this is done on a real network.

Step	Amount	Running total
Operations dashboard (POS end-of-day report)		$18,420
Trading-day boundary: the POS counts sales up to its 4am cutoff as the previous day's; the warehouse uses calendar date	−$1,160	$17,260
Transactions recorded before midnight while the link was down, which missed the overnight load	−$640	$16,620
Aggregator-feed copies of orders the POS also recorded (excluding tax and fees)	+$2,175	$18,795
Tax and customer-paid delivery and service fees on those aggregator copies, counted as net sales	+$460	$19,255
Analytics dashboard		$19,255

In the warehouse, Saturday night's midnight-to-4am window drops out of Saturday and Friday night's comes in, which nets to the first line. Sales after midnight during the outage were also late; the bridge counts them under the trading-day line and leaves them out of the late-transactions line. The late-transactions line exists because the analytics model loads each day once and doesn't go back for late arrivals.

Two lines are rules that differ between teams, one is an arrival gap, and one is a duplicate the analytics model doesn't look for. At this store the fee line disappears with the duplicates. The rule still matters for every aggregator order the POS never sees. Tax is not part of net sales, and customer-paid delivery and service fees on marketplace orders usually belong to the aggregator. Resolve all four, using this POS's definition of net, and store 0412 comes to $18,420, the POS figure, because every sale here went through the POS. Where injection fails during an outage and orders are taken on the aggregator's tablet instead, those orders exist only in the aggregator feed. Orders a POS never sees are added on top and reconciled at settlement.

There's often a fifth item that never makes the bridge, because it has to be solved before any comparison can be made. The store is 0412 in the POS, carries a different code in the inventory system, and has a third identifier in the delivery platform's merchant portal. Before anyone can compare a figure, someone has to know which records belong to the same store.

How QSR data estates fragment

In our experience, networks like this grow one system at a time. Each system arrives to solve a specific problem and brings its own clock, store identifiers, and definition of a sale. An acquisition brings a second POS. A brand launched in another market brings a third. Delivery marketplaces are often integrated one at a time, some injecting orders into the POS and some not, and the choice can differ by brand, by market, or by aggregator. The loyalty platform from Part 1 adds its own view of the same transactions. Unifying data that sat in separate point-of-sale, loyalty, marketing, supply chain, and franchisee reporting silos on a single data engineering platform was a core part of Sakura Sky's work with Craveable Brands.

Franchise networks add another layer. Some systems are chosen by franchisees, and the franchisor may receive only what the franchise agreement provides for, sometimes a daily sales report rather than transaction detail. The royalty base is usually a contractual definition of gross sales, so any published definition of net also has to map back to it.

Each team then builds the extracts it needs for its own questions. Operations takes the POS end-of-day reports because they come from the same close the manager signs off and counts the tills against. Analytics builds from the transaction-level feeds because it needs baskets, dayparts, and channels. Finance usually books from the daily sales journal and closes against card settlement, aggregator payouts, and bank deposits. All three are reasonable choices, and all three produce numbers that differ for reasons like those in the bridge.

When the numbers disagree, the fix usually lands in the dashboard layer, as a filter that excludes one aggregator's feed, a calculated field that subtracts tax, or a date shift for after-midnight trading. Each fix lives in one dashboard or one team's semantic model, so dashboards built elsewhere from the same tables don't have it, and the copies accumulate.

The dashboard layer is also a poor place for some of these rules. It can't recover transactions that weren't loaded. Matching duplicate orders and assigning trading days can be done there, but every model has to repeat the work, and the copies drift apart. A shared semantic layer is a reasonable place to expose the definition of net, but matching orders and recovering late data have to happen upstream of it.

A medallion pipeline for a multi-site network

What matters most in the architecture is where each decision is made, whichever products implement it. Much of Sakura Sky's lakehouse work uses the medallion pattern for this. Bronze holds raw data as received, silver holds cleaned, deduplicated, and conformed records, and gold holds the aggregated, business-ready tables that dashboards read (Databricks, 2026; Google Cloud, n.d.d). Figure 1 shows the layers.

Ingestion jobs (API pulls from POS and aggregator platforms, file drops, webhooks, and event streams) write each source's data into the bronze layer as received. Bronze is append-only apart from retention and erasure deletes. It also holds the POS end-of-day reports and payment settlement files, which silver later parses into control totals. The one exception to "as received" is cardholder data. Incoming data is checked for full card numbers or track data at ingestion. Card numbers are truncated and track data is removed before landing, and each hit is logged (without the data) and raised with the source system to fix. The ingestion step that does this, and any drop zone or queue the raw files pass through first, handles card data, so in our designs it's treated as inside PCI DSS scope and kept small and locked down.

Bronze also needs retention and erasure rules for personal data, such as loyalty identifiers and the customer names and addresses in delivery orders. Erasure has to reach every layer. In silver and gold it only fully takes effect once BigQuery's time travel window has passed (Google Cloud, n.d.b) or old Iceberg snapshots have been expired, and in bronze once any soft-deleted or versioned objects have aged out.

That raw history is what lets the pipeline reprocess a store-day when a rule changes or a late batch arrives (Databricks, 2026; Google Cloud, n.d.d), without asking the POS vendor to resend anything. Parsing happens after landing, with one parser per source system and version, so a change in one vendor's export format is contained in one place.

Silver is where most of the bridge items get resolved, once. Databricks lists deduplication and the resolution of out-of-order and late-arriving data among the jobs of this layer (Databricks, 2026). That's where the duplicate line belongs, though matching one order across two systems is closer to entity resolution than to removing repeated rows. The late-transactions line is closed by reprocessing the day from bronze, with gold's store-day status showing it as provisional until then.

The trading day is a conforming rule applied in silver, using the store master (silver reference data). The store master maps each source system's code to one store key and records the store's time zone, trading-day cutoff, delivery integrations, and status. It keeps each attribute with the dates it applied, so reprocessing an old day uses the cutoff and codes that applied then.

Each order becomes one sales event carrying the store key, the event time, and a business date (the trading day it belongs to). Its line items are kept alongside, and refunds and post-tender voids are recorded as their own events. Money is kept as components (gross, discounts, brand-funded and aggregator-funded promotions, tax and who remits it, customer-paid delivery and service fees, aggregator commission, and tips), so net sales becomes a published definition built from those components. Networks that trade in more than one currency also need a stated conversion policy for any network-wide figure.

Orders that arrive from more than one source carry a cross-source link, typically the aggregator's order ID where the POS integration records it. Where it doesn't, a fallback match on store, time, and amount pairs orders one-to-one within a tolerance, since the two copies rarely carry identical amounts (modifiers, promotions, and tax are often recorded differently). Ambiguous candidates are left unmatched. Until it's matched, the aggregator copy of an order from an injecting integration is held out of net sales, so a missed match understates delivery rather than double-counting it. Once the store-day reconciles to its end-of-day report, the POS side is treated as complete. Any aggregator orders still unmatched are then checked against the day's POS orders from any channel, because staff often re-key tablet orders by hand when injection fails. Those still unmatched after a holding period are released into net as aggregator-only sales and flagged. That changes the store-day's net but not its reconciled status, which covers POS-sourced orders only, and they're tracked against settlement rather than counted as corrections. A rising rate of unmatched orders is treated as an integration fault to fix at source, usually by getting the POS integration to record the aggregator's order ID.

The merged order keeps the POS business date, so the two copies can't land on different trading days near the cutoff. It also keeps both sets of money components, the POS's for reconciling to the end-of-day report and the aggregator's for settlement. Net sales uses the POS's components.

Where the POS records its own business date, the pipeline usually adopts it, because that's the day the end-of-day report covers. Listing 1 is for the sources that don't, such as aggregator feeds, and assigns each event to the store's trading day from its time and the store's configured cutoff. Where managers run the POS close by hand, the cutoff approximates it. Aggregator feeds can carry several timestamps per order, so the rule uses one agreed timestamp, usually when the order was accepted.

Python

from dataclasses import dataclass
from datetime import date, datetime, time, timedelta
from zoneinfo import ZoneInfo

@dataclass(frozen=True)
class Store:
    store_key: str     # one key across POS, delivery, loyalty, and inventory
    tz: str            # IANA zone name, e.g. "America/Chicago"
    day_cutoff: time   # trading day rolls over here, e.g. 04:00; keep it
                       # clear of the hour that repeats when clocks go back
    # zone and cutoff are the store's attributes as of the event's date

    def __post_init__(self) -> None:
        ZoneInfo(self.tz)  # fail fast on an unknown zone name
        if self.day_cutoff.tzinfo is not None:
            raise ValueError("day_cutoff must be naive local time")

def business_date(store: Store, event_time: datetime) -> date:
    """The trading day an event belongs to, in the store's own terms."""
    if event_time.utcoffset() is None:
        raise ValueError("event_time must be timezone-aware")
    local = event_time.astimezone(ZoneInfo(store.tz))
    if local.time() < store.day_cutoff:
        return local.date() - timedelta(days=1)
    return local.date()

Rust

use chrono::{DateTime, Days, NaiveDate, NaiveTime, Utc};
use chrono_tz::Tz;

#[derive(Debug, Clone)]
pub struct Store {
    pub store_key: String,     // one key across POS, delivery, loyalty, and inventory
    pub tz: Tz,                // IANA zone, e.g. chrono_tz::America::Chicago
    pub day_cutoff: NaiveTime, // trading day rolls over here, e.g. 04:00; keep it
                               // clear of the hour that repeats when clocks go back
    // zone and cutoff are the store's attributes as of the event's date
}

/// The trading day an event belongs to, in the store's own terms.
/// Taking `DateTime<Utc>` means a time without a zone can't be passed in.
pub fn business_date(store: &Store, event_utc: DateTime<Utc>) -> NaiveDate {
    let local = event_utc.with_timezone(&store.tz);
    if local.time() < store.day_cutoff {
        local.date_naive() - Days::new(1)
    } else {
        local.date_naive()
    }
}

Listing 1. Assigning an event to the store's trading day, using the store's IANA time zone and cutoff.

The store carries an IANA time zone name instead of a fixed UTC offset, because offsets and daylight-saving rules change by government decision. The IANA Time Zone Database is updated to track those changes (IANA, n.d.). Its 2026 releases record British Columbia, Alberta, the Northwest Territories, and Manitoba moving to permanent UTC offsets (IANA, 2026), so a service on older rules would put most stores in those regions an hour out each winter, starting in November 2026.

The name only helps if the database behind it is current. Python's zoneinfo reads the operating system's copy or the tzdata package (Python Software Foundation, 2026), so updating those is the usual route. ZoneInfo also returns cached zone objects, so a long-running process can keep serving the old rules until it restarts or clears the cache. The chrono-tz crate builds the database into the binary at compile time (chrono-tz contributors, 2025), so a change normally arrives with a crate release that includes it, followed by a rebuild. Its latest release at the time of writing, 0.10.4 from July 2025, predates all four changes. A Rust service for stores in those regions needs either a chrono-tz release with current rules, once one ships, or a time zone source that reads the system's database at runtime. Listing 1 uses chrono-tz to keep the example short. Rules applied in SQL or in Spark typically use that engine's own copy of the database, which needs the same attention.

Daylight saving also makes some trading days shorter or longer than usual, which is worth flagging on hourly and same-day-last-year comparisons. Some POS exports record only local wall-clock time with no offset. The parser then converts using the store's zone, and both the skipped hour when clocks go forward and the repeated hour when they go back need an explicit rule. Python's zoneinfo, for example, treats a repeated time as its first occurrence (fold=0) unless told otherwise (Python Software Foundation, 2026).

Gold metrics are then computed once, upstream of every dashboard, with their definitions published alongside them. Operations, analytics, and finance can still build different views, at different grains and for different questions, but they read the same net sales for store 0412 on Saturday.

Google Cloud's medallion explainer puts bronze in Cloud Storage and silver and gold in BigQuery, with the transformations written in SQL (for example with Dataform) or in Apache Spark (Google Cloud, n.d.d).

Networks that want to stay on open formats can keep silver and gold as Apache Iceberg tables (Apache Iceberg, n.d.a). BigQuery's Iceberg managed tables (formerly BigLake tables for Apache Iceberg in BigQuery) store the data in buckets the customer owns. Writes go through BigQuery, including from Spark and Dataflow via its Storage Write API. Open-source engines such as Spark can read the tables, though Google warns that modifying the data files outside BigQuery can cause query failure or data loss (Google Cloud, n.d.a). Late store-days are reprocessed through BigQuery's DML (Google Cloud, n.d.a). Where Spark manages its own Iceberg tables through its own catalog, MERGE INTO does the same job (Apache Iceberg, n.d.d).

Neither platform's time travel is a long-term record. BigQuery's window is configurable from two to seven days, with seven by default (Google Cloud, n.d.b). Iceberg snapshots last until they're expired, and expiring them regularly is recommended, after which they're no longer available for time travel (Apache Iceberg, n.d.c). A month-end state can be kept longer with a BigQuery table snapshot of a standard table, taken at month-end (Google Cloud, n.d.c), or with an Iceberg tag where Spark or another engine manages the Iceberg catalog (Apache Iceberg, n.d.b). BigQuery's Iceberg managed tables don't support table snapshots (Google Cloud, n.d.a), so on those the month-end state is written out explicitly.

Finance and franchisees need to know what a store-day showed when a month-end report or royalty statement was produced. A pinned copy doesn't record why a figure changed, so revision history is kept explicitly, as a gold table of store-day versions with the reason for each change.

The same layering works on Databricks (Databricks, 2026) or a self-managed Iceberg catalog, and the platform choice is a question for an initial cloud assessment. Part 3 of this series will look at networks whose data already spans more than one cloud.

Operating disciplines around the pipeline

An architecture like Figure 1 can still produce numbers nobody trusts if the pipeline treats each day as finished as soon as a load has run. The Dataflow Model paper treats this as a starting assumption: for data that arrives continuously and out of order, a system can't know whether it has seen everything, only that new data will arrive and earlier data may be retracted (Akidau et al., 2015). A two-thousand-site network behaves like that kind of source. On a typical night in a network this size, some stores are likely to be offline and some POS terminals likely to forward late.

The discipline that follows is to make each store-day's state visible. Listing 2 is a simple version. A store-day is open until its trading day ends. It's provisional until the pipeline's net for POS-sourced orders agrees with the store's end-of-day report, and reconciled once it does. That report covers only what went through the POS. The comparison uses each POS's own definition of net, rebuilt from the stored components, so a definitional difference doesn't look like missing data.

Python

from enum import Enum

class DayStatus(Enum):
    OPEN = "open"                # trading day not yet ended
    PROVISIONAL = "provisional"  # ended, not yet reconciled
    RECONCILED = "reconciled"    # agrees with the store's end-of-day report

def store_day_status(
    *,
    trading_day_ended: bool,
    pipeline_pos_net_minor: int,     # pipeline net for POS-sourced orders, in minor units
    pos_eod_net_minor: int | None,   # None until the end-of-day report lands
    tolerance_minor: int,            # set per POS; line rounding and tax-inclusive
                                     # pricing can differ
) -> DayStatus:
    if tolerance_minor < 0:
        raise ValueError("tolerance_minor must not be negative")
    if not trading_day_ended:
        return DayStatus.OPEN
    if pos_eod_net_minor is None:
        return DayStatus.PROVISIONAL
    if abs(pipeline_pos_net_minor - pos_eod_net_minor) > tolerance_minor:
        return DayStatus.PROVISIONAL
    return DayStatus.RECONCILED

Rust

#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum DayStatus {
    Open,        // trading day not yet ended
    Provisional, // ended, not yet reconciled
    Reconciled,  // agrees with the store's end-of-day report
}

pub fn store_day_status(
    trading_day_ended: bool,
    pipeline_pos_net_minor: i64,    // pipeline net for POS-sourced orders, in minor units
    pos_eod_net_minor: Option<i64>, // None until the end-of-day report lands
    tolerance_minor: u64,           // set per POS; line rounding and tax-inclusive
                                    // pricing can differ
) -> DayStatus {
    if !trading_day_ended {
        return DayStatus::Open;
    }
    match pos_eod_net_minor {
        Some(eod) if pipeline_pos_net_minor.abs_diff(eod) <= tolerance_minor => {
            DayStatus::Reconciled
        }
        _ => DayStatus::Provisional,
    }
}

Listing 2. Store-day status: open, provisional, or reconciled against the store's own end-of-day report.

Orders that never pass through the POS, such as those from non-injecting aggregators, aren't covered by this check; they're reconciled against the aggregator's own reports or settlement, usually later. A production version also records why a day is provisional (report missing or totals that don't match) and how long it has been in that state, and compares transaction counts as well as net, since totals alone can hide offsetting errors.

Franchised stores that send only a daily sales report have nothing at transaction level to reconcile it against, so those store-days carry their own status. They're checked against card settlement or aggregator data where the franchise agreement provides it, and against royalty submissions.

The status is recalculated whenever a store-day's events change, so a reconciled day returns to provisional if a late batch moves its POS-sourced total. In the estates we work with, refunds are usually booked to the day they're processed, matching the POS, so a reconciled day isn't reopened by them.

A change to a provisional day is a revision, and a change to a day that had already reconciled is a correction. The revision history covers both. Neither is a restatement in the accounting sense.

Once finance has closed a period, a small correction is usually carried into the open period, and a correction to a day already covered by a royalty statement becomes an adjustment on the next one. Gold keeps the corrected store-day for operational analysis, and the revision history keeps the version that finance and the franchisee were given.

Figure 2 follows store 0412 through its lost link. Saturday shows as provisional, with its end-of-day report counted as missing in the coverage figure, until the buffered transactions and the report arrive on Sunday morning and the day reconciles. The revised figure then reaches both teams' dashboards from the same table on their next refresh, with a note that the day was revised.

The status depends on a few other practices. Coverage is reported every day as store-days with an end-of-day report received against store-days expected to trade, which needs the store master to know about openings, closures, refits, and changes of trading hours. The store master itself has an owner, because a franchise transfer or a reopened site under a new code breaks joins without warning, and the first sign is often a dashboard that looks wrong weeks later.

Each parser has tests against sample files from its source system. POS and aggregator releases are also tracked; in our experience, a POS release rolled out store by store often shows up as a gradual drift in one brand's numbers. Card processor settlement and aggregator payouts give a second reconciliation point independent of the stores, usually run later and at finance's grain, while cash goes through deposit reconciliation.

What changes for operations and analytics

For operations, the most visible change is that the figure they already reconcile to becomes the control total the whole pipeline is held to. Their dashboards move onto the gold layer, and the end-of-day report keeps its standing because it's the store's own close. Analytics changes too, giving up calendar-date grouping and its own tax and fee handling for the shared conforming rules in silver and the published definition of net in gold.

Operations usually hears first about changes to trading hours, cutoffs, temporary closures, and refits. The pipeline now reads them from where operations records them, so a store that closes early for a refit doesn't show as missing. For franchised sites, coverage alerts go to the franchisee through the field team, under the reporting terms of the franchise agreement.

The daily conversation changes shape too. A morning summary might read: 1,826 of 1,839 expected transaction-feed store-days reconciled to their POS end-of-day reports, and 13 provisional. Of 150 daily-report franchise stores, 148 have reported. Two previously reconciled days were corrected since yesterday and are reconciled again, and aggregator-only sales are still to reconcile at settlement.

Disagreements still happen, but they move to definitions that can be decided once: whether staff meals and manager comps count as sales, whether operational figures follow finance in keeping gift card loads out until redemption, how loyalty redemptions and aggregator-funded promotions reduce net, which price counts for marketplace orders, and whether a delivery fee on the brand's own app counts as a sale. The decision is then applied in the pipeline, where every dashboard picks it up.

Sakura Sky's Data & AI practice builds and runs pipelines like this for restaurant and retail networks, from POS and aggregator ingestion through to the store-day status that operations, analytics, and finance read.

References

Akidau, T., Bradshaw, R., Chambers, C., Chernyak, S., Fernández-Moctezuma, R.J., Lax, R., McVeety, S., Mills, D., Perry, F., Schmidt, E. and Whittle, S., 2015. The Dataflow Model: A Practical Approach to Balancing Correctness, Latency, and Cost in Massive-Scale, Unbounded, Out-of-Order Data Processing. Proceedings of the VLDB Endowment, 8(12), pp. 1792-1803. Available at: https://www.vldb.org/pvldb/vol8/p1792-Akidau.pdf [Accessed 8 October 2026].

Apache Iceberg, n.d.a. Apache Iceberg™: The open table format for analytic datasets. Apache Software Foundation. Available at: https://iceberg.apache.org/ [Accessed 8 October 2026].

Apache Iceberg, n.d.b. Branching and Tagging. Apache Iceberg documentation. Available at: https://iceberg.apache.org/docs/latest/branching/ [Accessed 8 October 2026].

Apache Iceberg, n.d.c. Maintenance. Apache Iceberg documentation. Available at: https://iceberg.apache.org/docs/latest/maintenance/ [Accessed 8 October 2026].

Apache Iceberg, n.d.d. Spark Writes. Apache Iceberg documentation. Available at: https://iceberg.apache.org/docs/latest/spark-writes/ [Accessed 8 October 2026].

chrono-tz contributors, 2025. chrono_tz (version 0.10.4). docs.rs. Available at: https://docs.rs/chrono-tz/latest/chrono_tz/ [Accessed 8 October 2026].

Databricks, 2026. What is the medallion lakehouse architecture? Databricks documentation. Available at: https://docs.databricks.com/aws/en/lakehouse/medallion [Accessed 8 October 2026].

Google Cloud, n.d.a. Apache Iceberg managed tables. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/biglake-iceberg-tables-in-bigquery [Accessed 8 October 2026].

Google Cloud, n.d.b. Data retention with time travel and fail-safe. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/time-travel [Accessed 8 October 2026].

Google Cloud, n.d.c. Introduction to table snapshots. BigQuery documentation. Available at: https://docs.cloud.google.com/bigquery/docs/table-snapshots-intro [Accessed 8 October 2026].

Google Cloud, n.d.d. What is medallion architecture? Google Cloud. Available at: https://cloud.google.com/discover/what-is-medallion-architecture [Accessed 8 October 2026].

IANA, 2026. Time Zone Database Releases. Internet Assigned Numbers Authority. Available at: https://www.iana.org/time-zones/releases [Accessed 8 October 2026].

IANA, n.d. Time Zones. Internet Assigned Numbers Authority. Available at: https://www.iana.org/time-zones [Accessed 8 October 2026].

Python Software Foundation, 2026. zoneinfo: IANA time zone support. Python 3 documentation. Available at: https://docs.python.org/3/library/zoneinfo.html [Accessed 8 October 2026].

The Loyalty Backend Is a Data Engineering Problem

Andrew Stevens — Wed, 07 Oct 2026 06:52:52 +0000

Customers judge a loyalty programme by its balance. When the points from today's burger don't appear, show up late, or a reward is declined at the counter, the app and the offers lose credibility, however well the launch went.

Accounting standards treat the balance seriously too. Under IFRS 15, points that give a customer a material right are a separate performance obligation. The customer has in effect paid in advance for future goods, so part of the transaction price is allocated to the points and held as a contract liability until they are redeemed or expire (European Commission, 2016, IFRS 15 para. B40). That allocation already reflects how likely the points are to be used (para. B42). Points that are never redeemed are often referred to as breakage. A business that expects breakage recognises it in line with the pattern of redemptions, and one that doesn't recognises it when redemption becomes remote (paras. B45 to B46). US GAAP's Topic 606 was issued alongside IFRS 15 with the aim of reaching the same conclusions (FASB, 2014). Either way, finance depends on a reliable redemption history, and that history comes from the loyalty backend.

That puts a financial system behind a marketing programme, and promotions are where the two pull apart, because a double-points weekend changes the rules and the volume at once. Accrual is one of the workloads Peak Load Is the Steady State treats as able to queue under pressure, which is safe when the backend is designed for it. The stages below follow one point through that design.

Enrol

The customer signs up in the app, at a kiosk, or by giving an email address at the counter. The data engineering question here is identity. The same customer may enrol twice, once in the app and once at a kiosk, or may already exist in the CRM from an earlier programme. If the backend creates a new member for each, points get split across records, and the customer sees a lower balance than they expect. Resolving identity at enrolment, by matching on verified contact details and linking payment tokens (never card numbers) or app installs to one member, is usually simpler than merging accounts after points have been earned on both.

Enrolment is also where the basis for using the member's data is recorded. Loyalty programmes have rules of their own. California allows financial incentive programmes, which include many loyalty schemes, but a business can enter a consumer into one only with prior opt-in consent that describes the material terms and can be revoked at any time (Cal. Civ. Code §1798.125(b)(3)). Under GDPR, a member can object at any time to direct marketing, including related profiling, after which their data can no longer be used for that purpose (European Parliament and Council, 2016, Art. 21(2) to (3)). Those choices have to travel with the member record, because the offer engine depends on them.

Earn

The customer buys something and scans their app, or the POS links the payment to their member ID. This creates an earn event, and it's where a loyalty backend first meets the realities of a restaurant or store network.

Stores don't always have a reliable connection. Many POS systems keep trading when the link drops and forward their transactions when it comes back, so earn events can arrive late, in bursts, and out of order. Transactions get voided, refunded, or amended after the fact. Some brands run more than one POS across their network, each with its own event format. Members also buy through several channels: an app order paid in the app and collected at a store that's offline, a kiosk that identifies members by QR code, or a delivery-aggregator order that arrives through a separate integration. Receipt-claim features, where a customer enters a code later, add earn events that are late by design.

A backend that treats each incoming message as a fresh instruction to add points will double-count retried messages and miss refunds. The usual fixes come from other event-driven systems. Every earn event carries a key the source can reproduce on retry, often a composite of store, register, business date, and transaction number, because POS transaction IDs can repeat across stores. Voids and refunds reverse the original even when the points have already been spent, so the backend needs a rule for negative balances. Late events are applied in transaction order, and order-dependent rules, such as a bonus on the fifth visit, are recalculated when a late event lands earlier in the sequence.

Points have cash value, so the event stream also carries abuse: staff attaching unclaimed transactions to their own member ID, accounts opened in bulk to farm a sign-up bonus, and takeovers of accounts with large balances. These tend to show up as patterns of velocity, device, and store in data the backend already processes, so fraud rules can often run on the accrual stream before points become redeemable.

Accrue

Accrual is where earn events become points, by applying the programme's rules: the base rate, the bonus, the double-points weekend, the offer the customer activated in the app. A successful promotion raises volume and rule complexity together, which is why accrual is often where a backend falls behind.

What matters most to customers is what the POS has to wait for. Identifying the member and checking which offers apply usually has to happen while the basket is open, because a discount changes the price. Posting the earned points usually doesn't. If the POS waits for accrual before completing the sale, a slow rules engine becomes a slow counter. If it writes the transaction to a durable queue and moves on, the counter stays fast and accrual can catch up after a spike. The synchronous path is then limited to member lookup and offer validation, ideally with a cached fallback.

Catching up is acceptable when customers can see what's happening. A balance that shows points as pending, with the transaction that earned them, is likely to hold trust better than a balance that shows nothing with no explanation. Treating accrual as a stream processing problem, with a queue that absorbs the burst, a rules engine that scales out, and a lag metric someone watches, means a promotion spike shows up as pending points, not missing ones. For restaurant and retail brands, our Data & AI practice often starts with the accrual pipeline and its lag metric, because that's where a promotion shows up first.

Redeem

Redemption is usually the hardest stage, because it has to be right at the moment it happens. A customer at the counter wants a free coffee with their points. The POS asks the backend whether the balance covers it, and if so, the points have to be deducted before the coffee is handed over.

Two things can go wrong. If the balance the POS sees is behind accrual, a customer who has the points may be told they don't. If two redemptions happen close together, at the counter and in the app, a backend without a reservation step can let both through against the same points. Redemption needs a consistent view of the balance and a reservation that holds the points until the sale completes or is cancelled, so it often gets its own path through the backend, separate from the bulk accrual pipeline.

Stores that keep trading offline face the same question at redemption. Some brands block rewards until the link returns, and others allow limited offline redemptions and reconcile afterwards. That's a trade-off between fraud exposure and goodwill, and the backend has to record which path each redemption took.

Offers add another layer. Rewards are often limited by store, time of day, product, or quantity, and each limit is a rule the POS and the backend have to agree on. Where they disagree, the customer sees a reward in the app that the counter won't honour, which can undo some of the goodwill the promotion created.

Expire

Many programmes expire points after a set period or a stretch of inactivity. For finance, expiry releases the liability held against those points (European Commission, 2016, IFRS 15 para. B40). For the backend, it's a scheduled job that runs against the event history, sends any reminder the programme's terms promise, and leaves a record that reconciliation can match. Expiry rules tend to change over a programme's life, so the backend has to know which rule applied to which point.

Reconcile

Each trading day, the loyalty ledger has to agree with the systems around it: the POS sales records, the payments data, and the finance team's liability account. Points issued should match qualifying transactions. Points redeemed should match the rewards given away at the counter. The outstanding balance should support the liability on the balance sheet, and the redemption history feeds the breakage estimate the auditors will ask about.

Franchising adds another layer. In a franchise network, the store that issues points and the store that redeems them may belong to different franchisees. One common approach is a set reimbursement rate per reward, often funded from the marketing fund, and calculating it needs a store-level record of every earn and redemption event.

A backend built with reconciliation in mind keeps the event history as the source of truth and derives balances from it, so any balance can be explained transaction by transaction. A running balance that's updated in place can be hard to reconcile after a busy promotion, because the history behind each number has to be reconstructed.

Across the network

Each stage tends to get harder across a large network. Hundreds or thousands of locations mean several POS versions in the field at once, different connectivity, different trading hours, and in many networks a mix of company-owned and franchised stores with their own systems.

Loyalty data tends to be most useful in the same platform as sales and operational data, so marketing, operations, and finance work from one record. Sakura's Google Cloud data platform for Craveable Brands, the franchisor behind Red Rooster, Oporto, and Chicken Treat, brought loyalty and CRM records together with point-of-sale, marketing, and franchisee data across what was then more than 570 restaurants (Craveable Brands case study). Part 2 of this series looks at running a network of sites on one pipeline.

After go-live, the lag alert, the daily reconciliation, and the POS adapter that breaks after a vendor release all need an owner. Our Managed Services team takes that on for restaurant and retail brands.

References

California Legislature, n.d. California Civil Code §1798.125 (California Consumer Privacy Act). California Legislative Information. Available at: https://leginfo.legislature.ca.gov/faces/codes_displaySection.xhtml?lawCode=CIV&sectionNum=1798.125 [Accessed 6 October 2026].

European Commission, 2016. Commission Regulation (EU) 2016/1905 of 22 September 2016 amending Regulation (EC) No 1126/2008 adopting certain international accounting standards in accordance with Regulation (EC) No 1606/2002 of the European Parliament and of the Council as regards International Financial Reporting Standard 15. Official Journal of the European Union, L 295, 29 October. Available at: https://eur-lex.europa.eu/eli/reg/2016/1905/oj [Accessed 6 October 2026].

European Parliament and Council, 2016. Regulation (EU) 2016/679 of the European Parliament and of the Council of 27 April 2016 on the protection of natural persons with regard to the processing of personal data and on the free movement of such data (General Data Protection Regulation). Official Journal of the European Union, L 119, 4 May, pp. 1-88. Available at: https://eur-lex.europa.eu/eli/reg/2016/679/oj [Accessed 6 October 2026].

FASB, 2014. Accounting Standards Update No. 2014-09, Revenue from Contracts with Customers (Topic 606). Financial Accounting Standards Board, May 2014. Available at: https://storage.fasb.org/ASU%202014-09_Section%20A.pdf [Accessed 6 October 2026].

Patient Data Across Boundaries

Andrew Stevens — Tue, 06 Oct 2026 09:52:43 +0000

A trial that enrols patients in the EU, the US, Singapore, and Brazil holds its data under four data protection regimes. They don't all apply to every record. Which rules govern a participant's data depends on where that participant enrolled, what they agreed to, and who holds the copy in question. The trial's documents capture most of this: the protocol, the consent forms, the data transfer agreements. The data itself usually carries the site, the country, and the consent date, but rarely the legal basis, the permitted purposes, or the optional consents a participant did or didn't give.

Those missing values decide what the platform may do next. This post sets out some example fields a multi-jurisdiction trial platform can carry, what values each can take, and where the four regimes differ. There's a reference table at the end.

Who holds the obligation

Start with who the rules bind, because it isn't the same in every country. HIPAA binds covered entities, such as the hospitals and clinics running US sites, and their business associates. A sponsor usually isn't either. HIPAA governs what the site may disclose to the sponsor, and the authorisation a US participant signs has to warn that information disclosed under it may no longer be protected by the Privacy Rule (HHS, n.d.a, §164.508(c)(2)(iii)). From there, HIPAA no longer applies, and what binds the sponsor is the informed consent it obtained under FDA rules, its contracts with sites, and any applicable state privacy laws. In the EU, Singapore, and Brazil, data protection law applies to the sponsor's own processing as well as the site's. In Brazil, the 2024 law on research with human beings now governs the protection of participants' personal data, with the LGPD applying where it is silent (Presidência da República, 2024, Art. 61).

So it helps to record, for each copy of the data, who holds it and in what role.

Example fields

Some of these belong on each participant record and some on each copy of the data. They're examples, not a complete schema.

`holder_role`

On each copy: the organisation that holds it, the country it's established in, and its role, such as site, sponsor, CRO, or partner. Most of the fields below can only be read correctly alongside this one.

`jurisdiction`

The country where the participant enrolled. It decides which regime governs collection, but it doesn't settle every later use. GDPR also applies to processing in the context of a holder established in the EU, wherever the processing happens (European Parliament and Council, 2016, Art. 3(1)), so this field has to be read together with holder_role. It also has to stay attached when data from several countries is pooled, since a pooled dataset that has lost its country column is hard to check against any of the rules that follow.

`legal_basis`

The basis on which the data is processed, which is often more than one, and differs by purpose.

In the EU, the informed consent a trial requires is an ethical safeguard, and the European Data Protection Board says it must not be confused with consent as a legal basis under GDPR. Processing for safety reporting and record-keeping rests on legal obligation. Processing for the research itself can rest on explicit consent, a task in the public interest, or the sponsor's legitimate interests, combined with a condition in Article 9, and the Board says consent will not be the appropriate basis in most cases (EDPB, 2019). Member states may also add conditions for health and genetic data (European Parliament and Council, 2016, Art. 9(4)). In the US, a site discloses to the sponsor under a written authorisation that describes each purpose, or under a waiver approved by an institutional review board, and can disclose to an FDA-regulated sponsor for safety purposes such as adverse event reporting (HHS, n.d.a, §164.508; HHS, n.d.c, §164.512(b) and (i)).

Singapore's PDPA requires consent unless a listed exception applies (AGC, n.d., s.13), and other written law prevails where it is inconsistent with the PDPA (AGC, n.d., s.4(6)(b)), so trial-specific rules can take precedence. Its research exception for using personal data requires, among other things, that the results won't be used to make any decision that affects the individual (AGC, n.d., Second Schedule, Part 2, Division 3), which suits later research better than the trial itself. In Brazil, the LGPD, which fills the gaps in the research law, allows sensitive data to be processed with specific, highlighted consent, or without consent where needed for a legal or regulatory obligation. Its research route is limited to Brazilian public bodies and non-profits established in Brazil whose mission includes research, which leaves most commercial sponsors relying on consent or legal obligation (Presidência da República, 2018, Arts. 5 and 11).

Record the basis per purpose at enrolment, from the consent version the participant signed, so that each later use can be checked against it.

`consent_scope`

The optional consents, separate from the main trial consent. The EU Clinical Trials Regulation lets a sponsor ask participants to agree to use of their data outside the protocol, exclusively for scientific purposes, and says that consent can be withdrawn at any time, without prejudice to data protection law (European Parliament and Council, 2014, Art. 28(2)). Other countries use similar optional consents for future research, sample storage, or sharing with partners. Each one is a yes, a no, or not asked, and it can change. A model-training project, a partner extract, or a pooled analysis should only draw on participants whose consent scope covers it, and the use still needs its own legal_basis entry, because the Board treats this optional consent as an ethical safeguard rather than a GDPR legal basis (EDPB, 2019). Consent and lineage models like these are a regular part of the clinical data platforms our Data & AI practice works on.

`transfer_route`

How the data may leave the country, and under what safeguard. All four regimes regulate how data leaves; none of them requires it to stay. GDPR allows transfers to countries the European Commission has found adequate, with appropriate safeguards, or in limited cases under a derogation (European Parliament and Council, 2016, Chapter V). LGPD allows transfers to adequate countries or with safeguards such as standard contractual clauses (Presidência da República, 2018, Art. 33). PDPA makes the transferring organisation ensure the data gets protection comparable to the Act's (AGC, n.d., s.26). HIPAA's Privacy Rule has no data-location rule. Separately, a US Justice Department rule bars US persons from some transactions, and restricts others, that give countries of concern access to bulk US sensitive personal data. The thresholds include personal health data on more than 10,000 US persons and genomic data on more than 100, and transactions involving bulk genomic and other 'omic data or biospecimens are prohibited (DOJ, n.d., §§202.205 and 202.303). There's an exemption for transactions ordinarily incident to and part of FDA-regulated clinical investigations (DOJ, n.d., §202.511), but reuse outside the trial may fall outside it.

Each transfer, including a CRO statistician working from another country, needs a recorded route. The platform should be able to show, for any dataset, which participants' data left which country and on what basis. Our Sovereignty Versus Efficiency post looked at residency as an architecture question across regulated industries, and participant data adds a further question about which uses each participant agreed to.

`identifiability`

Whether a given copy counts as personal data, which depends on the law and on who holds it. Under GDPR, pseudonymised data that could be attributed to a person using additional information is personal data (European Parliament and Council, 2016, Recital 26). The EU Court of Justice has since held, under the equivalent rules for EU institutions, that pseudonymised data isn't personal data in all cases and for every person, since pseudonymisation may prevent people other than the controller from identifying anyone (CJEU, 2025, para. 86). Singapore's PDPA counts data as personal if a person can be identified from it together with other information the organisation has or is likely to have access to (AGC, n.d., s.2). HIPAA treats data as de-identified when an expert determines the risk of re-identification is very small, or when a listed set of identifiers is removed and the covered entity has no actual knowledge that what's left could identify someone (HHS, n.d.b, §164.514(b)). That list includes all elements of dates except the year, so a coded trial dataset with visit dates isn't de-identified under the second method. LGPD treats anonymised data as outside the law unless the anonymisation can be reversed with reasonable effort (Presidência da República, 2018, Art. 12).

So the same coded dataset can be personal data under one regime and not under another, and the answer can change when it's copied to a new holder. Set this field per copy, and record which participants' data trained or validated each model, so a later question about reuse can be answered.

`withdrawal_state`

What still applies after a participant leaves. Every regime gives participants a way out, and they differ on what happens next. In the EU, the Board's view is that withdrawing consent stops the research based on that consent, and that where no other basis applies the data should be deleted, while processing for legal obligations such as safety reporting continues (EDPB, 2019), and the Clinical Trials Regulation keeps data obtained before withdrawal, without prejudice to GDPR (European Parliament and Council, 2014, Art. 28(3)). In the US, FDA policy is that data already collected about participants who leave a study is kept as part of the study data (FDA, 2008), and a site's HIPAA authorisation can be revoked in writing except where the site has already acted on it (HHS, n.d.a, §164.508(b)(5)). PDPA requires the organisation to stop using the data unless the law requires or authorises the use without consent (AGC, n.d., s.16(4)). LGPD keeps processing done before revocation valid while no deletion is requested (Presidência da República, 2018, Art. 8 §5).

The field can record which uses must stop, which may continue, and whether deletion applies, so the platform can find every copy affected: analysis datasets, partner extracts, and the training data behind models. Keeping the evidence of those checks current as consents and regulations change is what Praxis, Sakura's compliance solution, is built for.

The four regimes side by side

A simplified reference. Each cell has exceptions, and the sources above give the detail. In the EU, member states may add conditions for health and genetic data. In Brazil, the 2024 research law applies first, with the LGPD filling gaps.

Question	EU	US	Singapore	Brazil
Basis	Legal obligation for safety and records; explicit consent, public interest, or legitimate interests for research (EDPB)	Site: authorisation or IRB waiver (HIPAA); sponsor: consent, FDA rules, and contracts	Consent, or listed exceptions with conditions (PDPA)	Research law first; under LGPD, specific consent or legal or regulatory obligation
Transfer	Adequacy, safeguards, or limited derogations (GDPR)	No HIPAA location rule; DOJ bulk data rule, with a trial exemption	Transferring organisation ensures comparable protection (PDPA)	Adequacy or safeguards (LGPD)
Identifiers removed	Depends on who holds it (GDPR; CJEU)	De-identified by expert determination or by removing listed identifiers (HIPAA)	Personal if identifiable with information the holder has or can access (PDPA)	Not personal data unless reasonably reversible (LGPD)
Withdrawal	Consent-based research stops, with deletion if no other basis; legal obligations continue	Accrued data kept (FDA); site authorisation revocable	Stop using unless the law permits (PDPA)	Earlier processing valid unless deletion requested (LGPD)

Example fields like these help keep each participant's data current and checked before a transfer or a new use. Clear-cut cases can be handled by the platform, and the rest can go to someone who can decide.

That closes the Pharma and Healthcare series. Part 1 covered custody of trial data and Part 2 the provenance of research data, and this post has looked at permission, meaning which uses each participant's data allows in each country. For the jurisdiction-by-jurisdiction mapping behind those fields, talk to our GRC service.

Not legal advice. This article offers general commentary on the EU General Data Protection Regulation, the EU Clinical Trials Regulation, the US HIPAA Privacy Rule, FDA guidance, the US Justice Department's rule on access to sensitive personal data, Singapore's Personal Data Protection Act, and Brazil's research and data protection laws for a pharma and healthcare engineering audience. It is not legal advice and is not a substitute for advice from qualified counsel or qualified regulatory affairs professionals. Specific obligations depend on the nature of your clinical programme, the jurisdictions in which you operate, your sponsor and CRO agreements, and the regulators to which you are subject. Readers must obtain independent legal and regulatory advice on how these laws apply to their specific products and clinical operations.

References

Attorney-General's Chambers (AGC), n.d. Personal Data Protection Act 2012. Singapore Statutes Online. Available at: https://sso.agc.gov.sg/Act/PDPA2012?WholeDoc=1 [Accessed 6 October 2026].

Court of Justice of the European Union (CJEU), 2025. European Data Protection Supervisor v Single Resolution Board, Case C-413/23 P. Judgment of 4 September 2025, ECLI:EU:C:2025:645. Available at: https://eur-lex.europa.eu/legal-content/EN/TXT/?uri=CELEX:62023CJ0413 [Accessed 6 October 2026].

European Data Protection Board (EDPB), 2019. Opinion 3/2019 concerning the Questions and Answers on the interplay between the Clinical Trials Regulation (CTR) and the General Data Protection Regulation (GDPR). Adopted 23 January 2019. Available at: https://www.edpb.europa.eu/documents/legislative-opinion/opinion-32019-concerning-the-questions-and-answers-on-the-interplay_en [Accessed 6 October 2026].

European Parliament and Council, 2014. Regulation (EU) No 536/2014 of the European Parliament and of the Council of 16 April 2014 on clinical trials on medicinal products for human use. Official Journal of the European Union, L 158, 27 May. Available at: https://eur-lex.europa.eu/eli/reg/2014/536/oj [Accessed 6 October 2026].

European Parliament and Council, 2016. Regulation (EU) 2016/679 of the European Parliament and of the Council of 27 April 2016 on the protection of natural persons with regard to the processing of personal data and on the free movement of such data (General Data Protection Regulation). Official Journal of the European Union, L 119, 4 May, pp. 1-88. Available at: https://eur-lex.europa.eu/eli/reg/2016/679/oj [Accessed 6 October 2026].

Presidência da República, 2018. Lei nº 13.709, de 14 de agosto de 2018: Lei Geral de Proteção de Dados Pessoais (LGPD). Brasília. Available at: https://www.planalto.gov.br/ccivil_03/_ato2015-2018/2018/lei/l13709.htm [Accessed 6 October 2026].

Presidência da República, 2024. Lei nº 14.874, de 28 de maio de 2024: dispõe sobre a pesquisa com seres humanos e institui o Sistema Nacional de Ética em Pesquisa com Seres Humanos. Brasília. Available at: https://www.planalto.gov.br/ccivil_03/_ato2023-2026/2024/lei/L14874.htm [Accessed 6 October 2026].

US Department of Health and Human Services (HHS), n.d.a. 45 CFR 164.508: Uses and disclosures for which an authorization is required. Electronic Code of Federal Regulations. Available at: https://www.ecfr.gov/current/title-45/subtitle-A/subchapter-C/part-164/subpart-E/section-164.508 [Accessed 6 October 2026].

US Department of Health and Human Services (HHS), n.d.b. 45 CFR 164.514: Other requirements relating to uses and disclosures of protected health information. Electronic Code of Federal Regulations. Available at: https://www.ecfr.gov/current/title-45/subtitle-A/subchapter-C/part-164/subpart-E/section-164.514 [Accessed 6 October 2026].

US Department of Health and Human Services (HHS), n.d.c. 45 CFR 164.512: Uses and disclosures for which an authorization or opportunity to agree or object is not required. Electronic Code of Federal Regulations. Available at: https://www.ecfr.gov/current/title-45/subtitle-A/subchapter-C/part-164/subpart-E/section-164.512 [Accessed 6 October 2026].

US Department of Justice (DOJ), n.d. 28 CFR Part 202: Access to U.S. Sensitive Personal Data and Government-Related Data by Countries of Concern or Covered Persons. Electronic Code of Federal Regulations. Available at: https://www.ecfr.gov/current/title-28/chapter-I/part-202 [Accessed 6 October 2026].

US Food and Drug Administration (FDA), 2008. Guidance for Sponsors, Clinical Investigators, and IRBs: Data Retention When Subjects Withdraw from FDA-Regulated Clinical Trials. October 2008. Available at: https://www.fda.gov/regulatory-information/search-fda-guidance-documents/data-retention-when-subjects-withdraw-fda-regulated-clinical-trials [Accessed 6 October 2026].

Your Lab Notebook Is a Data Product

Andrew Stevens — Mon, 05 Oct 2026 07:38:41 +0000

When your corporate development team has a term sheet for out-licensing a preclinical programme, the partner's diligence list is likely to run to several hundred lines. While most will be routine, a handful will land on the research organisation, and they tend to look like these four, paraphrased from the kind that appear in life sciences licensing and M&A diligence:

14.3 Provide the primary data behind the in vivo efficacy figures in the data room summary, including raw instrument output.
14.7 Confirm the analysis can be reproduced: methods, software and versions used, and any exclusions applied.
14.9 For each key experiment, identify who generated the data, when, and under which employment, CRO, or collaboration agreement, with the supporting records.
14.12 List any third-party data in the package (CROs, academic collaborators, material transfer agreements, licensed datasets, and any government funding) and the terms under which it can be used and transferred.

The science is rarely what slows these requests down. The effort goes into answering them in a way the partner will rely on when it comes to price, because anything it can't verify is likely to come back as a warranty or a price adjustment. The answers usually exist, spread across electronic lab notebooks (ELNs), instrument PCs, a scientific data management system (SDMS), departmental shares, CRO portals, and the memories of scientists, some of whom now work on other programmes.

Research data in most pharma and biotech organisations is kept in several ways at once: by individual scientists in the way that suits their work, in shared drives and an ELN under lab-level rules, and, less often, as versioned, described, and owned datasets built for people who weren't there when they were generated. Diligence tests all of them, because it asks the questions day-to-day research rarely does.

Request 14.3: the raw data

For a typical in vivo efficacy study, the raw data is mostly where the team left it. Caliper and body-weight readings sit in a spreadsheet, imaging files on the instrument PC or an external drive, and the processed numbers in a workbook named in a way that made sense at the time. The ELN entry records the protocol and attaches the final figure as an image. An SDMS may have captured the imaging output, but nothing records which files fed which figure, so the link between the summary in the data room and the data underneath it depends on folder conventions and on the people who remember them.

What would have answered 14.3 is an identifier for the study, with the raw output, the processed data, and each figure linked to one another and to the ELN entry that describes the experiment. With that in place, the response is an export of a defined set of records to the data room, and nothing has to be assembled for the occasion.

Request 14.7: reproducing the result

This is often the request that takes longest. The FAIR principles, a widely adopted framework for research data stewardship, ask for data to be findable, accessible, interoperable, and reusable, by machines as well as people, and for data and metadata to be associated with detailed provenance (Wilkinson et al., 2016, R1.2). For an analysis, provenance means more than the files: which script, which version, which parameters, and which animals were excluded and why.

The method is usually well written up in the ELN. The code that ran it is more often in an analyst's personal repository, and exclusion decisions made in a lab meeting may be recorded only as rows deleted from a spreadsheet. Reproducing the result then means finding the analyst, recovering the script version, and reconstructing the reasoning for the exclusions from meeting notes.

A research data platform built for this stores the script and its version with the output it produced, records parameters as data, and logs each exclusion with its reason. This is the same engineering any production data platform uses, applied to lab data.

Request 14.9: who generated it, and under what agreement

This request is about chain of title. A partner wants to know that the organisation owns what it's licensing, which means knowing who generated each key dataset and under which agreement: an employee's contract, a CRO's statement of work, or a collaboration agreement with its own terms on ownership.

The lab notebook's role in research IP has changed. Since the US moved to a first-inventor-to-file system for applications filed on or after 16 March 2013, a dated notebook entry no longer wins a priority contest against someone who filed first (USPTO, n.d.). Notebooks still matter as evidence of who conceived an invention, in disputes with collaborators, in derivation proceedings, which must be supported by substantial evidence (USPTO, n.d.), and for patents filed under the earlier rules that remain in force. Inventorship is a separate question from who generated the data, and a diligence team will usually ask about it separately.

The NIH's data management and sharing policy, which applies to research NIH funds or conducts, draws a line that's useful well beyond its scope. It defines scientific data as data of sufficient quality to validate and replicate research findings, and excludes laboratory notebooks, preliminary analyses, and communications with colleagues from that definition (NIH, n.d.). Many organisations haven't drawn that line internally, so data gets pasted into notebook entries and notebook pages get photographed into data folders. Keeping them linked but distinct, with the ELN entry recording who did what and why and the dataset recording what was produced and by which instrument or provider, lets 14.9 be answered with a query across the two records. In GLP studies, original observations recorded in a notebook are themselves raw data under FDA rules (FDA, n.d., §58.3(k)), so the split applies mainly to discovery and other non-GLP work, which is where most efficacy data sits.

Request 14.12: whose data it is

The last request can change the deal terms. A preclinical package often includes data generated by CROs, material received under transfer agreements, academic collaborations, and licensed third-party datasets, each under its own agreement. FAIR asks for data to be released with a clear and accessible data usage licence (Wilkinson et al., 2016, R1.1), and that's a sensible standard inside a company as well as outside it. Data from an NIH-funded academic collaborator may also come with sharing commitments made in that collaborator's data management plan (NIH, n.d.).

Notebooks and shared drives rarely carry those terms. The CRO's study report is in the archive, and the contract that says who owns the underlying data is with legal. When source and usage terms are carried as metadata, the package can be filtered to what's transferable before the data room opens. FAIR's principle that metadata should stay accessible even when the data itself is no longer available (Wilkinson et al., 2016, A2) matters here as well, because an agreement may require data to be returned or deleted while the record that it existed has to remain.

This is the argument Telemetry Is Becoming the Business made about operational data, applied to the bench: a by-product of the work that needs an owner, a contract, and a structure once someone outside the lab relies on it. Sakura's Data & AI practice builds research data platforms on top of the ELN and instrument estate a lab already runs, which is usually where the pharma data architecture work starts.

What works at the bench

None of this works unless scientists use it, and research culture has good reasons for its habits. The line tends to fall in predictable places.

Capture that happens without the scientist is usually welcome. Instruments that write raw output straight to a managed store, with the run's metadata attached, remove work. So does an ELN template that pulls sample and instrument identifiers from the systems that already know them. Anything that removes a manual copy step tends to be adopted quickly.

Structure is accepted when it pays the scientist back. Someone who can find an older assay dataset in seconds, or rerun a colleague's analysis without a meeting, sees the point of identifiers and versioning. A form asking for fifteen metadata fields for someone else's benefit gets skipped.

What doesn't work is a governance process that slows exploratory work. Most experiments are exploratory, and many are superseded, so treating every early run as a curated asset costs effort the science can't spare. The workable approach is for automated raw capture to run for every experiment, and for curation, review, and ownership to start at a defined promotion point: when a result is going to be presented, included in an invention disclosure, or built on. The notebook stays the scientist's space, and the data product is what the organisation builds from the experiments that matter.

That promotion step, with its integrations and access rules, has to be run as instruments, partners, and programmes change. Our Managed Services team keeps it running for research organisations. Done well, the scientist notices fewer copy steps, and the diligence team notices nothing at all.

References

National Institutes of Health (NIH), n.d. Data Management and Sharing Policy Overview. NIH Grants and Funding. Available at: https://grants.nih.gov/policy-and-compliance/policy-topics/sharing-policies/dms/policy-overview [Accessed 5 October 2026].

United States Patent and Trademark Office (USPTO), n.d. America Invents Act (AIA) Frequently Asked Questions. USPTO. Available at: https://www.uspto.gov/patents/laws/america-invents-act-aia/america-invents-act-aia-frequently-asked [Accessed 5 October 2026].

US Food and Drug Administration (FDA), n.d. 21 CFR 58.3: Definitions (Good Laboratory Practice for Nonclinical Laboratory Studies). Electronic Code of Federal Regulations. Available at: https://www.ecfr.gov/current/title-21/chapter-I/subchapter-A/part-58/subpart-A/section-58.3 [Accessed 5 October 2026].

Wilkinson, M.D., Dumontier, M., Aalbersberg, I.J. et al., 2016. The FAIR Guiding Principles for scientific data management and stewardship. Scientific Data, 3, 160018. Available at: https://doi.org/10.1038/sdata.2016.18 [Accessed 5 October 2026].

Chain of Custody for Trial Data

Andrew Stevens — Fri, 02 Oct 2026 07:04:35 +0000

Between two transfers from the central laboratory, one LDL cholesterol result in a cardiovascular trial fell from 3.1 to 2.4 mmol/L. The value was participant 1047's reading at visit 6, an input to the trial's primary endpoint, the percent change in LDL-C from baseline. Below is everything the sponsor's clinical data repository recorded about the change.

Row	Time (UTC)	Action	Old	New	User (role, organisation)	Reason recorded
1	2026-03-03 09:12	Value loaded		3.1	lab_transfer (system, central lab)	Transfer 14
2	2026-03-19 09:10	Value updated	3.1	2.4	lab_transfer (system, central lab)	Transfer 15
3	2026-03-19 09:31	Discrepancy raised			recon_check (system, CRO)	Differs from previous transfer
4	2026-03-24 11:40	Discrepancy closed			dm_03 (data manager, CRO)	Confirmed with lab

Simplified from load histories we've reviewed. The trial, identifiers, and values are invented.

A reconciliation check did its job and flagged the change. What the repository can't show is why the laboratory's number moved, or what the data manager saw before closing row 4. The answer exists. The lab's triglyceride result for that sample was too high for a calculated LDL-C, so the lab measured LDL-C directly and issued a revised value. A lab project manager explained this in an email to the contract research organisation (CRO). The data manager read the email, checked the lab's report, and closed the discrepancy. The lab, the CRO, and data management each followed their own procedures. But the evidence that turns row 4 from an assertion into a fact sits in a laboratory information system, an inbox, and the data manager's memory.

That matters most in a sponsor inspection, often long after the people who closed row 4 have moved on. A value that feeds the primary endpoint dropped by more than a fifth, and the only explanation on record is "Confirmed with lab."

Evidence Versus Speed argued for capturing lineage at the moment data is written. Trials complicate that advice, because most of the writes behind one endpoint value happen in systems the sponsor doesn't run. Chain of custody, borrowed from forensics, is a better frame here: evidence keeps its value only if every handover leaves a record of who passed what to whom, and in what condition.

Custody inside one system

Inside each system, custody is usually in good shape. 21 CFR Part 11 requires secure, computer-generated, time-stamped audit trails that independently record operator entries and actions that create, modify, or delete electronic records, without obscuring what was there before, and retained for as long as the records themselves (FDA, n.d., §11.10(e)). It requires authority checks so only authorised individuals can alter a record (§11.10(g)), and signatures that show the signer's printed name, the time, and the meaning of the signature (§11.50). The repository's audit trail above captures what those sections ask for.

ICH E6(R3), adopted in January 2025, sets a higher bar for data of higher criticality, such as endpoints. It asks for systems that document changes including, where appropriate, the reason for the change, and for audit trails that are interpretable and can support review (ICH, 2025, §4.2.2). Its section on corrections says a correction should be attributed to the person or system making it, justified, and supported by source records from around the time of the original entry (§4.2.4). The EMA's guideline on computerised systems in clinical trials is specific about what an audit trail should show: what changed, by whom (username, role, and organisation), when, and where applicable why (EMA, 2023, §6.2.1).

Organisation is the field that marks a handover, and it's where most audit trails stop. Row 2 says the central lab changed the value. It can't say why, because the reason lives in the lab's own system. Row 4 records a reason, but "Confirmed with lab" points to a conversation, not a record. In the programmes we see, trial data lineage problems usually start this way, with an audit trail that is complete and accurate and still doesn't lead to the evidence.

Custody between systems

A single endpoint value can pass through three or four organisations before it reaches an analysis dataset. This one began in the lab's information system and travelled as a cumulative transfer file under a data transfer agreement. It was loaded into the sponsor's repository by the CRO, reconciled, and would later move again into the datasets used for analysis. Lab results like this one are usually kept from site staff after randomisation in a lipid trial, since the drop would reveal treatment, so the chain runs between lab, CRO, and sponsor rather than through the site.

The guidance is direct about these hops. E6(R3) asks for validated processes or reconciliation to make sure data transferred between computerised systems, including relevant metadata, keeps its integrity, and for transfers and migrations to be documented to ensure traceability (ICH, 2025, §4.2.5). The EMA requires every transfer during a trial to be pre-specified and validated, and expects transferred data and their audit trails to remain continuously accessible (EMA, 2023, §6.1.2). In a sponsor inspection, the FDA says it will generally focus on, among other things, the processes that keep data from being altered in value or meaning, including during transfer to durable electronic repositories, along with change control and contracts with service providers (FDA, 2024, Q8).

E6(R3) does require interfaces between systems to be validated (ICH, 2025, §4.3.4(e)). But a validated interface shows the file arrived intact. It doesn't show why its contents differ from the last one. The transfer for participant 1047 was validated, and the lab's revision followed the lab's own procedures, yet neither record pointed at the data point it changed. The chain recorded the files and none of the decisions behind them.

Custody after the trial

The last handover is the one teams plan least: from live systems to the archive, as trials finish, CRO contracts end, and vendor systems are decommissioned. The EMA expects inspectors to keep direct, read-only access to a decommissioned system's data, and expects archived formats to allow the database to be restored with its metadata (audit trail, event logs, edit checks, queries, and user logs). Where that isn't possible, data and metadata should be kept as dynamic files, and static copies of dynamic data aren't considered adequate (EMA, 2023, §4.11, §6.12). FDA guidance makes the same point when a hosted-system contract ends: the sponsor should make sure the metadata is obtained, retained, and linkable to each data element (FDA, 2024). E6(R3) asks for data and metadata to stay retrievable and readable, and protected from unauthorised access and alteration, for the whole retention period (ICH, 2025, §4.2.7).

For participant 1047, this is where the chain could break for good. If the lab's direct-measurement record and the email behind row 4 aren't in the sponsor's archive when the lab and CRO contracts close, row 4 can only be explained from memory. A programme that runs across several labs and CROs repeats that risk at every exit, which is why audit-ready clinical data at closeout depends on planning those exits at the start.

A receipt at every handover

None of this needs a new clinical data platform. Each handover needs to produce a record the trial keeps, and four changes do most of the work.

Each inbound transfer is logged as an event in its own right, with the file's hash, the specification version, the sending system, and a link to the reconciliation that checked it. A change that starts outside the repository carries its source identifier, such as the lab's accession number for the direct measurement. Correspondence that justifies a change is filed in the trial master file and linked to the data point. And data transfer agreements require those records to reach the sponsor's archive with each transfer.

The core component is a lineage store keyed on the data point, fed by events from each system and queryable by anyone with the right access. The unambiguous timestamps E6(R3) asks for, with UTC as its example (ICH, 2025, §4.2.2(d)), make it possible to put events from different organisations in one order. That store is itself a computerised system in scope for validation and access control (ICH, 2025, §4.3.4), so it belongs in the validation plan from the start. Sakura's Data & AI practice builds this layer for sponsors and CROs, usually starting from the transfers they already receive.

With those records in place, row 4 reads differently. The closure links to the lab's revised result and the reason for it, the result links to the transfer that carried it, and the transfer links to the reconciliation that caught it. An inspector can follow the chain outward from the data point using records the sponsor already holds. The risk-based review E6(R3) asks for (ICH, 2025, §4.2.3) gets easier too: when central monitoring flags a run of revised lab values from one transfer, the reasons are already linked.

The cheapest place to add a receipt is a contract that hasn't been signed yet. A data transfer agreement can name the events a lab or CRO must deliver, the identifiers they carry, and where they go when the contract ends, while retrofitting the same terms at closeout means renegotiating with a vendor who is already leaving. Sakura's GRC service helps sponsor teams write those terms and set up the checks that confirm they're met.

Not legal advice. This article offers general commentary on FDA 21 CFR Part 11 and ICH E6(R3) Good Clinical Practice for a pharma and healthcare engineering audience. It is not legal advice and is not a substitute for advice from qualified counsel or qualified regulatory affairs professionals. Specific obligations depend on the nature of your clinical programme, the jurisdictions in which you operate, your sponsor and CRO agreements, and the inspection regime to which you are subject. Readers must obtain independent legal and regulatory advice on how these regulations apply to their specific products and clinical operations.

References

European Medicines Agency (EMA), 2023. Guideline on computerised systems and electronic data in clinical trials. EMA/INS/GCP/112288/2023. European Medicines Agency. Available at: https://www.ema.europa.eu/en/documents/regulatory-procedural-guideline/guideline-computerised-systems-and-electronic-data-clinical-trials_en.pdf [Accessed 1 October 2026].

International Council for Harmonisation (ICH), 2025. ICH Harmonised Guideline: Guideline for Good Clinical Practice E6(R3). Adopted 6 January 2025. ICH. Available at: https://database.ich.org/sites/default/files/ICH_E6(R3)_Step4_FinalGuideline_2025_0106.pdf [Accessed 1 October 2026].

US Food and Drug Administration (FDA), n.d. 21 CFR Part 11: Electronic Records; Electronic Signatures. Electronic Code of Federal Regulations. Available at: https://www.ecfr.gov/current/title-21/chapter-I/subchapter-A/part-11 [Accessed 1 October 2026].

US Food and Drug Administration (FDA), 2024. Electronic Systems, Electronic Records, and Electronic Signatures in Clinical Investigations: Questions and Answers. Guidance for Industry. Procedural, Revision 1, October 2024. FDA. Available at: https://www.fda.gov/regulatory-information/search-fda-guidance-documents/electronic-systems-electronic-records-and-electronic-signatures-clinical-investigations-questions [Accessed 1 October 2026].

Legacy Modernisation That Meets the Audit Bar

Andrew Stevens — Thu, 01 Oct 2026 16:03:43 +0000

The audit manager had a sample of sixty benefit payments and a question about the forty-first. The department had replaced its benefits system partway through the year. The new platform was running well and the old one, which had been in service since before most of the team joined, had been switched off. The audit was for the annual accounts, and the conversation went like this.

The post-modernisation audit

Audit manager: Sample item 41. The payment went out after cutover, but the award was set up on the old system. I'll recalculate it myself, so I need the circumstances and rates that applied on each date, and the evidence for each change.

Transformation lead: The new platform has the award as it stood at cutover, and every change and payment since. That's all on screen.

Audit manager: The award changed twice before cutover, once for a change of circumstances and once on appeal. Where are those?

Transformation lead: We migrated the current state of each claim as an opening position. The history went into an archive extract.

Audit manager: Before I rely on the extract, how do you know it's complete and accurate?

Transformation lead: We reconciled total caseload and total weekly liability between the two systems. They matched to the penny.

Audit manager: That tells me nothing went missing in aggregate. It doesn't tell me this claim's history is right. Can I see the change history for it, with who made each change and why?

Transformation lead: The officer IDs came across. The reason codes lived in a lookup table on the old system, so some are just numbers now. And the links to the case files used the old system's IDs, which we didn't migrate, so I can't point you to the appeal decision from here.

Audit manager: Who has write access to the archive, and is that access logged?

Transformation lead: It's in a storage bucket with restricted access. I'd have to check the logging.

Audit manager: If I can't test the history on this claim, I can't test it on any migrated claim, and that's most of the caseload. I'll need to take that to my director, because it may affect the opinion.

Composite of several programmes; roles, not people.

The new platform works, and none of the audit manager's questions were about that. She was asking whether the department could show, for one specific claim, what was decided, by whom, on what evidence, and that the record hadn't changed since. The old system could answer, clumsily, because it kept a journal of every change to a claim and linked each one to its case file. The modernisation carried the functionality across and left most of that behind.

Evidence Versus Speed made the general case that evidence has to be produced by the systems themselves, not assembled afterwards. This post is about what happens to that evidence when the system holding it is replaced.

Where the standard migration playbook falls short

The cost-led playbook for legacy system replacement, which is as common in government as in industry, is sound for what it's designed to do. Move the current state, keep a copy of the history somewhere cheap, prove the new system works through testing and a parallel run, then switch the old one off to stop paying for it. Success is measured by cutover, feature parity, and the cost line that drops when the legacy contract ends.

The auditor's evidence standard is the same one a commercial auditor applies. The INTOSAI principles that supreme audit institutions draw on say that, in financial audits, the requirements on the auditor are the same as under the international auditing standards. They ask for evidence that is sufficient to persuade a knowledgeable person and appropriate, meaning relevant, valid, and reliable, and they list recalculation and reperformance among the ways to get it. If the evidence an auditor can't obtain could be material, the opinion is qualified, and if it's also pervasive, the auditor disclaims (INTOSAI, 2019). What government audit adds is regularity, testing whether transactions comply with the laws and authorities that govern them (INTOSAI, 2019). For a benefits payment, that means showing the award followed the legislation on the dates that mattered, which is impossible without the history.

Records law adds a second layer. In the US, federal agencies running electronic recordkeeping must keep audit trails that track use of records, and must have procedures to migrate records together with their associated metadata when technology changes. The same rule says backups don't count as a recordkeeping system (National Archives and Records Administration, n.d.). An archive extract in a storage bucket can easily end up closer to a backup than a record. The UK's National Archives, in guidance written for document and records management systems, warns that audit trails may not export at all, in which case they have to be kept accessible outside the system. It also warns that dates such as a record's creation date can be reset to the date of migration (The National Archives, 2017).

Records managers know all of this. The trouble is that it often sits outside the legacy modernisation plan. The US Government Accountability Office's review of the most critical federal legacy systems treated planned disposition of the old system as one of three minimum elements of a modernisation plan, alongside milestones and a description of the work, and found that most of the plans it examined didn't fully cover all three (Government Accountability Office, 2025).

The engineering disciplines that must deliver for audit

An audit-ready modernisation treats evidence as a workstream with its own requirements, tests, and sign-off, alongside functionality. In practice that comes down to a handful of disciplines.

Migrate the history as data, because this is where government data lineage is either carried across or lost. The change journal, with officer, timestamp, reason, and the before and after values, moves into the new estate as structured records that stay linked to the claim they belong to. Lookup tables travel with it, so a reason code still means something on the other side, and so do the links to case files and decision notices. Original dates are carried as data, never inferred from the load.

Reconcile at two levels. Control totals show nothing went missing. A record-by-record comparison of the old and new positions shows each record is right, and the comparison itself is kept, with every difference explained, because the reconciliation run is evidence an auditor will want to see.

Keep what a recalculation needs. Auditors recalculate entitlement from the legislation, the rates, and the claimant's circumstances, so the essentials are dated rate tables and the inputs to each decision. Where the new platform holds its rules as versioned, dated artefacts, past decisions can also be explained in the system's own terms. Keeping the old calculation runnable in a read-only form is a useful extra for claims likely to be revisited, not the core.

Keep the parallel-run results as a record. When both systems calculate the same claims for a period, the comparison is evidence, including the cases where they disagreed and how each was resolved.

Decommission last, against a checklist agreed with the people responsible for audit and records. The National Archives recommends agreeing clear acceptance criteria with suppliers before a migration starts (The National Archives, 2017). Evidence criteria belong on that list, and they're easiest to agree while the old system is still there to test against.

The evidence layer that moves with the workload

Those disciplines work best with an architectural home of their own: an evidence layer that sits beside the application rather than inside it.

Every consequential action on a claim (an award, a change of circumstances, a payment, or an override) writes a record to a store that the application can add to but not edit. Each record carries who acted, under what authority, on which evidence, and the original timestamp. Records are hash-chained, and the latest chain hash is signed at intervals and stored somewhere the store's own operators can't write, so a later change, even by an administrator, shows up when the chain is checked. Corrections are new entries that point to the record they correct. Access to the store is controlled and logged, which is the audit-trail requirement records law already sets. Disposal is designed in from the start too, because records schedules require eligible records to be deleted (National Archives and Records Administration, n.d.). One way is to encrypt each claim's records under its own key and destroy the key when the retention period ends, so the store can meet its disposal schedule without breaking the chain.

Migration then means loading history into the same layer with its provenance marked: which system it came from, when, and which reconciliation run verified it. An auditor can follow sample item 41 from its first award to the latest payment, with a marker showing exactly where the line between systems fell and which run verified the crossing. The chain only protects history from the moment it's loaded, so whether the history was right before then still rests on the reconciliation and on the old system's controls. That's why the reconciliation is kept.

The layer is also built to outlive the application. Benefits platforms get replaced, and in many departments the next replacement is already being planned. If the evidence lives inside the application, every modernisation has to solve this problem again. If it lives in its own layer, with its own retention rules and access controls, on whichever public sector cloud or data centre the department uses, the next migration moves the application and leaves the evidence where it is.

A lot of government IT transformation is still to come. In its report on government cyber resilience, the UK National Audit Office found departments still running hundreds of ageing legacy systems, with no fully funded remediation plans for half of them (National Audit Office, 2025). The GAO's list of the most critical federal systems includes several it describes as decades old (Government Accountability Office, 2025). We build the evidence layer into the first wave of a government cloud migration, before go-live rather than after it, as part of Sakura's Cloud practice.

What good looks like

Same sample, a different programme: item 41 again, this time where the history was migrated into an evidence layer. The audit manager sees both pre-cutover changes, with officers, readable reasons, original dates, and links to the case file and appeal decision. A provenance record ties them to a named reconciliation run, and the rates in force on each date are on file. She recalculates the award and gets the same figure. The migration is still reviewed as a significant change in the year, but against a reconciliation the department already holds, not one it has to rebuild under pressure.

Evidence Versus Speed traced the same move in clinical research and in the audit profession's own standards, from describing controls to producing evidence for a specific item on demand. Benefits audit has asked for evidence item by item for a long time. Modern platforms make that evidence cheap to keep, as long as the modernisation plans for it.

It does cost something. Migrating history properly takes longer than migrating current state, record-level reconciliation is harder than totals, and an evidence layer is another system to run. Those costs are usually smaller than rebuilding lost history once the old system has gone, and they're easiest to fund at the start, which is why evidence requirements belong in the business case and the supplier's acceptance criteria.

Before the old system is switched off, pick one claim that crosses the cutover and try to evidence it end to end. If you want a second pair of hands on that test, our GRC service has run it on live programmes, and the gaps it turns up become the programme's evidence requirements.

References

Government Accountability Office, 2025. Information Technology: Agencies Need to Plan for Modernizing Critical Decades-Old Legacy Systems. GAO-25-107795. US Government Accountability Office. Available at: https://www.gao.gov/products/gao-25-107795 [Accessed 1 October 2026].

INTOSAI, 2019. ISSAI 100: Fundamental Principles of Public-Sector Auditing. International Organisation of Supreme Audit Institutions. Available at: https://www.issai.org/wp-content/uploads/2019/08/ISSAI-100-Fundamental-Principles-of-Public-Sector-Auditing-1.pdf [Accessed 1 October 2026].

National Archives and Records Administration, n.d. 36 CFR 1236.20: What are appropriate recordkeeping systems for electronic records? Electronic Code of Federal Regulations. Available at: https://www.ecfr.gov/current/title-36/chapter-XII/subchapter-B/part-1236/subpart-C/section-1236.20 [Accessed 1 October 2026].

National Audit Office, 2025. Government cyber resilience. HC 546, Session 2024-25. National Audit Office. Available at: https://www.nao.org.uk/reports/government-cyber-resilience/ [Accessed 1 October 2026].

The National Archives, 2017. Migrating information between records management systems. The National Archives. Available at: https://cdn.nationalarchives.gov.uk/documents/information-management/edrms.pdf [Accessed 1 October 2026].

API Governance Done Properly

Andrew Stevens — Wed, 30 Sep 2026 12:44:04 +0000

Well after the platform had launched, the privacy officer sent the API platform team three questions. Which APIs are live? Who calls them? What personal data do they return? The team expected to answer from a dashboard. It turned into an investigation.

By the usual measures the programme had gone well. It opened an agency's services to other agencies and approved partners, teams shipped quickly, and partners integrated faster than planned. It also had a standards document, and most teams followed most of it. What it lacked was any way to see the estate it had built, so answering three plain questions meant going back through gateway logs, network scans, and interviews with engineers who had since moved on. We've merged several programmes into one here and changed the details that would identify them. Nothing below is invented.

Governance had become a project, with its own budget and deadline. In the programmes that avoid this, it's something the platform does every day without being asked. It matters in government because every credential an agency issues is a statement about who it trusts with citizens' data. A credential is a trust decision made in code, which is the argument of Trust Is an Engineering Output, and a programme opening public sector APIs to partners makes hundreds of them.

The first audit

The clearest way to see what went wrong is to follow one API: the eligibility check, the programme's busiest service. The audit found two versions of it running. The newer one filtered out fields partners didn't need. The older one didn't, and one partner was still calling it. Three clients shared a single API key, so the gateway could show that the API was busy but not who was busy. A test copy, loaded with production data so a partner could try things out, was still reachable months later. And the API's OpenAPI document described the service as it was at launch, which no longer matched what was deployed.

Elsewhere in the estate, two teams had put APIs straight behind a load balancer to meet a deadline, so the gateway didn't know they existed. The OWASP API Security Top 10 calls this improper inventory management, one of the ten most critical API risks, and its list of warning signs reads like the audit report: hosts with no clear purpose, no record of which version is running or who should reach it, stale documentation, and no retirement plan. Its first example is a beta host running production's API without production's protection. OWASP also advises against production data in non-production deployments, and says that where it can't be avoided, those deployments need the same protection as production (OWASP, 2023).

The test copy was the one finding nobody tried to defend. The privacy impact assessment had been done properly at launch, but it described the design, and by the time of the audit the design and the estate were two different things.

What governance after the fact buys you

Here is how the eligibility API got to that state, one sensible step at a time.

It was built in a sprint to meet a partner's go-live date, and the OpenAPI document was written afterwards for the developer portal. The design review board saw it early and approved it. Later the partner asked for three more fields, which were added in a minor release that never went back to the board. Someone added a row to the API register spreadsheet at go-live, and nobody updated it again. The partner received an API key by email and, a few weeks on, shared it with two contractors working on a joint service. After a privacy concern, the team shipped a version 2 with proper field filtering. Version 1 stayed up because nobody could say who was still calling it, and turning it off might break a service they didn't know about.

Each of those steps passed the governance the programme had: the board, the register, the standards document, and the plan for a periodic audit. That model buys real things. It gives security and privacy teams a place to ask questions, and the board brings judgement no tool can supply: whether a new data flow is justified, whether a field is needed, whether an existing API could be reused instead. What it can't do is keep up. The register drifts from the day it's finished. A board that meets on a fixed schedule becomes a queue, and teams under deadline find ways around queues. The standards document describes the rules but has no way to stop an API that breaks them, and a periodic audit reports on a single day.

Keep the board, and take routine conformance off its desk, so it only sees the decisions that need people, such as a new category of personal data or a new external consumer.

What governance built in looks like

Now run the same API through a platform that governs as it goes.

The first draft of the eligibility API arrives as an OpenAPI document, before any code. The UK's API technical and data standards recommend exactly this: make the OpenAPI document the first output of design, lint it for rule and security problems, and generate the reference documentation from it (Government Digital Service and Central Digital and Data Office [GDS and CDDO], 2026). The specification is a standard, language-agnostic description of an HTTP API that both people and tools can read (OpenAPI Initiative, n.d.). The draft includes a date-of-birth field, and the pipeline flags it because the field carries no data classification. OpenAPI has no built-in marker for personal data, so agencies add their own extension or link fields to a data catalogue. The privacy officer sees the question in a pull request before the API exists, instead of in an audit after partners depend on it.

A few months in, the partner asks for three more fields. The pull request fails the linter because none of them is classified, and the privacy officer approves two of the three in the review thread. Nothing reaches production without passing the same checks, and the deployed service is compared against its specification on every release, so the document the portal shows is the one that's running.

Publishing goes through one door. The gateway is the only way to reach an API from outside its network, and network policy enforces that, so the catalogue of externally reachable APIs stays complete for as long as the policy holds. Regular discovery scans of cloud accounts, DNS, and certificates check that it does, and they're what would have found the two APIs behind the load balancer. The partner's sandbox is generated from the specification and seeded with synthetic data. The UK standards say test services should never hold real personal or transactional data and should still require authentication (GDS and CDDO, 2026), so the exposed test copy from the first story never gets built. GSA's own API standards, which apply to GSA rather than the whole US government, require something similar for its public APIs: listing in its API directory, versioning, an OpenAPI file, and a shared management service that handles keys, rate limits, and usage analytics (General Services Administration, n.d.). That model runs on API keys rather than OAuth, which suits open data better than personal data, but the idea of one managed route with usage analytics carries over.

The contractors arrive next. Each is onboarded as its own consumer under the data-sharing agreement, with a client whose credential is bound to a key or certificate it holds, so a copied secret is useless and any one of them can be cut off without touching the others. The UK standards recommend OAuth 2.0 for API access control, with client credentials when a service calls on its own behalf, token exchange when it calls on behalf of a user, scopes checked on every request, and API keys avoided where possible. They also require agencies to log when personal or sensitive data is provided and to whom (GDS and CDDO, 2026).

Then version 2 ships. Telemetry shows which clients still call version 1, and GSA's standards describe using that kind of analytics data to notify recent users before a breaking change (General Services Administration, n.d.). The guidance pulls two ways here, because GSA asks teams to keep at least one previous major version running, while OWASP points out that every live version is attack surface and needs a retirement plan (OWASP, 2023). A workable rule is that an old version stays only while it has callers, and only with the same protection as the new one. The last partner still on version 1 moves across a few weeks after being told, and version 1 is switched off.

Sakura designed something close to this with SkillsFuture Singapore in 2020 (how SkillsFuture Singapore gated its API lifecycle). Each API moved through four stages (Inception, Build, Adoption, and Close), and each stage had entry and exit criteria. REST, OpenAPI, and JSON Schema were mandatory, the OWASP REST Security Cheat Sheet was enforced at the endpoint level, and nothing was published until it had passed a conformance test against the framework. The framework treated the teams building APIs and the teams consuming them as separate groups with different cadences and different controls. Standards were set centrally and day-to-day decisions stayed with the teams, which was intended to keep the gates from turning into a queue. It also kept a periodic check in place, a quarterly maturity assessment using Apigee Compass, because a platform that enforces the rules still benefits from someone asking whether they're the right ones.

The platform architecture that supports it

Underneath that walk-through, API platform architecture comes down to one component per stage of the API lifecycle, each leaving evidence behind as a side effect of doing its job.

Stage	Component	What it records
Design	Specification repository and linter	The OpenAPI document, field classifications, and lint results
Build	CI pipeline with conformance and security tests	Test results tied to each version
Publish	Gateway, developer portal, sandbox, and network policy	A catalogue entry generated from the specification
Consume	Identity provider, gateway scope checks, and authorisation in the API	Per-client credentials and a log of personal data provided
Change and retire	Traffic telemetry	Callers per version, per client
Around all of it	Discovery scans and drift checks	Anything reachable that the platform doesn't know about

The gateway's scope check only decides which operations a partner can call. The API itself still has to decide which records and which fields that partner may see. Together they're the technical side of a data-sharing agreement, and API security depends on both, though neither covers the agreement's terms on purpose, retention, and onward sharing.

API observability should also record every call by API, version, client, and response class, along with a pseudonymous reference to the record returned, without logging the payload itself. The privacy officer's first two questions then become a query, and the log can still show which client received a given person's record. The third question becomes a query too, once fields are classified in the specification and responses are validated against it.

Most agencies already own some of these parts. The UK standards note that access control, audit and logging, and network management are services an API management platform provides that rarely make sense to build yourself (GDS and CDDO, 2026). NIST's guidance on API protection sorts its controls into pre-runtime and runtime stages, and its March 2026 update adds an appendix listing recommended controls by API lifecycle stage, a useful cross-check for the table above (Chandramouli and Butcher, 2026). The harder work is the wiring between components, so each one feeds the next. Our Cloud practice builds that wiring on whichever clouds the agency already runs.

What belongs in the tender

Few agencies build every row of that table themselves. The gateway might be a managed service, identity a SaaS tenant, and telemetry a monitoring vendor's pipeline. The platform trusts all of them, and every one is run by an operator that answers to the law of one or more jurisdictions. The first post in this series followed an agency whose sovereign platform leaked through the services around it, and a directory synchronised to a global tenant was the largest single cause (Sovereign Cloud Is an Architecture, Not a Procurement). An API platform leans on identity even harder. The gateway's scope checks and the API's own record and field decisions both trust whatever the authorisation server issues. Anyone who operates that server, holds its signing keys, or can compel either of them can create access that the rest of the table will honour.

Most of the tenders we see still leave that surrounding estate to engineering after signature. Key custody is the exception, and it shows up in requirements more often now, usually as customer-held or externally managed keys. Identity and telemetry are the biggest gaps. It's rare for a tender to ask who operates the identity provider and under which law, or where logs and traces go once they leave the platform. The answers then arrive one integration at a time, after the price is fixed.

What works is a dependency inventory submitted with the tender. Each bidder lists every external service the platform needs in order to operate, with its operator and jurisdiction. That covers the identity provider and who holds its token-signing keys, where logs and traces are sent, the support access paths for the supplier and any subcontractors, the CI/CD tooling, and any managed gateway or developer portal. The agency adds what it supplies itself, such as an existing directory the platform will federate to, because no bidder's list will show that. Since the requirement is in the tender documents, every bidder answers the same question, and the inventory can be scored, set as pass or fail, or refined in a negotiation round where the procedure allows one. At award it becomes a contract schedule. Adding a service then needs notice and the agency's approval, much as GDPR already requires a processor to get written authorisation before engaging a sub-processor (European Parliament and Council, 2016, Article 28(2)). From there, egress monitoring checks the running estate against the schedule, so a new dependency shows up as drift instead of as an audit finding.

The trust the platform produces

Send the privacy officer's three questions to an agency running a government API platform built this way, and the answers come from data it already holds. Partners can rely on published contracts, managed versions, and notice before a breaking change. Auditors get evidence the platform produced while running, not a reconstruction. Delivery teams building government digital services find the governed path is also the quickest one, so they have fewer reasons to route around it.

Someone has to run the platform, and its gates have to be quick, or they become the review-board queue again in automated form. An agency with hundreds of existing APIs can't move them all at once. It can require the platform for every new API, then bring the existing estate across in order of risk, starting with APIs that return personal data or serve the most external consumers, and using gateway traffic plus a discovery scan to decide the order. Contracts for APIs that a supplier builds or hosts for the agency can require the same platform route, with the same specification and conformance checks as everything else.

The privacy officer will ask the same three questions again, and next time the answer should take a query, not an investigation. Keeping access control and security testing current as partners and threats change is the part our Security practice does with agency teams.

References

Chandramouli, R. and Butcher, Z., 2026. Guidelines for API Protection for Cloud-Native Systems. NIST Special Publication 800-228, June 2025, includes updates as of 13 March 2026. National Institute of Standards and Technology. Available at: https://csrc.nist.gov/pubs/sp/800/228/upd1/final [Accessed 30 September 2026].

European Parliament and Council, 2016. Regulation (EU) 2016/679 of the European Parliament and of the Council of 27 April 2016 on the protection of natural persons with regard to the processing of personal data and on the free movement of such data (General Data Protection Regulation). Official Journal of the European Union, L 119, 4 May, pp. 1-88. Available at: https://eur-lex.europa.eu/eli/reg/2016/679/oj [Accessed 30 September 2026].

General Services Administration, n.d. GSA API Standards. US General Services Administration. Available at: https://github.com/GSA/api-standards [Accessed 30 September 2026].

Government Digital Service and Central Digital and Data Office (GDS and CDDO), 2026. API technical and data standards. GOV.UK, last updated 30 September 2026. Available at: https://www.gov.uk/guidance/gds-api-technical-and-data-standards [Accessed 30 September 2026].

OpenAPI Initiative, n.d. OpenAPI Specification v3.2.1. The Linux Foundation. Available at: https://spec.openapis.org/oas/latest.html [Accessed 30 September 2026].

OWASP, 2023. API9:2023 Improper Inventory Management. OWASP API Security Top 10. Available at: https://owasp.org/API-Security/editions/2023/en/0xa9-improper-inventory-management/ [Accessed 30 September 2026].

Sovereign Cloud Is an Architecture, Not a Procurement

Andrew Stevens — Tue, 29 Sep 2026 09:22:01 +0000

The agency in this story is a composite, drawn from several engagements, though every detail in it is one we've seen. It signed a sovereign cloud contract after a careful procurement. The platform was certified, the data would stay in-country, and the provider's operating entity met the ownership conditions the policy asked for. Well into the second year, a team mapping dependencies for an unrelated migration counted thirty-two services in the agency's stack that still reached outside the sovereign boundary. The identity directory synchronised to a global tenant. Logs and traces went to a monitoring service hosted overseas, and that vendor's support staff, in another jurisdiction, could see them. A CI/CD service and a handful of developer tools each had their own route out. Separately, the platform's key management service was still running on the provider's defaults, with the settings that would have given the agency its own key custody left for a later phase.

The contract covered the platform well and said very little about everything around it. Procurement had bought what the policy described, a sovereign platform, and the engineering team inherited it the way engineering teams usually inherit contracts: after signature, with a delivery date attached. In our experience that's where sovereignty tends to leak, in the systems that surround the platform: who holds the keys, where people authenticate, what the data layer will accept, where telemetry goes, and what the contract lets engineers actually check. Our earlier series, The Engineering Underneath, made the cross-industry case that sovereignty gets settled in the architecture as much as at the procurement desk (see Sovereignty Versus Efficiency). Government cloud architecture is where that argument has the most at stake, and where the frameworks are explicit enough to show the gap clearly.

The thirty-two services

None of the thirty-two arrived as a sovereignty decision. The identity directory had been set up years earlier for email and collaboration, and the new platform simply federated to it. The monitoring service was already the agency's standard, so new workloads were pointed at it on day one. Endpoint agents, the CI/CD service, and the developer tools were each approved separately, by different teams, for good reasons. The platform's certification didn't catch them either, and it wasn't asked to. The agency's own system authorisations had each looked at one workload at a time, so nobody had assessed the estate as a whole.

Each government cloud framework points at the wider picture, although they differ a lot in how much sovereignty they demand. France's SecNumCloud qualification is aimed at protecting sensitive data from both cyber threats and extraterritorial law, and ANSSI notes that the qualification says nothing about the security of the customer services built on a qualified offer (ANSSI, n.d.). A qualified platform, in other words, doesn't make what runs on it sovereign. Australia's Hosting Certification Framework separates Assured certification from Strategic certification, the highest level, which is only available to providers that let government specify ownership and control conditions. It covers sensitive government data, whole-of-government systems, and systems rated PROTECTED (Department of Home Affairs, n.d.a), and it's currently being reformed, with new certification registrations paused since November 2025 (Department of Home Affairs, n.d.b). The UK takes a risk-based line. The National Cyber Security Centre's cloud guidance asks organisations to know where their data is and who can access it, including derivatives such as verbose logs, and it points out that data held in a local region may still be reached through an authentication service replicated worldwide (National Cyber Security Centre, 2018).

These regimes ask for very different amounts of sovereignty, and this post isn't arguing for more of it across all public sector cloud use. Most OFFICIAL workloads don't need a hard sovereign boundary, and building one for them is expensive. The argument is about where a commitment leaks once an agency has decided it needs one, whatever its jurisdiction and whichever provider it uses. For those workloads, a government cloud strategy built around the platform alone leaves most of the outcome to chance. Five decisions do most of the work, and in many agencies they sit with different owners, so nobody holds the whole picture.

The five decisions

Where the keys live

The first decision is who can decrypt the data. Encryption at rest is standard on every major platform. What varies is custody. If the provider generates and holds the keys, protection rests on the provider's controls and on the laws the provider answers to. If the agency holds the keys in its own hardware security modules, outside the provider's reach, a demand made of the provider largely yields ciphertext for data at rest, provided the provider's services can't be compelled to request key unwraps on the agency's behalf. Data in use is a separate problem, because anything the provider's services process in plaintext is still exposed, and that's where confidential computing and service design come in. Most arrangements sit in between, with customer-managed keys stored in the provider's key service, and the details of that middle ground matter.

For a lot of workloads, the provider's key service is the right answer, and the NCSC accepts a provider's cloud-hosted key management service as a way to protect keys through their lifecycle (National Cyber Security Centre, 2018). The higher the classification, the more the custody question matters, because sovereign data residency and actual control can part ways: data can sit in the right country on a certified platform and still be readable by whoever can reach the keys. Holding keys independently has real costs, including an operational burden, availability planning for the key service itself, and some platform features that stop working when the provider can't see plaintext. Those costs belong in the decision, made on purpose, rather than deferred to a phase that doesn't get funded.

Where the identity provider lives

Next comes where people and workloads authenticate. Whoever controls authentication controls access to everything the keys protect, and an agency can keep every byte in-country while routing every login, token, and administrative session through a service that lives elsewhere. That's the case the NCSC describes, and in the composite agency the directory synchronised to a global tenant was the largest single reason the boundary didn't hold.

A sovereign identity layer means the directory, the token service, and the privileged access paths for administrators sit inside the boundary, or are federated so the authoritative decision is made inside it. It also means support access gets engineered: who can open a privileged session, from where, with whose approval, and with what record left behind. Provider support access is sometimes necessary, and it should be rare, time-bound, approved by the agency, and logged somewhere the agency controls. Public sector security teams already hold their own administrators to that standard, and it has to reach the provider's people too. This is also the most expensive of the five to change. When the directory in question is the one that runs email and collaboration for the whole agency, moving it inside the boundary can be a multi-year programme, and for many workloads a well-controlled federation is the realistic answer.

What the data layer actually accepts

The third decision is whether the data layer enforces the rules on government data sovereignty or only documents them. In our experience most agencies classify their data carefully, and far fewer have a data layer that refuses to put a record where its classification says it can't go. Without enforcement, a backup policy replicates to a second region for resilience, an analytics team spins up a workspace in a default location, or a managed service creates a cache somewhere convenient. Each is a reasonable default, set by someone doing their job.

Enforcement means classification travels with the data as metadata, and placement rules are written as policy that the platform evaluates when resources are created and when data moves. A storage account in a region that isn't permitted fails to deploy, and a replication rule that crosses the boundary is rejected with a clear reason. Derived data counts too: backups, snapshots, replicas, extracts, and the features built from them for analytics. In a public sector multi-cloud estate this gets harder, because each provider expresses these controls differently, and the agency needs one policy that means the same thing everywhere it runs.

Where the telemetry goes

Telemetry catches a lot of teams. Logs, traces, metrics, and crash reports are data about the system, and they very often carry data from it: identifiers, request payloads, query parameters, and error messages that quote the record they failed on. The NCSC lists credentials, configuration data, derived metadata, and logs among the data types most often overlooked (National Cyber Security Centre, 2018). In the composite agency, the overseas monitoring service was receiving a steady stream of exactly that, and the vendor's support staff could read it.

Keep telemetry inside the boundary, or scrub it before it leaves, so observability doesn't become the easiest way out. Then watch what leaves at all. An agency can only show where its data goes for the traffic it can see, and egress monitoring that records every connection leaving the boundary, by service and destination, would have surfaced most of the thirty-two on day one rather than well into the second year. It won't show key custody or how federation is configured, which is why it sits alongside the other decisions, and it's also what catches the next developer tool that ships with an undeclared path home.

The contract the engineering team can hold

The fifth decision is to write the sovereign commitment in terms engineers can test. Procurement teams do careful work on ownership, jurisdiction, liability, and exit. What often goes unwritten are the technical conditions that make those terms real, and they fall into two groups. Some belong in the contract, because the provider has to support and evidence them: where keys can be held, which regions data may occupy, how provider support access is approved and recorded, and what an exit looks like in practice. Others are the agency's own architecture decisions, such as where identity is resolved and where telemetry may go, and they belong in a design record kept alongside the contract, so the engineering team is held to them as firmly as the provider is held to its side. Exit deserves a sentence of its own, because an agency that can't move a workload off a provider in a stated time, or keep it running if the provider's global control plane is cut off, has less control than its contract suggests.

The broader shift in government assurance points the same way. FedRAMP 20x, the US programme's overhaul of cloud security assessment and certification, rests on principles that treat a provider's security as something to be enforced and reported continuously, and that give ongoing evidence more weight than written policy (FedRAMP, 2026). Those principles are aimed at providers, and they work just as well for the conditions an agency writes down for itself. Singapore's Government on Commercial Cloud is a platform rather than a certification regime, and its emphasis is assurance and speed more than sovereignty: it gives agencies a standardised way onto commercial cloud and builds continuous compliance, observability, and auditability into the platform. GovTech reports that more than seventy percent of eligible government systems now run on it (GovTech Singapore, 2025).

Sakura saw a small version of the same idea with SkillsFuture Singapore, where controls were engineered into the platform and every API was tested for conformance before release (SkillsFuture Singapore case study). Building the enforced, provider-neutral foundation that makes conditions like these testable is a large part of what Sakura's Cloud practice does for public sector clients.

The practical step is for procurement and engineering to sit down together before the tender or call-off goes out, so that sovereign cloud procurement specifies the architecture around the platform as well as the platform itself, and the engineering team inherits a commitment it can check.

Turning those conditions into controls an agency can evidence on demand, and keeping them true as the estate changes, is work Sakura's GRC service does alongside agency engineering and procurement teams.

References

ANSSI, n.d. Cloud: posture générale et actions de l'ANSSI sur le cloud (SecNumCloud). Agence nationale de la sécurité des systèmes d'information. Available at: https://cyber.gouv.fr/enjeux-technologiques/cloud/ [Accessed 29 September 2026].

Department of Home Affairs, n.d.a. Hosting Certification Framework: framework overview. Australian Government. Available at: https://www.hostingcertification.gov.au/framework [Accessed 29 September 2026].

Department of Home Affairs, n.d.b. Hosting Certification Framework: home. Australian Government. Available at: https://www.hostingcertification.gov.au/ [Accessed 29 September 2026].

FedRAMP, 2026. FedRAMP 20x. US General Services Administration. Available at: https://www.fedramp.gov/20x [Accessed 29 September 2026].

GovTech Singapore, 2025. Government on Commercial Cloud (GCC). Government Technology Agency of Singapore, last updated 26 September 2025. Available at: https://www.tech.gov.sg/products-and-services/for-government-agencies/software-development/government-on-commercial-cloud/ [Accessed 29 September 2026].

National Cyber Security Centre, 2018. Cloud security guidance, Principle 2: asset protection and resilience. Version 2.1, reviewed 7 June 2023. Available at: https://www.ncsc.gov.uk/collection/cloud/the-cloud-security-principles/principle-2-asset-protection-and-resilience [Accessed 29 September 2026].