Every pipeline starts cheap and gets expensive in the same way. A job is written to scan everything because everything is small, the data grows, and the job keeps scanning everything. Nobody notices until the warehouse bill or the run time becomes a weekly conversation. Efficient pipeline design is mostly a set of decisions made early that keep that from happening.
What efficient actually means
Fast is the easy metric to reach for and the wrong one to optimise alone. A pipeline that finishes in two minutes but recomputes the same three years of history every night is not efficient, and neither is one that is cheap but delivers yesterday's numbers when the team needs this hour's.
I hold four things at once:
- Cost per row delivered. The bill divided by the rows that actually reach a consumer. It falls when you scan less and rise when you process data nobody reads.
- Freshness. The gap between an event happening and it being queryable. This is a requirement you get from the consumer, not a number you pick.
- Correctness. Results that are the same whether the job ran once or three times. Anything less and every incident becomes an argument about which numbers to trust.
- Operability. How much attention it needs. A pipeline that pages someone every night is expensive in a way the invoice does not show.
Most of the design work is choosing where to spend between these, and the usual mistake is spending on freshness when nobody asked for it.
Work backwards from the consumer
Before choosing a tool, I write down who reads the output and how often. A dashboard refreshed every morning and an alert that has to fire within a minute are different products, and the second one costs an order of magnitude more to run. The consumer sets the freshness budget, and the freshness budget sets almost everything else.
A useful question early: what is the smallest useful answer? If a dashboard shows daily totals, the pipeline should produce daily totals, not raw events that the dashboard aggregates on every load. Moving aggregation upstream is the single most reliable way to cut cost, because it shrinks the number of rows that leave the pipeline.
The shape
Most of my pipelines settle into the same shape, which people call the medallion layout. Names vary; the idea is that data lands untouched, gets cleaned once, and gets modelled for a specific consumer.
<svg viewBox="0 0 960 430" xmlns="http://www.w3.org/2000/svg" role="img" aria-label="Pipeline layout: sources land in a bronze layer, are cleaned into silver, modelled into gold marts, then served, with a stream path for low-latency events and a batch path for everything else">
<defs>
<marker id="p" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse">
<path d="M 0 0 L 10 5 L 0 10 z" fill="currentColor" opacity="0.7"/>
</marker>
</defs>
<g font-family="ui-monospace, monospace" fill="currentColor">
<text x="10" y="24" font-size="13" font-weight="700" opacity="0.6">BATCH PATH</text>
<rect x="20" y="46" width="150" height="66" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.45"/>
<text x="95" y="74" font-size="13" text-anchor="middle" font-weight="700">Sources</text>
<text x="95" y="94" font-size="10" text-anchor="middle" opacity="0.6">db, api, files</text>
<rect x="210" y="46" width="150" height="66" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.6"/>
<text x="285" y="74" font-size="13" text-anchor="middle" font-weight="700">Bronze</text>
<text x="285" y="94" font-size="10" text-anchor="middle" opacity="0.6">append only</text>
<rect x="400" y="46" width="150" height="66" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.6"/>
<text x="475" y="74" font-size="13" text-anchor="middle" font-weight="700">Silver</text>
<text x="475" y="94" font-size="10" text-anchor="middle" opacity="0.6">typed, deduped</text>
<rect x="590" y="46" width="150" height="66" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.6"/>
<text x="665" y="74" font-size="13" text-anchor="middle" font-weight="700">Gold marts</text>
<text x="665" y="94" font-size="10" text-anchor="middle" opacity="0.6">one per question</text>
<rect x="780" y="46" width="160" height="66" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.45"/>
<text x="860" y="74" font-size="13" text-anchor="middle" font-weight="700">Serving</text>
<text x="860" y="94" font-size="10" text-anchor="middle" opacity="0.6">bi, api, cache</text>
<line x1="170" y1="79" x2="208" y2="79" stroke="currentColor" stroke-opacity="0.7" marker-end="url(#p)"/>
<line x1="360" y1="79" x2="398" y2="79" stroke="currentColor" stroke-opacity="0.7" marker-end="url(#p)"/>
<line x1="550" y1="79" x2="588" y2="79" stroke="currentColor" stroke-opacity="0.7" marker-end="url(#p)"/>
<line x1="740" y1="79" x2="778" y2="79" stroke="currentColor" stroke-opacity="0.7" marker-end="url(#p)"/>
<text x="10" y="196" font-size="13" font-weight="700" opacity="0.6">STREAM PATH</text>
<rect x="20" y="218" width="150" height="60" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.45"/>
<text x="95" y="244" font-size="13" text-anchor="middle" font-weight="700">Event bus</text>
<text x="95" y="262" font-size="10" text-anchor="middle" opacity="0.6">retained log</text>
<rect x="210" y="218" width="150" height="60" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.6"/>
<text x="285" y="244" font-size="13" text-anchor="middle" font-weight="700">Windowed jobs</text>
<text x="285" y="262" font-size="10" text-anchor="middle" opacity="0.6">short windows</text>
<rect x="400" y="218" width="150" height="60" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.45"/>
<text x="475" y="244" font-size="13" text-anchor="middle" font-weight="700">Live tables</text>
<text x="475" y="262" font-size="10" text-anchor="middle" opacity="0.6">low latency</text>
<line x1="170" y1="248" x2="208" y2="248" stroke="currentColor" stroke-opacity="0.7" marker-end="url(#p)"/>
<line x1="360" y1="248" x2="398" y2="248" stroke="currentColor" stroke-opacity="0.7" marker-end="url(#p)"/>
<line x1="550" y1="248" x2="778" y2="100" stroke="currentColor" stroke-opacity="0.35" stroke-dasharray="5 5" marker-end="url(#p)"/>
<line x1="20" y1="320" x2="940" y2="320" stroke="currentColor" stroke-opacity="0.25" stroke-dasharray="5 6"/>
<text x="10" y="348" font-size="13" font-weight="700" opacity="0.6">SHARED</text>
<rect x="20" y="368" width="180" height="52" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.45"/>
<text x="110" y="399" font-size="12" text-anchor="middle">Object storage</text>
<rect x="230" y="368" width="180" height="52" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.45"/>
<text x="320" y="399" font-size="12" text-anchor="middle">Catalog and lineage</text>
<rect x="440" y="368" width="180" height="52" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.45"/>
<text x="530" y="399" font-size="12" text-anchor="middle">Orchestrator</text>
<rect x="650" y="368" width="180" height="52" rx="8" fill="none" stroke="currentColor" stroke-opacity="0.45"/>
<text x="740" y="399" font-size="12" text-anchor="middle">Quality checks</text>
</g>
</svg>
The important property of this layout is that each layer has one job. Bronze keeps what arrived, exactly as it arrived, so you can replay it. Silver is where typing, deduplication, and business rules live, and it is the layer you can rebuild from bronze at any time. Gold shapes data for a specific consumer. When a number turns out to be wrong, the layer tells you where to look, which is worth more than any amount of cleverness in a single monolithic job.
The stream path exists only where freshness demands it, and it feeds the same serving layer rather than becoming a second source of truth.
Decision one: batch or streaming
The default answer is batch, and streaming is the exception you earn.
Streaming costs more in every dimension. The compute has to stay warm, state has to live somewhere durable, late and out-of-order events become your problem, and testing a windowed job is harder than testing a query over a partition. If the consumer reads the result once a day, batch is the correct answer and streaming is complexity you will pay for forever.
The signal to go streaming is a real latency requirement that cannot be met any other way: an alert that must fire within a minute, or a counter that a user watches live. When that is genuinely the case, stream the small number of things that need it and let everything else stay on the batch path. A hybrid is normal. A fully streaming platform that nobody asked for is the expensive mistake.
Decision two: how data arrives, and whether it can run twice
Ingestion decisions are where correctness is won or lost.
A full snapshot every run is simple and gets expensive as the table grows. Change data capture reads the write-ahead log or the replication stream and delivers only the changes, which is far cheaper, at the cost of a pipeline that must handle out-of-order updates, schema changes mid-stream, and the occasional gap that needs a backfill. Start with snapshots if the table is small. Move to CDC when the scan volume forces it.
Whichever you pick, make the write idempotent. No broker gives you exactly-once delivery on its own. The practical way to get it is at-least-once delivery plus a sink that can absorb the same record twice. On object storage that means writing into a partition and replacing it atomically, or merging on a stable key with a deterministic tie-break. In a warehouse it means a merge on the natural key rather than an insert.
The test I apply is blunt: run the loader twice for the same window and check that the row count and the contents are identical. If they are not, the pipeline is not safe to rerun, and every backfill becomes a risk.
Decision three: storage layout does more than any query tuning
Two pipelines can run the same SQL and differ by an order of magnitude in cost because of how the files are laid out.
Columnar formats like Parquet matter because a query that reads four columns out of forty should not pay for the other thirty-six. Compression matters because the bill is bytes scanned, and compressed bytes are fewer bytes. Sorting and clustering within a file matter because the reader can skip row groups whose min and max statistics rule out the predicate.
Partitioning is the lever with the largest range, and it is easy to overdo. Partitioning by day turns a query for one day from a full scan into a scan of one directory, which in the worked example below is the difference between a terabyte and a gigabyte. Partitioning by a high-cardinality key such as user id creates hundreds of thousands of tiny directories and slows everything down. Partition by the columns people actually filter on, and keep the count in the hundreds or low thousands.
The small file problem is the tax you pay for streaming or frequently-appended ingestion: thousands of kilobyte files, each with its own metadata read. Compaction, which rewrites small files into larger ones on a schedule, is not an optimisation you add later. It is part of running a lakehouse.
sequenceDiagram
participant S as Source
participant L as Loader
participant B as Bronze
participant T as Transform
participant G as Gold
Note over L: read watermark for this dataset
S->>L: rows where updated_at > watermark
L->>L: dedupe on natural key
L->>B: write into partition batch_id
B->>T: merge on key, watermark advances
T->>T: run quality checks
T->>G: replace partition atomically
Note over T: safe to rerun for the same window
Decision four: incremental work, and honest backfills
Recomputing everything is the habit that makes pipelines expensive. Incremental processing reads only what changed since the last successful run and merges it into the target. The watermark is the small piece of state that makes this safe, and it should advance only after the write it depends on has succeeded.
Late data is the complication. Events arrive after the window closed, and a pipeline that ignores them is quietly wrong. The two honest options are a short grace period during which a window stays open, and a scheduled restatement that reprocesses the last few days on purpose. Both are fine. Silently dropping late rows is not.
Backfills deserve the same care as the daily run. A backfill should be a parameter, not a script someone edits at midnight: reprocess this range, write into the same idempotent sink, and check the result with the same tests. The pipeline I trust is the one where the backfill and the scheduled run are the same code path with a different window.
Decision five: spend compute where the data shrinks
Where you put a transform changes what the next stage has to read. An aggregation placed early means every stage downstream handles fewer rows, and one placed late means everything upstream pays for the full volume.
{
"type": "bar",
"data": {
"labels": ["Raw events", "Bronze", "Silver", "Gold", "Serving"],
"datasets": [{
"label": "Rows per day (millions, worked example)",
"data": [1000, 1000, 180, 6, 6],
"backgroundColor": ["#cba6f7", "#89b4fa", "#94e2d5", "#a6e3a1", "#f9e2af"],
"borderWidth": 0
}]
},
"options": {
"responsive": true,
"plugins": { "legend": { "display": false } },
"scales": { "y": { "beginAtZero": true, "title": { "display": true, "text": "millions of rows" } } }
}
}
A day of this workload produces a billion raw events at about a kilobyte each, so roughly a terabyte before compression and a few hundred gigabytes once it is Parquet. Deduplication and typing bring it to a couple hundred million rows, and the aggregates a consumer actually reads are single-digit millions. Filtering and aggregating early is what turns a terabyte problem into a gigabyte one, and it costs nothing but thinking about the order of operations.
Decision six: the compute engine is a detail, the plan is not
Whether the work runs on a warehouse, a distributed engine, or a single machine with a columnar file, the shape of the efficient plan is the same. Read fewer columns, read fewer rows, shuffle less, and avoid the join that explodes.
Three habits carry most of the weight. Push filters down so the engine reads only matching rows, and avoid wrapping a column in a function in a predicate, because that defeats the statistics. Filter before you join, and put the smaller side of the join first so the engine can broadcast it instead of shuffling both sides. Watch for skew, where one key holds a disproportionate share of the rows and a single task becomes the whole job. Salting the hot key or splitting the job by a second dimension fixes it, and detecting it is as simple as looking at the slowest tasks in the job history.
Decision seven: orchestration is where reliability lives
A pipeline is a set of tasks with dependencies, and the orchestrator's job is to run them in order, retry the ones that failed, and stop the ones whose input is wrong.
The details that matter are unglamorous. Every task should be idempotent, because retries are normal. Dependencies should be on data, not on the clock, so a task does not start because it is 2am but because its input partition exists. Failures should fail loudly, because a task that logs an error and exits zero is worse than one that crashes. And the retry policy should be bounded, because a task that retries forever while upstream is broken is a way to turn one incident into an outage.
Decision eight: quality checks that page someone
Checks are only worth writing if a failure is actionable. Row counts that drop to zero, nulls in a key column, a freshness gap that exceeds the SLA, and referential integrity between a fact and its dimension will catch most real breakages. Anomaly checks on volume catch the rest, because a pipeline that suddenly processes ten times the usual rows is either a launch or a bug, and both deserve a look.
The cheapest version of this is a small set of assertions attached to the transform layer, where the pipeline fails before the bad data reaches the consumer.
The levers, ranked
If I had to order the work by return on effort, it would look like this.
| Lever | Mechanism | What it buys |
|---|---|---|
| Partition pruning | Filter on the partition column so the engine skips directories | A one-day query over a terabyte becomes a gigabyte |
| Incremental processing | Read only rows past the watermark | Work scales with change, not with history |
| Aggregating early | Shrink rows before they move | Every downstream stage gets cheaper |
| Column pruning | Select only needed columns in a columnar format | Bytes scanned fall roughly with column count |
| Compaction | Merge small files on a schedule | Fewer metadata reads, faster scans |
| Join ordering | Broadcast the small side, filter before joining | Avoids the shuffle that dominates run time |
| Caching | Reuse a materialised intermediate | Removes repeated work between jobs |
| Compression | Encode Parquet more tightly | Fewer bytes billed and moved |
The top three are architectural. The bottom five are tuning. Getting the order wrong is how teams end up micro-optimising a query that should never have scanned the whole table.
What breaks first
Small files accumulate and quietly slow everything down. Skew turns a parallel job into a single-threaded one. Late data makes yesterday's numbers change tomorrow, and if nobody planned for it the change looks like a bug. Schema drift, where an upstream adds or renames a column, breaks a transform that assumed the old shape. Duplicate rows from a retry that was not idempotent corrupt a total in a way that is hard to find. And backfills that were never tested quietly overwrite good data with wrong data.
Trade-offs
The efficient design is not the simplest one, and I try to be honest about the cost. Incremental processing needs a watermark, a merge, and a reconciliation job to catch drift. Medallion layers need storage and a catalog. Quality checks need someone to act on them. A simpler pipeline that recomputes a small dataset every night is a perfectly good answer when the dataset is small, and the right moment to add complexity is when a number forces it.
The thing I would keep regardless of scale is the arithmetic. Writing down rows per day, bytes per row, and the partition a query actually reads turns a vague sense that the pipeline is slow into a specific fix, usually before anyone has written a line of code.
Reach me at swadhinbiswas.cse@gmail.com or on GitHub and LinkedIn.
No comments yet.