Encountering Error When Running 'INSERT' Statement as a Pre-Hook in dbt

Help
1

Hi!
I’m trying to create a new pre-hook in dbt that inserts some data into a specific table every time it runs. When I define the pre-hook using the dbt_project.yml file, the model fails. However, when I define the pre-hook directly in the model config section, it runs successfully. When I checked the SQL that is causing the failure using “snowflake.account_usage.QUERY_HISTORY”, it appears that dbt is generating this string as a query and attempting to run it as a model | column | data_type | | ----------------------- | --------- | | number of rows inserted | Number | .
I suspect that the issue is related to the fact that the macro I’m using as a pre-hook executes an INSERT statement. However, I’m not sure why it works correctly when I call it from inside the model config section, but fails when I try to call it from the dbt_project.yml file.

The error message - SQL compilation error: syntax error line 1 at position 0 unexpected ‘|’.

2

can you post ur macro code and code how you are calling the pre-hook from dbt_project.yml

3

This is how I call this macro from the dbt_project.yml:

models:
  aaa:
    bbb:
      ccc:
        schema: tttt
        +unique_key: 'ID'
        +successor_unique_key: 'ID'
        +union_condition_key: 'a'
        +successor_union_condition_key: 'b'
        +pre-hook: '{{ add_row_count_record_for_enriched_positions_table(run_on_raw=True, run_on_canonic=True) }}'
        +post-hook: '{{ add_row_count_record_for_enriched_positions_table(run_on_enriched=True) }}'
4

Here’s the relevant macro that I’m using for the pre-hook (it works correctly when I call it from the model directly):

{% macro add_row_count_record_for_enriched_positions_table(database_name='', schema_name='', table_name='', run_on_enriched=False, run_on_canonic=False, run_on_raw=False, where_clause='') %}
    {% if execute %}
        {% set this_database_name =  database_name.upper() %}
        {% if this_database_name ==  '' %}
            {% set this_database_name =  this.database.upper() %}
        {% endif %}
        {% set this_schema_name =  schema_name.upper() %}
        {% if this_schema_name ==  '' %}
            {% set this_schema_name =  this.schema.upper() %}
        {% endif %}
        {% set this_table_name =  table_name.upper() %}
        {% if this_table_name ==  '' %}
            {% set this_table_name =  this.table.upper() %}
        {% endif %}
        {% set this_where_clause =  where_clause %}

        {% if run_on_enriched == True %}
            {{ add_rows_count_record_of_this_table(database_name=this_database_name, schema_name=this_schema_name, table_name=this_table_name) }}
        {% endif %}

        {% if run_on_raw == True %}
            {% set this_raw_schema_name = this_schema_name | replace("ENRICHED", "RAW") %}
            {{ add_rows_count_record_of_this_table(database_name=this_database_name, schema_name=this_raw_schema_name, table_name=this_table_name) }}
        {% endif %}

        {% if run_on_canonic == True %}
            {% set this_canonic_schema_name = this_schema_name | replace("ENRICHED", "CANONIC") %}
            {{ add_rows_count_record_of_this_table(database_name=this_database_name, schema_name=this_canonic_schema_name, table_name=this_table_name) }}
        {% endif %}
        {{ execution_log("Monitoring system - finished successfully") }}
    {% endif %}
{% endmacro %}

{% macro add_rows_count_record_of_this_table(database_name, schema_name, table_name, where_clause = '') %}
    {% set this_database_name =  database_name %}
    {% set this_schema_name =  schema_name %}
    {% set this_table_name =  table_name %}
    {% set this_where_clause =  where_clause %}
    {% set rows_number = get_table_rows_count(this_database_name, this_schema_name, this_table_name) %}
    {{ execute_the_insereration_of_rows_count_record(this_database_name, this_schema_name, this_table_name, rows_number, this_where_clause) }}
    {{ execution_log("Monitoring system - row count record added successfully for: " + this_database_name + "." + this_schema_name + "." + this_table_name) }}
{% endmacro %}

{% macro execute_the_insereration_of_rows_count_record(database_name, schema_name, table_name, rows_number, where_clause = '') %}
    {% set this_database_name =  database_name %}
    {% set this_schema_name =  schema_name %}
    {% set this_table_name =  table_name %}
    {% set this_where_clause =  where_clause %}
    {% set rows_number = rows_number %}
    {% set monitoring_db = var('monitoring_db') %}
    {% set snapshots_schema = var('snapshots_schema') %}
    {% set rows_count_table = 'ROWS_COUNT' %}
    {% set insert_rows_count_record_query %}
        INSERT INTO {{monitoring_db}}.{{snapshots_schema}}.{{rows_count_table}} (DATABASE_NAME, SCHEMA_NAME, TABLE_NAME, TIMESTAMP, ROW_COUNT)
        VALUES ('{{this_database_name}}', '{{this_schema_name}}', '{{this_table_name}}', CURRENT_TIMESTAMP, {{rows_number}});
    {% endset %}
    {{ execution_log("Monitoring system - generate new record of rows count for - " + this_table_name) }}
    {{ execution_log("Monitoring system - number of rows counted - " + rows_number|string) }}
    {{ run_query(insert_rows_count_record_query) }}
{% endmacro %}

{% macro get_table_rows_count(database_name, schema_name, table_name, where_clause = '') %}
    {% set this_database_name =  database_name %}
    {% set this_schema_name =  schema_name %}
    {% set this_table_name =  table_name %}
    {% set this_where_clause =  where_clause %}
    {% set count_rows_query %}
        SELECT COALESCE(COUNT(*), 0) FROM {{this_database_name}}.{{this_schema_name}}.{{this_table_name}}
        {{ this_where_clause }};
    {% endset %}
    {% set rows_number = run_query(count_rows_query).rows[0][0] %}
    {{ return(rows_number) }}
{% endmacro %}