Introduction

DuckDB, as a high-performance analytical embedded database, has rapidly risen in the data engineering landscape. Starting from v1.5.x, DuckDB has seen breakthrough upgrades in its Parquet file format processing capabilities — including partial variant shredding, Snowflake-compatible VARIANT Parquet file reading, column-level metadata loading and serialization, and a new memory-based row group write control.

These features make DuckDB far more flexible when dealing with complex nested data structures, while significantly improving Parquet read/write performance. This article walks through each advanced feature with runnable code examples.

1. Partial Variant Shredding: A New Revolution in Parquet Writing

What is Variant Shredding?

In traditional relational databases, handling nested data (like JSON) typically means storing the entire document as a single VARCHAR or JSON column. This approach is inefficient because querying a specific field requires deserializing the entire document first.

DuckDB’s VARIANT type solves this problem through a shredding mechanism — it flattens nested structures into multiple physical columns, each corresponding to a JSON path.

The Breakthrough of Partial Variant Shredding

Before v1.5.x, when VARIANT data in a Parquet file contained fields that didn’t match the predefined schema, DuckDB would fall back to storing the entire VARIANT as a single column. This meant that even if most data could be efficiently shredded, a single unmatched field would cause the entire file to degrade.

The Partial Variant Shredding introduced in v1.5.4 allows DuckDB to mix shredded columns and unshredded columns within a Parquet file:

-- Start DuckDB CLI or Python DuckDB

-- Create a table with complex nested data
CREATE TABLE products (
    product_id INTEGER,
    product_name VARCHAR,
    attributes VARIANT
);

-- Insert data with different nested structures
INSERT INTO products VALUES
(1, 'Laptop', '{"color": "black", "weight": 2.5, "specs": {"cpu": "M3", "ram": "16GB"}}'),
(2, 'Mouse', '{"color": "white", "weight": 0.15}'),
(3, 'Keyboard', '{"color": "rgb", "weight": 0.8, "layout": "TKL"}');

-- Export to Parquet
COPY products TO 'products.parquet' (FORMAT PARQUET);

-- Now query nested fields efficiently
SELECT 
    product_name,
    attributes->>'$.color' AS color,
    attributes->>'$.weight' AS weight
FROM products;

Real-World Scenario: Mixed Data Source Parquet Writing

Suppose you collect product data from multiple sources — some with complete specifications, others with only basic info:

-- Simulate product data from different APIs
CREATE TABLE raw_products AS
SELECT * FROM (
    VALUES
    ('P001', 'Monitor', '{"brand": "Dell", "size": 27, "resolution": "4K", "refresh_rate": 144}'),
    ('P002', 'Webcam', '{"brand": "Logitech", "resolution": "1080p"}'),
    ('P003', 'Headset', '{"brand": "Sony", "type": "wireless", "battery": "30h"}'),
    ('P004', 'Cable', '{}'),
    ('P005', 'Dock', '{"brand": "CalDigit", "ports": 12, "power": "96W"}')
) AS t(id, name, attrs);

-- Export to Parquet — some fields get shredded, others remain intact
COPY raw_products TO 'mixed_products.parquet' (FORMAT PARQUET);

-- Query can safely access any nested field
SELECT 
    id,
    name,
    attrs->>'$.brand' AS brand,
    attrs->>'$.size' AS size_inches,
    attrs->>'$.power' AS power_watts
FROM read_parquet('mixed_products.parquet')
WHERE attrs->>'$.brand' IS NOT NULL;

2. Snowflake-Compatible Variant Parquet Reading

Cross-Platform Data Interoperability

In enterprise environments, data often flows between multiple platforms. DuckDB v1.5.x adds native support for reading Snowflake-produced shredded VARIANT Parquet files:

-- Directly read Snowflake-exported shredded VARIANT Parquet files
SELECT 
    *,
    parse_json(variant_col) AS parsed_data
FROM read_parquet('snowflake_export.parquet');

-- Expand nested fields from VARIANT
SELECT 
    event_id,
    user_id,
    payload->>'$.action' AS action,
    payload->>'$.source' AS source,
    payload->>'$.metadata.timestamp' AS ts
FROM read_parquet('snowflake_events.parquet')
LIMIT 100;

This compatibility means you can use DuckDB to quickly explore and analyze data exported from Snowflake, without additional format conversion.

3. Column-Level Metadata: A Performance Accelerator for Parquet Read/Write

What is Column-Level Metadata?

Parquet is a columnar storage format, and its core advantage lies in predicate pushdown — by checking column statistics (min/max values, null counts, etc.) when reading a file, it can skip row groups that don’t contain the target data.

DuckDB v1.5.x introduces column-level metadata loading and serialization, which enables:

1. Faster Parquet file reading: Metadata can be pre-loaded and cached, reducing overhead per read
2. More precise row group pruning: More detailed column statistics allow skipping more unnecessary row groups
3. Cross-session metadata reuse: Serialized metadata can be shared across sessions

Practical Example: Impact of Column-Level Metadata on Query Performance

-- Enable column-level metadata cache
PRAGMA enable_metadata_cache;

-- Create a dataset with many Parquet files
CREATE TABLE sales_2024 AS
SELECT 
    generate_series(1, 1000000) AS order_id,
    date '2024-01-01' + (random() * 365)::INTEGER AS order_date,
    ('Category_' || (random() * 10)::INTEGER) AS category,
    (random() * 1000)::DECIMAL(10,2) AS amount,
    ('Customer_' || (random() * 10000)::INTEGER) AS customer_id
FROM generate_series(1, 1000000);

-- Export as Parquet, partitioned by month
COPY sales_2024 TO 'sales_2024/' (PARTITION_BY order_date, FORMAT PARQUET);

-- First query: metadata is loaded and cached
EXPLAIN ANALYZE
SELECT category, SUM(amount) 
FROM read_parquet('sales_2024/**/')
WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31'
GROUP BY category;

-- Second query: uses cached metadata, faster
EXPLAIN ANALYZE
SELECT category, AVG(amount) 
FROM read_parquet('sales_2024/**/')
WHERE order_date BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY category;

Metadata Serialization Example

-- Export column-level metadata for Parquet files
CALL export_parquet_metadata('sales_2024/', 'parquet_meta.json');

-- View metadata statistics
SELECT 
    file_path,
    column_name,
    min_value,
    max_value,
    num_nulls,
    num_rows
FROM parquet_metadata('sales_2024/**/');

4. Memory-Based Row Group Write Control

Problems with Traditional Parquet Writing

When exporting large datasets to Parquet using COPY ... TO, DuckDB defaults to deciding when to flush data based on row group count. For large datasets, this can lead to:

1. High memory peaks: Accumulating large amounts of data before flushing
2. Inconsistent row group sizes: Some row groups may be much larger than others
3. OOM risk: May trigger out-of-memory in resource-constrained environments

The write_buffer_row_group_memory_limit Setting

v1.5.x introduces the write_buffer_row_group_memory_limit configuration, allowing you to control row group flushing based on memory usage rather than row count:

-- Set memory-based row group size limit (in bytes)
-- E.g.: each row group occupies at most 64MB of memory
SET write_buffer_row_group_memory_limit = 64 * 1024 * 1024;

-- Or set to 256MB
SET write_buffer_row_group_memory_limit = 256 * 1024 * 1024;

-- View current setting
SHOW write_buffer_row_group_memory_limit;

-- Now export large datasets
COPY (SELECT * FROM large_table) TO 'output.parquet' (FORMAT PARQUET);

Performance Comparison

-- Scenario: Export 100 million rows to Parquet

-- Method 1: Default behavior (based on row count)
SET write_buffer_row_group_memory_limit = DEFAULT;
COPY (SELECT * FROM events) TO 'events_default.parquet';

-- Method 2: Memory-based limit (more controllable)
SET write_buffer_row_group_memory_limit = 128 * 1024 * 1024;  -- 128MB
COPY (SELECT * FROM events) TO 'events_memory.parquet';

-- Compare generated Parquet file sizes and row group counts
SELECT 
    file_name,
    num_row_groups,
    total_bytes,
    avg_row_group_size
FROM (
    SELECT 
        'events_default.parquet' AS file_name,
        COUNT(*) AS num_row_groups,
        SUM(row_group_size) AS total_bytes
    FROM parquet_metadata('events_default.parquet/**/')
    
    UNION ALL
    
    SELECT 
        'events_memory.parquet' AS file_name,
        COUNT(*) AS num_row_groups,
        SUM(row_group_size) AS total_bytes
    FROM parquet_metadata('events_memory.parquet/**/')
) t;

5. Geometry Column Parquet Statistics Pruning

v1.5.x also improves Parquet statistics handling for spatial data. For Parquet files containing geometry data, DuckDB can now more effectively leverage column-level statistics for row group pruning:

-- Create a table with spatial data
CREATE TABLE geo_locations (
    location_id INTEGER,
    city VARCHAR,
    coordinates GEOGRAPHY,
    population BIGINT
);

INSERT INTO geo_locations VALUES
(1, 'Beijing', ST_GEOGPOINT(116.4074, 39.9042), 21540000),
(2, 'Shanghai', ST_GEOGPOINT(121.4737, 31.2304), 24870000),
(3, 'Guangzhou', ST_GEOGPOINT(113.2644, 23.1291), 18676000),
(4, 'Shenzhen', ST_GEOGPOINT(114.0579, 22.5431), 17560000);

-- Export as Parquet (preserving geographic statistics)
COPY geo_locations TO 'geo_locations.parquet' (FORMAT PARQUET);

-- Query geographic locations within a specific area
SELECT city, population
FROM read_parquet('geo_locations.parquet')
WHERE ST_DWITHIN(coordinates, ST_GEOGPOINT(116.4, 39.9), 100000);
-- 100000 represents a 100km radius

6. Performance Comparison with Traditional Tools

7. Best Practices

1. Configure Row Group Size Appropriately

-- For small files (< 1GB), use smaller row groups
SET write_buffer_row_group_memory_limit = 32 * 1024 * 1024;

-- For medium files (1-10GB), balanced size
SET write_buffer_row_group_memory_limit = 128 * 1024 * 1024;

-- For large files (> 10GB), larger row groups for better read efficiency
SET write_buffer_row_group_memory_limit = 512 * 1024 * 1024;

2. Leverage Metadata Cache for Repeated Queries

-- Enable metadata cache
PRAGMA enable_metadata_cache;

-- After the first query, subsequent queries on the same file will be significantly faster
SELECT * FROM read_parquet('large_dataset.parquet') WHERE id > 1000000;
SELECT * FROM read_parquet('large_dataset.parquet') WHERE category = 'electronics';

3. Optimal Write Strategy for Mixed Data Sources

-- For mixed data sources with incomplete VARIANT data
-- Use IGNORE_NULLS option to prevent write failures from null values
COPY mixed_data TO 'output.parquet' (
    FORMAT PARQUET,
    PARTITION_BY date_column,
    COMPRESSION ZSTD
);

8. Monetization Suggestions

After mastering these Parquet advanced features, you can commercialize in several directions:

1. Data Engineering Consulting Services

Many small and medium enterprises need to migrate data from platforms like Snowflake and BigQuery to local DuckDB environments. You can provide cross-platform data migration services, leveraging DuckDB’s native compatibility for rapid migration, charging per project (¥10,000-50,000/project).

2. Automated Reporting SaaS Product

Build an automated daily/weekly report SaaS for e-commerce and retail industries, based on Parquet’s efficient read/write capabilities. Use partial variant shredding for flexible e-commerce product attributes, and column-level metadata to accelerate multi-source data aggregation. Monthly subscription model (¥299-999/month).

3. Data Quality Monitoring Tool

Develop a data quality monitoring platform based on DuckDB, using Parquet statistics to quickly detect data anomalies (sudden increases in null rates, numerical range shifts, etc.), providing real-time monitoring dashboards for data teams. Freemium model (free basic version, advanced ¥199/month).

4. Embedded Analytics Engine

Embed DuckDB into your web applications or desktop software, providing users with client-side data analysis capabilities. Particularly suitable for finance, healthcare, and other industries requiring local processing of sensitive data. Charge via licensing fee + technical support.

5. Technical Training Courses

Develop paid courses around DuckDB Parquet advanced features, covering everything from beginner to production-ready. Publish on platforms like Knowledge Planet or Geek Time, priced at ¥199-499 per course.

Conclusion

DuckDB v1.5.x’s multiple upgrades in Parquet processing capabilities make it an indispensable tool in data engineering. Partial variant shredding solves the mixed data source writing challenge, Snowflake-compatible reading bridges cross-platform data pipelines, column-level metadata caching significantly boosts query performance, and memory-based row group control provides more reliable guarantees for large-scale data export.

Mastering these advanced features will enable you to create greater value in data engineering, analytics, and visualization.