Numerical computing - Intro to DuckDB¶

DuckDB is an in-process analytical database that runs entirely inside your Python session. It is optimised for OLAP (analytical) workloads: fast aggregations, scans, and joins on columnar data — without a server, configuration, or data movement.

This notebook covers:

Installation & connection
Creating and populating tables
Querying with SQL
Reading files directly (CSV, Parquet, JSON)
Working with pandas DataFrames
Data types and schema inspection
Aggregation and grouping
Joins
Window functions
CTEs and subqueries
String and date operations
Exporting results
Persistent databases
Benchmarks: DuckDB vs pandas

1. Installation & Connection¶

In [1]:

!pip install duckdb -q

In [2]:

import duckdb
import pandas as pd
import numpy as np

print(f'duckdb version: {duckdb.__version__}')

# In-memory database (default — nothing persisted to disk)
con = duckdb.connect()
print('Connected to in-memory DuckDB')

duckdb version: 1.5.2
Connected to in-memory DuckDB

DuckDB connections are cheap. You can also use the default in-process connection:

# Module-level shorthand — no explicit con needed
duckdb.sql('SELECT 42').fetchdf()

In [3]:

# Quick smoke test
con.sql("SELECT 'Hello, DuckDB!' AS greeting").fetchdf()

Out[3]:

	greeting
0	Hello, DuckDB!

2. Creating and Populating Tables¶

2.1 CREATE TABLE and INSERT¶

In [4]:

con.sql("""
    CREATE OR REPLACE TABLE employees (
        id      INTEGER PRIMARY KEY,
        name    VARCHAR,
        dept    VARCHAR,
        salary  DOUBLE,
        years   INTEGER,
        rating  DOUBLE
    )
""")

con.sql("""
    INSERT INTO employees VALUES
        (1, 'Alice', 'Eng',     95000, 5,  4.2),
        (2, 'Bob',   'HR',      72000, 3,  3.8),
        (3, 'Carol', 'Eng',    105000, 8,  4.5),
        (4, 'Dave',  'Finance', 88000, 6,  4.0),
        (5, 'Eve',   'HR',      68000, 2,  3.5),
        (6, 'Frank', 'Eng',    115000, 10, 4.8),
        (7, 'Grace', 'Finance', 92000, 4,  4.1)
""")

con.sql('SELECT * FROM employees').fetchdf()

Out[4]:

	id	name	dept	salary	years	rating
0	1	Alice	Eng	95000.0	5	4.2
1	2	Bob	HR	72000.0	3	3.8
2	3	Carol	Eng	105000.0	8	4.5
3	4	Dave	Finance	88000.0	6	4.0
4	5	Eve	HR	68000.0	2	3.5
5	6	Frank	Eng	115000.0	10	4.8
6	7	Grace	Finance	92000.0	4	4.1

2.2 CREATE TABLE AS SELECT (CTAS)¶

In [5]:

con.sql("""
    CREATE OR REPLACE TABLE eng_team AS
    SELECT * FROM employees WHERE dept = 'Eng'
""")
con.sql('SELECT * FROM eng_team').fetchdf()

Out[5]:

	id	name	dept	salary	years	rating
0	1	Alice	Eng	95000.0	5	4.2
1	3	Carol	Eng	105000.0	8	4.5
2	6	Frank	Eng	115000.0	10	4.8

2.3 List tables in the database¶

In [6]:

con.sql('SHOW TABLES').fetchdf()

Out[6]:

	name
0	employees
1	eng_team

3. Querying with SQL¶

3.1 Basic SELECT¶

In [7]:

con.sql("SELECT name, dept, salary FROM employees ORDER BY salary DESC").fetchdf()

Out[7]:

	name	dept	salary
0	Frank	Eng	115000.0
1	Carol	Eng	105000.0
2	Alice	Eng	95000.0
3	Grace	Finance	92000.0
4	Dave	Finance	88000.0
5	Bob	HR	72000.0
6	Eve	HR	68000.0

3.2 WHERE clause¶

In [8]:

con.sql("""
    SELECT name, salary
    FROM   employees
    WHERE  salary > 90000
      AND  dept = 'Eng'
""").fetchdf()

Out[8]:

	name	salary
0	Alice	95000.0
1	Carol	105000.0
2	Frank	115000.0

3.3 LIMIT and OFFSET¶

In [9]:

con.sql('SELECT * FROM employees ORDER BY salary DESC LIMIT 3').fetchdf()

Out[9]:

	id	name	dept	salary	years	rating
0	6	Frank	Eng	115000.0	10	4.8
1	3	Carol	Eng	105000.0	8	4.5
2	1	Alice	Eng	95000.0	5	4.2

In [10]:

con.sql('SELECT * FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 2').fetchdf()

Out[10]:

	id	name	dept	salary	years	rating
0	1	Alice	Eng	95000.0	5	4.2
1	7	Grace	Finance	92000.0	4	4.1
2	4	Dave	Finance	88000.0	6	4.0

3.4 DISTINCT and IN¶

In [11]:

con.sql('SELECT DISTINCT dept FROM employees').fetchdf()

Out[11]:

	dept
0	Eng
1	Finance
2	HR

In [12]:

con.sql("SELECT * FROM employees WHERE dept IN ('Eng', 'Finance')").fetchdf()

Out[12]:

	id	name	dept	salary	years	rating
0	1	Alice	Eng	95000.0	5	4.2
1	3	Carol	Eng	105000.0	8	4.5
2	4	Dave	Finance	88000.0	6	4.0
3	6	Frank	Eng	115000.0	10	4.8
4	7	Grace	Finance	92000.0	4	4.1

3.5 CASE expressions¶

In [13]:

con.sql("""
    SELECT name, salary,
           CASE
               WHEN salary >= 100000 THEN 'Senior'
               WHEN salary >= 85000  THEN 'Mid'
               ELSE                       'Junior'
           END AS level
    FROM employees
    ORDER BY salary DESC
""").fetchdf()

Out[13]:

	name	salary	level
0	Frank	115000.0	Senior
1	Carol	105000.0	Senior
2	Alice	95000.0	Mid
3	Grace	92000.0	Mid
4	Dave	88000.0	Mid
5	Bob	72000.0	Junior
6	Eve	68000.0	Junior

4. Reading Files Directly¶

DuckDB can query CSV, Parquet, and JSON files without loading them into a table first. This is one of its most powerful features.

4.1 CSV¶

In [14]:

# Query a CSV file as if it were a table
# con.sql("SELECT * FROM 'data.csv' LIMIT 5").fetchdf()

# With options
# con.sql("""
#     SELECT * FROM read_csv('data.csv',
#         header=true,
#         delim=',',
#         columns={'name': 'VARCHAR', 'age': 'INTEGER'})
# """).fetchdf()

# Demo: write a temp CSV and query it
import tempfile, os
tmp = tempfile.NamedTemporaryFile(suffix='.csv', delete=False, mode='w')
tmp.write('city,state,pop\nPittsburgh,PA,302971\nPhiladelphia,PA,1603797\nHarrisburg,PA,50135\n')
tmp.close()

con.sql(f"SELECT * FROM '{tmp.name}'").fetchdf()

Out[14]:

	city	state	pop
0	Pittsburgh	PA	302971
1	Philadelphia	PA	1603797
2	Harrisburg	PA	50135

4.2 Parquet¶

In [15]:

# con.sql("SELECT * FROM 'data.parquet' LIMIT 10").fetchdf()

# Write temp Parquet and query it
import pandas as pd
pdf = pd.DataFrame({'a': range(5), 'b': list('abcde')})
parquet_path = tmp.name.replace('.csv', '.parquet')
pdf.to_parquet(parquet_path, index=False)

con.sql(f"SELECT * FROM '{parquet_path}'").fetchdf()

Out[15]:

	a	b
0	0	a
1	1	b
2	2	c
3	3	d
4	4	e

4.3 Glob patterns — query multiple files at once¶

In [16]:

# Query all CSVs in a directory
# con.sql("SELECT * FROM 'data/chunk_*.csv'").fetchdf()

# Parquet partitions
# con.sql("SELECT * FROM 'data/year=*/month=*/*.parquet'").fetchdf()

4.4 JSON¶

In [17]:

# con.sql("SELECT * FROM 'data.json'").fetchdf()

# read_json_auto expects a file path — write inline JSON to a temp file first
import tempfile, os

json_data = '[{"name":"Alice","age":25},{"name":"Bob","age":30}]'
tmp_json = tempfile.NamedTemporaryFile(suffix='.json', delete=False, mode='w')
tmp_json.write(json_data)
tmp_json.close()

con.sql(f"SELECT * FROM read_json_auto('{tmp_json.name}')").fetchdf()

Out[17]:

	name	age
0	Alice	25
1	Bob	30

5. Working with Pandas DataFrames¶

DuckDB can query a pandas DataFrame by name — no import step required. It scans the DataFrame in-process without copying data.

In [18]:

df_pd = pd.DataFrame({
    'product': ['Widget', 'Gadget', 'Doohickey', 'Thingamajig'],
    'category': ['Hardware', 'Software', 'Hardware', 'Software'],
    'price': [9.99, 29.99, 4.99, 14.99],
    'stock': [120, 45, 300, 80],
})

# Reference the pandas DataFrame directly in SQL
con.sql('SELECT * FROM df_pd WHERE price < 20 ORDER BY price').fetchdf()

Out[18]:

	product	category	price	stock
0	Doohickey	Hardware	4.99	300
1	Widget	Hardware	9.99	120
2	Thingamajig	Software	14.99	80

In [19]:

# Aggregate a pandas DataFrame with SQL
con.sql("""
    SELECT category,
           COUNT(*)          AS n_products,
           AVG(price)        AS avg_price,
           SUM(stock)        AS total_stock
    FROM df_pd
    GROUP BY category
""").fetchdf()

Out[19]:

	category	n_products	avg_price	total_stock
0	Hardware	2	7.49	420.0
1	Software	2	22.49	125.0

5.1 Result fetch formats¶

In [20]:

result = con.sql('SELECT * FROM employees LIMIT 3')

result.fetchdf()          # pandas DataFrame

Out[20]:

	id	name	dept	salary	years	rating
0	1	Alice	Eng	95000.0	5	4.2
1	2	Bob	HR	72000.0	3	3.8
2	3	Carol	Eng	105000.0	8	4.5

In [21]:

con.sql('SELECT * FROM employees LIMIT 3').fetchnumpy()  # dict of NumPy arrays

Out[21]:

{'id': array([1, 2, 3], dtype=int32),
 'name': array(['Alice', 'Bob', 'Carol'], dtype=object),
 'dept': array(['Eng', 'HR', 'Eng'], dtype=object),
 'salary': array([ 95000.,  72000., 105000.]),
 'years': array([5, 3, 8], dtype=int32),
 'rating': array([4.2, 3.8, 4.5])}

In [22]:

con.sql('SELECT * FROM employees LIMIT 3').fetchall()    # list of tuples

Out[22]:

[(1, 'Alice', 'Eng', 95000.0, 5, 4.2),
 (2, 'Bob', 'HR', 72000.0, 3, 3.8),
 (3, 'Carol', 'Eng', 105000.0, 8, 4.5)]

In [23]:

con.sql('SELECT * FROM employees LIMIT 1').fetchone()    # single tuple

Out[23]:

(1, 'Alice', 'Eng', 95000.0, 5, 4.2)

6. Schema Inspection¶

In [24]:

con.sql('DESCRIBE employees').fetchdf()

Out[24]:

	column_name	column_type	null	key	default	extra
0	id	INTEGER	NO	PRI	None	None
1	name	VARCHAR	YES	None	None	None
2	dept	VARCHAR	YES	None	None	None
3	salary	DOUBLE	YES	None	None	None
4	years	INTEGER	YES	None	None	None
5	rating	DOUBLE	YES	None	None	None

In [25]:

con.sql("""
    SELECT column_name, data_type, is_nullable
    FROM information_schema.columns
    WHERE table_name = 'employees'
""").fetchdf()

Out[25]:

	column_name	data_type	is_nullable
0	id	INTEGER	NO
1	name	VARCHAR	YES
2	dept	VARCHAR	YES
3	salary	DOUBLE	YES
4	years	INTEGER	YES
5	rating	DOUBLE	YES

In [26]:

# Summary statistics in one shot
con.sql('SUMMARIZE employees').fetchdf()

Out[26]:

	column_name	column_type	min	max	approx_unique	avg	std	q25	q50	q75	count
0	id	INTEGER	1	7	7	4.0	2.160246899469287	2	4	6	7
1	name	VARCHAR	Alice	Grace	7	None	None	None	None	None	7
2	dept	VARCHAR	Eng	HR	3	None	None	None	None	None	7
3	salary	DOUBLE	68000.0	115000.0	7	90714.28571428571	16770.154896067455	76000.0	92000.0	102500.0	7
4	years	INTEGER	2	10	7	5.428571428571429	2.819996622760558	3	5	8	7
5	rating	DOUBLE	3.5	4.8	7	4.128571428571428	0.4309458036856674	3.8499999999999996	4.1	4.425	7

7. Aggregation and Grouping¶

In [27]:

con.sql("""
    SELECT dept,
           COUNT(*)          AS headcount,
           AVG(salary)       AS avg_salary,
           MIN(salary)       AS min_salary,
           MAX(salary)       AS max_salary,
           STDDEV(salary)    AS std_salary
    FROM employees
    GROUP BY dept
    ORDER BY avg_salary DESC
""").fetchdf()

Out[27]:

	dept	headcount	avg_salary	min_salary	max_salary	std_salary
0	Eng	3	105000.0	95000.0	115000.0	10000.000000
1	Finance	2	90000.0	88000.0	92000.0	2828.427125
2	HR	2	70000.0	68000.0	72000.0	2828.427125

In [28]:

# HAVING — filter after grouping
con.sql("""
    SELECT dept, COUNT(*) AS headcount, AVG(salary) AS avg_salary
    FROM   employees
    GROUP  BY dept
    HAVING COUNT(*) > 1
    ORDER  BY avg_salary DESC
""").fetchdf()

Out[28]:

	dept	headcount	avg_salary
0	Eng	3	105000.0
1	Finance	2	90000.0
2	HR	2	70000.0

In [29]:

# ROLLUP — add subtotals
con.sql("""
    SELECT dept, COUNT(*) AS headcount, ROUND(AVG(salary), 0) AS avg_salary
    FROM   employees
    GROUP  BY ROLLUP(dept)
    ORDER  BY dept NULLS LAST
""").fetchdf()

Out[29]:

	dept	headcount	avg_salary
0	Eng	3	105000.0
1	Finance	2	90000.0
2	HR	2	70000.0
3	None	7	90714.0

8. Joins¶

In [30]:

con.sql("""
    CREATE OR REPLACE TABLE departments (
        dept             VARCHAR PRIMARY KEY,
        location         VARCHAR,
        headcount_limit  INTEGER
    )
""")
con.sql("""
    INSERT INTO departments VALUES
        ('Eng',     'Pittsburgh', 20),
        ('HR',      'NYC',        10),
        ('Finance', 'Chicago',    15),
        ('Legal',   'Boston',      5)
""")

In [31]:

# INNER JOIN
con.sql("""
    SELECT e.name, e.dept, e.salary, d.location
    FROM   employees e
    JOIN   departments d ON e.dept = d.dept
    ORDER  BY e.salary DESC
""").fetchdf()

Out[31]:

	name	dept	salary	location
0	Frank	Eng	115000.0	Pittsburgh
1	Carol	Eng	105000.0	Pittsburgh
2	Alice	Eng	95000.0	Pittsburgh
3	Grace	Finance	92000.0	Chicago
4	Dave	Finance	88000.0	Chicago
5	Bob	HR	72000.0	NYC
6	Eve	HR	68000.0	NYC

In [32]:

# LEFT JOIN — keep all employees even if dept has no match
con.sql("""
    SELECT e.name, e.dept, d.location
    FROM   employees  e
    LEFT   JOIN departments d ON e.dept = d.dept
""").fetchdf()

Out[32]:

	name	dept	location
0	Alice	Eng	Pittsburgh
1	Bob	HR	NYC
2	Carol	Eng	Pittsburgh
3	Dave	Finance	Chicago
4	Eve	HR	NYC
5	Frank	Eng	Pittsburgh
6	Grace	Finance	Chicago

In [33]:

# RIGHT JOIN — keep all departments even if no employee
con.sql("""
    SELECT d.dept, d.location, e.name
    FROM   employees  e
    RIGHT  JOIN departments d ON e.dept = d.dept
""").fetchdf()

Out[33]:

	dept	location	name
0	Eng	Pittsburgh	Alice
1	HR	NYC	Bob
2	Eng	Pittsburgh	Carol
3	Finance	Chicago	Dave
4	HR	NYC	Eve
5	Eng	Pittsburgh	Frank
6	Finance	Chicago	Grace
7	Legal	Boston	None

In [34]:

# FULL OUTER JOIN
con.sql("""
    SELECT e.name, COALESCE(e.dept, d.dept) AS dept, d.location
    FROM   employees  e
    FULL   OUTER JOIN departments d ON e.dept = d.dept
""").fetchdf()

Out[34]:

	name	dept	location
0	Alice	Eng	Pittsburgh
1	Bob	HR	NYC
2	Carol	Eng	Pittsburgh
3	Dave	Finance	Chicago
4	Eve	HR	NYC
5	Frank	Eng	Pittsburgh
6	Grace	Finance	Chicago
7	None	Legal	Boston

9. Window Functions¶

Window functions compute a value for each row based on a related set of rows (the window) without collapsing rows like GROUP BY does.

In [35]:

con.sql("""
    SELECT name, dept, salary,
           RANK()   OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank,
           AVG(salary) OVER (PARTITION BY dept)                   AS dept_avg,
           salary - AVG(salary) OVER (PARTITION BY dept)          AS vs_dept_avg
    FROM employees
    ORDER BY dept, dept_rank
""").fetchdf()

Out[35]:

	name	dept	salary	dept_rank	dept_avg	vs_dept_avg
0	Frank	Eng	115000.0	1	105000.0	10000.0
1	Carol	Eng	105000.0	2	105000.0	0.0
2	Alice	Eng	95000.0	3	105000.0	-10000.0
3	Grace	Finance	92000.0	1	90000.0	2000.0
4	Dave	Finance	88000.0	2	90000.0	-2000.0
5	Bob	HR	72000.0	1	70000.0	2000.0
6	Eve	HR	68000.0	2	70000.0	-2000.0

In [36]:

# ROW_NUMBER, DENSE_RANK, PERCENT_RANK
con.sql("""
    SELECT name, salary,
           ROW_NUMBER()   OVER (ORDER BY salary DESC) AS row_num,
           DENSE_RANK()   OVER (ORDER BY salary DESC) AS dense_rank,
           PERCENT_RANK() OVER (ORDER BY salary DESC) AS pct_rank
    FROM employees
""").fetchdf()

Out[36]:

	name	salary	row_num	dense_rank	pct_rank
0	Frank	115000.0	1	1	0.000000
1	Carol	105000.0	2	2	0.166667
2	Alice	95000.0	3	3	0.333333
3	Grace	92000.0	4	4	0.500000
4	Dave	88000.0	5	5	0.666667
5	Bob	72000.0	6	6	0.833333
6	Eve	68000.0	7	7	1.000000

In [37]:

# Running total and moving average
con.sql("""
    SELECT name, salary,
           SUM(salary)  OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS running_total,
           AVG(salary)  OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
               AS moving_avg_3
    FROM employees
""").fetchdf()

Out[37]:

	name	salary	running_total	moving_avg_3
0	Alice	95000.0	95000.0	95000.000000
1	Bob	72000.0	167000.0	83500.000000
2	Carol	105000.0	272000.0	90666.666667
3	Dave	88000.0	360000.0	88333.333333
4	Eve	68000.0	428000.0	87000.000000
5	Frank	115000.0	543000.0	90333.333333
6	Grace	92000.0	635000.0	91666.666667

In [38]:

# LAG and LEAD
con.sql("""
    SELECT name, salary,
           LAG(salary, 1)  OVER (ORDER BY id) AS prev_salary,
           LEAD(salary, 1) OVER (ORDER BY id) AS next_salary
    FROM employees
""").fetchdf()

Out[38]:

	name	salary	prev_salary	next_salary
0	Alice	95000.0	NaN	72000.0
1	Bob	72000.0	95000.0	105000.0
2	Carol	105000.0	72000.0	88000.0
3	Dave	88000.0	105000.0	68000.0
4	Eve	68000.0	88000.0	115000.0
5	Frank	115000.0	68000.0	92000.0
6	Grace	92000.0	115000.0	NaN

10. CTEs and Subqueries¶

Common Table Expressions (CTEs) make complex queries readable by naming intermediate results.

In [39]:

con.sql("""
    WITH dept_stats AS (
        SELECT dept,
               AVG(salary) AS avg_salary,
               COUNT(*)    AS headcount
        FROM employees
        GROUP BY dept
    ),
    above_avg AS (
        SELECT e.name, e.dept, e.salary, d.avg_salary
        FROM   employees e
        JOIN   dept_stats d ON e.dept = d.dept
        WHERE  e.salary > d.avg_salary
    )
    SELECT * FROM above_avg ORDER BY salary DESC
""").fetchdf()

Out[39]:

	name	dept	salary	avg_salary
0	Frank	Eng	115000.0	105000.0
1	Grace	Finance	92000.0	90000.0
2	Bob	HR	72000.0	70000.0

In [40]:

# Recursive CTE — generate a number series
con.sql("""
    WITH RECURSIVE series(n) AS (
        SELECT 1
        UNION ALL
        SELECT n + 1 FROM series WHERE n < 10
    )
    SELECT n, n * n AS squared FROM series
""").fetchdf()

Out[40]:

	n	squared
0	1	1
1	2	4
2	3	9
3	4	16
4	5	25
5	6	36
6	7	49
7	8	64
8	9	81
9	10	100

11. String and Date Operations¶

11.1 String functions¶

In [41]:

con.sql("""
    SELECT name,
           UPPER(name)                   AS upper_name,
           LENGTH(name)                  AS name_len,
           SUBSTRING(name, 1, 3)          AS first3,
           name || ' (' || dept || ')'    AS label,
           REGEXP_MATCHES(name, '^[AEI]') AS starts_vowel
    FROM employees
""").fetchdf()

Out[41]:

	name	upper_name	name_len	first3	label	starts_vowel
0	Alice	ALICE	5	Ali	Alice (Eng)	True
1	Bob	BOB	3	Bob	Bob (HR)	False
2	Carol	CAROL	5	Car	Carol (Eng)	False
3	Dave	DAVE	4	Dav	Dave (Finance)	False
4	Eve	EVE	3	Eve	Eve (HR)	True
5	Frank	FRANK	5	Fra	Frank (Eng)	False
6	Grace	GRACE	5	Gra	Grace (Finance)	False

In [42]:

con.sql("""
    SELECT name,
           SPLIT_PART(name, 'a', 1)  AS before_a,
           REPLACE(name, 'e', '3')   AS leet,
           LPAD(name, 10, '-')        AS padded
    FROM employees
""").fetchdf()

Out[42]:

	name	before_a	leet	padded
0	Alice	Alice	Alic3	-----Alice
1	Bob	Bob	Bob	-------Bob
2	Carol	C	Carol	-----Carol
3	Dave	D	Dav3	------Dave
4	Eve	Eve	Ev3	-------Eve
5	Frank	Fr	Frank	-----Frank
6	Grace	Gr	Grac3	-----Grace

11.2 Date and time functions¶

In [43]:

con.sql("""
    SELECT CURRENT_DATE                      AS today,
           CURRENT_TIMESTAMP                   AS now,
           DATE_TRUNC('month', CURRENT_DATE)   AS month_start,
           CURRENT_DATE + INTERVAL '7 days'    AS next_week,
           EXTRACT('year'  FROM CURRENT_DATE)  AS year,
           EXTRACT('month' FROM CURRENT_DATE)  AS month,
           EXTRACT('dow'   FROM CURRENT_DATE)  AS day_of_week
""").fetchdf()

Out[43]:

	today	now	month_start	next_week	year	month	day_of_week
0	2026-05-03	2026-05-03 07:57:20.738773-04:00	2026-05-01	2026-05-10	2026	5	0

In [44]:

con.sql("""
    WITH events AS (
        SELECT UNNEST(['2024-01-15', '2024-03-22', '2024-07-04', '2024-12-31']::DATE[])
            AS event_date
    )
    SELECT event_date,
           STRFTIME(event_date, '%B %d, %Y')          AS formatted,
           DATEDIFF('day', '2024-01-01'::DATE, event_date) AS days_into_year,
           DAYNAME(event_date)                         AS weekday
    FROM events
""").fetchdf()

Out[44]:

	event_date	formatted	days_into_year	weekday
0	2024-01-15	January 15, 2024	14	Monday
1	2024-03-22	March 22, 2024	81	Friday
2	2024-07-04	July 04, 2024	185	Thursday
3	2024-12-31	December 31, 2024	365	Tuesday

12. Exporting Results¶

In [45]:

# Export to CSV
# con.sql("COPY employees TO 'output.csv' (HEADER, DELIMITER ',')")  

# Export to Parquet
# con.sql("COPY employees TO 'output.parquet' (FORMAT PARQUET)")

# Export a query result — not just a table
# con.sql("""
#     COPY (SELECT * FROM employees WHERE dept = 'Eng')
#     TO 'eng.csv' (HEADER)
# """)

# Fetch into a pandas DataFrame (most common in notebook workflows)
result_df = con.sql('SELECT * FROM employees ORDER BY salary DESC').fetchdf()
result_df

Out[45]:

	id	name	dept	salary	years	rating
0	6	Frank	Eng	115000.0	10	4.8
1	3	Carol	Eng	105000.0	8	4.5
2	1	Alice	Eng	95000.0	5	4.2
3	7	Grace	Finance	92000.0	4	4.1
4	4	Dave	Finance	88000.0	6	4.0
5	2	Bob	HR	72000.0	3	3.8
6	5	Eve	HR	68000.0	2	3.5

13. Persistent Databases¶

By default DuckDB uses an in-memory database. Pass a file path to persist data to disk.

In [46]:

import tempfile, os

db_path = os.path.join(tempfile.gettempdir(), 'demo.duckdb')

# Open (or create) a persistent database
con_file = duckdb.connect(db_path)
con_file.sql('CREATE OR REPLACE TABLE notes AS SELECT 1 AS id, \'hello\' AS msg')
con_file.close()

# Re-open and the data is still there
con_file2 = duckdb.connect(db_path)
print(con_file2.sql('SELECT * FROM notes').fetchdf().to_string())
con_file2.close()

os.remove(db_path)
print('Cleaned up.')

   id    msg
0   1  hello
Cleaned up.

14. Benchmarks: DuckDB vs. Pandas¶

DuckDB uses vectorized, columnar execution. On analytical queries (aggregations, scans, filtered selects) it is typically 2–10× faster than pandas for in-memory data, and much faster when reading files because it only deserialises needed columns.

14.1 Setup¶

In [47]:

import time
import numpy as np
import pandas as pd
import duckdb

np.random.seed(42)
N = 5_000_000

def bench(label, fn, repeats=3):
    times = []
    for _ in range(repeats):
        t0 = time.perf_counter()
        fn()
        times.append(time.perf_counter() - t0)
    print(f'{label:55s}  min={min(times)*1000:7.1f} ms  mean={sum(times)/len(times)*1000:7.1f} ms')

df_big = pd.DataFrame({
    'a':     np.random.randn(N),
    'b':     np.random.randn(N),
    'group': np.random.choice(['x', 'y', 'z'], size=N),
    'val':   np.random.randint(0, 1000, size=N),
})

bench_con = duckdb.connect()
print(f'Dataset: {N:,} rows × 4 columns')

Dataset: 5,000,000 rows × 4 columns

14.2 GroupBy aggregation¶

In [48]:

bench('pandas  groupby mean+std   (5 M rows)',
      lambda: df_big.groupby('group')['a'].agg(['mean', 'std']))

bench('duckdb  groupby mean+std   (5 M rows)',
      lambda: bench_con.sql("""
          SELECT   "group", AVG(a) AS mean_a, STDDEV(a) AS std_a
          FROM     df_big
          GROUP BY "group"
      """).fetchdf())

pandas  groupby mean+std   (5 M rows)                    min=  103.1 ms  mean=  104.9 ms
duckdb  groupby mean+std   (5 M rows)                    min=    7.5 ms  mean=    8.4 ms

14.3 Filtered aggregation¶

In [49]:

bench('pandas  filter + groupby  (5 M rows)',
      lambda: df_big[df_big['val'] > 500].groupby('group')['b'].mean())

bench('duckdb  WHERE + GROUP BY   (5 M rows)',
      lambda: bench_con.sql("""
          SELECT   "group", AVG(b)
          FROM     df_big
          WHERE    val > 500
          GROUP BY "group"
      """).fetchdf())

pandas  filter + groupby  (5 M rows)                     min=   64.0 ms  mean=   68.3 ms
duckdb  WHERE + GROUP BY   (5 M rows)                    min=    4.1 ms  mean=    4.3 ms

14.4 Column arithmetic¶

In [50]:

bench('pandas  a * b + val       (5 M rows)',
      lambda: df_big['a'] * df_big['b'] + df_big['val'])

bench('duckdb  a * b + val       (5 M rows)',
      lambda: bench_con.sql('SELECT a * b + val FROM df_big').fetchdf())

pandas  a * b + val       (5 M rows)                     min=    2.8 ms  mean=    3.1 ms
duckdb  a * b + val       (5 M rows)                     min=    8.9 ms  mean=    9.6 ms

14.5 Reading Parquet — column pruning¶

In [51]:

import tempfile, os

tmpdir  = tempfile.mkdtemp()
pq_path = os.path.join(tmpdir, 'big.parquet')
df_big.to_parquet(pq_path, index=False)
print(f'File size: {os.path.getsize(pq_path)/1e6:.1f} MB')

bench('pandas  read_parquet all cols (5 M rows)',
      lambda: pd.read_parquet(pq_path))

bench('pandas  read_parquet 1 col   (5 M rows)',
      lambda: pd.read_parquet(pq_path, columns=['a']))

bench('duckdb  scan parquet all cols  (5 M rows)',
      lambda: bench_con.sql(f"SELECT * FROM '{pq_path}'").fetchdf())

bench('duckdb  scan parquet 1 col    (5 M rows)',
      lambda: bench_con.sql(f"SELECT a FROM '{pq_path}'").fetchdf())

import shutil
shutil.rmtree(tmpdir)

File size: 90.3 MB
pandas  read_parquet all cols (5 M rows)                 min=   51.0 ms  mean=   62.6 ms
pandas  read_parquet 1 col   (5 M rows)                  min=   12.8 ms  mean=   14.3 ms
duckdb  scan parquet all cols  (5 M rows)                min=   58.6 ms  mean=   61.8 ms
duckdb  scan parquet 1 col    (5 M rows)                 min=   12.7 ms  mean=   13.3 ms

14.6 Summary¶

Workload	Winner	Notes
GroupBy aggregation	DuckDB	Vectorised columnar engine
Filtered aggregation	DuckDB	Predicate pushdown skips irrelevant rows
Column arithmetic (in-memory)	Roughly equal	Both use SIMD; pandas calls NumPy
Parquet scan (all columns)	DuckDB	Reads in parallel, skips row groups
Parquet column pruning	DuckDB	Only deserialises needed columns
Complex pandas transforms	pandas	Rich API for reshaping, string ops, etc.
Row-level Python logic	pandas	DuckDB is SQL-only; UDFs are limited

Rule of thumb: for SQL-expressible analytical queries, DuckDB is faster. For row-level Python logic or complex reshaping, stay in pandas.

Licensed under the GNU General Public License v2.0 (GPL-2).
You may redistribute and/or modify this work under the terms of GPL-2.
This work is distributed WITHOUT ANY WARRANTY.
Happy computing.

icaoberg / Numerical computing - Intro to DuckDB