Need Help with Partition Elimination in BigQuery for dbt Relationship Test

Help
,
1

The problem I’m having

I am encountering a Database Error in my relationship test when running dbt test. The error message indicates that BigQuery requires a filter on the partitioned column transaction_created_at for partition elimination in the stg_transactions table.

The context of why I’m trying to do this

I have a mrt_transactions model in my dbt project that needs to validate relationships with the stg_transactions model. The transaction_id field in mrt_transactions should have a corresponding transaction_id in stg_transactions. Both tables are partitioned by the transaction_created_at column.

What I’ve already tried

  1. Added a where clause in the unique test configuration to filter by transaction_created_at.
  2. Added a where clause in the relationships test configuration to filter by transaction_created_at.
  3. Verified that both configurations match the partition column requirements.

Despite these adjustments, the error persists, indicating a need for partition elimination in the stg_transactions table.

Some example code or error messages

Here’s the relevant portion of my models.yml file:

version: 2

models:
  - name: mrt_transactions
    description: Transaction Mart layer that brings together information from different domains.
    columns:
      - name: transaction_id
        description: Unique identifier for the transaction
        tests:
          - unique:
              config:
                where: "TIMESTAMP_TRUNC(transaction_created_at, DAY) > TIMESTAMP('2022-01-01')"
          - relationships:
              to: ref('stg_transactions')
              field: transaction_id
              config:
                where: "TIMESTAMP_TRUNC(transaction_created_at, DAY) > TIMESTAMP('2022-01-01')"  # Filter for partition elimination
      - name: user_id
        description: Unique identifier for the user associated with the transaction
      - name: billing_amount
        description: The amount billed in the transaction
      - name: transaction_created_at
        description: Timestamp when the transaction was created
      - name: user_created_at_date
        description: Timestamp when the user was created

Error message from dbt test:

Database Error in test relationships_mrt_transactions_transaction_id__transaction_id__ref_stg_transactions_ (models/marts/mrt_transactions.yml)
  Cannot query over table 'fizz-prod-analytics.dbt_lubbiali.stg_transactions' without a filter over column(s) 'transaction_created_at' that can be used for partition elimination
  compiled Code at target/run/fizz/models/marts/mrt_transactions.yml/relationships_mrt_transactions_589cbd7d0de0121c473cf069145e36f8.sql

Request for Assistance

Could someone help me understand how to correctly apply the partition filter in the relationship test to ensure partition elimination and avoid this error? Any insights or examples would be greatly appreciated!

2

Hi. I wasn’t able to find any core support for this, because, in my understanding, where is only applied to the model, not the reference in the to. However, there is a way around it - you can squeeze in the WHERE condition with the reference. It’s a bit hackish, but it will compile correctly and it works. :sweat_smile:

- relationships:
    to: "{{ ref('stg_transactions') }} WHERE TIMESTAMP_TRUNC(transaction_created_at, DAY) > TIMESTAMP('2022-01-01')"
    field: transaction_id
    config:
       where: "TIMESTAMP_TRUNC(transaction_created_at, DAY) > TIMESTAMP('2022-01-01')"  # Filter for partition elimination
1 Like