Query raw CSV data, build an Apache Iceberg table and apply order corrections with SQL.
An online retailer receives daily order exports. Finance needs corrected order totals without rebuilding every report. You will load a tiny order dataset into a lake and apply a late-arriving correction.
A queryable Iceberg table containing three orders, with a corrected total of 149.00 for order 1002.
Raw ordersraw/orders.csv
Query & mergeEngine version 3Curated lakeApache Iceberg / Parquet
AWS Glue Data Catalog · metadata used by AthenaFor production, add ingestion orchestration, deduplication before MERGE, schema checks, separate access to raw and curated prefixes, and an Iceberg maintenance policy. Choose this pattern for analytical corrections and batch reporting; do not use it as the checkout transaction database.
Durable object storage for files, application assets and data lakes.
Choose it when: Store objects independently of compute and retain multiple versions.
Consider the tradeoff: Use EBS for a block device or EFS for a shared file system.
AWS service documentationThe Data Catalog stores database and table metadata used by query engines.
Choose it when: Share table definitions between analytics tools. This lab uses the catalog, not Glue ETL jobs.
Consider the tradeoff: A catalog does not store the underlying rows; the files remain in S3.
AWS service documentationA serverless SQL query service for analyzing data in S3.
Choose it when: Explore a data lake with SQL without operating a query cluster.
Consider the tradeoff: Evaluate Redshift for a managed warehouse; RDS for application transactions.
AWS service documentationAWS charges may apply for Athena bytes scanned, S3 storage/requests and Glue catalog usage. Use only the three-row sample; set an Athena workgroup scan limit. Free Tier eligibility varies by account.
Use a non-production account, sign in through an IAM role rather than root, and review AWS pricing. Budget alerts notify you; they do not automatically cap spending.
The bucket is private and its region is us-east-1.
order_id,customer,total
1001,Asha,79.00
1002,Ravi,129.00
1003,Leah,59.00s3://YOUR_BUCKET/raw/orders.csv contains a header and three order rows.
CREATE DATABASE cloudadhar_orders;
CREATE EXTERNAL TABLE cloudadhar_orders.raw_orders (
order_id int, customer string, total double
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://YOUR_BUCKET/raw/'
TBLPROPERTIES ('skip.header.line.count'='1');
SELECT * FROM cloudadhar_orders.raw_orders ORDER BY order_id;The SELECT returns three rows; order 1002 has total 129.0.
If no output location is set, check the selected workgroup. AccessDenied can indicate S3, Glue, workgroup or Lake Formation permissions. Do not resolve it by making the bucket public.
CREATE TABLE cloudadhar_orders.orders (
order_id int, customer string, total decimal(10,2)
)
LOCATION 's3://YOUR_BUCKET/curated/orders/'
TBLPROPERTIES ('table_type'='ICEBERG', 'format'='parquet');
INSERT INTO cloudadhar_orders.orders
SELECT order_id, customer, CAST(total AS decimal(10,2))
FROM cloudadhar_orders.raw_orders;
SELECT * FROM cloudadhar_orders.orders ORDER BY order_id;Three curated rows appear. In S3, curated/orders/ now contains Iceberg data and metadata.
Do not use CREATE EXTERNAL TABLE for the Iceberg table. If you insert twice, drop and recreate this disposable curated table before continuing; otherwise the data is duplicated.
MERGE INTO cloudadhar_orders.orders t
USING (SELECT 1002 AS order_id, CAST(149.00 AS decimal(10,2)) AS total) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET total = s.total;
SELECT COUNT(*) AS order_count, SUM(total) AS revenue
FROM cloudadhar_orders.orders;
SELECT * FROM cloudadhar_orders.orders WHERE order_id = 1002;order_count = 3, revenue = 287.00, and Ravi’s total = 149.00. Save these results as your proof of work.
DROP TABLE cloudadhar_orders.orders;
DROP TABLE cloudadhar_orders.raw_orders;
DROP DATABASE cloudadhar_orders;The lab bucket, database, tables and workgroup no longer exist.
Use these service guides and architecture frameworks to go deeper. The diagram above is an original teaching design, not an AWS-certified production blueprint.