Expose three sheets (Sales, Returns, Staff) from two regional workbooks as separate external tables and JOIN across them for net-revenue and return-rate analysis.
-- Zone + schema
CREATE ZONE IF NOT EXISTS {{zone_name}} TYPE EXTERNAL;
CREATE SCHEMA IF NOT EXISTS {{zone_name}}.excel_demos;
-- One external table per sheet, same LOCATION, different sheet_name
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.excel_demos.all_sales
USING EXCEL LOCATION '{{data_path}}'
OPTIONS (
sheet_name = 'Sales',
has_header = 'true',
infer_schema_rows = '100',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.excel_demos.all_returns
USING EXCEL LOCATION '{{data_path}}'
OPTIONS (
sheet_name = 'Returns',
has_header = 'true',
infer_schema_rows = '100',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.excel_demos.all_staff
USING EXCEL LOCATION '{{data_path}}'
OPTIONS (
sheet_name = 'Staff',
has_header = 'true',
infer_schema_rows = '100',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
-- Revenue per region (df_file_name carries the region identifier)
ASSERT ROW_COUNT = 2
ASSERT VALUE orders = 17 WHERE region LIKE '%east%'
ASSERT VALUE orders = 16 WHERE region LIKE '%west%'
SELECT df_file_name AS region,
COUNT(*) AS orders,
ROUND(SUM(CAST(total_amount AS DOUBLE)), 2) AS total_amount
FROM {{zone_name}}.excel_demos.all_sales
GROUP BY df_file_name
ORDER BY df_file_name;
-- Cross-sheet JOIN: Sales LEFT JOIN Returns on order_id
ASSERT VALUE total_orders = 33
ASSERT VALUE returned_orders = 7
SELECT COUNT(*) AS total_orders,
COUNT(r.return_id) AS returned_orders,
COUNT(*) - COUNT(r.return_id) AS clean_orders
FROM {{zone_name}}.excel_demos.all_sales s
LEFT JOIN {{zone_name}}.excel_demos.all_returns r ON s.order_id = r.order_id;
-- Net revenue per region (gross sales − refunds)
SELECT s.df_file_name AS region,
ROUND(SUM(CAST(s.total_amount AS DOUBLE)), 2) AS gross_sales,
ROUND(COALESCE(SUM(CAST(r.refund_amount AS DOUBLE)), 0), 2) AS total_refunds,
ROUND(SUM(CAST(s.total_amount AS DOUBLE))
- COALESCE(SUM(CAST(r.refund_amount AS DOUBLE)), 0), 2) AS net_revenue
FROM {{zone_name}}.excel_demos.all_sales s
LEFT JOIN {{zone_name}}.excel_demos.all_returns r ON s.order_id = r.order_id
GROUP BY s.df_file_name
ORDER BY s.df_file_name;
-- Cleanup
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.excel_demos.all_sales WITH FILES;
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.excel_demos.all_returns WITH FILES;
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.excel_demos.all_staff WITH FILES;## When to Use Pick this demo when regional offices hand you a single XLSX with multiple related tabs, the classic 'one workbook per branch, one tab per subject' pattern. You model each sheet as its own `CREATE EXTERNAL TABLE` so that queries can JOIN across sheets without first having to unify them in a staging table. This scenario is unique to Excel: no other file format has the concept of named sheets inside a single file. ## What You Will Learn 1. How to project the same LOCATION through three different `sheet_name` values to materialise three tables. 2. How the `file_metadata` option exposes `df_file_name` so you can GROUP BY region without encoding region as a column. 3. How to write a cross-sheet LEFT JOIN on `order_id` to flag returned orders and compute net revenue. 4. How to combine `ASSERT VALUE` for integer counts with `ASSERT WARNING VALUE ... BETWEEN` for floating-point SUMs that drift across platforms. 5. How `infer_schema_rows` controls the sample size the Excel reader uses to pick column types. ## Prerequisites - A directory containing `region-east-2024.xlsx` and `region-west-2024.xlsx`, each with Sales / Returns / Staff sheets. - A target zone (the setup creates one of type EXTERNAL if missing). - Basic comfort with `LEFT JOIN` and `GROUP BY`.