Part 171: DuckDB & Embedded Analytical SQL: Vectorized Parquet Engine

"SQLite for Analytics": Run blazing-fast OLAP SQL directly in your Python, Node, or CLI processes.

1. What is DuckDB?

DuckDB is an in-process, serverless SQL OLAP database: - Zero external server dependencies (runs inside your Python / Node process). - State-of-the-art columnar vectorized query engine. - Capable of querying multi-gigabyte Parquet, CSV, and JSON files directly from local disk or S3 without loading them first!

2. Querying Parquet Files Directly with Pure SQL

-- Query remote S3 Parquet file without spinning up a cluster:
INSTALL httpfs;
LOAD httpfs;

SELECT 
    pickup_zone, 
    COUNT(*) AS ride_count,
    ROUND(AVG(fare_amount), 2) AS avg_fare
FROM 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2026-01.parquet'
WHERE passenger_count > 1
GROUP BY pickup_zone
ORDER BY ride_count DESC
LIMIT 10;

3. Exporting Query Results to Parquet

-- Convert 20GB of raw CSVs into highly compressed Parquet in seconds:
COPY (
    SELECT * FROM read_csv_auto('logs/*.csv')
) TO 'output/compressed_logs.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);

4. Seamless Python / Pandas / Arrow Interoperability

import duckdb
import pandas as pd

df = pd.DataFrame({"id": [1, 2, 3], "val": [10.5, 20.0, 35.2]})

# Query the pandas DataFrame directly via SQL:
res = duckdb.query("SELECT SUM(val) AS total FROM df WHERE id > 1").df()
print(res)

Key Takeaways

• DuckDB replaces heavy Spark clusters for local, embedded, and single-node data engineering pipelines.

In Part 172, we explore ClickHouse SQL: MergeTree Engine Family & High-Speed Ingestion!