External Tables & Iceberg: Multi-Cloud Data Access

In modern data architectures, data often resides in multi-cloud data lakes (AWS S3, Azure Blob, GCS). Snowflake's external tables allow you to query that data directly without loading it into Snowflake storage. With the rise of open table formats like Apache Iceberg, you can now combine the flexibility of Iceberg's ACID transactions and schema evolution with Snowflake's powerful query engine. This tutorial dives deep into creating and querying Snowflake external tables on Iceberg tables, covering multi-cloud access patterns, production debugging, and best practices. You'll learn how to set up external tables, handle partitioning, and avoid common pitfalls. By the end, you'll be able to build a cross-cloud data lake that can be queried by Snowflake, Spark, Trino, and more.

What are External Tables in Snowflake?

External tables in Snowflake allow you to query data stored in external cloud storage (AWS S3, Azure Blob, GCS) as if it were a regular table. The data remains in the external location, and Snowflake reads it on the fly. This is useful for data lakes where you want to avoid data movement. External tables are read-only and support partitioning, file formats like Parquet, ORC, CSV, JSON, and now Apache Iceberg. They are defined using a storage integration and a file format. For Iceberg, Snowflake can read the Iceberg metadata to understand the table schema, partitions, and snapshots.

-- Create a storage integration (one-time setup)
CREATE STORAGE INTEGRATION my_s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/my-role'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-bucket/iceberg/');

-- Create external stage pointing to the Iceberg metadata location
CREATE STAGE my_iceberg_stage
  STORAGE_INTEGRATION = my_s3_int
  URL = 's3://my-bucket/iceberg/';

-- Create external table on Iceberg table
CREATE OR REPLACE EXTERNAL TABLE my_iceberg_ext_table
  WITH LOCATION = @my_iceberg_stage
  AUTO_REFRESH = FALSE
  FILE_FORMAT = (TYPE = PARQUET);

-- Note: For Iceberg, Snowflake reads the metadata from the stage location.
-- The Iceberg table must be written by an Iceberg-compatible engine (e.g., Spark, Flink).

Setting Up an Iceberg Table in Snowflake

Snowflake can also create and manage Iceberg tables directly (Snowflake-managed Iceberg tables). This allows you to use Snowflake's compute to write Iceberg tables that can be read by other engines. To create an Iceberg table, you need an external volume (a storage location) and specify the Iceberg table format. Here's how to create a Snowflake-managed Iceberg table and then query it via an external table (or directly).

-- Create an external volume (requires ACCOUNTADMIN)
CREATE OR REPLACE EXTERNAL VOLUME my_iceberg_vol
  STORAGE_LOCATIONS = (
    (
      NAME = 'my-s3-location'
      STORAGE_PROVIDER = 'S3'
      STORAGE_BASE_URL = 's3://my-bucket/iceberg/'
      STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/my-role'
    )
  );

-- Create a Snowflake-managed Iceberg table
CREATE OR REPLACE ICEBERG TABLE my_iceberg_table (
  id INT,
  name STRING,
  dt DATE
)
  EXTERNAL_VOLUME = 'my_iceberg_vol'
  CATALOG = 'SNOWFLAKE'
  BASE_LOCATION = 'my_iceberg_table/';

-- Insert data (Snowflake writes Iceberg files)
INSERT INTO my_iceberg_table VALUES (1, 'Alice', '2025-01-01'), (2, 'Bob', '2025-01-02');

-- Query directly
SELECT * FROM my_iceberg_table;

-- Now create an external table on the same location (for cross-engine access)
CREATE OR REPLACE EXTERNAL TABLE my_iceberg_ext
  WITH LOCATION = @my_iceberg_stage
  FILE_FORMAT = (TYPE = PARQUET);

-- Note: The external table will see the same data as the managed table.

Multi-Cloud Data Access with Iceberg

One of the biggest advantages of Iceberg is that it is cloud-agnostic. You can have an Iceberg table stored in AWS S3, and query it from Snowflake running on Azure, or from Spark on GCP. Snowflake's external tables can read Iceberg tables from any cloud storage, as long as network access is configured. This enables multi-cloud data lakes. For example, you might have data produced in AWS, processed in Azure, and consumed in GCP. With Iceberg, all engines see the same consistent snapshot. To set up multi-cloud access, you need to create storage integrations for each cloud provider and point the external table to the Iceberg metadata location. Snowflake will read the metadata and data files from the remote cloud.

-- Assume Iceberg table is in AWS S3, but Snowflake is on Azure
-- Create storage integration for AWS
CREATE STORAGE INTEGRATION aws_s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::...'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-bucket/iceberg/');

-- Create stage
CREATE STAGE aws_iceberg_stage
  STORAGE_INTEGRATION = aws_s3_int
  URL = 's3://my-bucket/iceberg/';

-- Create external table
CREATE EXTERNAL TABLE cross_cloud_table
  WITH LOCATION = @aws_iceberg_stage
  FILE_FORMAT = (TYPE = PARQUET);

-- Query across clouds
SELECT region, COUNT(*) FROM cross_cloud_table GROUP BY region;

-- If the Iceberg table uses a catalog (e.g., AWS Glue), you can integrate with Snowflake's Iceberg catalog integration.

Partitioning and Clustering in Iceberg External Tables

Iceberg tables support partitioning (by a column, e.g., date) and clustering (sorting within partitions). Snowflake's external tables can leverage Iceberg's partition metadata to prune partitions during queries. This is critical for performance. When you create an external table on an Iceberg table, Snowflake reads the partition spec from the Iceberg metadata. You don't need to specify PARTITION BY in the external table definition. However, to get the best performance, ensure your queries filter on partition columns. You can also use clustering keys in the Iceberg table to further optimize scans.

-- Assume Iceberg table partitioned by dt (date)
-- Query with partition filter
SELECT * FROM my_iceberg_ext_table WHERE dt = '2025-01-01';

-- Check query profile to see partition pruning
EXPLAIN SELECT * FROM my_iceberg_ext_table WHERE dt = '2025-01-01';

-- Output will show "Partitions Pruned"
-- You can also see partition information
SHOW PARTITIONS IN EXTERNAL TABLE my_iceberg_ext_table;

Schema Evolution and Time Travel

Iceberg supports schema evolution: you can add, drop, or rename columns without rewriting data. Snowflake external tables on Iceberg automatically detect schema changes after a REFRESH. However, the external table definition must be updated if you want to query new columns. You can use ALTER EXTERNAL TABLE ... ADD COLUMN to add columns. Iceberg also supports time travel: you can query a specific snapshot ID or timestamp. Snowflake external tables do not directly support time travel; you need to use the Iceberg table's snapshot metadata. For Snowflake-managed Iceberg tables, you can use AT (TIMESTAMP => ...) syntax.

-- Add a new column to the Iceberg table (using Spark, for example)
-- ALTER TABLE my_iceberg_table ADD COLUMN email STRING;

-- In Snowflake, refresh the external table
ALTER EXTERNAL TABLE my_iceberg_ext_table REFRESH;

-- Add the column to the external table definition
ALTER EXTERNAL TABLE my_iceberg_ext_table ADD COLUMN email STRING;

-- Now query the new column
SELECT id, email FROM my_iceberg_ext_table;

-- For time travel on Snowflake-managed Iceberg table:
SELECT * FROM my_iceberg_table AT (TIMESTAMP => '2025-01-01 00:00:00'::TIMESTAMP);

Performance Optimization and Best Practices

To get the best performance from Iceberg external tables, follow these practices: 1) Use partitioning and clustering in the Iceberg table. 2) Filter on partition columns. 3) Use column pruning (select only needed columns). 4) Consider materialized views if queries are repeated. 5) Monitor query profiles for full scans. 6) Use AUTO_REFRESH or scheduled tasks to keep metadata fresh. 7) For large tables, consider using Snowflake's search optimization service (not available for external tables yet). 8) Use the Iceberg REST catalog integration for better metadata management.

-- Create a materialized view on external table (if supported)
CREATE MATERIALIZED VIEW mv_iceberg AS
SELECT dt, COUNT(*) as cnt
FROM my_iceberg_ext_table
GROUP BY dt;

-- Query the materialized view
SELECT * FROM mv_iceberg WHERE dt = '2025-01-01';

-- Use clustering on the Iceberg table (if managed by Snowflake)
ALTER ICEBERG TABLE my_iceberg_table CLUSTER BY (dt);

-- For external tables, clustering is not applicable; rely on Iceberg partitioning.