Data-based: Going Beyond the Dataframe

Querying Files Directly from Disk

DuckDB can query files like CSVs and Parquet directly from disk, streaming the data instead of loading it all into memory. This works well with datasets that don't fit in RAM.

Copy code
# Save to CSV for demonstration
df.to_csv('sales_data.csv', index=False)

# Query CSV directly without loading into pandas
result = duckdb.sql("""
    SELECT 
        region,
        COUNT(DISTINCT customer_id) as unique_customers,
        SUM(amount) as total_revenue
    FROM 'sales_data.csv' -- Query the file path
    GROUP BY region
    ORDER BY total_revenue DESC
""").df()

# Note: File was streamed from disk, never fully loaded into memory

DuckDB supports wildcards, so if you have a folder full of log files or monthly sales reports, you can query them all at once:

Copy code
SELECT * FROM 'sales_data_*.csv'

This saves you from writing tedious Python loops to read and concatenate files.

The Pandas Approach

To analyze this data with Pandas, we first load the entire dataset into memory.

Copy code
# Load into pandas from DuckDB
df = con.sql("SELECT * FROM transactions").df()

# Load time: 0.54 seconds
# Memory usage: ~1035 MB (due to Pandas' in-memory object overhead)

Now, let's run three queries: a complex group-by, a top-N aggregation, and a moving average calculation. These operations take around 1 second in total, including the initial load, and consume over 1 GB of RAM.

The DuckDB Approach

With DuckDB, we don't load the data. We query the 115 MB file on disk directly.

Copy code
# Query 1: Sales by category and region
q1_duckdb = con.sql("""
    SELECT 
        category, region,
        SUM(amount) as total_sales,
        AVG(amount) as avg_sale,
        COUNT(*) as num_transactions,
        COUNT(DISTINCT customer_id) as unique_customers
    FROM transactions
    GROUP BY category, region
""").df()

# ... (similar SQL for Query 2 and 3)

The total time for the same three queries is just 0.07 seconds, with minimal RAM usage.

The Results

DuckDB is 14x faster and uses over 90% less memory. This difference becomes even more dramatic as datasets grow.

In a test with a simulated 50-million-row dataset (by reading a Parquet file 10 times), DuckDB was over 40x faster and used 99% less memory than the Pandas equivalent, which required over 10 GB of RAM.

This isn't just about speed. DuckDB's memory efficiency lets you analyze datasets on your laptop that would be impossible with an in-memory tool like Pandas.

Step 1: Heavy Lifting with DuckDB SQL

We'll use DuckDB for what it does best: fast, complex aggregations over large datasets. We can write a single SQL query to generate a rich set of features for each customer, including recency, frequency, and monetary value.

Copy code
# This single query engineers 20+ features from 1M raw transactions
features = duckdb.sql("""
    WITH customer_stats AS (
        SELECT
            customer_id,
            MAX(transaction_date) AS last_purchase_date,
            DATE '2024-11-28' - MAX(transaction_date) AS days_since_last_purchase,
            COUNT(*) AS total_transactions,
            SUM(amount) AS total_spend,
            AVG(amount) AS avg_transaction_value,
            -- ... and many more aggregations
        FROM customer_transactions
        GROUP BY customer_id
    )
    SELECT
        *,
        -- ... additional calculations on the aggregated data
    FROM customer_stats
""").df()

# Engineered 20 features for 50,000 customers
# Time: 0.05s

In just 50 milliseconds, we've crunched 1 million raw transactions down to a clean, 50,000-row feature set with one row per customer.

Step 2 & 3: Preprocessing and Training with Pandas & Scikit-learn

Now that we have a smaller, manageable DataFrame (features), we can hand it off to the rich ecosystems of Pandas and Scikit-learn for the final steps.

Copy code
# Step 2: Use Pandas for final type conversions and target variable creation
features['last_purchase_date'] = pd.to_datetime(features['last_purchase_date'])
# ... create the 'will_purchase_next_30d' target variable

# Step 3: Use Scikit-learn to train a Random Forest model
from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import train_test_split

X = features[feature_columns]
y = features['will_purchase_next_30d']
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2)

model = RandomForestClassifier(n_estimators=100, max_depth=10, n_jobs=-1)
model.fit(X_train, y_train)

The Power of the Hybrid Approach

The entire pipeline, from generating 1 million rows of data to training a highly accurate model, takes less than a second.

We used DuckDB for the heavy data crunching, then passed a smaller, aggregated DataFrame to Pandas and Scikit-learn for their specialized APIs. This approach lets you iterate faster and, as developer advocate Jacob likes to say, "be wrong faster," which is critical for effective data science.

What is the difference between DuckDB and Polars?

Polars is another high-performance DataFrame library with its own powerful expression API. Like DuckDB, Polars is vectorized and very fast. The key difference is philosophy: DuckDB is an embedded analytical database that brings a powerful SQL engine to your local files and DataFrames. Polars is a Rust-powered DataFrame library designed to be a faster, more parallel alternative to the Pandas API. DuckDB also has mature out-of-core capabilities, which allow it to process datasets larger than your available RAM by spilling to disk. Many choose DuckDB for its robust SQL capabilities and out-of-core processing, while Polars is excellent for those who prefer a DataFrame-style API.

How does MotherDuck handle scaling for large datasets?

MotherDuck scales both vertically and horizontally. Vertically, you can choose from different instance sizes for your workload, up to massive machines with over a terabyte of RAM. Horizontally, you can run multiple, isolated jobs in parallel using service accounts, all sharing the same underlying storage.

What are the data size limits for DuckDB?

Locally, the main limiting factor for DuckDB isn't RAM, it's your available disk space. We've seen users process hundreds of billions of rows on a single node. RAM primarily affects speed, while the disk handles the scale.