DEV Community: Aniruddha Writes

When Fast Ingestion Creates Slow Analytics

Aniruddha Writes — Sat, 29 Aug 2026 05:02:20 +0000

Solving the S3 Small-File Problem with Apache Iceberg, Compaction, and Amazon Redshift Spectrum

A reference architecture case study

The Pipeline Was Fast. The Queries Were Slow.

Every metric on the ingestion side looked healthy.

Events from IoT devices, mobile apps, and web clients streamed into an Amazon S3 data lake continuously, durably, with low write latency. The pipeline was doing exactly what it was built to do.

The dashboards built on top of that data told a different story.

Queries that used to return in seconds were taking minutes. Analysts filed tickets. Someone suggested adding more Redshift compute. It didn't help much.

The two halves of this system were never in conflict about correctness — the data was all there, intact and durable. They were in conflict about shape.

A streaming system optimizes for low-latency, continuous, durable writes.

An analytical engine optimizes for the opposite: fewer, larger, well-organized files it can plan around efficiently.

Nobody had designed for the second half of that sentence, because nothing about the ingestion pipeline was wrong on its own terms.

The problem wasn't necessarily the amount of data. It was the physical shape of the data.

Architecture Note

This is a reference architecture, not a specific customer engagement.

The workload, file counts, file sizes, and query patterns described below are illustrative and intended to represent a common streaming-ingestion pattern.

Where a claim is backed by AWS or Apache documentation, it is cited. Where a number is illustrative rather than measured, that is stated explicitly.

No benchmark result in this article is presented as measured fact unless independently verified.

1. The Architecture at a Glance

The system has three stages, and the story of this article is what happens to the data's physical shape as it moves through them.

Ingestion

IoT devices, mobile apps, and web applications emit events continuously.

A streaming layer writes them to an Amazon S3 landing zone as JSON objects, favoring low write latency over downstream analytical concerns.

The Bottleneck

Over hours and days, that landing zone accumulates an enormous number of small JSON objects — illustratively, tens or hundreds of thousands of files averaging around 10 KB each.

Amazon Redshift Spectrum, querying this layout as external tables, absorbs a large and largely avoidable planning and scanning overhead before it ever reaches a useful byte of data.

The Optimization

Apache Iceberg is introduced as a table format and metadata layer over the same S3 storage.

AWS Glue or Spark jobs perform compaction — rewriting many small JSON files into fewer, larger Parquet files — and commit the result as a new Iceberg table snapshot.

Redshift Spectrum then queries a physically consolidated, columnar, metadata-described table instead of a sprawling directory of small text files.

The Architecture in One View

Figure 1 — From fast ingestion to fragmented storage and finally to a metadata-aware, compacted analytical layout.

Three stages, three different concerns:

how data arrives,
how data is stored for analysis,
how a query engine plans and executes against it.

Keeping those three concerns distinct is the throughline of everything that follows.

2. The Incident: Fast Ingestion, Slow Analytics

The presenting symptom was simple:

Dashboard queries against recent event data were getting progressively slower, while the ingestion pipeline showed no degradation at all.

Write throughput was steady.

S3 PUT success rates were normal.

No errors. No backpressure. No obvious infrastructure fault.

That combination — a healthy write path and a degrading read path over the same data — is the signature of a physical layout problem, not necessarily a capacity problem.

If the cluster were simply undersized, you'd expect query performance to be fixable, at least to some degree, by scaling compute.

Here, scaling compute produced only marginal improvement.

That is itself a diagnostic clue worth taking seriously.

Throwing more compute at a query engine doesn't reduce the number of small objects it has to enumerate, open, and parse before it reaches useful data. It can process some of that overhead in parallel, but only up to a point.

3. The Investigation

The investigation deliberately avoided jumping straight to:

"We need Iceberg."

It started with measurement.

The team looked at:

Query latency, broken into planning time and execution time separately, not as one combined number.
Files scanned per query, compared against the amount of data a query's predicates should have actually required.
Bytes scanned, to distinguish "we're touching too many objects" from "we're touching too much data."
S3 object layout — file count per partition, average object size, and the distribution around that average.
File format — JSON, uncompressed, record-oriented.
Partition structure — how data was organized under the landing prefix.
Query plans — what Redshift Spectrum's planner was actually doing before scan work began.

This separation matters because file-count reduction and query-latency reduction are not the same claim.

A compaction job can drastically cut the number of files a query touches while only moderately reducing the bytes scanned, particularly for queries that aren't highly selective.

AWS's Spectrum performance guidance ties query performance to file size, file format, and partition design as separate, interacting levers — not one lever with one dial.

4. Root Cause #1: The Small-File Explosion

Small files, on their own, aren't the problem.

A single 10 KB file is nothing.

The problem is what accumulates when a streaming pipeline optimized for write latency runs continuously against the same landing zone for hours and days without any downstream consolidation step.

It's worth being precise about why a large number of small objects is expensive for an analytical engine.

"More files means more requests, therefore slow" is an oversimplification.

The actual mechanism has several contributing layers:

Object and file enumeration. Before any scanning happens, the query engine has to discover which objects exist under the relevant prefixes or partitions.
Metadata discovery and planning overhead. Building an execution plan against a directory structure with an extremely high object count takes measurably longer than planning against a smaller number of well-described files.
Object access overhead. Each object that participates in a scan carries its own fixed cost — opening it, reading whatever header or footer metadata the format requires, and closing it — independent of how much useful data it contains.
Task scheduling. A distributed query engine has to schedule work across many small units instead of fewer, larger, more efficient ones, and scheduling overhead doesn't scale to zero.
Parsing cost. Record-oriented text formats like JSON have to be parsed row by row, with no ability to skip irrelevant fields during the read.
Reduced parallelism efficiency. Extremely small files can create more scheduling overhead than benefit, because the fixed cost per file competes with the actual work being done.

The governing principle is:

The smaller the object, the larger the fixed overhead becomes relative to the amount of useful data it contains.

At 10 KB per object and tens of thousands of objects, an analytical engine can spend a disproportionate share of total query time on overhead that has nothing to do with the actual predicates in the query.

S3 Isn't the Bottleneck

It's also worth being explicit about what isn't the bottleneck here.

Amazon S3 itself is not slow, and it is not inherently poorly suited to storing many small objects.

The bottleneck is the interaction between:

object granularity,
file format,
partitioning,
metadata,
and how an analytical query engine has to plan and execute a scan across that layout.

That distinction matters.

"S3 is slow" and "this physical layout is expensive to scan" point to very different fixes.

The Small-File Tax

Figure 2 — Illustrative comparison of the same 1 TB dataset represented as many tiny objects versus substantially fewer larger objects.

The important point is not the exact 10 KB or 128 MB figure.

The point is:

The amount of data is unchanged. The physical organization is not.

More objects mean more fixed work relative to useful bytes.

5. Root Cause #2: JSON Is the Wrong Physical Shape for Analytics

JSON is not a bad format.

It's an excellent format for what it's usually used for: representing individual records in application and integration contexts where flexibility and human-readability matter more than analytical scan efficiency.

The problem isn't that JSON is deficient.

It's that JSON's design center and an analytical engine's access pattern point in different directions.

JSON is text-based and record-oriented.

To read any single field from any single record, the parser generally has to read and interpret the whole record.

It isn't organized around columnar access, which means an analytical query that only needs three columns out of forty still pays the cost of parsing all forty for every record it touches.

It's important not to overstate this.

JSON isn't incompressible, and general-purpose compression can and does reduce its size on disk.

But compression on a record-oriented text format doesn't grant the ability to skip columns during a scan.

That's where a large share of analytical efficiency actually comes from.

Why Parquet Changes the Equation

Parquet addresses this directly.

It's columnar, so a query that touches three columns can, subject to how the file was written and how the engine plans the scan, avoid reading data for the other thirty-seven.

It supports efficient compression schemes suited to columnar layouts.

And Parquet files carry embedded statistics — such as min/max values and null counts — that a query engine's planner can use to skip entire row groups without reading them at all.

The underlying architectural principle is important:

Reducing file count addresses file-level overhead. Moving to a columnar format addresses per-byte scan efficiency.

They are related, additive improvements, not the same improvement described twice.

A workload that fixes file count but stays in JSON still pays the columnar-access penalty.

A workload that moves to Parquet but keeps producing thousands of tiny files still pays the object-overhead penalty.

Both matter independently.

6. Root Cause #3: The Need for Metadata-Aware Table Management

The third root cause is less visible than file size or file format, and it's the one most often skipped.

Even after you decide to compact files and move to Parquet, someone has to safely manage the process of rewriting a live, continuously growing dataset without:

breaking readers,
losing data,
or leaving the table in an inconsistent state mid-rewrite.

This is a genuinely different problem from:

"What format should the files be?"

or:

"How many files should there be?"

It's a table-management problem.

How do you:

track which files currently constitute the authoritative state of a table?
let a rewrite job replace a large batch of files atomically?
let readers see a consistent view of the table while compaction is actively running?

A raw S3 prefix with no table format has no native answer to all of these questions.

This is the gap Apache Iceberg is designed to fill.

And it is a distinct concern from file size or file format.

7. The Architectural Intervention

The fix is not:

"Adopt Iceberg."

It's the deliberate combination of three things that each solve a different part of the problem.

Apache Iceberg

Provides the table abstraction — the metadata layer that tracks table state, snapshots, and schema, and that makes atomic, consistent rewrites of the underlying files possible.

Parquet

Provides the physical file format — columnar storage, efficient compression, and embedded statistics that a query planner can use.

Compaction

Run as a Spark job on AWS Glue or Amazon EMR, compaction is the process that actually rewrites many small files into fewer, larger ones and commits the result through Iceberg.

Three Technologies. Three Different Jobs.

Technology / Concept	Primary responsibility
Apache Iceberg	Table abstraction, metadata, snapshots, atomic commits
Apache Parquet	Physical columnar file format
Compaction	Rewrites many smaller files into fewer larger files

These are three separable technologies performing three separable jobs.

Iceberg is not a file format.

It doesn't itself make files larger, smaller, columnar, or compressed.

Parquet is not a table format.

It has no concept of snapshots, schema evolution, or atomic multi-file commits on its own.

Compaction is not free.

It is compute work that has to be scheduled, run, monitored, and paid for.

Keeping these boundaries distinct is what separates an accurate explanation of this architecture from a marketing summary of it.

8. How Iceberg Organizes the Table

Iceberg's contribution is best understood as a metadata hierarchy sitting above the physical Parquet files in S3.

Figure 3 — Conceptual Iceberg metadata hierarchy from table state to physical Parquet objects.

A simplified logical view is:

Iceberg Table
      ↓
Snapshot
      ↓
Manifest List
      ↓
Manifests
      ↓
Data Files
      ↓
Parquet Objects in S3

A snapshot represents the complete, consistent state of the table at a point in time.

Each snapshot points to a manifest list, which in turn references one or more manifests — files that track metadata about groups of data files, including partition values and, when generated, column-level statistics.

The manifests point to the actual data files: the Parquet objects physically stored in S3.

A Necessary Caveat

This hierarchy is a conceptual and logical model for how Iceberg organizes table state and enables metadata-driven planning.

It is not a literal claim about the exact sequence or count of network operations Redshift Spectrum performs for any given query.

What it gives a query engine is the ability to plan from structured metadata about which files are relevant to a query, rather than having to enumerate an entire object listing from S3 directly.

AWS documentation on querying Iceberg tables from Redshift describes this metadata-driven planning model and separately notes that AWS Glue-generated statistics for Iceberg tables can help Redshift's optimizer reduce the number of files it needs to scan.

Iceberg also provides atomic commits and snapshot isolation.

A compaction job can replace a large batch of small files with a smaller batch of large ones as a single atomic operation.

Readers querying the table mid-rewrite see either the old consistent state or the new one — never a partial, broken mix of both.

That property is what makes it safe to continuously reorganize a live analytical dataset.

And that is a genuinely different contribution from:

"makes files bigger"

or:

"makes queries faster."

9. How Compaction Changes the Physical Layout

Compaction is the mechanical process that actually does the file rewriting.

Figure 4 — Compaction rewrites physical files while Iceberg manages the resulting table state.

The lifecycle is conceptually:

Small Files
     ↓
Compaction Job
(Glue / Spark)
     ↓
Read Existing Data Files
     ↓
Rewrite / Merge into Larger Parquet Files
     ↓
Atomic Iceberg Commit
     ↓
New Table Snapshot

The job reads a set of existing small files, rewrites their combined contents into a smaller number of larger Parquet files, and commits that rewrite as a new Iceberg snapshot.

The old files aren't deleted the instant the new ones are written.

They remain part of prior snapshots until retention and expiration policies clean them up.

That is deliberate.

It is what allows time-travel queries and safe rollback if something in the rewrite went wrong.

Compaction Is Recurring

Compaction is recurring, not one-time, because ingestion doesn't stop.

A landing zone under continuous write load will keep accumulating small files after every compaction run.

That raises an operational question:

How often should compaction actually run?

"Run it daily" is not a universal answer.

The right cadence depends on:

rate of file accumulation,
query workload,
freshness requirements,
cost of the compaction job,
partition behavior,
and available rewrite capacity.

A workload accumulating files faster than a daily job can absorb needs more frequent, threshold-based triggering.

Compact when a partition crosses a file-count or fragmentation threshold rather than on a fixed calendar regardless of actual need.

A workload with a healthy, slowly growing partition doesn't need the compute cost of a daily rewrite it doesn't yet require.

10. What Changes at Query Time

With the intervention in place, Redshift Spectrum is querying a table with a materially different physical and metadata profile.

It's important to state precisely what's actually happening.

This is exactly the point where oversimplified claims tend to creep in.

Redshift Spectrum does not perform:

"a single metadata lookup"

and then instantly access all relevant data.

That phrase overstates how metadata-driven planning works.

The actual mechanism is a structured, hierarchical process of using Iceberg's snapshot and manifest metadata to identify a relevant, pruned set of data files before scan work begins.

Nor is it accurate to describe the underlying mechanism as:

"Spectrum issues one HTTP GET request for every file."

That's a plausible-sounding simplification, but stating it as a precise fact about Spectrum's execution path isn't something the available documentation directly confirms.

The actual overhead mechanism is broader:

enumeration,
planning,
parsing,
scheduling,
and object access.

What Can Be Stated with Confidence

Redshift Spectrum performance benefits from:

columnar file formats,
appropriately sized files rather than KB-scale objects,
well-designed partitioning that supports pruning,
and, specifically for Iceberg tables, Glue-generated statistics that can help the optimizer reduce the number of files it needs to scan.

The architectural change is therefore not:

1 metadata lookup
        ↓
everything is fast

It is:

Fragmented physical layout
        ↓
Metadata-aware table
        ↓
File / partition pruning
        ↓
Fewer, better-organized files
        ↓
More efficient analytical scanning

11. Before vs. After

Figure 5 — The ingestion path remains largely unchanged; the downstream analytical representation changes.

Before

Applications (IoT / mobile / web)
     ↓
Streaming ingestion
     ↓
Amazon S3 landing zone
     ↓
Many small JSON objects
(~10 KB, illustrative)
     ↓
Amazon Redshift Spectrum
     ↓
Higher enumeration, planning,
and parsing overhead

After

Applications (IoT / mobile / web)
     ↓
Streaming ingestion
     ↓
Amazon S3 landing zone
     ↓
Apache Iceberg table
     ↓
AWS Glue / Spark compaction
(threshold-based)
     ↓
Fewer, larger Parquet files
committed as new snapshots
     ↓
Amazon Redshift Spectrum

Same Data. Different Physical Layout.

Aspect	Before	After
File format	JSON	Parquet
Average file size	~10 KB (illustrative)	64–128 MB (illustrative target range)
File count	Very high	Substantially lower
Metadata planning	Directory-level object listing	Iceberg snapshots and manifests
Optimizer statistics	Minimal	Glue-generated Iceberg statistics
Scan efficiency	Lower — full-record parsing	Higher — columnar, prunable
Compression	Text-oriented	Columnar compression
Data freshness	Immediate	Slightly delayed, dependent on compaction cadence

Ingestion behavior is unchanged in this picture.

That's deliberate.

The applications still write the way they were always going to write.

What changed is entirely downstream, in how that data is subsequently organized for the workload that consumes it.

12. Benchmarking the Improvement

Here is the point where this article deliberately declines to hand you a headline number.

No specific production benchmark result — no percentage, no "Nx faster," no before/after latency figure — is presented as measured fact in this article because none was independently verified for this reference scenario.

What follows instead is a reproducible methodology, along with hypotheses that AWS's own documentation supports.

Those hypotheses should be tested against the actual workload before being turned into performance claims.

Benchmark at More Than One Scale

Run the comparison at more than one scale — for example:

100 GB
1 TB
10 TB

Overhead effects at small scale don't always predict behavior at larger scale.

Variants to Compare

Variant	Format	Average file size	Table layer
Baseline	JSON	~10 KB	Raw external table, no Iceberg
Optimized — Parquet only	Parquet	64–128 MB	No Iceberg / minimal metadata
Optimized — Parquet + Iceberg	Parquet	64–128 MB	Iceberg table + Glue-generated statistics

Including the middle variant matters.

It lets you separate the contribution of file format and file size from the contribution of Iceberg's metadata layer, rather than crediting one intervention for the combined effect of two.

Representative Query Workload

Test several query shapes:

time-range filter,
highly selective predicate,
aggregation,
multi-column projection,
partition-pruned query.

This covers query patterns that can behave differently under fragmented versus consolidated layouts.

Measure the Layers Separately

Measure:

total query latency,
planning latency,
execution latency,
bytes scanned,
files scanned,
observable S3 request patterns where available,
relevant Spectrum-side query metrics,
compute consumption,
cost per query.

Control the Variables

Keep the following identical between runs:

SQL text,
Redshift cluster configuration,
concurrency,
S3 region,
partition scheme,
underlying row content,
compression settings.

Report More Than a Single Number

Report:

median,
p95,
and the distribution across repeated runs.

Don't report only the single best-case query.

More importantly, report each metric layer separately rather than collapsing everything into one latency figure.

What the Documentation Supports as a Hypothesis

A reasonable hypothesis is:

JSON at small file sizes will show the highest planning and scan overhead of the three variants.
Moving to Parquet will reduce per-byte scan cost through columnar access and compression.
Adding Iceberg with Glue-generated statistics can further reduce files scanned for selective queries because the optimizer has structured statistics available for pruning.

These are hypotheses to benchmark, not guaranteed results.

If you run this methodology against your own workload and observe a specific speedup — 10x, 50x, 100x, whatever the number turns out to be — that number is a property of:

your workload,
your query selectivity,
your data shape,
your partitioning,
your infrastructure,
and your implementation.

It is not a universal property of Iceberg.

Benchmark the architecture, not the technology label.

13. The Cost of Optimization

It would be dishonest to present this as a pure win.

Compaction is not free.

Pretending otherwise undermines the credibility of everything else in this article.

Costs Introduced

Glue or Spark compute to run the compaction jobs themselves.
The rewrite operation's own I/O — reading the small files being consolidated and writing the new Parquet output.
Temporary storage overhead during the rewrite.
Operational complexity: something new to monitor, alert on, and operate.
A freshness trade-off — data isn't reflected in the optimized table until it has been through a compaction cycle, so there is an inherent lag between arrival and full analytical optimization.

Costs Potentially Reduced

Query-side compute spent on unnecessary scanning and parsing.
The operational and business cost of slow dashboards.
Reduced request and object-access overhead per query.

The governing economic principle is:

Compaction is worthwhile when the recurring analytical cost and latency caused by a fragmented physical layout exceed the cost of maintaining an optimized one.

For a landing zone that's rarely queried directly, that calculus might not favor aggressive compaction at all.

For a table backing high-frequency dashboards under continuous analytical load, it usually will.

That's a workload-specific judgment, not a default answer.

14. Production Guardrails

A physical-layout optimization introduces its own failure modes.

None of these are exotic.

They're the ordinary operational reality of running compaction as a continuous process against a continuously growing dataset.

Failure mode	Why it happens	Detection	Mitigation
Compaction lag	Files accumulate faster than compaction jobs can process them	File count and freshness lag trending upward	Threshold-based triggers; sufficient rewrite capacity
Compaction storms	Multiple large rewrites trigger simultaneously	Spikes in job concurrency and compute cost	Scheduling controls, rate limiting, partition-scoped optimization
Over-partitioning	Partition design is too fine-grained	High file count per partition	Coarser partitioning aligned to actual query patterns
Wrong target file size	Files end up too small or too large	File-size distribution tracked against target	Tune thresholds against workload behavior
Snapshot accumulation	Old snapshots are retained indefinitely	Snapshot count and age trending upward	Retention and expiration policies
Orphaned files	Failed rewrites leave unreferenced objects	Catalog-to-S3 mismatch	Scheduled cleanup / orphan-file removal
Failed compaction jobs	Jobs fail without being noticed	Job alerts and freshness monitoring	Retries, backoff, lag-based alerting
Raw-zone querying	Consumers bypass the optimized table	Query source auditing	Access controls and catalog separation

None of these are reasons to avoid the architecture.

They're the reason it needs monitoring, lifecycle management, and operational ownership rather than being treated as a one-time migration.

15. When You Don't Actually Need Iceberg

This is worth stating directly because it's the section most often missing from articles that are ultimately trying to sell a technology rather than solve a problem:

Iceberg is not the first thing to try.

The decision order is roughly in ascending order of complexity.

1. Fix Ingestion Batching or Windowing First

If the root problem is that the producer is flushing far more frequently than necessary, adjusting write batching or windowing at the source can eliminate the small-file problem before it ever reaches S3.

This is the cheapest fix and the one most often skipped in favor of a downstream architectural solution.

2. Write Directly to Parquet

If the dataset is relatively simple, doesn't need concurrent readers and writers reasoning about consistent snapshots, and doesn't need to evolve its schema or partitioning over time, writing directly to Parquet may be sufficient.

3. Run Scheduled or Threshold-Based Compaction

Where the operational need is genuinely just:

"Consolidate files periodically."

you may not need a table format if snapshot isolation, schema evolution, and other table semantics aren't required.

4. Adopt Iceberg with Compaction

Iceberg becomes compelling when the dataset genuinely needs table abstraction:

concurrent readers and writers operating safely against an evolving dataset,
schema evolution,
partition evolution,
atomic multi-file commits,
time travel,
and long-lived governance over an analytical table's lifecycle.

The conclusion is not:

"Everyone should use Iceberg."

It is:

Use the simplest architecture that solves the actual problem.

Introduce a table format when the dataset's lifecycle actually justifies the complexity it adds.

A batch job that compacts JSON into Parquet on a schedule, with no table format at all, may be entirely sufficient for a dataset that doesn't need concurrent writers or schema evolution.

Iceberg earns its place when the table's operational lifecycle demands more than compaction alone can provide.

16. Architecture Decision Framework

Figure 6 — A decision framework for determining whether the workload actually needs Iceberg.

Approach	Complexity	Solves Small Files?	Adds Table Semantics?	Best Fit
Better ingestion batching	Low	Yes	No	Root cause is upstream write behavior
JSON → Parquet	Low–Medium	Partially	No	Simple, relatively static analytical datasets
Standalone scheduled / threshold compaction	Medium	Yes	No	Periodic file consolidation without table semantics
Iceberg + compaction	Medium–High	Yes	Yes	Evolving datasets requiring table governance
Alternative table format	Medium–High	Yes	Yes	Workloads favoring another format's semantics

This table isn't meant to declare a winner.

It's meant to help you place your own workload against the actual axes that matter:

How complex is the fix you're willing to operate?
Does the dataset genuinely need table-level semantics?
Or does it simply need a cleaner physical layout?

What This Article Is — and Isn't

This article presents a reference architecture, not a production benchmark or customer case study.

It is not an argument that every S3 data lake should use Apache Iceberg.

It is not a claim that 128 MB is the universally correct file size.

It is not a guarantee that compaction produces a specific percentage improvement.

It is not a claim that S3 performs poorly when a bucket contains many objects.

And it is not a claim that Redshift Spectrum performs one specific network operation per file as the sole explanation for the small-file problem.

The problem is the interaction between:

file granularity + physical format + partitioning + metadata management + analytical workload

That is the architectural boundary that matters.

17. Conclusion

The problem this article describes was never really that the data lake contained too much data.

It was that the data had been physically organized around the needs of the producer — low-latency, continuous, durable writes — rather than the needs of the analytical consumer, which wants the opposite:

fewer, larger, well-described files it can plan around efficiently.

The fix is not:

"Use Iceberg."

It's the deliberate combination of:

appropriate ingestion behavior,
an appropriate physical file format,
sensible file sizing tuned to the actual workload,
a compaction process that runs on a cadence matched to real accumulation and freshness needs,
and a metadata-aware table management layer introduced when — and only when — the dataset's lifecycle actually justifies it.

Design the way data arrives for ingestion.

Design the way data is stored for analytics.

And reach for a table format when the lifecycle of that analytical data genuinely demands one — not because it's the technology attached to the fastest-sounding benchmark number you've seen.

The goal isn't to make the data lake look modern. The goal is to make the physical data layout fit the workload.

Sources

Amazon Redshift Spectrum query performance — AWS Documentation
Data files for queries in Amazon Redshift Spectrum — AWS Documentation
Using Apache Iceberg tables with Amazon Redshift — AWS Documentation
Accelerate query performance with Apache Iceberg statistics on the AWS Glue Data Catalog — AWS Big Data Blog
Introducing AWS Glue Data Catalog automation for table statistics collection — AWS Big Data Blog
Apache Iceberg optimization: Solving the small files problem in Amazon EMR — AWS Big Data Blog
Optimizing read performance — AWS Prescriptive Guidance
Using Iceberg workloads in Amazon S3 — AWS Prescriptive Guidance
Best practices for using Amazon Redshift Spectrum — AWS Prescriptive Guidance
10 Best Practices for Amazon Redshift Spectrum — AWS Big Data Blog
Apache Iceberg Table Specification — Apache Iceberg

This article was originally published on my blog: https://aniruddhawrites.github.io/2026-08-27-when-fast-ingestion-creates-slow-analytics/ and is republished here for Dev.to readers.

Independence Is Not an Event. It Is a Capability.

Aniruddha Writes — Sat, 15 Aug 2026 08:47:01 +0000

Editor’s Introduction

Every August, we celebrate the moment India became independent.

But history suggests something deeper.

11. Author's Note

This article is a personal reflection on India's journey from political independence to digital capability. The views expressed are my own and are intended to explore the relationship between nation-building, technology, public infrastructure, and the responsibilities of the AI era.

References to public digital platforms such as Aadhaar, UPI, DigiLocker, FASTag, and CoWIN are used as examples of capability-building at scale. The article is not a policy analysis, political commentary, or endorsement of any institution. It is an invitation to reflect on how capabilities are built, sustained, and strengthened across generations.

From nation-building to digital public infrastructure, India's journey reminds us that true independence is sustained through capabilities built and strengthened across generations.

Independence is rarely secured in a day. It is built through capabilities that take decades to create and generations to sustain.

There is a photograph most Indians have seen, even if they weren't alive to witness it: a flag rising over Red Fort as midnight becomes morning. It is one of the most reproduced images in the country's visual memory, and it captures, with total accuracy, a single instant. What it cannot capture is everything that instant required to mean something.

1947 gave India the right to govern itself. It did not give India the roads to govern, the schools to educate the governed, the courts to adjudicate disputes among them, or the institutions to convert a subcontinent of extraordinary diversity into a functioning republic. That took decades. It is still taking decades. Independence, it turns out, is not the moment a nation is handed the keys. It is everything that happens afterward to make the keys worth having.

This distinction — between the granting of independence and the building of it — is easy to state and hard to feel, because ceremonies are vivid and capability-building is slow. A flag-raising happens in a morning. An institution takes a generation. We remember the morning. We rarely notice the generation, because nothing about it looks like a single decisive event. It looks like thousands of unremarkable decisions, compounding.

The quiet decades

Consider what had to happen between 1947 and any meaningful sense of an Indian citizen exercising self-determination in daily life. Universal adult suffrage had to be organized in a country where the majority of voters had never cast a ballot and much of the electorate could not read the ballot paper. A currency, a civil service, and a federal structure had to be built for a territory larger than most continents' worth of separate nations, each with its own language and history. Steel plants, dams, and agricultural research had to close the distance between a population and the food required to feed it.

None of this made headlines the way independence itself did, because none of it was an event. It was a capability under construction — visible only in retrospect, and only in aggregate. A single dam commissioned in 1960 did not feel like sovereignty. A few hundred of them, alongside a green revolution, a literacy campaign, and a banking system extended into villages that had never seen a bank branch, eventually did. Sovereignty, it turns out, is rarely felt in the moment it is built. It is felt only once enough of it has accumulated to become invisible.

This is worth sitting with, because the same pattern is now repeating in a domain that would have been unimaginable to the generation that built those dams: the digital one.

A second infrastructure

Over the past decade, India has built something that rarely gets described with the gravity it deserves — not because it lacks importance, but because it doesn't look like infrastructure. It has no concrete, no steel, no ribbon to cut in front of cameras. It is a layer of shared digital rails, built by different agencies for different purposes — identity, payments, documents, mobility, public health — that together now function as one thing: a way for a citizen to be recognized, trusted, and served by systems that have never met them in person.

Aadhaar, UPI, DigiLocker, FASTag, and CoWIN are, individually, products. Collectively, they are closer to what economists and technologists have started calling public digital infrastructure — shared, foundational systems that a private company, a government office, or an ordinary citizen can all build on top of, the way earlier generations built on top of roads and electricity. None of them earned their significance on launch day. Each was, for a time, simply an idea a ministry had funded and a small number of early users had tried. What made them consequential was not the announcement but the slow, cumulative process of becoming load-bearing — the moment a fruit vendor, a migrant worker, a retired teacher, and a college student each independently decided to trust the same system with something real.

That moment is worth naming precisely, because it is the hinge on which this entire idea turns. A system becomes a capability at the exact moment people stop thinking about it as a system and start relying on it the way they rely on electricity — noticing it only when it's absent. A currency is not sovereign because a central bank prints it; it is sovereign because people accept it in exchange for their labor. A digital identity is not a capability because it exists in a database; it is a capability the day a citizen with no other paperwork can use it to open a bank account. Launch is permission. Adoption, sustained across millions of ordinary, unrecorded decisions to trust it again tomorrow, is proof.

The harder half of the work

What is easy to overlook, amid all of this, is that building a capability and sustaining one are not the same skill, and the second is by far the less glamorous of the two. A dam can be commissioned once. It has to be maintained forever. A digital identity system can be launched once. It has to remain worthy of trust every single day after, through every new vulnerability, every policy change, every edge case that its designers never imagined. Trust of this kind is accumulated in decades and can be spent in an afternoon — which is precisely why it is not a feature to be shipped but a discipline to be practiced indefinitely. Every generation, in this sense, inherits infrastructure it did not build and did not fully choose, and its real inheritance is not the system itself but the obligation to keep it worth inheriting. The true measure of any capability, digital or institutional, is not how impressive it looked when it was new, but whether the generation that received it chose to strengthen it rather than simply spend it down.

It is this obligation — not the technology itself — that connects the India of 1947 to the India now approaching a new technological threshold. The dam-builders and census-takers of the mid-twentieth century were not laying the groundwork for UPI specifically; they had no way to imagine it. What they were building was a habit: the expectation that each generation would extend what it inherited rather than merely use it up. Digital public infrastructure is that habit's most recent expression. Artificial intelligence will be its next test.

What the next chapter asks for

If public digital infrastructure was the last decade's capability-building project, the next one is already visible on the horizon, and it will not forgive complacency about what was already achieved.

Artificial intelligence built on top of Aadhaar-scale identity and UPI-scale transaction data carries a different order of consequence than either system carried alone. Readiness for that shift is not primarily a question of model access or computing capacity — it is a question of whether the country's data governance, consent frameworks, and grievance-redressal mechanisms are as mature as the infrastructure they will sit on top of. Digital literacy has to extend beyond the ability to scan a QR code toward the ability to reason about what a system built on one's own data is doing. Cybersecurity has to keep pace with the fact that infrastructure this widely relied upon is now, by definition, high-value infrastructure for anyone who wishes it harm. And inclusion has to remain a design constraint, not an afterthought — because a capability that works brilliantly for the digitally fluent and poorly for everyone else is not, in the fullest sense, a national capability at all.

None of this is a criticism of what has been built. It is simply what capability-building has always demanded: that today's achievement be treated as tomorrow's foundation rather than tomorrow's finish line. The systems that got India this far were themselves built by people who inherited an earlier, incomplete independence and refused to treat it as complete.

India's journey from political independence to the AI era can be viewed as a continuous process of capability-building. The foundation laid after 1947 through institutions, infrastructure, education, and governance created the conditions for a new layer of digital public infrastructure. Systems such as Aadhaar, UPI, DigiLocker, and CoWIN became meaningful not simply because they were launched, but because they evolved into trusted capabilities used at national scale. The next stage is not just about adopting AI, but about building the trust, literacy, governance, security, and responsible innovation needed to sustain that capability.

Returning to the thesis

It is tempting, every August, to let the story end at the flag. The story is more interesting, and more useful, if it doesn't. What 1947 actually started was not a condition but a project — one carried forward by dam engineers and census-takers, by the architects of a banking system that reached villages, by the teams who built an identity and payments layer relied upon by a scale of citizens no single generation could have imagined serving, and, soon enough, by whoever builds responsibly on top of what those systems have made possible.

Independence is not an event.

It is a capability — built once, and then rebuilt, generation after generation, by whoever is willing to treat the work as unfinished.

References

Government & Public Infrastructure

Additional Reading

16. About the Author

Aniruddha Bhattacharya is a Data Platform Architect, BI and Analytics leader, and technology writer with nearly two decades of experience delivering enterprise data and analytics solutions across global organizations.

His writing explores the intersection of data, AI, digital transformation, architecture, and technology leadership. Through Aniruddha Writes, he shares practical insights, long-form essays, and reflections on how technology shapes organizations, industries, and society.

When not working with data platforms and analytics ecosystems, he enjoys writing on technology, innovation, and the evolving relationship between people and digital systems.

IndependenceDay #DigitalIndia #UPI #Aadhaar #DigiLocker #CoWIN #PublicDigitalInfrastructure #AI #AIGovernance #TechnologyLeadership #NationBuilding #Innovation #DigitalTransformation #India #CapabilityBuilding

The DISTKEY That Was Right, and Still Wrong

Aniruddha Writes — Thu, 06 Aug 2026 19:50:01 +0000

Editor's Introduction

Enterprise data platforms rarely fail at the layer where the problem was created.

More often, a modeling decision made months or years earlier quietly propagates through ingestion layers, work tables, conformed dimensions, materialized views, semantic models, and operational reporting systems until a seemingly unrelated incident finally exposes it.

This case study examines one such investigation in Amazon Redshift, where a performance optimization that was technically correct revealed deeper challenges involving shared dimensions, physical design, lineage visibility, and downstream dependencies.

The Morning Nothing Was Supposed to Be Wrong

Eight-oh-four on a Tuesday. Three messages hit the team channel inside sixty seconds, from three people who hadn't talked to each other yet. That's the tell, more than the message itself — when three different people notice the same thing independently before anyone's had a chance to compare notes, you're not dealing with someone's misconfigured laptop.

The exec sales dashboard — store performance, category rollups, day-over-day, built to be ready before the 8:15 leadership call — had been loading in six seconds for months. That morning it sat spinning somewhere north of ninety.

Nothing had crashed. That's worth saying plainly, because it's the whole shape of the problem. No node down, no failed job, no red anything. I pulled the cluster health view at 8:06 and it looked like a Tuesday.

An outage hands you a starting point — a stack trace, a failed health check, something that's already screaming at you. This wasn't that. This was a system that was healthy by every metric anyone had thought to alert on, and useless for the one thing that mattered that morning. Nobody had written an alert for "correct answer, too slow to matter," because nobody writes those until they've been burned by one.

First move, before touching anything: figure out what kind of slow this is. Compute-starved, contending for something, or just doing more work than it needs to. From the outside these look identical. They are not the same fix.

stl_query confirmed what the frozen screen already told us — elapsed time up roughly fifteenfold against the thirty-day baseline. More useful was what hadn't moved. svv_table_info showed fact_sales at 3% unsorted, same as a month prior — no sort-key decay. skew_rows sat near 1.0 across the tables in the join path — nothing lopsided. Cluster CPU was up a few points, nowhere near saturated. Concurrency Scaling hadn't fired once. stl_alert_event_log had nothing for this query in the prior 24 hours.

That's three plausible stories ruled out inside twenty minutes — not decayed, not skewed, not starved. It's tempting to read that as "we're close." I'd push back on that instinct. Ruling something out narrows the search. It doesn't tell you where the answer is. Confusing the two is how you end up an hour later, confidently wrong about something adjacent to the truth.

One more thing before we go further, because it changes how you read everything that follows: this dashboard sits in Power BI on a scheduled Import refresh, not DirectQuery. That matters. "The dashboard was frozen" and "the query was slow" are two different claims, and conflating them is how you waste an hour chasing a gateway timeout that was never the problem. We checked — the refresh history showed the query itself, not the connector, eating the ninety-plus seconds. Worth the thirty seconds it took to confirm, because if we'd guessed wrong here the whole morning goes sideways.

The other belief worth naming and rejecting outright: that throwing more nodes at it is a safe, reversible first move while you think. It isn't neutral. It costs money, it needs a window, and if it happens to shave a few seconds off — which it usually does, a little, for reasons that have nothing to do with your actual problem — it will convince the room the theory behind it was right. It wasn't tested. It was coincidence wearing a lab coat. We didn't resize. Worth saying, because the temptation was there, and in a lot of rooms it wins.

By nine we had a narrowed problem, a clean baseline, and genuinely no cause yet. What happened next looked, at the time, like the textbook move — and it was, technically, correct. That's exactly what makes it worth telling in detail.

The Fix That Was Right on Paper

By nine-fifteen someone had the EXPLAIN plan up, and you could feel the room relax the way rooms do when something finally has a name. There it was — XN Hash Join DS_DIST_BOTH between fact_sales and dim_store. Not buried. First page of any Redshift tuning guide, plain as day: both sides of the join getting redistributed across every slice, on every execution.

svl_query_summary backed it up — the join step alone was moving roughly 40M rows across the network per execution, accounting for the overwhelming majority of the query's runtime. Not a guess. A number, sitting in a system table, that told you exactly where the ninety seconds were going.

This wasn't a routine change to a random table, so it didn't go through routine channels — dim_store sits on the shared conformed layer, which around here means anything touching it goes through an emergency-change path with a rollback script attached, not the normal weekly release window. That's the only reason "afternoon window, same day" is a true sentence and not a red flag. Worth saying explicitly, because doing this on a shared dimension without that path would be a different, worse story.

dim_store is Type 1 — current state only, no history retained. That's not incidental to this story; it's the reason store_id is even a clean, unambiguous grain to distribute on. Had it been Type 2, with a surrogate key and multiple versions per store, "distribute on store_id" stops being obviously correct — you're now co-locating on a business key that isn't the actual row-level grain, and the join locality argument gets murkier. Worth checking before you touch the DDL, not after.

The change went in: DISTSTYLE from EVEN to KEY on store_id, matching fact_sales. Ran the query again. Eleven seconds. svl_query_summary on the re-run showed the redistribution step gone entirely — replaced by a local join, no network shuffle. The ticket closed with a note that read, roughly, "root cause identified and resolved."

Here's where I want to slow down, because skating past this is how the lesson gets cheapened. The fix was not wrong. I need to be precise about that — it would be a much less useful story if it were. The join genuinely was redistributing both sides, every time. The change genuinely eliminated that. Anyone in that room could defend the decision an hour later with a system-table printout in hand.

The problem was the size of the question everyone agreed to answer. The question that got asked: is this DISTKEY right for this join? The question that actually mattered: is this DISTKEY right for every join that touches this table? Those sound like the same question stated more broadly. They're not.

The first one you can verify in an afternoon with svl_query_summary. The second one requires knowing something that lived nowhere in the platform — who else depends on dim_store — because nobody had ever built a bus matrix, and the catalog tooling we had covered pipeline lineage within a team's own domain, not cross-team consumption of shared dimensions.

That's not a process the team forgot to follow. It's a capability the platform never had.

Diagram D2 — The Lineage No One Had Mapped

A shared conformed dimension can act as an enterprise hub. Without complete lineage visibility, a local optimization may unknowingly impact downstream consumers that were never evaluated. *

A quick word on scope, because the mechanics here are Redshift's, specifically — DISTKEY, slice placement, DS_DIST_BOTH. On Snowflake this conversation doesn't happen the same way; there's no distribution key to get wrong, clustering behaves on a completely different axis. On Fabric, you're reasoning about V-Order and file compaction, not which slice a row lives on.

The lesson that survives the platform — a shared table has as many "correct" configurations as it has consumers, and optimizing for the loudest one is a trade, not a fix — that part travels. The mechanism doesn't.

Ticket closed. Dashboard fast. Everyone went home Tuesday believing it was over.

It took less than a week to learn it hadn't ended. It had moved. Two tickets landed forty-eight hours apart, filed by two teams who had no idea they were describing the same event.

Two Tickets, Same Week, No Shared Cause Line

The first came from the store-traffic team Thursday afternoon.

Their model — fact_customer_traffic, feeding a staffing-optimization tool — had started throwing intermittent timeout errors on its late-afternoon refresh.

Intermittent is the word that should slow you down.

A deterministic failure is a bug.

An intermittent one, on a schedule that hadn't changed, on a query that hadn't changed, on data volume that hadn't meaningfully grown — that's contention, and contention means something else is sharing the room.

The second came Saturday morning, from the inventory team.

Their overnight fact_inventory load had blown past its four-hour SLA window twice in the same week, for the first time in over a year of stable history.

Neither ticket mentioned dim_store.

Neither team had touched anything of their own.

That's precisely the trap: when the thing that broke isn't the thing you own, your first instinct is to assume the platform is having a bad week, not that someone else's Tuesday-afternoon fix reached into your pipeline uninvited.

A shared table doesn't announce who it's about to affect. It just changes, and waits for the tickets to arrive.

Nobody connected the tickets immediately.

They were triaged separately.

Different teams.

Different symptoms.

Different assumptions.

What eventually connected them wasn't insight.

It was discipline.

Both query plans touched dim_store.

That was enough reason to keep digging.

The investigation stopped being about one dashboard.

It became a question about the architecture itself.

Following the Lineage, Not the Symptom

Environment at a Glance

At this point the investigation had to stop being reactive.

Chasing each symptom individually would have produced three separate fixes on top of one shared cause.

Instead, we traced the lineage.

The platform looked like this:

L1 — landing and standardization
WRK — transformation and business rules
DWH — conformed enterprise model
Materialized Views — performance acceleration layer
Data Marts and Semantic Models — consumer-facing analytics

Diagram D3 — One Change, Three Symptoms

One physical design change produced four different outcomes. The original dashboard improved dramatically, while other consumers experienced entirely different performance symptoms through unrelated mechanisms. *

We traced each affected consumer through the stack.

The traffic model showed memory spill.

The inventory process showed I/O contention.

The dashboard's materialized view showed silent fallback from incremental refresh to full recompute.

Three symptoms.

Three mechanisms.

One upstream event.

And for the first time, the investigation started moving away from the dashboard and toward the model itself.

What "Skewed" Actually Meant Here

The traffic-model spill deserved its own look, because it's where the investigation nearly went sideways a second time.

The instinct in the room, once svv_table_info showed skew_rows climbing on fact_sales and fact_customer_traffic post-change, was to run VACUUM and see if it helped.

It's worth stating precisely why that instinct is wrong, because it's one of the most common misdiagnoses in Redshift work, and it nearly cost us a maintenance window we didn't need to spend.

VACUUM reclaims deleted-row space and re-sorts rows within a slice's existing boundaries.

It does not move rows between slices.

Distribution skew — an uneven number of rows landing on different slices — is a placement problem, not a sort problem, and no amount of re-sorting within a slice changes how many rows that slice was assigned in the first place.

We ran the numbers before running the command.

skew_rows for fact_sales sat at roughly 2.3, meaning the busiest slice was holding well over double the rows of the least busy one.

VACUUM was never going to touch that number.

The actual cause was structural, and it was sitting in dim_store all along.

store_id was not an evenly distributed key.

A handful of values — flagship stores and aggregated online channels — accounted for a disproportionate share of transaction volume across the platform.

Co-locating fact tables on store_id didn't just eliminate redistribution.

It concentrated the heaviest-volume rows onto the same physical slices.

For every fact table.

At the same time.

The fix hadn't introduced a new problem so much as it had made an old, dormant one load-bearing for the first time.

That distinction mattered.

The skew wasn't created by the DISTKEY change.

The skew already existed in the business.

The DISTKEY change simply exposed it.

Under EVEN distribution, Redshift ignored business meaning and spread rows mechanically.

Under KEY distribution, Redshift inherited the real-world imbalance embedded in the business key.

The platform's physical layout became a reflection of the business itself.

That's where the investigation changed direction.

It stopped being about distribution keys.

It started being about modeling decisions.

The Grain Problem Underneath the Distribution Problem

The question that actually needed answering wasn't:

What distribution style fixes this?

The question was:

Why does a single value in store_id carry forty times the transaction volume of a normal store?

The answer wasn't hiding in a query plan.

It was hiding in the data model.

When we traced the lineage all the way back to the L1 and WRK layers, we found something that had existed for years.

dim_store wasn't modeling a single business concept.

It was modeling two.

Physical retail locations.

And aggregated digital sales channels.

Online sales.

Marketplace integrations.

Digital storefronts.

All represented as single synthetic "store" rows.

From a reporting perspective, the design was elegant.

Every downstream report could treat online and offline channels uniformly.

No branching logic.

No special-case joins.

No additional dimensions.

But that convenience came with a hidden cost.

One synthetic row represented a volume of transactions that dwarfed every physical location.

As digital sales grew, that imbalance grew with them.

The model encoded business skew directly into the dimension grain.

No DISTKEY could fix that.

No SORTKEY could fix that.

No VACUUM could fix that.

You can redistribute rows.

You cannot redistribute the business meaning behind a row.

If one value legitimately represents thirty-five percent of enterprise transactions, every physical design strategy must eventually deal with that reality.

EVEN distribution hides it.

KEY distribution exposes it.

Neither removes it.

That's the thesis of the incident.

The bottleneck wasn't born in the dashboard.

It wasn't born in the materialized view.

It wasn't born in the DWH layer.

It originated years earlier when a modeling decision defined what a "store" meant.

Everything downstream was merely experiencing the consequences.

Why We Couldn't Just Fix dim_store

The obvious solution seemed straightforward.

Split the grain.

Separate physical stores from digital channels.

Give each concept its own dimension.

Remove the concentration at the source.

Technically, that would have been the cleanest solution.

Operationally, it was impossible.

At least not immediately.

dim_store wasn't an internal implementation detail.

It was a published enterprise contract.

Power BI semantic models depended on it.

Scheduled ETL jobs depended on it.

Inventory systems depended on it.

Forecasting applications depended on it.

External vendor integrations depended on it.

Changing the grain would have meant coordinating dozens of downstream consumers.

Some owned by other teams.

Some owned by vendors.

Some with release schedules measured in months rather than days.

The issue wasn't technical complexity.

The issue was dependency complexity.

This is one of the most important lessons in enterprise architecture.

The technically correct solution and the shippable solution are often different solutions.

Interfaces become expensive precisely because they succeed.

The more consumers depend on them, the harder they become to change.

dim_store had become successful enough to be dangerous.

Changing it wasn't a database change.

It was an organizational change.

And organizational changes move slower than production incidents.

So the redesign had to happen somewhere else.

Somewhere invisible to consumers.

Somewhere inside the platform.

Redesigning the Work Layer, Not the Interface

The eventual solution came from separating two concepts that had accidentally become linked.

Business grain.

And physical distribution.

Consumers cared about the business grain.

Redshift cared about physical distribution.

There was no requirement that they be represented by the same key.

The redesign happened entirely within the WRK layer.

No downstream consumer saw it.

No contract changed.

No semantic model needed modification.

No vendor needed notification.

For the handful of high-volume digital-channel records responsible for the skew, we introduced a derived distribution bucket.

Instead of distributing those records solely by store_id, we computed a synthetic distribution value using a secondary attribute and a hashing function.

The objective wasn't to change business meaning.

The objective was to spread physical placement across slices.

Ordinary stores continued using their natural key.

Only the concentrated digital-channel records were salted.

The salting existed purely for distribution.

Not for reporting.

Not for analytics.

Not for business logic.

Diagram D4 — WRK Layer Redesign: Salting Without Breaking the Contract

Salting was introduced only within the WRK layer to improve physical distribution. The business-facing grain and consumer contracts remained unchanged after reconciliation in DWH.

The second part of the solution was equally important.

Before anything crossed into the DWH layer, the salted buckets were re-aggregated back to the original business grain.

Consumers never saw the buckets.

Consumers never saw the salting.

Consumers continued seeing the same store_id values they always had.

The contract remained unchanged.

The optimization remained internal.

One more thing worth flagging.

The reconciliation process required rebuilding portions of the downstream structures.

Whenever a large rebuild occurs, SORTKEY effectiveness should be revalidated rather than assumed.

Eliminating redistribution only to lose scan pruning would have been a poor trade.

We verified that the existing date-based SORTKEY strategy continued delivering the same zone-map pruning benefits after the redesign.

Distribution improved.

Scan efficiency remained intact.

The redesign also included several supporting changes:

Targeted ANALYZE on frequently joined and filtered columns
Explicit maintenance-window scheduling for future DISTKEY changes
Ongoing monitoring of skew_rows and unsorted%
Validation of materialized-view refresh behavior after physical redesign

The goal wasn't simply to fix one incident.

It was to create guardrails that would detect the next one sooner.

What We Rejected, and Why

Several alternatives were considered.

Some looked attractive.

Some looked simpler.

None addressed the actual problem.

Option 1: Revert `dim_store` Back to EVEN Distribution

This would have restored the original physical layout.

It also would have restored the original dashboard problem.

The Tuesday incident would return immediately.

The ninety-second query would become normal again.

Nothing would be learned.

Nothing would be fixed.

This option simply moved the pain back to its original owner.

Rejected.

Option 2: DISTSTYLE ALL

At first glance, this looked promising.

Replicate dim_store to every node.

Eliminate redistribution entirely.

Allow every consumer to perform local joins.

Problem solved.

Except it wasn't.

dim_store wasn't static.

It received daily updates.

Type 1 changes.

New channel additions.

Attribute corrections.

Store metadata updates.

DISTSTYLE ALL makes every write more expensive because every node must maintain a copy.

What appears inexpensive at today's scale often becomes technical debt at tomorrow's scale.

A growing shared dimension replicated everywhere eventually becomes a write-side bottleneck.

Rejected.

Option 3: Immediately Split the Grain

From a modeling perspective, this was the cleanest solution.

Separate physical stores.

Separate digital channels.

Eliminate the concentration.

Create a more accurate business representation.

Technically correct.

Operationally unrealistic.

Too many downstream consumers.

Too many dependencies.

Too many contracts.

The cost of coordination exceeded the urgency of the incident.

Rejected for now.

Not rejected forever.

Option 4: Tune Each Consumer Individually

This approach would have produced three separate fixes:

One for the dashboard
One for the traffic model
One for inventory processing

The problem with symptom-based optimization is that it assumes you've already found every symptom.

We hadn't.

We couldn't.

The actual number of consumers remained unknown.

Fixing three visible problems while leaving the root cause intact simply guarantees a fourth ticket later.

Rejected.

The common theme across every rejected option was simple.

They optimized symptoms.

Not causes.

The investigation had already proven where that approach leads.

What Actually Changed, Measured Carefully

Engineering articles often overstate outcomes.

This one shouldn't.

The results were meaningful.

Not magical.

The executive dashboard returned to single-digit response times.

The original query stabilized near eleven seconds after the redesign.

Materialized-view refreshes returned to incremental maintenance instead of full recomputation.

The staffing model stopped experiencing intermittent timeout events.

Across the observation period that followed, no new memory-spill events appeared in the relevant hash-join stages.

The inventory pipeline returned to its SLA window.

Overnight processing consistently completed within the operational threshold required by downstream warehouse operations.

The skew metrics improved substantially.

skew_rows decreased from approximately 2.3 to under 1.3.

Not perfect.

Better.

Importantly, scan efficiency remained stable.

Post-redesign analysis showed no meaningful increase in blocks scanned for the dashboard's date-filtered workloads.

The skew reduction did not come at the expense of SORTKEY effectiveness or zone-map pruning.

That validation mattered.

Performance tuning often succeeds in one dimension while quietly regressing another.

This time it didn't.

The outcome most people remember is the faster dashboard.

The outcome I remember is different.

The platform became predictable again.

Engineers stopped discovering hidden consequences several days after a change.

That's harder to measure.

And often more valuable.

Diagram D5 — Before vs After: Where the Skew Lives

The investigation succeeded because the team stopped treating the dashboard as the problem and followed the lineage upstream until reaching the original modeling decision that created the downstream symptoms.

What's Still Unresolved

Two things remain unresolved.

Both matter.

The first is the grain problem itself.

Physical stores and digital channels still share the same published dimension.

The salting strategy manages the consequences.

It does not eliminate the underlying modeling decision.

As online volume grows, the pressure on that design will grow with it.

Eventually the organization may need to revisit the grain directly.

The second issue is more concerning.

The lineage gap still exists.

The incident revealed it.

The investigation worked around it.

Nothing fundamentally removed it.

We now know far more about dim_store consumers than we did before.

Mostly because we spent time manually discovering them.

The next engineer facing a similar change would still need to perform much of that discovery themselves.

The highest-priority follow-up is not another DISTKEY review.

It is visibility.

Shared assets require shared lineage.

Without it, teams optimize locally and discover globally.

That's exactly what happened here.

The Lesson That Outlasted the Ticket

Looking back, every individual decision made during the incident was defensible.

Changing the DISTKEY was reasonable.

Analyzing query plans was reasonable.

Avoiding unnecessary VACUUM operations was reasonable.

Rejecting DISTSTYLE ALL was reasonable.

None of those decisions were mistakes.

The mistake was assuming a shared enterprise object could be optimized in isolation.

It couldn't.

And it never could.

The dashboard failure wasn't caused by the dashboard.

The inventory delay wasn't caused by the inventory process.

The staffing-model spill wasn't caused by the staffing team.

Each symptom appeared in a different place.

Each root cause pointed to the same place.

A modeling decision made years earlier.

One layer further upstream than anyone initially expected.

Downstream symptoms are where you notice a problem. They are rarely where the problem was made.

That's the lesson that survives beyond Amazon Redshift.

The mechanism changes across platforms.

Snowflake.

Fabric.

Databricks.

BigQuery.

The pattern remains remarkably consistent.

A fix appears successful.

A symptom disappears.

A different symptom emerges elsewhere.

The real challenge is resisting the urge to stop investigating when the first visible problem goes away.

We didn't close this incident.

We reduced its impact.

We moved the cost to a layer where it could be managed deliberately.

We documented the trade-offs openly.

And we identified the work that still remains.

That's not a lesser outcome than "solved."

At enterprise scale, with shared models and long-lived contracts, it's usually the honest one.

Somewhere in that same conformed layer, another perfectly reasonable design decision is sitting quietly.

Waiting for an ordinary Tuesday morning to give it somewhere to concentrate.

This time, at least, we'll know where to look before the third ticket lands.

Key Takeaways

A correct DISTKEY can still create enterprise-wide problems when applied to a shared conformed dimension.
Query-level optimization and platform-level optimization are not the same thing.
Distribution skew often originates from business-modeling decisions, not database-engine behavior.
VACUUM cannot solve slice-level distribution skew.
Shared dimensions are architectural contracts, not local implementation details.
Physical optimization decisions should always be evaluated against all known consumers.
Lineage visibility is as important as performance visibility.
Sometimes the best fix happens in the WRK layer, not in the published model.
Enterprise performance issues frequently originate multiple layers upstream from where symptoms appear.
The most valuable outcome is often predictability, not speed.

Environment at a Glance

The investigation succeeded because the team stopped treating the dashboard as the problem and followed the lineage upstream until reaching the original modeling decision that created the downstream symptoms.

References

Amazon Redshift Distribution Styles
Amazon Redshift Sort Keys
Amazon Redshift Materialized Views
Amazon Redshift System Tables and Views
The Data Warehouse Toolkit — Ralph Kimball
AWS Prescriptive Guidance

The Post-Acquisition Assumption Gap

How inherited assumptions create hidden risks across enterprise data platforms.

When Data Becomes the Bottleneck

Unmasking the real causes behind SLA misses in modern analytics ecosystems.

Slowly Changing Dimensions in the Real World

Why SCD design decisions often create consequences years after implementation.

Author

Aniruddha Banerjee

Project Manager | Data Architect | Enterprise Data Engineering | Cloud & Analytics Platforms

Sharing real-world lessons from enterprise-scale data platforms, architecture investigations, performance engineering, and operational excellence.

GitHub Pages: https://ruddhanib.github.io/aniruddhawrites/

LinkedIn: https://www.linkedin.com/in/ruddhani/

Medium: https://ruddhani.medium.com/

Author's Note

This article is inspired by recurring patterns observed across enterprise Amazon Redshift environments. Table names, business context, operational timelines, and selected implementation details have been generalized to protect confidentiality.

The technical mechanisms, architectural trade-offs, investigation approaches, and performance-engineering principles described remain representative of genuine production challenges commonly encountered in large-scale analytical platforms.

amazon-redshift #data-engineering #data-architecture #data-modeling #performance-optimization

The Post-Acquisition Assumption Gap

Aniruddha Writes — Sat, 18 Jul 2026 05:15:57 +0000

Two years after we closed an acquisition, we found out our own pipeline still thought the company hadn't happened.

Not the ERP. Not the org chart. The CDC job watching product category changes — built two years before the deal, quietly assuming there was exactly one PIM instance in the world, because at the time there was. Nobody rearchitected that assumption when the acquisition closed, because "does our pipeline's cardinality assumption survive an acquisition" isn't a line item anyone owns. Integration architecture disbands once systems are technically connected. Data engineering inherits the pipeline, not the reason it was built the way it was. The gap between those two teams is where this incident actually lives, and I want to say clearly: nobody in this story made a mistake. A category manager clicked save on a taxonomy cleanup that had been scheduled for weeks. That's the entire inciting event. Everything expensive that happened afterward was already true before she logged in.

I'm going to move through the CDC-can't-capture-intent problem quickly, because if you've built distributed systems you already know this — row-level change capture answers "what changed," not "why," and no amount of tooling sophistication fixes that; you either capture intent at the write path (an outbox, a domain event) or you don't have it. What I want to spend real time on is what capturing intent actually costs you, because that part gets skipped in almost every version of this argument I've read.

The Outbox Pattern — Mechanics and Its Gap

Here's the part nobody puts in the diagram. The outbox pattern doesn't eliminate the intent-capture problem. It relocates it — from "we don't know why this changed" to "we have a reason_code column, and now someone has to govern what goes in it, forever, with the same rigor you'd apply to a shared vocabulary in a multi-team API contract." That's a harder job than it sounds like, because unlike a schema, a taxonomy of meaning has no compiler to enforce it. Marketing adds 'seasonal-refresh' because it unblocks their sprint. Nobody tells compliance, who six months later need to isolate 'regulatory-correction' events for an audit and discover the taxonomy has quietly drifted into unreliability.

The uncomfortable version of this claim: most organizations that adopt the outbox pattern to solve the CDC-intent problem end up trading a technical debt they understood for a governance debt they don't have a process for at all. Technical debt shows up in a code review. Taxonomy drift shows up eighteen months later, in an audit, as a surprise. I don't think this makes the pattern wrong. I think it means "we added an outbox" is not the finish line people treat it as, and if your architecture review signs off on intent-capture without also assigning a human owner to the reason-code vocabulary, you've solved the half of the problem that was easier to solve.

Reason-Code Governance Debt Over Time

I think most of the SCD debate — Type 1 versus Type 2, table-level versus attribute-level, how much history is enough — is arguing about the wrong variable. The question isn't "what SCD type does this attribute deserve." It's "who is asking, and what does they need to be true about the past."

Finance, reconstructing last quarter's numbers, needs the category that was true on the transaction date — Type 2, strict effective dating. Merchandising, planning next season's assortment, wants the current hierarchy applied backward so trend lines are comparable to today's structure — which is functionally Type 1, applied retroactively, and is correct for their question even though it's the same "wrong" behavior that blew up finance's quarter. Compliance, defending an audit, needs to know not just what the value was, but who decided it and why — which neither Type 1 nor Type 2 gives you without an outbox event attached.

Three consumers. Three different correct answers. One physical attribute. Most warehouses pick one SCD treatment per attribute and call it done, because building consumer-specific temporal views is expensive — and then act surprised when one consumer's "correct" implementation is another consumer's incident. I don't think there's a clean fix for this that doesn't cost something. Type 6 hybrids (carry both the historical and current value on every row) get you partway there for two consumers at once, but they don't solve for compliance's need for decision-provenance, which requires the event-level metadata a bare dimension table was never built to hold. I'm not sure the honest answer is more sophisticated than "you need to know which consumer you're building for before you pick a strategy, and most teams pick the strategy before anyone's asked the question."

Consumer-Relative SCD — The Core Reframe

I used to tell every team the same rule: never hard-delete a crosswalk row, always end-date it. I don't hold that as a blanket rule anymore.

End-dating solves a real problem — it lets you tell "this relationship expired" apart from "this relationship never existed," which a hard delete destroys completely. That part I still believe.

What I left out for years: if that relationship touches anything adjacent to personal data, even indirectly, keeping it around forever in end-dated form is still retention — and some data minimization regimes have an affirmative deletion requirement that end-dating doesn't satisfy no matter how well-intentioned it is. "Never delete" and "comply with deletion law" are not always compatible instructions, and most SCD advice, mine included, has quietly assumed more history is an unqualified good. It isn't. It's a trade-off with a legal dimension I used to treat as someone else's problem.

Hard Delete vs. End-Date — The Privacy Tension

Instead of presenting the typo/decision rows as "here's proof of the problem," frame it as a direct provocation, which lands harder and is more shareable as a standalone image:

Look at your own dim_product right now. Pick any Type 2 attribute. Can you tell, from the table alone — no tribal knowledge, no Slack archaeology — which of its historical rows represent a business decision and which represent someone fixing a typo?

If the answer is no, you don't have an audit trail. You have a very expensive list of things that happened, with the one piece of information that would make it useful — why — living somewhere else, if it's living anywhere at all.

(Table follows as before, now serving as evidence for a claim already made, not the claim itself.)

The Bridge-Table Wager

Call it the bridge-table wager. Every team building a crosswalk between two systems is making an implicit bet about how many independent dimensions of change that relationship will need over its lifetime. Bet low — one dimension, effective dating alone — and a Kimball bridge table is simpler, cheaper, and analyst-friendly. Bet high — effective dating plus provenance plus, eventually, some kind of confidence scoring you haven't thought of yet — and a Data Vault Link with its own Satellite ages better, because it doesn't force every new dimension of change into the same row-versioning event.

Nobody knows which bet is right at design time. That's the actual disagreement, and I don't think it resolves with more expertise — it resolves with hindsight, which is exactly the thing architecture decisions never get to have. I've watched two architects I equally respect land on opposite sides of this exact wager for reasons that were both completely defensible given what they knew when they made the call.

Provenance You Can't Verify

One thing worth keeping at full strength rather than trimming: when you retrofit a source_system column onto a crosswalk table that's been running for five years across three different loaders, the rows written before that column existed don't get real provenance. They get your best guess, formatted to look like data. You can infer which loader probably wrote a row from column population patterns or ID formatting, but you can't verify it, and a compliance query built on that inference is answering a question with a guess wearing a structured column's clothing. I don't think this is a solvable problem after the fact. I think it's a reason to instrument provenance on day one even when it feels like premature process, because there is no retroactive fix that isn't fiction.

When Data Becomes the Bottleneck: Unmasking the Real Culprit Behind SLT Misses

Aniruddha Writes — Sat, 11 Jul 2026 09:39:10 +0000

We kept blaming the data. Turns out, the data was innocent. 🔍
For weeks, our SLTs were being missed. Query timeouts. Stale dashboards. Frustrated users. Every indicator pointed at the data layer — so that's where we dug.
What we actually found:
🔴 The root cause wasn't bad data — it was data CONTENTION. Hundreds of concurrent queries fighting over the same shared Fabric capacity, queuing, blocking, and cascading into failure.
🌍 And then there was the geography problem nobody had mapped — our Fabric capacity tenant sitting across the Atlantic, adding 80–150ms of round-trip latency to EVERY query. Not slow computation. Slow continents.
The result? Gateway timeouts that looked like data failures. Connection abortions mid-flight. SLT compliance sitting at 54%.
Once we tackled concurrency limits, workload management, and migrated our tenant region closer to our users?
✅ Response times dropped 68%
✅ SLT compliance hit 91%
✅ Gateway timeouts: near zero
The data was fine the whole time. We were just asking too much of it, from too far away.
I believe you will be more interested 👇 — especially relevant if you're running analytics on Microsoft Fabric or dealing with cross-region BI deployments.

A deep-dive into how high-concurrency query contention and cross-Atlantic infrastructure latency quietly erode service levels — and what to do about it.

The Symptom Everyone Sees, The Cause Nobody Suspects

In most performance post-mortems I've been part of, the conversation starts the same way: "The data is slow."

Dashboards are lagging. Reports are timing out. Business users are frustrated. SLTs (Service Level Targets) are being breached, and fingers are pointing squarely at the data layer.

And to be fair — the data is where the pain surfaces. But here's the uncomfortable truth that took us time to fully unpack:

The data wasn't broken. The data was overwhelmed — and it had a geography problem.

The Investigation: Peeling Back the Layers

Layer 1 — SLT Misses and the Data Blame Game

Our Service Level Targets were consistently missed during peak business hours. Query response times that should have landed under 3 seconds were stretching to 30, 60, sometimes over 120 seconds. Dashboards were either stale or outright failing to load.

The immediate assumption? Data quality issues. Bad indexes. Poorly written queries. An overloaded dataset.

We tuned queries. We rebuilt indexes. We optimised data models.

The problem persisted.

Layer 2 — The Real Root Cause: Data Contention Under Concurrent Load

What our monitoring eventually revealed was not a quality problem but a concurrency problem.

During peak hours, hundreds of users were simultaneously firing queries against the same datasets. The underlying engine — running on a shared Microsoft Fabric capacity — was not failing because the data was wrong. It was failing because too many queries were competing for the same computational resources at the same time.

This is data contention: a condition where high volumes of concurrent queries queue up, block each other, and create a cascading slowdown that ripples all the way to the end user experience.

Key symptoms we identified:

Query queue depth spiking dramatically during 9–11 AM and 2–4 PM windows
Throttling events logged at the Fabric capacity level
Spill-to-disk operations increasing as memory pressure mounted
Individual query execution time remaining acceptable in isolation, but degrading 10–40x under concurrent load

The data was never the culprit. Contention was.

Layer 3 — The Atlantic Problem: Latency Nobody Mapped

Here is where the investigation took an unexpected turn.

Our Microsoft Fabric capacity tenant was provisioned in a region across the Atlantic — physically and network-topologically distant from the majority of our user base. What looked like slow query responses was, in many cases, not slow computation at all. It was network round-trip time compounding every single interaction.

The effects were insidious:

Gateway timeouts — connections from local gateways to the remote Fabric tenant were breaching timeout thresholds before query results could be returned, not because queries were slow, but because the network handshake and data transfer time pushed the total wall-clock time over the edge.
Connection abortions — under load, TCP connections to the distant tenant were being dropped mid-flight. The client saw a failure. The capacity was actually working — the work was simply never delivered.
Compounding effect — contention-induced delay + cross-Atlantic latency + gateway timeout threshold = a perfect storm that made every SLT miss look far worse than the underlying compute performance warranted.

A query that took 8 seconds to execute looked like a 45-second failure from the user's perspective. The network ate the difference.

The Architecture of the Problem

Every query traversed this path — twice (request and response). Under contention, queries that lingered in the execution queue long enough would cause the gateway to give up before the answer arrived.

What We Did About It

Addressed Contention at the Capacity Layer
Scaled up Fabric capacity during peak windows using autoscale policies
Implemented query concurrency limits and workload management rules to prioritise critical service-level reports
Introduced incremental refresh and aggregation tables to reduce raw query payload sizes
Shifted non-urgent batch workloads to off-peak hours to reduce simultaneous demand
Tackled the Geography Problem
Engaged with our Fabric tenant configuration to migrate capacity to a region closer to our primary user base — a process that required planning but delivered the most dramatic latency improvement
Increased gateway timeout thresholds as an interim measure to prevent premature connection drops while longer queries completed legitimately
Implemented connection pooling and keep-alive configurations on the gateway layer to reduce connection setup overhead on every request
Improved Observability
Instrumented query telemetry to distinguish network latency from compute latency — critical for correctly diagnosing future incidents
Built a capacity utilisation dashboard to give early warning of contention events before they breached SLTs
Established a baseline for expected cross-region RTT so anomalies could be detected faster

The Results

Once the contention management and regional migration work completed:

Average query response time dropped by ~68% during peak windows
SLT compliance improved from ~54% to over 91% within 6 weeks
Gateway timeouts effectively dropped to near-zero
User satisfaction scores for the analytics platform improved significantly

The data, as it turned out, had been perfectly fine the whole time.

Key Takeaways for Data & Platform Engineers

Don't confuse the symptom with the cause. Slow data experiences are often infrastructure and concurrency problems wearing a data costume.
Concurrent query volume is a first-class concern. Design your capacity with peak concurrency in mind, not just peak data volume.
Geography matters more than people think. Cross-region tenancy is often an afterthought in platform provisioning. It shouldn't be. Measure your RTT early and often.
Gateway timeouts are a network story, not a query story. If timeouts correlate with specific time windows and user geographies, suspect latency before suspecting code.
Observability must separate layers. If you can't distinguish network time from compute time in your telemetry, you will misdiagnose performance problems — repeatedly.

Final Thought

The most dangerous performance problems are the ones that look obvious but aren't. SLT misses that appear to be data problems can spend months in the wrong queue — being "fixed" by data engineers while the real causes (concurrency limits, tenant geography, gateway configuration) sit untouched.

Invest the time to instrument your full stack. The answer is rarely where the pain is loudest.

Have you encountered similar patterns in your data platforms? I'd love to hear how your teams approached contention and latency challenges — drop a comment below.

#DataEngineering #MicrosoftFabric #PowerBI #ServiceLevels #DataPlatform #CloudArchitecture #Latency #PerformanceEngineering #Analytics #DataContention #TechLeadership