dbt partitions bigquery table on wrong field

Viewed 509

Trying to add data build tool to our ecosystem and faced with titled problem. We are currently using huge BigQuery tables so we want them to be partitioned and extended every day. IDK if that matters, but everything is running in docker container. Here's the way you can reproduce it:

bigquery sql query to create source table:

create table `***.dbt_nick_test.partition_test_20210304` (
    session_date DATE,
    user_id STRING
);

insert into `***.dbt_nick_test.partition_test_20210304` (session_date, user_id)
values ('2021-03-04', '1234'), ('2021-03-04', NULL), ('2021-03-04', '1235');

dbt_project.yml - models defenition part:

models:
        ***:
                test:
                        +schema: test

profiles.yml - just in case to be sure everything is configured ok:

***-bq:
    target: "{{ env_var('DBT_TARGET', 'dev') }}"
    outputs:
        dev:
            type: bigquery
            method: service-account
            project: ***
            dataset: dbt_nick_test
            threads: 4
            keyfile: /root/.dbt/bq-creds.json
            timeout_seconds: 300
            priority: interactive
            retries: 1

cat models/test/test.sql:

{{
config(
    partition_by={
        "field": "session_date",
        "data_type": "date",
        "granularity": "day"
    },
    partitions=dbt.partition_range(var('dates', default=yesterday())),
    verbose=True
)
}}

SELECT
  session_date,
  user_id
FROM `***`.`dbt_nick_test`.`{{ date_sharded_table('partition_test_') }}`

yesterday macro is default from dbt tutorial.

After running dbt -dS run -m test --vars 'dates: "20210304, 20210304"' (everything is going OK) dbt reports that table is created successfully. Now, going to BigQuery I can see that the table is actually created, but it has bad "partition by" field -- _PARTITIONTIME instead of "session_date" screenshot.

If I manually create correctly partitioned table and then run dbt run -- it will work as expected, everything is perfect.

Also, tables created from this table using dbt are also badly partitioned.

0 Answers
Related