\n
\n Introduction – SQL without a Server<\/p>\n
Pandas is widely used for data analysis and almost every data analyst or even data engineers utilize it for faster analysis with table like data structure called DataFrames.The drawback is that it suffers once the data goes beyond few GB’s and spinning up a Postgres or a Redshift is an overkill for quick analysis.Duck DB fills this gap with Zero-setup columnar SQL.<\/p>\n
Getting Started – zero config, instant power<\/p>\n
DuckDb is an open source OLAP database management system designed for analytics and for running within the same process as the application.It is lightweight, can work directly with data files in csv, parquet etc without needing a server.<\/p>\n
Installation and first query<\/p>\n
pip install duckdb – No ports to open, No configuration and No daemon<\/p>\n
In-Memory and Persistent Database – Two Operating Modes<\/p>\n
In-MemoryWhen DuckDB connection is created without specifying a file, a database lives entirely in RAM.<\/p>\n
import duckdb
\ncon = duckdb.connect() # or duckdb.connect(‘:memory:’)<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
All data is stored in RAM and no files are written to disk
\nExtremely fast reads\/writes since there is zero I\/O overhead.
\nData is completely lost when connection closes.
\nNo file locking or concurrency concerns<\/p>\n
Persistent ModeWhen the user provides a location DuckDB can write the results to disk in .duckDb format.<\/p>\n
con = duckdb.connect(‘my_database.duckdb’)<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
Tables,Schemas and indexes are persisted.
\nUses a columnar storage format with compression and buffered I\/O
\nOnly one write connection at a time but multiple read connection are allowed.
\nSupports WAL(Write Ahead Logging) for crash recovery<\/p>\n
Powerful Pattern<\/p>\n
DuckDb allows you to mix both modes where user can start with in-memory and attach a persistent database or use copy\/export to snapshot in-memory result to disk.<\/p>\n
con = duckdb.connect()<\/p>\n
#Query a CSV, transform it, save the result to a persistent file
\ncon.execute(“””
\n COPY(SELECT region, SUM(sales) AS total FROM read_csv(‘data.csv’)
\n GROUP BY region
\n )
\n TO ‘results.parquet’ (FORMAT PARQUET)
\n“””)<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
Users gets the speed of In-Memory processing which accelerates the pipeline processing with an option to persist.<\/p>\n
Reading files directly –CSV,PARQUET,JSON,Arrow,<\/p>\n
Query CSV without loading into memory<\/p>\n
Select * from read_csv(‘data_csv’, auto_detect=true);<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
-Auto detects delimiter, compression and data types-Handles malformed rows gracefully-Can read multiple CSVs at once read_csv(‘data\/*.csv’)<\/p>\n
Parquet<\/p>\n
Select * from read_parquet(‘data.parquet’);
\n–even from S3 directly
\nSelect * from read_parquet(‘s3:\/\/bucket\/data\/*.parquet’);<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
Exploits column pruning as it only reads columns you need
\nLeverages row group skipping using Parquet’s build in min\/max stats
\nNative support for nested types(structs,list,maps)<\/p>\n
JSON\/NDJSON<\/p>\n
SELECT * FROM read_json(‘events.ndjson’, auto_detect=true);<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
-AUTO INFERS schema from data-NDJSON(Newline delimited) streams efficiently line by line-Can unnest deeply nested JSON fields using DuckDB’s json_extract, UNNEST, or -> operators<\/p>\n
Apache Arrow<\/p>\n
import pyarrow as pa
\narrow_table = pa.Table.from_pandas(df)
\nduckdb.query(“”SELECT * from arrow_table”””)<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
-Zero copy integration: DuckDB reads from Arrow memory without serialization-Ideal for pipelines where data never needs to touch disk<\/p>\n
SQL Beyond Select<\/p>\n
DuckDB is not just a query engine, it supports rich SQL that covers data transformation, creation, and some genuinely unique syntax extensions to available in most databases.<\/p>\n
Full Suite of WINDOW Functions<\/p>\n
Select
\n customer,
\n ordered_at,
\n amount,<\/p>\n
— Running total
\n SUM(amount) OVER (PARTITION BY customer ORDER BY ordered_at) AS running_tot,<\/p>\n
— Lag\/lead comparisons
\n LAG(amount) OVER (PARTITION BY customer ORDER BY ordered_at) AS prev_amt,<\/p>\n
— Percentile rank
\n PERCENT_RANK() OVER (ORDER BY amount) AS pct_rank,<\/p>\n
— Named window reuse
\n FIRST_VALUE(amount) OVER w AS first_order
\nFROM orders
\nWINDOW w AS (PARTITION BY customer ORDER BY ordered_at);<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
DuckDB also allows the use of qualify clause which filters on window result without a subquery.<\/p>\n
Select * From orders
\nQUALIFY ROW_NUMBER() OVER (PARTITION BY customer ORDER BY amount DESC) = 1;<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
PIVOT and UNPIVOT<\/p>\n
Most databases make you write case when manually for PIVOTS.DuckDB does it natively.<\/p>\n
–PIVOT- rows to columns
\nPIVOT orders on region USING SUM(amount) GROUP BY year;<\/p>\n
–UNPIVOT- Column to rows
\nUNPIVOT sales_wide ON(q1,q2,q3,q4) INTO NAME quarter VALUE revenue;<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
MULTI DATABASE SQL<\/p>\n
–Attach another DuckDB file
\nATTACH ‘archive.duckdb’ AS archive;<\/p>\n
— Cross-database join
\nSELECT a.*, b.region
\nFROM main.orders a
\nJOIN archive.customers b ON a.customer_id = b.id;<\/p>\n
–Attach another database
\nATTACH ‘postgres:\/\/user:pass@host\/db’ AS pg (TYPE POSTGRES);
\nSELECT * FROM pg.public.users LIMIT 10;<\/p>\n
Enter fullscreen mode<\/p>\n
Exit fullscreen mode<\/p>\n
DUCKDB+Pandas+Polars –Choosing your stack<\/p>\n
DuckDB does not replace pandas or Polars it solves a problem which is niche.The sweet spot of the industry is to use DuckDB for SQL-shaped operations and pandas\/polars for row level python logic.<\/p>\n
Where Duck DB shines<\/p>\n
Feature Engineering for ML: Window functions or group by’s for feature computation are often faster and more readable in DuckDB then pandas before handing it over to Sklearn or pytorch
\nUnit testing DBT models locally:DuckDB lets you run complete dbt project locally without a cloud warehouse providing fast feedback loop for data engineers.
\nLight weight ETL Pipelines: One can read raw parquet from S3, transform with SQL, write cleaned output back without any spark cluster or airflow jobs. <\/p>\n
Conclusion<\/p>\n
DuckDB lets you think in SQL for analytical tasks without worrying about infrastructure setup. Anyone using python can utilize duckdb for analysis of larger files where regular pandas will give headache.Given the advantages, it is important to know whare DuckDB should not be used which in case of concurrent writes,OLTP workloads and long running multi user services.<\/p>\n
Reference-https:\/\/duckdb.org\/docs\/current\/data\/overview<\/p>\n