Bootstrap a Data Lakehouse in an Afternoon

doesn’t should be that difficult. On this article, I’ll present you easy methods to develop a fundamental, “starter” one which makes use of an Iceberg desk on AWS S3 storage. As soon as the desk is registered utilizing AWS Glue, you’ll be capable to question and mutate it from Amazon Athena, together with utilizing:

• Merging, updating and deleting knowledge

• Optimising and vacuuming your tables.

I’ll additionally present you easy methods to examine the identical tables domestically from DuckDB, and we’ll additionally see easy methods to use Glue/Spark to insert extra desk knowledge.

Our instance is perhaps fundamental, but it surely’ll showcase the setup, the completely different instruments and the processes you’ll be able to put in place to construct up a extra in depth knowledge retailer. All trendy cloud suppliers have equivalents of the AWS companies I’m discussing on this article, so it ought to be pretty easy to copy what I talk about right here on Azure, Google Cloud, and others.

To verify we’re all on the identical web page, here’s a transient rationalization of a number of the key applied sciences we’ll be utilizing.

AWS Glue/Spark

AWS Glue is a totally managed, serverless ETL service from Amazon that streamlines knowledge preparation and integration for analytics and machine studying. It routinely detects and catalogues metadata from numerous sources, corresponding to S3, right into a centralised Information Retailer. Moreover, it could actually create customisable Python-based Spark ETL scripts to execute these duties on a scalable, serverless Apache Spark platform. This makes it nice for constructing knowledge lakes on Amazon S3, loading knowledge into knowledge warehouses like Amazon Redshift, and performing knowledge cleansing and transformation. all with out managing infrastructure.

AWS Athena

AWS Athena is an interactive question service that simplifies knowledge evaluation instantly in Amazon S3 utilizing customary SQL. As a serverless platform, there’s no have to handle or provision servers; simply level Athena at your S3 knowledge, outline your schema (often with AWS Glue), and start working SQL queries. It’s incessantly utilised for advert hoc evaluation, reporting, and exploration of enormous datasets in codecs corresponding to CSV, JSON, ORC, or Parquet.

Iceberg tables

Iceberg tables are an open desk format for datasets that present database-like capabilities for knowledge saved in knowledge lakes, corresponding to Amazon S3 object storage. Historically, on S3, you’ll be able to create, learn, and delete objects(information), however updating them will not be potential. The Iceberg format addresses that limitation whereas additionally providing different advantages, together with ACID transactions, schema evolution, hidden partitioning, and time-travel options.

DuckDB

DuckDB is an in-memory analytical database written in C++ and designed for analytical SQL workloads. Since its launch a few years in the past, it has grown in recognition and is now one of many premier knowledge processing instruments utilized by knowledge engineers and scientists, because of its grounding in SQL, efficiency, and flexibility.

Situation overview

Let’s say you will have been tasked with constructing a small “warehouse-lite” analytics desk for order occasions, however you don’t wish to undertake a heavyweight platform simply but. You want:

• Secure writes (no damaged readers, no partial commits)
• Row-level adjustments (UPDATE/DELETE/MERGE, not solely append)
• Level-in-time reads (for audits and debugging)
• Native analytics in opposition to production-accurate knowledge for fast checks

What we’ll construct

1. Create an Iceberg desk in Glue & S3 by way of Athena
2. Load and mutate rows (INSERT/UPDATE/DELETE/MERGE)
3. Time journey to prior snapshots (by timestamp and by snapshot ID)
4. Preserve it quick with OPTIMIZE and VACUUM
5. Learn the identical desk domestically from DuckDB (S3 entry by way of DuckDB Secrets and techniques)
6. See easy methods to add new data to our desk utilizing Glue Spark code

So, in a nutshell, we’ll be utilizing:-

• S3 for knowledge storage
• Glue Catalogue for desk metadata/discovery
• Athena for serverless SQL reads and writes
• DuckDB for affordable, native analytics in opposition to the identical Iceberg desk
• Spark for processing grunt

The important thing takeaway from our perspective is that by utilizing the above applied sciences, we can carry out database-like queries on object storage.

Organising our growth surroundings 

I desire to isolate native tooling in a separate surroundings. Use any device you want to do that; I’ll present utilizing conda since that’s what I often do. For demo functions, I’ll be working all of the code inside a Jupyter Pocket book surroundings.

# create and activate an area env
conda create -n iceberg-demo python=3.11 -y
conda activate iceberg-demo

# set up duckdb CLI + Python package deal and awscli for fast assessments
pip set up duckdb awscli jupyter

Conditions

As we’ll be utilizing AWS companies, you’ll want an AWS account. Additionally,

• An S3 bucket for the information lake (e.g., s3://my-demo-lake/warehouse/)
• A Glue database (we’ll create one)
• Athena Engine Model 3 in your workgroup
• An IAM function or consumer for Athena with S3 + Glue permissions 

1/ Athena setup

When you’ve signed into AWS, open Athena within the console and set your workgroup, engine model and S3 output location (for question outcomes). To do that, search for a hamburger-style menu icon on the highest left of the Athena dwelling display. Click on on it to convey up a brand new menu block on the left. In there, you must see an Administration-> Workgroups hyperlink. You’ll routinely be assigned to the first workgroup. You may keep on with this or create a brand new one should you like. Whichever choice you select, edit it and be sure that the next choices are chosen.

• Analytics Engine — Athena SQL. Manually set the engine model to three.0.
• Choose customer-managed question consequence configuration and enter the required bucket and account info.

2/ Create an Iceberg desk in Athena

We’ll retailer order occasions and let Iceberg handle partitioning transparently. I’ll use a “hidden” partition on the day of the timestamp to unfold writes/reads. Return to the Athena dwelling web page and launch the Trino SQL question editor. Your display ought to seem like this.

Sort in and run the next SQL. Change bucket/desk names to swimsuit.

-- This routinely creates a Glue database 
-- if you do not have one already
CREATE DATABASE IF NOT EXISTS analytics;

CREATE TABLE analytics.sales_iceberg (
  order_id    bigint,
  customer_id bigint,
  ts          timestamp,
  standing      string,
  amount_usd  double
)
PARTITIONED BY (day(ts))
LOCATION 's3://your_bucket/warehouse/sales_iceberg/'
TBLPROPERTIES (
  'table_type' = 'ICEBERG',
  'format' = 'parquet',
  'write_compression' = 'snappy'
)

3) Load and mutate knowledge (INSERT / UPDATE / DELETE / MERGE)

Athena helps actual Iceberg DML, permitting you to insert rows, replace and delete data, and upsert utilizing the MERGE assertion. Beneath the hood, Iceberg makes use of snapshot-based ACID with delete information; readers keep constant whereas writers work in parallel. 

Seed a number of rows.

INSERT INTO analytics.sales_iceberg VALUES
  (101, 1, timestamp '2025-08-01 10:00:00', 'created', 120.00),
  (102, 2, timestamp '2025-08-01 10:05:00', 'created',  75.50),
  (103, 2, timestamp '2025-08-02 09:12:00', 'created',  49.99),
  (104, 3, timestamp '2025-08-02 11:47:00', 'created', 250.00);

A fast sanity examine.

SELECT * FROM analytics.sales_iceberg ORDER BY order_id;

 order_id | customer_id |          ts           |  standing  | amount_usd
----------+-------------+-----------------------+----------+-----------
  101     | 1           | 2025-08-01 10:00:00   | created  | 120.00
  102     | 2           | 2025-08-01 10:05:00   | created  |  75.50
  103     | 2           | 2025-08-02 09:12:00   | created  |  49.99
  104     | 3           | 2025-08-02 11:47:00   | created  | 250.00

Replace and delete.

UPDATE analytics.sales_iceberg
SET standing = 'paid'
WHERE order_id IN (101, 102)

-- removes order 103
DELETE FROM analytics.sales_iceberg
WHERE standing = 'created' AND amount_usd < 60

Idempotent upserts with MERGE

Let’s deal with order 104 as refunded and create a brand new order 105.

MERGE INTO analytics.sales_iceberg AS t
USING (
  VALUES
    (104, 3, timestamp '2025-08-02 11:47:00', 'refunded', 250.00),
    (105, 4, timestamp '2025-08-03 08:30:00', 'created',   35.00)
) AS s(order_id, customer_id, ts, standing, amount_usd)
ON s.order_id = t.order_id
WHEN MATCHED THEN 
  UPDATE SET 
    customer_id = s.customer_id,
    ts = s.ts,
    standing = s.standing,
    amount_usd = s.amount_usd
WHEN NOT MATCHED THEN 
  INSERT (order_id, customer_id, ts, standing, amount_usd)
  VALUES (s.order_id, s.customer_id, s.ts, s.standing, s.amount_usd);

Now you can re-query to see: 101/102 → paid, 103 deleted, 104 → refunded, and 105 → created. (In case you’re working this in a “actual” account, you’ll discover the S3 object depend ticking up — extra on upkeep shortly.)

SELECT * FROM analytics.sales_iceberg ORDER BY order_id

# order_id customer_id ts standing amount_usd
1 101 1 2025-08-01 10:00:00.000000 paid 120.0
2 105 4 2025-08-03 08:30:00.000000 created 35.0
3 102 2 2025-08-01 10:05:00.000000 paid 75.5
4 104 3 2025-08-02 11:47:00.000000 refunded 250.0

4) Time journey (and model journey)

That is the place the true worth of utilizing Iceberg shines. You may question the desk because it checked out a second in time or by a selected snapshot ID. In Athena, use this syntax,

-- Time journey to midday on Aug 2 (UTC)
SELECT order_id, standing, amount_usd
FROM analytics.sales_iceberg
FOR TIMESTAMP AS OF TIMESTAMP '2025-08-02 12:00:00 UTC'
ORDER BY order_id;

-- Or Model journey (substitute the id with an precise snapshot id out of your desk)

SELECT *
FROM analytics.sales_iceberg
FOR VERSION AS OF 949530903748831860;

To get the varied model (snapshot) IDs related to a specific desk, use this question.

SELECT * FROM "analytics"."sales_iceberg$snapshots"
ORDER BY committed_at DESC;

5) Preserving your knowledge wholesome: OPTIMIZE and VACUUM

Row-level writes (UPDATE/DELETE/MERGE) create many delete information and might fragment knowledge. Two statements maintain issues quick and storage-friendly:

• OPTIMIZE … REWRITE DATA USING BIN_PACK — compacts small/fragmented information and folds deletes into knowledge
• VACUUM — expires outdated snapshots + cleans orphan information

-- compact "sizzling" knowledge (yesterday) and merge deletes
OPTIMIZE analytics.sales_iceberg
REWRITE DATA USING BIN_PACK
WHERE ts >= date_trunc('day', current_timestamp - interval '1' day);

-- expire outdated snapshots and take away orphan information
VACUUM analytics.sales_iceberg;

6) Native analytics with DuckDB (read-only)

It’s nice to have the ability to sanity-check manufacturing tables from a laptop computer with out having to run a cluster. DuckDB’s httpfs + iceberg extensions make this straightforward.

6.1 Set up & load extensions

Open your Jupyter pocket book and kind within the following.

# httpfs offers S3 assist; iceberg provides Iceberg readers.

import duckdb as db
db.sql("set up httpfs; load httpfs;")
db.sql("set up iceberg; load iceberg;")

6.2 Present S3 credentials to DuckDB the “proper” manner (Secrets and techniques)

DuckDB has a small however highly effective secrets and techniques supervisor. Essentially the most strong setup in AWS is the credential chain supplier, which reuses regardless of the AWS SDK can discover (surroundings variables, IAM function, and so on.). Due to this fact, you’ll need to make sure that, as an illustration, your AWS CLI credentials are configured.

db.sql("""CREATE SECRET ( TYPE s3, PROVIDER credential_chain )""")

After that, any s3://… reads on this DuckDB session will use the key knowledge.

6.3 Level DuckDB on the Iceberg desk’s metadata

Essentially the most specific manner is to reference a concrete metadata file (e.g., the newest one in your desk’s metadata/ folder:)

To get a listing of these, use this question

consequence = db.sql("""
SELECT *
FROM glob('s3://your_bucket/warehouse/**')
ORDER BY file
""")
print(consequence)

...
...
s3://your_bucket_name/warehouse/sales_iceberg/metadata/00000-942a25ce-24e5-45f8-ae86-b70d8239e3bb.metadata.json                                      │
s3://your_bucket_name/warehouse/sales_iceberg/metadata/00001-fa2d9997-590e-4231-93ab-642c0da83f19.metadata.json                                      │
s3://your_bucket_name/warehouse/sales_iceberg/metadata/00002-0da3a4af-64af-4e46-bea2-0ac450bf1786.metadata.json                                      │
s3://your_bucket_name/warehouse/sales_iceberg/metadata/00003-eae21a3d-1bf3-4ed1-b64e-1562faa445d0.metadata.json                                      │
s3://your_bucket_name/warehouse/sales_iceberg/metadata/00004-4a2cff23-2bf6-4c69-8edc-6d74c02f4c0e.metadata.json    
...
...
...

Search for the metadata.json file with the best numbered begin to the file identify, 00004 in my case. Then, you should utilize that in a question like this to retrieve the newest place of your underlying desk.

# Use the best numbered metadata file (00004 seems to be the newest in my case)
consequence = db.sql("""
SELECT *
FROM iceberg_scan('s3://your_bucket/warehouse/sales_iceberg/metadata/00004-4a2cff23-2bf6-4c69-8edc-6d74c02f4c0e.metadata.json')
LIMIT 10
""")
print(consequence)

┌──────────┬─────────────┬─────────────────────┬──────────┬────────────┐
│ order_id │ customer_id │         ts          │  standing  │ amount_usd │
│  int64   │    int64    │      timestamp      │ varchar  │   double   │
├──────────┼─────────────┼─────────────────────┼──────────┼────────────┤
│      105 │           4 │ 2025-08-03 08:30:00 │ created  │       35.0 │
│      104 │           3 │ 2025-08-02 11:47:00 │ refunded │      250.0 │
│      101 │           1 │ 2025-08-01 10:00:00 │ paid     │      120.0 │
│      102 │           2 │ 2025-08-01 10:05:00 │ paid     │       75.5 │
└──────────┴─────────────┴─────────────────────┴──────────┴────────────┘

Desire a particular snapshot? Use this to get a listing.

consequence = db.sql("""
SELECT *
FROM iceberg_snapshots('s3://your_bucket/warehouse/sales_iceberg/metadata/00004-4a2cff23-2bf6-4c69-8edc-6d74c02f4c0e.metadata.json')
""")
print("Out there Snapshots:")
print(consequence)

Out there Snapshots:
┌─────────────────┬─────────────────────┬─────────────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ sequence_number │     snapshot_id     │      timestamp_ms       │                                                          manifest_list                                                           │
│     uint64      │       uint64        │        timestamp        │                                                             varchar                                                              │
├─────────────────┼─────────────────────┼─────────────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│               1 │ 5665457382547658217 │ 2025-09-09 10:58:44.225 │ s3://your_bucket/warehouse/sales_iceberg/metadata/snap-5665457382547658217-1-bb7d0497-0f97-4483-98e2-8bd26ddcf879.avro │
│               3 │ 8808557756756599285 │ 2025-09-09 11:19:24.422 │ s3://your_bucket/warehouse/sales_iceberg/metadata/snap-8808557756756599285-1-f83d407d-ec31-49d6-900e-25bc8d19049c.avro │
│               2 │   31637314992569797 │ 2025-09-09 11:08:08.805 │ s3://your_bucket/warehouse/sales_iceberg/metadata/snap-31637314992569797-1-000a2e8f-b016-4d91-9942-72fe9ddadccc.avro   │
│               4 │ 4009826928128589775 │ 2025-09-09 11:43:18.117 │ s3://your_bucket/warehouse/sales_iceberg/metadata/snap-4009826928128589775-1-cd184303-38ab-4736-90da-52e0cf102abf.avro │
└─────────────────┴─────────────────────┴─────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

7) Non-obligatory additional: Writing from Spark/Glue

In case you desire Spark for bigger batch writes, Glue can learn/write Iceberg tables registered within the Glue Catalogue. You’ll in all probability nonetheless wish to use Athena for ad-hoc SQL, time journey, and upkeep, however massive CTAS/ETL can come by way of Glue jobs. (Simply bear in mind that model compatibility and AWS LakeFormation permissions can chew, as Glue and Athena might lag barely on Iceberg variations.)

Right here’s an instance of some Glue Spark code that inserts a number of new knowledge rows, beginning at order_id = 110, into our current desk. Earlier than working this, you must add the next Glue job parameter (below Glue Job Particulars-> Superior Parameters-> Job parameters.

Key: --conf
Worth: spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions

import sys
import random
from datetime import datetime
from pyspark.context import SparkContext
from awsglue.utils import getResolvedOptions
from awsglue.context import GlueContext
from awsglue.job import Job
from pyspark.sql import Row

# --------------------------------------------------------
# Init Glue job
# --------------------------------------------------------
args = getResolvedOptions(sys.argv, ['JOB_NAME'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

# --------------------------------------------------------
# Power Iceberg + Glue catalog configs (dynamic solely)
# --------------------------------------------------------
spark.conf.set("spark.sql.catalog.glue_catalog", "org.apache.iceberg.spark.SparkCatalog")
spark.conf.set("spark.sql.catalog.glue_catalog.warehouse", "s3://your_bucket/warehouse/")
spark.conf.set("spark.sql.catalog.glue_catalog.catalog-impl", "org.apache.iceberg.aws.glue.GlueCatalog")
spark.conf.set("spark.sql.catalog.glue_catalog.io-impl", "org.apache.iceberg.aws.s3.S3FileIO")
spark.conf.set("spark.sql.defaultCatalog", "glue_catalog")

# --------------------------------------------------------
# Debug: record catalogs to substantiate glue_catalog is registered
# --------------------------------------------------------
print("Present catalogs accessible:")
spark.sql("SHOW CATALOGS").present(truncate=False)

# --------------------------------------------------------
# Learn current Iceberg desk (optionally available)
# --------------------------------------------------------
existing_table_df = glueContext.create_data_frame.from_catalog(
    database="analytics",
    table_name="sales_iceberg"
)
print("Present desk schema:")
existing_table_df.printSchema()

# --------------------------------------------------------
# Create 5 new data
# --------------------------------------------------------
new_records_data = []
for i in vary(5):
    order_id = 110 + i
    file = {
        "order_id": order_id,
        "customer_id": 1000 + (i % 10),
        "value": spherical(random.uniform(10.0, 500.0), 2),
        "created_at": datetime.now(),
        "standing": "accomplished"
    }
    new_records_data.append(file)

new_records_df = spark.createDataFrame([Row(**r) for r in new_records_data])
print(f"Creating {new_records_df.depend()} new data:")
new_records_df.present()

# Register temp view for SQL insert
new_records_df.createOrReplaceTempView("new_records_temp")

# --------------------------------------------------------
# Insert into Iceberg desk (alias columns as wanted)
# --------------------------------------------------------
spark.sql("""
    INSERT INTO analytics.sales_iceberg (order_id, customer_id, ts, standing, amount_usd)
    SELECT order_id,
           customer_id,
           created_at AS ts,
           standing,
           value AS amount_usd
    FROM new_records_temp
""")

print(" Sccessfully added 5 new data to analytics.sales_iceberg")

# --------------------------------------------------------
# Commit Glue job
# --------------------------------------------------------
job.commit()

Double-check with Athena.

choose * from analytics.sales_iceberg
order by order_id

# order_id customer_id ts standing amount_usd
1 101 1 2025-08-01 10:00:00.000000 paid 120.0
2 102 2 2025-08-01 10:05:00.000000 paid 75.5
3 104 3 2025-08-02 11:47:00.000000 refunded 250.0
4 105 4 2025-08-03 08:30:00.000000 created 35.0
5 110 1000 2025-09-10 16:06:45.505935 accomplished 248.64
6 111 1001 2025-09-10 16:06:45.505947 accomplished 453.76
7 112 1002 2025-09-10 16:06:45.505955 accomplished 467.79
8 113 1003 2025-09-10 16:06:45.505963 accomplished 359.9
9 114 1004 2025-09-10 16:06:45.506059 accomplished 398.52

Future Steps

From right here, you may:

• Create extra tables with knowledge.

• Experiment with partition evolution (e.g., change desk partition from day → hour as volumes develop),

• Add scheduled upkeep. For instance, EventBridge, Step, and Lambdas may very well be used to run OPTIMIZE/VACUUM on a scheduled cadence.

Abstract

On this article, I’ve tried to offer a transparent path for constructing an Iceberg knowledge lakehouse on AWS. It ought to function a information for knowledge engineers who wish to join easy object storage with complicated enterprise knowledge warehouses.

Hopefully, I’ve proven that constructing a Information Lakehouse—a system that mixes the low value of knowledge lakes with the transactional integrity of warehouses—doesn’t essentially require extensive infrastructure deployment. And whereas making a full lakehouse is one thing that evolves over a very long time, I hope I’ve satisfied you that you simply actually could make the bones of 1 in a day.

By leveraging Apache Iceberg on a cloud storage system like Amazon S3, I demonstrated easy methods to rework static information into dynamic, managed tables able to ACID transactions, row-level mutations (MERGE, UPDATE, DELETE), and time journey, all with out provisioning a single server.

I additionally confirmed that by utilizing new analytic instruments corresponding to DuckDB, it’s potential to learn small to medium knowledge lakes domestically. And when your knowledge volumes develop and get too massive for native processing, I confirmed how simple it was to step as much as an enterprise class knowledge processing platform like Spark.