Do You Really Understand the “Cost” of CSV?

Imagine you have a 5GB sales data CSV file and need to run aggregation queries with DuckDB. You confidently execute your query, only to wait 30 seconds for results. Worse yet, if you repeat this process daily, the time cost multiplies exponentially.

The problem isn’t that DuckDB is slow—it’s that CSV is a row-based format. When you only need 3 columns, DuckDB still has to read and parse every single row in its entirety.

This tutorial teaches you how to migrate your data from CSV to Parquet columnar storage using DuckDB, achieving 10x+ query speedups and reducing memory usage to 1/5 of the original.

Core Principles: Why Is Parquet So Much Faster Than CSV?

Row-Based vs Column-Based Storage

CSV (Row-Based Storage) stores data like this on disk:

Row1: [id, name, amount, date, category, ...]
Row2: [id, name, amount, date, category, ...]
Row3: [id, name, amount, date, category, ...]

When you run SELECT amount FROM sales, DuckDB must read every field in every row, then extract only the amount column. If a row has 20 fields but your query needs just 1, 19/20 of the I/O is wasted.

Parquet (Column-Based Storage) arranges data completely differently:

Column [amount]:    [100, 200, 150, 300, ...]  (compressed to ~1/5 of original)
Column [date]:      [2026-01-01, 2026-01-02, ...]
Column [category]:  ["Electronics", "Clothing", "Food", ...]

Need only the amount column? DuckDB directly targets the amount column’s data blocks—the other columns are never loaded.

Parquet’s Three Killer Features

1. Compression: Data within a single column is highly similar (e.g., all dates, all amounts), achieving compression ratios of typically 3-10x. A 5GB CSV might compress to just 500MB-1GB.

2. Predicate Pushdown: Filter conditions take effect at the data reading stage. WHERE amount > 1000 skips loading blocks that don’t satisfy the condition, rather than reading everything and filtering afterward.

3. Column-Level Statistics: Each data block (Row Group) records the minimum, maximum, and NULL count for that column. The query optimizer can directly skip blocks that don’t meet the query criteria.

Step 1: CSV to Parquet Conversion

DuckDB makes format conversion a one-liner:

import duckdb

con = duckdb.connect("analysis.db")

# Method 1: Direct read/write with DuckDB auto-optimization
con.execute("""
    CREATE TABLE orders AS
    SELECT * FROM read_csv_auto('/data/sales_2026.csv')
""")

# Export to Parquet (auto Snappy compression)
con.execute("COPY orders TO '/data/sales_2026.parquet' (FORMAT PARQUET)")

print("✅ CSV → Parquet conversion complete")

💡 Key Tip: COPY ... TO ... (FORMAT PARQUET) automatically uses Snappy compression on write. For higher compression ratios, specify COMPRESSION ZSTD.

Step 2: Performance Comparison—Immediate Results

Here’s a complete performance testing script:

import time

# Test 1: Read CSV
start = time.time()
csv_result = con.execute("""
    SELECT category, SUM(amount) AS total
    FROM read_csv_auto('/data/sales_2026.csv')
    GROUP BY category
    ORDER BY total DESC
""").fetchdf()
csv_time = time.time() - start

# Test 2: Read Parquet
start = time.time()
parquet_result = con.execute("""
    SELECT category, SUM(amount) AS total
    FROM read_parquet('/data/sales_2026.parquet')
    GROUP BY category
    ORDER BY total DESC
""").fetchdf()
parquet_time = time.time() - start

print(f"CSV time: {csv_time:.2f}s")
print(f"Parquet time: {parquet_time:.2f}s")
print(f"Speedup: {csv_time/parquet_time:.1f}x")

###实测 Results (10 million rows, 4GB CSV)

💡 Key Insight: The difference is largest with WHERE filters—because Parquet’s predicate pushdown skips unsatisfying data blocks at read time, rather than reading everything first.

Step 3: Partitioned Parquet—the Ultimate Solution for Large Datasets

When data reaches hundreds of millions of rows, a single Parquet file is still too large. Use partitioning:

# Write with year-month partitioning
con.execute("""
    COPY orders TO '/data/sales_parquet/'
    (FORMAT PARQUET, PARTITION_BY (year, month))
""")

Generated directory structure:

/data/sales_parquet/
  year=2026/month=01/
    part-0.parquet
    part-1.parquet
  year=2026/month=02/
    part-0.parquet
  ...

Query only the partitions you need:

# Read only August 2026 data—the other months are never touched
result = con.execute("""
    SELECT category, SUM(amount) AS total
    FROM read_parquet('/data/sales_parquet/year=2026/month=08/*.parquet')
    GROUP BY category
    ORDER BY total DESC
""").fetchdf()

💡 Key Insight: Even with a 100GB dataset, reading just one month’s data means DuckDB loads only those small files instead of scanning everything. This is the power of partition pruning.

Step 4: Advanced Compression Options

DuckDB supports multiple compression algorithms, each suited to different scenarios:

# Method A: Fast compression (Snappy, default, fastest)
con.execute("COPY orders TO '/data/fast.parquet' (FORMAT PARQUET)")

# Method B: High compression ratio (ZSTD, smaller files, slightly slower reads)
con.execute("COPY orders TO '/data/small.parquet' (FORMAT PARQUET, COMPRESSION ZSTD)")

# Method C: Fine-grained tuning with custom settings
con.execute("""
    COPY orders TO '/data/smart.parquet' (
        FORMAT PARQUET,
        COMPRESSION ZSTD,
        PAGE_SIZE = 32768,      -- 32KB per data page
        STATISTICS_SIZE = 0.1   -- 10% column statistics sampling
    )
""")

# Method D: Control ZSTD compression level (1-22, higher = better ratio but slower)
con.execute("""
    COPY orders TO '/data/tuned.parquet' (
        FORMAT PARQUET,
        COMPRESSION ZSTD,
        ZSTD_COMPRESSION_LEVEL = 9
    )
""")

Compression Strategy Selection Guide

Step 5: Other Practical Parquet Tips

5.1 Batch Reading Multiple Files

# Read all Parquet files in a directory (auto-merge schemas)
result = con.execute("""
    SELECT * FROM read_parquet('/data/exports/*.parquet')
    LIMIT 100
""").fetchdf()

# Read matching files with filters
result = con.execute("""
    SELECT * FROM read_parquet('/data/sales_2026_*.parquet')
    WHERE amount > 1000
""").fetchdf()

5.2 Keep Filename as a Column

# filename=true auto-adds a column showing the source file
result = con.execute("""
    SELECT filename, category, SUM(amount) AS total
    FROM read_parquet('/data/monthly/*.parquet', filename=true)
    GROUP BY filename, category
    ORDER BY total DESC
""").fetchdf()

5.3 Create Persistent Tables Directly from Parquet

con.execute("""
    CREATE TABLE monthly_sales AS
    SELECT * FROM read_parquet('/data/monthly/*.parquet')
""")

# Subsequent queries read the table directly—no file re-reading needed
con.execute("SELECT * FROM monthly_sales LIMIT 10")

Complete实战: Building a Parquet Data Pipeline

Here’s a complete production-grade data pipeline example:

import duckdb
import os
from datetime import datetime

DB_PATH = "sales_analytics.duckdb"
RAW_DIR = "/data/raw"
PARQUET_DIR = "/data/parquet"

con = duckdb.connect(DB_PATH)

# ── Step 1: Incrementally convert new CSVs to Parquet ──
def convert_csv_to_parquet(csv_file):
    """Auto-convert incoming CSV to partitioned Parquet"""
    basename = os.path.basename(csv_file).replace('.csv', '')
    
    con.execute(f"""
        CREATE OR REPLACE TABLE staging AS
        SELECT * FROM read_csv_auto('{csv_file}')
    """)
    
    # Extract date for partitioning
    sample_date = con.execute("""
        SELECT MIN(order_date) FROM staging
    """).fetchone()[0]
    year = str(sample_date)[:4]
    month = str(sample_date)[5:7]
    
    # Write to partitioned Parquet
    partition_dir = f"{PARQUET_DIR}/year={year}/month={month}/"
    os.makedirs(partition_dir, exist_ok=True)
    
    con.execute(f"""
        COPY staging TO '{partition_dir}'
        (FORMAT PARQUET, PARTITION_BY (year, month))
    """)
    
    print(f"✅ {csv_file} → {partition_dir}")
    return partition_dir

# ── Step 2: Cross-month aggregation (only reads needed partitions) ──
def query_by_date_range(start_month, end_month):
    """Query data for a specified month range"""
    pattern = f"{PARQUET_DIR}/year=2026/month={start_month}/*.parquet"
    
    if int(end_month) > int(start_month):
        pattern2 = f"{PARQUET_DIR}/year=2026/month={end_month}/*.parquet"
        result = con.execute(f"""
            SELECT category, SUM(amount) AS total
            FROM (
                SELECT * FROM read_parquet('{pattern}')
                UNION ALL
                SELECT * FROM read_parquet('{pattern2}')
            )
            GROUP BY category
            ORDER BY total DESC
        """).fetchdf()
    else:
        result = con.execute(f"""
            SELECT category, SUM(amount) AS total
            FROM read_parquet('{pattern}')
            GROUP BY category
            ORDER BY total DESC
        """).fetchdf()
    
    return result

# ── Step 3: Generate daily reports ──
def daily_report():
    report = query_by_date_range(
        (datetime.now() - __import__('datetime').timedelta(days=7)).strftime('%m'),
        datetime.now().strftime('%m')
    )
    report.to_csv(f"/tmp/daily_report_{datetime.now().strftime('%Y%m%d')}.csv",
                  index=False, encoding='utf-8-sig')
    print("📊 Daily report generated")
    return report

report = daily_report()
print(report.to_string(index=False))

Performance Comparison with Traditional Tools

When Should You Use Parquet?

✅ Recommended for Parquet

• Data exceeds 100MB and requires repeated querying
• Queries access only a subset of columns (maximizes columnar advantage)
• Frequent filtering and aggregation operations
• Needs cross-day/cross-month incremental processing
• Storage cost sensitive (much smaller after compression)

❌ Stick with CSV

• Very small data (< 10MB)—conversion overhead isn’t worth it
• One-time read with no persistence needed
• Data needs manual editing
• Data exchange with other systems (CSV has better compatibility)

💰 Monetization Advice

Mastering the DuckDB + Parquet combination opens several commercial opportunities:

1. Data Pipeline Service: Build automated CSV → Parquet pipelines for SMEs at ¥500-2000/month per client.
2. Performance Consulting: Help teams migrate from pandas to DuckDB + Parquet, charging ¥3000-10000 per project.
3. SaaS Data Product Backend: Use Parquet as data storage to build multi-tenant analytics platforms with usage-based pricing.
4. Automated Reporting Tool: Combine cron + DuckDB + Parquet for daily auto-reporting services at ¥200-500/month per merchant.

Action Item: Today, find a CSV file on your machine (preferably 100MB+), convert it to Parquet with DuckDB, and compare query speeds. After feeling the performance improvement firsthand, consider how to productize it.

Summary

Migrating from CSV to Parquet is one of the most impactful performance optimizations you can make in a DuckDB production environment. Key takeaways:

1. One-command conversion: COPY table TO 'file.parquet' (FORMAT PARQUET)
2. Partitioned storage: Use PARTITION_BY for efficient partition pruning
3. Flexible compression: Choose between Snappy or ZSTD based on your scenario
4. Production pipeline: Combine with cron for automated incremental conversion

Remember: CSV is for humans to read; Parquet is for machines to process. Let each format do what it does best.

📖 More DuckDB tutorials at duckdblab.org 💡 Want to systematically learn how to build commercializable data products with DuckDB? Check out our complete tutorial series