# Error: dbt incremental model on BQ Partitioned table using string field

**URL:** <https://discourse.getdbt.com/t/error-dbt-incremental-model-on-bq-partitioned-table-using-string-field/14826>\
**Category:** Help\
**Tags:** jinja, incremental, bigquery, dbt-core\
**Created:** [August 12, 2024, 11:08am UTC](https://discourse.getdbt.com/t/error-dbt-incremental-model-on-bq-partitioned-table-using-string-field/14826 "2024-08-12T11:08:23Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![madm4niac](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@madm4niac](https://discourse.getdbt.com/u/madm4niac)\
**Post date:** [August 12, 2024, 11:08am UTC](https://discourse.getdbt.com/t/error-dbt-incremental-model-on-bq-partitioned-table-using-string-field/14826/1 "2024-08-12T11:08:23Z")

</div>

## The problem I’m having

Unable to replace records in **pub\_table** with records from **int\_table** based on a string column

```auto
Database Error in model pub_table(models/project/dataset/published/pub_table.sql)
Query error: Function not found: string_trunc

```

## The context of why I’m trying to do this

For every Airflow DAG run, I would like to replace the records in the **pub\_table** with the records from the **int\_table** if there is a match with the field **\_meta\_source\_file**.

For example,  
If the **pub\_table** has 100 records and **int\_table** has 120 records for **\_meta\_source\_file = “abc”** , then during the DAG run, the **pub\_table** 100 records should be replaced with **int\_table** 120 records.

Basically, **delete** 100 records from **pub\_table** and **insert** 120 records from the **int\_table**.

## What I’ve already tried

I understand Big Query only allows int, date and timestamp fields in the partition by. Is there any other way to address my requirement.

## Some example code or error messages

pub\_table script -

```auto
{{
    config(
        materialized="incremental",
        incremental_strategy='insert_overwrite',
        on_schema_change="fail",
        persist_docs={"relation": true, "columns": true},
        partition_by={"field": "_meta_source_file", "data_type": "STRING"},
        cluster_by=["_meta_use_case","transaction_date"],
    )
}}

{% set airflow_info = fromjson(var('airflow_info', '{}')) %}
{% set max_meta_inserted_at = get_max_meta_inserted_at(ref("int_table"), airflow_info) %}

select *
    from {{ ref("int_table") }} as int_model
    {% if is_incremental() %}
        where int_model._meta_inserted_at = {{ max_meta_inserted_at }}
    {% else %}  
        where int_model._meta_inserted_at is not null
    {% endif %}

```

---

<div class="post-metadata">

**Author:** ![stephen](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@stephen](https://discourse.getdbt.com/u/stephen)\
**Post date:** [August 12, 2024, 12:46pm UTC](https://discourse.getdbt.com/t/error-dbt-incremental-model-on-bq-partitioned-table-using-string-field/14826/2 "2024-08-12T12:46:17Z")

</div>

Hello Again …  
So for this problem the quickest fix would be to introduce another column `_meta_source_file_hash` in your model and use the [BigQuery FARM\_FINGERPRINT](https://cloud.google.com/bigquery/docs/reference/standard-sql/hash_functions#farm_fingerprint) function to give it the hashed value of the `_meta_source_file` column.  
Since the new column `_meta_source_file_hash` will be of type int64, you should now be able to easily partition the table on it. Just make sure to include it in your where clauses to ensure partition pruning takes effect.

---

<div class="post-metadata">

**Author:** ![madm4niac](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@madm4niac](https://discourse.getdbt.com/u/madm4niac)\
**Post date:** [August 12, 2024, 12:59pm UTC](https://discourse.getdbt.com/t/error-dbt-incremental-model-on-bq-partitioned-table-using-string-field/14826/3 "2024-08-12T12:59:17Z")

</div>

> [@stephen](#):
>
> Just make sure to include it in your where clauses to ensure partition pruning takes effect.

Hi 🙂

I’m sorry. I didn’t get this part, “Just make sure to include it in your where clauses to ensure partition pruning takes effect.” Can you modify the pub\_table script to explain the above line.

Thanks.

---

<div class="post-metadata">

**Author:** ![stephen](https://avatars.discourse-cdn.com/v4/letter/s/96bed5/32.png) [@stephen](https://discourse.getdbt.com/u/stephen)\
**Post date:** [August 12, 2024, 1:48pm UTC](https://discourse.getdbt.com/t/error-dbt-incremental-model-on-bq-partitioned-table-using-string-field/14826/4 "2024-08-12T13:48:05Z")

</div>

Ok so I had a look at the context of your problem (my old post literally just answered the title - how to partition on a String column).

Now I don’t think you really want the `insert_overwrite` option.  
Consider the following case:-

Imagine you use the strategy I mentioned in the previous post and manage to partition based on the `_meta_source_file_hash`.  
Now in BigQuery for integer partitions [we assign a range of values](https://cloud.google.com/bigquery/docs/partitioned-tables#integer_range) for each partition.

So its totally possible the hashes say of two files `abc` and `def` map to the same partition.  
Next in your `dbt run` if you get data only for file `abc`, all the data in partition which where data from files `abc` and `def` were residing will be cleared and re-populated with new which only has rows for `abc`.

_Note this is assuming your `int_table` file gets cleared regularly and doesn’t at all times contain all the data saved to `pub`._

Honestly I am unsure how to achieve this using just one model (replacing some records but not based on unique key) ☹ . The only way that comes to mind is to use a dbt pre-hook to delete the data linked to source files present in the `int_table` and then let the `pub_model` ingest all the data coming from `int_table`. (_note there I think should be better ways of achieving this_)

* * *

On your previous question how to add the hash:-

```auto
select *, farm_fingerprint(_meta_source_file) as _meta_source_file_hash
    from {{ ref("int_table") }} as int_model
    {% if is_incremental() %}
        where int_model._meta_inserted_at = {{ max_meta_inserted_at }}
    {% else %}  
        where int_model._meta_inserted_at is not null
    {% endif %}
```

---

<div class="post-metadata">

**Author:** ![madm4niac](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@madm4niac](https://discourse.getdbt.com/u/madm4niac)\
**Post date:** [August 13, 2024, 12:15pm UTC](https://discourse.getdbt.com/t/error-dbt-incremental-model-on-bq-partitioned-table-using-string-field/14826/5 "2024-08-13T12:15:55Z")

</div>

Hi Stephen, you are being very helpful. Thanks a lot for that first. I developed on your idea - Use **prehook** to delete the records and the insert new records using incremental strategy.

This is what I came up with, and the macro is not working as intended, I don’t know where I’m going wrong. Can you you help me on this one?

Failing macro (checks table’s presence and truncates the table if present) -

```auto
{% macro delete_existing_records(table_name, ref_table_name, max_meta_inserted_at) %}
    {% set table_exists_query %}
    SELECT COUNT(*) 
    FROM `{{ table_name.project }}.{{ table_name.dataset }}. __TABLES__ `
    WHERE table_id = '{{ table_name.name }}' -- returns 1 for pub_table
    {% endset %}
    
    {% set table_exists = dbt_utils.get_single_value(table_exists_query) %}
    
    {% if table_exists | int > 0 %}
        TRUNCATE TABLE {{ table_name }}
        -- DELETE FROM {{ table_name }}
        -- WHERE _meta_source_file IS NOT NULL
        -- IN (
        -- SELECT DISTINCT _meta_source_file
        -- FROM {{ ref_table_name }}
        -- WHERE _meta_inserted_at = SAFE_CAST({{ max_meta_inserted_at }} AS timestamp)
        -- )
    {% endif %}
{% endmacro %}

```

simple macro - this works(truncates the table before incremental insertion):

```auto
{% macro delete_existing_records(table_name, ref_table_name, max_meta_inserted_at) %}
    TRUNCATE TABLE {{ table_name }}
{% endmacro %}

```

pub\_table:

```auto
{% set airflow_info = fromjson(var('airflow_info', '{}')) %}
{% set max_meta_inserted_at = get_max_meta_inserted_at(ref("int_table"), airflow_info) %}

{{
    config(
        materialized="incremental",
        on_schema_change="fail",
        persist_docs={"relation": true, "columns": true},
        cluster_by=["_meta_source_file","_meta_use_case","transaction_date"],
        pre_hook=delete_existing_records(
            this, # pub_table
            ref("int_table"),
            max_meta_inserted_at
        )
    )
}}

select *
from {{ ref("int_table") }} as int_model
{% if is_incremental() %}
    where int_model._meta_inserted_at = {{ max_meta_inserted_at }}
    # where 1<>1
{% else %}  
  where int_model._meta_inserted_at is not null
{% endif %}

```

---

<div class="post-metadata">

**Author:** ![madm4niac](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@madm4niac](https://discourse.getdbt.com/u/madm4niac)\
**Post date:** [August 14, 2024, 6:56am UTC](https://discourse.getdbt.com/t/error-dbt-incremental-model-on-bq-partitioned-table-using-string-field/14826/6 "2024-08-14T06:56:15Z")

</div>

I raised another issue for the Prehook - macro one. Adding it just for your reference and help.

> [@Pre hook - Macro - Delete statement](https://discourse.getdbt.com/t/pre-hook-macro-delete-statement/14840):
>
> The problem I’m having The pre\_hook fires the macro but the macro isn’t deleting the records from the pub\_table The context of why I’m trying to do this For every Airflow DAG run, I would like to replace the records in the pub\_table with the records from the int\_table if there is a match with the field \_meta\_source\_file. For example, If the pub\_table has 100 records and int\_table has 120 records for \_meta\_source\_file = “abc”, then during the DAG run, the pub\_table 100 records should be replac…

---

<div class="post-metadata">

**Author:** ![madm4niac](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@madm4niac](https://discourse.getdbt.com/u/madm4niac)\
**Post date:** [August 15, 2024, 11:15am UTC](https://discourse.getdbt.com/t/error-dbt-incremental-model-on-bq-partitioned-table-using-string-field/14826/7 "2024-08-15T11:15:53Z")

</div>

I used the pre\_hook and the macro as you @stephen suggested and it worked like a charm, thanks. Below is my script,

pub\_table -

```auto
{% set airflow_info = fromjson(var('airflow_info', '{}')) %}
{% set max_meta_inserted_at = get_max_meta_inserted_at(ref("int_model"), airflow_info) %}

{{
    config(
        materialized="incremental",
        on_schema_change="fail",
        persist_docs={"relation": true, "columns": true},
        cluster_by=["_meta_source_file","_meta_use_case","transaction_date"],
        pre_hook=delete_existing_records(
            this,
            ref("int_model"),
            max_meta_inserted_at
        )
    )
}}

select *
    from {{ ref("int_model") }} as int_model
    {% if is_incremental() %}
        where int_model._meta_inserted_at = {{ max_meta_inserted_at }}
        -- where 1<>1
    {% else %}  
        where int_model._meta_inserted_at is not null
    {% endif %}

```

delete\_existing\_records macro -

```auto
{% macro delete_existing_records(table_name, ref_table_name, max_meta_inserted_at) %}
    {% if execute %}
        {% set table_exists_query %}
            SELECT COUNT(*) 
            FROM `{{ table_name.project }}.{{ table_name.dataset }}. __TABLES__ `
            WHERE table_id = '{{ table_name.name }}'
        {% endset %}
            
        {% set table_exists = dbt_utils.get_single_value(table_exists_query) | int %}
        
        {{ log("Table exists value: " ~ table_exists, info=True) }}
        
        {% if table_exists > 0 %}
            {{ log("Deleting records from table: " ~ table_name, info=True) }}

            {%- call statement('delete_records', fetch_result=True) -%}
                DELETE FROM {{ table_name }} 
                WHERE <some_condition>;
            {%- endcall -%}

            {%- set delete_result = load_result('delete_records') -%}
            {{ log(delete_result['response'].rows_affected ~ ' rows deleted', info=True) }}

            -- Add this to verify the execution continues
            {{ log("Successfully executed DELETE on: " ~ table_name, info=True) }}
        {% else %}
            {{ log("Table does not exist: " ~ table_name, info=True) }}
        {% endif %}
    {% endif %}
{% endmacro %} 

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [August 22, 2024, 11:16am UTC](https://discourse.getdbt.com/t/error-dbt-incremental-model-on-bq-partitioned-table-using-string-field/14826/8 "2024-08-22T11:16:20Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.