Scale & Architecture

Partitioning & physical layout

At scale, the dominant cost is how much data you read, and that's decided by physical layout long before query time. Partitioning splits a table into directories by a column so the engine can skip whole partitions — partition pruning — instead of scanning everything.

• Partition by what you filter on, at the right grain. For pipeline data that's almost always event date (event_date=2026-09-11), because nearly every query and every incremental merge is bounded by a date range. Pruning a query from a full-history scan to three days is often a 100× cost cut.
• The small-files problem. Many tiny files (one per micro-batch, per partition) wreck read performance — the engine pays per-file open/list overhead and loses columnar batching. Streaming and over-frequent writes are the usual culprits.
• Over-partitioning is the mirror image. Partitioning by (event_date, user_id) yields millions of near-empty directories, catastrophic metadata/listing cost, and worse pruning. Rule of thumb: keep partitions in the hundreds-of-MB-to-GB range, not KB.

The fix for both small files and layout drift is compaction: periodically rewrite many small files into fewer large ones. Inside a partition, clustering / bucketing / Z-ordering co-locates rows by a secondary key (e.g. customer_id) so filters on that key also skip data. Z-order (Delta) and sort-based clustering multi-dimensionally sort within files so pruning works on more than just the partition column.

File formats & pushdown

The format under the partition matters as much as the partitioning. Analytical pipelines are columnar — Parquet or ORC — not row-oriented (Avro/CSV/JSON), and the reason is two forms of pushdown:

• Projection pushdown. Columnar storage lets the engine read only the columns a query touches. SELECT amount FROM orders reads one column's worth of bytes, not the whole row.
• Predicate pushdown. Parquet/ORC store per-row-group min/max statistics (and optionally bloom filters), so a WHERE amount > 1000 skips entire row groups whose max is below the threshold — reading is pruned inside a file, not just across partitions.

Both formats also compress far better than row formats because a column holds homogeneous values (run-length, dictionary encoding). Row formats like Avro still win for the write side — streaming ingest and per-record serialization — which is why a common shape is Avro on the wire/landing, compacted to Parquet for the analytical layer.

Table formats & the lakehouse

Parquet files in a directory are not a table — there's no atomic commit, no snapshot isolation, no safe concurrent write. Open table formats — Iceberg, Delta Lake, Hudi — add a metadata layer over the files that brings ACID transactions, time-travel, and schema/partition evolution to object storage. This is the technical core of the lakehouse, and it's what makes the atomic swap and WAP flow from Chapter 07 real on a data lake.

Two concepts every interviewer probes here:

• Hidden partitioning (Iceberg): you declare "partition by day(event_time)" and the engine derives the partition value; queries filtering on event_time prune automatically without users knowing the partition scheme — and it can evolve without rewriting old data.
• MoR vs CoW. Copy-on-Write rewrites whole files on update — fast reads, expensive writes (good for read-heavy marts). Merge-on-Read writes lightweight delete/update deltas and merges at read time — cheap frequent writes, more read cost (good for high-frequency upserts and CDC). Compaction periodically materializes MoR deltas back into base files.

Warehouse vs lake vs lakehouse

Know the three storage paradigms as a trade-off spectrum, not a religion:

The industry trend — and the reason ELT won (Chapter 02) — is the lakehouse collapsing the old two-tier "lake for data science, warehouse for BI" split into one governed copy. But a managed warehouse is still the right, boring answer for a pure-SQL analytics shop; don't reach for a lakehouse to prove sophistication when Snowflake or BigQuery solves it with less operational surface.

Spark: skew, shuffle, spill, broadcast

Distributed compute at scale lives and dies by the shuffle — moving data across the network to co-locate keys for a join or aggregation. Four failure modes come up constantly:

• Shuffle cost. Wide transformations (joins, groupBy) shuffle. Minimize them, filter/aggregate before the shuffle, and don't shuffle more columns than you need.
• Skew. If one key holds 40% of the rows (a null customer_id, a whale account), one task does most of the work while the rest idle — the job runs at the speed of its slowest partition. Fixes: salting the hot key, Spark's adaptive skew join (AQE), or filtering/handling nulls separately.
• Spill. When a partition doesn't fit in executor memory, Spark spills to disk — correct but slow. It signals skew or under-partitioning; repartition or bump memory/partition count.
• Broadcast joins. When one side is small (a dimension), broadcast it to every executor and skip the shuffle entirely. Huge win — but broadcasting something too big causes OOM. Set the threshold deliberately; let AQE decide when it can.

Streaming architectures

When freshness requirements drop below the batch interval, you move to streaming — and inherit a different set of scale concerns. Kafka is the usual backbone:

• Partitions are the unit of parallelism and of ordering. Kafka guarantees order within a partition, not across. So the partition key is a correctness decision: key by order_id and all events for an order stay ordered on one partition. Too few partitions caps consumer parallelism; too many strains the cluster.
• Consumer groups distribute partitions across consumers; a rebalance reassigns them on scale/failure. Offsets are the checkpoint (tie back to restartability in Chapter 07).
• Retention vs compaction. Time/size retention drops old records; log compaction instead keeps the latest value per key — turning a topic into a changelog you can rebuild state from.

For the processing engine, know the Flink vs Spark Structured Streaming distinction:

Both realize exactly-once sinks the same way conceptually — checkpoint the input position and commit the output transactionally together, which is the streaming twin of the effectively-once/outbox story in Chapter 07. And the watermark, allowed-lateness, and windowing machinery here is exactly the late-arriving-data model from Chapter 04 — streaming state is where those concepts become infrastructure rather than SQL.

Batch vs micro-batch vs streaming: a decision framework

The over-engineering trap (Chapter 01) is reaching for streaming to look advanced. Drive the choice from requirements:

What is the FRESHNESS requirement?  (ask before choosing — Ch01)
  hours / daily        ─► BATCH
       cheapest, simplest, easiest to reconcile & backfill. Default.
  1–15 min             ─► MICRO-BATCH (Structured Streaming / dbt microbatch)
       near-real-time without full streaming ops burden.
  sub-second / seconds ─► STREAMING (Flink / Kafka Streams)
       only if the business truly consumes it that fresh.

Then sanity-check against:
  • Correctness bar   : streaming makes reconciliation & restatement HARDER.
  • Cost              : always-on compute vs scheduled bursts.
  • Ops maturity      : streaming = 24/7 state, checkpoints, backpressure.
  • Late data         : both need watermarks (Ch04); batch absorbs via lookback.

Rule: choose the SLOWEST tier that meets the freshness SLA. Earn the complexity.

Most "real-time" requirements are actually "fresh enough by the time someone looks," which micro-batch serves at a fraction of streaming's operational cost. Reserve true streaming for cases where a human or system acts on the data within seconds — fraud blocks, live ops dashboards, alerting.

Cost/performance engineering & multi-region

At scale, architecture is cost engineering — the design that's 3× cheaper often wins over the one that's 10% faster. The levers:

• Decouple compute from storage. The modern default (Snowflake, BigQuery, lakehouse): pay cheap object-storage rates for data at rest, spin compute up only when a job runs. Right-size and auto-suspend warehouses; scan less via the partitioning/pushdown above.
• Materialization strategy. Trade compute for storage deliberately — pre-aggregate hot marts (compute once, read many) vs compute-on-read views for cold data. This is the incremental-vs-full-refresh economics from Chapter 05 at the table level.
• Spot/preemptible + caching. Run fault-tolerant batch on spot instances (restartability makes this safe); cache reference data and broadcast dimensions rather than re-reading them.
• Backpressure & throughput. Streaming systems must shed or buffer load gracefully when downstream slows — Kafka's durable log is the buffer; the consumer's lag is the SLI to watch.

For multi-region / DR, know the vocabulary: RPO (how much data you can lose) and RTO (how long recovery takes) drive the design. Cross-region replication of the object store plus reproducible, idempotent pipelines means DR is "re-point compute at the replica and re-run from the last checkpoint" — which only works because the pipeline is idempotent and partitions are immutable. Reliability primitives from Chapter 07 are what make DR cheap; bolting it on later is what makes it expensive.

Schema evolution & zero-downtime migration

The hardest scale problem is changing a live system without breaking the consumers depending on it. Two layers:

• Schema evolution. Additive changes (new nullable column) are safe and backward-compatible — the whole point of contracts (Chapter 06). Breaking changes (drop, rename, retype) need coordination: expand-then-contract — add the new field, dual-write both, migrate readers, then remove the old. Table formats with id-based schema evolution (Iceberg) make the rename safe at the storage layer; a schema registry with compatibility rules enforces it on the wire.
• Zero-downtime pipeline migration. To swap a pipeline or a model definition, dual-run / shadow: run the new version alongside the old, writing to a parallel table, and reconcile the two outputs (row counts, aggregates, hash-diffs — the recon toolkit from Chapter 06) until you trust it. Then cut consumers over behind a view or contract, and retire the old path. No flag day, no big-bang risk.

Leveling ladder

Scale is where the ladder reaches toward staff and principal — the question isn't "can you make it fast?" but "can you reason about the whole system's economics, correctness, and evolution?"

You now have the full production-grade toolkit. Next it all comes together in a live, timed whiteboard — 09 · The Design Session.

✓ Check yourself

Answer from memory before opening each one.

1. A team partitions a 2 TB events table by event date and user id. Queries get slower and the metastore struggles. Why, and what do you do instead?

   Show answer

   User id has huge cardinality, so the table becomes millions of tiny directories, with heavy listing cost and little data per partition. Partition on the always-filtered low-cardinality column, event date, and cluster or sort on user id inside it. Aim for partitions in the hundreds of megabytes to gigabytes, and compact small files. See partitioning and physical layout.

2. A query filters amount > 1000 on a Parquet table whose rows are in arrival order. Pushdown barely reduces the bytes read. Why, and what would change that?

   Show answer

   Pushdown compares the filter with each row group's min and max, and unsorted data puts a wide range of amounts in every group, so almost none can be skipped. Sorting or clustering the data on that column narrows each group's range and lets the statistics rule groups out. See file formats and pushdown.

3. Why is a folder of Parquet files not a table, and for a mart that takes frequent CDC upserts, do you pick copy-on-write or merge-on-read?

   Show answer

   There is no atomic commit, snapshot isolation or safe concurrent write, so readers can see partial writes; a table format adds a metadata layer that commits by one atomic swap. For frequent upserts choose merge-on-read, because it writes small deltas cheaply and pays at read time, with compaction folding them back. A read-heavy mart with rare updates favours copy-on-write. See table formats and the lakehouse.

4. A Spark stage shows 199 tasks finishing in seconds and one running for twenty minutes. How do you confirm the cause, and what are your fixes?

   Show answer

   Open the Spark UI and compare task input sizes: a single oversized task means key skew, often a null key or one very large account. Salt the hot key, let adaptive query execution split skewed partitions, handle nulls separately, and broadcast the small side of a join when it fits. Spill points to the same cause or too few partitions. See Spark: skew, shuffle, spill, broadcast.

5. Fraud scoring must block a payment within seconds. The finance dashboard needs hourly numbers that tie to the ledger. Which tier does each get, and what is the rule behind it?

   Show answer

   Fraud gets streaming, because a person or system acts on the data within seconds. The dashboard gets batch, which is cheaper and far easier to reconcile and backfill. The rule is to choose the slowest tier that meets the freshness SLA and to earn each step up in complexity, since streaming makes reconciliation and restatement harder. See the decision framework.

6. You must replace the pipeline behind a finance table with no outage and no leap of faith. How do you do it, and what do you get that a cutover does not give?

   Show answer

   Run the new pipeline in parallel into a separate table and reconcile its output with the old using counts, aggregates and hash diffs for a set period. Then flip consumers behind a view or contract and retire the old path. The old path stays live, so rollback is a pointer change instead of a rebuild. See schema evolution and zero-downtime migration.

Takeaway

• Partition on the always-filtered, low-cardinality column, cluster on the high-cardinality one, and compact small files.
• Columnar formats win on analytics through projection and predicate pushdown, and pushdown only helps when the data is clustered on the filter column.
• Open table formats add atomic commits, time travel and schema and partition evolution to object storage, which makes merges and write-audit-publish real on a lake.
• Choose copy-on-write for read-heavy tables and merge-on-read for frequent upserts, and compact either way.
• A slow Spark stage is diagnosed from the UI: a straggler means skew, and the fixes are salting, adaptive skew handling, null handling and broadcast.
• Pick the slowest of batch, micro-batch and streaming that meets the freshness SLA; the Kafka partition key is an ordering decision.
• Replace pipelines by shadow-running and reconciling, then switching consumers behind a pointer; change schemas with expand then contract.

The final pieces are in place, and the next chapter puts them to work in a timed design session. → The Design Session

Further reading

• Apache Parquet documentation — Apache Parquet. File layout, row groups, column chunks and the statistics behind pushdown.
• Iceberg: Partitioning — Apache Iceberg docs. Hidden partitioning and why queries do not need to know the scheme.
• Iceberg: Evolution — Apache Iceberg docs. Schema and partition evolution without rewriting data.
• Lakehouse: A New Generation of Open Platforms — Armbrust et al., CIDR, 2021. The case for table-format metadata over object storage as one platform.
• Spark SQL Performance Tuning — Apache Spark docs. Broadcast joins, adaptive query execution and skew join handling.
• Kafka: Message Delivery Semantics — Apache Kafka docs. Ordering within a partition and the delivery guarantees Kafka offers.