BigQuery date partitioning different from UI?

Archive
1

Hey folks!

I’ve just updated to dbt 0.16.0.
I want to create a partitioned table in BigQuery on a column named “date” which is of type “date”.

My partition_by parameter looks like this:

{{ config(
  ...
  partition_by={
    "field": "date",
    "data_type": "date"
  },
  ...
) }}

After running the model, the table gets created. When I head to the BigQuery UI to check the partitioning, I get that the table is partitioned using _PARTITIONTIME which I understand to be the ingestion time.

I get this from the partitioned table details:

Table type = Partitioned
Partitioned by = Day
Partitioned on field = _PARTITIONTIME 
Partition filter = Not required

If I create a table from the UI, partitioned on the date field, I get this:

Table type = Partitioned
Partitioned by = Day
Partitioned on field = date
Partition filter = Not required

which is why I expect.

Any idea why dbt doesn’t get the same output than the UI? I’m wondering if the field name “date” somehow conflicts with something.

Another weird thing is that in the dbt generated table, _PARTITIONTIME seems to be equal to the date field, even if the date and ingestion times are different.

I’d really like to be able to partition my table by the date field directly from my dbt model. The simple workaround would be to create the table in the BQ UI and then use it in dbt but it’s not ideal.

Thanks for your help!

2

Hey @timmy - is this an incremental model or a table model? I’m really surprised by the behavior you’re seeing here. What does the rest of your config() function look like for this model?

3

Hey @drew!

Thanks for solving the issue haha.

My config looked like this:

{{ config(
  materialized='table',
  partitions=dbt.partition_range(var('dates', default=today())),
  partition_by={
    "field": "date",
    "data_type": "date"
  },
  verbose=True
) }}

It looks like the partitions parameter conflicts with the partition_by one. Removing the partitions parameter fixes the problem.

Thanks a lot!

I’m trying different setups based on this new information :slight_smile:

4

Ok, looks like this is the setup I was after:

{{ config(
  materialized='incremental',
  partition_by={
    "field": "date",
    "data_type": "date"
  },
  incremental_strategy = 'insert_overwrite',
  partitions=dbt.partition_range(var('dates', default=today())),
  verbose=True
) }}

I doing things really wrong …
This correctly sets the partition and doesn’t cost more then necessary when updating the table :+1:

Thanks!