Building a Scalable Lakehouse with Apache Iceberg and Snowflake Horizon Catalog

Our engineering team built a production lakehouse using Apache Iceberg and Snowflake Horizon Catalog as a unified data platform, replacing fragmented per-customer pipelines. This single source of truth serves three consumption models: (1) customers access live, up-to-real-time data via read-through Secure Data Sharing with no refresh jobs or exports; (2) external tools read and write through Horizon’s open Iceberg REST endpoint without separate pipelines; (3) internal analytics operates directly on the Lakehouse’s Silver tables. The outcome is a scalable system where onboarding a customer is a lightweight entitlement change, customers always see current data, and operations scales without proportional overhead.

Introduction

A production lakehouse faces a fundamental architectural question: how should data move from the source system to consumers? The naive approach—multiple pipelines, format conversions, and redundant copies—introduces operational complexity, data consistency issues, and latency. This article outlines a design that addresses these challenges through a unified, open-format architecture, and the trade-offs involved in each choice. The design consolidates ingestion and transformation into a single platform (Snowflake), uses an open format (Iceberg) for data storage, and provides a unified metadata layer (Horizon Catalog) for access control and lineage tracking.

Our pipeline architecture is:

1. Ingestion: PostgreSQL Change Data Capture (CDC) via Datastream, written as Avro to cloud object storage (GCS).
2. Bronze Layer: Iceberg tables in cloud storage, populated via Snowpipe auto-ingest from the Avro files on GCS.
3. Silver Layer: Business-logic-ready Iceberg tables, maintained via Snowflake tasks running MERGE operations.
4. Gold Layer (planned): Consumption-ready, business-level datasets derived from Silver — aggregates, metrics, and ML/AI feature sets shaped for specific downstream use cases.

A key property of building on open Iceberg tables governed by the Horizon Catalog is that the Gold layer does not have to be produced by Snowflake. Because the Silver tables are exposed through Horizon over the open Iceberg REST protocol, any engine can read Silver in place and write Gold back as Iceberg tables registered in the same catalog.

Consumers

The lakehouse serves every consumer from one governed copy — the Silver Iceberg tables — with no per-consumer pipelines, exports, or extract copies. Consumers always see the current state, and access is governed centrally by Horizon Catalog. Today this supports three kinds of consumers:

1. Snowflake consumers via Secure Data Sharing. Customers query their own Ironclad data directly from their own Snowflake account, live, with no exports or scheduled jobs to run. Sharing is read-through, not materialized (no per-customer refresh to operate), and per-tenant isolation is enforced at query time — so onboarding a customer is a lightweight entitlement change, each customer sees only their own data, and when a customer offboards, their data is purged once at the source and the share reflects it immediately. This is currently our lakehouse’s primary consumption method in production.
2. Non-Snowflake consumers via Open Data Sharing. Because the data is already in open Iceberg tables governed by Horizon, we can extend the same model to consumers who aren’t on Snowflake. With Open Data Sharing, the identical live, read-only data is shared as a governed data product: external consumers connect from any tool that speaks the open Iceberg REST Catalog protocol, using a managed access token.
3. Other engines via Horizon Catalog. Beyond sharing to customers, our own and other processing engines can operate directly on the tables through Horizon’s Iceberg REST endpoint — reading Silver and writing derived (Gold) tables back into the same catalog. This makes the transformation and analytics engine a choice — Snowflake, Spark, Flink, BigQuery (via BigLake), or a custom engine — rather than a lock-in, while governance and lineage stay centralized in Horizon. We’re currently exploring this for BigQuery via BigLake.

The common thread is one open, governed copy in Iceberg, and multiple ways to access it — a Snowflake share, an open Iceberg REST share, or direct catalog access — with no duplicate pipelines or copies as we add engines.

1. Architectural decisions

1.1 Decision 1: Apache Iceberg as the table format

Why Iceberg?

Iceberg is a table format specification that standardizes how tables are represented in cloud object storage. Unlike proprietary formats (Snowflake’s native table storage, BigQuery’s storage) or ecosystem-specific formats (Delta/Databricks), Iceberg is an open standard maintained by the Apache Software Foundation. Any Iceberg-compatible engine can operate on the same tables in cloud storage, rather than being tied to a single vendor’s proprietary storage.

The key advantage: single source of truth across multiple engines. Instead of maintaining separate pipelines and copies for different consumers, all engines can query the same tables in cloud storage via a single metadata catalog.

The cost is operational surface complexity. Iceberg is more than a file format: it adds snapshot and version management, background compaction of the small files that incremental writes leave behind, and a dependency on a separate metadata catalog to track table versions and coordinate writes (Section 1.2). None of this is free — but it’s the price of open, consistent, multi-engine access, and it’s manageable with the right automation and monitoring.

1.2 Decision 2: Snowflake Horizon Catalog as the unified metadata layer

Why Horizon Catalog?

Iceberg specifies the table format but not the metadata catalog. A catalog manages:

• Table versioning and snapshots
• Concurrent write coordination (preventing corrupted writes)
• Access control and row-level policies
• Lineage tracking (which tables feed which)

Crucially, the catalog is also what turns Iceberg’s open format into open, multi-engine access. Horizon Catalog exposes our Snowflake-managed Iceberg tables over the open Iceberg REST protocol, so external engines such as Spark, Flink, Trino, and DuckDB can both read and write the same tables. Horizon issues temporary, scoped storage credentials based on Snowflake roles, allowing each engine to access authorized data without separately managing long-lived cloud storage credentials. In other words, it is Iceberg (the open format) and Horizon (an open catalog) together that make cross-engine read/write possible.

The open data plane is matched by a single, consolidated control plane. All catalog responsibilities – plus operations like compaction – live in Snowflake, giving us unified, centralized governance alongside full data openness.

1.3 Data architecture: Bronze, Silver, and Dynamic Tables

Our implementation organizes the Iceberg tables into layers:

Bronze Layer: Raw CDC streams from PostgreSQL, one table per source table. Bronze tables are append-only and contain no transformations. Each record includes the full CDC payload and source metadata. Because Bronze tables are append-only and rarely queried directly, compaction is less critical (though still beneficial for storage efficiency).

Silver Layer: Business-logic-ready tables derived from Bronze. Silver tables use MERGE operations to maintain current state, with merge keys defined by the business logic (e.g., company ID + record ID).

Dynamic Tables: Derived views that require complex transformations (joins across multiple tables, flattening nested properties, aggregations). These are Iceberg DYNAMIC tables, which automatically refresh on a schedule and maintain lineage within Snowflake’s catalog.

Performance characteristics

• Ingestion latency: Ingestion is event-driven rather than scheduled. Datastream streams row-level changes from the source PostgreSQL tables (CDC), and Snowpipe auto-ingest loads them into the Bronze tables as soon as the change files land in object storage — keeping Bronze near real-time (seconds to low minutes from source commit). The current-state Silver tables reflect those changes after the next MERGE refresh, which runs every 5 minutes, so end-to-end freshness to Silver is on the order of a few minutes.
• Read latency: Iceberg’s metadata layer adds ~10-50ms of overhead per query (reading manifest files to resolve the current table version). For typical analytical queries (seconds to minutes), this is negligible. For sub-second OLTP queries, it may matter.
• Write latency: MERGE operations on Iceberg tables introduce metadata write operations (updating snapshots). We mitigated this via Gen2 warehouses (Section 2.1).
• Compaction latency: Automated compaction runs in the background but can cause temporary query slowdowns on large tables.

2. Optimization patterns

We applied a set of targeted optimizations to address the specific bottlenecks we hit in production. Together they cut steady-state daily spend by roughly 60% while preserving data freshness and query latency. The following image summarizes the daily spend reduction that we achieved over a period of 3 months:

Each optimization is covered in turn below.

2.1 Faster DML for high-volume writes (Gen2 warehouses)

Problem: Traditional Snowflake DML (INSERT, UPDATE, DELETE, MERGE) on Standard (“Gen1”) warehouses is tuned for transactional workloads — modest row counts, strong ACID guarantees, immediate commit. Our use case is different: MERGE operations that touch millions of rows (bulk upsert of CDC data), where transactional guarantees matter but latency is critical. A traditional MERGE on a large table is expensive: it scans the target to find matches, then rewrites entire micro-partitions even when only a few rows in them change (write amplification), plus the associated metadata work.

Solution: We run these MERGE workloads on Snowflake Gen2 warehouses — a newer warehouse generation you explicitly opt into, not an execution mode Snowflake auto-selects per query. Gen2 speeds up DELETE/UPDATE/MERGE primarily by:

• Deleting files alongside micro-partitions: when only part of a micro-partition changes, Gen2 records the change with a delete file instead of rewriting the whole partition — cutting write amplification.
• Faster scans and joins for the match phase of large MERGE/UPDATE/DELETE statements.

Results: Snowflake reports Gen2 DML at roughly 4–5x faster than Standard warehouses. In our production lakehouse, our highest-volume MERGE task shows the effect clearly: before the Gen2 switch, its run time averaged ~10–15 minutes and spiked to ~40+ minutes; after the switch, it dropped to a stable ~1–2 minutes, with peaks under ~3 minutes. That’s roughly a 90% reduction in task run time — and just as importantly, the wild run-to-run variance collapsed, making refreshes predictable.

Implementation notes:

• Gen2 is a per-warehouse setting, not a per-query optimization.
• Gen2 bills at a higher per-second rate and the gains are largest on bulk MERGE/UPDATE/DELETE. Light and ad-hoc workloads are best kept in Standard warehouses where the higher rate isn’t justified.
• Gen2 availability can vary by region and warehouse size.
• Monitor task duration to confirm the speedup and catch regressions (e.g., a task accidentally routed back to a Standard warehouse).

2.2 Removing clustering (when an “obvious” optimization backfires)

Problem: Clustering is usually a safe win: physically co-locating rows by a frequently-filtered key (e.g., company ID) improves partition pruning and speeds up analytical scans. We initially clustered our bronze and silver tables on exactly that intuition — and it backfired.

The cause was our ingestion pattern. Every CDC cycle lands a fresh batch of changes that must be written into the bronze and silver tables, and those batched writes land rows in new micro-partitions that don’t respect the clustering key. In other words, every cycle actively un-clusters the tables. Snowflake’s Automatic Clustering service then kicks in to re-sort the data back into clustering order — continuously, because the next CDC cycle immediately undoes its work.

The result was a treadmill: Automatic Clustering could never keep up with the rate of incoming writes, and the constant re-clustering compute caused costs to balloon — all to maintain an ordering that the next batch would disturb again, and that our query patterns weren’t benefiting from enough to justify.

Solution: We turned off Automatic Clustering on all bronze and silver tables. Ingestion writes no longer fight a background re-sort, the recurring Automatic Clustering spend disappeared, and query performance did not meaningfully degrade for our access patterns.

Implementation notes:

• Before removing clustering, confirm that queries don’t rely on the clustering key for pruning by checking query profiles and comparing latency before/after.
• Replace clustering with partitioning and query design (plus the natural time/write ordering CDC already produces) for whatever pruning you still need.
• Track SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY to confirm the reclustering spend actually drops, and to catch any table where clustering gets re-enabled.

Results: Removing clustering eliminated a large consistent Automatic Clustering cost line with no meaningful regression in query latency for our workloads.

2.3 Serverless tasks for workload isolation

Problem: Before serverless tasks, all scheduled jobs (MERGE operations, dynamic table refreshes, maintenance tasks) ran on a single shared warehouse. This created:

1. Contention: When two jobs ran simultaneously, they competed for the same warehouse compute, causing unpredictable latency.
2. Over-provisioning: We sized the warehouse for peak load (all jobs running at once). Most of the time, it was idle, wasting credits.
3. Opaque resource attribution: If the warehouse was slow, it was unclear which job was to blame.

Solution: Snowflake serverless tasks decouple jobs from user-managed warehouses. Instead of “run on warehouse X,” a serverless task runs on Snowflake-managed compute: Snowflake provisions, right-sizes, and tears down the compute for each run, billing serverless credits for the time actually used. You can give it a starting-size hint via USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE, and Snowflake adapts from there based on the task’s history.

Implementation notes:

• Serverless bills at a higher per-second rate, so near-constant workloads can be cheaper on a dedicated warehouse.
• Expect per-run spin-up overhead (and slower cold starts after a long idle); for very high-frequency tasks (~every minute) this adds meaningful latency and cost.
• Set USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE from the task’s historical needs; Snowflake adapts from there.
• Monitor per-task credit usage and duration to catch cost regressions. Execution traces are less granular than warehouse query profiles, making some performance troubleshooting harder.

Results: Serverless tasks eliminated warehouse contention entirely. Because Gen2 DML, removing clustering, and serverless tasks all rolled out during overlapping periods, we measure their impact together: steady-state daily compute spend fell ~26% after these three optimizations.

The following is a point-in-time snapshot of the transition from shared warehouse (green) to serverless compute (yellow) – there is a sharp drop in hourly compute credits as tasks were switched over.

2.4 Direct Iceberg serving for data sharing

Problem: Our data sharing requirements involved publishing subsets of the lakehouse (filtered by company, compliance status, etc.) to customers and partners. The traditional approach:

1. Create a dynamic table that reads from Silver Iceberg tables, applies business logic (filtering, joins), and materializes a view.
2. Refresh the dynamic table on a schedule (e.g., every 5 minutes).
3. Publish the view via Snowflake Secure Data Shares and Listings.

This works, but each dynamic table incurs refresh overhead (~30 seconds of compute per refresh). With multiple views to share, this becomes a steady compute cost.

Solution: For simpler views — those expressible as a secure view with row-level access policies, without complex SQL — we skip the dynamic table and serve directly from the Silver Iceberg tables by defining a secure view over the Silver table (with its row-level access policies) and publishing it via Listings. Because the view is computed on-read rather than materialized, there is no refresh job to schedule or monitor, readers always see the most recent state, and we incur compute only when the view is actually queried — no standing refresh overhead and no intermediate storage.

Implementation notes:

• Best for simple views (filtering + row-level policies); complex transformations still need dynamic tables — so use a hybrid approach: materialize complex views, serve simple ones directly from Iceberg.
• On-read means each query recomputes the join/filter, so for large tables or many simultaneous consumers a pre-materialized view can be faster and cheaper; test with multiple consumers to understand latency.
• Audit row-level access policies carefully — a misconfigured policy can leak data.

Results: Serving data shares directly from Iceberg (instead of materialized dynamic tables) drove spend down further with a ~46% reduction on compute spend, bringing the cumulative reduction to ~60% below baseline while maintaining real-time data freshness for simple views.

3. Operational considerations

3.1 Monitoring and alerting

• Task duration — alert when a Snowflake task’s execution time exceeds its historical p95 by >20% (early sign of a regression).
• Task failures — alert on any failure; usually indicates a schema change or data-quality issue.
• Compute cost — track daily warehouse and serverless credit usage and alert on upward trends.
• Table growth — watch Bronze/Silver sizes; uncontrolled growth signals a data-quality or retention problem.

3.2 Maintenance

Snowflake automates Iceberg maintenance (compaction, snapshot expiration, metadata cleanup), but you should still:

• Review compaction periodically, and tighten it if small files accumulate.
• Set snapshot retention (e.g., 30 days) so old snapshots don’t pile up storage.
• Test time-travel queries so you can actually recover from data issues.

3.3 Scaling challenges

As data volume grows, three costs tend to creep up, and each has a straightforward mitigation:

• Metadata overhead grows. Larger tables accumulate more manifests and snapshots, so queries slow down as metadata reads start to dominate. A common mitigation is to partition tables on a low-cardinality key (for example, by date): it prunes whole partitions at query time, and because partitioning is applied at write time it avoids the continuous reclustering cost described in Section 2.4.
• Compaction gets more expensive. More incremental writes mean more small files to compact. Scheduling heavier maintenance during off-peak hours keeps compaction from competing with query workloads.
• External-engine load adds up. Many concurrent Spark or Trino queries against the tables add metadata lookups and network overhead. Where possible, serving consumers through data shares rather than direct catalog access keeps that load off the core tables.

4. Lessons learned

1. Open formats require operational discipline: Iceberg is powerful, but compaction, snapshot retention, and metadata management require planning. Don’t assume automation is perfect.
2. Unified metadata is valuable: Having one catalog (Horizon) instead of multiple catalogs per engine eliminates synchronization headaches and makes access control consistent.
3. Cost visibility is essential: Serverless tasks forced us to think about cost per operation. This transparency helped us optimize more aggressively.
4. Faster DML is a big win, but not magic: It requires appropriate warehouse sizing and query structure. Monitor to ensure it’s being used.
5. Data freshness vs. cost: Direct Iceberg serving eliminates refresh overhead, but for use cases where stale data is acceptable, materialization would be cheaper and faster. There’s no one-size-fits-all answer.

5. Conclusion

Building a production lakehouse requires balancing multiple concerns: consistency (ACID), performance (query latency), cost, and operational simplicity. We chose:

• Iceberg for the table format, for openness and consistency across engines.
• Horizon Catalog as the unified metadata layer, for Snowflake integration and fine-grained access control.
• Specific optimization patterns (Faster DML, serverless tasks, direct Iceberg serving, no clustering) to address specific bottlenecks.

The result is a lakehouse that serves internal analytics and external consumers from a single source of truth, with lower operational overhead and cost than our previous multi-pipeline approach.

This design is not universally optimal: simpler use cases — a single warehouse, no external consumers — may not justify Iceberg’s added complexity. But for organizations that need a unified, scalable, multi-engine lakehouse, it has proven effective. External processing engines can still be used where appropriate, while Snowflake remains the unified governance and interoperability layer over open data.

---

Ironclad is not a law firm, and this post does not constitute or contain legal advice. To evaluate the accuracy, sufficiency, or reliability of the ideas and guidance reflected here, or the applicability of these materials to your business, you should consult with a licensed attorney.