Converting Snowflake Native Tables to Iceberg: Part 2

Part 2 of 2 — with the pre-conversion decisions covered in part 1, this article covers conversion execution, data validation, cutover and rollback.

Conversion strategy: Side-by-side

Other than for initial tests and proofs of concept, side-by-side conversion is generally the right approach: Create the Iceberg table in a parallel schema, fully validate it while the native table remains live, and swap it in via a controlled rename sequence.

The conversion follows five sequential steps:

1. Pre-conversion: - Capture grants and policies - Plan and line up smoke test assets - Freeze writes
2. Conversion - Create iceberg table (i.e. the iceberg version of the original table being converted) - Copy the data - Restore grants, policies and tags
3. Validation (row counts, HASH_AGG, consumer smoke-test)
4. Cutover (swap the original native table with its iceberg equivalent)
5. Cleanup

Pre-conversion tasks

1. Confirm that you have the tools and scripts needed to smoke-test each table consumer. See “Consumer tool smoke-test” below.
2. Generate and validate scripts to reapply all grants, tags, fine-grained access policies (like masking and row-access) on the table(s) being migrated
3. Remove write permissions on the native table: revoke INSERT, UPDATE, DELETE and TRUNCATE while preserving SELECT (note that if the table owner is used for write, you might need to change the owner or pause the pipelines).
4. Drain all streams on the table(s) being converted and drop them afterwards. A stream’s offset can’t be carried over to the new Iceberg table, so anything left unconsumed at conversion time is lost.
5. Also confirm that the target schema exists (if not the same as the source table) and the role performing the migration has: – OWNERSHIP on source table and target schema (required for cross-schema rename) – CREATE ICEBERG TABLE permissions on the target schema – USAGE on the external volume, databases and schemas involved – If loading data via export and import (option 1), USAGE rights will be needed on the stage – If either schema is a managed access schema, moving a table into it also requires the table owner to own that schema
6. Communicate the start of the maintenance window for the conversion.

Convert the table

Part 1’s Choosing your conversion technique covers which method suits each table, based on its size and data types. In short, option 1 is more efficient, but only supports tables whose columns are numeric, string, binary, date or boolean. Use option 2 for everything else.

Option 1: Export / Import (aka Load_Mode)

This is a three-step process where you create the iceberg table, export the data to Parquet on the external volume, then register the files on the Iceberg table using COPY INTO … LOAD_MODE, which is the most efficient method to copy the data.

Step 1: Create the target Iceberg table

CREATE ICEBERG TABLE <target_schema>.<interim_table_name> (
    -- column definitions matching source, with any type adjustments defined in the pre-conversion (covered in part 1 of this series)
)
CATALOG           = 'SNOWFLAKE'
EXTERNAL_VOLUME   = '<external_volume_name>'
BASE_LOCATION     = '<database>/<schema>/<original_table_name>'
[ CLUSTER BY ... ] ;

Note: Setting theBASE_LOCATION as above is optional, just a convenience so that the folder name will match the fully qualified name of the table after conversion. The storage path for the table is not renamed when the table is, so while ommiting BASE_LOCATION works fine, the table will be stored under the “interim table name” permanently afterwards.

Step 2: Export to Parquet

COPY INTO @<stage>/<path>/
FROM    <source_schema>.<table_name>
FILE_FORMAT = (TYPE = PARQUET)
HEADER = TRUE
OVERWRITE = TRUE;

Note: HEADER = TRUE is essential for loading the data in step 3, otherwise all columns are loaded as NULL.

Note: Create a stage pointing to the same location as the external volume

Step 3: Register Parquet files

COPY INTO <target_schema>.<interim_table_name>
FROM @<stage>/<path>/
FILE_FORMAT = (TYPE = PARQUET USE_VECTORIZED_SCANNER = TRUE)
LOAD_MODE = ADD_FILES_COPY
PURGE = TRUE
MATCH_BY_COLUMN_NAME = CASE_SENSITIVE;

Notes:

• LOAD_MODE = ADD_FILES_COPY is not supported for partitioned Iceberg tables. If PARTITION is required, use CREATE ICEBERG TABLE … AS SELECT or LOAD_MODE = FULL_INGEST instead.
• Define Snowflake FLOAT columns as DOUBLE in the target iceberg table.

Option 2: “CREATE TABLE AS SELECT” (CTAS)

CREATE ICEBERG TABLE <target_schema>.<interim_table_name>
    CATALOG         = 'SNOWFLAKE'
    EXTERNAL_VOLUME = 'SNOWFLAKE_MANAGED' / '<external_volume_name>'
    BASE_LOCATION   = '<database>/<schema>/<original_table_name>'
    -- Add PARTITION BY or CLUSTER BY per part 1 decisions
    AS SELECT
      <col1>,
      <col2>,
      <...>
    FROM <source_schema>.<table_name>;

Regardless of the method to create and populate the iceberg table, now is the time to execute all GRANT / policy / tag scripts produced in the pre-conversion step.

Post-conversion validation

Data integrity

I like to use HASH_AGG for validating the copied data — a single checksum over all rows tells you quickly whether everything made it across, which matters when you need to decide fast on progressing with the cutover or rolling back.

WITH source_hash AS (
    SELECT
        COUNT(*)                               AS row_count,
        HASH_AGG(<col1>, <col2>, ..., <colN>) AS hash_agg
    FROM <source_schema>.<table_name>
),
iceberg_hash AS (
    SELECT
        COUNT(*)                               AS row_count,
        HASH_AGG(<col1>, <col2>, ..., <colN>) AS hash_agg
    FROM <target_schema>.<table_name>
)
SELECT
    s.row_count                        AS source_rows,
    i.row_count                        AS iceberg_rows,
    (s.row_count = i.row_count)        AS row_count_match,
    (s.hash_agg  = i.hash_agg)        AS hash_match,
    CASE
        WHEN s.row_count = i.row_count
         AND s.hash_agg  = i.hash_agg  THEN '✅ PASS'
        WHEN s.row_count != i.row_count THEN '❌ FAIL — row count mismatch'
        ELSE                                 '❌ FAIL — data mismatch (same count, different hash)'
    END                                AS validation_result
FROM source_hash s
CROSS JOIN iceberg_hash i;

Notes:

• Always list columns explicitly. Avoid HASH_AGG(*) as it can produce incorrect results if the column order is not the same between the source and iceberg tables.
• HASH_AGG doesn't accept GEOGRAPHY or GEOMETRY columns. Pass them as ST_ASWKB(<col>) instead.
• It is possible, though very unlikely, that two different input tables will produce the same result for HASH_AGG. If you need absolute certainty, you will need to compare the data for equality.

Consumer tool smoke-test

Even if the data is copied correctly, differences in query behaviour, tool compatibility and downstream assumptions can still cause unexpected failures.

Before committing to cutover:

1. Identify a representative sample: queries, pipelines, dashboards, BI reports
2. Execute each against the Iceberg table in target_schema
3. Confirm that each test runs without errors and its results match the source output

Cutover

• Because writes were frozen throughout the conversion, no delta merge is required and we can rename both tables:

ALTER TABLE <source_schema>.<table_name>
    RENAME TO <source_schema>.<table_name>_NATIVE_<DATE>;

ALTER ICEBERG TABLE <target_schema>.<iceberg_table_name>
    RENAME TO <source_schema>.<table_name>;

• Recreate any streams that existed on the native table, this time on the Iceberg table
• Validate that: – All GRANT from the native table confirmed on Iceberg table – Spot-check: confirm roles can SELECT from the Iceberg table – Validate fine-grained access control policies return the same rows/columns as on the native table
• Notify consumers that the maintenance window has finished

Rollback plan

The _NATIVE_<DATE> table is the validated fallback. Reverse the rename sequence and re-grant write privileges to roll back.

• Rename Iceberg back to temp name

ALTER ICEBERG TABLE <source_schema>.<table_name>
     RENAME TO <target_schema>.<iceberg_table_name>;

• Rename native table back to original name

ALTER TABLE <source_schema>.<table_name>_NATIVE_<DATE>
     RENAME TO <source_schema>.<table_name>;

1. Re-grant the write privileges revoked during the freeze (INSERT, UPDATE, DELETE, TRUNCATE). Other grants and policies stay with the native table through the rename.

Cleanup

1. Agree retention window for <table_name>_NATIVE_<DATE> with the data owner
2. Drop <table_name>_NATIVE_<DATE> after agreed retention window

Working with Snowflake Service Delivery

The steps in this guide are fully executable with standard Snowflake SQL, but at scale a manual approach becomes time-consuming and error-prone.

The Snowflake Service Delivery team has the processes and tooling to accelerate the repetitive parts and has done this across enough accounts to know where the edge cases hide.

If you’re planning a conversion at scale or want expert guidance through the process, speak with your Snowflake account team about involving us.

Converting Snowflake Native Tables to Iceberg: Part 2 was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.