Bootstrap a Data Lakehouse in an Afternoon

doesn’t must be that sophisticated. On this article, I’ll present you the right way to develop a primary, “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 information

• Optimising and vacuuming your tables.

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

Our instance could be primary, but it surely’ll showcase the setup, the completely different instruments and the processes you possibly can put in place to construct up a extra intensive information retailer. All fashionable cloud suppliers have equivalents of the AWS providers I’m discussing on this article, so it needs to be pretty easy to duplicate what I focus on right here on Azure, Google Cloud, and others.

To ensure we’re all on the identical web page, here’s a temporary clarification of a number of the key applied sciences we’ll be utilizing.

AWS Glue/Spark

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

AWS Athena

AWS Athena is an interactive question service that simplifies information evaluation instantly in Amazon S3 utilizing normal SQL. As a serverless platform, there’s no must handle or provision servers; simply level Athena at your S3 information, outline your schema (normally with AWS Glue), and start working SQL queries. It’s continuously 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 information saved in information lakes, corresponding to Amazon S3 object storage. Historically, on S3, you possibly can create, learn, and delete objects(recordsdata), however updating them is just not attainable. 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 information processing instruments utilized by information engineers and scientists, due to its grounding in SQL, efficiency, and flexibility.

Situation overview

Let’s say you may 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:

• Protected 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 towards production-accurate information for fast checks

What we’ll construct

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

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

• S3 for information storage
• Glue Catalogue for desk metadata/discovery
• Athena for serverless SQL reads and writes
• DuckDB for affordable, native analytics towards 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 software you want to do that; I’ll present utilizing conda since that’s what I normally do. For demo functions, I’ll be working all of the code inside a Jupyter Pocket book surroundings.

# create and activate a neighborhood 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 exams
pip set up duckdb awscli jupyter

Conditions

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

• An S3 bucket for the info 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 person 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 house display screen. Click on on it to deliver up a brand new menu block on the left. In there, it is best to see an Administration-> Workgroups hyperlink. You’ll mechanically be assigned to the first workgroup. You may stick to this or create a brand new one when you like. Whichever choice you select, edit it and be certain that the next choices are chosen.

• Analytics Engine — Athena SQL. Manually set the engine model to three.0.
• Choose customer-managed question outcome 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 house web page and launch the Trino SQL question editor. Your display screen ought to appear like this.

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

-- This mechanically 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 information (INSERT / UPDATE / DELETE / MERGE)

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

Seed a couple 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 test.

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 rely 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 particular 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 information wholesome: OPTIMIZE and VACUUM

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

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

-- compact "sizzling" information (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 recordsdata
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 sort within the following.

# httpfs provides S3 help; 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 many others.). Subsequently, you have to to make sure that, for example, 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 information.

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

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

...
...
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 title, 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)
outcome = db.sql("""
SELECT *
FROM iceberg_scan('s3://your_bucket/warehouse/sales_iceberg/metadata/00004-4a2cff23-2bf6-4c69-8edc-6d74c02f4c0e.metadata.json')
LIMIT 10
""")
print(outcome)

┌──────────┬─────────────┬─────────────────────┬──────────┬────────────┐
│ 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.

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

Obtainable 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) Elective 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 most likely nonetheless wish to use Athena for ad-hoc SQL, time journey, and upkeep, however massive CTAS/ETL can come through Glue jobs. (Simply bear in mind that model compatibility and AWS LakeFormation permissions can chew, as Glue and Athena could lag barely on Iceberg variations.)

Right here’s an instance of some Glue Spark code that inserts a couple of new information rows, beginning at order_id = 110, into our present desk. Earlier than working this, it is best to 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)

# --------------------------------------------------------
# Drive 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 out there:")
spark.sql("SHOW CATALOGS").present(truncate=False)

# --------------------------------------------------------
# Learn present Iceberg desk (elective)
# --------------------------------------------------------
existing_table_df = glueContext.create_data_frame.from_catalog(
    database="analytics",
    table_name="sales_iceberg"
)
print("Current desk schema:")
existing_table_df.printSchema()

# --------------------------------------------------------
# Create 5 new information
# --------------------------------------------------------
new_records_data = []
for i in vary(5):
    order_id = 110 + i
    document = {
        "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(document)

new_records_df = spark.createDataFrame([Row(**r) for r in new_records_data])
print(f"Creating {new_records_df.rely()} new information:")
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 information 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 can:

• Create extra tables with information.

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

• Add scheduled upkeep. For instance, EventBridge, Step, and Lambdas could possibly 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 information lakehouse on AWS. It ought to function a information for information engineers who wish to join easy object storage with complicated enterprise information warehouses.

Hopefully, I’ve proven that constructing a Information Lakehouse—a system that mixes the low price of information 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 the right way to rework static recordsdata 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 attainable to learn small to medium information lakes domestically. And when your information volumes develop and get too huge for native processing, I confirmed how simple it was to step as much as an enterprise class information processing platform like Spark.