Query Development with DBeaver for Python

The DBeaver-First Development Workflow

The most significant bottleneck when embedding SQL in code is the slow feedback loop: you edit the SQL string in your Python script, run the script, check the output, find an error, and repeat. DBeaver breaks this cycle by providing an interactive environment to perfect your logic first.

The workflow looks like this:

1. Formulate an Analytical Question: Start with a business need, e.g., "What is the month-over-month variance in spending by department?"
2. Iterate in DBeaver: Open the SQL editor and build your query step-by-step. Start simple, run it, check the results, add another layer of logic (like a JOIN or a window function), run it again, and so on.
3. Finalize the Query: Once the query produces the exact output you need, it's considered "production-ready."
4. Hand-off to Python: Copy the finalized, tested SQL query and paste it into a multi-line string in your Python script for automation.

The image below shows a complex query being edited in DBeaver—this is your development workbench.

The ultimate goal is to place this perfected query into your Python code, as illustrated in the next image.

To become proficient with this workflow, you first need to be comfortable with the tools DBeaver's editor provides. The following video gives a great tour.

Practical Example: Developing a Month-over-Month Variance Report

Let's apply this iterative workflow to a common FP&A task: calculating the month-over-month (MoM) variance in spending for each department. We'll use the all_actuals view you created in the last lesson.

Step 1: Get Monthly Aggregates

First, we need to aggregate our transactional data into monthly totals for each department.

In a new DBeaver SQL editor tab, write and run this query:

-- Step 1: Aggregate data to the monthly level
SELECT
    strftime("Date", '%Y-%m') AS transaction_month,
    "Department",
    SUM("Amount") AS total_amount
FROM all_actuals
GROUP BY 1, 2 -- Group by the first and second columns
ORDER BY "Department", transaction_month;

This query uses the strftime function to format the Date column into a 'YYYY-MM' string, allowing us to group by month. After running this, you should see a table of monthly totals per department, sorted chronologically within each department.

Step 2: Access the Previous Month's Data with LAG()

Now for the core of the MoM calculation. We need to get the previous month's total_amount onto the same row as the current month's amount. This is a perfect use case for a window function.

You mentioned wanting to replace pandas operations like .shift() with more powerful SQL. The LAG() window function does exactly that. It "looks back" a specified number of rows within an ordered partition of data.

Let's modify the query. We can't use LAG() on SUM("Amount") directly in the same step as the GROUP BY. We need to first calculate the sums, and then apply the window function. The most readable way to stage this multi-step logic is with a Common Table Expression (CTE).

-- Step 2: Use a CTE and the LAG() window function
WITH monthly_totals AS (
    SELECT
        strftime("Date", '%Y-%m') AS transaction_month,
        "Department",
        SUM("Amount") AS total_amount
    FROM all_actuals
    GROUP BY 1, 2
)
SELECT
    transaction_month,
    "Department",
    total_amount,
    LAG(total_amount, 1, 0) OVER (PARTITION BY "Department" ORDER BY transaction_month) AS previous_month_amount
FROM monthly_totals
ORDER BY "Department", transaction_month;

Breakdown of the new LAG() part:

• WITH monthly_totals AS (...): This defines our CTE. It's a temporary, named result set that we can query.
• LAG(total_amount, 1, 0): This function fetches the value of total_amount from the row that is 1 position before the current row. If no such row exists (i.e., for the first month), it returns a default value of 0.
• OVER (...): This is the clause that defines the "window" for the function.
• PARTITION BY "Department": This is crucial. It tells LAG() to operate on rows within each department independently. The look-back "resets" for every new department.
• ORDER BY transaction_month: Within each department partition, the rows are ordered by month. This is essential for LAG() to correctly identify the "previous" month.

Run this query. You should now see a fourth column, previous_month_amount, containing the value from the prior month for that same department.

Step 3: Calculate the Final Variance

The final step is straightforward. We just need to subtract the previous month's amount from the current one. We can do this by nesting our CTEs or, more simply, adding the calculation to the final SELECT statement.

-- Step 3: Calculate the variance using the LAG result
WITH monthly_totals AS (
    SELECT
        strftime("Date", '%Y-%m') AS transaction_month,
        "Department",
        SUM("Amount") AS total_amount
    FROM all_actuals
    GROUP BY 1, 2
)
SELECT
    transaction_month,
    "Department",
    total_amount,
    LAG(total_amount, 1, 0) OVER (PARTITION BY "Department" ORDER BY transaction_month) AS previous_month_amount,
    (total_amount - LAG(total_amount, 1, 0) OVER (PARTITION BY "Department" ORDER BY transaction_month)) AS mom_variance
FROM monthly_totals
ORDER BY "Department", transaction_month;

Now you have a complete, reusable query for MoM variance analysis, developed interactively.

The concepts of CTEs and window functions are fundamental to modern analytical SQL. The following summary from the DuckDB in Action book provides an excellent overview of these advanced features.

The Hand-off to Python

You've built and validated your query. The final step is to transfer this logic into your automation script. You simply copy the SQL code and assign it to a multi-line string variable in Python.

import duckdb




# The complete, tested query from DBeaver
mom_variance_query = """
WITH monthly_totals AS (
    SELECT
        strftime("Date", '%Y-%m') AS transaction_month,
        "Department",
        SUM("Amount") AS total_amount
    FROM all_actuals
    GROUP BY 1, 2
)
SELECT
    transaction_month,
    "Department",
    total_amount,
    LAG(total_amount, 1, 0) OVER (PARTITION BY "Department" ORDER BY transaction_month) AS previous_month_amount,
    (total_amount - LAG(total_amount, 1, 0) OVER (PARTITION BY "Department" ORDER BY transaction_month)) AS mom_variance
FROM monthly_totals
ORDER BY "Department", transaction_month;
"""




# Now, execute this query in your Python script
# We'll cover this integration in more detail in Module 6
try:
    con = duckdb.connect(database='finance.duckdb', read_only=True)
    report_df = con.execute(mom_variance_query).fetch_df()
    con.close()
    
    print("Successfully generated MoM variance report:")
    print(report_df.head())

except Exception as e:
    print(f"An error occurred: {e}")

This snippet demonstrates the clean separation of concerns: DBeaver is for SQL development, and Python is for execution and orchestration.

Conclusion

In this lesson, you've established a powerful workflow for developing robust analytical logic. By using DBeaver as your SQL workbench, you can iterate quickly, leverage advanced features like CTEs and window functions to solve real-world financial analysis problems, and confidently transfer tested queries into your automation scripts.

Key Takeaways:

• DBeaver's SQL editor is a powerful Integrated Development Environment (IDE) for SQL.
• The "DBeaver-first" workflow involves iteratively building and testing queries before moving them to Python.
• You can execute portions of a script in DBeaver, allowing for rapid, step-by-step validation.
• Advanced SQL features like CTEs and window functions (LAG()) are essential for complex analysis and are best developed interactively.
• The final step is a simple "copy and paste" of the perfected query into a Python string.

You've now had a practical introduction to some advanced SQL concepts. In the next module, Advanced SQL for Financial Analysis, we will dive deep into these topics, exploring CTEs, LAG/LEAD, running totals, and other functions that are indispensable for FP&A.