Dremio Blog

28 minute read · February 27, 2026

Optimize Supply Chain Analytics on Dremio Cloud

Alex Merced Alex Merced Head of DevRel, Dremio
Start For Free
Optimize Supply Chain Analytics on Dremio Cloud
Copied to clipboard

Table of Contents

Optimize Supply Chain Analytics on Dremio Cloud

What You'll Build
Step 1: Sign Up for Dremio Cloud
Step 2: Create Folders and Seed Data
Suppliers Table (ERP)
Purchase Orders Table (ERP)
Inventory Table (Warehouse)
Shipments Table (Warehouse)
Why Apache Iceberg Matters for Supply Chain Data
Iceberg vs. Federated: When to Use Which
Step 3: Build Bronze Views
Step 4: Build Silver Views
Supplier Order Performance
Shipment Quality
Step 5: Build Gold Views
Supplier Performance Scorecard
Inventory Risk Assessment
Step 6: Enable AI-Generated Wikis and Tags
Step 7: Ask Questions with the AI Agent
Next Steps

Supply chain teams operate across ERP systems, warehouse management platforms, and IoT sensor networks. When a product manager asks "Which suppliers are causing the most delivery delays, and do we have enough safety stock to cover it?", answering requires data from all three systems. Most organizations can't answer that question without a week of manual Excel consolidation.

This tutorial shows you how to build a supply chain analytics pipeline on Dremio Cloud that unifies procurement, warehouse, and sensor data. You'll seed sample datasets, model them through Bronze, Silver, and Gold views, and use the AI Agent to evaluate supplier performance and inventory risk through natural language questions.

What You'll Build

  • Source tables simulating an ERP system (Oracle), warehouse management (PostgreSQL), and IoT sensors (S3)
  • Bronze views that standardize types and naming
  • Silver views that join supplier data with purchase orders and shipments
  • Gold views for supplier performance scoring and inventory risk assessment
  • AI-generated wikis and tags for operational context
  • Natural language supply chain analytics using Dremio's AI Agent

Try Dremio’s Interactive Demo

Explore this interactive demo and see how Dremio's Intelligent Lakehouse enables Agentic AI

Step 1: Sign Up for Dremio Cloud

Go to dremio.com/get-started and create a free trial account. You get 30 days and $400 in compute credits. Your trial includes a project with an Open Catalog and managed storage. Navigate to the SQL Runner from the left sidebar.

Step 2: Create Folders and Seed Data

Create folders to simulate the different operational systems:

CREATE FOLDER IF NOT EXISTS supplychainexp;
CREATE FOLDER IF NOT EXISTS supplychainexp.oracle;
CREATE FOLDER IF NOT EXISTS supplychainexp.postgres;
CREATE FOLDER IF NOT EXISTS supplychainexp.s3;

Note: Tables in the built-in Open Catalog use folder.subfolder.table_name without a catalog prefix. External sources use source_name.schema.table_name.

Suppliers Table (ERP)

CREATE TABLE supplychainexp.oracle.suppliers (
  supplier_id INT,
  supplier_name VARCHAR,
  country VARCHAR,
  category VARCHAR,
  lead_time_days INT,
  contract_status VARCHAR,
  reliability_rating DECIMAL(3,2)
);

INSERT INTO supplychainexp.oracle.suppliers VALUES
(1,'Precision Parts Co','USA','mechanical',14,'active',0.95),
(2,'Global Electronics','China','electronic',28,'active',0.88),
(3,'Euro Components','Germany','mechanical',21,'active',0.97),
(4,'Pacific Materials','Japan','raw_material',35,'active',0.92),
(5,'Atlas Packaging','Mexico','packaging',10,'active',0.90),
(6,'Nordic Sensors','Sweden','electronic',25,'active',0.94),
(7,'Southern Steel','Brazil','raw_material',30,'active',0.85),
(8,'UK Plastics Ltd','UK','raw_material',18,'active',0.91),
(9,'Midwest Fasteners','USA','mechanical',7,'active',0.96),
(10,'Shenzhen Circuits','China','electronic',32,'active',0.82),
(11,'Bavaria Machining','Germany','mechanical',20,'active',0.98),
(12,'Ontario Chemicals','Canada','raw_material',12,'active',0.93),
(13,'Delta Logistics','USA','packaging',5,'active',0.89),
(14,'Taiwan Semiconductors','Taiwan','electronic',30,'active',0.91),
(15,'Rhine Valley Glass','Germany','raw_material',22,'active',0.95),
(16,'Coastal Rubber','USA','raw_material',8,'active',0.87),
(17,'Seoul Tech Parts','South Korea','electronic',26,'active',0.93),
(18,'Alpine Precision','Switzerland','mechanical',18,'active',0.99),
(19,'Mumbai Materials','India','raw_material',38,'active',0.80),
(20,'Great Lakes Coatings','USA','raw_material',10,'active',0.92);

Purchase Orders Table (ERP)

CREATE TABLE supplychainexp.oracle.purchase_orders (
  po_id INT,
  supplier_id INT,
  order_date DATE,
  expected_delivery DATE,
  actual_delivery DATE,
  item_description VARCHAR,
  quantity INT,
  unit_price DECIMAL(10,2),
  status VARCHAR
);

INSERT INTO supplychainexp.oracle.purchase_orders VALUES
(2001,1,'2024-01-10','2024-01-24','2024-01-23','Precision bearings',500,12.50,'delivered'),
(2002,2,'2024-01-15','2024-02-12','2024-02-18','PCB assemblies',200,45.00,'delivered'),
(2003,3,'2024-01-20','2024-02-10','2024-02-09','CNC machined parts',300,28.00,'delivered'),
(2004,4,'2024-01-25','2024-03-01','2024-03-08','Aluminum sheets',1000,8.50,'delivered'),
(2005,5,'2024-02-01','2024-02-11','2024-02-11','Cardboard packaging',5000,0.45,'delivered'),
(2006,6,'2024-02-05','2024-03-02','2024-03-01','Temperature sensors',150,32.00,'delivered'),
(2007,7,'2024-02-10','2024-03-12','2024-03-20','Steel rods',800,6.75,'delivered'),
(2008,8,'2024-02-15','2024-03-05','2024-03-04','Polycarbonate panels',400,15.20,'delivered'),
(2009,9,'2024-02-20','2024-02-27','2024-02-26','M8 bolts',10000,0.12,'delivered'),
(2010,10,'2024-02-25','2024-03-29','2024-04-08','LED modules',300,22.00,'delivered'),
(2011,11,'2024-03-01','2024-03-21','2024-03-20','Precision gears',250,35.00,'delivered'),
(2012,12,'2024-03-05','2024-03-17','2024-03-17','Industrial adhesive',200,18.50,'delivered'),
(2013,13,'2024-03-10','2024-03-15','2024-03-14','Shipping crates',1000,2.80,'delivered'),
(2014,14,'2024-03-15','2024-04-14','2024-04-20','Microcontrollers',500,8.90,'delivered'),
(2015,15,'2024-03-20','2024-04-11','2024-04-10','Borosilicate tubes',150,42.00,'delivered'),
(2016,1,'2024-03-25','2024-04-08','2024-04-07','Precision bearings',600,12.50,'delivered'),
(2017,2,'2024-04-01','2024-04-29','2024-05-05','PCB assemblies',250,44.00,'delivered'),
(2018,3,'2024-04-05','2024-04-26','2024-04-25','CNC machined parts',350,27.50,'delivered'),
(2019,16,'2024-04-10','2024-04-18','2024-04-22','Rubber gaskets',2000,1.20,'delivered'),
(2020,17,'2024-04-15','2024-05-11','2024-05-10','Flex cables',400,9.50,'delivered'),
(2021,18,'2024-04-20','2024-05-08','2024-05-07','Swiss machined pins',1000,4.80,'delivered'),
(2022,19,'2024-04-25','2024-06-02','2024-06-12','Copper wire',500,14.00,'delivered'),
(2023,20,'2024-05-01','2024-05-11','2024-05-10','Powder coating',300,22.50,'delivered'),
(2024,4,'2024-05-05','2024-06-09','2024-06-15','Aluminum sheets',1200,8.30,'delivered'),
(2025,5,'2024-05-10','2024-05-20','2024-05-20','Foam inserts',3000,0.65,'delivered'),
(2026,7,'2024-05-15','2024-06-14','2024-06-22','Steel rods',900,6.50,'delivered'),
(2027,9,'2024-05-20','2024-05-27','2024-05-27','M10 bolts',8000,0.15,'delivered'),
(2028,10,'2024-05-25','2024-06-26','2024-07-05','LED modules',350,21.50,'delivered'),
(2029,11,'2024-06-01','2024-06-21','2024-06-20','Precision gears',200,36.00,'delivered'),
(2030,6,'2024-06-05','2024-07-01','2024-06-30','Pressure sensors',180,38.00,'delivered'),
(2031,8,'2024-06-10','2024-06-28','2024-06-27','ABS panels',500,12.80,'delivered'),
(2032,12,'2024-06-15','2024-06-27','2024-06-28','Epoxy resin',250,24.00,'delivered'),
(2033,13,'2024-06-20','2024-06-25','2024-06-25','Bubble wrap rolls',2000,1.10,'delivered'),
(2034,14,'2024-06-25','2024-07-25',NULL,'FPGA chips',200,52.00,'in_transit'),
(2035,15,'2024-07-01','2024-07-23',NULL,'Glass lenses',100,65.00,'in_transit'),
(2036,1,'2024-07-05','2024-07-19',NULL,'Precision bearings',700,12.30,'ordered'),
(2037,3,'2024-07-10','2024-07-31',NULL,'CNC machined parts',400,27.00,'ordered'),
(2038,18,'2024-07-15','2024-08-02',NULL,'Swiss machined pins',1200,4.60,'ordered'),
(2039,2,'2024-07-20','2024-08-17',NULL,'PCB assemblies',300,43.50,'ordered'),
(2040,19,'2024-07-25','2024-09-01',NULL,'Copper wire',600,13.80,'ordered'),
(2041,4,'2024-08-01','2024-09-05',NULL,'Titanium alloy',500,32.00,'ordered'),
(2042,6,'2024-08-05','2024-08-30',NULL,'Vibration sensors',200,45.00,'ordered'),
(2043,17,'2024-08-10','2024-09-05',NULL,'Ribbon cables',350,7.80,'ordered'),
(2044,7,'2024-08-15','2024-09-14',NULL,'Stainless rods',750,7.20,'ordered'),
(2045,20,'2024-08-20','2024-08-30',NULL,'Anodizing service',400,5.50,'ordered'),
(2046,9,'2024-08-25','2024-09-01',NULL,'M6 screws',15000,0.08,'ordered'),
(2047,10,'2024-09-01','2024-10-03',NULL,'OLED panels',150,68.00,'ordered'),
(2048,16,'2024-09-05','2024-09-13',NULL,'Silicone seals',3000,0.90,'ordered'),
(2049,11,'2024-09-10','2024-09-30',NULL,'Worm gears',180,42.00,'ordered'),
(2050,5,'2024-09-15','2024-09-25',NULL,'Shrink wrap',4000,0.35,'ordered');

Inventory Table (Warehouse)

CREATE TABLE supplychainexp.postgres.inventory (
  sku VARCHAR,
  item_name VARCHAR,
  warehouse VARCHAR,
  quantity_on_hand INT,
  reorder_point INT,
  reorder_quantity INT,
  unit_cost DECIMAL(10,2),
  last_counted DATE
);

INSERT INTO supplychainexp.postgres.inventory VALUES
('SKU-001','Precision bearings','WH-East',320,200,500,12.50,'2024-07-01'),
('SKU-002','PCB assemblies','WH-East',45,100,200,45.00,'2024-07-01'),
('SKU-003','CNC machined parts','WH-West',280,150,300,28.00,'2024-07-01'),
('SKU-004','Aluminum sheets','WH-East',850,500,1000,8.50,'2024-07-01'),
('SKU-005','Cardboard packaging','WH-Central',3200,2000,5000,0.45,'2024-07-01'),
('SKU-006','Temperature sensors','WH-West',90,50,150,32.00,'2024-07-01'),
('SKU-007','Steel rods','WH-East',150,300,800,6.75,'2024-07-01'),
('SKU-008','Polycarbonate panels','WH-West',310,200,400,15.20,'2024-07-01'),
('SKU-009','M8 bolts','WH-Central',7500,5000,10000,0.12,'2024-07-01'),
('SKU-010','LED modules','WH-East',25,80,300,22.00,'2024-07-01'),
('SKU-011','Precision gears','WH-West',185,100,250,35.00,'2024-07-01'),
('SKU-012','Industrial adhesive','WH-Central',140,100,200,18.50,'2024-07-01'),
('SKU-013','Shipping crates','WH-East',650,400,1000,2.80,'2024-07-01'),
('SKU-014','Microcontrollers','WH-West',380,200,500,8.90,'2024-07-01'),
('SKU-015','Borosilicate tubes','WH-East',65,50,150,42.00,'2024-07-01'),
('SKU-016','Rubber gaskets','WH-Central',1800,1000,2000,1.20,'2024-07-01'),
('SKU-017','Flex cables','WH-West',290,150,400,9.50,'2024-07-01'),
('SKU-018','Swiss machined pins','WH-East',750,500,1000,4.80,'2024-07-01'),
('SKU-019','Copper wire','WH-Central',80,200,500,14.00,'2024-07-01'),
('SKU-020','Powder coating','WH-West',220,150,300,22.50,'2024-07-01'),
('SKU-021','Foam inserts','WH-Central',2100,1500,3000,0.65,'2024-07-01'),
('SKU-022','M10 bolts','WH-East',6200,4000,8000,0.15,'2024-07-01'),
('SKU-023','Pressure sensors','WH-West',120,80,180,38.00,'2024-07-01'),
('SKU-024','ABS panels','WH-East',380,250,500,12.80,'2024-07-01'),
('SKU-025','Epoxy resin','WH-Central',160,120,250,24.00,'2024-07-01'),
('SKU-026','Bubble wrap rolls','WH-East',1400,1000,2000,1.10,'2024-07-01'),
('SKU-027','FPGA chips','WH-West',150,100,200,52.00,'2024-07-01'),
('SKU-028','Glass lenses','WH-East',40,30,100,65.00,'2024-07-01'),
('SKU-029','Ribbon cables','WH-West',260,150,350,7.80,'2024-07-01'),
('SKU-030','Stainless rods','WH-East',120,250,750,7.20,'2024-07-01'),
('SKU-031','Anodizing service','WH-Central',300,200,400,5.50,'2024-07-01'),
('SKU-032','M6 screws','WH-East',12000,8000,15000,0.08,'2024-07-01'),
('SKU-033','OLED panels','WH-West',30,50,150,68.00,'2024-07-01'),
('SKU-034','Silicone seals','WH-Central',2500,1500,3000,0.90,'2024-07-01'),
('SKU-035','Worm gears','WH-West',95,80,180,42.00,'2024-07-01'),
('SKU-036','Shrink wrap','WH-Central',2800,2000,4000,0.35,'2024-07-01'),
('SKU-037','Titanium alloy','WH-East',60,100,500,32.00,'2024-07-01'),
('SKU-038','Vibration sensors','WH-West',70,60,200,45.00,'2024-07-01'),
('SKU-039','Spring washers','WH-Central',9500,6000,12000,0.05,'2024-07-01'),
('SKU-040','Heat sinks','WH-East',200,120,300,11.00,'2024-07-01'),
('SKU-041','Ceramic capacitors','WH-West',4500,3000,6000,0.22,'2024-07-01'),
('SKU-042','Nylon spacers','WH-Central',3800,2500,5000,0.18,'2024-07-01'),
('SKU-043','Servo motors','WH-East',35,40,100,85.00,'2024-07-01'),
('SKU-044','Optical filters','WH-West',55,40,80,48.00,'2024-07-01'),
('SKU-045','Carbon fiber sheets','WH-East',25,30,60,92.00,'2024-07-01'),
('SKU-046','Ferrite cores','WH-Central',1200,800,2000,3.20,'2024-07-01'),
('SKU-047','Linear bearings','WH-West',110,80,200,19.50,'2024-07-01'),
('SKU-048','Thermal paste','WH-East',400,250,500,6.80,'2024-07-01'),
('SKU-049','Braided cable','WH-Central',550,400,800,4.50,'2024-07-01'),
('SKU-050','Toggle switches','WH-West',680,500,1000,2.40,'2024-07-01');

Shipments Table (Warehouse)

CREATE TABLE supplychainexp.postgres.shipments (
  shipment_id INT,
  po_id INT,
  ship_date DATE,
  arrival_date DATE,
  carrier VARCHAR,
  tracking_status VARCHAR,
  items_received INT,
  items_damaged INT
);

INSERT INTO supplychainexp.postgres.shipments VALUES
(3001,2001,'2024-01-21','2024-01-23','FedEx','delivered',500,2),
(3002,2002,'2024-02-14','2024-02-18','DHL','delivered',200,8),
(3003,2003,'2024-02-07','2024-02-09','UPS','delivered',300,1),
(3004,2004,'2024-03-04','2024-03-08','Maersk','delivered',1000,15),
(3005,2005,'2024-02-09','2024-02-11','FedEx','delivered',5000,0),
(3006,2006,'2024-02-27','2024-03-01','DHL','delivered',150,0),
(3007,2007,'2024-03-16','2024-03-20','Maersk','delivered',800,12),
(3008,2008,'2024-03-02','2024-03-04','UPS','delivered',400,3),
(3009,2009,'2024-02-25','2024-02-26','FedEx','delivered',10000,5),
(3010,2010,'2024-04-02','2024-04-08','DHL','delivered',300,10),
(3011,2011,'2024-03-18','2024-03-20','UPS','delivered',250,0),
(3012,2012,'2024-03-15','2024-03-17','FedEx','delivered',200,1),
(3013,2013,'2024-03-13','2024-03-14','UPS','delivered',1000,0),
(3014,2014,'2024-04-16','2024-04-20','DHL','delivered',500,6),
(3015,2015,'2024-04-08','2024-04-10','FedEx','delivered',150,0),
(3016,2016,'2024-04-05','2024-04-07','FedEx','delivered',600,3),
(3017,2017,'2024-05-01','2024-05-05','DHL','delivered',250,9),
(3018,2018,'2024-04-23','2024-04-25','UPS','delivered',350,1),
(3019,2019,'2024-04-18','2024-04-22','FedEx','delivered',2000,8),
(3020,2020,'2024-05-08','2024-05-10','DHL','delivered',400,2),
(3021,2021,'2024-05-05','2024-05-07','UPS','delivered',1000,0),
(3022,2022,'2024-06-08','2024-06-12','Maersk','delivered',500,7),
(3023,2023,'2024-05-08','2024-05-10','FedEx','delivered',300,1),
(3024,2024,'2024-06-11','2024-06-15','Maersk','delivered',1200,18),
(3025,2025,'2024-05-18','2024-05-20','FedEx','delivered',3000,0),
(3026,2026,'2024-06-18','2024-06-22','Maersk','delivered',900,14),
(3027,2027,'2024-05-25','2024-05-27','FedEx','delivered',8000,4),
(3028,2028,'2024-07-01','2024-07-05','DHL','delivered',350,11),
(3029,2029,'2024-06-18','2024-06-20','UPS','delivered',200,0),
(3030,2030,'2024-06-28','2024-06-30','DHL','delivered',180,1),
(3031,2031,'2024-06-25','2024-06-27','UPS','delivered',500,2),
(3032,2032,'2024-06-25','2024-06-28','FedEx','delivered',250,2),
(3033,2033,'2024-06-23','2024-06-25','UPS','delivered',2000,0),
(3034,2034,'2024-07-20',NULL,'DHL','in_transit',NULL,NULL),
(3035,2035,'2024-07-18',NULL,'FedEx','in_transit',NULL,NULL),
(3036,2036,NULL,NULL,NULL,'pending',NULL,NULL),
(3037,2037,NULL,NULL,NULL,'pending',NULL,NULL),
(3038,2038,NULL,NULL,NULL,'pending',NULL,NULL),
(3039,2039,NULL,NULL,NULL,'pending',NULL,NULL),
(3040,2040,NULL,NULL,NULL,'pending',NULL,NULL),
(3041,2041,NULL,NULL,NULL,'pending',NULL,NULL),
(3042,2042,NULL,NULL,NULL,'pending',NULL,NULL),
(3043,2043,NULL,NULL,NULL,'pending',NULL,NULL),
(3044,2044,NULL,NULL,NULL,'pending',NULL,NULL),
(3045,2045,NULL,NULL,NULL,'pending',NULL,NULL),
(3046,2046,NULL,NULL,NULL,'pending',NULL,NULL),
(3047,2047,NULL,NULL,NULL,'pending',NULL,NULL),
(3048,2048,NULL,NULL,NULL,'pending',NULL,NULL),
(3049,2049,NULL,NULL,NULL,'pending',NULL,NULL),
(3050,2050,NULL,NULL,NULL,'pending',NULL,NULL);

Why Apache Iceberg Matters for Supply Chain Data

Supply chain data grows fast and changes constantly. Apache Iceberg is built for this.

Inventory Snapshots with Time Travel. Iceberg tracks every change to your tables as immutable snapshots. When your CFO asks "What was our inventory position at the end of Q2?", you query the table at that timestamp directly. No need to maintain separate snapshot tables or end-of-day batch jobs.

Schema Evolution for Changing Requirements. Supply chains add new data points constantly: new sensor types, new compliance fields, new supplier attributes. Iceberg lets you add columns without rewriting existing data, so historical records stay intact.

Automated Performance Management. Dremio automatically compacts small files, rewrites manifests, clusters data, and vacuums old snapshots on Iceberg tables in the Open Catalog. IoT sensor tables that receive thousands of inserts per day stay performant without DBA intervention.

Autonomous Reflections and Results Cache. Supply chain dashboards tend to run the same queries: inventory levels, supplier on-time delivery rates, daily shipment counts. Dremio detects these patterns and automatically creates pre-computed Reflections. Combined with the Results Cache for identical queries, your supply chain KPIs return in sub-second time.

Iceberg vs. Federated: When to Use Which

In this tutorial, all tables are Iceberg because they're in the Open Catalog. In production, the decision depends on how operational your data needs to be.

Keep data federated when you need real-time inventory or order status. Your ERP system updates purchase order statuses throughout the day. A warehouse management system tracks inventory movements as they happen. If you replicate this data to Iceberg on an hourly ETL schedule, your operations team could be making stocking decisions based on outdated inventory counts. By querying these systems directly through Dremio's federation, every query reflects the latest state. You can still create manual Reflections on federated views to accelerate dashboard queries, and you control the refresh cadence (every 5 minutes, every hour) to balance speed against freshness.

Migrate data to Iceberg when the data is historical or analytical. Completed purchase orders, historical shipment records, and past IoT sensor readings don't change. These datasets benefit from Dremio's automatic compaction, manifest optimization, clustering, vacuuming, and eligibility for Autonomous Reflections without any freshness trade-off.

The practical approach for supply chain: federate your live ERP and warehouse systems for real-time operational visibility. Migrate historical procurement data, completed shipment records, and aggregated quality metrics to Iceberg for automated optimization.

Step 3: Build Bronze Views

Bronze views create a consistent interface over the raw operational tables. Supply chain systems are notorious for inconsistent data models: your ERP stores dates one way, your warehouse system uses different column naming, and IoT platforms use yet another schema. The Bronze layer normalizes these differences so all downstream analysis works against a predictable schema.

Create the layer folders:

CREATE FOLDER IF NOT EXISTS supplychainexp.bronze;
CREATE FOLDER IF NOT EXISTS supplychainexp.silver;
CREATE FOLDER IF NOT EXISTS supplychainexp.gold;

This view passes through the supplier master data. The fields are already clean, so the Bronze view simply provides a consistent reference point that other views can join against.

CREATE VIEW supplychainexp.bronze.erp_suppliers AS
SELECT
  supplier_id,
  supplier_name,
  country,
  category,
  lead_time_days,
  contract_status,
  reliability_rating
FROM supplychainexp.oracle.suppliers;

The purchase orders view casts all three date fields (order, expected delivery, actual delivery) to timestamps. The actual_delivery can be NULL for orders that haven't arrived yet. We rename status to po_status to avoid naming conflicts when we join with shipments.

CREATE VIEW supplychainexp.bronze.erp_purchase_orders AS
SELECT
  po_id,
  supplier_id,
  CAST(order_date AS TIMESTAMP) AS order_timestamp,
  CAST(expected_delivery AS TIMESTAMP) AS expected_delivery_timestamp,
  CAST(actual_delivery AS TIMESTAMP) AS actual_delivery_timestamp,
  item_description,
  quantity,
  unit_price,
  status AS po_status
FROM supplychainexp.oracle.purchase_orders;

The inventory view brings in current stock levels with the reorder parameters. The last_counted date tells us how fresh the physical inventory count is, which matters for accuracy assessments.

CREATE VIEW supplychainexp.bronze.wh_inventory AS
SELECT
  sku,
  item_name,
  warehouse,
  quantity_on_hand,
  reorder_point,
  reorder_quantity,
  unit_cost,
  CAST(last_counted AS TIMESTAMP) AS last_counted_timestamp
FROM supplychainexp.postgres.inventory;

The shipments view casts dates and includes the quality metrics (items_received, items_damaged). Shipments with NULL values for received/damaged items are either in transit or pending, which we'll filter appropriately in downstream views.

CREATE VIEW supplychainexp.bronze.wh_shipments AS
SELECT
  shipment_id,
  po_id,
  CAST(ship_date AS TIMESTAMP) AS ship_timestamp,
  CAST(arrival_date AS TIMESTAMP) AS arrival_timestamp,
  carrier,
  tracking_status,
  items_received,
  items_damaged
FROM supplychainexp.postgres.shipments;

Step 4: Build Silver Views

Silver views join procurement and logistics data to create actionable supplier and quality metrics. Each Silver view answers a specific operational question that requires data from multiple systems.

Supplier Order Performance

This view answers: "How is each supplier actually performing vs. their contracted terms?" It joins suppliers with purchase orders to compute total order volume, total spend, late delivery count, and average actual lead time. The late delivery calculation compares actual_delivery_timestamp against expected_delivery_timestamp for completed orders. This is the core metric that procurement teams use during quarterly business reviews with suppliers.

CREATE VIEW supplychainexp.silver.supplier_orders AS
SELECT
  s.supplier_id,
  s.supplier_name,
  s.country,
  s.category,
  s.lead_time_days,
  s.reliability_rating,
  COUNT(po.po_id) AS total_orders,
  SUM(po.quantity * po.unit_price) AS total_order_value,
  SUM(CASE WHEN po.actual_delivery_timestamp IS NOT NULL
    AND po.actual_delivery_timestamp > po.expected_delivery_timestamp THEN 1 ELSE 0 END) AS late_deliveries,
  AVG(CASE WHEN po.actual_delivery_timestamp IS NOT NULL
    THEN TIMESTAMPDIFF(DAY, po.order_timestamp, po.actual_delivery_timestamp)
    ELSE NULL END) AS avg_actual_lead_days
FROM supplychainexp.bronze.erp_suppliers s
LEFT JOIN supplychainexp.bronze.erp_purchase_orders po ON s.supplier_id = po.supplier_id
GROUP BY s.supplier_id, s.supplier_name, s.country, s.category, s.lead_time_days, s.reliability_rating;

Shipment Quality

This view answers: "What is the incoming quality for each supplier's shipments?" It joins purchase orders with shipment inspection data to compute a damage_rate (items damaged divided by items received). This is filtered to only delivered shipments because in-transit and pending shipments haven't been inspected yet. A supplier might deliver on time but consistently ship damaged goods, which this view reveals.

CREATE VIEW supplychainexp.silver.shipment_quality AS
SELECT
  po.po_id,
  po.supplier_id,
  s.supplier_name,
  po.item_description,
  po.quantity AS ordered_quantity,
  sh.items_received,
  sh.items_damaged,
  sh.carrier,
  CASE
    WHEN sh.items_received IS NOT NULL AND sh.items_received > 0
    THEN CAST(sh.items_damaged AS DECIMAL(10,4)) / CAST(sh.items_received AS DECIMAL(10,4))
    ELSE 0
  END AS damage_rate
FROM supplychainexp.bronze.erp_purchase_orders po
JOIN supplychainexp.bronze.erp_suppliers s ON po.supplier_id = s.supplier_id
LEFT JOIN supplychainexp.bronze.wh_shipments sh ON po.po_id = sh.po_id
WHERE sh.tracking_status = 'delivered';

Step 5: Build Gold Views

Gold views are the final layer that procurement managers, warehouse supervisors, and the AI Agent query directly. Each Gold view pre-computes a specific business decision so the end user gets actionable intelligence.

Supplier Performance Scorecard

This is the primary view for procurement teams. It computes an on_time_rate (1 minus late deliveries divided by total orders) and a supplier_health classification. Suppliers with 2+ late deliveries and a reliability rating below 0.85 are flagged "At Risk," triggering a formal review process. Those with any late delivery or a rating below 0.90 are on "Watch." This enables proactive supplier management rather than reacting to disruptions after they've already hit the production line.

CREATE VIEW supplychainexp.gold.supplier_performance AS
SELECT
  so.supplier_id,
  so.supplier_name,
  so.country,
  so.category,
  so.total_orders,
  so.total_order_value,
  so.late_deliveries,
  so.avg_actual_lead_days,
  so.reliability_rating,
  CASE
    WHEN so.total_orders > 0
    THEN ROUND(1.0 - (CAST(so.late_deliveries AS DECIMAL(10,4)) / CAST(so.total_orders AS DECIMAL(10,4))), 2)
    ELSE 1.0
  END AS on_time_rate,
  CASE
    WHEN so.late_deliveries >= 2 AND so.reliability_rating < 0.85 THEN 'At Risk'
    WHEN so.late_deliveries >= 1 OR so.reliability_rating < 0.90 THEN 'Watch'
    ELSE 'Healthy'
  END AS supplier_health
FROM supplychainexp.silver.supplier_orders so;

Inventory Risk Assessment

This view translates raw inventory counts into actionable stock status classifications. It compares quantity_on_hand against the reorder_point to flag items that need immediate replenishment ("Below Reorder Point"), items approaching the threshold ("Low Stock" when within 20% of reorder point), and adequate stock levels. The recommended_action column maps directly to procurement workflows: "Order Now" means create a PO today, "Plan Reorder" means schedule it this week. The inventory_value calculation helps prioritize by financial impact since running out of a $92 carbon fiber sheet matters more than running low on $0.08 screws.

CREATE VIEW supplychainexp.gold.inventory_risk AS
SELECT
  inv.sku,
  inv.item_name,
  inv.warehouse,
  inv.quantity_on_hand,
  inv.reorder_point,
  inv.reorder_quantity,
  inv.unit_cost,
  inv.quantity_on_hand * inv.unit_cost AS inventory_value,
  CASE
    WHEN inv.quantity_on_hand <= 0 THEN 'Stock Out'
    WHEN inv.quantity_on_hand < inv.reorder_point THEN 'Below Reorder Point'
    WHEN inv.quantity_on_hand < inv.reorder_point * 1.2 THEN 'Low Stock'
    ELSE 'Adequate'
  END AS stock_status,
  CASE
    WHEN inv.quantity_on_hand < inv.reorder_point THEN 'Order Now'
    WHEN inv.quantity_on_hand < inv.reorder_point * 1.2 THEN 'Plan Reorder'
    ELSE 'No Action'
  END AS recommended_action
FROM supplychainexp.bronze.wh_inventory inv;

Step 6: Enable AI-Generated Wikis and Tags

  1. Click Admin in the left sidebar, then go to Project Settings.
  2. Select the Preferences tab.
  3. Scroll to the AI section and enable Generate Wikis and Labels.
  4. Go to the Catalog and navigate to your Gold views under supplychainexp.gold.
  5. Click the Edit button (pencil icon) next to the desired view.
  6. In the Details tab, find the Wiki section and click Generate Wiki. Do the same for the Tags section by clicking Generate Tags.
  7. Repeat for each Gold view.

To enhance the generated wikis with operational context, copy the output into the AI Agent on the homepage and ask it to produce an improved version in a markdown code block. For example, add details like: "On-time rate below 0.90 triggers a supplier review meeting. Stock status of 'Below Reorder Point' means a purchase order should be created within 24 hours." Copy the refined output and paste it back into the wiki editor.

Step 7: Ask Questions with the AI Agent

Navigate to the Homepage and use the AI Agent:

"Which suppliers are At Risk?"

The Agent queries supplychainexp.gold.supplier_performance and returns suppliers with multiple late deliveries and low reliability ratings.

"Show me a chart of on-time delivery rate by supplier country."

The Agent generates a bar chart comparing supplier performance across geographies. This reveals whether certain regions consistently cause delays.

"What items are below their reorder point?"

The Agent queries supplychainexp.gold.inventory_risk and returns SKUs that need immediate replenishment, along with the recommended action.

"Create a chart showing inventory value by warehouse and stock status."

The Agent generates a stacked bar chart showing how much capital is tied up in each warehouse, broken down by risk category. This helps procurement teams prioritize where to allocate their budget.

Next Steps

  • Connect real ERP and warehouse systems. Replace the simulated folders with federated connections to SAP, Oracle, or your WMS.
  • Add IoT data. Connect your S3 bucket with sensor readings for real-time quality monitoring.
  • Use FGAC for supplier pricing. Mask unit prices and contract terms for users outside procurement.
  • Scale with Autonomous Reflections. As your supply chain KPI dashboard gets regular traffic, Dremio automatically accelerates the most common queries.

The supply chain analytics pipeline you built here connects procurement, warehousing, and quality data into a single governed analytics layer. Replace the sample data with real connections, and you have production-ready supply chain intelligence.

Try Dremio Cloud free for 30 days and build supply chain analytics on your own data.

Try Dremio Cloud free for 30 days

Deploy agentic analytics directly on Apache Iceberg data with no pipelines and no added overhead.

Start For Free

Article Topics

Product Insights from the Dremio Blog