DuckDB Parquet Performance Guide: The Secret to 10x Faster Queries

Have you ever encountered these scenarios?

• A 2GB CSV file takes 30 seconds to read with pandas, memory full
• Excel crashes when opening, data is too large
• Processing the same large files every week, waiting half a day each time

The solution is Parquet — a columnar storage format designed for analytical queries.

DuckDB has native support for Parquet, reading, writing, and querying 10x faster than CSV.

This tutorial dives deep into Parquet’s performance principles, practical techniques, and monetization value.

1. Why is Parquet 10x Faster than CSV?

1.1 Core Difference: Row Store vs Column Store

CSV is a row store — you must read the entire row to find the columns you need.

Parquet is a column store — it only reads the columns you need, skipping everything else.

Analogy:

• CSV = You bought a newspaper, want to find info about a city, must read the entire paper
• Parquet = You subscribed to independent city magazines, just grab the one you need

For a table with 100 columns, if you only need 3 columns, Parquet can be 30x faster.

1.2 Three Advantages of Columnar Storage

1.3 Compression Algorithm Comparison

DuckDB supports multiple compression algorithms — choose based on your scenario:

Recommendation: Use ZSTD — best balance between compression ratio and read speed.

2. Practical Guide 1: CSV to Parquet Conversion

2.1 Basic Conversion Flow

import duckdb

# Connect to in-memory database
con = duckdb.connect(":memory:")

# Read data from CSV (automatic type inference)
con.execute("""
    CREATE TABLE orders AS
    SELECT * FROM read_csv_auto('/data/sales/orders.csv')
""")

# Export to Parquet in one step
con.execute("""
    COPY orders TO '/data/sales/orders.parquet'
    (FORMAT PARQUET, COMPRESSION ZSTD)
""")

print("✅ Export completed")

2.2 Real-world Compression Results (10 million row order data)

Conclusion: ZSTD achieves 5.5x compression and 11x speed improvement.

3. Practical Guide 2: Query Parquet Files Directly

Parquet supports Predicate Pushdown — DuckDB only reads rows and columns that match your conditions.

# Only read needed columns, only read matching data
result = con.execute("""
    SELECT customer_id, SUM(amount) AS total
    FROM read_parquet('/data/sales/*.parquet')
    WHERE order_date >= '2026-07-01'
      AND region = 'East'
    GROUP BY customer_id
    ORDER BY total DESC
    LIMIT 100
""").fetchdf()

print(result)

3.1 Key Advantages

• ✅ No need to load entire file into memory
• ✅ Automatically skips unnecessary rows and columns
• ✅ Supports wildcard to read multiple files
• ✅ Predicate pushdown: WHERE conditions filter during read

3.2 Performance Comparison Test

import time

# Test 1: Read 10 million rows
print("=== Read Performance Comparison ===")

# CSV method
start = time.time()
con.execute("SELECT * FROM read_csv_auto('/data/orders.csv')")
csv_time = time.time() - start
print(f"CSV read: {csv_time:.2f} seconds")

# Parquet method
start = time.time()
con.execute("SELECT * FROM read_parquet('/data/orders.parquet')")
parquet_time = time.time() - start
print(f"Parquet read: {parquet_time:.2f} seconds")
print(f"Speedup: {csv_time/parquet_time:.1f}x")

# Test 2: Read specific columns only
print("\n=== Column Selection Performance ===")

# CSV method (must read entire row)
start = time.time()
con.execute("SELECT customer_id, amount FROM read_csv_auto('/data/orders.csv')")
csv_col_time = time.time() - start
print(f"CSV column select: {csv_col_time:.2f} seconds")

# Parquet method (only read needed columns)
start = time.time()
con.execute("SELECT customer_id, amount FROM read_parquet('/data/orders.parquet')")
parquet_col_time = time.time() - start
print(f"Parquet column select: {parquet_col_time:.2f} seconds")
print(f"Speedup: {csv_col_time/parquet_col_time:.1f}x")

Typical Results:

• Full read: Parquet is 10-15x faster than CSV
• Single column query: Parquet is 30-50x faster than CSV

4. Practical Guide 3: Partitioned Parquet Files

For big data analysis, the best practice is partitioned storage.

4.1 Time-based Partitioning

-- Write with year and month partitioning
COPY orders TO '/data/sales/parquet_partitioned/'
(
    FORMAT PARQUET,
    PARTITION_BY (order_date YEAR, order_date MONTH)
);

Generated directory structure:

/data/sales/parquet_partitioned/
├── order_date_year=2026/
│   ├── order_date_month=01/
│   │   ├── part-0.parquet
│   │   └── part-1.parquet
│   ├── order_date_month=02/
│   │   └── part-0.parquet
│   └── ...
└── order_date_year=2025/
    └── ...

4.2 Automatic Partition Pruning

DuckDB automatically skips unrelated partitions during queries:

-- Only scan July 2026 data, other partitions automatically skipped
SELECT *
FROM read_parquet('/data/sales/parquet_partitioned/')
WHERE order_date >= '2026-07-01'
  AND order_date < '2026-08-01';

Performance gain: 12 months of data, read only 1 month, ~12x speedup.

5. Practical Guide 4: Python Integration Workflow

5.1 Complete Data Pipeline

import duckdb
import pandas as pd
from pathlib import Path

class DataPipeline:
    """Data pipeline: CSV input → Parquet processing → Analysis output"""
    
    def __init__(self, input_dir, output_dir):
        self.con = duckdb.connect(":memory:")
        self.input_dir = Path(input_dir)
        self.output_dir = Path(output_dir)
        self.output_dir.mkdir(parents=True, exist_ok=True)
    
    def ingest_csv(self, filename):
        """Import CSV and convert to Parquet"""
        csv_path = self.input_dir / filename
        table_name = Path(filename).stem
        
        # Read CSV
        self.con.execute(f"""
            CREATE TABLE {table_name} AS
            SELECT * FROM read_csv_auto('{csv_path}')
        """)
        
        # Export Parquet
        parquet_path = self.output_dir / f"{table_name}.parquet"
        self.con.execute(f"""
            COPY {table_name} TO '{parquet_path}'
            (FORMAT PARQUET, COMPRESSION ZSTD)
        """)
        
        print(f"✅ {filename} → {parquet_path}")
        return parquet_path
    
    def query_parquet(self, sql):
        """Query Parquet files"""
        return self.con.execute(sql).fetchdf()
    
    def export_results(self, df, filename):
        """Export results"""
        output_path = self.output_dir / filename
        df.to_csv(output_path, index=False)
        print(f"✅ Results saved to {output_path}")
        return output_path

# Usage example
pipeline = DataPipeline('/data/input', '/data/output')

# Import data
pipeline.ingest_csv('orders.csv')
pipeline.ingest_csv('customers.csv')

# Analytical query
result = pipeline.query_parquet("""
    SELECT 
        c.customer_name,
        COUNT(o.order_id) AS order_count,
        ROUND(SUM(o.amount), 2) AS total_amount
    FROM read_parquet('/data/output/customers.parquet') c
    JOIN read_parquet('/data/output/orders.parquet') o
        ON c.customer_id = o.customer_id
    GROUP BY c.customer_name
    ORDER BY total_amount DESC
    LIMIT 10
""")

# Export results
pipeline.export_results(result, 'top_customers.csv')

6. Comparison with Traditional Tools

DuckDB Advantages:

• Single-machine Spark equivalent, no distributed cluster needed
• Memory usage is only 1/10 of Pandas
• Query speed 10-100x faster than Pandas
• Works out of the box, no Hadoop/Spark configuration needed

7. Monetization Strategies

7.1 Low-Cost Approach (¥0-5,000 startup)

Data Cleaning Service

• Help enterprises convert historical CSV data to Parquet format
• Optimize storage costs, improve query speed
• Pricing: ¥500-2,000/project

Automated Reporting System

• Build daily reporting pipeline with Parquet + DuckDB
• Replace Excel + manual processing
• Pricing: ¥3,000-10,000/set

7.2 Medium-Cost Approach (¥5,000-50,000 startup)

Data Product Subscription

• Build industry datasets (e-commerce, finance, retail)
• Update Parquet format data weekly/monthly
• Pricing: ¥99-999/month subscription

Enterprise Data Lake Solution

• Help enterprises build Parquet-based data lakes
• Integrate DuckDB query engine
• Pricing: ¥20,000-100,000/project

7.3 High-Cost Approach (¥50,000+ startup)

SaaS Analytics Platform

• Build online analytics platform based on Parquet + DuckDB
• Support multi-tenant, permission control
• Pricing: ¥999-9,999/month

8. Summary

Parquet is the ultimate format for data analytics:

1. Columnar Storage: Only read needed columns, 10x faster
2. High Compression: ZSTD compression 5-10x smaller than CSV
3. Predicate Pushdown: Automatically skip unrelated data during queries
4. Partition Pruning: Partition by time/region for faster queries
5. Native Support: DuckDB has built-in Parquet reader, no extra dependencies

Best Practice Workflow:

1. Collect raw data in CSV
2. Import to DuckDB and convert to Parquet for storage
3. Query Parquet directly for analysis
4. Export results to CSV or Excel for business users

Next time you face big data processing, stop struggling with CSV — Parquet is the right solution.

📖 Complete tutorial with runnable examples published at duckdblab.org

🔍 Want to systematically learn DuckDB performance optimization? duckdblab.org has a complete tutorial series from beginner to commercialization, covering query optimization, data product architecture, and automated deployment.