Schema Modeling & SQL Migration Tools for BigQuery

[alex-goida]

Hi,

I'm working with BigQuery and managing around 50 tables, along with multiple routines, scheduled jobs, and views. I'm looking for recommendations on schema modeling and SQL schema migration tools that you use.

I've explored DBT, but its behavior seems unclear to me. Specifically, it looks like adding a column triggers a full table recreation rather than an in-place migration. Is there a way to handle this better in DBT, or are there other tools you’d recommend for more controlled schema migrations?

[marckevin]

Hi @alex-goida,

Welcome to Google Cloud Community!

For Schema Modeling approaches for planning the structure, I recommend focusing on best practices to optimize your BigQuery schema, including partitioning, clustering, and denormalization (Star Schema and Snow Schema), you could refer to this documentation.

For other schema migration tools, you can also check this Stack Overflow discussion on how to manage schema migrations in Google BigQuery like adding new columns.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[alex-goida]

Hi @marckevin,

When I mentioned Schema Modeling, I was referring to tools that assist with the modeling process. For example, Lucidchart can be used, but it requires too much manual effort and lacks integration or support for actual SQL scripts.

Regards,