Data Pipeline Cost & Scale Estimation

The reasoning

The chain is short and each link is an assumption worth saying aloud: rows per day, bytes per row in the form the producer sends, the ratio you get after columnar encoding and compression, and the retention period. Give those four and the answer follows; give a number without them and an interviewer cannot tell whether you reasoned or guessed.

Bytes per row is the one people get most wrong, because JSON is far larger than it feels — repeated field names on every record, numbers as text, and no type information. The honest way to get it is to take a sample of a thousand real records and divide, and saying that you would do exactly that is a better answer than any figure you could recall.

Compression is worth carrying a rough expectation for, since it moves the result by close to an order of magnitude. Typical clickstream in Parquet with a modern codec lands somewhere around a seventh to a tenth of its raw JSON size, driven by dictionary encoding on the repetitive low-cardinality columns that dominate this kind of data. Be explicit that it is an estimate to be measured, not a constant — high-cardinality identifiers compress far worse than status codes.

The reasoning

At rest the three are closer than the usual advice suggests. Gzipped CSV strips the field names that make JSON so heavy, and general-purpose compression on the result is very effective. Parquet's own encoding is better per column, but it carries footers, row-group metadata and page headers, and on a narrow table it can land slightly larger than a well-compressed CSV. The run below shows exactly that ordering.

The scanned figure is where it stops being close. A row-oriented file has to be decompressed and read in full to get at three columns, because the columns are interleaved with everything else. Parquet stores each column contiguously, so a query touching three of twenty-four reads roughly an eighth of the file — and on a warehouse billed per byte scanned, that ratio *is* the bill.

Two more properties are worth naming because they decide real designs. Parquet is splittable, so many workers read one file in parallel; a gzipped CSV is not, and one large one is processed by exactly one worker no matter how big your cluster is. And Parquet carries typed statistics per row group, so a predicate can skip whole chunks without reading them, which stacks on top of the projection saving.

The reasoning

The observation the tiering rests on is that query access is heavily skewed toward recent partitions — usually well over ninety percent of reads land inside the last quarter. The older data is retained because somebody must be able to get it, not because anyone reads it weekly, and those are different requirements with very different prices attached.

Cold and archive tiers cost a fraction of standard storage and charge you in retrieval latency and in per-request fees instead. That trade is excellent for data whose realistic access pattern is an audit request twice a year, and unacceptable for anything a dashboard touches. The run below prices the same three years both ways and the gap is most of the bill.

Two cautions to raise unprompted, because they are where naive tiering goes wrong. Retrieval is not free — pulling a large archived range back can cost more than the months of storage you saved, so tier by realistic access rather than by age alone. And retention is a ceiling as well as a floor: data held past its lawful basis is a liability, so the policy should delete as well as demote.

The reasoning

Anchor on a quantity somebody already knows. Nobody can tell you how many rows a new event stream will produce, but they can tell you how many orders a day, or how many active users, and events per user per session is a much easier thing to reason about than events per day in the abstract. Build the estimate as a chain of multiplications from a number the business already reports.

Then give a range rather than a point. A low case and a high case, roughly an order of magnitude apart on the uncertain terms, tells the reader what they are actually committing to — and the gap between them is where the conversation belongs. A single number implies a precision you do not have and will be quoted back to you.

The most valuable part is the sensitivity. Change each assumption in turn and see which one moves the answer most; in almost every storage estimate it is bytes per row and the compression ratio, not the row count, because the row count is the term people already have a feel for. Saying 'this is within a factor of two unless bytes per row is wildly off, and here is how we would measure that in a day' is a far stronger answer than any figure.

The reasoning

A transaction fact grows with events, which is the term everyone estimates. Three other shapes do not. A periodic snapshot fact writes a row per entity per period whether or not anything happened — a daily balance snapshot of ten million accounts is ten million rows a day forever, and it grows when the business does nothing at all.

A Type 2 dimension grows with *change*, not with size. A customer dimension of two million rows is trivial; the same dimension tracking history on an attribute that churns weekly is two million times fifty-two per year, and the usual cause is someone adding a volatile column such as a score or a segment to a dimension that was designed around stable attributes.

Bridge tables grow with the product of the two sides, which is the one that genuinely surprises people: a many-to-many between accounts and products is not the sum of the two but their realistic combination count. The practical takeaway for an estimate is to size each table by its own grain, and to be specific that adding a fast-changing attribute to a Type 2 dimension is a storage decision as much as a modelling one.

The reasoning

The arithmetic is just multiplication and the result is startling, which is why it is worth doing out loud: sixty writers flushing every ten minutes produce thousands of files a day, each a tiny fraction of a megabyte, against a handful at a sensible target size. Over a year that is millions of objects for a few terabytes of data.

The read cost is per file and largely independent of file size. Every file means a listing entry, an open, a footer read, and typically a task scheduled to process it. When the file holds a quarter of a megabyte, the overhead is many times the work — so a query over that partition spends its time on file handling rather than on data, and adding cluster capacity barely helps because the bottleneck is per-object, not per-byte.

There are two other costs worth naming. Object stores rate-limit listings, so a directory with hundreds of thousands of entries becomes slow to enumerate before anything is read. And table formats keep metadata per file, so the manifest itself grows into something that has to be read before planning can start. The fix is compaction — a scheduled rewrite into target-sized files — and the target is typically in the hundreds of megabytes, chosen to match the engine's split size.

The reasoning

Partitioning exists to let a query skip data, and it pays for itself only when queries actually filter on the partition column. The cost is paid regardless: each partition is a directory or a metadata group, holds at least one file, and contributes entries the planner has to read before it can decide what to scan.

The multiplication is what catches people. Going from daily to hourly does not add complexity, it multiplies partition count by twenty-four — and adding a second column multiplies again by its cardinality. The run below shows two years under three schemes, and the manifest alone goes from a few megabytes to over a hundred, which is read on every query plan.

The rule of thumb worth stating is to size partitions so each holds enough data to be worth a scan — commonly at least a gigabyte, certainly not megabytes — and to partition only on columns queries genuinely filter by. If most queries hit a range of days, day is right and hour is waste. For the second dimension, prefer clustering or sort order inside the partition, which gives you skipping without multiplying the partition count.

The reasoning

The mechanism is simple: if the table is partitioned by day and the query filters on day, the engine reads only those partitions. Without partitioning — or with a filter the engine cannot map to partitions — it reads everything and discards most of it after the fact. Same result, two very different quantities of bytes.

Multiplying by query volume is what makes the point land. A single query's difference looks trivial; four hundred a day over a month is the figure in the run below, and it is the kind of number that gets a layout change prioritised. This is worth doing in an interview because it shows you can translate a technical decision into the terms the person approving it uses.

Two ways pruning silently stops working are worth naming. A function applied to the partition column in the predicate — casting or formatting the date — can prevent the engine from matching it, so the filter is applied after a full scan. And a join whose filter lives on the other table may not propagate, depending on the engine, so the fact table is scanned whole. Reading the query plan is how you find out, and 'I would check the plan' is a better answer than assuming.

The reasoning

Compaction rewrites data that queries are reading, so the first requirement is isolation. On a table format with atomic commits — Iceberg, Delta, Hudi — the rewrite produces a new snapshot and readers continue on the old one until it is committed, which makes the operation safe by construction. On plain files in a directory there is no such guarantee, and a reader can catch the directory mid-rewrite; that is the strongest practical argument for a real table format.

The scheduling question is about which partitions are finished. Compacting a partition still being written to just creates more small files behind you, so the usual pattern is a pass over partitions whose write window has closed — yesterday and older — rather than a continuous rewrite. Streaming ingestion often runs compaction on a lag for exactly this reason.

Then treat it as a real job with real cost. It reads and rewrites the data, so it is not free, and on a metered engine it appears on the bill next to everything else. It should be monitored like any other pipeline, because a compaction job that has been silently failing for a fortnight is discovered as a query performance problem rather than as a failure. **What would change my answer**: streaming ingestion with hot reads on the current partition, where the answer becomes a rolling compaction with a shorter lag and a higher cost.

The reasoning

Partitioning is a physical directory split, so its cost scales with the number of distinct values. That is fine for a date and catastrophic for an identifier: partitioning by customer means one directory per customer per day, each holding a handful of rows, which is the small files problem produced deliberately.

Clustering — or sorting within the partition, depending on the engine's vocabulary — gets you most of the benefit without the split. The data is ordered by the clustering column, and the file or row-group statistics then let the engine skip anything whose range cannot contain the value. A query for one customer reads a few chunks of a partition rather than all of it, and there are no extra directories at all.

The rule that follows: partition on low-cardinality columns that bound the query range, typically a date; cluster on the high-cardinality columns queries filter or join by. Clustering is not free — it has to be maintained as data arrives, which is usually folded into the compaction pass — but the cost is a background job rather than a permanent multiplication of your metadata.

The reasoning

Full refresh scans the whole table every run; incremental scans a day of it. The ratio is roughly the retention length in days, which sounds decisive until you multiply by how often the job runs. Once a day, on a table of this size, the absolute difference is small enough that many teams should simply keep the refresh and keep the correctness guarantees that come with it.

Hourly changes the picture entirely, because the full-refresh cost multiplies by twenty-four while the incremental cost stays proportional to the new data. The run below shows all three frequencies, and the pattern is that **run frequency drives this decision more than table size does** — which is not most people's intuition.

Put the engineering cost on the same page before deciding. Going incremental buys you a watermark to maintain, a backfill procedure, deletes you can no longer see for free, and a reconciliation job you should now build. Against a modest monthly saving that is a poor trade; against a large one it is obvious. The honest recommendation is to compute both numbers and let the gap decide, rather than reaching for incremental because it sounds more professional.

The reasoning

Take it in three pieces. Scan cost is partitions times bytes times price, and it is often surprisingly affordable — which is why people approve backfills without thinking further. Wall clock is partitions times duration divided by concurrency, and that is the number that determines whether this finishes tonight or on Thursday.

The third piece is contention, and it is the one that causes incidents. The run below compares the backfill against what the rest of the platform consumes in a night: it is several times a normal day's budget, so running it at full speed means every other pipeline queues behind it. Throttled to fit inside the spare capacity, the same work spreads across several nights — which is usually the right answer and needs to be said before the work starts, not after someone else's dashboard is late.

Two practical requirements follow. It must be resumable: four hundred partitions processed independently with progress recorded means a failure at partition three hundred resumes rather than restarts. And it should be ordered by value — most recent partitions first, because those are what people are actually looking at, so the useful part of the backfill lands on the first night even if the tail takes a week.

The reasoning

The waste is structural: a pull request touching one model triggers a rebuild of two hundred and thirty-nine that did not change. Multiplied by pull requests per day and working days per month it becomes the figure in the run below, spent entirely on recomputing unchanged results.

Two changes remove most of it. Build only the modified models and their downstream children — every serious transformation tool supports selecting a subgraph, and the typical pull request touches a small percentage of the graph. Then run development and continuous integration against sampled or limited sources rather than full history, since the purpose is to prove the SQL compiles and the tests pass, not to produce a correct production number.

Two cautions so this does not become false economy. Sampling can hide bugs that only appear at volume or on rare values, so a full build on the main branch — nightly, or before release — is still worth running. And state-based selection depends on an accurate manifest of what production currently is; when that drifts, the tool silently builds the wrong subgraph, which is worse than building everything.

The reasoning

Get the split before proposing anything. On a typical warehouse platform, compute — transformations, dashboard queries, ad-hoc analysis — is the large majority of the bill and storage is a minority of it. That means a heroic effort on retention policy can move a small fraction while an afternoon spent on the three most expensive queries moves considerably more.

The reliable targets, in order: the handful of queries or models that account for most of the scan, which is nearly always a very short list; full refreshes running far more often than the data changes; dashboards auto-refreshing for nobody at 3am; and continuous integration rebuilding everything on every change. Each of those is a policy change rather than an engineering project.

Two things to defend. Retention of data with a legal or contractual basis is not a cost lever, and offering it first signals you do not know that. And the test suite is not either — cutting the checks to save scan cost trades a visible line on the bill for an invisible increase in the chance of a wrong number, which is a much more expensive kind of failure. **What would change my answer**: if the split turned out to be storage-dominated, which happens on lakehouses with very large raw retention, then tiering moves to the top of the list.

The reasoning

One undifferentiated number is nobody's problem, so nothing changes. Attributing spend by team, model or dashboard turns it into something with an owner, and in most organisations showing the number is enough — a team seeing that one dashboard is a large fraction of their consumption will usually fix it without being charged.

Hard chargeback goes further and has real effects in both directions. It creates genuine incentive to clean up, and it also creates incentives you did not intend: teams avoid shared models because the cost lands on whoever materialises them, duplicate work into their own space where it is cheaper for them and more expensive overall, and argue about attribution instead of about design. That is a predictable outcome and worth anticipating out loud.

The mechanics matter too. Attribution needs tagging by team, model and dashboard from the start — retrofitting it onto an untagged platform is painful — and shared infrastructure has to be allocated by some rule everyone accepts as roughly fair rather than exactly right. The pragmatic position is showback by default, with chargeback only where budgets genuinely sit with the consuming teams. **What would change my answer**: an organisation where teams hold real budgets and the platform does not — there, chargeback is the only mechanism that works.

The reasoning

Do it as a table rather than an argument: list each component, what it uses now, what its ceiling is, and multiply. The run below does exactly that and the result is the interesting part — the warehouse scan grows tenfold and remains comfortably inside its limit, because warehouse compute is elastic and you buy more of it. The extract window does not, because it is bounded by a maintenance window on a production system you do not control.

That is the general pattern. Elastic components — warehouse compute, object storage, worker pools — degrade into higher bills rather than into failures, which is unpleasant but not an outage. Inelastic ones fail hard: a nightly window that must end before the business day, a source database that cannot serve a larger extract without affecting its own users, a single-threaded step, an API rate limit, a per-account quota.

So the useful discipline is to know which of your components is inelastic and what its actual number is, before growth arrives. And the answers when it does are usually structural rather than about capacity: switch from a full extract to a change feed so the extract stops scaling with table size, or split the extract across a wider window, or move it off the primary. Each of those is weeks of work, which is why the estimate is worth doing eighteen months early.

The reasoning

The storage arithmetic is the straightforward part and the run below covers it: a second copy of everything at rest, ongoing transfer of the daily change, and a one-off cost to seed the replica. Roughly double, plus a modest replication line — which is why the running cost alone often sounds acceptable in a meeting.

What that figure omits is most of the real cost. Compute in the second region either sits idle waiting or does not exist until you need it, and the second case means your recovery time includes provisioning. Orchestration, secrets, catalogue and connector configuration all have to exist there too. And failover has to actually be exercised — an untested disaster recovery plan is a document, and the day you need it is the worst possible day to discover which piece was never replicated.

The question that should come before the price is what recovery objectives are actually required. A recovery point of twenty-four hours and a recovery time of a week is a nightly copy to another region and very little else. A recovery point of minutes and a recovery time of an hour is continuous replication with warm compute and regular failover drills, which is a different order of cost. Those two numbers decide the design, and asking for them is the strongest move available.

The reasoning

Start with the naive figure because it is what makes the case: rows per day times fields per row is the call count, and at tens of millions of rows that is a number no per-call service is priced for. It is also a latency problem — a synchronous call per field per row serialises the entire pipeline behind a network round trip.

The property that saves it is that personal identifiers repeat. The same customers appear many times a day, so the mapping from value to token is highly cacheable, and a high hit rate turns the call volume into a small fraction of the naive one — the run below shows the difference. Deterministic tokenisation, where the same input always yields the same token, is what makes caching possible at all, and it is also what lets you join on the token afterwards.

Two constraints a security reviewer will raise. The cache of value-to-token mappings is itself sensitive — it contains the plaintext — so it needs the vault's protection and a bounded lifetime. And deterministic tokenisation is weaker than random: identical values are visibly identical, so frequency analysis leaks information about a small domain. That is an acceptable trade for a high-cardinality identifier and a poor one for a postcode.

Tokenisation is also not the only control, and naming the other two is what makes the answer complete. **Dynamic masking** leaves the real value in the table and hides it at query time based on the caller's role — cheap, reversible, and only as strong as your access control, because the value is still there for anyone who gets past it. **Column-level access control** removes the column from the query result entirely for unauthorised roles, which is stronger and does not survive a copy of the table into someone's own schema. Tokenisation is the only one of the three that travels with the data, which is why it is what a lake uses and masking is what a warehouse adds on top.

The reasoning

Below a threshold, a join where one side fits in memory is broadcast to every worker and the operation is close to linear — each row does a hash lookup. Above it, the engine cannot broadcast and switches strategy: both sides are shuffled across the network by key, and any partition that does not fit spills to disk. The cost does not slope upward at that point, it steps.

Skew makes it worse and is the usual real cause. If the join key is unevenly distributed — a null placeholder, a default customer, one enormous account — then one partition receives a disproportionate share and one task runs while the others have finished. Total data grew tenfold; the largest partition may have grown far more, and the job's duration is set by that single task.

For estimation the practical lesson is that anything involving a shuffle should be assumed to scale worse than linearly, and that the interesting question is where the thresholds are: when does the small side stop being broadcastable, and what is the distribution of the join key. The mitigations are known — pre-aggregate before joining, filter earlier, salt a skewed key, or denormalise the lookup away — but they are structural changes, so knowing the threshold in advance is what gives you time to make them.

The reasoning

The tombstoned files are not garbage in the sense people assume. They are the previous versions of the table, which is what time travel reads — VERSION AS OF and RESTORE both resolve to files VACUUM would delete. Reclaiming them is fine when you have decided you no longer need to read the past, and it is a decision rather than a cleanup.

RETAIN 0 HOURS is dangerous for a second and less obvious reason: a long-running reader that resolved the file list minutes ago is still reading those paths. Delete them underneath it and the query fails with a missing file rather than returning wrong data — which is at least loud, but it fails a report nobody expected to fail. The default retention exists to be longer than any plausible in-flight query, which is why engines make you override a safety check to go below it.

The safe way is to set retention deliberately from two numbers: the longest query you run, and the furthest back anyone needs to time travel. Then run OPTIMIZE and VACUUM on a schedule, monitor them like any other job, and understand what each does — OPTIMIZE bin-packs small files into larger ones and ZORDER additionally sorts by a clustering column so file statistics can skip more; VACUUM only removes what OPTIMIZE and other rewrites left behind. Running VACUUM without OPTIMIZE reclaims very little, which surprises people.

The reasoning

**Why it is needed.** Merge-on-Read keeps writes cheap by never rewriting base files: an update becomes a delta record. Every query then has to read the base file, read every outstanding delta, resolve by key and apply deletes before it can return a row. That cost grows with the number of deltas, so read latency degrades continuously and unpredictably as writes accumulate. Compaction is what resets it.

**What it does.** Read the base and its deltas, merge by key, apply deletes, write a new optimised file, update the metadata, drop the old files. The effect is to turn Merge-on-Read data back into Copy-on-Write-shaped data — after compaction a reader touches one clean file again.

**Two kinds, and they cost differently.** *Minor* compaction merges only the delta or log files with each other, leaving base files alone. It is fast, it cuts the log count, and it is what you run frequently against a hot landing table. *Major* compaction merges deltas into the base and rewrites full data files. It is expensive and it produces the best possible read performance, so you run it when the data has stabilised or ahead of an SLA-critical read window.

**When to trigger.** Four workable signals: time-based (hourly or nightly), file-count (more than some number of delta files), size-based (delta volume approaching base volume), or SLA-driven (before the morning BI peak). The rule that matters is that **you never run it continuously** — it is a full read-and-rewrite of the data, so a permanently-running compaction is a permanently-doubled bill.

**By layer:** bronze gets frequent minor compaction because it absorbs the write volume; silver gets scheduled major compaction so downstream reads are clean; gold is usually rebuilt rather than updated, so there is nothing to compact.

The reasoning

**The arithmetic first.** 200,000 events × 2 KB ≈ 400 MB/s. Divide by the per-partition write limit your platform gives you — Kinesis shards are 1 MB/s or 1,000 records/s, whichever binds first, and here the record limit binds first: 200,000 records/s needs at least 200 shards, while 400 MB/s needs 400. So 400, plus headroom. Say which limit binds; that is the part being tested.

**Then the distribution, which is what people miss.** Capacity is per partition and assignment is by key hash. If one merchant produces 15% of the traffic, that 60 MB/s lands on a single partition with a 1 MB/s ceiling — throttled, while the stream as a whole sits at 40% utilisation. Total capacity being sufficient tells you nothing about whether any individual partition is.

**So the checks are:** total throughput against the ceiling; **per-key concentration** — the top few keys as a share of the whole; **key cardinality**, because you cannot use more partitions than you have distinct keys; and the growth curve, because both the total and the skew move.

**The signals to watch afterwards** are producer-side: write throttling and retry rate climbing are the leading indicators, and they show up long before anything downstream notices. On the consumer side it is iterator age or lag. Rising producer retries with total throughput well under capacity is the specific signature of a hot key rather than an undersized stream — and the fix is different, which is why the distinction matters.