7.2 Data Lakehouse Architecture & Apache Iceberg

7.2 Data Lakehouse Architecture & Apache Iceberg

Modern enterprise data architectures increasingly seek to combine the reliability, governance, and analytical performance of a data warehouse with the open format flexibility, multi-engine interoperability, and low-cost economics of a cloud data lake. This unified paradigm is known as the Data Lakehouse.

Historically, organizations maintained bifurcated data estates: high-value curated business intelligence resided in proprietary data warehouses like Snowflake, while raw telemetry, unstructured documents, and petabyte-scale machine learning datasets remained in cloud object storage (Amazon S3, Azure Data Lake Storage Gen2, Google Cloud Storage) queried via distributed engines like Apache Spark or Trino. This bifurcation introduced data silos, synchronization lag, redundant storage costs, and governance fragmentation.

Snowflake solves this challenge through a multi-tiered lakehouse architecture supporting External Stages, Directory Tables, External Tables, and natively integrated Apache Iceberg Tables. For the SnowPro Advanced: Architect exam, you must master the mechanics, catalog governance models, partition strategies, and performance tradeoffs across each lakehouse pattern.

---

Data Lakehouse Architecture: Storage Tiers & Data Formats

Snowflake provides native processing capabilities across all three fundamental data categories:

1. Structured Data: Fixed relational schemas stored in native, proprietary micro-partitions with automated clustering and full DML support.
2. Semi-Structured Data: Flexible schema formats (JSON, Avro, ORC, Parquet, XML) ingested natively into the first-class VARIANT, OBJECT, and ARRAY data types. Snowflake shreds semi-structured data into columnar sub-elements under the hood, delivering relational-like query speeds.
3. Unstructured Data: Arbitrary file formats (PDF, TIFF, audio, video, genomics BAM files, machine learning model weights) stored in internal or external cloud stages, governed by Directory Tables and accessible via scoped URLs in Snowpark Python and SQL.

┌─────────────────────────────────────────────────────────────────────────────────┐
│                     Snowflake Data Lakehouse Storage Tiers                      │
│                                                                                 │
│  ┌─────────────────────────┐  ┌───────────────────────┐  ┌───────────────────┐  │
│  │ Native Snowflake Tables │  │ Apache Iceberg Tables │  │  External Tables  │  │
│  │ (Proprietary Format)    │  │ (Open Parquet Format) │  │ (Read-Only Lake)  │  │
│  │ • Snowflake Storage     │  │ • Customer Cloud S3   │  │ • Customer Storage│  │
│  │ • Micro-Partitions      │  │ • Open Table Standard │  │ • On-the-fly Parse│  │
│  │ • Max Performance       │  │ • Multi-Engine Interop│  │ • Virtual Columns │  │
│  │ • Time Travel+Fail-safe │  │ • Time Travel, No FS  │  │ • High Latency    │  │
│  └─────────────────────────┘  └───────────────────────┘  └───────────────────┘  │
│               ▲                           ▲                        ▲            │
│               └───────────────────────────┼────────────────────────┘            │
│                                           │                                     │
│                        Snowflake Centralized Governance Engine                  │
│               (RBAC, Row Access Policies, Masking, Object Tagging)              │
└─────────────────────────────────────────────────────────────────────────────────┘

Internal vs. External Stages

Stages represent the cloud storage endpoints through which files enter or are referenced by Snowflake:

• Internal Stages: Stored in Snowflake-managed cloud object storage. Internal stages include User Stages (@~), Table Stages (@%table_name), and Named Internal Stages (@my_internal_stage). Files staged internally are protected by 128-bit or 256-bit AES customer-isolated encryption with automated key rotation.
• External Stages: Reference customer-managed cloud object storage buckets (S3, ADLS Gen2, GCS). External stages are secured using Storage Integrations (CREATE STORAGE INTEGRATION), an account-level object that delegates cloud provider authentication to IAM roles and external IDs (AWS) or Service Principals (Azure/GCP). This completely eliminates long-lived access keys and secret tokens from SQL scripts.

Directory Tables for Unstructured Data Governance

A Directory Table is a built-in metadata layer over a stage that indexes and presents the staged files as a queryable relational view:

-- Create an external stage with an auto-refreshing directory table
CREATE OR REPLACE STAGE docs_stage
  URL = 's3://acme-lakehouse-data/legal_contracts/'
  STORAGE_INTEGRATION = s3_storage_int
  DIRECTORY = (
    ENABLE = TRUE
    AUTO_REFRESH = TRUE
  );

When a directory table is enabled, Snowflake automatically tracks staged files and exposes metadata columns:

• RELATIVE_PATH: File path relative to the stage root.
• SIZE: File size in bytes.
• LAST_MODIFIED: Timestamp of last modification.
• FILE_URL: Permanent Snowflake-hosted URL to access the file.

-- Query the directory table metadata directly
SELECT 
    relative_path,
    size,
    last_modified,
    file_url,
    -- Generate a time-limited presigned URL for external applications
    GET_PRESIGNED_URL(@docs_stage, relative_path, 3600) AS presigned_url,
    -- Generate a scoped URL for secure Snowpark Python processing
    BUILD_SCOPED_FILE_URL(@docs_stage, relative_path) AS scoped_url
FROM DIRECTORY(@docs_stage)
WHERE size > 1048576; -- Files larger than 1 MB

External Tables: Architecture, Mechanics & Tradeoffs

An External Table creates a read-only tabular schema directly over data files stored in an external cloud storage stage. External tables allow analysts to query data lake files using standard ANSI SQL without executing COPY INTO to ingest the data into Snowflake micro-partitions.

External Table Mechanics & Virtual Columns

Unlike native tables, an external table does not store micro-partitions inside Snowflake. Instead, when a query runs against an external table, Snowflake's virtual warehouse reaches out to the cloud object store, reads the raw files (Parquet, JSON, CSV), and projects rows dynamically.

In an external table, Snowflake automatically provides a single variant column named VALUE containing the entire record payload, alongside metadata columns:

• VALUE: A VARIANT column representing the parsed record.
• METADATA$FILENAME: The name and path of the source data file.
• METADATA$FILE_ROW_NUMBER: The row index within the source file.

Architects define strongly-typed Virtual Columns by writing expressions that traverse the VALUE column:

-- Create an External Table over Parquet files partitioned by date
CREATE OR REPLACE EXTERNAL TABLE ext_clickstream (
    -- Derived virtual columns with explicit data types
    event_id VARCHAR AS (VALUE:event_id::VARCHAR),
    user_id NUMBER AS (VALUE:user_id::NUMBER),
    event_timestamp TIMESTAMP_NTZ AS (VALUE:event_time::TIMESTAMP_NTZ),
    device_type VARCHAR AS (VALUE:device:type::VARCHAR),
    -- Partition column extracted from the cloud storage path
    event_date DATE AS TO_DATE(SPLIT_PART(METADATA$FILENAME, '/', 3), 'YYYY-MM-DD')
)
PARTITION BY (event_date)
LOCATION = @lakehouse_stage/clickstream/
FILE_FORMAT = (TYPE = PARQUET)
AUTO_REFRESH = TRUE;

Partitioning & Partition Pruning

Because external tables lack Snowflake's native micro-partition metadata (min/max clustering values), query performance is entirely dependent on Partition Pruning based on the file storage path:

• By specifying PARTITION BY (event_date), Snowflake evaluates query WHERE clauses (e.g., WHERE event_date = '2026-09-20') and prunes unneeded file paths before reading them from cloud storage.
• If a query filters on a non-partitioned virtual column (e.g., WHERE user_id = 98124), Snowflake is forced to perform a full scan of every single file in the external stage, resulting in severe I/O bottlenecks and massive compute consumption.

Metadata Caching & Automated Refresh

To avoid listing cloud storage buckets on every query, Snowflake maintains an internal metadata cache that records the list of files, sizes, and partition values associated with the external table:

1. Automated Refresh (AUTO_REFRESH = TRUE): Snowflake configures event notifications (AWS SQS queues, Azure Event Grid subscriptions, GCP PubSub) via a notification integration. As new files land in S3/ADLS/GCS, notifications alert Snowflake, which incrementally updates the metadata cache.
2. Manual Refresh: Administrators can trigger an ad-hoc metadata synchronization via SQL:

   -- Incrementally refresh metadata cache to discover new or dropped files
   ALTER EXTERNAL TABLE ext_clickstream REFRESH;
   
   -- Refresh only a specific sub-path
   ALTER EXTERNAL TABLE ext_clickstream REFRESH 'clickstream/2026/09/';
   

Performance Tradeoffs & Materialized Views

Architectural Pattern: Materialized Views over External Tables: To accelerate frequent analytical queries over external tables without full data ingestion, architects create Materialized Views over external tables. The materialized view reads from the external table but persists standard, clustered Snowflake micro-partitions inside Snowflake. As the external table's metadata cache refreshes, Snowflake's serverless maintenance service automatically synchronizes the materialized view deltas.

Apache Iceberg Tables in Snowflake

While external tables provide a basic window into external files, they suffer from severe limitations: they are read-only, lack transactional consistency (ACID), cannot perform row-level mutations, and provide sub-optimal query performance.

Apache Iceberg is an open-source, high-performance table format for massive analytic datasets. Iceberg brings the reliability and simplicity of SQL tables to open cloud object storage, providing:

• Full ACID Transactions: Atomic commits, serializable isolation, and zero dirty reads.
• Hidden Partitioning: Automatic partition evolution without requiring users to rewrite queries or understand physical bucket paths.
• Schema Evolution: Add, drop, rename, or reorder columns without side effects or table rewrites.
• Time Travel & Snapshots: Inspect table states at specific snapshot IDs or historical timestamps.
• Multi-Engine Interoperability: Apache Spark, Trino, Flink, DuckDB, and Snowflake can all read and write to the same underlying dataset.

Snowflake Iceberg Architecture: Storage & Catalog Separation

In Snowflake, an Iceberg table stores its physical data files (Apache Parquet) and metadata files (manifest files, manifest lists, and metadata JSON files) in customer-managed cloud storage. Compute, query execution, caching, and governance are provided by Snowflake.

                                  Query Clients
                      (Snowflake, Spark, Trino, DuckDB)
                                     │
                                     ▼
               Iceberg Catalog Layer (Metadata Management)
       ┌─────────────────────────────┴─────────────────────────────┐
       ▼                                                           ▼
┌───────────────────────────────┐               ┌───────────────────────────────┐
│ Snowflake-Managed Catalog     │               │ Externally-Managed Catalog    │
│ • CATALOG = 'SNOWFLAKE'       │               │ • Glue, Open Catalog, REST    │
│ • Snowflake is Source of Truth│               │ • External catalog is Master  │
│ • Full Read-Write DML Allowed │               │ • Read; writes via REST catalog│
└───────────────────────────────┘               └───────────────────────────────┘
                                     │
                                     ▼
                 External Volume (Cloud IAM & Storage URI)
                                     │
                                     ▼
  ┌─────────────────────────────────────────────────────────────────────────────┐
  │               Customer Cloud Object Storage (S3 / ADLS / GCS)               │
  │                                                                             │
  │   Metadata Layer: [ v1.metadata.json ] ──► [ snap-101.avro (Manifest List)] │
  │                                                      │                      │
  │                                                      ▼                      │
  │   Manifest Layer:                           [ manifest-a.avro ]             │
  │                                                      │                      │
  │                                                      ▼                      │
  │   Data Layer:                               [ data-01.parquet ]             │
  │                                             [ data-02.parquet ]             │
  └─────────────────────────────────────────────────────────────────────────────┘

Snowflake-Managed vs. Externally-Managed Iceberg Tables

A central focus of the ARA-C01 exam is distinguishing between the two catalog management models supported for Iceberg tables in Snowflake:

Configuring Iceberg Tables: External Volumes & DDL Mechanics

Deploying Iceberg tables requires configuring two foundational abstractions: an External Volume and (for externally-managed tables) a Catalog Integration.

1. Creating an External Volume

An External Volume (EXTERNAL_VOLUME) is an account-level object that encapsulates cloud storage locations, IAM credentials, and encryption settings for Iceberg data and metadata files across AWS, Azure, and GCP:

-- Create an External Volume pointing to customer-owned AWS S3 bucket
CREATE OR REPLACE EXTERNAL VOLUME iceberg_ext_vol
  STORAGE_LOCATIONS = (
    (
      NAME = 's3-us-east-1-iceberg'
      STORAGE_PROVIDER = 'S3'
      STORAGE_BASE_URL = 's3://acme-analytics-lake/iceberg/'
      STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake_iceberg_role'
      STORAGE_AWS_EXTERNAL_ID = 'iceberg_access_ext_id'
      ENCRYPTION = (TYPE = 'AWS_SSE_KMS', KMS_KEY_ID = 'arn:aws:kms:us-east-1:123456789012:key/abc-123')
    )
  );

2. Creating a Snowflake-Managed Iceberg Table

With the external volume defined, creating a Snowflake-managed Iceberg table requires specifying CATALOG = 'SNOWFLAKE' and designating the EXTERNAL_VOLUME and BASE_LOCATION (the subfolder prefix in cloud storage):

-- Create a Snowflake-managed Iceberg Table
CREATE OR REPLACE ICEBERG TABLE customer_orders_iceberg (
    order_id BIGINT,
    customer_id BIGINT,
    order_amount DECIMAL(12, 2),
    order_status VARCHAR,
    order_date DATE
)
  CATALOG = 'SNOWFLAKE'
  EXTERNAL_VOLUME = 'iceberg_ext_vol'
  BASE_LOCATION = 'customer_orders/'
  CLUSTER BY (order_date);

-- Full DML operations are fully supported
INSERT INTO customer_orders_iceberg VALUES 
  (5001, 101, 149.99, 'COMPLETED', '2026-09-23'),
  (5002, 102, 299.50, 'PROCESSING', '2026-09-23');

UPDATE customer_orders_iceberg 
SET order_status = 'SHIPPED' 
WHERE order_id = 5002;

3. Creating an Externally-Managed Iceberg Table (e.g., AWS Glue)

When external engines like Spark govern table metadata, an architect defines a Catalog Integration connecting Snowflake to the external catalog, followed by creating an externally-managed Iceberg table:

-- Step 1: Create Catalog Integration for AWS Glue
CREATE OR REPLACE CATALOG INTEGRATION glue_catalog_int
  CATALOG_SOURCE = GLUE
  CATALOG_NAMESPACE = 'analytics_lakehouse'
  TABLE_FORMAT = ICEBERG
  GLUE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake_glue_sync_role'
  GLUE_CATALOG_ID = '123456789012'
  ENABLED = TRUE;

-- Step 2: Create Iceberg Table referencing the external Glue catalog
CREATE OR REPLACE ICEBERG TABLE raw_telemetry_iceberg
  EXTERNAL_VOLUME = 'iceberg_ext_vol'
  CATALOG = 'glue_catalog_int'
  CATALOG_TABLE_NAME = 'raw_telemetry';

-- Step 3: Refresh table when external Spark jobs commit new Iceberg snapshots
ALTER ICEBERG TABLE raw_telemetry_iceberg REFRESH;

---

Iceberg Storage, Time Travel, Governance & Architectural Constraints

Architects must master the differences in lifecycle, governance, and operational behavior between native Snowflake tables and Iceberg tables.

Time Travel on Iceberg Tables

Snowflake supports standard Time Travel querying on Iceberg tables using timestamps or Iceberg snapshot IDs:

-- Query Iceberg table at a specific historical point in time
SELECT * 
FROM customer_orders_iceberg 
AT (TIMESTAMP => '2026-09-23 01:00:00 -07:00'::TIMESTAMP_TZ);

You can query any snapshot committed within the table's DATA_RETENTION_TIME_IN_DAYS; Snowflake deletes metadata for expired snapshots after the retention period passes.

Storage Economics & Billing

• Storage billed by your cloud provider: With an external volume, Parquet files and Iceberg metadata live in the customer's own bucket, so the customer pays the cloud provider for storage and Snowflake does not bill storage for those tables. (A newer option, Snowflake-managed storage for Iceberg tables, stores the files in Snowflake storage instead — in that case Snowflake bills storage, and permanent tables get Fail-safe.)
• Compute Billing: Queries and DML mutations against Iceberg tables consume virtual warehouse credits identical to queries against native tables.

Feature Parity & Architectural Constraints Matrix

Critical Exam Trap: Zero Fail-Safe on Iceberg Tables: Native permanent Snowflake tables feature a mandatory 7-day Fail-safe disaster recovery period managed by Snowflake Support. Apache Iceberg tables have zero Fail-safe protection. If data files or metadata JSON files are accidentally deleted or corrupted in the customer's S3/ADLS bucket, Snowflake Support cannot recover them. Disaster recovery and accidental deletion protection must be configured directly on the cloud storage bucket (e.g., S3 Versioning, Object Lock, or bucket replication).