Analyze 1 Million Rows with DuckDB

Run big data SQL analytics on 1 million rows locally using DuckDB and Python.

Introduction

30 Second Summary

Every time you search for a product online, filter by price, or check your order history, a database is crunching through millions of rows behind the scenes. Most people assume you need expensive cloud clusters to handle that much data.

In this project, you will build a Python analytics pipeline that generates 1 million rows of sales data and queries it in seconds using DuckDB. All of this runs on your laptop with zero cloud costs.

What You'll Build

A local analytics pipeline that generates synthetic e-commerce data and runs complex SQL queries against 1 million rows using DuckDB's columnar engine.

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

  • A Python script that generates 1 million rows of realistic e-commerce sales data.
  • Analytical SQL queries that perform aggregations, rankings, and trend analysis using DuckDB.
  • A Parquet file export and performance comparisons showing why columnar databases outperform row-based approaches for analytics.
  • Secret Mission: Add time-series window functions to detect month-over-month revenue trends by product category.

Are there any prerequisites?

None. This project includes full setup instructions for every tool you need, including Python, VS Code, and DuckDB.

Set Up Your Python Environment

Before you can analyze a million rows of data, you need a working Python environment with the right packages installed.

In this step, you will install Python, VS Code, and the two libraries that power your analytics pipeline: DuckDB and Pandas. You will also create a project folder and verify everything works.

In this step, get ready to:
  • Install Python 3.10+ and VS Code.
  • Install DuckDB and Pandas via pip.
  • Create a project folder and verify your installation.
Install Python and VS Code

DuckDB requires Python 3.10 or newer. Let's check whether you already have the right version installed.

  • Open your terminal. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or Command Prompt (Windows) and press Enter to open it.
  • Check your Python version by running this command:

macOS

python3 --version

Windows

python --version

✔️ I see version 3.10 or higher

Python is ready to go. Skip ahead to the VS Code section below.

ⓧ I see an older version

Your system has an older Python installed, but DuckDB needs 3.10 or higher. You need to install a newer version.

On macOS, the system Python is often outdated. Installing from python.org gives you a separate, newer version that does not interfere with the system copy.

  • Go to https://www.python.org/downloads and download the latest Python 3 installer for your operating system.
  • Run the installer. On Windows, check the box that says Add Python to PATH before clicking Install Now.
  • Close and reopen your terminal, then re-run the version check command.
  • On macOS, use python3 --version to confirm the new version. The python3 command points to the newly installed Python, not the older system version.

ⓧ Command not found

Python is not installed yet. Let's install it now.

  • Go to https://www.python.org/downloads and download the latest Python 3 installer for your operating system.
  • Run the installer. On Windows, check the box that says Add Python to PATH before clicking Install Now.
  • Close and reopen your terminal after installation.
  • Run the version check command again. You should now see a version number of 3.10 or higher.

Next, you need VS Code as your code editor.

  • If you already have VS Code installed, skip to the next substep.
  • If you need to install it, go to https://code.visualstudio.com and download the installer for your operating system.
  • Run the installer and follow the default prompts to complete setup.
Install DuckDB and Pandas

DuckDB is the analytics engine that makes querying a million rows fast. Pandas provides the data structures you will use to generate your synthetic dataset.

  • Install both packages by running this command in your terminal:
pip install duckdb==1.5.2 pandas

What does this command do?

This installs two packages: duckdb at version 1.5.2 (the analytics engine) and pandas (the data manipulation library).

Pandas depends on NumPy, so NumPy gets installed automatically as well. Your data generation script will use NumPy to create randomized values across a million rows.

You should see pip downloading and installing multiple packages. The process finishes with a success message.

Seeing a permission error or command not found?

  • On macOS, try pip3 instead of pip. Some macOS setups map pip3 to the Python 3 package manager.
  • On Windows, make sure you checked Add Python to PATH during installation. If you missed it, reinstall Python with that box checked.
  • If you see a permissions error on macOS, try adding --user to the end of the install command.

Help me fix my pip install error

Create your project folder and verify the installation

Now you need a dedicated folder to hold your analytics scripts and the data files they generate.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir duckdb-analytics
cd duckdb-analytics
  • Confirm the folder exists by running:
ls

The folder is empty for now. That is expected. You will add your Python scripts here in the next steps.

  • Open VS Code in this folder. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type VS Code and press Enter to open it.
  • In VS Code, click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop and select the duckdb-analytics folder, then click Open.

Now let's verify that DuckDB is installed correctly by running a quick test.

  • In VS Code, open a new terminal by pressing Ctrl+` (backtick key, same on both macOS and Windows).
  • Create a test file by running:

macOS

touch test_install.py

Windows

New-Item test_install.py
  • Click on test_install.py in the VS Code Explorer sidebar to open it.
  • Paste the following code into the file:
import duckdb

# Verify DuckDB is installed and working
duckdb.sql("SELECT 'DuckDB is ready!' AS status").show()

What does this code do?

This imports the duckdb library and runs a simple SQL query. The .show() method prints the query results directly to your terminal.

If everything is installed correctly, you will see a table with the text DuckDB is ready! displayed.

  • Save the file with Cmd+S (macOS) or Ctrl+S (Windows).
  • Run the test script in your VS Code terminal:

macOS

python3 test_install.py

Windows

python test_install.py

You should see a small table printed in your terminal with DuckDB is ready! as the status value. This confirms DuckDB is installed and working.

Seeing ModuleNotFoundError?

  • Make sure you ran pip install duckdb==1.5.2 pandas in the same terminal session.
  • If you have multiple Python versions, the pip you used might point to a different Python than the one running your script. Try python3 -m pip install duckdb==1.5.2 pandas on macOS to ensure the packages install for the correct Python.

Help me fix my DuckDB import error

  • Now that the test passed, delete the test file. You will not need it again:

macOS

rm test_install.py

Windows

Remove-Item test_install.py

Your environment is ready. Next up, you will write a script that generates 1 million rows of realistic sales data.

Generate 1 Million Rows of Sales Data

Your environment is set up with Python, DuckDB, and Pandas ready to go. Now you need data to analyze.

Real companies work with massive datasets, but getting access to production data is difficult. In this step, you'll generate your own realistic 1-million-row e-commerce dataset using NumPy and Pandas.

In this step, get ready to:
  • Define product categories, price ranges, and regions for a realistic e-commerce schema.
  • Generate 1 million rows of randomized sales data spanning two years.
  • Export the dataset to a CSV file and verify the row count.
Create the script and define product data

Before generating any random data, you need to define what kind of data to generate. This means setting up the product categories, the products within each category, and realistic price ranges.

  • In VS Code, create a new file called generate_data.py in your project folder by clicking File then New File.
  • Add the following code to set up your imports, configuration, and product schema:
import numpy as np
import pandas as pd
import time

print("Generating 1,000,000 rows of sales data...")
start = time.time()

np.random.seed(42)
n_rows = 1_000_000

categories = ["Electronics", "Clothing", "Home & Garden", "Sports", "Books", "Food & Beverage", "Health & Beauty", "Toys"]
regions = ["North", "South", "East", "West", "Central"]
payment_methods = ["Credit Card", "Debit Card", "PayPal", "Bank Transfer", "Cash"]

products_by_category = {
    "Electronics": ["Laptop", "Headphones", "Smartphone", "Tablet", "Smartwatch"],
    "Clothing": ["T-Shirt", "Jeans", "Jacket", "Sneakers", "Dress"],
    "Home & Garden": ["Lamp", "Plant Pot", "Cushion", "Rug", "Candle"],
    "Sports": ["Yoga Mat", "Dumbbell", "Running Shoes", "Water Bottle", "Resistance Band"],
    "Books": ["Novel", "Textbook", "Cookbook", "Biography", "Comic"],
    "Food & Beverage": ["Coffee Beans", "Chocolate", "Pasta", "Olive Oil", "Tea"],
    "Health & Beauty": ["Moisturizer", "Shampoo", "Sunscreen", "Perfume", "Vitamins"],
    "Toys": ["Board Game", "Puzzle", "Action Figure", "Building Blocks", "Stuffed Animal"],
}

price_ranges = {
    "Electronics": (50, 1500),
    "Clothing": (15, 200),
    "Home & Garden": (10, 150),
    "Sports": (10, 100),
    "Books": (5, 60),
    "Food & Beverage": (3, 50),
    "Health & Beauty": (8, 80),
    "Toys": (5, 75),
}

What does this code do?

This section sets up the foundation for your data generator:

  • np.random.seed(42) ensures your random data is reproducible. Anyone running this script with seed 42 gets the same dataset.
  • n_rows = 1_000_000 sets the target to 1 million rows. Python lets you use underscores in numbers for readability.
  • products_by_category maps each category to 5 realistic product names so orders contain believable items.
  • price_ranges ensures prices are category-appropriate. Electronics range from $50-$1500 while Books range from $5-$60.

You won't see any output yet. The next section uses these definitions to generate the actual data.

Generate randomized order data

With the schema defined, you can now use NumPy to randomly assign categories, products, prices, and dates to each of the 1 million orders.

  • Add the following code below your price_ranges dictionary in generate_data.py:
chosen_categories = np.random.choice(categories, n_rows)
chosen_products = [np.random.choice(products_by_category[cat]) for cat in chosen_categories]
chosen_prices = [round(np.random.uniform(*price_ranges[cat]), 2) for cat in chosen_categories]

start_date = pd.Timestamp("2023-01-01")
end_date = pd.Timestamp("2024-12-31")
date_range_days = (end_date - start_date).days
random_days = np.random.randint(0, date_range_days, n_rows)
order_dates = [start_date + pd.Timedelta(days=int(d)) for d in random_days]

What does this code do?

This section creates the raw data arrays that will become your dataset columns:

  • np.random.choice(categories, n_rows) picks a random category for each of the 1 million rows.
  • The list comprehensions then look up the matching product and price range for each chosen category. This ensures a "Laptop" always falls under "Electronics" pricing.
  • Dates are generated by picking random numbers of days between January 1, 2023 and December 31, 2024, giving you two full years of order history.

You won't see any output yet. The next section assembles these arrays into a DataFrame and saves the file.

Assemble the DataFrame and export to CSV

The final section combines all your generated arrays into a single Pandas DataFrame, exports it to a CSV file, and prints a summary so you can verify everything worked.

  • Add the following code at the bottom of generate_data.py:
df = pd.DataFrame({
    "order_id": range(1, n_rows + 1),
    "customer_id": np.random.randint(1000, 50000, n_rows),
    "product_category": chosen_categories,
    "product_name": chosen_products,
    "quantity": np.random.randint(1, 10, n_rows),
    "unit_price": chosen_prices,
    "order_date": order_dates,
    "region": np.random.choice(regions, n_rows),
    "payment_method": np.random.choice(payment_methods, n_rows),
})

df.to_csv("sales_data.csv", index=False)
elapsed = time.time() - start

print(f"Done! Generated {len(df):,} rows in {elapsed:.2f} seconds")
print(f"File size: {round(pd.io.common.file_exists('sales_data.csv') and __import__('os').path.getsize('sales_data.csv') / (1024*1024), 2)} MB")
print(f"\nSample rows:")
print(df.head())

What does this code do?

This section brings everything together:

  • pd.DataFrame({...}) combines all your generated arrays into a structured table with named columns.
  • df.to_csv("sales_data.csv", index=False) writes the entire DataFrame to a CSV file. The index=False prevents Pandas from adding an extra row number column.
  • The print statements report the total rows, file size in MB, and a preview of the first 5 rows.
  • Save generate_data.py.
  • Run the script by executing this command in your terminal:
python generate_data.py
  • You should see output showing 1,000,000 rows generated, a file size around 55-65 MB, and a preview of the first 5 rows with all 9 columns.

Seeing an error when you run the script?

  • If you see ModuleNotFoundError: No module named 'numpy', NumPy may not be installed. Run pip install numpy and try again.
  • If you see a SyntaxError, double-check that all brackets and quotes are properly closed in the dictionary definitions.
  • Make sure you are running the command from your project folder where generate_data.py is saved.

help me debug my generate_data.py script

✔️ Awesome, I've got everything!

Great. Double check you've saved your file and that sales_data.csv now appears in your project folder.

ⓧ I'd like to double check the full code

import numpy as np
import pandas as pd
import time

print("Generating 1,000,000 rows of sales data...")
start = time.time()

np.random.seed(42)
n_rows = 1_000_000

categories = ["Electronics", "Clothing", "Home & Garden", "Sports", "Books", "Food & Beverage", "Health & Beauty", "Toys"]
regions = ["North", "South", "East", "West", "Central"]
payment_methods = ["Credit Card", "Debit Card", "PayPal", "Bank Transfer", "Cash"]

products_by_category = {
    "Electronics": ["Laptop", "Headphones", "Smartphone", "Tablet", "Smartwatch"],
    "Clothing": ["T-Shirt", "Jeans", "Jacket", "Sneakers", "Dress"],
    "Home & Garden": ["Lamp", "Plant Pot", "Cushion", "Rug", "Candle"],
    "Sports": ["Yoga Mat", "Dumbbell", "Running Shoes", "Water Bottle", "Resistance Band"],
    "Books": ["Novel", "Textbook", "Cookbook", "Biography", "Comic"],
    "Food & Beverage": ["Coffee Beans", "Chocolate", "Pasta", "Olive Oil", "Tea"],
    "Health & Beauty": ["Moisturizer", "Shampoo", "Sunscreen", "Perfume", "Vitamins"],
    "Toys": ["Board Game", "Puzzle", "Action Figure", "Building Blocks", "Stuffed Animal"],
}

price_ranges = {
    "Electronics": (50, 1500),
    "Clothing": (15, 200),
    "Home & Garden": (10, 150),
    "Sports": (10, 100),
    "Books": (5, 60),
    "Food & Beverage": (3, 50),
    "Health & Beauty": (8, 80),
    "Toys": (5, 75),
}

chosen_categories = np.random.choice(categories, n_rows)
chosen_products = [np.random.choice(products_by_category[cat]) for cat in chosen_categories]
chosen_prices = [round(np.random.uniform(*price_ranges[cat]), 2) for cat in chosen_categories]

start_date = pd.Timestamp("2023-01-01")
end_date = pd.Timestamp("2024-12-31")
date_range_days = (end_date - start_date).days
random_days = np.random.randint(0, date_range_days, n_rows)
order_dates = [start_date + pd.Timedelta(days=int(d)) for d in random_days]

df = pd.DataFrame({
    "order_id": range(1, n_rows + 1),
    "customer_id": np.random.randint(1000, 50000, n_rows),
    "product_category": chosen_categories,
    "product_name": chosen_products,
    "quantity": np.random.randint(1, 10, n_rows),
    "unit_price": chosen_prices,
    "order_date": order_dates,
    "region": np.random.choice(regions, n_rows),
    "payment_method": np.random.choice(payment_methods, n_rows),
})

df.to_csv("sales_data.csv", index=False)
elapsed = time.time() - start

print(f"Done! Generated {len(df):,} rows in {elapsed:.2f} seconds")
print(f"File size: {round(pd.io.common.file_exists('sales_data.csv') and __import__('os').path.getsize('sales_data.csv') / (1024*1024), 2)} MB")
print(f"\nSample rows:")
print(df.head())

You now have a million-row dataset sitting in your project folder. Next up, you'll query this data with DuckDB SQL and see results return in seconds.

Query Your Data with DuckDB SQL

Your dataset is ready. One million rows of sales data sitting in a CSV file on your machine.

Now comes the exciting part. You will query that data using DuckDB's SQL engine and watch results come back in fractions of a second. You will start with a basic exploration, then build up to aggregations and window-function rankings.

In this step, get ready to:
  • Load and explore 1 million rows directly from CSV using DuckDB.
  • Write an aggregation query grouping revenue by category and region.
  • Write a ranking query using window functions to find top categories per region.
Load and explore your sales data

Before running complex analytics, you need to understand what is in your dataset. DuckDB can read a CSV file directly inside a SQL query without importing it into a database first.

  • Create a new file called analyze_data.py in your project folder.
  • Add the following code to set up imports and run a dataset overview query:
import duckdb
import pandas as pd
import time

print("=" * 60)
print("BIG DATA ANALYTICS WITH DUCKDB")
print("=" * 60)

# --- Basic Exploration ---
print("\n--- Dataset Overview ---")
result = duckdb.sql("""
    SELECT
        COUNT(*) AS total_rows,
        COUNT(DISTINCT customer_id) AS unique_customers,
        COUNT(DISTINCT product_category) AS categories,
        MIN(order_date) AS earliest_order,
        MAX(order_date) AS latest_order
    FROM read_csv('sales_data.csv')
""")
result.show()

What does this code do?

The read_csv('sales_data.csv') function tells DuckDB to read your CSV file directly inside the SQL query. No need to load data into a separate database first.

The query counts total rows, unique customers, and categories while also finding the date range of your orders. The .show() method prints the results as a formatted table in your terminal.

  • Save analyze_data.py.
  • Run the script by executing this command:
python analyze_data.py
  • You should see a table showing 1,000,000 total rows, around 49,000 unique customers, 8 categories, and a date range spanning 2023 to 2024.

Seeing a file not found error?

Make sure sales_data.csv exists in the same folder as analyze_data.py. DuckDB looks for the file relative to where you run the script.

If you skipped generating the data, go back and run python generate_data.py first.

help me fix a file not found error when running analyze_data.py

Write an aggregation query for revenue analysis

Aggregation queries group rows together and calculate summary statistics. This is the bread and butter of analytics work. You will calculate total revenue, average order value, and order count for every combination of product category and region.

  • In analyze_data.py, add this code below the exploration query:
# --- Aggregation Query ---
print("\n--- Revenue by Category and Region ---")
agg_query = """
    SELECT
        product_category,
        region,
        COUNT(*) AS total_orders,
        ROUND(SUM(quantity * unit_price), 2) AS total_revenue,
        ROUND(AVG(quantity * unit_price), 2) AS avg_order_value
    FROM read_csv('sales_data.csv')
    GROUP BY product_category, region
    ORDER BY total_revenue DESC
    LIMIT 15
"""
duckdb.sql(agg_query).show()

What does this query do?

The GROUP BY product_category, region clause groups all 1 million rows into buckets for each category-region pair (8 categories x 5 regions = 40 groups).

For each group, SUM(quantity * unit_price) calculates total revenue and AVG(quantity * unit_price) calculates average order value. The ORDER BY total_revenue DESC LIMIT 15 returns only the top 15 highest-revenue combinations.

  • Save analyze_data.py and run it again:
python analyze_data.py
  • You should see a new table with 15 rows showing the highest-revenue category and region combinations. Electronics categories will dominate due to their higher price range.

Seeing a syntax error in the query?

Check that the triple-quoted string """ opens and closes correctly around the SQL query. A missing closing """ will cause Python to throw a syntax error.

Also confirm the query variable is called agg_query and that duckdb.sql(agg_query).show() appears on the line after the closing quotes.

help me fix a syntax error in my DuckDB aggregation query

Write a ranking query with window functions

Window functions let you calculate rankings across groups without collapsing rows. You will use RANK() OVER to find the top 5 highest-revenue product categories within each region.

What is a window function?

A window function performs a calculation across a set of rows that are related to the current row. Unlike GROUP BY, which collapses rows into one summary row per group, window functions keep all the rows and add a computed column alongside them.

The PARTITION BY region clause creates separate ranking "windows" for each region. Within each window, RANK() assigns a rank based on revenue.

  • In analyze_data.py, add this ranking query below the aggregation query:
# --- Ranking Query ---
print("\n--- Top 5 Categories by Revenue per Region ---")
ranking_query = """
    WITH category_revenue AS (
        SELECT
            region,
            product_category,
            ROUND(SUM(quantity * unit_price), 2) AS total_revenue
        FROM read_csv('sales_data.csv')
        GROUP BY region, product_category
    )
    SELECT
        region,
        product_category,
        total_revenue,
        RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) AS revenue_rank
    FROM category_revenue
    QUALIFY revenue_rank <= 5
    ORDER BY region, revenue_rank
"""
duckdb.sql(ranking_query).show()

What does this query do?

The WITH category_revenue AS (...) block is a Common Table Expression (CTE). It first calculates total revenue per category per region, then the main query ranks those results.

The QUALIFY revenue_rank <= 5 clause is a DuckDB feature that filters rows after the window function runs. It keeps only the top 5 ranked categories in each region.

  • Save analyze_data.py and run it one more time:
python analyze_data.py
  • You should see a table with 25 rows (5 categories x 5 regions), each showing its revenue rank within its region. All three query sections should print results one after another.

Seeing an error about QUALIFY?

The QUALIFY keyword is specific to DuckDB. If you see an error, make sure the query string is exactly as shown above. A typo or missing letter will cause DuckDB to reject the query.

Also check that the WITH block and main SELECT are both inside the same triple-quoted string.

help me fix a QUALIFY or window function error in DuckDB

✔️ Awesome, I've got everything!

Great. Double check you have saved analyze_data.py and that all three query results print successfully.

ⓧ I'd like to double check the full code

Here is the complete analyze_data.py file up to this point:

import duckdb
import pandas as pd
import time

print("=" * 60)
print("BIG DATA ANALYTICS WITH DUCKDB")
print("=" * 60)

# --- Basic Exploration ---
print("\n--- Dataset Overview ---")
result = duckdb.sql("""
    SELECT
        COUNT(*) AS total_rows,
        COUNT(DISTINCT customer_id) AS unique_customers,
        COUNT(DISTINCT product_category) AS categories,
        MIN(order_date) AS earliest_order,
        MAX(order_date) AS latest_order
    FROM read_csv('sales_data.csv')
""")
result.show()

# --- Aggregation Query ---
print("\n--- Revenue by Category and Region ---")
agg_query = """
    SELECT
        product_category,
        region,
        COUNT(*) AS total_orders,
        ROUND(SUM(quantity * unit_price), 2) AS total_revenue,
        ROUND(AVG(quantity * unit_price), 2) AS avg_order_value
    FROM read_csv('sales_data.csv')
    GROUP BY product_category, region
    ORDER BY total_revenue DESC
    LIMIT 15
"""
duckdb.sql(agg_query).show()

# --- Ranking Query ---
print("\n--- Top 5 Categories by Revenue per Region ---")
ranking_query = """
    WITH category_revenue AS (
        SELECT
            region,
            product_category,
            ROUND(SUM(quantity * unit_price), 2) AS total_revenue
        FROM read_csv('sales_data.csv')
        GROUP BY region, product_category
    )
    SELECT
        region,
        product_category,
        total_revenue,
        RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) AS revenue_rank
    FROM category_revenue
    QUALIFY revenue_rank <= 5
    ORDER BY region, revenue_rank
"""
duckdb.sql(ranking_query).show()

You just queried 1 million rows three different ways and got results in seconds. Next up, you will put DuckDB head-to-head against Pandas to see exactly how much faster columnar processing is.

Compare Performance Against Pandas

In the previous step, you wrote powerful SQL queries that aggregated, ranked, and filtered 1 million rows. But how fast is DuckDB compared to the tool most Python developers already use for data work?

In this step, you will run the exact same aggregation query in both Pandas and DuckDB, measuring execution time for each. The results will make the performance difference concrete and measurable.

In this step, get ready to:
  • Benchmark the same aggregation using Pandas groupby().
  • Benchmark the same aggregation using DuckDB SQL.
  • Print a side-by-side speed comparison showing the difference.
Implement the Pandas aggregation with timing

For a fair comparison, you need to run the same aggregation logic in both tools. Pandas uses groupby() and agg() to achieve what SQL does with GROUP BY.

  • In analyze_data.py, find the last line of the ranking query section:
duckdb.sql(ranking_query).show()
  • Add the following code below that line to load the CSV into Pandas and run a timed aggregation:
# --- Performance Comparison ---
print("\n--- Performance Comparison: DuckDB vs Pandas ---")

# Load data into Pandas for fair comparison
df = pd.read_csv("sales_data.csv")

# Pandas version
start = time.time()
pandas_result = (
    df.assign(revenue=df["quantity"] * df["unit_price"])
    .groupby(["product_category", "region"])
    .agg(
        total_orders=("order_id", "count"),
        total_revenue=("revenue", "sum"),
        avg_order_value=("revenue", "mean"),
    )
    .reset_index()
    .sort_values("total_revenue", ascending=False)
    .head(15)
)
pandas_time = time.time() - start

What does this code do?

This code replicates the same aggregation you wrote in SQL, but using Pandas method chains instead.

  • pd.read_csv("sales_data.csv") loads all 1 million rows into memory as a DataFrame.
  • df.assign(revenue=...) creates a new column by multiplying quantity by unit_price.
  • .groupby(["product_category", "region"]) groups rows the same way GROUP BY does in SQL.
  • time.time() captures timestamps before and after so you can measure how long the operation takes.

You won't see output yet. The next chunk adds the DuckDB benchmark and prints the comparison.

Add the DuckDB benchmark and run the comparison

Now you will time DuckDB running the exact same aggregation query. Because agg_query is already defined earlier in the file, you can reuse it directly.

  • Add the following code directly below the pandas_time = time.time() - start line you just wrote:
# DuckDB version
start = time.time()
duckdb_result = duckdb.sql(agg_query).df()
duckdb_time = time.time() - start

print(f"Pandas execution time:  {pandas_time:.4f} seconds")
print(f"DuckDB execution time:  {duckdb_time:.4f} seconds")
print(f"DuckDB is {pandas_time / duckdb_time:.1f}x faster!")

What does this code do?

  • duckdb.sql(agg_query).df() runs the same SQL aggregation and converts the result to a Pandas DataFrame using .df().
  • The final three print() lines display both execution times and calculate how many times faster DuckDB is.

Why is DuckDB faster than Pandas here?

Pandas processes data row by row in Python. DuckDB uses columnar storage and vectorized execution, meaning it reads only the columns it needs and processes them in fast batches using low-level CPU instructions.

For analytical queries on large datasets, this architectural difference makes DuckDB significantly faster without any configuration or tuning.

  • Save analyze_data.py.
  • Run the script by executing this command in your terminal:
python analyze_data.py
  • Scroll to the Performance Comparison section of the output. You should see both execution times printed, followed by a line showing DuckDB is roughly 3-5x faster.

Not seeing a speed difference?

  • Make sure you saved analyze_data.py before running. Unsaved changes mean the old version runs.
  • If you see a NameError for agg_query, check that the new code is placed after the aggregation query section, not before it.
  • If both times are nearly identical, your machine may be under heavy load. Close other applications and try again.

Help me debug my performance comparison

✔️ Awesome, I've got everything!

Great. Double check you have saved your file before moving on.

ⓧ I'd like to double check the full code

Here is the full analyze_data.py file up to this point:

import duckdb
import pandas as pd
import time

print("=" * 60)
print("BIG DATA ANALYTICS WITH DUCKDB")
print("=" * 60)

# --- Basic Exploration ---
print("\n--- Dataset Overview ---")
result = duckdb.sql("""
    SELECT
        COUNT(*) AS total_rows,
        COUNT(DISTINCT customer_id) AS unique_customers,
        COUNT(DISTINCT product_category) AS categories,
        MIN(order_date) AS earliest_order,
        MAX(order_date) AS latest_order
    FROM read_csv('sales_data.csv')
""")
result.show()

# --- Aggregation Query ---
print("\n--- Revenue by Category and Region ---")
agg_query = """
    SELECT
        product_category,
        region,
        COUNT(*) AS total_orders,
        ROUND(SUM(quantity * unit_price), 2) AS total_revenue,
        ROUND(AVG(quantity * unit_price), 2) AS avg_order_value
    FROM read_csv('sales_data.csv')
    GROUP BY product_category, region
    ORDER BY total_revenue DESC
    LIMIT 15
"""
duckdb.sql(agg_query).show()

# --- Ranking Query ---
print("\n--- Top 5 Categories by Revenue per Region ---")
ranking_query = """
    WITH category_revenue AS (
        SELECT
            region,
            product_category,
            ROUND(SUM(quantity * unit_price), 2) AS total_revenue
        FROM read_csv('sales_data.csv')
        GROUP BY region, product_category
    )
    SELECT
        region,
        product_category,
        total_revenue,
        RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) AS revenue_rank
    FROM category_revenue
    QUALIFY revenue_rank <= 5
    ORDER BY region, revenue_rank
"""
duckdb.sql(ranking_query).show()

# --- Performance Comparison ---
print("\n--- Performance Comparison: DuckDB vs Pandas ---")

# Load data into Pandas for fair comparison
df = pd.read_csv("sales_data.csv")

# Pandas version
start = time.time()
pandas_result = (
    df.assign(revenue=df["quantity"] * df["unit_price"])
    .groupby(["product_category", "region"])
    .agg(
        total_orders=("order_id", "count"),
        total_revenue=("revenue", "sum"),
        avg_order_value=("revenue", "mean"),
    )
    .reset_index()
    .sort_values("total_revenue", ascending=False)
    .head(15)
)
pandas_time = time.time() - start

# DuckDB version
start = time.time()
duckdb_result = duckdb.sql(agg_query).df()
duckdb_time = time.time() - start

print(f"Pandas execution time:  {pandas_time:.4f} seconds")
print(f"DuckDB execution time:  {duckdb_time:.4f} seconds")
print(f"DuckDB is {pandas_time / duckdb_time:.1f}x faster!")

You just proved that DuckDB's columnar engine outperforms Pandas on the same analytical query. Next up, you will export your data to Parquet format and see another major speed improvement.

Export to Parquet and Query Directly

You just proved that DuckDB crushes Pandas on analytical queries. Now it is time to level up your data storage.

CSV files are human-readable, but they waste space and force the database to parse every row from scratch. Parquet is a columnar file format designed for analytics. It compresses data, stores column metadata, and lets query engines skip irrelevant sections entirely.

In this step, get ready to:
  • Export your 1-million-row dataset to Parquet format and measure the file size savings.
  • Run a monthly revenue trends query directly on the Parquet file.
  • Complete your analytics script with a final complex query.
Export your data to Parquet format

Your CSV file works, but it stores data as plain text rows. Every time DuckDB reads it, it has to parse all that text. Parquet stores data in columns with built-in compression, making both storage and reads dramatically more efficient.

  • In analyze_data.py, find the last line that prints the speed comparison:
  • Copy the following code below that line:
# --- Export to Parquet ---
print("\n--- Exporting to Parquet ---")
start = time.time()
duckdb.sql("""
    COPY (SELECT * FROM read_csv('sales_data.csv'))
    TO 'sales_data.parquet' (FORMAT PARQUET)
""")
parquet_time = time.time() - start
print(f"Exported to Parquet in {parquet_time:.4f} seconds")

import os
csv_size = os.path.getsize("sales_data.csv") / (1024 * 1024)
parquet_size = os.path.getsize("sales_data.parquet") / (1024 * 1024)
print(f"CSV size:     {csv_size:.2f} MB")
print(f"Parquet size: {parquet_size:.2f} MB")
print(f"Compression:  {(1 - parquet_size/csv_size) * 100:.1f}% smaller!")

What does this code do?

The COPY statement reads everything from your CSV and writes it out as a Parquet file. DuckDB handles the conversion and compression internally.

The os.path.getsize() calls measure both files in megabytes so you can see the storage savings. Parquet's columnar compression typically shrinks data significantly compared to raw CSV.

  • Save analyze_data.py.
  • Run the script to confirm the export works by running:
python analyze_data.py
  • Scroll to the bottom of the output. You should see the Parquet file size is noticeably smaller than the CSV, with a compression percentage printed.

Why is Parquet so much smaller?

CSV stores every value as text characters. The number 1500.00 takes 7 bytes in CSV but only 4-8 bytes in Parquet's native binary encoding.

Parquet also groups identical column values together, which makes compression algorithms far more effective. Repeated strings like region names compress down to almost nothing.

Seeing a file not found error?

Make sure sales_data.csv exists in the same folder where you are running the script. If it is missing, run python generate_data.py again first.

help me debug a file not found error when exporting to Parquet

Query the Parquet file with a trend analysis

Now that your data lives in Parquet, you can query it directly without converting it back to CSV. DuckDB reads Parquet natively and benefits from its columnar layout to scan only the columns each query needs.

  • In analyze_data.py, add the following code at the bottom of the file (below the compression print statement):
# --- Query Parquet Directly ---
print("\n--- Monthly Revenue Trends (from Parquet) ---")
trend_query = """
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        EXTRACT(YEAR FROM order_date) AS year,
        ROUND(SUM(quantity * unit_price), 2) AS monthly_revenue,
        COUNT(*) AS order_count
    FROM read_parquet('sales_data.parquet')
    GROUP BY month, year
    ORDER BY month
    LIMIT 12
"""
duckdb.sql(trend_query).show()

print("\n" + "=" * 60)
print("ANALYSIS COMPLETE!")
print("=" * 60)

What does this query do?

DATE_TRUNC('month', order_date) rounds each order date down to the first of its month. This groups all orders within the same month together for aggregation.

EXTRACT(YEAR FROM order_date) pulls the year as a separate column, making it easy to compare the same months across different years. The query sums revenue and counts orders for each month, limited to the first 12 results.

Notice this query reads from read_parquet('sales_data.parquet') instead of read_csv. DuckDB queries both formats with the same SQL syntax.

  • Save analyze_data.py and run the full script:
python analyze_data.py
  • At the bottom of the output, you should see a table of monthly revenue figures followed by the ANALYSIS COMPLETE! banner.

Query returns no results?

Check that the Parquet export ran successfully before this query. If sales_data.parquet does not exist, the read_parquet call will fail. Re-run the script from the top so the export happens first.

help me debug why my Parquet query returns no data

✔️ Awesome, I've got everything!

Great. Double check you have saved analyze_data.py and that the full script runs without errors.

ⓧ I'd like to double check the full code

Here is the complete analyze_data.py file at this point.

import duckdb
import pandas as pd
import time

print("=" * 60)
print("BIG DATA ANALYTICS WITH DUCKDB")
print("=" * 60)

# --- Basic Exploration ---
print("\n--- Dataset Overview ---")
result = duckdb.sql("""
    SELECT
        COUNT(*) AS total_rows,
        COUNT(DISTINCT customer_id) AS unique_customers,
        COUNT(DISTINCT product_category) AS categories,
        MIN(order_date) AS earliest_order,
        MAX(order_date) AS latest_order
    FROM read_csv('sales_data.csv')
""")
result.show()

# --- Aggregation Query ---
print("\n--- Revenue by Category and Region ---")
agg_query = """
    SELECT
        product_category,
        region,
        COUNT(*) AS total_orders,
        ROUND(SUM(quantity * unit_price), 2) AS total_revenue,
        ROUND(AVG(quantity * unit_price), 2) AS avg_order_value
    FROM read_csv('sales_data.csv')
    GROUP BY product_category, region
    ORDER BY total_revenue DESC
    LIMIT 15
"""
duckdb.sql(agg_query).show()

# --- Ranking Query ---
print("\n--- Top 5 Categories by Revenue per Region ---")
ranking_query = """
    WITH category_revenue AS (
        SELECT
            region,
            product_category,
            ROUND(SUM(quantity * unit_price), 2) AS total_revenue
        FROM read_csv('sales_data.csv')
        GROUP BY region, product_category
    )
    SELECT
        region,
        product_category,
        total_revenue,
        RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) AS revenue_rank
    FROM category_revenue
    QUALIFY revenue_rank <= 5
    ORDER BY region, revenue_rank
"""
duckdb.sql(ranking_query).show()

# --- Performance Comparison ---
print("\n--- Performance Comparison: DuckDB vs Pandas ---")

# Load data into Pandas for fair comparison
df = pd.read_csv("sales_data.csv")

# Pandas version
start = time.time()
pandas_result = (
    df.assign(revenue=df["quantity"] * df["unit_price"])
    .groupby(["product_category", "region"])
    .agg(
        total_orders=("order_id", "count"),
        total_revenue=("revenue", "sum"),
        avg_order_value=("revenue", "mean"),
    )
    .reset_index()
    .sort_values("total_revenue", ascending=False)
    .head(15)
)
pandas_time = time.time() - start

# DuckDB version
start = time.time()
duckdb_result = duckdb.sql(agg_query).df()
duckdb_time = time.time() - start

print(f"Pandas execution time:  {pandas_time:.4f} seconds")
print(f"DuckDB execution time:  {duckdb_time:.4f} seconds")
print(f"DuckDB is {pandas_time / duckdb_time:.1f}x faster!")

# --- Export to Parquet ---
print("\n--- Exporting to Parquet ---")
start = time.time()
duckdb.sql("""
    COPY (SELECT * FROM read_csv('sales_data.csv'))
    TO 'sales_data.parquet' (FORMAT PARQUET)
""")
parquet_time = time.time() - start
print(f"Exported to Parquet in {parquet_time:.4f} seconds")

import os
csv_size = os.path.getsize("sales_data.csv") / (1024 * 1024)
parquet_size = os.path.getsize("sales_data.parquet") / (1024 * 1024)
print(f"CSV size:     {csv_size:.2f} MB")
print(f"Parquet size: {parquet_size:.2f} MB")
print(f"Compression:  {(1 - parquet_size/csv_size) * 100:.1f}% smaller!")

# --- Query Parquet Directly ---
print("\n--- Monthly Revenue Trends (from Parquet) ---")
trend_query = """
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        EXTRACT(YEAR FROM order_date) AS year,
        ROUND(SUM(quantity * unit_price), 2) AS monthly_revenue,
        COUNT(*) AS order_count
    FROM read_parquet('sales_data.parquet')
    GROUP BY month, year
    ORDER BY month
    LIMIT 12
"""
duckdb.sql(trend_query).show()

print("\n" + "=" * 60)
print("ANALYSIS COMPLETE!")
print("=" * 60)

Your analytics pipeline is complete. You have generated, queried, benchmarked, and exported 1 million rows entirely on your laptop. Next up, the Secret Mission challenges you to add time-series trend detection with window functions.

Secret mission

Time-Series Window Functions

Your analytics pipeline already handles aggregations and rankings. But real data teams need to spot trends over time. Can you add month-over-month revenue tracking that automatically flags which product categories are growing, declining, or staying flat?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs or cloud resources to worry about.

Decide whether to keep your project files and packages installed, or clean them up to free disk space.

Resources you used:

  • Project files (generate_data.py, analyze_data.py).
  • Generated data files (sales_data.csv, sales_data.parquet).
  • Python packages (duckdb, pandas, numpy).

Keep everything running

No action needed. Choose this if you plan to keep experimenting with your analytics pipeline or want to reference the code later.

  • Your project files and generated data remain on your Desktop.
  • The duckdb, pandas, and numpy packages stay installed and available for other Python projects.
  • The sales_data.csv and sales_data.parquet files take up disk space (around 70-90 MB combined) but have no ongoing cost.

Pause - I'll come back to this later

Delete the large generated data files to free up disk space. Keep your scripts so you can regenerate the data anytime.

  • Delete the generated data files by running:

macOS

cd ~/Desktop
rm sales_data.csv sales_data.parquet

Windows

cd ~/Desktop
Remove-Item sales_data.csv, sales_data.parquet

Your Python scripts and installed packages remain in place. Run generate_data.py again whenever you want to recreate the dataset.

Delete - I don't want to use this again

Remove all project files and uninstall the Python packages to fully clean up.

  • Delete your project folder and all generated files by running:

macOS

cd ~/Desktop
rm generate_data.py analyze_data.py sales_data.csv sales_data.parquet

Windows

cd ~/Desktop
Remove-Item generate_data.py, analyze_data.py, sales_data.csv, sales_data.parquet
  • Uninstall the Python packages by running:
pip uninstall duckdb pandas numpy -y

This removes the packages from your Python environment. You can always reinstall them later with pip install.

That's a Wrap!

That's a Wrap!

You just analyzed 1 million rows of data on your laptop in seconds. No cloud clusters, no expensive infrastructure, no complex configuration.

You've learned how to:

  • Generated a realistic 1-million-row e-commerce dataset with Python and queried it in seconds using DuckDB.
  • Written analytical SQL queries with aggregations, rankings, and time-series analysis against a million rows.
  • Compared columnar database performance against Pandas and exported data to Parquet, the industry-standard file format for big data workloads.
  • Built a time-series trend detection pipeline using window functions to identify month-over-month revenue changes by product category.

Ready to quiz yourself?