The Hidden Cost of CDC Pipelines: How Small Files Create an S3 Request Bomb

In Why We Redesigned Our CDC Pipeline with Debezium and Flink, we introduced our Debezium + Flink-based CDC pipeline. Debezium captures database changes, routes them through Kafka, and Flink writes Parquet files to S3 every 5 minutes.

The pipeline worked well for over a year. Data consistency was verified, and operations were stable. The problem remained invisible until we dug into our S3 costs as part of a data platform TCO analysis.

S3 Cost Is More Than Just Data Scanned

If you run Athena, you probably think of cost like this:

“Athena cost = data scanned × $5/TB”

That’s not wrong — but it’s only the Athena service charge. Here’s what actually happens when Athena runs a query:

1. Lists target files from S3 (LIST)
2. Opens and reads each file (GET)
3. Writes results back to S3 (PUT)

Among these, GET requests scale proportionally with the number of files. LIST operates at the prefix level, and PUT is once per query for result storage — but GET must open every single file. $0.0004 per 1,000 GET requests (ap-south-1) — sounds tiny, but not when you have hundreds of thousands of files.

We broke down our data platform’s S3 costs by API operation:

Over 10 months, storage grew 45%, but GetObject cost surged 825%. Nearly half of our S3 bill was from reading files.

Tracking Down the Culprit

To pinpoint the cost, we needed to know “which path’s files are being read how often.” The problem is that Cost Explorer only shows costs at the S3 service level — it can’t tell you which bucket or prefix is responsible.

We combined three approaches.

1. S3 Cost Allocation Tag + Cost Explorer

Tag your S3 buckets (e.g., purpose: datalake), then activate the tag in Billing → Cost Allocation Tags. Without activation, tags won’t appear in Cost Explorer filters. Once activated, group by tag to see storage vs request cost ratios per bucket.

2. S3 Storage Class Analysis

This feature analyzes access patterns per prefix within a bucket. The console provides limited visualization, but by configuring a destination bucket, you can export daily CSV data with GetRequestCount, DataRetrieved_MB, Storage_MB per prefix. Data appends daily, enabling trend analysis.

We set up analysis groups for 29 prefixes. Since multiple optimizations were running simultaneously, we used the stable window just before compaction (6/5~6/8 average) as our baseline:

Note: The GetRequestCount column actually includes both GET and PUT requests despite its name. AWS documentation explicitly states it is mislabelled. Keep this in mind for precise cost estimation.

A single prefix — service_a.cdc — accounted for 93% of all requests.

3. File-Level Analysis

While Storage Class Analysis showed us “where,” we needed to look at the actual file structure to understand “why.” We analyzed the CDC path on S3:

335KB average. Clearly inefficient for Parquet files in production.

Why Small Files Happen

This is a structural problem in CDC pipelines.

Flink commits files at every checkpoint interval. Our setting is 5 minutes. That’s 288 times a day, across 300+ tables, each with dt partitions — multiply it out and you get tens of thousands of files per day.

Each file contains “changes that occurred in that table during those 5 minutes.” Most tables only see dozens to hundreds of changes per 5-minute window. The result: files ranging from a few KB to a few hundred KB pile up endlessly.

After about 14 months of operation, 31.4 million files had accumulated in the CDC path.

It’s Not Just About Cost

The small file problem isn’t limited to cost.

Reading from S3 requires opening an HTTP connection per file. Reading one 128MB file versus 400 files of 335KB each transfers roughly the same data, but the overhead differs by orders of magnitude.

• After listing files in a partition/prefix, each object must be opened individually
• Each file incurs GET or range GET, Parquet footer/metadata parsing, schema processing, and task scheduling overhead
• Without connection reuse, TCP handshake + TLS negotiation occurs for every file

Whether it’s Athena or Spark, no engine can be fast if it has to open hundreds of thousands of files.

How We Fixed It: Daily Compaction

We evaluated three approaches.

1. Increase Flink checkpoint interval

• Pros: Simplest approach
• Cons: Degrades data freshness, impacts all downstream — business monitoring, CDC-based replication, and other pipelines

2. Write directly to Iceberg from Flink

• Pros: Table format-level small file management
• Cons: Requires Flink sink redesign, high migration risk

3. Post-hoc Daily Compaction

• Pros: No changes to existing ingestion path, incremental rollout possible
• Cons: Additional batch cost, requires safety verification for file replacement

We chose option 3. It delivered the same result with the lowest risk, without touching the existing pipeline:

• Read all small files per partition (dt) and rewrite as 128MB files
• Preserve binlog position ordering
• Triggered daily by Airflow after CDC ingestion completes

Compaction replaces original files directly, so safeguards against data loss were essential. We implemented a Safe Swap pattern:

1. Write compacted output to a temp path (_tmp_compact/)
2. Verify files were successfully created in the temp path
3. Delete originals → copy temp files to original location → delete temp

Even if a failure occurs mid-step-3, data remains in _tmp_compact/ for recovery. We also compare row counts before and after compaction to verify no data loss.

Since S3 doesn’t provide atomic rename/replace, we only target completed previous-day partitions — never active ones — and schedule execution during low-query windows.

Single partition test result: 72,885 → 839 files (99% reduction)

We also backfilled 520 days of historical data. 31.4 million files were reduced to 369,000 (98.8% reduction).

Results

As compaction progressed, Requests/GB (requests per data retrieved) steadily decreased. This means the same amount of data could be read with fewer requests — the most direct measure of compaction effectiveness (Storage Class Analysis):

The figures up to 6/11 still include PUT requests from the backfill. 6/12 is the first fully clean post-backfill day, and the numbers stabilized by 6/12~14 — Requests/GB dropped from 6,016 to 1,281, a 79% reduction in the number of requests needed to read the same amount of data.

Since daily data retrieval varies, we normalized to the same retrieval volume (100,000GB) to compare costs:

Cost calculation: requests × 30 days × $0.0004/1,000 (ap-south-1). Actual query volume may fluctuate, so these figures don’t map 1:1 to billing.

Daily compaction now runs automatically on the previous day’s partitions, preventing small files from accumulating again.

How Much Faster Is It?

To measure the performance impact beyond cost, we ran benchmarks. We restored pre-compaction original files (45,008) from S3 versioning to a separate bucket and compared them against post-compaction files (310) using identical Athena queries. Row counts matched exactly at 59,184,128.

-- Q1: Full COUNT (155 days)
SELECT count(*)
FROM loan
WHERE dt >= '2026-01-01' AND dt <= '2026-06-05'

→ Before: 4,542ms / After: 1,403ms (-69%)

-- Q2: Filtered COUNT
SELECT count(*)
FROM loan
WHERE dt >= '2026-01-01' AND dt <= '2026-06-05'
  AND status = 'CLOSED'

→ Before: 2,138ms / After: 1,041ms (-51%)

-- Q3: GROUP BY aggregation
SELECT status, count(*)
FROM loan
WHERE dt >= '2026-01-01' AND dt <= '2026-06-05'
GROUP BY status
ORDER BY 2 DESC

→ Before: 2,752ms / After: 1,243ms (-55%)

-- Q4: Single day COUNT
SELECT count(*)
FROM loan
WHERE dt = '2026-03-01'

→ Before: 826ms / After: 560ms (-32%)

Across full scans, filtered queries, and aggregations, we observed 32–69% performance improvement. The total data scanned was identical — the difference comes purely from file-open overhead.

Looking Back

The common wisdom that “Athena cost = data scanned” actually delayed our discovery. Athena service charges looked like just a few hundred dollars per month — hardly alarming. But behind the scenes, hundreds of millions of S3 GET requests were firing daily, and those costs were buried in the S3 bill.

If you’re running a streaming pipeline that writes to S3, it’s worth checking:

1. How much of your S3 cost is requests vs storage
2. Whether requests are concentrated on specific prefixes (via Storage Class Analysis)
3. What the average file size is in your streaming paths

If the answers are “requests exceed 40% of total S3 cost,” “requests concentrate on a single prefix,” and “average file size under 1MB” — you’re probably facing the same problem.

For more details on the CDC pipeline architecture, see the Afinit team blog.