Querying Parquet Files with DuckDB

A Quick Tour of the DBeaver SQL Editor

With DBeaver open and your connection to finance.duckdb active, take a look at the interface.

1. Database Navigator (Left Pane): You should see your connection listed here. You can expand it to see the contents of your DuckDB database file. Right now, it's likely empty, but we'll change that soon.
2. SQL Editor (Main Pane): To start querying, you need an editor. Right-click on your database connection and select "SQL Editor" -> "New SQL Script". This opens a new tab where you can write and execute SQL.

Let's run a quick test query to ensure everything is working. Type the following into the editor and press Ctrl+Enter (or click the orange "Execute SQL Statement" play button in the toolbar):

SELECT 'Hello from DBeaver!';

You should see the result appear in a new panel at the bottom. This confirms you're ready to interact with DuckDB.

The Power of Querying Files Directly

One of DuckDB's most powerful features is its ability to query data files directly as if they were database tables. This is a significant departure from traditional database workflows where you must first LOAD or IMPORT data into the database. With DuckDB, you can run complex SQL queries on Parquet, CSV, or other files that live on your local disk.

Why is this so transformative for your work?

• Speed: You can analyze a new data file the moment it arrives without a time-consuming ingestion step.
• Flexibility: You can join data from multiple sources—a Parquet file, a CSV, and an existing database table—all in a single query.
• Performance: DuckDB employs highly optimized techniques to make this fast. It doesn't just naively read the entire file.

Let's see this in action.

Querying and Inspecting Your Parquet Files

Imagine you have the Parquet files you created in Module 3, stored in your project's data/processed/ directory. You can query them directly from the DBeaver SQL editor.

1. Querying a Single File

To query a single Parquet file, you simply use its path in the FROM clause of your SELECT statement. Remember to use forward slashes / for paths, even on Windows. It's best to use the full, absolute path to avoid ambiguity.

-- Replace with the absolute path to one of your Parquet files
SELECT *
FROM 'C:/Users/YourName/Documents/finance_project/data/processed/2023_actuals.parquet'
LIMIT 10;

Run this query. DBeaver will execute it via DuckDB, which reads the Parquet file and returns the first 10 rows.

2. Inspecting a File's Schema

Before running a full query, you might want to know what columns and data types are in the file. You can do this with the DESCRIBE command.

-- Replace with the absolute path to your file
DESCRIBE SELECT *
FROM 'C:/Users/YourName/Documents/finance_project/data/processed/2023_actuals.parquet';

This returns a table showing the column names, types, and other metadata, without loading any of the actual data. It's an invaluable tool for quick exploration.

The following video provides a concise demonstration of using DuckDB to query multiple files and inspect their structure.

Working with Multiple Files

In FP&A, you often deal with data split across multiple files, for instance, monthly transaction exports. DuckDB excels at consolidating these on the fly using "globbing," where wildcards * match patterns in filenames.

This is a central concept for your automation goals, replacing the slow pandas loops you wanted to move away from. The DuckDB documentation provides excellent examples.

Let's apply this. If your data/processed folder contains several Parquet files (e.g., actuals_2022.parquet, actuals_2023.parquet), you can query them all as a single table:

-- Note the wildcard '*' in the path
SELECT *
FROM 'C:/Users/YourName/Documents/finance_project/data/processed/*.parquet';

To see which file each row came from, you can select the special filename column:

SELECT
    filename,
    "Date",       -- Assuming you have these columns
    "Department",
    "Amount"
FROM 'C:/Users/YourName/Documents/finance_project/data/processed/*.parquet'
WHERE "Amount" > 100000;

This single query reads multiple files, unions them together, adds the source filename, and filters the results. This is a dramatic improvement in efficiency and simplicity over looping through files with pandas.

Creating a Stable Interface with Views

While querying files directly is fantastic for exploration, typing out long file paths repeatedly is cumbersome. For data you'll access often, you can create a VIEW. A VIEW is a stored query that you can interact with just like a table. It doesn't store any data itself; it simply provides a named shortcut to your underlying file query.

Now, let's create a view in DBeaver for all your "actuals" data. This command will save the view definition inside your finance.duckdb file.

CREATE OR REPLACE VIEW all_actuals AS
SELECT *
FROM 'C:/Users/YourName/Documents/finance_project/data/processed/*.parquet';

After running this, go back to the "Database Navigator" on the left, right-click your connection, and hit "Refresh". You should now see a "Views" folder, and inside it, your new all_actuals view.

From this point on, you can query your Parquet files with a much simpler command:

SELECT
    "Department",
    SUM("Amount") AS total_amount
FROM all_actuals
WHERE "Date" >= '2023-01-01'
GROUP BY "Department"
ORDER BY total_amount DESC;

This combines the best of both worlds: your data remains in efficient Parquet files, but you can query it with the simplicity of a standard database table.

Conclusion

In this lesson, you've learned how to turn DBeaver into a powerful, interactive console for your file-based financial data. You are no longer tethered to writing and running Python scripts for every analytical question. You can now explore, validate, and analyze your Parquet datasets directly with the full power of SQL.

Key Takeaways:

• DBeaver's SQL editor, connected to DuckDB, allows you to query local Parquet files directly.
• You can query a single file by its path or multiple files at once using glob patterns (*).
• The DESCRIBE command lets you inspect a file's schema without loading any data.
• Including the filename column in your SELECT statement helps trace data back to its source file.
• Creating a VIEW provides a permanent, table-like name for a query on one or more files, simplifying future analysis.

You have now mastered the exploratory side of the workflow. In our next lesson, we will focus on how to leverage DBeaver as a development environment to build and test the complex SQL queries you'll eventually embed in your automated Python scripts.