The problem I’m having - I am working on a macro where users input a query, and the macro executes it in BigQuery. However, when users input an incorrect query, such as a syntax error or a table not found error, the macro terminates with a “Database error” message. To identify and resolve the issue, we currently need to check the BigQuery prompt separately.
I attempted to address this issue by implementing a try and exception block, but it hasn’t proven effective. Specifically, when a list of queries is passed, and any one of them encounters an issue, the subsequent queries don’t run, and there’s no mechanism to handle these exceptions.
In summary, the current setup lacks the ability to handle errors effectively, especially when multiple queries are involved.
The context of why I’m trying to do this - I am working on creating a macro designed to automate the testing process for all tables under regression testing. The goal is to streamline the testing workflow by allowing users to execute the macro, which in turn automates the testing for various tables. This process aims to enhance efficiency and accuracy in regression testing across multiple tables.
What I’ve already tried -In our efforts to enhance error handling in the macro, we tried implementing a Try/Catch mechanism in Jinja format, inspired by Python. Unfortunately, this approach did not resolve the issues. Previous attempts to use try and exception blocks also fell short, with the macro still terminating on encountering errors like syntax issues or missing tables. We are currently exploring alternative solutions to improve error handling and overall robustness.
Some example code or error messages
{% macro run_queries(queries_list) %}
{% for query in queries_list %}
{% set query_name = query.name %}
{% set query_sql = query.sql %}
{% try %}
{{ run_query(query_sql) }}
{% except dbt.exceptions.RuntimeException, e %}
{{ log("Error executing query '{{ query_name }}': " ~ e) }}
{% continue %}
{% endtry %}
{% endfor %}
{% endmacro %}