Zero Dirty Reads: Building a Trustworthy Lakehouse with DuckDB
Forma Engineering Blog · Series Part 3 (Finale)
TL;DR
"Lakehouse" sounds good, but every engineer ends up asking the same thing: how do I know the data I'm querying isn't stale or dirty?
This post explains how Forma uses Anti-Join and a Dirty Set to keep federated queries from reading uncommitted or inconsistent data. PostgreSQL handles the present, DuckDB and Parquet handle the past, and together they produce zero dirty reads.
Why do we need a lakehouse?
The first two posts solved OLTP problems. Part 1 built AI-ready flexible storage from hot tables and JSON Schema. Part 2 killed N+1 queries with CTE + JSON_AGG, taking latency from 1 second to 25 milliseconds.
One question is still open: when data reaches billions of records and a single PostgreSQL instance can't handle it, what then?
Even with hot table indexes, once the EAV table reaches 100 million rows and historical data in Parquet reaches terabytes, single-machine PostgreSQL runs into memory and I/O limits.
Historical data is also accessed completely differently from real-time data:
| Data Type | Access Frequency | Access Pattern | Typical Scenario | Query Share |
|---|---|---|---|---|
| Last 7 days | Hundreds/second | Point queries, filtering, pagination | Daily operations | ~80% |
| 7-90 days | Dozens/day | Bulk exports, reports | Monthly analysis | ~15% |
| 90+ days | Few times/month | Full scans, aggregations | Annual audits | ~5% |
Tuning a table for the last 7 days while it also holds 3 years of history wastes resources on both sides.
The appeal of a lakehouse
Hot/cold separation is the obvious answer. Hot data stays in PostgreSQL with transactional consistency and low-latency indexes. Cold data gets exported to Parquet files on S3 and queried with an OLAP engine.
The architecture diagram looks clean:
┌─────────────────────────────────────────────────────────────┐
│ Query Router │
└─────────────────────────────────────────────────────────────┘
│ │
▼ ▼
┌───────────────────┐ ┌───────────────────┐
│ PostgreSQL │ │ DuckDB │
│ (Hot Data) │ │ (Cold Data) │
│ Last 7 days │ │ Parquet on S3 │
└───────────────────┘ └───────────────────┘And then every engineer who hears "Lakehouse" has the same voice in their head:
"Wait, if the same record exists in both PostgreSQL and Parquet, which version do I get? If PostgreSQL data hasn't synced to Parquet yet, will I read stale data? Or worse, duplicate data?"
That is the consistency fear, and it is the biggest reason teams hold off on lakehouse architecture.
"Wait, isn't EAV an anti-pattern?"
Historically, yes, EAV (Entity-Attribute-Value) is considered an anti-pattern, and for good reason. Traditional EAV implementations have horrific query performance, because the N+1 problem turns simple queries into thousands of database round-trips. They have no type safety, since everything becomes a string and you lose integer comparisons and date sorting. The code is unmaintainable, with dynamic pivot queries scattered everywhere. And there is no workable indexing strategy, because you can't index "any possible attribute," so every query is a full table scan.
If EAV has burned you before, the skepticism is earned.
How Forma tames it
Forma addresses each of those problems with a specific technical choice:
| EAV Problem | Forma's Solution | Where It's Covered |
|---|---|---|
| N+1 query nightmare | CTE + JSON_AGG (single round-trip) | Part 2 |
| No type safety | JSON Schema validation on write | Part 1 |
| No indexing | Hot Table with B-tree indexes for frequent fields | Part 1 |
| Dirty data in lakehouse | Dirty Set quarantine (Anti-Join) | This post (below) |
The data quarantine zone
The Dirty Set is a quarantine zone for data. Think of airport security: before data is cleared for travel (flushed_at > 0), it stays in the holding area (PostgreSQL). Once cleared, it can proceed to its destination (Parquet). That explicit state tracking, rather than timestamps or heuristics, is what makes the system trustworthy.
Any record that has been modified but not yet synced to cold storage is quarantined. Queries fetch it from PostgreSQL, the source of truth, instead of Parquet, which may be stale. No guessing, no race conditions, no hoping the timestamps line up.
Putting it together
EAV is powerful and easy to misuse, so we put four guard rails around it: type validation through JSON Schema (Part 1), indexed hot fields for the 20% of attributes that get 80% of queries (Part 1), single-query aggregation through CTE + JSON_AGG (Part 2), and explicit sync-state tracking through the Dirty Set (this post).
What comes out is flexibility without the chaos. AI applications can evolve their data structures freely, without the historical baggage of EAV's reputation.
The root of consistency fear
Take a concrete record, row_id = 123:
- 09:00: a user creates the record; it's written to PostgreSQL
- 09:05: the CDC job exports it to Parquet
- 09:10: the user updates it, so the PostgreSQL value changes
- 09:15: the user issues a query
Which version should the 09:15 query return?
| Data Source | row_id | Version | Status |
|---|---|---|---|
| PostgreSQL | 123 | v2 | Latest (09:10 update) |
| Parquet | 123 | v1 | Stale (09:05 export) |
A query engine that naively merges both sources gives users duplicates (the same record twice, v1 and v2), dirty reads (v1, the stale version), or phantom reads (sometimes v1, sometimes v2, depending on timing). None of those are acceptable.
Why timestamp comparison isn't enough
The obvious fix is deduplicating on updated_at:
SELECT * FROM (
SELECT *, 'pg' AS source FROM postgres_data
UNION ALL
SELECT *, 's3' AS source FROM parquet_data
)
WHERE row_number() OVER (PARTITION BY row_id ORDER BY updated_at DESC) = 1It has fatal flaws. PostgreSQL and CDC job clocks can differ by milliseconds. If a record is updated while an export is in progress, the two updated_at values might be identical. And if a record is deleted in PostgreSQL, the old version in Parquet can resurrect itself.
Timestamp comparison is optimistic. It assumes timestamps perfectly reflect data freshness, and in a distributed system that assumption is dangerous.
Forma's solution: Anti-Join + Dirty Set
Forma takes the pessimistic route and trusts state instead of timestamps:
If a record in PostgreSQL hasn't been flushed to Parquet yet, then regardless of whether Parquet has this record, ignore the Parquet version and use only the PostgreSQL version.
The change_log table
Forma maintains a change_log table in PostgreSQL:
CREATE TABLE change_log (
id BIGSERIAL PRIMARY KEY,
schema_id UUID,
row_id UUID,
op SMALLINT, -- 1=INSERT, 2=UPDATE, 3=DELETE
created_at BIGINT, -- Change timestamp
flushed_at BIGINT -- Export timestamp; 0 = not exported
);The key field is flushed_at. At 0, the change hasn't synced to Parquet, so the data is dirty. Above 0, it has synced, and the data is clean.
Anti-Join logic at query time
When a user issues a query, Forma's DuckDB query engine runs this logic:
SQL implementation:
-- Step 1: Get dirty set (row_ids not yet flushed)
dirty_ids AS (
SELECT row_id
FROM change_log
WHERE flushed_at = 0 AND schema_id = $SCHEMA_ID
),
-- Step 2: Read from Parquet, but exclude records in dirty set
s3_clean AS (
SELECT *
FROM read_parquet('s3://bucket/data/*.parquet')
WHERE row_id NOT IN (SELECT row_id FROM dirty_ids) -- Anti-Join!
),
-- Step 3: Read dirty data from PostgreSQL (latest version)
pg_hot AS (
SELECT *
FROM postgres_scan('SELECT * FROM entity_main WHERE ...')
WHERE row_id IN (SELECT row_id FROM dirty_ids)
),
-- Step 4: Merge
SELECT * FROM s3_clean
UNION ALL
SELECT * FROM pg_hotAs a formula:
$$Result = (Parquet_{data} \setminus DirtySet) \cup PostgreSQL_{hot}$$
In plain English: from Parquet, keep only records that have been flushed and have no newer version. From PostgreSQL, keep only records that haven't been flushed or were just updated. The union of the two gives each record exactly once, always at its latest version.
Why this is pessimistic, and safe
Freshness is decided by explicit sync state, not by timestamps:
| Scenario | Dirty Set | Parquet | PostgreSQL | Returns |
|---|---|---|---|---|
| Record only in PG | row_id ∈ Dirty | None | Exists | PG version |
| Record synced, no updates | row_id ∉ Dirty | Exists | Exists (same) | Parquet version |
| Record synced, then updated | row_id ∈ Dirty | Exists (old) | Exists (new) | PG version |
| Record deleted in PG | row_id ∈ Dirty | Exists (old) | None | Not returned |
In every one of those cases the user sees the latest, consistent data.
Analogy: orders still in transit
Imagine you run an online store with two ledgers. The local ledger (PostgreSQL) records every order in real time. The cloud ledger (Parquet) receives a sync from the local ledger every night.
Someone asks: what's today's total sales?
Adding up both ledgers is wrong, because orders that are still syncing get counted twice.
The right approach has four steps:
- Check which orders haven't synced to the cloud yet. Those are the dirty set.
- Take the cloud ledger data, excluding those in-transit orders.
- Take the local ledger data, counting only those in-transit orders.
- Add the two together.
That's Anti-Join plus Dirty Set.
CDC flow: how data moves from PostgreSQL to Parquet
On write: record changes
Every write to entity_main or eav_data also inserts into change_log:
-- Application writes data
INSERT INTO entity_main (...) VALUES (...);
INSERT INTO eav_data (...) VALUES (...);
-- Record change (flushed_at = 0 means not exported)
INSERT INTO change_log (schema_id, row_id, op, created_at, flushed_at)
VALUES ($schema_id, $row_id, 1, now(), 0);CDC job: incremental export
The CDC job runs periodically, every minute by default:
-- 1. Find row_ids pending export
SELECT DISTINCT row_id FROM change_log
WHERE schema_id = $SCHEMA_ID AND flushed_at = 0;
-- 2. Read complete records, flatten EAV to wide table
SELECT m.row_id, m.text_01 AS name, m.integer_01 AS age, ...
FROM entity_main m
LEFT JOIN eav_data e ON m.row_id = e.row_id
WHERE m.row_id IN ($PENDING_IDS);
-- 3. Write to Parquet
COPY (...) TO 's3://bucket/delta/<uuid>.parquet';
-- 4. Mark as exported
UPDATE change_log SET flushed_at = now()
WHERE row_id IN ($PENDING_IDS) AND flushed_at = 0;Full data flow
┌─────────────────────────────────────────────────────────────────────┐
│ Query Path │
│ DuckDB: (Parquet - DirtySet) ∪ (PostgreSQL ∩ DirtySet) │
└─────────────────────────────────────────────────────────────────────┘Failure modes and self-healing
Distributed systems fail. Forma's job is to keep data uncorrupted when that happens, scenario by scenario.
CDC crash recovery
The most dangerous moment in any sync system is a mid-export crash. What happens if the CDC job dies after writing to S3 but before updating flushed_at?
Write order is what saves you:
┌─────────────────────────────────────────────────────────────────────┐
│ CDC Job Execution Order │
│ │
│ 1. BEGIN TRANSACTION (PostgreSQL) │
│ 2. SELECT * FROM entity_main WHERE row_id IN (dirty_ids) │
│ 3. Write to S3 Parquet ◄─── If crash here, Parquet has data │
│ 4. UPDATE change_log SET flushed_at = now() ◄─── but PG doesn't │
│ 5. COMMIT │
└─────────────────────────────────────────────────────────────────────┘Say it crashes after step 3 and before step 4:
| Component | State After Crash |
|---|---|
| S3 Parquet | Contains the exported data |
| change_log.flushed_at | Still 0 (not updated) |
| Next query | Will fetch from PostgreSQL (correct!) |
That is safe by design. Because flushed_at is only updated after the S3 write succeeds, a crash leaves the record in the Dirty Set. The query engine sees flushed_at = 0, marks the record dirty, fetches it from PostgreSQL, and ignores the orphaned Parquet file, which gets overwritten on the next successful export.
┌─────────┐ ┌─────────┐ ┌─────┐ ┌─────────┐
│ CDC Job │ │ PG │ │ S3 │ │ Query │
└────┬────┘ └────┬────┘ └──┬──┘ └────┬────┘
│ 1. Read dirty IDs │ │ │
│◄───────────────────│ │ │
│ │ │ │
│ 2. Read full data │ │ │
│◄───────────────────│ │ │
│ │ │ │
│ 3. Write Parquet │ │ │
│──────────────────────────────────────▶│ │
│ │ │ │
│ ╔═══════════════╧══════════════════╧════╗ │
│ ║ ⚡ CRASH HERE ║ │
│ ╚═══════════════╤══════════════════╤════╝ │
│ │ │ │
│ │ │ 4. Query arrives │
│ │ │◄─────────────────│
│ │ │ │
│ │ 5. Check dirty │ │
│ │ (flushed=0) │ │
│ │◄─────────────────┼──────────────────│
│ │ │ │
│ │ 6. Return PG data│ │
│ │─────────────────────────────────────▶
│ │ (Parquet ignored - record is dirty)│The ACID guarantee chain
Forma's consistency leans on PostgreSQL's ACID properties at three points.
On the write path, from application to PostgreSQL:
BEGIN;
INSERT INTO entity_main (...) VALUES (...);
INSERT INTO eav_data (...) VALUES (...);
INSERT INTO change_log (row_id, flushed_at) VALUES ($1, 0);
COMMIT; -- All-or-nothing: either all three inserts succeed, or noneIf the transaction fails, no partial data exists. The record either fully exists with flushed_at = 0, or doesn't exist at all.
On the export path, from PostgreSQL to S3:
BEGIN;
-- Read is consistent within transaction
SELECT * FROM entity_main WHERE row_id IN (SELECT row_id FROM change_log WHERE flushed_at = 0);
-- Write to S3 (outside transaction, but idempotent)
-- ...S3 PUT...
-- Only mark as flushed AFTER S3 confirms
UPDATE change_log SET flushed_at = now() WHERE row_id IN ($exported_ids) AND flushed_at = 0;
COMMIT;The AND flushed_at = 0 clause is what makes this idempotent, preventing double-marking when CDC jobs run concurrently.
On the query path, DuckDB reads both sources as a point-in-time snapshot: change_log for the Dirty Set, Parquet (immutable files, so no concurrent write issues), and PostgreSQL under snapshot isolation. No locks are needed, so readers never block writers and writers never block readers.
Failure mode summary
| Failure Scenario | Data State | Recovery Action | Data Lost? |
|---|---|---|---|
| App crash mid-write | PG transaction rolled back | Automatic (ACID) | No |
| CDC crash before S3 | No changes | Automatic retry on next run | No |
| CDC crash after S3, before PG update | S3 has data, PG says "dirty" | Query fetches from PG (correct); S3 file orphaned | No |
| S3 write fails | PG unchanged | Automatic retry on next run | No |
| DuckDB query fails | No side effects | Client retry | No |
| PostgreSQL down | Queries fail | Fall back to Parquet-only (degraded mode) | No* |
*In degraded mode, queries may return slightly stale data from the last successful export. Forma can optionally block queries instead, when zero dirty reads is mandatory.
Graceful degradation
When DuckDB or S3 becomes unavailable, Forma degrades rather than crashing:
┌─────────────────────────────────────────────────────────────────────┐
│ Degradation Modes │
├─────────────────────────────────────────────────────────────────────┤
│ Normal: PostgreSQL (hot) + DuckDB/Parquet (cold) → Full data │
│ S3 down: PostgreSQL only → Hot data only (recent records) │
│ PG down: DuckDB/Parquet only → Cold data only (may be stale) │
│ Both down: Service unavailable (circuit breaker triggered) │
└─────────────────────────────────────────────────────────────────────┘The application picks the behavior: strict mode rejects queries if any data source is unavailable, best-effort mode returns available data with a warning header, and cached mode returns results from the previous successful query.
Last-write-wins: handling residual duplicates
Anti-Join resolves PostgreSQL against Parquet. Parquet against itself is a separate problem.
Because CDC exports incrementally, the same record can exist in multiple Parquet files at different versions:
delta/001.parquet: row_id=123, version=1delta/002.parquet: row_id=123, version=2
Forma uses QUALIFY ROW_NUMBER() for last-write-wins:
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY updated_at DESC) AS rn
FROM read_parquet('s3://bucket/**/*.parquet')
)
WHERE rn = 1
AND (deleted_at IS NULL OR deleted_at = 0) -- Filter soft deletesEach row_id returns only its latest version, and deleted records stay deleted.
Why DuckDB?
Why DuckDB instead of Trino, Spark, or PostgreSQL's FDW?
| Feature | DuckDB | Trino/Spark | PostgreSQL FDW |
|---|---|---|---|
| Deployment complexity | Embedded, zero deployment | Requires 3-10 node cluster | Requires FDW extension config |
| Cold start latency | 50-100ms | 2-10 seconds (JVM warmup) | Milliseconds (connection reuse) |
| Native Parquet support | Native, vectorized execution | Good (needs connector) | Needs parquet_fdw plugin |
| PostgreSQL connectivity | postgres_scanner | JDBC (extra 10-50ms latency) | Built-in |
| Cost model | Pay-per-query friendly | Cluster standing cost $500-5000/mo | Depends on main DB resources |
DuckDB is an embedded OLAP engine, so it runs inside your application process with no extra servers. That suits serverless well: a Lambda function loads DuckDB on startup (~50MB), connects directly to PostgreSQL through postgres_scanner and to S3 through httpfs at query time, and when the query ends and the Lambda terminates, cost drops to zero.
Serverless cost model
Traditional OLAP architectures need standing clusters that burn money even when idle. DuckDB's embedded nature makes real pay-per-use pricing possible:
| Cost Item | Traditional OLAP Cluster | DuckDB Serverless |
|---|---|---|
| Idle cost | $500-5000/month | $0 |
| Single query (1GB scan) | ~$0.001 | ~$0.005 (incl. Lambda) |
| 1000 queries/month avg | $500-5000 | ~$5-10 |
For low query volume against high data volume, such as historical audits and monthly reports, the serverless cost advantage reaches 100-500x.
Series summary
The complete architecture built across these three posts:
┌─────────────────┐
│ Flexibility │
│ EAV + JSON │
│ Schema │
└────────┬────────┘
│
┌───────────────┼───────────────┐
│ │ │
▼ │ ▼
┌─────────────────┐ │ ┌─────────────────┐
│ Performance │ │ │ Cost │
│ Hot Table + │◀─────┴─────▶│ DuckDB + │
│ CTE JSON_AGG │ │ Serverless │
└─────────────────┘ └─────────────────┘Problems solved by each post
| Part | Problem | Solution | Key Metrics |
|---|---|---|---|
| Part 1 | Schema flexibility | EAV + JSON Schema + Hot Table | Zero DDL, 80/20 index optimization |
| Part 2 | N+1 queries | CTE + JSON_AGG | 101→1 queries, 1000ms→25ms |
| Part 3 | Massive historical data | DuckDB + Anti-Join | Zero dirty reads, Serverless cost |
Core design principles
- State, not timestamps.
flushed_atexplicitly marks sync state instead of comparing clocks. - Pessimistic over optimistic. Querying PostgreSQL one extra time beats risking a dirty read.
- Push computation down. PostgreSQL and DuckDB each do what they do best.
- Degrade gracefully. Fall back to PostgreSQL alone when DuckDB fails.
Ideal use cases
This architecture suits AI-driven applications, where frequently changing data structures need JSON Schema's flexibility. It suits multi-tenant SaaS, where different tenants need different fields and EAV supports that naturally. And it suits analytical queries over historical data, where DuckDB and Parquet handle aggregation, reports, and exports efficiently.
Not ideal for
Scenarios needing cross-table ACID transactions are better served by pure PostgreSQL. Sub-10ms point queries want a Redis cache in front of PostgreSQL. And real-time streaming wants Kafka or Flink, since CDC here has minute-level latency.
Forma vs. the alternatives
| Capability | Forma | MongoDB + Atlas | DynamoDB | PostgreSQL + TimescaleDB |
|---|---|---|---|---|
| Schema flexibility | ✅ JSON Schema | ✅ Schemaless | ✅ Schemaless | ⚠️ Requires DDL |
| ACID across records | ✅ Full | ⚠️ Multi-doc limited | ⚠️ 25 items max | ✅ Full |
| SQL compatibility | ✅ Native | ❌ MQL only | ❌ PartiQL (limited) | ✅ Native |
| Hot/cold separation | ✅ Built-in | ⚠️ Manual tiering | ⚠️ TTL-based | ⚠️ Chunk-based |
| Serverless analytics | ✅ DuckDB | ⚠️ Atlas BI ($$) | ⚠️ Athena (separate) | ❌ Requires cluster |
| Cold storage cost | ✅ S3 ($0.023/GB) | ⚠️ Atlas archive | ⚠️ S3 export needed | ⚠️ Disk-based |
| Zero dirty reads | ✅ Dirty Set | ❌ Eventual | ⚠️ Strong per-item | ✅ MVCC |
| AI pipeline integration | ✅ JSON Schema = LLM contract | ⚠️ Manual validation | ⚠️ Manual validation | ⚠️ Manual validation |
What separates Forma is the combination: the flexibility of document stores with the consistency of relational databases, plus native support for AI workloads (JSON Schema is the contract between LLMs and storage) and cheap cold data analytics through DuckDB and Parquet.
Conclusion
"Lakehouse" isn't a new concept. Making people trust it is the hard part.
Anti-Join plus Dirty Set is a pessimistic consistency protocol: assume data might be in transit at any moment, then handle that uncertainty explicitly. It costs more per query than optimistic timestamp comparison, because scanning the change_log table isn't free. What you get back is a consistency guarantee you can prove.
In data systems, correctness comes before performance, because a fast wrong answer is worse than a slow right one.
Series Navigation
- [Part 1] Why EAV is the Most Underrated Data Model for AI
- [Part 2] Killing N+1: How One SQL Trick Cut Our Latency by 40x
- [Part 3] Zero Dirty Reads: Building a Trustworthy Lakehouse with DuckDB ← You are here (Finale)
This post is based on engineering practices from the Forma project. Forma is a flexible data storage engine designed for the AI era.
The code and the discussion are on GitHub.