Storing Postgres Changes as Delta Tables (Change History)
Who this is for:
Architecture / Concept Overview: Storing Postgres Changes as Delta Tables (Change History)
Change history works by tailing the Lakebase write-ahead log and writing CDC (Change Data Capture) records to a Delta table in Unity Catalog. Each record includes the operation type, timestamp, and full row payload.
%%{init: {"theme":"base","themeVariables":{"background":"#0B0E14","primaryTextColor":"#E0E6ED","lineColor":"#5D6470","darkMode":true,"primaryColor":"#2E4A4A","secondaryColor":"#374151","secondaryTextColor":"#E0E6ED","tertiaryColor":"#111827","tertiaryTextColor":"#E0E6ED","edgeLabelBackground":"#1f2937"}}}%%
flowchart LR
classDef source fill:#3F4B59,stroke:#9CA3AF,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef ingestion fill:#5A4B36,stroke:#C9A86B,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef processing fill:#535072,stroke:#8E82B4,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef storage fill:#2E4A4A,stroke:#5FAFA8,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef serving fill:#3D5550,stroke:#6BB7AA,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef governance fill:#5A3F52,stroke:#C28BB0,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
A[Application Writes] -->|INSERT/UPDATE/DELETE| B[Lakebase Engine]
B -->|WAL Tail| C[Change Capture Process]
C -->|CDC Records| D[Delta Table in Unity Catalog]
D -->|Query| E[Audit Reports]
D -->|Feed| F[Downstream Pipelines]
D -->|Reconstruct| G[Point-in-Time Views]
A:::source
B:::processing
C:::ingestion
D:::storage
E:::serving
F:::serving
G:::governance
*Change history captures every mutation from the Lakebase WAL and writes it as immutable CDC records in Delta format.*
%%{init: {"theme":"base","themeVariables":{"background":"#0B0E14","primaryTextColor":"#E0E6ED","lineColor":"#5D6470","darkMode":true,"primaryColor":"#2E4A4A","secondaryColor":"#374151","secondaryTextColor":"#E0E6ED","tertiaryColor":"#111827","tertiaryTextColor":"#E0E6ED","edgeLabelBackground":"#1f2937"}}}%%
graph TD
classDef source fill:#3F4B59,stroke:#9CA3AF,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef ingestion fill:#5A4B36,stroke:#C9A86B,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef processing fill:#535072,stroke:#8E82B4,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef storage fill:#2E4A4A,stroke:#5FAFA8,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef serving fill:#3D5550,stroke:#6BB7AA,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
classDef governance fill:#5A3F52,stroke:#C28BB0,stroke-width:2px,rx:8,ry:8,color:#E0E6ED
A[CDC Record Schema] --> B[_change_type]
A --> C[_commit_timestamp]
A --> D[_commit_version]
A --> E[Original Columns]
B --> F[INSERT / UPDATE_BEFORE / UPDATE_AFTER / DELETE]
A:::processing
B:::ingestion
C:::governance
D:::governance
E:::source
F:::storage
*Each CDC record contains metadata columns alongside the original row data, identifying the operation type and exact timestamp.*
Key Terms
Prerequisites and Setup
- A Lakebase database with tables you want to track
- Unity Catalog metastore attached to the workspace
- A target catalog and schema in Unity Catalog for storing change history tables
- Permissions to enable change history on the Lakebase database
Step-by-Step Implementation
Configuration Reference
| Parameter | Description | Default |
|---|---|---|
change_history.enabled | Enable or disable change tracking | false |
change_history.target_catalog | Unity Catalog catalog for the CDC table | Required |
change_history.target_schema | Schema for the CDC table | Required |
change_history.target_table | Table name for CDC records | {source_table}_changes |
change_history.retention_days | Days to retain change records before cleanup | 90 |
change_history.capture_before_image | Include UPDATE_BEFORE records | true |
change_history.batch_interval | Frequency of CDC batch writes | 1 minute |