Quant Data Pipeline

Quant Data Pipeline

Shared data infrastructure for quantitative research where AI agents and humans analyze the same data without sync, transfer, or format conversion. Built on DuckDB (analytical SQL), Polars (fast DataFrames), and Parquet (columnar storage).

Triggers

Use this skill when:

• Setting up data infrastructure for quant/analytics work
• Building a pipeline where AIs and humans share the same datasets
• User asks about data formats for quant analysis
• Need to ingest CSV/JSON/API data and query it efficiently
• Evaluating storage formats vs Samba/SSHFS/network shares

Architecture

company/data/
├── pipeline.py        ← CSV/JSON/API → .parquet (ingestion)
├── query.py           ← DuckDB SQL on Parquet (CLI + REPL + saved queries)
├── raw/               ← Incoming CSVs, JSON, API dumps (transient)
├── processed/         ← Cleaned .parquet files (source of truth)
├── queries/           ← Saved .sql files (runnable by humans + AIs)
└── exports/           ← .parquet → .csv (for Excel consumption)

Core insight: AIs read/write local Parquet files at native ext4 speed. Humans point the same query tool at the same files via SSH or a synced directory. No Samba, no network filesystem overhead, no format conversion.

Setup

# 1. Directory structure
mkdir -p ~/company/{data/{raw,processed,queries,exports},docs,logs,shared}

# 2. Python environment
python3 -m venv ~/company/venv
source ~/company/venv/bin/activate
pip install duckdb polars pyarrow pandas

# 3. Deploy pipeline.py and query.py from references/

Day-to-Day Usage

source ~/company/venv/bin/activate
cd ~/company/data

# List all datasets
python pipeline.py list

# Ingest new data
python pipeline.py ingest new_data.csv table_name

# Run a saved analytical query
python query.py saved perf_sharpe

# Ad-hoc DuckDB SQL
python query.py run "SELECT ticker, AVG(sharpe) FROM signals GROUP BY ticker ORDER BY 2 DESC"

# Describe a table (schema + row count + sample)
python query.py describe signals

# Interactive SQL shell
python query.py shell

# Export Parquet → CSV (for Excel)
# (query.py → pipeline.py via pipe, or direct export)
python pipeline.py export table_name output.csv

Key Design Decisions

Why Parquet over CSV

• Columnar: read 2 columns from 50M rows in ~0.1s vs ~30s for CSV
• Compression: typical quant data compresses 10:1 (500 MB CSV → 50 MB Parquet)
• Type-safe: datetimes stay datetimes, floats stay float64
• Universal: pandas, Polars, DuckDB, PySpark, R — all read it natively

Why DuckDB over pandas

• Zero server, single binary, embedded
• Reads Parquet files directly — no "load into memory" step
• Analytical SQL is faster to write and more readable than pandas chains
• Saved .sql files are version-controllable and AI-friendly

Why Polars for ETL

• Faster than pandas for CSV ingestion (Rust engine)
• Lazy evaluation for large files
• Direct Parquet write without intermediate steps

Why not Samba/SSHFS for the working directory

• Excel over Samba triggers constant small reads/writes (auto-save, lock files)
• Every SMB operation has handshake overhead — hundreds of ops = noticeable lag
• Solution: AIs work on local ext4. Humans use SSHFS for browsing, Syncthing for active-editing folders, or SSH in and run queries directly.

Pitfalls

• Path resolution in pipeline.py: Use Path(__file__).parent (ONE parent), not .parent.parent. The script lives IN the data/ directory, so the company root is the parent of data/.
• First run has no tables: query.py will return empty results. Run pipeline.py ingest first with sample data to populate, or pipeline.py list to confirm state.
• DuckDB .sql column names: Saved queries assume specific column names. If your Parquet schema differs, create new query files adapted to your columns.
• Large CSVs: For files >1 GB, use Polars lazy loading: pl.scan_csv() instead of pl.read_csv().

Templates

• templates/queries/ — reusable SQL query patterns for quant analysis

Files

References

• references/pipeline-py.md — full pipeline.py source (CSV/JSON/API → Parquet, export)
• references/query-py.md — full query.py source (DuckDB CLI, REPL, saved queries)

Templates

• templates/queries/perf_sharpe.sql — top signals by Sharpe ratio (last 90 days)
• templates/queries/signal_decay.sql — forward return decay across time horizons
• templates/queries/correlation_matrix.sql — cross-category signal correlation
• templates/queries/data_quality.sql — null %, duplicates, outliers, date ranges

Copy any template to company/data/queries/ and run with python query.py saved <name>.