Build a Retail Data Warehouse

Build and profile a million-row retail warehouse with partitioned Parquet.

Introduction

30 Second Summary

A sales report can look convincing even when rows disappeared during preparation. As data grows, proving where every number came from becomes part of the job.

In this project, you will turn generated retail records into a one-million-row dimensional model using SQL inside DuckDB. You will compare a June 2025 revenue analysis across persistent tables plus year-and-month partitioned Parquet files.

What You'll Build

When you run your finished scripts, you can demonstrate a monthly retail sales report backed by measured query performance plus passing reconciliation checks.

By the end of this project, you'll have:

  • A repeatable million-row workload that you can generate entirely with SQL. Its summary confirms 100,000 customers, 1,000 products, 1,000,000 sales transactions, plus the complete date range.
  • A star-schema warehouse that answers monthly revenue questions by category plus region. Deterministic surrogate keys connect each transaction to reusable customer, product, plus date dimensions.
  • Shareable performance and data-quality evidence from table-backed plus partition-filtered query plans. Persisted checks prove that source rows, warehouse facts, Parquet rows, revenue totals, plus dimension keys reconcile.
  • Secret Mission: Scale the workload to five million sales rows. Then compare its database size, Parquet size, plus query-plan times with the original benchmark.

Are there any prerequisites?

You should be comfortable with SQL joins plus GROUP BY. You also need enough free local disk space for the database plus generated Parquet files.

Before We Start

This first checkpoint commits you to a million-row retail warehouse demonstration covering dimensional modeling, partitioning, profiling, plus data-quality verification. Its scope stays local to one machine, so it does not reproduce cloud operations.

Verify Your Local Warehouse Workspace

Your warehouse will soon hold one million transactions. A misplaced file or mismatched CLI version would make every later result harder to reproduce.

An organized workspace keeps each artifact in a predictable location. A version check gives you a known database engine before any data is generated.

In this step, get ready to:
  • Create a structured local workspace for the database, SQL scripts, and generated data.
  • Verify DuckDB CLI version 1.5.5 from Windows PowerShell.
  • Open a persistent database session and confirm it can execute SQL.
Create the warehouse workspace

A Visual Studio Code workspace anchors your terminal to the same folder as your database. The sql and data folders separate repeatable logic from generated files.

  • Press the Windows key to open Windows search.
  • Type Visual Studio Code into the search field.
  • Press Enter to open Visual Studio Code.

You will see a Visual Studio Code window. The next actions turn a folder on your Desktop into the workspace for every project artifact.

Workspace Trust May Appear

Visual Studio Code may show a Workspace Trust dialog after the folder opens. You are creating this empty folder yourself, so the trusted option enables the workspace features needed for the project.

  • Select File from the top menu.
  • Select Open Folder....
  • Select your Desktop in the folder dialog.
  • Select New Folder.
  • Enter company-warehouse as the folder name.
  • Select Select Folder to open the folder as your workspace.

You will see company-warehouse at the top of the Explorer. This folder becomes the fixed location for the database and every later artifact.

  • Select Yes, I trust the authors if the Workspace Trust dialog appears.
  • Right-click company-warehouse in the Explorer.
  • Select New Folder from the context menu.
  • Enter sql as the folder name.
  • Press Enter to create the folder.

You will see an empty sql folder beneath company-warehouse.

  • Right-click company-warehouse in the Explorer again.
  • Select New Folder from the context menu.
  • Enter data as the folder name.
  • Press Enter to create the folder.

Your Explorer now shows two empty folders named sql and data beneath company-warehouse. The workspace structure is ready.

Are the folders nested incorrectly?

Both folders must appear directly beneath company-warehouse. If one folder appears inside the other, move it back to the workspace folder with the Explorer.

Still unsure about the folder structure? Help me check my Visual Studio Code workspace structure.

Verify DuckDB CLI 1.5.5

The DuckDB version controls which database features and command behavior you can rely on. You will verify the target release from the integrated Windows PowerShell terminal.

  • Select View from the Visual Studio Code menu.
  • Select Terminal to open the integrated terminal.

You should see a PowerShell prompt whose path ends in company-warehouse. This confirms that commands run from the project folder.

  • Check the installed DuckDB CLI version by running this command:
duckdb -version

What Does This Command Do?

The duckdb -version command asks the installed CLI to report its version. This check catches missing or outdated installations before they affect your SQL scripts.

✔️ I see version 1.5.5

Good. Your output includes 1.5.5, so the database engine matches the project target.

  • Keep the integrated PowerShell terminal open for the database check.

ⓧ I see an older version

The CLI is available, but this project targets version 1.5.5. Updating now keeps every later command aligned with the verified project environment.

  • Open the official DuckDB Windows installation page.
  • Update the DuckDB CLI to version 1.5.5 using the Windows method listed on that page.
  • Restart Visual Studio Code after the update completes.
  • Select View from the Visual Studio Code menu.
  • Select Terminal to open a fresh integrated terminal.
  • Repeat the version check shown above.

The new output should include 1.5.5.

Still Seeing the Older Version?

A terminal opened before the update can retain the previous executable path. Restart Visual Studio Code before checking again.

If the older version remains, help me diagnose which DuckDB installation PowerShell is using.

ⓧ Command not found

PowerShell cannot find the DuckDB CLI yet. The official Windows package installs the command required by this project.

  • Install the DuckDB CLI by running this command:
winget install DuckDB.cli

What Does This Command Do?

This is DuckDB's documented Windows package command. It installs the DuckDB CLI package that provides the duckdb command.

  • Restart Visual Studio Code after the installation completes.
  • Select View from the Visual Studio Code menu.
  • Select Terminal to open a fresh integrated terminal.
  • Repeat the version check shown above.

The fresh terminal should report version 1.5.5.

Installation Command Failed?

Confirm that Windows Package Manager is available. Also check that your internet connection allows the package download.

If the command still fails, help me troubleshoot the DuckDB CLI installation on Windows.

Open the persistent database

A persistent database stores its state in a file inside your project folder. Opening company_warehouse.duckdb now creates that file without creating any project schemas or tables.

  • Open or create the persistent database from the project folder by running this command:
duckdb .\company_warehouse.duckdb

What Does This Command Do?

  • The duckdb command starts the interactive database CLI.
  • The .\company_warehouse.duckdb path creates or opens a persistent database file inside company-warehouse.
  • The interactive session keeps later scripts connected to the same database.
  • Confirm that company_warehouse.duckdb now appears beside the sql and data folders in the Explorer.

The database file is now persistent. It contains no project schemas or tables because no warehouse scripts have been executed.

Database File Missing From the Explorer?

Confirm that the terminal prompt was inside company-warehouse before you started DuckDB. Reopen the workspace folder if the terminal started elsewhere.

If the CLI did not open, help me troubleshoot the persistent DuckDB database command.

Before you run the final check, what value do you expect the ready column to contain?

  • Test the active DuckDB session by entering this SQL statement:
SELECT 42 AS ready;

What Does This Query Do?

The query returns the fixed value 42 under a column named ready. A successful result proves that the interactive session can execute SQL against the persistent database.

You will see a ready column containing 42. That result confirms your local warehouse workspace is ready for source-data generation.

  • Keep the DuckDB session open for the remaining project steps.

Query Did Not Return 42?

Make sure you entered the statement at the DuckDB prompt. Check that the statement ends with a semicolon inside the terminal.

If the session shows an error, help me diagnose the DuckDB readiness query.

Your project now has a persistent database, an organized folder structure, and a verified DuckDB session. Next, you will generate one million source transactions and capture the first query profile.

Generate and Profile the Raw Data

Your Visual Studio Code workspace is ready. The persistent database gives you a stable place to build a repeatable retail workload.

The first report needs a trustworthy baseline. All two years of transaction dates live together in the raw source, so profiling this layout gives you a real point of comparison.

In this step, get ready to:
  • Generate customer, product, and transaction source tables entirely with SQL.
  • Confirm one million sales rows and the complete source date range.
  • Run the June 2025 revenue report and inspect its baseline query plan.
Generate the source tables

A schema groups related database objects under one name. Your raw schema represents the operational source layer before any warehouse transformations.

DuckDB can generate synthetic rows directly with SQL. This keeps the workload reproducible because the same script creates the same source structure each time.

  • Select the sql folder in the Explorer sidebar.
  • Use the new-file control beside the folder to create sql/01_generate_raw.sql.
  • Create the raw schema and customer source by pasting this code into sql/01_generate_raw.sql:
CREATE SCHEMA IF NOT EXISTS raw;

CREATE OR REPLACE TABLE raw.customers AS
SELECT
    'CUST-' || CAST(customer_number AS VARCHAR) AS customer_id,
    'Customer ' || CAST(customer_number AS VARCHAR) AS customer_name,
    CASE customer_number % 4
        WHEN 0 THEN 'North'
        WHEN 1 THEN 'South'
        WHEN 2 THEN 'East'
        ELSE 'West'
    END AS region
FROM range(1, 100001) AS customers(customer_number);

What does this code do?

  • The raw schema separates generated source data from the warehouse objects you build later.
  • The range(1, 100001) expression produces 100,000 customer numbers because its upper bound is excluded.
  • The expressions create a business identifier and a readable customer name for every row.
  • The CASE expression distributes customers across four regions.
  • Save sql/01_generate_raw.sql.
  • Switch back to the connected DuckDB CLI session from earlier.
  • Load the customer source into the database by running:
.read sql/01_generate_raw.sql

What does this command do?

The .read command executes every statement currently saved in the script. The replace-table statement makes this script safe to run again after later chunks are added.

You will see the DuckDB prompt return without an error. The database now contains the 100,000-row customer source.

Script not loading?

  • Confirm that the open CLI session started from the company-warehouse folder.
  • Confirm that the file appears as sql/01_generate_raw.sql in the Explorer sidebar.
  • Save the file before loading it again.

Help me diagnose why DuckDB cannot load my generation script.

Products provide the categories used by the revenue report. A deterministic number pattern gives every product one category and a reusable operational key.

  • Return to sql/01_generate_raw.sql in the editor.
  • Append the product source below the customer table code by pasting:
CREATE OR REPLACE TABLE raw.products AS
SELECT
    'PROD-' || CAST(product_number AS VARCHAR) AS product_id,
    'Product ' || CAST(product_number AS VARCHAR) AS product_name,
    CASE product_number % 5
        WHEN 0 THEN 'Electronics'
        WHEN 1 THEN 'Home'
        WHEN 2 THEN 'Office'
        WHEN 3 THEN 'Sports'
        ELSE 'Outdoors'
    END AS category
FROM range(1, 1001) AS products(product_number);

How are products generated?

  • The range creates exactly 1,000 product numbers.
  • Each PROD-<number> value acts as an operational product identifier.
  • The remainder calculation cycles through five retail categories.
  • The deterministic pattern keeps every rerun consistent.
  • Save sql/01_generate_raw.sql.
  • Return to the connected DuckDB CLI session.
  • Load the expanded script by running:
.read sql/01_generate_raw.sql

What happens on this rerun?

DuckDB rebuilds raw.customers from the saved definition. It also creates raw.products from the new section.

The DuckDB prompt returns without an error. Both source dimensions are now available for transaction generation.

Product table failing?

  • Check that the customer statement ends with a semicolon in sql/01_generate_raw.sql.
  • Check that the product code sits below the complete customer statement.
  • Compare the category names with the supplied code.

Help me find the SQL error in my customer and product generation script.

The sales source uses a transaction grain of one row per generated sale. Each row carries the date and operational keys needed to join back to its customer and product.

  • Return to sql/01_generate_raw.sql in the editor.
  • Append the one-million-row transaction source by pasting:
CREATE OR REPLACE TABLE raw.sales_events AS
WITH generated_sales AS (
    SELECT
        sale_id,
        1 + ((sale_id - 1) % 100000) AS customer_number,
        1 + ((sale_id - 1) % 1000) AS product_number,
        CAST(1 + ((sale_id - 1) % 5) AS INTEGER) AS quantity
    FROM range(1, 1000001) AS sales(sale_id)
)
SELECT
    sale_id,
    'SALE-' || CAST(sale_id AS VARCHAR) AS sale_number,
    DATE '2024-01-01' + CAST((sale_id - 1) % 730 AS INTEGER) AS order_date,
    'CUST-' || CAST(customer_number AS VARCHAR) AS customer_id,
    'PROD-' || CAST(product_number AS VARCHAR) AS product_id,
    quantity,
    CAST(10 + (product_number % 190) AS DECIMAL(10, 2)) AS unit_price
FROM generated_sales;

How is the workload shaped?

  • The common table expression generates one million sequential sale identifiers.
  • The remainder calculations map every sale to an existing customer and product.
  • The date calculation spreads transactions across 730 days from the start of 2024.
  • The quantity and price expressions create repeatable measures for later revenue calculations.

This is the first heavy operation in the project. Its duration depends on your local hardware, so the terminal prompt is your completion signal.

  • Save sql/01_generate_raw.sql.
  • Return to the connected DuckDB CLI session.
  • Generate the transaction source by running:
.read sql/01_generate_raw.sql

What does this run create?

The script rebuilds the two smaller source tables. It then materializes raw.sales_events inside the persistent database.

The DuckDB prompt returns when generation is complete. Your persistent database now holds the transaction source.

Generation not completing?

  • Allow the current command to finish before entering another command.
  • Check that the sales range ends at 1000001.
  • Check that every generated column has the same spelling as the supplied code.

Help me troubleshoot the one-million-row raw.sales_events generation statement.

A final summary query turns table creation into a visible checkpoint. It reports the three row counts plus the earliest and latest transaction dates.

  • Return to sql/01_generate_raw.sql in the editor.
  • Append the source summary query by pasting:
SELECT
    (SELECT count(*) FROM raw.customers) AS customer_rows,
    (SELECT count(*) FROM raw.products) AS product_rows,
    (SELECT count(*) FROM raw.sales_events) AS sales_rows,
    (SELECT min(order_date) FROM raw.sales_events) AS first_sale_date,
    (SELECT max(order_date) FROM raw.sales_events) AS last_sale_date;

What does the summary prove?

  • The three count expressions confirm the intended scale of each source table.
  • The minimum date confirms where the generated timeline begins.
  • The maximum date confirms where the 730-day timeline ends.
  • Save sql/01_generate_raw.sql.

Before you run the complete script, what row counts and date boundaries do you expect the summary to show?

  • Return to the connected DuckDB CLI session.
  • Run the complete generation script by entering:
.read sql/01_generate_raw.sql

What should this final run establish?

The command rebuilds all three sources from one repeatable script. The final query prints the database state you need before profiling.

You will see 100,000 customer rows and 1,000 product rows. You will also see 1,000,000 sales rows dated from 2024-01-01 through 2025-12-30.

That is the first large checkpoint complete. Your persistent database now contains a reproducible million-row retail source layer.

Counts or dates look different?

  • Compare each range upper bound with the supplied script.
  • Check that the date remainder uses 730.
  • Rerun the saved script after correcting any mismatch.

Help me reconcile unexpected row counts or dates in my generated raw tables.

✔️ Awesome, I've got everything!

Your raw-data generation script now creates all three source tables and prints their scale.

  • Keep sql/01_generate_raw.sql saved in the sql folder.

ⓧ I'd like to double check the full code

CREATE SCHEMA IF NOT EXISTS raw;

CREATE OR REPLACE TABLE raw.customers AS
SELECT
    'CUST-' || CAST(customer_number AS VARCHAR) AS customer_id,
    'Customer ' || CAST(customer_number AS VARCHAR) AS customer_name,
    CASE customer_number % 4
        WHEN 0 THEN 'North'
        WHEN 1 THEN 'South'
        WHEN 2 THEN 'East'
        ELSE 'West'
    END AS region
FROM range(1, 100001) AS customers(customer_number);

CREATE OR REPLACE TABLE raw.products AS
SELECT
    'PROD-' || CAST(product_number AS VARCHAR) AS product_id,
    'Product ' || CAST(product_number AS VARCHAR) AS product_name,
    CASE product_number % 5
        WHEN 0 THEN 'Electronics'
        WHEN 1 THEN 'Home'
        WHEN 2 THEN 'Office'
        WHEN 3 THEN 'Sports'
        ELSE 'Outdoors'
    END AS category
FROM range(1, 1001) AS products(product_number);

CREATE OR REPLACE TABLE raw.sales_events AS
WITH generated_sales AS (
    SELECT
        sale_id,
        1 + ((sale_id - 1) % 100000) AS customer_number,
        1 + ((sale_id - 1) % 1000) AS product_number,
        CAST(1 + ((sale_id - 1) % 5) AS INTEGER) AS quantity
    FROM range(1, 1000001) AS sales(sale_id)
)
SELECT
    sale_id,
    'SALE-' || CAST(sale_id AS VARCHAR) AS sale_number,
    DATE '2024-01-01' + CAST((sale_id - 1) % 730 AS INTEGER) AS order_date,
    'CUST-' || CAST(customer_number AS VARCHAR) AS customer_id,
    'PROD-' || CAST(product_number AS VARCHAR) AS product_id,
    quantity,
    CAST(10 + (product_number % 190) AS DECIMAL(10, 2)) AS unit_price
FROM generated_sales;

SELECT
    (SELECT count(*) FROM raw.customers) AS customer_rows,
    (SELECT count(*) FROM raw.products) AS product_rows,
    (SELECT count(*) FROM raw.sales_events) AS sales_rows,
    (SELECT min(order_date) FROM raw.sales_events) AS first_sale_date,
    (SELECT max(order_date) FROM raw.sales_events) AS last_sale_date;
Profile the June baseline

The business query joins sales to products and customers through their operational string keys. It groups June 2025 revenue by category and region to produce the first report from your source layer.

  • Select the sql folder in the Explorer sidebar.
  • Use the new-file control beside the folder to create sql/02_profile_raw.sql.
  • Add the June 2025 business query by pasting:
SELECT
    p.category,
    c.region,
    count(*) AS transaction_count,
    sum(s.quantity) AS units_sold,
    sum(CAST(s.quantity AS DECIMAL(18, 2)) * s.unit_price) AS revenue
FROM raw.sales_events AS s
JOIN raw.products AS p ON s.product_id = p.product_id
JOIN raw.customers AS c ON s.customer_id = c.customer_id
WHERE s.order_date >= DATE '2025-06-01'
  AND s.order_date < DATE '2025-07-01'
GROUP BY p.category, c.region
ORDER BY p.category, c.region;

How does the report work?

  • The joins attach a category and region to each sale.
  • The date predicates include every June 2025 transaction.
  • The aggregates calculate transaction count and units sold.
  • The revenue expression multiplies quantity by unit price before summing each group.
  • Save sql/02_profile_raw.sql.
  • Return to the connected DuckDB CLI session.
  • Run the business query by entering:
.read sql/02_profile_raw.sql

What does this run confirm?

The script executes the saved report against your persistent raw tables. A successful result proves that the generated operational keys resolve across all three sources.

You will see category-and-region rows with transaction counts, units sold, and revenue. The report is now producing business-facing results from one million source transactions.

Report not returning rows?

  • Confirm that the generation script completed with all three expected row counts.
  • Check that both joins use matching customer_id and product_id columns.
  • Check that the date range starts at 2025-06-01.

Help me troubleshoot why the June 2025 raw revenue query returns an error or no rows.

A query plan reveals how the database executed that report. EXPLAIN ANALYZE runs the query while recording runtime information and operator cardinalities.

  • Return to sql/02_profile_raw.sql in the editor.
  • Append the profiled version of the report by pasting:
EXPLAIN ANALYZE
SELECT
    p.category,
    c.region,
    count(*) AS transaction_count,
    sum(s.quantity) AS units_sold,
    sum(CAST(s.quantity AS DECIMAL(18, 2)) * s.unit_price) AS revenue
FROM raw.sales_events AS s
JOIN raw.products AS p ON s.product_id = p.product_id
JOIN raw.customers AS c ON s.customer_id = c.customer_id
WHERE s.order_date >= DATE '2025-06-01'
  AND s.order_date < DATE '2025-07-01'
GROUP BY p.category, c.region
ORDER BY p.category, c.region;

What does the profile add?

  • The underlying business query remains identical to the report you just tested.
  • The profile records the operators DuckDB used to scan, join, group, and order the data.
  • The reported cardinalities show how many rows flowed through stages of the plan.
  • The total time becomes your local baseline for later comparison.
  • Save sql/02_profile_raw.sql.

Before you run the complete profile, which operations do you expect DuckDB to perform between reading the raw tables and returning grouped revenue?

  • Return to the connected DuckDB CLI session.
  • Execute the report and its profile by running:
.read sql/02_profile_raw.sql

What should the baseline show?

The command first prints the category-and-region report. It then prints the analyzed execution plan with operators and a total time.

This baseline reads from raw.sales_events plus the two lookup tables. It has no read_parquet operation or file-level partition filter.

  • Locate the total time reported in the analyzed plan.
  • Trace the plan from the raw table scans through the joins.
  • Identify the grouping operation that produces the category-and-region result.
  • Note the operator cardinalities shown for your local run.

You have now seen the intended shortfall: the report works over the raw tables, but the plan has no partitioned file layer to skip unrelated files. This result is the benchmark your later storage design must improve.

Plan difficult to inspect?

  • Expand the terminal panel to give the plan more horizontal space.
  • Scroll back to the beginning of the analyzed plan.
  • Confirm that EXPLAIN ANALYZE appears before the second SELECT statement.

Help me interpret the DuckDB execution plan for my June 2025 raw-table revenue query.

✔️ Awesome, I've got everything!

Your profiling script now contains the business query and its analyzed baseline.

  • Keep sql/02_profile_raw.sql saved for the later plan comparison.

ⓧ I'd like to double check the full code

SELECT
    p.category,
    c.region,
    count(*) AS transaction_count,
    sum(s.quantity) AS units_sold,
    sum(CAST(s.quantity AS DECIMAL(18, 2)) * s.unit_price) AS revenue
FROM raw.sales_events AS s
JOIN raw.products AS p ON s.product_id = p.product_id
JOIN raw.customers AS c ON s.customer_id = c.customer_id
WHERE s.order_date >= DATE '2025-06-01'
  AND s.order_date < DATE '2025-07-01'
GROUP BY p.category, c.region
ORDER BY p.category, c.region;

EXPLAIN ANALYZE
SELECT
    p.category,
    c.region,
    count(*) AS transaction_count,
    sum(s.quantity) AS units_sold,
    sum(CAST(s.quantity AS DECIMAL(18, 2)) * s.unit_price) AS revenue
FROM raw.sales_events AS s
JOIN raw.products AS p ON s.product_id = p.product_id
JOIN raw.customers AS c ON s.customer_id = c.customer_id
WHERE s.order_date >= DATE '2025-06-01'
  AND s.order_date < DATE '2025-07-01'
GROUP BY p.category, c.region
ORDER BY p.category, c.region;

Your million-row source layer is generated, verified, and profiled. Next, you will replace the operational-key reporting pattern with a dimensional warehouse built around reusable dimensions and a transaction-grain fact table.

Build the Dimensional Warehouse

Your million-row source now answers the June 2025 revenue question. You also have a baseline query plan that shows how the operational tables handle that report.

The baseline still joins transaction rows through operational string keys. This couples every analytical query to the source-table structure.

A star schema gives analytics a stable grain through reusable dimensions. Numeric surrogate keys provide warehouse-owned references for each fact row.

In this step, get ready to:
  • Create customer, product, and date dimensions with deterministic surrogate keys.
  • Load a one-million-row fact table with precomputed sales revenue.
  • Publish a reusable monthly sales view for category-and-region reporting.
Create deterministic dimensions

Dimension tables keep descriptive customer, product, and calendar attributes in reusable locations. Deterministic ordering gives the same source values the same numeric keys whenever you rebuild the warehouse.

  • In the file tree of Visual Studio Code from earlier, select the sql folder.
  • Use the new-file control above the file tree to create 03_build_star.sql.
  • Build the warehouse schema plus the customer dimension by pasting this code into sql/03_build_star.sql:
CREATE SCHEMA IF NOT EXISTS warehouse;

CREATE OR REPLACE TABLE warehouse.dim_customer AS
SELECT
    row_number() OVER (ORDER BY customer_id) AS customer_key,
    customer_id,
    customer_name,
    region
FROM raw.customers;

What does this code do?

  • The warehouse schema separates dimensional objects from the existing raw sources.
  • The row_number() expression assigns a numeric customer_key after sorting by customer_id.
  • The source customer ID remains available for fact-load lookups and traceability.
  • Save sql/03_build_star.sql.
  • Switch back to the open DuckDB CLI session from earlier.
  • Load the customer dimension into the connected database by running:
.read sql/03_build_star.sql

What should I see?

Your first warehouse dimension is now in place. The CLI finishes the script and returns to its input prompt.

Customer dimension not created?

  • Check that 03_build_star.sql sits inside the sql folder.
  • Check that the file contains the complete warehouse.dim_customer statement.

Ask for help with the customer dimension error.

The product dimension applies the same key pattern to merchandise. Its category attribute becomes a reusable grouping field for every warehouse report.

  • Switch back to sql/03_build_star.sql in Visual Studio Code.
  • Add the product dimension below FROM raw.customers; by pasting:
CREATE OR REPLACE TABLE warehouse.dim_product AS
SELECT
    row_number() OVER (ORDER BY product_id) AS product_key,
    product_id,
    product_name,
    category
FROM raw.products;

How is the product key assigned?

  • Sorting by product_id makes the product_key assignment repeatable.
  • Keeping category in this dimension gives reports a consistent product classification.
  • Save sql/03_build_star.sql.
  • Return to the open DuckDB CLI session.
  • Load both completed dimensions by running:
.read sql/03_build_star.sql

What happens on this run?

The script rebuilds the customer dimension before creating the product dimension. Both statements complete before the CLI returns to its prompt.

Product dimension statement failing?

  • Check that the product statement starts after the customer statement's closing full stop.
  • Compare every product_id reference with the column in raw.products.

Ask for help with the product dimension error.

A date dimension gives every transaction a shared calendar vocabulary. Distinct source dates produce one row per day across the two-year source range.

  • Switch back to sql/03_build_star.sql.
  • Add the date dimension below FROM raw.products; by pasting:
CREATE OR REPLACE TABLE warehouse.dim_date AS
SELECT
    row_number() OVER (ORDER BY order_date) AS date_key,
    order_date AS full_date,
    year(order_date) AS calendar_year,
    quarter(order_date) AS calendar_quarter,
    month(order_date) AS calendar_month,
    monthname(order_date) AS month_name
FROM (
    SELECT DISTINCT order_date
    FROM raw.sales_events
) AS dates;

Why use a date dimension?

  • The distinct-date query creates one dimension row for each source date.
  • The date_key gives fact rows a numeric calendar reference.
  • The calendar columns support year, quarter, month-number, and month-name analysis.
  • Save sql/03_build_star.sql.
  • Return to the open DuckDB CLI session.
  • Load all three dimensions by running:
.read sql/03_build_star.sql

What does this prove?

All three dimension statements now complete in sequence. Your warehouse has reusable customer, product, and calendar lookup tables.

Date dimension not loading?

  • Check that the inner query reads distinct values from raw.sales_events.
  • Check that order_date appears in every date expression exactly as shown.

Ask for help with the date dimension error.

Load the transaction-grain fact table

The fact grain defines what one row represents. Here, each fact row represents one source sale and carries numeric keys for its date, customer, and product.

The fact load looks up those keys through joins. It also stores sales_amount as an additive decimal measure for later aggregation.

  • Switch back to sql/03_build_star.sql.
  • Add the fact-table load below ) AS dates; by pasting:
CREATE OR REPLACE TABLE warehouse.fact_sales AS
SELECT
    s.sale_id,
    s.sale_number,
    d.date_key,
    c.customer_key,
    p.product_key,
    s.quantity,
    s.unit_price,
    CAST(s.quantity * s.unit_price AS DECIMAL(18, 2)) AS sales_amount
FROM raw.sales_events AS s
JOIN warehouse.dim_date AS d ON s.order_date = d.full_date
JOIN warehouse.dim_customer AS c ON s.customer_id = c.customer_id
JOIN warehouse.dim_product AS p ON s.product_id = p.product_id;

What does the fact load preserve?

  • The sale_id plus sale_number preserve the source transaction identity.
  • The three joins translate source business IDs into warehouse surrogate keys.
  • The decimal cast gives sales_amount a consistent analytical type.
  • Save sql/03_build_star.sql.
  • Return to the open DuckDB CLI session.
  • Build the transaction-grain fact table by running:
.read sql/03_build_star.sql

What should happen?

The fact load now joins every source transaction to its three dimension rows. The CLI returns to its prompt after completing the script.

Fact load not completing?

  • Check that each join uses the matching source business ID or date.
  • Check that sales_amount multiplies quantity by unit_price.

Ask for help with the fact-table load.

A row-count report makes the warehouse grain visible. It also confirms that every source sale reached the fact table.

  • Switch back to sql/03_build_star.sql.
  • Add the warehouse row-count report below the fact-table statement by pasting:
SELECT 'dim_customer' AS object_name, count(*) AS row_count
FROM warehouse.dim_customer
UNION ALL
SELECT 'dim_product', count(*) FROM warehouse.dim_product
UNION ALL
SELECT 'dim_date', count(*) FROM warehouse.dim_date
UNION ALL
SELECT 'fact_sales', count(*) FROM warehouse.fact_sales;

Why check all four counts together?

The combined query places each warehouse object beside its row count. This makes missing dimension rows or dropped fact rows visible in one result.

  • Save sql/03_build_star.sql.

Before you rerun the script, do you expect the fact count to match the raw transaction count?

  • Return to the open DuckDB CLI session.
  • Build the warehouse and display its row counts by running:
.read sql/03_build_star.sql

What should I see?

That transaction grain is now confirmed. You should see 100,000 customer rows, 1,000 product rows, 730 date rows, and 1,000,000 fact rows.

Fact count lower than expected?

  • Check the fact joins for a mismatched business-key column.
  • Check that all three dimensions completed before warehouse.fact_sales loaded.

Ask for help reconciling the warehouse counts.

Publish and verify the monthly view

A database view stores a reusable query definition. This view brings descriptive dimensions back to the fact measures for monthly reporting.

  • Switch back to sql/03_build_star.sql.
  • Place your cursor immediately before SELECT 'dim_customer' AS object_name, count(*) AS row_count.
  • Create the monthly sales view by pasting:
CREATE OR REPLACE VIEW warehouse.v_monthly_sales AS
SELECT
    d.calendar_year,
    d.calendar_month,
    d.month_name,
    p.category,
    c.region,
    count(*) AS transaction_count,
    sum(f.quantity) AS units_sold,
    sum(f.sales_amount) AS revenue
FROM warehouse.fact_sales AS f
JOIN warehouse.dim_date AS d ON f.date_key = d.date_key
JOIN warehouse.dim_product AS p ON f.product_key = p.product_key
JOIN warehouse.dim_customer AS c ON f.customer_key = c.customer_key
GROUP BY
    d.calendar_year,
    d.calendar_month,
    d.month_name,
    p.category,
    c.region;

What does the view calculate?

  • The dimension joins restore calendar, category, and region labels for reporting.
  • The grouping produces one row per month, category, and region combination.
  • The measures report transaction count, units sold, and revenue.
  • Save sql/03_build_star.sql.
  • Return to the open DuckDB CLI session.
  • Create the view and rerun the warehouse count report by running:
.read sql/03_build_star.sql

How does this confirm the view?

The row-count report appears after the view statement completes. Reaching that report confirms that DuckDB accepted the reusable view definition.

View statement failing?

  • Check that every selected label also appears in the GROUP BY list.
  • Check that each fact key joins to the matching dimension key.

Ask for help with the monthly view.

An ordered sample makes the business-facing result easy to inspect. The first ten rows show how the view combines month, category, region, and additive measures.

  • Switch back to sql/03_build_star.sql.
  • Move your cursor to the end of the file.
  • Add the monthly report sample by pasting:
SELECT *
FROM warehouse.v_monthly_sales
ORDER BY calendar_year, calendar_month, category, region
LIMIT 10;

Why order the sample?

The ordering makes the first ten rows predictable across repeated runs. It also presents the monthly groupings in a consistent sequence.

  • Save the completed sql/03_build_star.sql file.

Before you run the completed script, which four row counts do you expect to see before the monthly results?

  • Return to the open DuckDB CLI session.
  • Execute the completed dimensional warehouse script by running:
.read sql/03_build_star.sql

What should I see?

Your dimensional warehouse is working. You should see 100,000 customer rows, 1,000 product rows, 730 date rows, and 1,000,000 fact rows.

The next result contains ten monthly rows grouped by category and region. Each row includes transaction count, units sold, and revenue.

Monthly results missing?

  • Check that warehouse.v_monthly_sales appears before the two report queries.
  • Check that the final query ends with LIMIT 10;.

Ask for help with the final warehouse run.

✔️ Awesome, I've got everything!

Your script is saved. Your connected database now contains the complete star schema plus its monthly reporting view.

ⓧ I'd like to double check the full code

CREATE SCHEMA IF NOT EXISTS warehouse;

CREATE OR REPLACE TABLE warehouse.dim_customer AS
SELECT
    row_number() OVER (ORDER BY customer_id) AS customer_key,
    customer_id,
    customer_name,
    region
FROM raw.customers;

CREATE OR REPLACE TABLE warehouse.dim_product AS
SELECT
    row_number() OVER (ORDER BY product_id) AS product_key,
    product_id,
    product_name,
    category
FROM raw.products;

CREATE OR REPLACE TABLE warehouse.dim_date AS
SELECT
    row_number() OVER (ORDER BY order_date) AS date_key,
    order_date AS full_date,
    year(order_date) AS calendar_year,
    quarter(order_date) AS calendar_quarter,
    month(order_date) AS calendar_month,
    monthname(order_date) AS month_name
FROM (
    SELECT DISTINCT order_date
    FROM raw.sales_events
) AS dates;

CREATE OR REPLACE TABLE warehouse.fact_sales AS
SELECT
    s.sale_id,
    s.sale_number,
    d.date_key,
    c.customer_key,
    p.product_key,
    s.quantity,
    s.unit_price,
    CAST(s.quantity * s.unit_price AS DECIMAL(18, 2)) AS sales_amount
FROM raw.sales_events AS s
JOIN warehouse.dim_date AS d ON s.order_date = d.full_date
JOIN warehouse.dim_customer AS c ON s.customer_id = c.customer_id
JOIN warehouse.dim_product AS p ON s.product_id = p.product_id;

CREATE OR REPLACE VIEW warehouse.v_monthly_sales AS
SELECT
    d.calendar_year,
    d.calendar_month,
    d.month_name,
    p.category,
    c.region,
    count(*) AS transaction_count,
    sum(f.quantity) AS units_sold,
    sum(f.sales_amount) AS revenue
FROM warehouse.fact_sales AS f
JOIN warehouse.dim_date AS d ON f.date_key = d.date_key
JOIN warehouse.dim_product AS p ON f.product_key = p.product_key
JOIN warehouse.dim_customer AS c ON f.customer_key = c.customer_key
GROUP BY
    d.calendar_year,
    d.calendar_month,
    d.month_name,
    p.category,
    c.region;

SELECT 'dim_customer' AS object_name, count(*) AS row_count
FROM warehouse.dim_customer
UNION ALL
SELECT 'dim_product', count(*) FROM warehouse.dim_product
UNION ALL
SELECT 'dim_date', count(*) FROM warehouse.dim_date
UNION ALL
SELECT 'fact_sales', count(*) FROM warehouse.fact_sales;

SELECT *
FROM warehouse.v_monthly_sales
ORDER BY calendar_year, calendar_month, category, region
LIMIT 10;

How to use this reference

Compare this file with your saved script from top to bottom. The statement order ensures dimensions exist before the fact load creates its key lookups.

Your raw transactions now feed a validated dimensional model with reusable monthly reporting. Next, you will export the fact data to Parquet so year-and-month filters can skip unrelated files.

Partition the Fact Data for File Pruning

Your star schema now supports reusable monthly analysis. Its million fact rows still sit together inside one DuckDB table, so two years of transactions share the same storage boundary.

This step exports those facts into Parquet files. Hive partitioning turns each sale year into one folder level. Each sale month becomes the next level. A matching query filter then enables partition pruning before DuckDB scans file contents.

In this step, get ready to:
  • Export the sales facts into year-and-month partitioned Parquet files.
  • Inspect the Hive-style partition folders in Visual Studio Code.
  • Profile a June 2025 file-backed query against the partitioned data.
Export fact rows to partitioned Parquet

The export joins each fact to warehouse.dim_date to recover its calendar year plus month. DuckDB uses those values as directory-level partition keys while preserving the fact columns inside each Parquet file.

  • In the Visual Studio Code file sidebar, select the sql folder.
  • Use the sidebar file controls to create sql/04_export_partitioned.sql.
  • Write the partitioned export query into the file by pasting this code:
COPY (
    SELECT
        f.sale_id,
        f.sale_number,
        f.date_key,
        f.customer_key,
        f.product_key,
        f.quantity,
        f.unit_price,
        f.sales_amount,
        d.calendar_year AS sale_year,
        d.calendar_month AS sale_month
    FROM warehouse.fact_sales AS f
    JOIN warehouse.dim_date AS d ON f.date_key = d.date_key
)
TO 'data/parquet/fact_sales'
(
    FORMAT parquet,
    PARTITION_BY (sale_year, sale_month),
    OVERWRITE,
    RETURN_STATS
);

What does this export do?

  • The COPY statement writes the query result to data/parquet/fact_sales.
  • The join obtains sale_year plus sale_month from each fact row's date dimension record.
  • PARTITION_BY turns the two date attributes into nested folder levels.
  • OVERWRITE replaces existing output files in the targeted partition directories when you rerun the export.
  • RETURN_STATS reports the written filenames plus their row counts, sizes, statistics, and partition keys.
  • Save sql/04_export_partitioned.sql.
  • Switch back to the DuckDB CLI session from earlier.

Before you run the export, which two folder levels do you expect DuckDB to create?

  • Create the partitioned Parquet files by running this command:
.read sql/04_export_partitioned.sql

What does this command do?

The .read command executes every SQL statement currently saved in sql/04_export_partitioned.sql. The first run creates the Parquet directory tree and prints statistics for the exported files.

You should see export statistics containing Parquet filenames, row counts, and partition keys. These results confirm that DuckDB wrote the fact data to disk.

Export did not create the files?

Confirm that sql/04_export_partitioned.sql sits inside the sql folder in the company-warehouse workspace. Check that the DuckDB session still uses that workspace as its starting location.

If Windows blocks the write, close any program currently using files under data/parquet/fact_sales before retrying.

Help me diagnose a failed DuckDB partitioned Parquet export.

The file statistics prove that data was written. A separate count gives you one clear reconciliation point before you profile the files.

  • Return to sql/04_export_partitioned.sql in the editor.
  • Add the Parquet row-count check at the bottom by pasting this code:
SELECT count(*) AS parquet_rows
FROM read_parquet(
    'data/parquet/fact_sales/*/*/*.parquet',
    hive_partitioning = true
);

What does this check prove?

  • read_parquet loads every Parquet file across the two partition levels.
  • hive_partitioning = true reads partition values from the folder names.
  • parquet_rows counts the complete exported dataset before any filter is applied.
  • Save sql/04_export_partitioned.sql.
  • Switch back to the DuckDB CLI session.
  • Rebuild the files and run the new count by executing this command:
.read sql/04_export_partitioned.sql

What does the second run confirm?

DuckDB overwrites the targeted output before reading every generated Parquet file. The final result should show parquet_rows with a value of 1000000.

That storage layer is now working. All one million facts have moved into a repeatable partitioned export without changing the warehouse table.

Parquet count does not match?

Confirm that the export still includes OVERWRITE. This keeps reruns from leaving stale output inside the targeted directories.

Check that the wildcard path includes two folder levels before *.parquet. The first level represents the sale year. The second represents the sale month.

Help me reconcile an unexpected Parquet row count.

✔️ Awesome, I've got everything!

  • Confirm that sql/04_export_partitioned.sql is saved before continuing.

ⓧ I'd like to double check the full code

COPY (
    SELECT
        f.sale_id,
        f.sale_number,
        f.date_key,
        f.customer_key,
        f.product_key,
        f.quantity,
        f.unit_price,
        f.sales_amount,
        d.calendar_year AS sale_year,
        d.calendar_month AS sale_month
    FROM warehouse.fact_sales AS f
    JOIN warehouse.dim_date AS d ON f.date_key = d.date_key
)
TO 'data/parquet/fact_sales'
(
    FORMAT parquet,
    PARTITION_BY (sale_year, sale_month),
    OVERWRITE,
    RETURN_STATS
);

SELECT count(*) AS parquet_rows
FROM read_parquet(
    'data/parquet/fact_sales/*/*/*.parquet',
    hive_partitioning = true
);

How should I use this reference?

Compare this complete file with your saved script. The export statement should appear first. The row-count query should appear directly below it.

Inspect the Hive partition folders

Hive-style folders encode each partition key as column=value. This structure gives DuckDB file-level information before it opens the Parquet contents.

  • Expand data in the Visual Studio Code file sidebar.
  • Expand parquet.
  • Expand fact_sales.
  • Expand sale_year=2025.
  • Expand sale_month=6.
  • Confirm that the month folder contains one or more files with the .parquet extension.

Why do the folders matter?

A query filtered to June 2025 can use the year folder plus the month folder to exclude unrelated files. DuckDB can therefore skip other partitions before scanning their columns.

Profile the partition-filtered query

The file-backed query uses the same category-by-region revenue question as the baseline. Its scan source changes to the partitioned Parquet files, so the execution plan can show how the June filters affect file access.

  • Select the sql folder in the Visual Studio Code file sidebar.
  • Use the sidebar file controls to create sql/05_profile_partitioned.sql.
  • Write the June 2025 business query into the file by pasting this code:
SELECT
    p.category,
    c.region,
    count(*) AS transaction_count,
    sum(f.quantity) AS units_sold,
    sum(f.sales_amount) AS revenue
FROM read_parquet(
    'data/parquet/fact_sales/*/*/*.parquet',
    hive_partitioning = true
) AS f
JOIN warehouse.dim_product AS p ON f.product_key = p.product_key
JOIN warehouse.dim_customer AS c ON f.customer_key = c.customer_key
WHERE f.sale_year = 2025
  AND f.sale_month = 6
GROUP BY p.category, c.region
ORDER BY p.category, c.region;

What does this query do?

  • The Parquet scan exposes the folder values as sale_year plus sale_month columns.
  • The two filters select the June 2025 partition.
  • The dimension joins restore the category plus region labels used by the business report.
  • The aggregates calculate transaction count, units sold, and revenue for each result group.
  • Save sql/05_profile_partitioned.sql.
  • Switch back to the DuckDB CLI session.
  • Run the file-backed business query by executing this command:
.read sql/05_profile_partitioned.sql

What does this command show?

DuckDB reads the partitioned files for June 2025. You should see revenue results grouped by product category plus customer region.

The business result confirms that the exported storage remains queryable through the warehouse dimensions. You can now add execution profiling to inspect how DuckDB produced it.

No business results returned?

Confirm that data/parquet/fact_sales contains the exported files. Check that both filters use the numeric values 2025 plus 6.

Verify that the joins use product_key plus customer_key from the existing warehouse dimensions.

Help me troubleshoot an empty partitioned sales query.

EXPLAIN ANALYZE executes the query while recording actual operator timing plus row counts. Running it against the file-backed query gives you evidence of the scan path and partition filtering behavior.

  • Return to sql/05_profile_partitioned.sql.
  • Append the profiled version of the query by pasting this code:
EXPLAIN ANALYZE
SELECT
    p.category,
    c.region,
    count(*) AS transaction_count,
    sum(f.quantity) AS units_sold,
    sum(f.sales_amount) AS revenue
FROM read_parquet(
    'data/parquet/fact_sales/*/*/*.parquet',
    hive_partitioning = true
) AS f
JOIN warehouse.dim_product AS p ON f.product_key = p.product_key
JOIN warehouse.dim_customer AS c ON f.customer_key = c.customer_key
WHERE f.sale_year = 2025
  AND f.sale_month = 6
GROUP BY p.category, c.region
ORDER BY p.category, c.region;

What does the profile reveal?

The profiled query keeps the same joins, filters, and aggregates as the business query. Its plan records the operators DuckDB used plus their measured runtime on your computer.

  • Save sql/05_profile_partitioned.sql.
  • Switch back to the DuckDB CLI session.

Before you run the full script, do you expect the year and month predicates to limit the Parquet files DuckDB needs to scan?

  • Generate the partition-filtered execution plan by running this command:
.read sql/05_profile_partitioned.sql

What does the full run produce?

The script first returns the June 2025 business result. It then executes the same query through EXPLAIN ANALYZE and prints the measured plan.

You should see the plan execute through read_parquet with the sale_year = 2025 plus sale_month = 6 predicates applied as partition filters. This proves that unrelated year-and-month files can be skipped.

  • Return to the baseline EXPLAIN ANALYZE plan from sql/02_profile_raw.sql.
  • Compare the scan source used by each plan.
  • Compare the reported total time from each plan.

The file-backed plan can have a different total time from the raw-table plan. Treat each timing as an observation from your hardware plus its current system load.

Partition filters missing from the plan?

Confirm that hive_partitioning = true remains inside the read_parquet call. Check that the query filters the partition columns named sale_year plus sale_month.

Make sure the file path still crosses both Hive folder levels before reaching *.parquet.

Help me diagnose missing partition pruning in this DuckDB plan.

✔️ Awesome, I've got everything!

  • Confirm that sql/05_profile_partitioned.sql is saved before continuing.

ⓧ I'd like to double check the full code

SELECT
    p.category,
    c.region,
    count(*) AS transaction_count,
    sum(f.quantity) AS units_sold,
    sum(f.sales_amount) AS revenue
FROM read_parquet(
    'data/parquet/fact_sales/*/*/*.parquet',
    hive_partitioning = true
) AS f
JOIN warehouse.dim_product AS p ON f.product_key = p.product_key
JOIN warehouse.dim_customer AS c ON f.customer_key = c.customer_key
WHERE f.sale_year = 2025
  AND f.sale_month = 6
GROUP BY p.category, c.region
ORDER BY p.category, c.region;

EXPLAIN ANALYZE
SELECT
    p.category,
    c.region,
    count(*) AS transaction_count,
    sum(f.quantity) AS units_sold,
    sum(f.sales_amount) AS revenue
FROM read_parquet(
    'data/parquet/fact_sales/*/*/*.parquet',
    hive_partitioning = true
) AS f
JOIN warehouse.dim_product AS p ON f.product_key = p.product_key
JOIN warehouse.dim_customer AS c ON f.customer_key = c.customer_key
WHERE f.sale_year = 2025
  AND f.sale_month = 6
GROUP BY p.category, c.region
ORDER BY p.category, c.region;

How should I use this reference?

Compare this complete file with your saved script. The business query should appear first. The matching EXPLAIN ANALYZE query should follow it.

Your warehouse now has a million-row file-backed storage layer with visible partition pruning. Next, you will reconcile the raw source, star schema, and Parquet output before presenting the warehouse as complete.

Validate and Present the Warehouse

Your DuckDB warehouse now has a star schema for analysis. Its partitioned Parquet layer supports file pruning for focused queries.

A fast query profile only proves performance. This step proves that the transformation preserved every source row and revenue amount.

In this step, get ready to:
  • Create persisted checks for row counts, revenue totals, and dimension keys.
  • Run every check before trusting the warehouse results.
  • Retain the database, scripts, storage observations, and query-plan evidence.
Create the warehouse validation script

Reconciliation tests compare the warehouse with its source at known control points. This script persists seven results in warehouse.validation_results so the evidence remains available after the session.

  • Select the sql folder in the Visual Studio Code Explorer.
  • Create 06_validate.sql inside the selected folder.

You should see 06_validate.sql beneath the five existing scripts.

  • Add the shared source, fact, Parquet, revenue, and customer-key metrics by pasting this first section into sql/06_validate.sql:
CREATE OR REPLACE TABLE warehouse.validation_results AS
WITH metrics AS (
    SELECT
        (SELECT count(*) FROM raw.sales_events) AS raw_rows,
        (SELECT count(*) FROM warehouse.fact_sales) AS fact_rows,
        (
            SELECT count(*)
            FROM read_parquet(
                'data/parquet/fact_sales/*/*/*.parquet',
                hive_partitioning = true
            )
        ) AS parquet_rows,
        (
            SELECT sum(CAST(quantity AS DECIMAL(18, 2)) * unit_price)
            FROM raw.sales_events
        ) AS raw_revenue,
        (SELECT sum(sales_amount) FROM warehouse.fact_sales) AS fact_revenue,
        (
            SELECT count(*)
            FROM warehouse.fact_sales AS f
            LEFT JOIN warehouse.dim_customer AS c
                ON f.customer_key = c.customer_key
            WHERE c.customer_key IS NULL
        ) AS orphan_customer_rows,

What do these metrics capture?

  • The row-count metrics compare the raw source, fact table, and partitioned files.
  • The revenue metrics calculate the source value independently from the precomputed fact value.
  • The customer lookup counts fact rows whose customer key has no matching dimension row.
  • Continue the same metrics definition by pasting this section directly below the previous code:
        (
            SELECT count(*)
            FROM warehouse.fact_sales AS f
            LEFT JOIN warehouse.dim_product AS p
                ON f.product_key = p.product_key
            WHERE p.product_key IS NULL
        ) AS orphan_product_rows,
        (
            SELECT count(*)
            FROM warehouse.fact_sales AS f
            LEFT JOIN warehouse.dim_date AS d
                ON f.date_key = d.date_key
            WHERE d.date_key IS NULL
        ) AS orphan_date_rows
)

How do the orphan checks work?

Each left join keeps every fact row. A missing dimension match leaves a null key on the dimension side.

A count of zero proves that every product key and date key resolves successfully.

  • Add the three row-count checks directly below the completed metrics definition:
SELECT
    'raw_row_count' AS check_name,
    CAST(raw_rows AS VARCHAR) AS observed_value,
    '1000000' AS expected_value,
    raw_rows = 1000000 AS passed
FROM metrics
UNION ALL
SELECT
    'fact_matches_raw',
    CAST(fact_rows AS VARCHAR),
    CAST(raw_rows AS VARCHAR),
    fact_rows = raw_rows
FROM metrics
UNION ALL
SELECT
    'parquet_matches_fact',
    CAST(parquet_rows AS VARCHAR),
    CAST(fact_rows AS VARCHAR),
    parquet_rows = fact_rows
FROM metrics

What do the count checks prove?

  • The raw source must contain the expected 1000000 transactions.
  • The fact table must preserve the raw source row count.
  • The Parquet layer must preserve the fact-table row count.
  • Continue the validation union with the revenue and first two dimension-key checks:
UNION ALL
SELECT
    'revenue_reconciles',
    CAST(fact_revenue AS VARCHAR),
    CAST(raw_revenue AS VARCHAR),
    fact_revenue = raw_revenue
FROM metrics
UNION ALL
SELECT
    'customer_keys_resolve',
    CAST(orphan_customer_rows AS VARCHAR),
    '0',
    orphan_customer_rows = 0
FROM metrics
UNION ALL
SELECT
    'product_keys_resolve',
    CAST(orphan_product_rows AS VARCHAR),
    '0',
    orphan_product_rows = 0
FROM metrics

What do these checks prove?

The revenue check compares the additive fact measure with a fresh calculation from the raw source. An exact match shows that loading preserved the monetary total.

The customer and product checks require zero unmatched dimension keys. That result confirms that both lookups succeeded for every fact row.

  • Complete the validation table and add its two result queries with this final section:
UNION ALL
SELECT
    'date_keys_resolve',
    CAST(orphan_date_rows AS VARCHAR),
    '0',
    orphan_date_rows = 0
FROM metrics;

SELECT *
FROM warehouse.validation_results
ORDER BY check_name;

SELECT *
FROM warehouse.v_monthly_sales
WHERE calendar_year = 2025
  AND calendar_month = 6
ORDER BY revenue DESC
LIMIT 10;

What does the final output show?

The first query displays every persisted check in a consistent order. The second query returns the ten highest-revenue category and region combinations for June 2025.

Together they connect technical reconciliation evidence with a business-facing warehouse result.

  • Save sql/06_validate.sql.
  • Compare the saved file with the complete reference below.

✔️ Awesome, I've got everything!

Your validation script now contains the shared metrics, seven persisted checks, and the June 2025 report.

ⓧ I'd like to double check the full code

CREATE OR REPLACE TABLE warehouse.validation_results AS
WITH metrics AS (
    SELECT
        (SELECT count(*) FROM raw.sales_events) AS raw_rows,
        (SELECT count(*) FROM warehouse.fact_sales) AS fact_rows,
        (
            SELECT count(*)
            FROM read_parquet(
                'data/parquet/fact_sales/*/*/*.parquet',
                hive_partitioning = true
            )
        ) AS parquet_rows,
        (
            SELECT sum(CAST(quantity AS DECIMAL(18, 2)) * unit_price)
            FROM raw.sales_events
        ) AS raw_revenue,
        (SELECT sum(sales_amount) FROM warehouse.fact_sales) AS fact_revenue,
        (
            SELECT count(*)
            FROM warehouse.fact_sales AS f
            LEFT JOIN warehouse.dim_customer AS c
                ON f.customer_key = c.customer_key
            WHERE c.customer_key IS NULL
        ) AS orphan_customer_rows,
        (
            SELECT count(*)
            FROM warehouse.fact_sales AS f
            LEFT JOIN warehouse.dim_product AS p
                ON f.product_key = p.product_key
            WHERE p.product_key IS NULL
        ) AS orphan_product_rows,
        (
            SELECT count(*)
            FROM warehouse.fact_sales AS f
            LEFT JOIN warehouse.dim_date AS d
                ON f.date_key = d.date_key
            WHERE d.date_key IS NULL
        ) AS orphan_date_rows
)
SELECT
    'raw_row_count' AS check_name,
    CAST(raw_rows AS VARCHAR) AS observed_value,
    '1000000' AS expected_value,
    raw_rows = 1000000 AS passed
FROM metrics
UNION ALL
SELECT
    'fact_matches_raw',
    CAST(fact_rows AS VARCHAR),
    CAST(raw_rows AS VARCHAR),
    fact_rows = raw_rows
FROM metrics
UNION ALL
SELECT
    'parquet_matches_fact',
    CAST(parquet_rows AS VARCHAR),
    CAST(fact_rows AS VARCHAR),
    parquet_rows = fact_rows
FROM metrics
UNION ALL
SELECT
    'revenue_reconciles',
    CAST(fact_revenue AS VARCHAR),
    CAST(raw_revenue AS VARCHAR),
    fact_revenue = raw_revenue
FROM metrics
UNION ALL
SELECT
    'customer_keys_resolve',
    CAST(orphan_customer_rows AS VARCHAR),
    '0',
    orphan_customer_rows = 0
FROM metrics
UNION ALL
SELECT
    'product_keys_resolve',
    CAST(orphan_product_rows AS VARCHAR),
    '0',
    orphan_product_rows = 0
FROM metrics
UNION ALL
SELECT
    'date_keys_resolve',
    CAST(orphan_date_rows AS VARCHAR),
    '0',
    orphan_date_rows = 0
FROM metrics;

SELECT *
FROM warehouse.validation_results
ORDER BY check_name;

SELECT *
FROM warehouse.v_monthly_sales
WHERE calendar_year = 2025
  AND calendar_month = 6
ORDER BY revenue DESC
LIMIT 10;

How to use this reference

Compare the complete file with your saved script from top to bottom. Check each table name, path, expected value, and join key carefully.

Run the reconciliation checks

The script creates a persistent audit table before displaying its results. Any failed row identifies a specific boundary where source data, the dimensional model, or file-backed storage diverged.

Before you run the script, do you expect all seven checks to agree?

  • Execute the completed validation script in the DuckDB session from earlier by running:
.read sql/06_validate.sql

What does this command do?

The command reads every statement from sql/06_validate.sql into the connected database session. It replaces warehouse.validation_results with results from the warehouse's current state.

You should see seven validation rows. Every value in the passed column should be true.

Below the checks, you should see ten June 2025 monthly sales rows grouped by category and region.

That is the trust layer complete. Your source, fact table, partitioned files, dimension lookups, and revenue total now reconcile.

Seeing a failed validation?

  • Use observed_value and expected_value to identify the mismatched layer.
  • Check the raw generation result when raw_row_count fails.
  • Check the star-schema load when fact_matches_raw or a key-resolution check fails.
  • Check the latest partitioned export when parquet_matches_fact fails.
  • Check the source calculation and fact measure when revenue_reconciles fails.

After correcting the affected layer, rerun the validation script.

Help me diagnose a failed warehouse validation check.

Retain your demonstration artifacts

A strong warehouse demonstration connects the model to measurable evidence. Your retained artifacts should show scale, storage design, execution behavior, and data quality.

  • Save a screenshot or note from the raw-table plan produced by sql/02_profile_raw.sql.
  • Save a screenshot or note from the partition-filtered plan produced by sql/05_profile_partitioned.sql.
  • Press the Windows key to open the system search.
  • Type File Explorer into the search field.
  • Press Enter to open File Explorer.
  • Navigate to the current company-warehouse workspace location.
  • Record the displayed storage size for company_warehouse.duckdb.
  • Record the displayed storage size for data/parquet/fact_sales.

Present evidence with context

Keep the raw and partition-filtered plan times beside their storage observations. Local timings depend on hardware and current system load.

Focus your explanation on the operators, partition filters, row reconciliation, and modeling decisions that the evidence supports.

Before your final review, do you expect every warehouse layer to have visible evidence?

  • Return to the Visual Studio Code Explorer.
  • Confirm that sql/01_generate_raw.sql, sql/02_profile_raw.sql, sql/03_build_star.sql, sql/04_export_partitioned.sql, sql/05_profile_partitioned.sql, and sql/06_validate.sql are present.
  • Confirm that company_warehouse.duckdb remains in the workspace.
  • Confirm that data/parquet/fact_sales retains its year and month partition folders.
  • Confirm that your two plan captures or notes remain with the storage-size observations.

You should now have six SQL scripts, a persistent database, one million partitioned fact rows, seven passing validations, two query-plan records, and storage-size observations.

Secret mission

Scale the Benchmark to Five Million Rows

Push the same warehouse architecture from one million to five million sales rows. Rebuild every analytical layer, compare both query profiles, and prove that the scaled data still reconciles.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your resources available, pause your work, or delete the project entirely. Your DuckDB warehouse runs entirely on your Windows computer, so keeping these resources costs $0.

Resources you used:

  • Persistent database: company_warehouse.duckdb. It contains the five-million-row raw source, the dimensional warehouse, the monthly sales view, and warehouse.validation_results.
  • Six main SQL scripts: sql/01_generate_raw.sql, sql/02_profile_raw.sql, sql/03_build_star.sql, sql/04_export_partitioned.sql, sql/05_profile_partitioned.sql, and sql/06_validate.sql.
  • Scale-test script: sql/secret_scale_test.sql. It generates the five-million-row benchmark.
  • Hive-partitioned Parquet directory: data/parquet/fact_sales. It contains five million exported fact rows organized by sale year and month.

Keep everything running

No action is needed. Choose this option if you want to keep querying the scaled warehouse or comparing its original and five-million-row profiles.

  • Retain the complete company-warehouse workspace so the database, scripts, and Parquet files stay together.
  • Continue using Visual Studio Code to inspect the scripts and recorded benchmark results.
  • Keep the connected DuckDB session available while you are actively querying the warehouse.
  • Leave the local resources idle at no ongoing cost when you finish.

Pause - I'll come back to this later

Pausing ends the active database session while preserving the database, scripts, and Parquet files for your return.

  • Exit the connected DuckDB session by entering this command:
.exit

This closes the DuckDB CLI connection. The persistent database remains stored in company_warehouse.duckdb.

  • Close Visual Studio Code when you are ready to step away.
  • Retain the complete company-warehouse folder so every local artifact remains available.

Delete - I don't want to use this again

Remove the complete local workspace when you no longer need the warehouse or its benchmark artifacts.

  • Exit the connected DuckDB session by entering this command:
.exit

This releases the open connection to company_warehouse.duckdb before you remove the workspace.

Deleting the workspace is permanent. It removes the project resources while leaving DuckDB and Visual Studio Code installed.

  • Close Visual Studio Code.
  • Press the Windows key to open the search bar.
  • Type File Explorer into the search bar.
  • Press Enter to open File Explorer.
  • Return to the location that contains the company-warehouse folder.
  • Delete the company-warehouse folder permanently from File Explorer.
  • Confirm that company-warehouse no longer appears in File Explorer.

The database, validation table, SQL scripts, scale-test script, and partitioned Parquet directory are now removed.

Nice Work!

Nice Work!

You did it! Your local DuckDB retail warehouse now holds five million validated sales facts in a star schema with partitioned Parquet storage.

What you learned:

  • Generated the original one-million-row retail workload entirely in SQL. Converted source business keys into three conformed dimensions through surrogate keys. Loaded a transaction-grain sales fact table.
  • Published a reusable monthly sales view for category-by-region reporting. Exported the fact data into year-and-month Hive-partitioned storage.
  • Captured query profiles for both June 2025 report paths with EXPLAIN ANALYZE. Reconciled source row counts against warehouse row counts. Reconciled warehouse row counts against Parquet row counts. Verified revenue totals. Confirmed every dimension key resolves.
  • Secret Mission: Scaled the same architecture to five million sales rows. Rebuilt every storage layer. Compared the original benchmark with the scaled query profiles.

Ready to quiz yourself?