The missing guide to debug() in dbt

In-Depth Discussions
1

This is a companion discussion topic for the original entry at The missing guide to debug() in dbt | dbt Developer Blog

2

Thanks @bper! I’ve never used debug() but am now keen to give it a try myself. There’s definitely times I would have found it useful in the past :eyes:

One question: you said in the post

if […] while in debug mode you press c, the debugger will stop at each of your breakpoints

Does this mean that a {{ debug() }} call very early on in your file would be almost impossible to trigger? I can understand how enough time would pass to trigger the breakpoint if you’re trying to capture something that happens in an on-run-end hook, but what about if you were debugging something in on-run-start?

Or are you also allowed to press c while dbt is doing the initial parse work etc, which means that you don’t need the quickest draw in the west to make it work?

3
4

Great question @joellabes

c stands for continue, e.g. continue from the current breakpoint until the next one (or until the end of the program).

So you don’t need to type c to trigger the first breakpoint, it will happen automatically (and you wont’t need the quickest draw in the west). If you have multiple breakpoints though, after you enter the first one you might want to type c to execute all the code until the next one.

1 Like
5

Hi how are u, i’m currently working in a use case in which i want to use the VALIDATE function in snowflake to valide COPY INTO statements, i’m creating an incremental model to load into a table the result of this function but i need a query id from the last copy made in snowflake, to obtain that i created the next macro and i put all the {{ debug() }} macros inside tu reach the point in which this macro doesn’t return the expected result:

{% macro log_error_but_validate() %}
{{ debug() }}
{% set query_id_validacion %}
SELECT QUERY_ID FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE(WAREHOUSE_NAME => ‘WH_LOADER’)) WHERE DATABASE_NAME LIKE ‘XXX’ AND SCHEMA_NAME LIKE ‘YYY’ AND QUERY_TYPE LIKE ‘COPY’ ORDER BY START_TIME DESC LIMIT 1
{% endset %}
{{ debug() }}
{% set results = run_query(query_id_validacion) %}
{{ debug() }}
{% if execute %}
{{ debug() }}
{% for row in results.rows %}
{{ debug() }}
{% set retval = row[1] %}
{{ debug() }}
{{ retval }}
{{ debug() }}
{% endfor %}

{% endif %}

{% endmacro %}

The problem seems to be in the query itself: {% set query_id_validacion %}

    SELECT QUERY_ID FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE(WAREHOUSE_NAME => 'WH_LOADER')) WHERE DATABASE_NAME LIKE 'XXX' AND SCHEMA_NAME LIKE 'YYY' AND QUERY_TYPE LIKE 'COPY' ORDER BY START_TIME DESC LIMIT 1
{% endset %}

When i debug in the next point i obtain the result and seems to be ok:

ipdb> pp l_1_query_id_validacion
('\n'
 '        SELECT QUERY_ID FROM '
 'TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_WAREHOUSE(WAREHOUSE_NAME => '
 "'WH_LOADER')) WHERE DATABASE_NAME LIKE 'XXX' AND SCHEMA_NAME LIKE "
 "'YYY' AND QUERY_TYPE LIKE 'COPY' ORDER BY START_TIME DESC LIMIT 1\n"
 '    ')

But when i tried to manually change the variable as was explained in this posts i got the following error message,

ipdb> l_1_query_id_validacion = '\n select query_id from table(information_schema.query_history_by_warehouse(warehouse_name => ‘WH_LOADER_BUT’)) where database_name like ‘RAW_DEV’ and schema_na
me like ‘BDOT’ and query_type like ‘COPY’ order by start_time desc limit 1 \n ’
*** SyntaxError: invalid syntax. Perhaps you forgot a comma?

I think the problem actually is with the ‘=>’, any thoughts on how to achieve this? thanks