Fresh Snowflake data without constant merges
Update-heavy replication can make a warehouse spend most of the day rewriting huge tables for a comparatively small number of changes. This guide explains a hybrid pattern that keeps reads fresh, runs the heavy merge once a day, and preserves the full change history in a data lake.
The starting pipeline
A conventional setup starts with Aurora Postgres as the production database. Debezium reads change events from the write-ahead log, a schema registry tracks schema versions, and Kafka carries Avro events that land as parquet files in S3. Parquet gives strong compression and lets other tools, such as Spark, read the same lake files. Snowflake then consolidates create, update, and delete events into current-state tables, after which dbt enriches data across layers and Airflow schedules those transformations. Third-party API sources can go directly to the consolidated layer when that is more convenient. Keeping the original change stream in S3 rather than Snowflake avoids duplicating history in the warehouse while retaining it in the lake.
From S3 files to a consolidated table
Commercial replication had practical problems: large TOAST values were mishandled, schema evolution was unreliable, volume was high, and diagnosing inconsistencies was difficult. The replacement therefore favored a standard, inspectable path and avoided the Snowflake Connector for Kafka because vendor-neutral parquet files could serve other purposes. An external table points at those S3 files and lets Snowflake compute over them without moving storage. An insert-only stream on that external table records rows from newly arrived files, removing the need to build a custom tracker for ingested files. A Snowflake task consumes each batch and merges it using the operation marker carried from Debezium. With hundreds of tables, a configuration tool generates and manages external tables, streams, tasks, and merge logic so pipelines can be changed and tested without hand-writing every object. The retained history also acts like version history for the database, making it possible to inspect earlier row versions during troubleshooting.
Why continuous merging becomes the bottleneck
Many tables in this workload are heavily updated rather than immutable and event-like. Multi-billion-row tables could take hours to merge even on a large warehouse, which hurt both latency and cost. The cost traces back to the physical layout of columnar files. Parquet stores data as compressed blocks, so touching one row means decompressing the block, changing that row, and recompressing the whole block on write. Scatter those edits across a wide table and almost every block needs rewriting, which is why merge duration scales with table size instead of with the count of changed rows. Row-oriented formats like Avro are tuned for selective reads, yet editing a row in place still rewrites the file. Appends behave differently: each one simply adds a new block. Deletes would be expensive if physically applied, so OLAP engines usually record them as tombstone markers or look-aside tables. The same behavior was observed empirically in Snowflake: changing 70,000, 200,000, or 2 million rows took roughly similar time. Moving every downstream pipeline to the raw stream would speed ingestion but slow queries, and redesigning data as immutable events was not a viable short-term change.
Split freshness from the daily merge
The hybrid design separates freshness from physical consolidation. A consolidated table holds one physical row per logical row, reflecting changes up to the last merge. A much smaller ingestion table holds recent create, update, and delete events, possibly with several versions of the same logical row. The low-latency view reconciles both sources for each logical row. When a row is untouched since the last merge, the view serves the consolidated copy; when it has changed, the view serves the most recent version from the ingestion table. Underneath, this is a union, and the real difficulty is keeping it efficient. Query predicates have to be pushed down into the large consolidated side so only the relevant partitions are scanned and billions of rows are not shipped across the network. Full scans of the small ingestion side are acceptable. TOAST fields need special handling, and the execution plan has to be checked rather than assumed. In the worked example, a table with about 15 billion rows received about 35 million changes per day, roughly 0.1 percent of its size, which is what makes the split effective.
How the two processes run
Changes still arrive through the external table and stream. A task running every few minutes appends the stream to the ingestion table; because that table is small, this step takes seconds on an extra-small warehouse. Once a day, a larger warehouse merges the ingestion table into the consolidated table and empties the ingestion table so it cannot grow indefinitely. The daily schedule can be tuned to how quickly recent changes accumulate. Because the consolidated table is no longer rewritten constantly, automatic reclustering becomes practical and can improve query performance without clustering work being perpetually undone by updates. Readers use the low-latency view throughout, so they see current data even though the physical merge is infrequent. Tooling generates the views, tasks, and SQL for more than 100 ingested tables in minutes, which made iteration feasible.
Results and practical takeaways
Latency fell from two to three hours to a few minutes in potential, with stream consumption running every 30 minutes in practice; running every minute was possible but unnecessary because downstream ETL ran every one to four hours. Cost fell by approximately 75 percent because the large warehouse was needed only for the daily consolidation instead of running nearly all day. Several lessons carry beyond this specific case. Update-heavy consolidation should be treated as a cost and latency risk before it turns into one. Pipeline tooling is worth building early, since hand-configured objects stop scaling once you pass a handful of tables. Where the domain permits, immutable, denormalized, event-like data is preferable because it removes most of the merge problem. Debezium paired with Kafka proved a dependable replication combination here, outperforming the earlier commercial option on cost, reliability, and flexibility. External tables and streams absorbed much of the S3 ingestion complexity, and retaining the lake stream made it easier to investigate both product and replication issues. Small Kafka files should be compacted, in this case with Spark into roughly 250 MB parquet files following Snowflake guidance, to improve compression and reduce access cost. Postgres TOAST handling from Debezium deserves separate attention because it introduced unexpected complexity.
Decision checklist
Use this pattern when recent changes are orders of magnitude smaller than the consolidated table and scattered updates make frequent merges expensive. Before adopting it, check that the view can push predicates into the large table, that TOAST and schema changes are handled, that the ingestion table is emptied by each consolidation, and that downstream consumers read the view rather than the consolidated table alone. If changes grow too large relative to the table, shorten the consolidation interval or reconsider an event-based model.
Frequently asked questions
What is a Snowflake external table in this design?
A virtual table over files in object storage. Snowflake holds only file metadata and exposes the files through SQL; storage stays in the cloud provider, and direct DDL on the underlying files is not available.
Why use an insert-only stream on an external table?
It tracks new rows arriving from newly uploaded files without needing a separate file-ingestion ledger. A scheduled task can then consume the stream and apply inserts, updates, and deletes to the consolidated table.
Why are updates so expensive in columnar storage?
Scattered updates force large compressed chunks to be read, rewritten, and recompressed, so merge time tracks total table size more than the number of changed rows. Inserts create new chunks, and deletes can be represented as tombstones, so both are usually cheaper.
How can data stay fresh if consolidation runs once a day?
A view combines a large consolidated table with a small recent-changes table. Readers see the latest version of each logical row, while the expensive merge runs only once a day instead of continuously.
What value does retaining the replication stream provide?
Keeping the change stream in the lake preserves row history for debugging consistency problems. Compacting small files into larger parquet files improves compression, reduces requests, and supports long retention.