dbt post hook relation "my_table" does not exist

user_8226607 0
Viewed 1672

I am building some models using dbt.

I have a model so -

SELECT
  COALESCE(
    col1, col2
  ) AS col,
  ....
FROM
  {{ source(
    'db',
    'tbl'
  ) }}
  WHERE ....

This model has a config section calling a macro

{{- config(
  post_hook = [macro()],
  materialized='table'
) -}}

Within the macro I use {% if execute %} and I also log to check the execute value {{ log('Calling update macro with exec value = ' ~ execute) }}

When I run dbt compile I do not expect the macro to fire according to the documentation. However, it does and actually sets the execute to true triggering the update and causing on error as the table doesn't exist. Am I missing something or is this a dbt bug? I am confused!

Here's the line from the logs - 2021-09-15 20:48:16.864555 (Thread-1): Calling update macro with exec value = True

.. and the error is relation "schema.my_table" does not exist

Appreciate any pointers someone might have, thanks

dbt
1 Answers

Ok, so here's what I found out about dbt.

When you dbt compile or dbt run the first time, the tables do not exist in the database yet. However, both compile and run will check the db objects exist and throw an error otherwise. So, my select within the macro failed irrespective of me using {% if execute %}

I called the adapter.get_relation() to check if the table exists -

{%- set source_relation = adapter.get_relation(
        database=this.database ,
        schema=this.schema,
        identifier=this.name) -%}

and used the check condition -

{% set table_exists=source_relation is not none  %}

For an incremental run, the fix was easier -

{% if execute and is_incremental() %}

Now, my code is fixed :)

user_82266070
Related