Apache Iceberg + Snowflake: End-to-End Data Lake Guide

Explore why Apache Iceberg is needed by comparing OLAP and OLTP, analyzing current data warehouse architectures and problems, and outlining the benefits Iceberg brings to an end-to-end data lake.

Explore how OLTP transactional systems capture checkout details, update customer records, and write data to a row-based database for fast, day-to-day operations.

Explore why OLTP systems struggle with data analysis, as joins, aggregates, and volume overwhelm structured data, while analysts seek regional sales, product users, and yearly revenue.

Gain a high-level overview of OLAP systems and how they address OLTP problems, with key components like storage, file formats, table formats, storage engine, compute engine, catalog, and consumption tools.

Explore the storage component of an olap system to store historical data from sources, using local file systems, Hadoop, or blob storage like S3, Google GCS, and Azure Blob Storage.

Learn how OLAP storage chooses file formats based on data type, using csv or avro for row data, json or text for semi-structured, and parquet or ORC for columnar storage.

Explore storage engines that lay out data per the table format and optimize data layout, while compute engines run workloads on distributed mpp systems like Spark, Snowflake, and Redshift.

Explore the olap catalog as a central metadata hub that identifies data sets, reveals table schemas, and shows storage locations, serving as the reference point for compute engines.

Explore how a data warehouse functions as a proprietary system with storage, catalog, and compute engines to support OLAP workloads, distinguishing from OLTP databases and examples like Redshift, BigQuery, Snowflake.

Data warehouses provide a single source of truth, govern data with schema conformance across sources, enabling fast historical analytics, but risk vendor lock-in, higher costs, and limited ml support.

Explore the drawbacks of a closed data warehouse architecture where data, formats, and processing engines are vendor-locked, and learn the value of open, interoperable solutions.

Trace the shift from data warehouses to data lakes, with hive metastore enabling scalable analytics on open formats. Note data transfer costs and lack of acid transactions in data lakes.

Discover how modern table formats like Iceberg, Hudi, and Delta Lake replace hive metastore with a canonical file list, enabling ACID transactions and data lakehouse capabilities on data lakes.

Learn to set up a local Apache Iceberg environment using GitHub Codespaces, Poetry, and PySpark, create a repository, install dependencies, and run Iceberg queries in a Jupyter notebook.

Demonstrate delete, create table, and merge operations on an Iceberg table, highlighting ACID-compliant transactions on the file system. Verify results in VS Code and the users target table.

Learn how the parquet file format and its metadata enable the Iceberg table format in data lakes, clarifying the link between parquet and Iceberg.

Compare row-wise, columnar, and hybrid storage, highlighting horizontal and vertical partitioning and their impact on memory access and locality. The lecture links formats to Parquet and ORC and previews Iceberg.

Explore the Parquet file structure at a high level, detailing row groups, column chunks, and data pages with metadata and encoding. See how these concepts connect to Iceberg table format.

Install the parquet CLI via brew in a code spaces environment, then inspect metadata, schema, and data, and convert parquet to CSV or Avro using a sample NYC traffic file.

Explore how parquet files organize data and metadata, including schema, row groups, compression, and encoding such as dictionary and run length encoding, and how footers enable access.

Explore parquet metadata and how metadata files, snapshots, and a catalog enable efficient querying, transaction consistency, and access control in the iceberg table format.

Learn how the Iceberg catalog manages metadata files, snapshots, and manifest lists to track inserts, updates, and deletes, linking to data files through manifest files for efficient querying.

Explore the basic features of Apache Iceberg, including partitioning types, the time travel feature, and schema evolution, in this introductory session.

Explore how Iceberg's hidden partitioning enables fast year, month, and day queries without creating extra columns, with Iceberg handling the transformation and partitioning behind the scenes.

Explore iceberg's hidden partition feature by creating a sample dataset and an iceberg table in a notebook, then observe how a transformed month filter affects partition prune and query planning.

Apply partitioning on Iceberg tables to enable partition pruning in Spark queries, and use simple range predicates on the partitioned date column rather than functions.

Demonstrates using truncate hidden partition with Iceberg to partition by the first five characters of the last name, enabling partition pruning and efficient query access.

Apply the bucket transformation on a high-cardinality column like customer_id to enable Iceberg hidden partitioning, distributing data across 15 buckets and improving selective query performance.

Explore partition evolution in Iceberg by evolving a country partition to add city, using an alter statement, and retaining old partitions while creating new subfolders to reflect new partitions.

Explore how time travel in Iceberg uses snapshot files to restore a table to a prior state, query as of a timestamp, and recover from data corruption.

Explore how Iceberg snapshot isolation ensures a consistent view by routing read and write transactions through the Iceberg catalog to the latest committed snapshot, manifest files, and data files.

Explain copy on write and merge on read in Iceberg, using a 56 to 67 update and a file creation in S3, and note Snowflake currently supports copy on write.

Explore copy-on-write and merge-on-read concepts in Apache Iceberg with a hands-on demo showing how inserts, updates, and deletes create new data files and updated metadata, and discuss benefits and tradeoffs.

Explore the merge on read strategy for insert, delete, and update operations, where delete files mark data positions. Compare faster updates with slower reads and compaction needs.

Configure Snowflake to use the Iceberg table format and set up aws s3 as blob storage. Perform basic operations in Iceberg tables by writing, updating, and deleting data.

Create an S3 bucket in the same region as Snowflake, then set up an IAM policy, role, and trust relationship to enable iceberg external volume.

Create an iceberg table in Snowflake and explore how metadata files populate in AWS S3, including metadata.json, version, and version hint for catalog navigation.

Insert a single record into the iceberg table, creating a data file in the S3 data folder and generating metadata files including version.txt and a manifest list and manifest file.

Learn how snowflake leverages iceberg table metadata to answer count(*) queries from the cache without scanning data files, producing a metadata based result from a single inserted record.

Learn how Iceberg update operations use copy on write. Iceberg copies the target data file, updates records, and writes a new file to S3 for each update transaction.

Understand delete operations on Iceberg tables in Snowflake via copy on write: a new data file is created, the old one marked deleted, and snapshots and metadata updated.

Apply clustering to an iceberg table in Snowflake by birth country, then observe how auto clustering reorganizes data into new folders and reduces partition scans.

Explore the data lake architecture with bronze, silver, and gold layers in blob storage, ingesting data from sources and enabling secure queries via Snowflake or Spark.

Build a three-layer data lake—bronze, silver, and gold—using iceberg and Snowflake to load raw JSON from S3, transform semi-structured data, and parse contact info into email and phone.

Explore interoperability by reading iceberg data stored in S3 via Spark, using a workaround to access metadata when version.txt is missing, and see Bob Smith records in Snowflake iceberg.

Examine the catalog challenges in Iceberg data, comparing Snowflake and Athena catalogs, and explore open catalogs like Polaris and Gravity that enable cross-engine access to Iceberg data.