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.