Build a Local Data Lake with DuckDB

Build a local retail data lake with DuckDB, Parquet, and SQL.

Introduction

30 Second Summary

Retail order files often arrive in separate batches. One repeated order can quietly make the day's revenue look more successful than it really is.

In this project, you will build a local retail sales data lake with DuckDB. You will trace an inflated total from raw CSV batches into corrected, partitioned Parquet reports.

What You'll Build

You will run analytical SQL on your own computer to turn separate order batches into a trusted regional revenue report.

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

  • A multi-file raw query that loads separate order batches as one logical dataset.
  • A duplicate check that shows seven loaded rows against six unique orders. The inflated revenue reads 805.00.
  • A partitioned revenue report that shows the corrected 625.00 total by date and region.
  • Secret Mission: Quarantine a negative-quantity order while keeping the original raw batch unchanged.

Are there any prerequisites?

You need a Windows computer with basic SQL knowledge. No cloud account is required because every file and query stays local.

Before We Start

Before any files or queries exist, take a moment to lock in what you are building and why it matters. You are committing to a fully local retail sales data lake that preserves source records while producing trusted data for analysis.

Set Up DuckDB on Windows

Your local data lake needs a pinned query engine before any order batches arrive. A predictable folder structure keeps each lake layer separate.

In this step, you will prepare DuckDB through Windows PowerShell. You will also create the empty workspace that later holds your raw files and curated Parquet data.

In this step, get ready to:
  • Confirm the Windows tools needed for setup.
  • Prepare DuckDB CLI version v1.4.5.
  • Create the empty local-data-lake folder structure.
Confirm WinGet and Notepad

WinGet installs the pinned DuckDB release. Notepad gives you a local editor for the CSV files and SQL scripts you create later.

  • Press the Windows key to open the search bar.
  • Type Windows PowerShell into the search bar.
  • Press Enter to open PowerShell.
  • Check whether WinGet is available by running this command:
winget --version

What does this check do?

The command asks WinGet to report its installed version. A version number confirms that Windows Package Manager is ready to install DuckDB.

  • Choose the tab that matches the result in PowerShell.

✔️ I see a version number

Your package manager is ready. You can continue to the Notepad check.

ⓧ Command not found

WinGet can be present through App Installer without being registered for your account. Request that registration before retrying the check.

  • Request App Installer registration by running this command:
Add-AppxPackage -RegisterByFamilyName -MainPackage Microsoft.DesktopAppInstaller_8wekyb3d8bbwe

What does this recovery command do?

The command registers the App Installer package for your Windows account. This makes its WinGet command available to PowerShell.

  • Retry the WinGet check by running this command:
winget --version

What should you see?

You should now see a WinGet version number. That result confirms the registration worked.

Get help with registering WinGet on Windows.

  • Confirm Notepad is available by running this command:
notepad.exe

What should you see?

You should see a blank Notepad window. This confirms that the editor is ready for the project files you create in the next step.

Install and verify DuckDB

The project uses a pinned DuckDB release so every query follows the same supported behavior. The version check below identifies whether the correct client is already installed.

  • Return to the PowerShell window from the previous check.
  • Check the current DuckDB version by running this command:
duckdb -version

How do you read the result?

The command asks the DuckDB CLI to report its version. Use the result to select the matching path below.

  • Choose the tab that matches the result in PowerShell.

✔️ I see the required version

Your DuckDB CLI reports v1.4.5. That is the pinned client this project needs.

ⓧ I see an older version

Your current client is below the project pin. Install DuckDB CLI version 1.4.5 so the later SQL workflow uses the expected release.

  • Install the pinned DuckDB client by running this command:
winget install DuckDB.cli --version 1.4.5

What does this command do?

WinGet downloads DuckDB CLI version 1.4.5 for Windows. It prints installation progress in PowerShell.

  • Close the current PowerShell window after the installation finishes.
  • Press the Windows key to open the search bar.
  • Type Windows PowerShell into the search bar.
  • Press Enter to open a fresh PowerShell window.
  • Verify the pinned client by running this command:
duckdb -version

What should you see?

You should see DuckDB CLI version v1.4.5. The fresh window allows PowerShell to find the newly installed command.

ⓧ Command not found

DuckDB is not installed yet. WinGet can install the exact release used throughout this project.

  • Install the pinned DuckDB client by running this command:
winget install DuckDB.cli --version 1.4.5

What does this command do?

WinGet downloads DuckDB CLI version 1.4.5 for Windows. It prints installation progress in PowerShell.

  • Close the current PowerShell window after the installation finishes.
  • Press the Windows key to open the search bar.
  • Type Windows PowerShell into the search bar.
  • Press Enter to open a fresh PowerShell window.
  • Verify the installed client by running this command:
duckdb -version

What should you see?

You should see DuckDB CLI version v1.4.5. Your local query engine is now ready.

DuckDB Needs a Runtime Library?

Some Windows systems need the Visual C++ v14 Redistributable before DuckDB can start. Install it only when the version check reports a missing runtime dependency.

  • Download the runtime from Microsoft's x64 installer.
  • Run the downloaded installer.
  • Approve the Windows permission request if it appears.
  • Return to the fresh PowerShell window.
  • Repeat the DuckDB version check above.

Get help with a DuckDB runtime dependency on Windows.

Create the lake workspace

The workspace separates untouched source batches from processed outputs. It also reserves a quarantine area for records that fail quality checks later.

  • Move to your Desktop by running this command in the fresh PowerShell window:
sl $home\desktop

What does this command do?

The command changes PowerShell's current location to your Desktop. The project folder will be easy to find there.

  • Create the empty lake directories by running these commands:
New-Item -ItemType Directory -Force -Path ".\local-data-lake\raw\orders"
New-Item -ItemType Directory -Force -Path ".\local-data-lake\curated"
New-Item -ItemType Directory -Force -Path ".\local-data-lake\rejected"
Set-Location -Path ".\local-data-lake" -PassThru

What does this workspace contain?

  • The raw\orders directory receives unchanged CSV batches.
  • The curated directory receives trusted analytical files.
  • The rejected directory receives quarantined records.

The final command enters local-data-lake. PowerShell prints the resulting path because the command includes -PassThru.

Workspace Path Missing?

Check that PowerShell was on your Desktop before you created the directories. The output should show a path ending in local-data-lake.

Get help with creating the local-data-lake directories in PowerShell.

Before you run the final check, which DuckDB version should this fresh PowerShell window report?

  • Verify DuckDB from inside the workspace by running this command:
duckdb -version

What should you see?

You should see DuckDB CLI version v1.4.5. This proves the pinned engine is available from your local-data-lake workspace.

Your local query engine is ready inside its workspace. Next, you will land two raw order batches without changing their source records.

Land and Query Raw Batches

Your local workspace is ready. DuckDB is available for the first data load.

Now the raw layer must preserve every source record exactly. A faithful load still needs a quality check before you can trust its totals.

In this step, get ready to:
  • Land two unchanged order batches in the raw folder.
  • Create a query that reads both batches through an explicit schema.
  • Compare the loaded row count with the unique order count.
Land the raw CSV batches

A CSV batch represents records received from a source system. Keeping each batch unchanged gives you evidence of what originally arrived.

  • Create raw\orders\batch-1.csv in the existing workspace by running these commands:
New-Item -Path ".\raw\orders\batch-1.csv" -ItemType File
notepad.exe ".\raw\orders\batch-1.csv"

What do these commands do?

The first command creates an empty batch-1.csv file inside raw\orders. The second command opens that file in Notepad so you can add the source records.

  • Paste the first source batch into batch-1.csv using this exact content:
order_id,order_date,region,product,quantity,unit_price
1001,2026-09-01,North,Keyboard,2,45.00
1002,2026-09-01,South,Mouse,3,20.00
1003,2026-09-01,West,Monitor,1,180.00

What is in the first batch?

The header defines six order fields. The three data rows preserve the first set of retail transactions exactly as received.

  • Save raw\orders\batch-1.csv.
  • Close the Notepad window for batch-1.csv.

You should see one header row plus three order rows before you close the file.

Did Notepad open the wrong file?

Close the untitled file without saving. Run the two PowerShell lines again from the local-data-lake workspace.

Help me open the first raw order batch from the correct PowerShell folder.

✔️ Awesome, I've got batch 1

Great. Your first immutable source batch is saved with three order rows.

ⓧ I'd like to double check batch 1

Compare your complete raw/orders/batch-1.csv file with this reference:

order_id,order_date,region,product,quantity,unit_price
1001,2026-09-01,North,Keyboard,2,45.00
1002,2026-09-01,South,Mouse,3,20.00
1003,2026-09-01,West,Monitor,1,180.00

What should match?

The header must appear once. Each field value must match the source batch exactly.

  • Create raw\orders\batch-2.csv in the existing workspace by running these commands:
New-Item -Path ".\raw\orders\batch-2.csv" -ItemType File
notepad.exe ".\raw\orders\batch-2.csv"

What changes this time?

These commands create a separate source file for the second delivery. Preserving the batches separately keeps their arrival boundaries visible.

  • Paste the second source batch into batch-2.csv using this exact content:
order_id,order_date,region,product,quantity,unit_price
1003,2026-09-01,West,Monitor,1,180.00
1004,2026-09-02,North,Dock,2,85.00
1005,2026-09-02,South,Keyboard,1,45.00
1006,2026-09-02,West,Mouse,4,20.00

What is in the second batch?

The second file uses the same six-column structure. Its four rows form another unchanged source delivery.

  • Save raw\orders\batch-2.csv.
  • Close the Notepad window for batch-2.csv.

You should see one header row plus four order rows before you close the file.

Does the second batch look incomplete?

Confirm that the file contains four data rows below the header. Check that every row has six comma-separated values.

Help me compare my second CSV batch with the required source records.

✔️ Awesome, I've got batch 2

Both source batches are now preserved inside raw\orders.

ⓧ I'd like to double check batch 2

Compare your complete raw/orders/batch-2.csv file with this reference:

order_id,order_date,region,product,quantity,unit_price
1003,2026-09-01,West,Monitor,1,180.00
1004,2026-09-02,North,Dock,2,85.00
1005,2026-09-02,South,Keyboard,1,45.00
1006,2026-09-02,West,Mouse,4,20.00

What should match?

The file must contain the header plus four unchanged order rows. Matching the reference preserves the source batch as an audit record.

Build the raw query script

The SQL script uses an explicit schema to control every column type. A glob lets one query read every matching batch as a single dataset.

  • Create 01_raw_query.sql in the local-data-lake workspace by running these commands:
New-Item -Path ".\01_raw_query.sql" -ItemType File
notepad.exe ".\01_raw_query.sql"

Why create a script file?

The script stores the raw-loading workflow as repeatable SQL. You can run the same checks again whenever another source batch arrives.

  • Paste the raw view definition at the top of 01_raw_query.sql using this code:
CREATE OR REPLACE VIEW raw_orders AS
SELECT *
FROM read_csv(
    'raw/orders/*.csv',
    delim = ',',
    header = true,
    columns = {
        'order_id': 'INTEGER',
        'order_date': 'DATE',
        'region': 'VARCHAR',
        'product': 'VARCHAR',
        'quantity': 'INTEGER',
        'unit_price': 'DECIMAL(10,2)'
    }
);

What does the raw view do?

  • The raw/orders/*.csv pattern reads every matching CSV file in the raw order folder.
  • The delim setting tells DuckDB that commas separate the fields.
  • The header setting treats the first line as column names.
  • The columns map enforces the expected numeric, date, text, quantity, and currency types.
  • Save 01_raw_query.sql.

The script should now end with the closing ); for the raw_orders view.

Is the schema hard to compare?

Check each column name against the CSV header. Confirm that every quoted type matches the reference code.

Help me compare my raw_orders view definition with the required schema.

  • Add the row inspection query below the view definition:
SELECT *
FROM raw_orders
ORDER BY order_id;

Why inspect every row?

This query prints every loaded record in order of order_id. The ordered output makes repeated or missing order IDs easier to spot.

  • Save 01_raw_query.sql.

You should now see the row inspection query directly below the view definition.

Is the row query in the wrong place?

Confirm that the view definition ends before the second SELECT begins. Keep ORDER BY order_id; at the end of this query.

Help me place the row inspection query correctly in my SQL script.

  • Add the summary query below the row inspection query:
SELECT
    count(*) AS rows_loaded,
    count(DISTINCT order_id) AS unique_orders,
    sum(quantity * unit_price) AS raw_revenue
FROM raw_orders;

What does the summary measure?

  • The rows_loaded value counts every source row returned by the glob.
  • The unique_orders value counts distinct order_id values.
  • The raw_revenue value multiplies quantity by unit price before summing every loaded row.
  • Save 01_raw_query.sql.
  • Close the Notepad window for 01_raw_query.sql.

Your saved script should contain one view definition plus two validation queries.

Does the summary look different?

Check the two count expressions for matching parentheses. Confirm that the revenue expression multiplies quantity by unit_price.

Help me compare the summary query with the required raw data checks.

✔️ Awesome, I've got everything

Your source batches are saved. Your raw query script is ready to run.

ⓧ I'd like to double check the full code

Compare your complete 01_raw_query.sql file with this reference:

CREATE OR REPLACE VIEW raw_orders AS
SELECT *
FROM read_csv(
    'raw/orders/*.csv',
    delim = ',',
    header = true,
    columns = {
        'order_id': 'INTEGER',
        'order_date': 'DATE',
        'region': 'VARCHAR',
        'product': 'VARCHAR',
        'quantity': 'INTEGER',
        'unit_price': 'DECIMAL(10,2)'
    }
);

SELECT *
FROM raw_orders
ORDER BY order_id;

SELECT
    count(*) AS rows_loaded,
    count(DISTINCT order_id) AS unique_orders,
    sum(quantity * unit_price) AS raw_revenue
FROM raw_orders;

What should the full script contain?

The file should define raw_orders first. The row inspection and summary queries should follow in that order.

Run the raw lake check

Opening a named database file keeps the raw_orders view available after this session. The script then tests whether the loaded rows describe unique orders.

Before you run the script, do you expect every loaded row to represent a unique order?

  • Create lake.duckdb and execute the raw query workflow by entering these lines in sequence:
duckdb lake.duckdb
.read 01_raw_query.sql
.quit

What does this workflow do?

  • The first line opens or creates the persistent lake.duckdb database.
  • The second line executes every statement stored in 01_raw_query.sql.
  • The final line exits the DuckDB CLI after the results appear.

You should see seven order rows printed in the terminal. The summary reports rows_loaded as 7.

The summary reports unique_orders as 6. It reports raw_revenue as 805.00.

The mismatch is the intended shortfall. Order 1003 appears in both source batches.

Its revenue is counted twice because the raw view preserves every source row. You caught the exact issue this raw check was designed to expose.

Did the raw check fail to run?

Confirm that PowerShell is still located in the local-data-lake workspace. Check that both CSV files are inside raw\orders.

Compare 01_raw_query.sql with the full-code tab if DuckDB reports a SQL or file-loading problem.

Help me diagnose why my raw DuckDB query did not produce the expected summary.

Your raw layer now preserves both source batches while exposing an inflated aggregate. Next, you'll build a curated layer that produces trusted analytical data.

Build the Curated Parquet Layer

Your raw layer now preserves both source batches as one queryable dataset. Its exact duplicate raises the revenue total to 805.00, which makes that total unsafe for analysis.

This step creates a curated layer that keeps one copy of each exact order. DuckDB writes those trusted rows as Parquet files organized through Hive partitioning.

In this step, get ready to:
  • Create a deduplicated view with calculated revenue.
  • Validate the corrected row count and revenue total.
  • Export the trusted orders into date-partitioned Parquet files.
Create the curated SQL script

The curated_orders view uses SELECT DISTINCT across every source column to remove the exact repeated order. It also calculates a reusable revenue value for each trusted row.

  • Press the Windows key to open the search bar.
  • Type Notepad into the search bar.
  • Press Enter to launch Notepad.
  • Add the curated view by pasting this code into the blank document:
CREATE OR REPLACE VIEW curated_orders AS
SELECT DISTINCT
    order_id,
    order_date,
    region,
    product,
    quantity,
    unit_price,
    quantity * unit_price AS revenue
FROM raw_orders;

What does this code do?

  • The curated_orders view reads from the existing raw_orders view, so the source CSV files remain unchanged.
  • The SELECT DISTINCT clause keeps one copy of the duplicated order.
  • The revenue expression multiplies each order's quantity by its unit price.
  • Press Ctrl+Shift+S to open Notepad's save window.
  • Select the local-data-lake folder that contains 01_raw_query.sql.
  • Enter 02_build_curated.sql in the filename field.
  • Choose the file-type option that keeps the .sql extension.
  • Save the file.
  • Confirm Notepad's title shows 02_build_curated.sql.

Did Notepad add a text extension?

Check whether the title shows 02_build_curated.sql.txt. Save the document again with a file type that preserves the .sql extension.

Ask for help if the extension still changes: Help me save a SQL file in Notepad without adding a text extension.

Validate the deduplicated view

A quality check makes the effect of deduplication visible. This query counts the curated rows before adding their calculated revenue.

  • Add the validation query by pasting this code below the existing statement:
SELECT
    count(*) AS curated_rows,
    sum(revenue) AS curated_revenue
FROM curated_orders;

What does this check measure?

  • The curated_rows result counts the rows remaining after exact duplicates are removed.
  • The curated_revenue result adds the calculated revenue from those remaining rows.
  • Save 02_build_curated.sql.

Before you run the check, do you expect the curated row count to match the seven raw rows or the six unique orders?

  • Return to PowerShell from earlier.
  • Open the persistent database by running this command:
duckdb lake.duckdb

What does this command open?

The command opens the existing lake.duckdb database. That database already contains the raw_orders view used by your new script.

  • Execute the saved SQL script by entering:
.read 02_build_curated.sql

What does this script run prove?

The .read command executes the statements saved in 02_build_curated.sql. Its result tests the deduplicated view against the existing raw data.

You should see curated_rows equal to 6. You should also see curated_revenue equal to 625.00.

Seeing the wrong totals?

Confirm that SELECT DISTINCT appears directly after the first SELECT in 02_build_curated.sql. Check that the view reads from raw_orders.

Ask for help with the result: Help me troubleshoot why curated_orders does not return six rows and 625.00 revenue.

Write the date-partitioned Parquet dataset

The final statement exports the trusted view into a file-based analytical layer. Each order date receives its own partition folder.

  • Switch back to the open 02_build_curated.sql document in Notepad.
  • Add the export statement by pasting this code below the validation query:
COPY (SELECT * FROM curated_orders)
TO 'curated/orders'
(FORMAT parquet, PARTITION_BY (order_date), OVERWRITE_OR_IGNORE);

How does the export work?

  • The COPY statement exports every row from curated_orders.
  • The FORMAT parquet option writes the output as Parquet files.
  • The PARTITION_BY (order_date) option creates one folder for each order date.
  • The OVERWRITE_OR_IGNORE option refreshes existing local partition folders when the script runs again.
  • Save 02_build_curated.sql.

✔️ Awesome, I've got everything!

Your 02_build_curated.sql file now defines the view, validates its totals, and exports the partitioned dataset.

ⓧ I'd like to double check the full code

Compare your complete 02_build_curated.sql file with this version:

CREATE OR REPLACE VIEW curated_orders AS
SELECT DISTINCT
    order_id,
    order_date,
    region,
    product,
    quantity,
    unit_price,
    quantity * unit_price AS revenue
FROM raw_orders;

SELECT
    count(*) AS curated_rows,
    sum(revenue) AS curated_revenue
FROM curated_orders;

COPY (SELECT * FROM curated_orders)
TO 'curated/orders'
(FORMAT parquet, PARTITION_BY (order_date), OVERWRITE_OR_IGNORE);

Before you run the complete script, do you expect the corrected totals to stay the same after the Parquet export is added?

  • Switch back to the DuckDB session from earlier.
  • Execute the complete script by entering:
.read 02_build_curated.sql

What happens on this run?

The script recreates curated_orders before checking its totals. It then writes the same six trusted orders to curated/orders.

You should again see curated_rows equal to 6. You should again see curated_revenue equal to 625.00.

Did the export fail?

Confirm that the destination is exactly curated/orders. Check that you launched DuckDB from the local-data-lake workspace.

Ask for help with the export: Help me troubleshoot why DuckDB cannot write my curated Parquet dataset.

  • Exit DuckDB by entering:
.quit

What does this command do?

The .quit command closes the DuckDB session. Your persistent views remain stored in lake.duckdb.

  • Inspect the exported partition folders in PowerShell by running:
Get-ChildItem -Path ".\curated\orders" -Recurse

What does this listing show?

The recursive listing displays every folder and file below curated/orders. The partition folder names expose the date values used to organize the dataset.

That's the curated layer built: you should see order_date=2026-09-01 and order_date=2026-09-02 folders containing Parquet files.

Cannot see both date partitions?

Confirm that the PowerShell prompt points to the local-data-lake workspace. Rerun the complete SQL script if the curated/orders folder is missing.

Ask for help with the folder output: Help me find the missing DuckDB Parquet partition folders.

Your raw records remain intact while the curated layer now carries corrected analytics. Next up, you'll query every date partition as one logical table.

Query the Partitioned Lake

Your curated layer now holds six deduplicated orders in a Parquet dataset organized with Hive partitioning.

Those files become useful when DuckDB discovers their partition values. In this step, you will query every curated file as one logical dataset.

In this step, get ready to:
  • Create a reusable lake_orders view over the partitioned files.
  • Confirm the curated totals through a partition-aware query.
  • Trace order_date to the Hive folder names.
Create the partition-aware lake check

The first query exposes every Parquet file below curated/orders through one view. An overall check then confirms that the partitioned layer preserved the corrected result.

  • Create 03_analyze.sql and open it in Notepad from PowerShell by running these commands:
New-Item -ItemType File -Path ".\03_analyze.sql"
Start-Process -FilePath "notepad.exe" -ArgumentList ".\03_analyze.sql"

What do these commands do?

  • The first command creates 03_analyze.sql inside your existing local-data-lake workspace.
  • The second command opens that file in Notepad.
  • Paste this first section into 03_analyze.sql from Notepad:
CREATE OR REPLACE VIEW lake_orders AS
SELECT *
FROM read_parquet(
    'curated/orders/*/*.parquet',
    hive_partitioning = true
);

SELECT
    count(*) AS lake_rows,
    sum(revenue) AS lake_revenue
FROM lake_orders;

What does this code do?

  • The read_parquet() call follows the wildcard path into every date partition.
  • The hive_partitioning = true setting reads order_date from the folder names.
  • The lake_orders view gives the partitioned files one reusable SQL name.
  • The final query checks the row count plus the total revenue.
  • Save 03_analyze.sql.
  • Switch back to PowerShell.
  • Start the persistent database from your existing workspace by running this command:
duckdb lake.duckdb

What does this command do?

This opens the existing lake.duckdb database. The database already contains your raw_orders view plus your curated_orders view.

Before you read the script, consider whether the lake total should match the raw total or the curated total.

  • Execute the current lake check from the DuckDB prompt by entering:
.read 03_analyze.sql

What should I see?

  • The lake_rows value is 6.
  • The lake_revenue value is 625.00.
  • The raw result was 805.00 before deduplication. The lower lake total proves that the duplicate did not flow into the Parquet layer.

Lake check not appearing?

Confirm that 03_analyze.sql is saved inside local-data-lake. Check that the wildcard contains two folder levels before the Parquet filename.

Ask for help with the partitioned lake check.

Add the revenue reports

The overall check proves the dataset is complete. A regional aggregation turns those curated rows into a result you can present.

  • Switch back to 03_analyze.sql in Notepad.
  • Add the regional aggregation below the lake check by pasting:
SELECT
    region,
    sum(revenue) AS regional_revenue
FROM lake_orders
GROUP BY region
ORDER BY region;

How does the regional report work?

  • The GROUP BY region clause collects orders from the same region.
  • The sum(revenue) expression calculates one revenue total for each group.
  • The ORDER BY region clause keeps the report in alphabetical order.
  • Save 03_analyze.sql.
  • Switch back to the DuckDB prompt.
  • Execute the updated script by entering:
.read 03_analyze.sql

What should the regional report show?

  • North has regional_revenue of 260.00.
  • South has regional_revenue of 105.00.
  • West has regional_revenue of 260.00.

Regional totals missing?

Confirm that you saved the regional query below the lake check. Check that every reference uses the existing lake_orders view.

Ask for help with the regional aggregation.

The regional report hides which date produced each amount. The final query keeps the date partition visible in the grouped result.

  • Switch back to 03_analyze.sql in Notepad.
  • Add the daily regional report below the regional aggregation by pasting:
SELECT
    order_date,
    region,
    sum(revenue) AS daily_regional_revenue
FROM lake_orders
GROUP BY order_date, region
ORDER BY order_date, region;

How does the daily report work?

  • The GROUP BY order_date, region clause creates one total for each date and region combination.
  • The daily_regional_revenue column shows the revenue inside each combination.
  • The final ordering keeps earlier dates first.
  • Save 03_analyze.sql.
Verify the date partitions

Your complete script now checks the lake before producing two levels of analysis. The last run proves that the folder partitions are available as queryable values.

Before the final run, predict where order_date comes from when the Parquet wildcard reads every partition.

  • Switch back to the DuckDB prompt.
  • Execute the complete analysis by entering:
.read 03_analyze.sql

What should the complete analysis show?

  • The lake check shows 6 rows plus total revenue of 625.00.
  • The regional report shows 260.00 for North.
  • The regional report shows 105.00 for South.
  • The regional report shows 260.00 for West.
  • The daily report shows rows for 2026-09-01 plus 2026-09-02.

Complete script stopping early?

Check that each query ends with a semicolon. Confirm that the daily report appears after the regional query in 03_analyze.sql.

Ask for help with the complete analysis script.

The order_date=2026-09-01 plus order_date=2026-09-02 folder names supply the order_date values. DuckDB can query every Parquet file as one table without loading that partition column from each file.

  • Exit DuckDB by entering:
.quit

What does this command do?

This closes the DuckDB CLI. The persistent lake_orders view remains stored in lake.duckdb.

✔️ Awesome, I've got everything!

Your saved 03_analyze.sql file now contains the lake check plus both revenue reports.

ⓧ I'd like to double check the full code

The complete 03_analyze.sql file is shown below for reference.

CREATE OR REPLACE VIEW lake_orders AS
SELECT *
FROM read_parquet(
    'curated/orders/*/*.parquet',
    hive_partitioning = true
);

SELECT
    count(*) AS lake_rows,
    sum(revenue) AS lake_revenue
FROM lake_orders;

SELECT
    region,
    sum(revenue) AS regional_revenue
FROM lake_orders
GROUP BY region
ORDER BY region;

SELECT
    order_date,
    region,
    sum(revenue) AS daily_regional_revenue
FROM lake_orders
GROUP BY order_date, region
ORDER BY order_date, region;

What does the full file contain?

This reference combines the partition-aware view with the overall check. It also contains the regional report plus the daily regional report.

That is the full lake loop working. Your partitioned Parquet files now answer overall plus regional revenue questions.

Secret mission

Quarantine a Bad Order

One new batch contains a useful sale. It also contains a negative-quantity order. Build a quality gate that preserves the source. Write valid rows to curated Parquet. Quarantine the bad record with a reason.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your local lake, pause it for later, or delete it entirely. Every file stays on your Windows computer, so there are no ongoing service charges.

Resources you used:

  • The local-data-lake project folder containing every local project artifact.

What Stays Installed?

The DuckDB CLI 1.4.5 LTS installation stays available for future projects. This cleanup only manages your local project folder.

Keep everything running

No action is needed. Choose this option if you want to keep the completed lake for future practice.

  • Keep the local-data-lake folder in its current parent directory.
  • Leave DuckDB CLI 1.4.5 installed for future local SQL work.

Pause - I'll come back to this later

Close the active tools to pause your work. Every local file stays ready for your next session.

  • Exit any active DuckDB session by running this command:
.quit

What Does This Command Do?

The command exits DuckDB. Your stored views remain inside lake.duckdb.

  • Confirm the PowerShell prompt is visible.
  • Close PowerShell.

Delete - I don't want to use this again

Deleting this folder permanently removes every project artifact. DuckDB CLI itself stays installed.

  • Move to the parent directory by running this command:
Set-Location -Path ..

Why Move Up One Level?

PowerShell cannot remove the folder while your session is working inside it. This command moves your session to the folder directly above local-data-lake.

  • Inspect the target path shown in the removal command.
  • Confirm .\local-data-lake names the project folder you intend to delete.
  • Delete the project folder by running this command:
Remove-Item -Path ".\local-data-lake" -Recurse

What Gets Deleted?

The Remove-Item command deletes local-data-lake. The -Recurse option also removes every nested file and folder.

  • Check the parent directory by running this command:
Get-ChildItem

What Should You See?

The command lists the contents of the parent directory. A successful cleanup leaves local-data-lake out of that list.

You should no longer see local-data-lake in the output.

The local workspace is cleared. Your DuckDB installation stays ready for another project.

Still See the Folder?

  • Confirm the PowerShell prompt points to the parent directory.
  • Close Notepad if a project file is still open.
  • Close any remaining DuckDB session.
  • Run the removal command again.

Help me work out why PowerShell cannot delete my local-data-lake folder.

Nice Work!

Nice Work!

You made it! Your Windows computer now hosts a working local retail data lake powered by DuckDB.

You've learned how to:

  • Built a raw layer that preserves immutable CSV batches. An explicit-schema SQL view queries every batch as one dataset.
  • Diagnosed an exact duplicate that inflated revenue to 805.00. SELECT DISTINCT restored six trusted orders without changing the raw files.
  • Wrote six trusted orders to Parquet with Hive-style date partitions. A direct analytical query returned 625.00 in total revenue. It also reported revenue by date and region.
  • Added a Secret Mission quality gate. It writes seven valid rows to curated/orders-quality. It quarantines order_id 1008 in rejected/orders with a clear rejection reason.

Ready to quiz yourself?