Iceberg V1: Warehouse Inventory Snapshots

Register a native Apache Iceberg v1 table (489 retail SKUs across 3 warehouses) and run inventory analytics with zero conversion to Delta.

Category: iceberg

Syntax

-- ============================================================================
-- SETUP
-- ============================================================================
-- Creates an external table backed by a native Apache Iceberg v1 table.
-- DeltaForge reads the Iceberg metadata chain directly:
-- metadata.json -> manifest list -> manifests -> Parquet data files.

-- STEP 1: Zone & Schema
CREATE ZONE IF NOT EXISTS {{zone_name}} TYPE EXTERNAL
    COMMENT 'External tables - demo datasets and file-backed data';

CREATE SCHEMA IF NOT EXISTS {{zone_name}}.iceberg_demos
    COMMENT 'Apache Iceberg native table demos';

-- STEP 2: Register the Iceberg v1 table.
-- LOCATION points to the Iceberg table root (containing metadata/ and data/).
-- DeltaForge parses metadata.json to discover schema and data files.
-- The format-version field in metadata.json is 1.
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.iceberg_demos.warehouse_inventory
USING ICEBERG
LOCATION '{{data_path}}/warehouse_inventory';

GRANT ADMIN ON TABLE {{zone_name}}.iceberg_demos.warehouse_inventory TO USER {{current_user}};


-- ============================================================================
-- QUERIES
-- ============================================================================

-- Query 1: Baseline row count - verifies the full v1 manifest chain resolved.
ASSERT ROW_COUNT = 489
SELECT * FROM {{zone_name}}.iceberg_demos.warehouse_inventory;

-- Query 2: Full schema projection - exercises all 10 Iceberg->Arrow type mappings.
ASSERT ROW_COUNT = 489
ASSERT VALUE sku IS NOT NULL WHERE sku = 'SKU-00001'
SELECT
    sku, product_name, category, warehouse,
    quantity_on_hand, reorder_point, unit_cost,
    last_restock_date, supplier, aisle_location
FROM {{zone_name}}.iceberg_demos.warehouse_inventory
ORDER BY sku;

-- Query 3: Per-warehouse row counts.
ASSERT ROW_COUNT = 3
ASSERT VALUE item_count = 159 WHERE warehouse = 'Charlotte-NC'
ASSERT VALUE item_count = 166 WHERE warehouse = 'Dallas-TX'
ASSERT VALUE item_count = 164 WHERE warehouse = 'Portland-OR'
SELECT warehouse, COUNT(*) AS item_count
FROM {{zone_name}}.iceberg_demos.warehouse_inventory
GROUP BY warehouse
ORDER BY warehouse;

-- Query 4: Per-category row counts.
ASSERT ROW_COUNT = 5
ASSERT VALUE item_count = 100 WHERE category = 'Apparel'
ASSERT VALUE item_count = 99  WHERE category = 'Electronics'
ASSERT VALUE item_count = 97  WHERE category = 'Food-Bev'
ASSERT VALUE item_count = 94  WHERE category = 'Furniture'
ASSERT VALUE item_count = 99  WHERE category = 'Industrial'
SELECT category, COUNT(*) AS item_count
FROM {{zone_name}}.iceberg_demos.warehouse_inventory
GROUP BY category
ORDER BY category;

-- Query 5: Total inventory value (SUM qty * unit_cost).
ASSERT ROW_COUNT = 1
ASSERT VALUE total_value = 17554271.58
SELECT ROUND(SUM(quantity_on_hand * unit_cost), 2) AS total_value
FROM {{zone_name}}.iceberg_demos.warehouse_inventory;

-- Query 6: Per-warehouse inventory value.
ASSERT ROW_COUNT = 3
ASSERT VALUE warehouse_value = 5047746.44 WHERE warehouse = 'Charlotte-NC'
ASSERT VALUE warehouse_value = 6234098.71 WHERE warehouse = 'Dallas-TX'
ASSERT VALUE warehouse_value = 6272426.43 WHERE warehouse = 'Portland-OR'
SELECT warehouse, ROUND(SUM(quantity_on_hand * unit_cost), 2) AS warehouse_value
FROM {{zone_name}}.iceberg_demos.warehouse_inventory
GROUP BY warehouse
ORDER BY warehouse;

-- Query 7: Items below reorder point (real-world warehouse alert).
ASSERT ROW_COUNT = 56
SELECT sku, product_name, warehouse, category, quantity_on_hand, reorder_point
FROM {{zone_name}}.iceberg_demos.warehouse_inventory
WHERE quantity_on_hand < reorder_point
ORDER BY quantity_on_hand ASC;

-- Query 8: Below-reorder counts per warehouse.
ASSERT ROW_COUNT = 3
ASSERT VALUE reorder_needed = 21 WHERE warehouse = 'Charlotte-NC'
ASSERT VALUE reorder_needed = 15 WHERE warehouse = 'Dallas-TX'
ASSERT VALUE reorder_needed = 20 WHERE warehouse = 'Portland-OR'
SELECT warehouse, COUNT(*) AS reorder_needed
FROM {{zone_name}}.iceberg_demos.warehouse_inventory
WHERE quantity_on_hand < reorder_point
GROUP BY warehouse
ORDER BY warehouse;

-- Query 9: Average unit cost by category (float aggregation).
ASSERT ROW_COUNT = 5
ASSERT VALUE avg_cost = 137.18 WHERE category = 'Apparel'
ASSERT VALUE avg_cost = 148.05 WHERE category = 'Electronics'
ASSERT VALUE avg_cost = 144.74 WHERE category = 'Food-Bev'
ASSERT VALUE avg_cost = 137.43 WHERE category = 'Furniture'
ASSERT VALUE avg_cost = 150.28 WHERE category = 'Industrial'
SELECT category, ROUND(AVG(unit_cost), 2) AS avg_cost
FROM {{zone_name}}.iceberg_demos.warehouse_inventory
GROUP BY category
ORDER BY category;

-- Query 10: Supplier distribution.
ASSERT ROW_COUNT = 5
ASSERT VALUE item_count = 88  WHERE supplier = 'Acme Corp'
ASSERT VALUE item_count = 101 WHERE supplier = 'EcoSupply'
ASSERT VALUE item_count = 82  WHERE supplier = 'GlobalTrade'
ASSERT VALUE item_count = 108 WHERE supplier = 'PrimeParts'
ASSERT VALUE item_count = 110 WHERE supplier = 'QuickShip'
SELECT supplier, COUNT(*) AS item_count
FROM {{zone_name}}.iceberg_demos.warehouse_inventory
GROUP BY supplier
ORDER BY supplier;

-- Query 11: High-value inventory items (compound predicate with arithmetic).
ASSERT ROW_COUNT = 372
SELECT
    sku, product_name, warehouse,
    quantity_on_hand, unit_cost,
    ROUND(quantity_on_hand * unit_cost, 2) AS line_value
FROM {{zone_name}}.iceberg_demos.warehouse_inventory
WHERE quantity_on_hand * unit_cost > 10000
ORDER BY line_value DESC;

-- VERIFY: cross-cutting sanity check.
ASSERT ROW_COUNT = 1
ASSERT VALUE total_rows = 489
ASSERT VALUE total_value = 17554271.58
ASSERT VALUE warehouse_count = 3
ASSERT VALUE category_count = 5
ASSERT VALUE supplier_count = 5
ASSERT VALUE below_reorder = 56
SELECT
    COUNT(*) AS total_rows,
    ROUND(SUM(quantity_on_hand * unit_cost), 2) AS total_value,
    COUNT(DISTINCT warehouse) AS warehouse_count,
    COUNT(DISTINCT category) AS category_count,
    COUNT(DISTINCT supplier) AS supplier_count,
    SUM(CASE WHEN quantity_on_hand < reorder_point THEN 1 ELSE 0 END) AS below_reorder
FROM {{zone_name}}.iceberg_demos.warehouse_inventory;


-- ============================================================================
-- CLEANUP
-- ============================================================================

-- WITH FILES removes both the catalog entry and the underlying Iceberg table
-- files. Omit WITH FILES if the files are shared with other demos.
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.iceberg_demos.warehouse_inventory WITH FILES;

DROP SCHEMA IF EXISTS {{zone_name}}.iceberg_demos;
DROP ZONE IF EXISTS {{zone_name}};

Description

## When to Use Reach for this demo when you need to prove that DeltaForge can read an existing Apache Iceberg **format-version 1** table produced by Spark/Trino/Flink without any conversion step. V1 is the original Iceberg spec: schema lives in `metadata.json`, file lists live in Avro manifest lists and manifests, and data lives in plain Parquet. This is the simplest possible Iceberg read path, if this works, your metadata-chain parsing is correct end to end. ## What You Will Learn 1. How `CREATE EXTERNAL TABLE ... USING ICEBERG LOCATION ...` auto-discovers schema and data files from `metadata.json`. 2. How DeltaForge walks the v1 chain: `metadata.json` → manifest list (Avro) → data manifest (Avro) → Parquet data files. 3. How Iceberg types map to Arrow/DataFusion (strings, ints, floats, dates) without explicit DDL. 4. Using `ASSERT ROW_COUNT` and `ASSERT VALUE` to pin expected results for regression testing. 5. GROUP BY / aggregation queries against an Iceberg table (inventory value, reorder-point alerts, supplier mix). 6. Predicate pushdown on integer columns via manifest statistics. 7. The scope difference between `DROP EXTERNAL TABLE` and `DROP EXTERNAL TABLE ... WITH FILES` in cleanup. 8. Why a v1 demo does **not** exercise position deletes, equality deletes, or deletion vectors (those arrive in v2/v3). ## Prerequisites You need a pre-generated Iceberg v1 table at `{{data_path}}/warehouse_inventory` containing the standard `metadata/` and `data/` subdirectories. The demo seeds 489 rows with 10 columns (`sku`, `product_name`, `category`, `warehouse`, `quantity_on_hand`, `reorder_point`, `unit_cost`, `last_restock_date`, `supplier`, `aisle_location`). No Delta conversion, no write path, and no Iceberg catalog service are required: DeltaForge reads the metadata directly from the filesystem/object store.

Pitfalls

LOCATION must point to the Iceberg table root (the directory containing metadata/ and data/), NOT directly at a .metadata.json file or at the data/ folder: DeltaForge discovers the latest metadata.json on its own.
Iceberg v1 has no position deletes, equality deletes, or deletion vectors. Every row in every referenced Parquet file is live, if you see unexpectedly many rows, suspect stale manifests, not a delete bug.
The v1 metadata.json carries the authoritative schema; DeltaForge does NOT fall back to Parquet footers. If you rewrite the Parquet but forget to publish a new metadata.json + manifest pair, the reader will still see the old schema.
USING ICEBERG creates a read-only external table. Writes (INSERT / UPDATE / MERGE) are rejected, use a Delta table or the UniForm writer when you need write paths.
DROP EXTERNAL TABLE alone only removes the catalog entry; the Iceberg files remain on disk. Use `DROP EXTERNAL TABLE ... WITH FILES` in this demo so re-running setup sees a clean location.
The single Parquet data file in this demo means manifest-level min/max pruning never reduces the scan to zero files, predicate pushdown still applies inside the Parquet footer, but file-level skipping requires multiple data files to be observable.

Iceberg V1: Warehouse Inventory Snapshots

Syntax

Description

Pitfalls

See Also