Iceberg in Data Pipelines (Loading, Transforming, Serving)
Apache Iceberg tables act exactly like standard Snowflake tables from a SQL perspective. This means you can seamlessly integrate them into your existing data pipelines, dbt models, and Medallion architectures while keeping the underlying data stored as open Parquet files in your own cloud storage.
Topics Covered
- Iceberg in the Medallion Architecture
- Loading Data into Iceberg Tables
- Transforming Iceberg Data (with Dynamic Tables)
- Migrating from Native to Iceberg
Iceberg in the Medallion Architecture
Iceberg is the perfect format for a "Lakehouse" Medallion Architecture:
- Bronze (Raw): Raw JSON or Parquet files ingested via Snowpipe into an Iceberg table.
- Silver (Cleansed): Deduplicated and filtered data stored as an Iceberg table.
- Gold (Curated): Aggregated business-level data. Depending on performance needs, Gold can be an Iceberg table (for interoperability with Spark) or a Native Snowflake table (if you need features like Search Optimization or Materialized Views).
Hands-On Demonstration: An Iceberg Pipeline
In this lab, we will build a mini data pipeline. We will start with a raw staging table, transform the data into a Silver Iceberg table, and aggregate it into a Gold Iceberg table using Dynamic Tables.
1. Database & Schema Setup
Copy and paste the following code into your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_LAKEHOUSE_DB;
USE DATABASE BEEMS_LAKEHOUSE_DB;
CREATE SCHEMA IF NOT EXISTS PIPELINE_SCHEMA;
USE SCHEMA PIPELINE_SCHEMA;
2. The Bronze Layer (Loading Data)
Normally, you would use COPY INTO to load data from an external stage directly into a Snowflake-managed Iceberg table. For this lab, let's create a Bronze table and insert mock data representing Indian customers and their spending.
(Note: These CREATE ICEBERG TABLE statements assume you have an External Volume named MY_ICEBERG_VOL configured. If not, treat this as conceptual).
-- Create Bronze Iceberg Table
CREATE OR REPLACE ICEBERG TABLE PIPELINE_SCHEMA.BRONZE_TRANSACTIONS (
RAW_PAYLOAD VARIANT,
INGESTED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
)
CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'MY_ICEBERG_VOL'
BASE_LOCATION = 'bronze_layer/';
-- Insert Raw Data
INSERT INTO PIPELINE_SCHEMA.BRONZE_TRANSACTIONS (RAW_PAYLOAD)
SELECT PARSE_JSON('{"txn_id":"T1", "user":"Arjun Nair", "state":"Kerala", "amount": 500}') UNION ALL
SELECT PARSE_JSON('{"txn_id":"T2", "user":"Neha Reddy", "state":"Telangana", "amount": 1200}') UNION ALL
SELECT PARSE_JSON('{"txn_id":"T3", "user":"Siddharth Patel", "state":"Gujarat", "amount": -50}') UNION ALL -- Bad data (negative amount)
SELECT PARSE_JSON('{"txn_id":"T4", "user":"Megha Patil", "state":"Maharashtra", "amount": 800}');
3. The Silver Layer (Transforming Iceberg Data)
We will use standard SQL (e.g., via a Snowflake Task or dbt model) to transform the Bronze data into Silver. We will flatten the JSON, enforce data types, and filter out invalid data (like negative amounts).
-- Create Silver Iceberg Table
CREATE OR REPLACE ICEBERG TABLE PIPELINE_SCHEMA.SILVER_TRANSACTIONS (
TXN_ID STRING,
USER_NAME STRING,
STATE STRING,
AMOUNT NUMBER(10,2),
PROCESSED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
)
CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'MY_ICEBERG_VOL'
BASE_LOCATION = 'silver_layer/';
-- Transform and Load (T&L)
INSERT INTO PIPELINE_SCHEMA.SILVER_TRANSACTIONS (TXN_ID, USER_NAME, STATE, AMOUNT)
SELECT
RAW_PAYLOAD:txn_id::STRING,
RAW_PAYLOAD:user::STRING,
RAW_PAYLOAD:state::STRING,
RAW_PAYLOAD:amount::NUMBER(10,2)
FROM PIPELINE_SCHEMA.BRONZE_TRANSACTIONS
WHERE RAW_PAYLOAD:amount::NUMBER > 0; -- Filtering out the negative amount
SELECT * FROM PIPELINE_SCHEMA.SILVER_TRANSACTIONS;
4. The Gold Layer (Iceberg + Dynamic Tables)
Yes, you can create a Dynamic Table that outputs its data as an Iceberg table! This is incredible for building Gold layer aggregations that update automatically while still saving the results to your own S3 bucket.
-- Create Gold Dynamic Iceberg Table
CREATE OR REPLACE DYNAMIC ICEBERG TABLE PIPELINE_SCHEMA.GOLD_STATE_REVENUE
TARGET_LAG = '1 hour'
WAREHOUSE = COMPUTE_WH
CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'MY_ICEBERG_VOL'
BASE_LOCATION = 'gold_layer/'
AS
SELECT
STATE,
COUNT(TXN_ID) AS TOTAL_TXNS,
SUM(AMOUNT) AS TOTAL_REVENUE
FROM PIPELINE_SCHEMA.SILVER_TRANSACTIONS
GROUP BY STATE;
-- Manually refresh for the lab
ALTER DYNAMIC TABLE PIPELINE_SCHEMA.GOLD_STATE_REVENUE REFRESH;
-- BI Tools connect to this table
SELECT * FROM PIPELINE_SCHEMA.GOLD_STATE_REVENUE;
(Reminder: When setting up tasks and dynamic tables, ensure your user/role has sufficient privileges. To verify your current user, execute SELECT CURRENT_USER();)
Migrating from Native Tables to Iceberg
If you have existing native Snowflake tables that you want to convert to Iceberg (perhaps to share the data with Apache Spark), you can't simply ALTER TABLE. You must create a new Iceberg table and insert the data.
-- Pattern for migration
CREATE OR REPLACE ICEBERG TABLE MY_NEW_ICEBERG_TABLE
CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'MY_ICEBERG_VOL'
BASE_LOCATION = 'migrated/'
AS
SELECT * FROM MY_OLD_NATIVE_TABLE;
-- You can then rename them to swap them out
-- ALTER TABLE MY_OLD_NATIVE_TABLE RENAME TO MY_OLD_NATIVE_TABLE_BACKUP;
-- ALTER TABLE MY_NEW_ICEBERG_TABLE RENAME TO MY_OLD_NATIVE_TABLE;
Summary
- Iceberg tables support all standard Snowflake DML operations (
INSERT,UPDATE,DELETE,MERGE). - You can build an entire Medallion Architecture using Snowflake-managed Iceberg tables.
- Dynamic Iceberg Tables allow you to declaratively build continuous pipelines that write open Parquet files to your cloud storage.