Building a Modern Data Pipeline in Snowflake: From Snowpipe to Managed Iceberg Tables with Sync Checks

As data volumes grow and real-time analytics become essential, organizations are turning to open table formats like Apache Iceberg to build flexible, scalable data lakehouse architectures. One of Iceberg’s key strengths is its open data catalog, a shared, standardized metadata layer that tracks table schema, partitions, snapshots, and other versions. This open catalog provides a single source of truth for table metadata, ensuring all engines see the same schema and table state (snapshots) with ACID transactional guarantees. In practice, the catalog allows multiple systems to operate on a single copy of data, so different query engines (Snowflake, Spark, Trino, Presto, e6data, etc.) can all query the exact same data and get consistent results. Iceberg’s design brings SQL table reliability (ACID transactions, schema evolution, time travel) to data lakes, enabling multi-engine interoperability without vendor lock-in.

Snowflake’s recent support for managed Iceberg tables leverages this open catalog to provide seamless cross-platform querying. A Snowflake-managed Iceberg table is an Iceberg table where Snowflake manages all writes and metadata, but the data is stored in your cloud storage in an open format (Parquet files with Iceberg metadata). In other words, Snowflake’s powerful SQL engine can insert and update data in the table, and other tools can read the same table via the open catalog. Snowflake writes data to cloud object storage (e.g., Amazon S3) as Parquet and stores the table’s metadata (snapshots, manifests, schema definitions) in the Iceberg format on that storage. This means you can use Snowflake for its performance and ease-of-use, alongside other processing engines on the same data, without data duplication or complex ETL transfers.

The Iceberg metadata acts as a neutral interface for any compatible engine to see the table’s latest snapshot and schema. Snowflake’s product team emphasizes that this approach gives customers the freedom to “mix and match multiple query engines, in any fashion, without lock-in.” In short, Snowflake-managed Iceberg tables enable agility, strong governance, and easy data sharing across diverse analytic tools on a unified dataset, all while avoiding the “new lock-in layer” of proprietary catalogs.

In this blog, we walk through building a modern data pipeline in Snowflake that ingests data via Snowpipe, transforms it, writes into a Snowflake-managed Iceberg table, and performs data quality checks to ensure everything stays in sync. It does all this while benefiting from Apache Iceberg’s open data catalog and multi-engine compatibility.

Overall Architecture - Data Pipeline

Here are the SQL statements to create the main tables used in the pipeline:

‍

Real-World Example: Daily Batch Processing for E-Commerce Orders

Imagine you run a data platform for an e-commerce company. Every night at 2 AM, new JSON files containing the latest orders are dropped into an Amazon S3 bucket. Your goals are to:

• Automatically ingest the daily order data into Snowflake as soon as it arrives.

• Apply business rules via transformations (e.g., remove or flag invalid orders with negative amounts, standardize date formats).

• Store the cleaned data in a Snowflake-managed Iceberg table for analytics and multi-engine access.

• Perform data quality checks to ensure the Iceberg table is fully synchronized with the curated source data.

• Automate the entire pipeline to run daily without manual intervention.

Daily Pipeline Flow

Step 1: Data Lands in S3

At 2 AM, a JSON file like this lands in s3://ecom-data/orders/2025-07-08/orders.json:

Step 2: Snowpipe Auto-Loads to Landing Table

‍

Snowpipe automatically ingests new files from S3 into the landing_orders table near real-time, enabling continuous data arrival.

Steps 3 and 4: Transformation and Iceberg Load (Encapsulated in Procedure)

To simplify management and ensure atomicity, the transformation and Iceberg load are encapsulated in a stored procedure transform_orders():

‍

Note: The procedure assumes that Snowpipe has finished loading the daily batch before transformation starts to avoid deleting records prematurely.

Step 5: Data Quality Check

Ensuring the data is fully synchronized between the curated table and the Iceberg table is critical for reliable analytics.

You can run row count and difference checks like this:

‍

For more robust validation, consider extending this with checksum or hash comparisons.

Step 6: Automate with a Daily Task

Schedule the pipeline to run daily with Snowflake Tasks:

‍

This ensures the entire pipeline runs consistently every day without manual intervention.

To streamline and automate modern data pipelines alongside Snowflake, platforms like Peliqan offer low-code integration flows and orchestration between diverse sources, transformation logic, and quality checks.

Peliqan enables teams to design, schedule, monitor, and troubleshoot complex pipelines using both SQL & Python, integrating seamlessly with Snowflake and other lakehouse targets - reducing engineering overhead and accelerating time-to-value.

‍

Conclusion

By combining Snowpipe for automated ingestion, SQL transformations for data cleaning, and Snowflake-managed Iceberg tables for storage, you can build a modern, scalable data pipeline that offers several key benefits:

• Automated ingestion and transformation: New data is loaded and processed continuously or on schedule without manual intervention, keeping datasets fresh.

• Clean, curated data for analytics: The pipeline enforces business rules and data quality checks during the transform step, producing high-quality, trusted data in a curated format.

• Access to Apache Iceberg’s open data catalog: The managed Iceberg table leverages Iceberg’s open metadata layer (the catalog of table snapshots, schema, partitions, etc.), which is shared across engines.

• Seamless multi-engine interoperability: Because the data is in Iceberg format (Parquet files + Iceberg metadata) on cloud storage, you can query it not only from Snowflake but also from Spark, Trino, Presto, e6data, or any other engine that supports Iceberg

• End-to-end data quality checks: Built-in quality checks (e.g., using Snowflake’s DMFs or custom SQL queries) at each stage of the pipeline ensure that issues are caught early.

Snowflake’s managed Iceberg tables effectively unlock the benefits of open data formats and open metadata catalogs while retaining Snowflake’s ease-of-use, performance, and governance features. This aids you in building next-generation lakehouse architectures that support diverse workloads and tools on a unified platform. In other words, you get the freedom of an open data lake (no lock-in, multi-engine access) combined with the convenience and reliability of Snowflake’s Data Cloud in one solution.

‍