Writing Data to Parquet with DuckDB and Pandas

Method 1: Writing from a Pandas DataFrame

Given your experience with pandas, the most direct way to create a Parquet file is from a DataFrame you've already prepared. This is a common pattern when you've performed some initial data manipulation in Python and want to save the result. The pandas library makes this exceptionally simple with the to_parquet() method.

Let's imagine you've processed some financial data into a DataFrame. The code to save it as a Parquet file is just a single line.

import pandas as pd

# Sample DataFrame representing financial data
data = {
    'transaction_date': pd.to_datetime(['2024-01-15', '2024-01-16', '2024-02-10', '2024-02-12']),
    'cost_center': ['CC101', 'CC102', 'CC101', 'CC103'],
    'amount': [1500.50, 750.00, 2500.00, 1200.75],
    'scenario': ['Actuals', 'Actuals', 'Actuals', 'Actuals']
}
df = pd.DataFrame(data)

# Writing the DataFrame to a Parquet file is a single method call
df.to_parquet('actuals_pandas.parquet', index=False)

print("DataFrame successfully written to 'actuals_pandas.parquet'")

In this example, df.to_parquet('actuals_pandas.parquet', index=False) takes the in-memory DataFrame df and writes it to a file named actuals_pandas.parquet. We set index=False because we typically don't need to store the DataFrame's row index in the output file.

While this method is simple, it has a significant limitation that becomes apparent with larger datasets. The following resource explains this drawback clearly.

As you've just read, the pandas approach can fail if the dataset is larger than your available RAM. This is because it is not a streaming process; the entire data object must exist in memory. This is precisely the kind of bottleneck you're looking to eliminate in your automation goals.

Method 2: Writing Directly from DuckDB

For larger datasets or when your transformations are already being performed in SQL, DuckDB provides a much more memory-efficient and scalable solution. Instead of pulling results into a Python DataFrame first, you can instruct DuckDB to write the output of a query directly to a Parquet file using the COPY command.

This approach is powerful because it combines the transformation and writing steps into a single, optimized operation within the DuckDB engine. It can stream data from the source, process it, and write it to the destination file without ever needing to hold the entire dataset in your computer's RAM.

The syntax is straightforward and can be used to export an entire table or, more flexibly, the result of any SELECT query.

Let's build on what you've learned about querying files. We'll create a dummy CSV of raw data, run a transformation query on it in DuckDB, and save the result directly to Parquet. This pattern is central to building efficient ETL (Extract, Transform, Load) pipelines.

import duckdb
import os

# Create a dummy CSV file for this example
csv_content = """Date,Account,Value
2024-05-01,Revenue,50000
2024-05-01,COGS,20000
2024-05-02,Revenue,55000
2024-05-02,COGS,22000
"""
with open('monthly_data.csv', 'w') as f:
    f.write(csv_content)

# Connect to an in-memory DuckDB database
con = duckdb.connect()

# Define a query that transforms the data (e.g., calculating Gross Profit)
# This could be any complex financial calculation you need.
transformation_query = """
SELECT
    Date,
    SUM(CASE WHEN Account = 'Revenue' THEN Value ELSE 0 END) -
    SUM(CASE WHEN Account = 'COGS' THEN Value ELSE 0 END) AS GrossProfit
FROM 'monthly_data.csv'
GROUP BY Date
"""

# Use the COPY command to write the result of the query to a Parquet file
copy_command = f"""
COPY ({transformation_query}) TO 'daily_gross_profit.parquet' (FORMAT PARQUET);
"""
con.execute(copy_command)

print("Query result successfully written to 'daily_gross_profit.parquet'")

# We can quickly verify the content by reading it back with DuckDB
result_df = con.execute("FROM 'daily_gross_profit.parquet'").df()
print("\nContent of the created Parquet file:")
print(result_df)

# Clean up the created files
con.close()
os.remove('monthly_data.csv')
os.remove('daily_gross_profit.parquet')
os.remove('actuals_pandas.parquet')

The video below provides a visual demonstration of using DuckDB for this task and highlights its efficiency compared to the pandas method.

Practical Considerations

When writing Parquet files, you have some control over their internal structure, which can influence performance and file size.

• Compression (CODEC): Parquet files are always compressed. DuckDB defaults to the Snappy codec, which provides a great balance between fast compression/decompression speeds and good compression ratios. For situations where minimizing file size is the top priority, you can specify ZSTD (CODEC 'ZSTD'), which offers better compression at the cost of slightly more CPU time.
• Row Group Size: As you saw in the previous lesson, Parquet files are divided into "row groups." You can suggest a size for these chunks (e.g., ROW_GROUP_SIZE 100000). Larger row groups can lead to better compression and are more efficient for sequential scans, but they may require more memory to write. The default settings are generally a good starting point.

Here is a summary of when to use each method:

• Use pandas.to_parquet(): This is a good choice for smaller datasets that comfortably fit in memory, especially if the data is already in a DataFrame as a result of other Python-based processing steps. It's quick, easy, and requires no SQL.
• Use DuckDB's COPY ... TO: This is the superior method for larger-than-memory datasets or when you are performing transformations on data from files (CSVs, other Parquet files, etc.). By keeping the entire operation within the DuckDB engine, you maximize performance and minimize memory usage.

Conclusion

You've now learned the two primary methods for creating Parquet files in a Python environment. This practical skill is the bridge between processing your raw financial data and storing it in an optimal format for analysis.

Key Takeaways:

• You can write a pandas DataFrame to a Parquet file using the simple df.to_parquet() method.
• This pandas method is convenient but limited by system memory, as it requires the entire DataFrame to be loaded at once.
• DuckDB's COPY (SELECT ...) TO 'file.parquet' command is a more powerful and scalable alternative.
• The COPY command allows you to transform data and write the result to Parquet in a single, memory-efficient operation, making it ideal for building robust data pipelines.

In our next lesson, we will complete the circle by learning how to efficiently read and query the Parquet files you now know how to create. We'll explore how DuckDB can analyze a single Parquet file or even a whole directory of them at once—a common requirement when working with financial data partitioned by time periods. We'll also see how you can use DBeaver to visually explore the contents of your newly created Parquet files.