duckdb-patternslisted

# DuckDB Patterns In-process analytical SQL. Treat DuckDB as a SQL-fluent replacement for the pandas-only pipeline when the dataset stops fitting comfortably in memory or you want to query parquet/csv/Postgres/S3 from one engine without standing up infrastructure. ## When to Activate - Reading or aggregating parquet / csv / json larger than RAM, or fast enough that pandas startup is the bottleneck - Replacing a pandas script with SQL for clarity, joins, or window functions - Embedded OLAP inside Python / Node code (no separate server) - Federated queries: parquet on disk + S3 + Postgres in one statement - DataFrame <-> SQL interop with Arrow zero-copy (no serialisation cost) Don't reach for it as a transactional store — it's an OLAP engine, single writer. Use Postgres / SQLite for OLTP. ## Connection Modes ```python import duckdb # In-memory: ephemeral, perfect for ad-hoc analysis con = duckdb.connect() # Persistent on disk: catalog + indexes + data survive restarts con = duckdb.connect("warehouse.duckdb") # Read-only: safe to share a file across processes (writers still single-process) con = duckdb.connect("warehouse.duckdb", read_only=True) ``` CLI: ```bash duckdb warehouse.duckdb # interactive duckdb -c "SELECT 1" # one-shot duckdb -json -c "SELECT 1" # JSON output for piping ``` ## Reading Files Without Loading DuckDB can query files directly — no `CREATE TABLE` step required. ```sql -- Auto-detect schema, headers, types SEL