Why We're Splitting Our ETL Pipeline Into Three Independent Layers

Most ETL pipelines combine ingestion, change detection, and transformation into a single process. We're pulling them apart — and early signs suggest it might be the right call.

This isn't a success story yet. We haven't battle-tested this in production. What we're sharing is the reasoning behind an architectural bet we're making, the early results that give us confidence, and the technical decisions involved.

What We Were Dealing With

Our data pipeline transforms transactional data into an OLAP dataset. Source records flow in via PostgreSQL logical replication — a Go worker consumes WAL changes from a replication slot, batches them, and delivers them to a message queue every predetermined interval or number of events, whichever comes first. ETL workers consume those batches and process them.

The previous process handled ingestion and transformation in a single transaction using database triggers. When a record was written to a staging table, a trigger would fire and generate the final OLAP representation immediately — sometimes triggering nested operations across multiple tables.

This worked for a long time but had shortcomings. As data volume grew and the number of transformations increased, several issues emerged:

Triggers hide complexity. Because transformations happen inside the database transaction, there's no visibility into what's actually occurring. Tracing a single event through multiple trigger cascades is virtually impossible without splitting ingestion into individual events, which defeats batching purposes.

Coupled failure modes. Source data arrives in batches with no particular ordering — events from different entities and different tenants are mixed. Before acknowledging a batch, every event in it must be ingested, and its triggers must be complete. If one tenant's data triggers a slow or failing transformation, processing halts for everyone. The worker retries the batch endlessly until it succeeds or someone intervenes.

Tenant isolation doesn't exist. Because batches aren't segregated by tenant, a data anomaly or an ungoverned data fix in one large account can degrade the experience for every other account on the platform. Splitting by tenant before ingestion would require writing the data to disk multiple times, adding IO pressure that undermines the efficiency being pursued.

Tuning one thing broke another. Each source entity type needed slightly different trigger-based transformation logic, and optimising queries for one entity could create contention for another sharing the same transaction.

The root cause wasn't any single query — it was that ingestion and transformation were fused in the same database transaction, making them impossible to scale, trace, or fail independently.

The Architecture We're Building

We're replacing the trigger-based approach with three independent stages. We internally called this "A 3 Body Problem" — three concerns that need to be sequential but shouldn't share a transaction boundary.

Layer 1: Loose Ingestion (Mirroring)

The ingestion layer writes source data into staging tables with minimal transformation — simple denormalization where useful, but no OLAP computation. It mirrors the data we care about from the transactional system, filtering out changes that are irrelevant to analytical purposes.

The key constraint: this layer knows nothing about downstream transformations. It doesn't know what queries will run later or what the final dataset looks like. It just ensures data lands faithfully.

By keeping ingestion lean, upstream batches can be acknowledged faster. A batch succeeds when its data is written — not when every downstream transformation completes. This breaks the coupling that caused tenant-wide stalls.

Layer 2: Targeting (What Needs Recomputing)

This is where the implicit trigger logic is replaced with an explicit queue. When data is ingested, a lightweight trigger inserts a work item into a targeting queue — a PostgreSQL table with a simple schema: entity, entity_id, created_at, and a processing_at timestamp for coordination.

The key insight that makes this efficient: we don't need to process every change, just ensure processing after the latest one. If the same entity changes five times in quick succession, five transformation runs aren't necessary. An upsert on conflict (entity, entity_id) ensures only one work item exists per entity at any time. If a worker already picked it up, the conflict resets processing_at to null, re-enqueuing it for processing.

This provides natural deduplication under write-heavy workloads — exactly where the old system suffered most.

A set of Go routines picks up batches from this queue — every interval or up to x pending items — and queries the local data cache to determine which records in the final OLAP dataset are affected by those entity changes. Those record IDs are then inserted into a second queue: the transformation queue.

Layer 3: Transformation

Workers pull from the transformation queue and execute a single transformation query — one UPSERT that computes the full OLAP representation.

The biggest simplification resides here. The old system had different trigger-based transformation queries for each source entity type — a user change computed differently from a project change, which computed differently from a task change. Each tried to optimise for the minimal set of columns that an entity touched, adding complexity without proven benefit.

All of it was replaced with one transformation query. Regardless of what source entity changed, the full record is recomputed. Yes, this means updating columns that may not have changed — but the row is already dirty in PostgreSQL's eyes, and the trade-off of maintaining a single, optimisable query instead of a dozen specialised ones is worth it many times over.

Both queues follow the same coordination pattern: pick up a batch of items, set processing_at to now, process them, and hard-delete on completion. If a worker dies mid-processing, the item becomes re-eligible after a timeout, giving at-least-once semantics without external locking infrastructure.

Why This Separation Matters Technically

Failure isolation. In the old system, a single failing event in a batch would block processing for every tenant until resolved — or the worker was manually restarted. With queued work items, a failing item gets deprioritized through last_attempted_at ordering while other items continue processing normally. A problematic tenant's data no longer holds the entire pipeline hostage.

Tracing becomes practical. Each event carries a PostgreSQL LSN (Log Sequence Number) from the original WAL entry. By propagating this LSN through every stage — ingestion, targeting, transformation — along with timestamps at each step, any individual change can be traced through the entire pipeline. The event volume would overwhelm telemetry, so trace data is logged to stdout and dashboards are built from it.

Backfills reuse the same pipeline. Need to regenerate the OLAP dataset? Dump the source records into the staging tables. The targeting and transformation machinery does the rest — same workers, same queries, same monitoring. No separate backfill scripts with their own bugs and blind spots.

Sync support comes for free. Because transformations flow through a well-defined queue, signals can be attached to specific changes. When a user action needs confirmation that the OLAP data has caught up, the application marks the change, and that marker propagates through both queues. Once the transformation completes, a notification fires back to the client. The queue becomes a readiness channel, not just a processing pipeline.

The transformation query becomes trivially testable. One query, parameterised by record ID. Give it an ID, check the output. No trigger chains, no implicit dependencies, no hidden failure conditions.

What the Early Numbers Show

Benchmarks — controlled benchmarks, not production traffic — show roughly 50% improvement in end-to-end latency for incremental updates compared to the previous pipeline.

Most of that gain comes from two things: not recomputing records that haven't changed (the upsert deduplication) and not blocking the pipeline on slow transformations (decoupled stages). The per-record transformation time is also lower because a single well-optimised query replaces a cascade of triggers.

But benchmarks are benchmarks. They don't account for schema migrations, replication lag spikes, backfill storms, or that one tenant with a dataset large enough to fill the queue faster than workers can drain it. We're cautiously optimistic, not declaring victory.

What Could Go Wrong

Queue growth under load. A large ingestion burst produces many work items. If workers can't keep up, queue depth grows, and data freshness degrades. The oldest last_attempted_at is being tracked as a staleness signal, but backpressure mechanisms haven't been fully designed — yet.

Processing lock timeout tuning. A processing_at timestamp is used to coordinate workers. If a worker dies mid-processing, the item isn't re-eligible until the timeout expires. Too short and duplicate processing occurs; too long and stalls happen. Getting this right under real load will take iteration.

The single-query trade-off. Recomputing the full record on every change is simpler but not free. For high-frequency update scenarios — an entity changing hundreds of times per minute — the bet is that queue-level deduplication absorbs most of the redundant work. If it doesn't, reconsidering this approach may be necessary.

Complexity budget. One kind of complexity (cascading trigger logic with no visibility) was traded for another (three coordinated stages with two queue tables). Each piece is simpler and independently testable — but "should be lower net complexity" isn't the same as "is." We'll know once we've operated it for a few months.

The Thinking Behind It

The core insight isn't novel — it's the separation of concerns applied to a data pipeline. But ETL systems resist this separation because the three concerns feel naturally intertwined. You're writing data anyway, so why not transform it in the same transaction? You know what changed, so why not compute the result right now?

The answer is that each concern has different performance characteristics and different failure modes. Ingestion is about throughput and acknowledgment speed. Targeting is about deduplication and scoping. Transformation is about query efficiency and correctness. Fusing them into the same transaction means none of them can be optimised, scaled, or recovered independently.

The other insight, specific to our case, was about what not to optimise. The old system tried to be smart about minimal updates — different transformation logic per entity type, each touching only the columns it affected. We went the opposite direction: one query, full recompute, let the deduplication layer prevent redundant work. Sometimes the best optimisation is removing optimisations.

We're betting that the operational overhead of three coordinated stages is worth the architectural clarity and the ability to independently scale, trace, and recover each one. We'll know in six months whether that bet paid off.