Event Sourcing Instead of CRUD: Building Immutable Audit Logs and Financial Ledgers in BigQuery

Data warehouse environments require a fundamental shift in how we handle data mutations. In operational databases (OLTP), rewriting existing rows using Create, Read, Update, and Delete (CRUD) operations is standard practice. In analytical data warehouses (DWH) like Google BigQuery, overriding data is an anti-pattern. Relying on CRUD in a DWH leads to a loss of historical context, severe performance degradation, and uncontrollable billing costs. The structural alternative is Event Sourcing: moving away from storing the current state and instead recording every change as an immutable event.

1. The Mechanical Clash: Why BigQuery Rejects CRUD

BigQuery is built on a columnar storage architecture designed to scan petabytes of data rapidly. It is not optimized for row-level mutations.

When you execute an UPDATE statement in a relational database, the engine modifies a specific block on the disk. When you execute an UPDATE in BigQuery, the system must read the entire affected file, rewrite the modified data into a new storage partition, and mark the old partition as deleted (a process known as tombstoning). This makes row updates mechanically expensive.

Furthermore, BigQuery imposes strict quotas on Data Manipulation Language (DML) operations. If a data pipeline attempts to process thousands of status updates per minute using standard UPDATE statements, it will quickly hit table mutation limits, causing pipeline failures.

Most importantly, CRUD destroys history. In financial systems or compliance audits, deleting or overwriting a record is unacceptable. If an account balance changes from $100 to $150, an UPDATE operation permanently erases the fact that the balance was ever $100. Event Sourcing solves this by ensuring that data is never deleted, only appended.

  subgraph ES [Event Sourcing]
      direction TB
      E1(T1: Append Event<br>Deposit = $100) --> E2(T2: Append Event<br>Deposit = $50<br><i style='font-size:12px; color:#22c55e'>History preserved</i>)
      E2 --> E3(T3: Append Event<br>Withdrawal = $100<br><i style='font-size:12px; color:#22c55e'>History preserved</i>)
      class E1,E2,E3 immutable;
  end

  classDef destructive fill:#fee2e2,stroke:#ef4444,stroke-width:2px,color:#7f1d1d;
  classDef immutable fill:#ecfdf5,stroke:#10b981,stroke-width:2px,color:#064e3b;
  classDef default fill:#ffffff,stroke:#cbd5e1,stroke-width:1px,color:#334155;

2. Designing the Immutable Ledger

To implement Event Sourcing, you must abandon wide “state tables” and adopt an event store. An event store is a ledger of facts. Instead of storing an Account entity with a balance column, you store discrete actions: AccountCreated, DepositProcessed, or WithdrawalCompleted.

A robust event schema requires a few standard columns: a unique event identifier, an entity identifier, a timestamp, an event type, and a payload. By utilizing JSON or BigQuery STRUCT types for the payload, the table schema remains stable even if new event types require different data fields.

Terraform

# Terraform example: Provisioning an append-only event ledger in BigQuery
resource "google_bigquery_table" "financial_events" {
  dataset_id = google_bigquery_dataset.core.dataset_id
  table_id   = "raw_financial_events"

  time_partitioning {
    type  = "DAY"
    field = "event_timestamp"
  }

  cluster_by = ["account_id", "event_type"]

  schema = <<EOF
[
  {"name": "event_id", "type": "STRING", "mode": "REQUIRED"},
  {"name": "account_id", "type": "STRING", "mode": "REQUIRED"},
  {"name": "event_timestamp", "type": "TIMESTAMP", "mode": "REQUIRED"},
  {"name": "event_type", "type": "STRING", "mode": "REQUIRED"},
  {"name": "amount", "type": "NUMERIC", "mode": "NULLABLE"}
]
EOF
}

This table is optimized for append-only operations. Streaming inserts can write millions of rows per second without hitting DML limits, and partitioning by day ensures that future analytical queries scan only relevant data blocks.

3. Reconstructing State via Projections

If the database only contains a chronological list of events, obtaining a current account balance requires calculating it on the fly. This process is called building a projection, or a read model.

In modern data engineering, dbt (data build tool) is the standard for transforming raw events into usable business models. To reconstruct the balance, we aggregate the historical events using standard SQL arithmetic.

SQL

-- dbt model: models/marts/finance/dim_account_balances.sql

{{ config(materialized='view') }}

WITH event_ledger AS (
    SELECT 
        account_id,
        event_type,
        amount,
        event_timestamp
    FROM {{ ref('raw_financial_events') }}
),

calculated_state AS (
    SELECT 
        account_id,
        SUM(
            CASE 
                WHEN event_type = 'DEPOSIT' THEN amount
                WHEN event_type = 'WITHDRAWAL' THEN -amount
                WHEN event_type = 'FEE_APPLIED' THEN -amount
                ELSE 0 
            END
        ) AS current_balance,
        MAX(event_timestamp) AS state_updated_at,
        COUNT(*) AS total_transactions
    FROM event_ledger
    GROUP BY account_id
)

SELECT * FROM calculated_state

4. FinOps and Performance Economics

Calculating state from raw events on demand works well during early development, but it becomes a major financial risk at scale. BigQuery charges based on the volume of data scanned during a query.

The basic cost calculation is: Total Query Cost = (Bytes Scanned / 1 TB) * Price per TB.

If an event table grows to 10 terabytes, recalculating the current balance for a single customer by scanning the entire table will cost exactly the same as calculating the balance for all customers. Executing this full table scan repeatedly throughout the day will rapidly deplete the analytics budget.

To optimize FinOps, engineers utilize snapshots. Instead of keeping the dbt model as a dynamic view, it should be transformed into an incremental model or a BigQuery Materialized View. This caches the calculated state at regular intervals (e.g., midnight). The next day’s queries only need to scan the cached state plus the small delta of new events that occurred since the snapshot was taken.

Storage costs for compressed columnar logs are remarkably low compared to compute costs. Storing billions of immutable events is economically viable; scanning them repeatedly is not.

5. Advanced Consistency and Distributed Architecture

As pipelines scale, data engineers must account for the complexities of distributed systems, specifically duplicate messages and out-of-order data.

Idempotency and Deduplication: Message brokers like Pub/Sub or Apache Kafka guarantee “at-least-once” delivery. This means a single withdrawal event might arrive at the DWH twice due to network retries. In a CRUD system, applying a withdrawal twice deducts the money twice, corrupting the balance. In an Event Sourcing system, we enforce idempotency downstream. Using dbt, we apply a QUALIFY ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY event_timestamp) = 1 filter in the staging layer to ignore duplicate event_id hashes before they reach the financial aggregation.

Late-Arriving Data: Distributed systems suffer from latency. An event generated on Tuesday might not reach BigQuery until Thursday. A CRUD system struggles here, as the Thursday state might accidentally overwrite a valid Wednesday update. Event Sourcing handles this gracefully: the delayed event is simply appended to the ledger with its original Tuesday timestamp. The next time the dbt read model rebuilds, it automatically processes the events in the correct chronological order, naturally correcting the historical timeline.

Compensating Transactions: In strict financial architectures, fixing an error requires an explicit trace. If a deposit of $500 was recorded by mistake, you cannot execute a DELETE command to remove the row. Instead, the application must issue a Compensating Transaction—a new event explicitly categorized as DepositReversed for $500. This preserves the absolute integrity of the audit log, proving to compliance teams exactly when the error occurred and exactly when the correction was applied.

Similar Posts