# Advice needed around BigQuery partitioned tables

**URL:** <https://discourse.getdbt.com/t/advice-needed-around-bigquery-partitioned-tables/10353>\
**Category:** Help\
**Tags:** bigquery, dbt-cloud\
**Created:** [October 16, 2023, 2:06pm UTC](https://discourse.getdbt.com/t/advice-needed-around-bigquery-partitioned-tables/10353 "2023-10-16T14:06:02Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Pheonix](https://avatars.discourse-cdn.com/v4/letter/p/b9bd4f/32.png) [@Pheonix](https://discourse.getdbt.com/u/Pheonix)\
**Post date:** [October 16, 2023, 2:06pm UTC](https://discourse.getdbt.com/t/advice-needed-around-bigquery-partitioned-tables/10353/1 "2023-10-16T14:06:02Z")

</div>

Hi,

I’m attempting to create a partitioned table within BigQuery. My model has the below parameters set:

```auto
{{
     config(
    materialized = "table",
    tag=['cust_model'],
    partition_by = {
      "field": "partition_date",
      "data_type": "date",
      "granularity": "day"
    },
    require_partition_filter = true
  )
}}

```

‘partition\_date’ is a date field in the format %Y-%m-%d. See below query excerpt (min\_start\_date is a string in yyyyMMdd format E.G 20230724):

```auto
CAST(CONCAT(SUBSTR(min_start_date,0,4),'-',SUBSTR(min_start_date,5,2),'-',SUBSTR(min_start_date,7,2)) AS DATE) AS partition_date

```

When I run the model containing a different partition\_date, the existing partition is removed. What I would like is for there to be a second partition with the new date, E.G:

Partition\_1 = 2023\_07\_24  
Partition\_2 = 2023\_07\_31  
…

I understand there are incremental\_models, however each new partition will contain a new set of data, so I thought a normal table materialization would be better.

Any help is greatly appreciated.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [October 16, 2023, 2:16pm UTC](https://discourse.getdbt.com/t/advice-needed-around-bigquery-partitioned-tables/10353/2 "2023-10-16T14:16:11Z")

</div>

The substr code is unnecessary - there’s a built-in BigQuery function `parse_date` that does what you want like `parse_date("%Y%m%d", "20230115")`

When you set the model to use `materialized = "table"`, this will use a `CREATE OR REPLACE TABLE &lt;name&gt; AS ...` statement. The instruction `materialized = "table"` is synonymous with “completely destroy and recreate my table on each dbt command”. If that isn’t the behaviour you want, you should use a different materialization, probably “incremental” with the “insert\_overwrite” strategy. You can refer to the docs for that [https://docs.getdbt.com/docs/build/incremental-models|here](https://docs.getdbt.com/docs/build/incremental-models%7Chere) and [https://docs.getdbt.com/reference/resource-configs/bigquery-configs#merge-behavior-incremental-models|here](https://docs.getdbt.com/reference/resource-configs/bigquery-configs#merge-behavior-incremental-models%7Chere).

Note: `@Mike Stanley` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1697465762084069?thread_ts=1697465186.755249&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![Pheonix](https://avatars.discourse-cdn.com/v4/letter/p/b9bd4f/32.png) [@Pheonix](https://discourse.getdbt.com/u/Pheonix)\
**Post date:** [October 16, 2023, 2:18pm UTC](https://discourse.getdbt.com/t/advice-needed-around-bigquery-partitioned-tables/10353/3 "2023-10-16T14:18:07Z")

</div>

Thank you, I will explore the incremental model with the insert overwrite strategy. I also appreciate the note on date formatting.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [October 23, 2023, 2:18pm UTC](https://discourse.getdbt.com/t/advice-needed-around-bigquery-partitioned-tables/10353/4 "2023-10-23T14:18:25Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.