# BigQuery date partitioning different from UI?

**URL:** <https://discourse.getdbt.com/t/bigquery-date-partitioning-different-from-ui/1054>\
**Category:** Archive\
**Created:** [April 15, 2020, 3:42pm UTC](https://discourse.getdbt.com/t/bigquery-date-partitioning-different-from-ui/1054 "2020-04-15T15:42:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![timmy](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/timmy/32/376_2.png) [@timmy](https://discourse.getdbt.com/u/timmy)\
**Post date:** [April 15, 2020, 3:42pm UTC](https://discourse.getdbt.com/t/bigquery-date-partitioning-different-from-ui/1054/1 "2020-04-15T15:42:00Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![drew](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/drew/32/9_2.png) [@drew](https://discourse.getdbt.com/u/drew)\
**Post date:** [April 17, 2020, 1:58pm UTC](https://discourse.getdbt.com/t/bigquery-date-partitioning-different-from-ui/1054/2 "2020-04-17T13:58:45Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![timmy](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/timmy/32/376_2.png) [@timmy](https://discourse.getdbt.com/u/timmy)\
**Post date:** [April 17, 2020, 8:14pm UTC](https://discourse.getdbt.com/t/bigquery-date-partitioning-different-from-ui/1054/3 "2020-04-17T20:14:49Z")

</div>

Hey @drew!

Thanks for solving the issue haha.

My config looked like this:

```auto
{{ 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 🙂

---

<div class="post-metadata">

**Author:** ![timmy](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/timmy/32/376_2.png) [@timmy](https://discourse.getdbt.com/u/timmy)\
**Post date:** [April 17, 2020, 9:34pm UTC](https://discourse.getdbt.com/t/bigquery-date-partitioning-different-from-ui/1054/4 "2020-04-17T21:34:46Z")

</div>

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

```auto
{{ 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 👍

Thanks!