DuckDB Materialized Views: Stop Recalculating the Same Data

Have you ever encountered this scenario:

A table with tens of millions of rows, and every time you query “monthly sales summary” it takes十几 seconds. You’ve tried layered queries, CTEs, subqueries—it’s still slow.

The feature I’m about to show you can reduce query time from 10 seconds to 0.1 seconds.

What Are Materialized Views

A regular view (VIEW) just stores a SQL statement. Every time you query it, the underlying SQL executes again.

A materialized view (MATERIALIZED VIEW) actually stores the query results on disk. When you query it, it reads the pre-computed file instead of recalculating.

Simple analogy:

• Regular view = Cooking from scratch every time
• Materialized view = Meal prep stored in the fridge, just reheat when needed

Basic Syntax: Three Steps

Step 1: Create Sample Data

-- Create a mock orders table (1 million rows)
CREATE TABLE orders AS
SELECT 
    (DATE '2024-01-01' + INTERVAL (random() * 1000) DAY)::DATE AS order_date,
    CASE random() * 5
        WHEN 0 THEN 'electronics'
        WHEN 1 THEN 'clothing'
        WHEN 2 THEN 'food'
        WHEN 3 THEN 'books'
        ELSE 'other'
    END AS category,
    ROUND(random() * 500 + 10, 2) AS amount,
    floor(random() * 10000) + 1 AS user_id
FROM generate_series(1, 1000000);

Step 2: Create Materialized View

-- Create monthly sales summary view
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT 
    date_trunc('month', order_date) AS month,
    category,
    SUM(amount) AS total_sales,
    COUNT(*) AS order_count,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY 1, 2;

Step 3: Query the Materialized View

-- Returns in milliseconds
SELECT * FROM mv_monthly_sales 
ORDER BY month DESC, total_sales DESC;

Refresh Strategies: What When Data Changes

Manual Refresh (Recommended for Scheduled Tasks)

-- Refresh once daily at midnight
REFRESH MATERIALIZED VIEW mv_monthly_sales;

Truncate and Rebuild (Suitable for Small Datasets)

-- Clear and recalculate
TRUNCATE mv_monthly_sales;
INSERT INTO mv_monthly_sales
SELECT 
    date_trunc('month', order_date) AS month,
    category,
    SUM(amount) AS total_sales,
    COUNT(*) AS order_count,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY 1, 2;

Incremental Refresh (For Large Datasets)

-- Only process new data
INSERT INTO mv_monthly_sales
SELECT 
    date_trunc('month', o.order_date) AS month,
    o.category,
    SUM(o.amount) AS total_sales,
    COUNT(*) AS order_count,
    AVG(o.amount) AS avg_order_value
FROM orders o
WHERE o.order_date > (SELECT MAX(month) FROM mv_monthly_sales)
GROUP BY 1, 2;

Materialized Views vs CTEs vs Temporary Tables

One-liner summary: If query results will be reused repeatedly, use materialized views.

Practical Example: Build Your First Dashboard

Suppose you want to build a “User Activity Dashboard” with:

1. Daily active users
2. New user retention rate
3. Channel conversion funnel

-- Step 1: Create basic materialized view
CREATE MATERIALIZED VIEW mv_daily_active AS
SELECT 
    event_date,
    COUNT(DISTINCT user_id) AS active_users,
    COUNT(DISTINCT CASE WHEN is_first_visit THEN user_id END) AS new_users
FROM user_events
GROUP BY event_date;

-- Step 2: Create aggregated materialized view
CREATE MATERIALIZED VIEW mv_channel_funnel AS
SELECT 
    channel,
    COUNT(DISTINCT user_id) AS visitors,
    COUNT(DISTINCT CASE WHEN action = 'purchase' THEN user_id END) AS purchasers,
    ROUND(
        COUNT(DISTINCT CASE WHEN action = 'purchase' THEN user_id END) * 100.0 / 
        COUNT(DISTINCT user_id), 2
    ) AS conversion_rate
FROM user_events
GROUP BY channel;

-- Step 3: Query the dashboard
SELECT * FROM mv_daily_active ORDER BY event_date DESC LIMIT 30;
SELECT * FROM mv_channel_funnel ORDER BY conversion_rate DESC;

Refresh schedule recommendations:

• Daily at 2 AM: REFRESH MATERIALIZED VIEW mv_daily_active;
• Weekly on Sunday at 3 AM: REFRESH MATERIALIZED VIEW mv_channel_funnel;

Python Integration: Automate Your Views

import duckdb
import schedule
import time

# Connect to DuckDB
conn = duckdb.connect("dashboard.db")

# Create materialized view
conn.execute("""
    CREATE MATERIALIZED VIEW IF NOT EXISTS mv_sales_summary AS
    SELECT 
        date_trunc('month', order_date) AS month,
        category,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY 1, 2
""")

# Refresh function
def refresh_views():
    conn.execute("REFRESH MATERIALIZED VIEW mv_sales_summary")
    print(f"{time.strftime('%Y-%m-%d %H:%M')} - Refresh completed")

# Scheduled task
schedule.every().day.at("02:00").do(refresh_views)

while True:
    schedule.run_pending()
    time.sleep(60)

Performance Comparison: How Much Faster

Comparison with Traditional BI Tools

Monetization Suggestions

1. Data Product: Monthly Subscription Analytics Dashboard

Scenario: Provide monthly sales analysis reports for small e-commerce businesses

• Tech stack: DuckDB materialized views + FastAPI + Streamlit
• Pricing: 99 RMB/month
• Target customers: E-commerce sellers with annual revenue under 5 million RMB
• Market size: 30 million+ small/medium enterprises in China, even 1% adoption is a 3 million market

2. Automated Reporting Service: Save Companies Manual Statistics

Scenario: Weekly automated business report generation

• Tech stack: Python scripts + DuckDB + cron
• Pricing: 299 RMB/time or 999 RMB/month
• Target customers: Traditional enterprises without data analysis teams
• Core value: Transform bosses from “waiting for reports” to “receiving reports”

3. Data Consulting: Build Data Systems for Clients

Scenario: Custom data dashboard development for enterprises

• Tech stack: DuckDB materialized views + visualization frontend
• Pricing: 5,000-20,000 RMB/project
• Target customers: Enterprises with data but no analysis capability
• Recurrence model: Follow-up maintenance + feature expansion

4. Online Course: Teach Others to Use DuckDB

Scenario: Create DuckDB materialized view tutorial series

• Platforms: YouTube, Bilibili, knowledge community
• Pricing: Free lead generation + paid courses 199-499 RMB
• Target audience: Data analysts, developers, entrepreneurs
• Marginal cost: Near zero

Best Practices

1. Don’t overuse: Only materialize aggregation results that are queried frequently
2. Set refresh strategy: Choose refresh timing based on data update frequency
3. Monitor storage: Materialized views use disk space, clean up unused views regularly
4. Combine with indexes: Creating indexes on materialized views can further accelerate queries
5. Automate refresh: Use cron or Python schedule library for automatic refresh

Summary

Materialized views are one of the most cost-effective performance optimization tools in DuckDB:

• Simple principle: Pre-compute and store for later use
• Low barrier to entry: CREATE once, query countless times
• Significant performance boost: From seconds to milliseconds
• Low maintenance cost: Just refresh on schedule

Remember this mantra: Frequent queries + Stable results = Materialized views.

If your report queries are always slow, try materialized views first—it might solve the problem permanently.

Next time you face the pain of repeated calculations, ask yourself: Will this result change? If not, create a materialized view for it.

📖 Full project code + automated refresh scripts → duckdblab.org