In this blog post I am going to walk through about a personal thought that came up in my mind while working in a specific use case.

Topic: partitioning combined with clustering in BigQuery using dbt insert_ovwerwite strategy. Why can not we partition based on a cluster column instead of all the table?

Parittioning

A partitioned table is divided into segments, called partitions. You partition tables by specifying a partition column which is used to segment the table.

The partioin column can be:

Clustering

Clustered tables in BigQuery are tables that have a user-defined column sort order using clustered columns. The clustered column is a user-defined table property that sorts storage blocks based on the values in the clustered columns.

Queries that filter or aggregate by the clustered columns only scan the relevant blocks based on the clustered columns instead of the entire table or table partition.

Use case

Partitioning and clusters can be combined together to achive finely grained sorting of the table itself.

Coming back to my previous sentence “Queries that filter or aggregate by the clustered columns only scan the relevant blocks based on the clustered columns instead of the entire table or table partition.”

So in a combined approach using both table partitioning and clustering, you first segment table data into partitions, then you cluster the data within each partition by the clustering columns.

When you work with tool like dbt and you are leveraging the different type of incremental models in order to have better performance and costs using the insert_overwrite strategy in which a merge statement that replaces entire partitions in the destination table will be generated. I have thought that could be interesting and usefull to change the partitions of only a specific cluster column not of the whole tables.

What I mean: Let’s immagine that we have the following table containing daily statics data based on Italian’s regions of the number of people that are getting married.

So the table will look like:

Initial table
Initial table

The table it is partitioned by marriage day and using only a cluster column based on the region. It can be rendered as per follow.

Clustered & Partitioned table
Clustered & Partitioned table

Imagine that from my ingestion tool I receive every day the last 3 months data with this scheduling:

  • 10AM data from Lombardia region

  • 12 AM data from Marche region

  • 3 PM data from Puglia region

Now if I use the dbt insert_overwrite strategy it means that all the time when I run my dbt model it is going to overwrite the last 3 months data indipendently from the region that implies that the load of 3PM will be replace the data from Marche and Lombardia region.

That it will result:

insert_overwrite srtrategy
insert_overwrite srtrategy
final table
final table


What I think that could be a nice to have: the possibility to remove only the partition for a specific cluster column.

What do you think about it?

Conclusion

In order to overcome the problem I split the models in different one based on the region so I can safely all the time replace the partition. Then aggregate all of this model in one using the UNION ALL statement in the final model with materialization view. Why? In order to benefit still from clustering and partitioning and having control on costs without materialize all the time that a region has been updated.

If you are looking for support on Data Stack or Google Cloud solutions, feel free to reach out to us at sales@astrafy.io.