Installation

Install DuckDB with pip:

pip install duckdb

Let’s verify the installation:

Zero Configuration

SQL operations on DataFrames typically require setting up database servers. With pandas and PostgreSQL, you need to:

• Install and configure a database server
• Ensure the service is running
• Set up credentials and connections
• Write the DataFrame to a table first

DuckDB eliminates this overhead. Query DataFrames directly with SQL:

Try it

Edit the query to select items with quantity greater than 30 from the inventory DataFrame:

Quiz

Integrate Seamlessly with pandas and Polars

Have you ever wanted to leverage SQL’s power while working with your favorite data manipulation libraries such as pandas and Polars?

DuckDB makes it seamless to query pandas and Polars DataFrames via the duckdb.sql function.

DuckDB’s integration with pandas and Polars lets you combine the strengths of each tool. For example, you can:

• Use pandas for data cleaning and feature engineering
• Use DuckDB for complex aggregations and complex queries

Now use DuckDB for complex aggregations:

Try it

Edit the query to combine results from both df_2023 and df_2024 using UNION ALL:

Quiz

Memory Efficiency

Pandas loads entire datasets into RAM before filtering, which can cause out-of-memory errors. DuckDB processes only the rows that match your filter, using a fraction of the memory. To see this in action, let’s compare both approaches on the same dataset.

First, create a sample CSV file:

With pandas, filtering loads ALL records into RAM first:

With DuckDB, only matching rows enter memory:

The diagram below summarizes the memory difference:

RAM Usage
│
│ ████████████  Pandas (loads all 1M rows)
│
│ ██            DuckDB (streams, keeps 10K matches)
│
└──────────────────────────────────────────────

Quiz

Out-of-Core Processing

When your dataset exceeds available RAM, pandas requires workarounds like chunking or Dask. DuckDB solves this with out-of-core processing:

• Breaks data into chunks that fit in memory
• Processes each chunk separately
• Stores intermediate results on disk
• Merges results into the final output

Dataset (50GB)         Memory (16GB)           Disk (temp files)
┌──────────┐           ┌──────────┐            ┌──────────────┐
│ Chunk 1  │ ────────> │ Process  │ ─────────> │ result_1.tmp │
│ Chunk 2  │           │ in RAM   │            │ result_2.tmp │
│ Chunk 3  │           └──────────┘            │ result_3.tmp │
│   ...    │                                   └──────┬───────┘
└──────────┘                                          │ merge
                                                      ▼
                                               ┌─────────────┐
                                               │ Final Result│
                                               └─────────────┘

The following example demonstrates this process. We’ll limit DuckDB to 10MB of memory and sort 5 million rows:

You can also configure where DuckDB stores temporary files:

Quiz

Fast Performance

While pandas processes data sequentially row-by-row, DuckDB uses a columnar-vectorized execution engine that processes data in parallel chunks. The diagram below shows how each approach handles data:

Pandas                         DuckDB
  │                              │
  ├─ Row 1 ──────> process       ├─ Chunk 1 (2048 rows) ─┐
  ├─ Row 2 ──────> process       ├─ Chunk 2 (2048 rows) ─┼─> process
  ├─ Row 3 ──────> process       ├─ Chunk 3 (2048 rows) ─┘
  ├─ Row 4 ──────> process       │
  │   ...                        │
  ▼                              ▼
  Sequential                   Parallel chunks

This architectural difference enables DuckDB to significantly outperform pandas, especially for computationally intensive operations like aggregations and joins.

Let’s compare the performance of pandas and DuckDB for aggregations on a million rows of data.

Quiz

FROM-First Syntax

Traditional SQL requires SELECT before FROM. This adds unnecessary boilerplate when you just want a quick look at your data:

DuckDB lets you skip SELECT * entirely, making quick data exploration faster:

Try it

Write a FROM-first query to get all sales with amount > 150:

Quiz

GROUP BY ALL

When using GROUP BY, you typically repeat every non-aggregated column:

DuckDB infers grouping columns automatically with GROUP BY ALL:

Try it

Rewrite this query using GROUP BY ALL instead of listing columns:

Quiz

SELECT * EXCLUDE

When you need all columns except a few, traditional SQL requires listing every column you want:

DuckDB’s EXCLUDE lets you specify what to remove instead:

You can also exclude multiple columns at once with a comma-separated list:

Try it

Write a query to get all columns except email from the users table:

Quiz

SELECT * REPLACE

When you need to transform one column while keeping others, traditional SQL requires listing every column:

DuckDB’s REPLACE lets you transform just the column you need:

Try it

Write a query to convert name to uppercase using REPLACE:

Quiz

Streamlined File Reading

DuckDB can query files directly without loading them into memory first. It supports CSV, Parquet, and JSON formats, automatically detecting structure, delimiters, and column types.

Quiz

Query Cloud Storage

DuckDB can query files directly from cloud storage providers like AWS S3, Google Cloud Storage, and Azure Blob Storage without downloading them first.

Query from S3:

Reading files from S3 works just like reading local files. Simply pass an S3 path to read_parquet.

For private buckets, add your credentials once and DuckDB handles authentication automatically.

The same approach works with other cloud providers.

Quiz

Automatic Parsing of CSV Files

When working with CSV files that have non-standard delimiters, pandas requires you to specify parameters like delimiter to avoid parsing errors.

With pandas, a pipe-delimited CSV is parsed incorrectly:

With DuckDB, the delimiter is auto-detected:

Try it

Change the delimiter from | to ; and run the query. Does DuckDB still parse it correctly?

Quiz

Automatic Flattening of Nested Parquet Files

When working with large, nested Parquet files, you typically need to pre-process the data to flatten nested structures or write complex extraction scripts, which adds time and complexity to your workflow.

With pandas, you need to manually flatten nested structures:

With DuckDB, query nested fields directly using dot notation:

Try it

Fill in the dot notation to extract color and weight from the specs struct:

Quiz

Automatic Flattening of Nested JSON Files

When working with JSON files that have nested structures, you need to normalize the data with pandas to access nested fields.

With pandas, you need json_normalize() to flatten nested structures:

With DuckDB, you can query each nested field directly with the syntax field_name.nested_field_name:

Quiz

Reading Multiple Files

Reading Multiple Files from a Directory

Reading multiple files from a directory is common in data pipelines.

First, let’s create some sample CSV files to work with:

With pandas, you need to read each file separately then concatenate:

With DuckDB, use glob patterns to read all files at once:

Quiz

Read From Multiple Sources

DuckDB allows you to read data from multiple sources in a single query, making it easier to combine data from different sources.

You can mix DataFrames, CSV files, Parquet files, and JSON files seamlessly.

First, let’s create two related DataFrames:

Now use DuckDB to join and aggregate these DataFrames with a single SQL query:

Quiz

Hive Partitioned Datasets

When working with large datasets, it’s common to partition data into separate files by date, region, or other columns. A typical approach is organizing files into folders:

data/
├── 2023/
│   ├── 01/data.parquet
│   └── 02/data.parquet
└── 2024/
    └── 01/data.parquet

With this structure, you lose the partition information when reading. You’d need to extract year and month from file paths manually.

Hive-style partitioning solves this by encoding column values in directory names:

data/
├── year=2023/
│   ├── month=01/data.parquet
│   └── month=02/data.parquet
└── year=2024/
    └── month=01/data.parquet

DuckDB can both write and read Hive-partitioned datasets automatically.

Writing partitioned data:

Use PARTITION_BY to create a Hive-partitioned dataset:

Verify the directory structure:

Quiz

Reading partitioned data:

Now read the data back. DuckDB auto-detects the column=value folder pattern:

Quiz

Exporting Data

DuckDB can export query results directly to CSV, Parquet, and JSON files without converting to pandas first. This avoids memory overhead for large datasets.

Export to CSV:

Use .write_csv() to save query results directly to a CSV file:

Export to Parquet:

Use .write_parquet() for columnar storage with optional compression:

Using COPY statement:

The COPY statement provides more control over export options:

• DELIMITER '|' – Custom delimiters (comma, pipe, tab)
• HEADER true – Include or exclude header row
• COMPRESSION 'gzip' – Compress output (gzip, zstd, snappy)
• DATEFORMAT '%Y-%m-%d' – Custom date formatting
• NULL 'NA' – Custom null value representation

Here’s an example using a custom delimiter and header option together.

Quiz

Creating Lists, Structs, and Maps

DuckDB supports rich nested data types that go beyond traditional SQL databases. You can create and manipulate LISTs, STRUCTs, and MAPs directly in SQL.

LIST – ordered collection of values:

Unlike traditional SQL arrays, DuckDB lists use familiar Python-style square brackets.

Quiz

STRUCT – named fields (like a dictionary):

Structs require the same field names. DuckDB throws an error if fields don’t match.

MAP – key-value pairs:

Use maps when each row needs different keys. No schema enforcement.

Quiz

Manipulating Nested Data

In traditional SQL, working with nested data requires complex joins or custom functions. DuckDB provides native operations: list indexing, Python-style comprehensions, dot notation for structs, and UNNEST to flatten lists.

Access list elements:

You can access elements using 1-based indexing, negative indices for the end, and slicing.

Try it

Write the slice to extract [20, 30, 40] from the list:

Transform lists with list comprehensions:

You can transform lists directly in SQL using Python-style comprehensions.

Quiz

Access struct fields:

DuckDB lets you access struct fields using dot notation, just like object properties.

Quiz

Unnest lists to rows:

The UNNEST function expands list elements into individual rows for row-by-row analysis.

Quiz

Parameterized Queries

When working with databases, you often need to run similar queries with different parameters. For instance, you might want to filter a table using various criteria.

First, let’s create a sample products table:

You might use f-strings to pass parameters to your queries:

DuckDB provides a safer way with parameterized queries using the ? placeholder:

Try it

Use the ? placeholder to find products under $300:

Quiz

ACID Transactions

DuckDB supports ACID transactions for data integrity:

• Atomicity: The transaction either completes entirely or has no effect at all. If any operation fails, all changes are rolled back.
• Consistency: The database maintains valid data by enforcing all rules and constraints throughout the transaction.
• Isolation: Transactions run independently without interfering with each other.
• Durability: Committed changes are permanent and survive system failures.

Let’s demonstrate ACID properties with a bank transfer. First, set up accounts and create a transfer function with balance checking:

Now define a transfer function that checks the balance before transferring. If funds are insufficient, it rolls back the transaction:

In the code above:

• BEGIN TRANSACTION starts a transaction block where all changes remain hidden until committed
• COMMIT permanently saves all changes made within the transaction
• ROLLBACK cancels all changes and restores the database to its state before the transaction began

Now let’s perform a valid transfer of $200 from Alice to Bob:

Now let’s attempt an invalid transfer. Bob tries to send $1000 to Charlie, but he only has $700:

Quiz

Attach External Databases

DuckDB can connect to external databases and query them as if they were local tables. This enables federated queries across PostgreSQL, MySQL, SQLite, and DuckDB files.

Attach a SQLite database:

Querying a SQLite database is straightforward. Just install the extension, attach the file, and run SQL.

Quiz

Attach a PostgreSQL database:

Connecting to PostgreSQL is just as easy. Simply install the extension and provide your connection details.

When querying with filters, DuckDB pushes the WHERE condition to PostgreSQL. Only matching rows travel back over the network, then DuckDB runs aggregations on the received data.

This query shows how DuckDB pushes the WHERE clause to PostgreSQL.

The diagram below illustrates the network optimization:

DuckDB                         PostgreSQL
  │                                │
  │──── WHERE year = 2024 ────────>│
  │                                │ (filters 1M → 10K rows)
  │<─── 10K matching rows ─────────│
  │                                │
  │ (runs SUM, GROUP BY locally)   │

Quiz

Federated queries – join across databases:

DuckDB’s real power shows when joining across databases. Simply attach each source and reference tables with their database prefix.

Quiz

Save attached database locally:

To avoid repeated network calls, you can create a local copy of remote tables.

Key Takeaways

DuckDB empowers data scientists with several key advantages:

• Zero-configuration SQL querying without database server setup
• Seamless integration with pandas and Polars DataFrames
• Out-of-core processing for datasets larger than available RAM
• Friendly SQL syntax with shortcuts like GROUP BY ALL and EXCLUDE
• Direct querying of cloud storage (S3, Azure, GCS)
• Reading and writing Hive-partitioned datasets
• Rich nested data types (LIST, STRUCT, MAP) with list comprehensions
• Federated queries across PostgreSQL, MySQL, and SQLite
• ACID transaction support ensuring data integrity
• High-performance columnar-vectorized execution engine

With DuckDB, you can unlock a new level of productivity and efficiency in your data analysis workflows.