Managed Iceberg Tables in Snowflake
There are two flavors of Iceberg tables in Snowflake: Managed and External.
This module focuses on Managed Iceberg Tables. With a Managed Iceberg table, Snowflake acts as the catalog and handles all data writing, compaction, and metadata management. The only difference between a Managed Iceberg table and a standard Snowflake native table is that the underlying data physically lives in your own cloud storage bucket (S3, GCS, Azure) in open Apache Parquet format.
Topics Covered
- Managed vs Native Table Comparison
- Creating Managed Iceberg Tables
- DML Operations and Querying
- Performance Characteristics
Managed vs Native Table Comparison
| Feature | Native Snowflake Table | Managed Iceberg Table |
|---|---|---|
| Storage Location | Snowflake's internal proprietary storage | Your own external cloud storage bucket |
| Data Format | Proprietary micro-partitions | Open-source Apache Parquet |
| Compute Engine | Only Snowflake | Snowflake, plus Spark, Trino, Flink (via external read) |
| Performance | Maximum performance (Search Optimization, etc.) | High performance, slightly higher latency than native |
| DML Support | Full (INSERT, UPDATE, DELETE, MERGE) | Full (INSERT, UPDATE, DELETE, MERGE) |
Hands-On Demonstration: Creating and Querying Managed Iceberg Tables
Let's build a managed Iceberg table, insert data into it, and perform standard DML operations. We'll use mock HR data featuring employees from across India.
1. Database & Schema Setup
Copy and paste the following code into your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_DATALAKE_DB;
USE DATABASE BEEMS_DATALAKE_DB;
CREATE SCHEMA IF NOT EXISTS HR_ICEBERG_SCHEMA;
USE SCHEMA HR_ICEBERG_SCHEMA;
2. Creating a Managed Iceberg Table
To create a managed Iceberg table, you must specify CATALOG = 'SNOWFLAKE' and point it to an EXTERNAL_VOLUME (which represents your S3/GCS/Azure bucket).
(Note: This assumes an External Volume named MY_ICEBERG_VOL exists. If you are practicing in a fresh sandbox without cloud storage configured, treat this syntax as conceptual. To check if you have an external volume, you can run SHOW EXTERNAL VOLUMES;)
CREATE OR REPLACE ICEBERG TABLE HR_ICEBERG_SCHEMA.EMPLOYEE_DIRECTORY (
EMP_ID INT,
EMP_NAME STRING,
STATE STRING,
DEPARTMENT STRING,
HIRED_DATE DATE
)
CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'MY_ICEBERG_VOL'
BASE_LOCATION = 'hr/employees/';
Note: BASE_LOCATION is the folder path inside your bucket where Snowflake will write the Parquet files.
3. Writing Data to the Iceberg Table
You insert data into a Managed Iceberg table exactly like you would a native table. Behind the scenes, Snowflake's virtual warehouse converts the data into Parquet files and writes them to your S3/GCS bucket.
INSERT INTO HR_ICEBERG_SCHEMA.EMPLOYEE_DIRECTORY VALUES
(101, 'Karthik Natarajan', 'Tamil Nadu', 'Engineering', '2021-05-15'),
(102, 'Priya Banerjee', 'West Bengal', 'Marketing', '2022-01-10'),
(103, 'Aarav Desai', 'Gujarat', 'Sales', '2020-11-01'),
(104, 'Ananya Sharma', 'Delhi', 'HR', '2023-03-20'),
(105, 'Vikram Singh', 'Punjab', 'Engineering', '2022-08-05');
4. Querying and DML Operations
Querying the data is seamless.
SELECT * FROM HR_ICEBERG_SCHEMA.EMPLOYEE_DIRECTORY WHERE DEPARTMENT = 'Engineering';
Because Snowflake manages this table, you have full support for all DML operations. Let's run an UPDATE and a DELETE.
-- Update an employee's department
UPDATE HR_ICEBERG_SCHEMA.EMPLOYEE_DIRECTORY
SET DEPARTMENT = 'Product Management'
WHERE EMP_NAME = 'Aarav Desai';
-- Delete an employee who left the company
DELETE FROM HR_ICEBERG_SCHEMA.EMPLOYEE_DIRECTORY
WHERE EMP_ID = 104;
-- Verify the changes
SELECT * FROM HR_ICEBERG_SCHEMA.EMPLOYEE_DIRECTORY;
When you ran that UPDATE and DELETE, Snowflake automatically wrote new Parquet files and updated the Iceberg metadata snapshot to reflect the changes, completely abstracting away the complexity of managing a data lake!
(Reminder: The user executing these queries becomes the owner of the table. To check your current username, run SELECT CURRENT_USER();)
Performance Characteristics
Managed Iceberg tables provide excellent performance, often rivaling native tables.
- Snowflake caches the Iceberg metadata and the Parquet files on the local SSDs of the Virtual Warehouse, just like native micro-partitions.
- Because Snowflake wrote the Parquet files, it perfectly aligned the metadata statistics (min/max values), allowing the query engine to heavily prune files and scan data efficiently.
- Limitation: As of the current release, some advanced native performance features like Search Optimization Service and Query Acceleration Service may not be fully supported on Iceberg tables.
Summary
- Managed Iceberg Tables use Snowflake as the catalog.
- You write, read, and mutate them exactly like standard Snowflake tables.
- The physical data is stored in your own cloud bucket as open Parquet files.
- They offer near-native performance while completely avoiding vendor lock-in for your storage layer.