dbt and Google Cloud: What’s new and what’s next

As the industry standard for data transformation, dbt brings software engineering practices into the analytics workflow, including version control, testing, CI/CD deployments, data lineage, and other features. As part of our commitment to our customers, we continue to evolve dbt to remain vendor-independent and work seamlessly with the major data storage and cloud providers in the industry.

That’s why so many Google Cloud customers choose dbt for data transformations with Google BigQuery. Besides integrating tightly with BigQuery and Google Cloud, dbt works with a large number of third-party applications. This enables Google Cloud developers to integrate other data applications easily with the Google Cloud ecosystem.

dbt and Google Cloud are working closely together to provide even better support for BigQuery from within dbt-powered data pipelines. We’ll review how dbt supports BigQuery today and what features you can expect to see in the near future.

Integrating dbt and BigQuery gets easier

dbt’s support for a mature analytics development lifecycle enables data and analytics engineers to produce and ship high-quality data in a collaborative, governed manner. Using standard data engineering languages, such as SQL and Python, data engineering teams can gain more control and power over their data workflows than they’ve had before.

dbt is validated for Google Cloud BigQuery. That means it’s an officially recognized partner solution that has fulfilled a specific set of requirements to ensure the best possible integration and performance.

We’ve worked closely with the Google team to build dbt from the ground up for Google Cloud. In addition to blazing performance, users can harness specific BigQuery-specific features such as partitioning, clustering in BigQuery ML, and more.

Today, BigQuery is the second largest adapter for dbt, with tens of thousands of projects in production. Multiple Google Cloud customers - including Rocket Money, Bilt Rewards, and Virgin Media O2 - have leveraged dbt and BigQuery for their data transformation workflows. Customers report that using dbt lowers BigQuery ramp-up times and results in increased productivity, reduced errors, and faster delivery.

Getting started with dbt from Google Cloud is easy, thanks to the latest integration released by the Google Cloud and dbt teams. If you don’t have a dbt account, you can create one by navigating to the Google Cloud Partner Center.

From there, you can establish a connection between dbt and BigQuery. You can authorize dbt to connect to BigQuery using either a service account key downloaded in a JSON file or OAuth.

From there, you can create a new model in the dbt IDE and run it to see how your transformation shows up in BigQuery. dbt stores your changes in a Git repository, so if you need to roll back a change, you can do so quickly and easily.

Using BigQuery from dbt - no upskilling required

Have your Python developers ever come across scenarios where their local runtime is just not large or powerful enough to process their Python data? When processing runs into terabytes of data, a single Python runtime often isn’t enough.

BigQuery contains built-in support for DataFrames, providing a Pythonic DataFrame and machine learning (ML) API powered by the BigQuery engine. This gives Python developers a Pandas or Scikit-like interface that automatically transpiles to SQL for server-side execution.

This means developers can now leverage the distributed power of BigQuery and the scale of BigQuery from their laptops. A data scientist can go from tens of gigabytes of data to terabytes of data without having to switch on infrastructure.

BigQuery accomplishes this with several libraries that provide extensions above and beyond the default Pandas and Scikit features:

• bigframes.pandas: Transpiles Python into BigQuery SQL
• bigframes.ml: Transpiles Scikit code to BigQuery ML, BigQuery’s native machine learning language, which can handle featurization, training, and batch prediction
• bigframes.ml.llm: Provides access to large language models and features such as multimodal processing

So how does this come together within dbt? On top of SQL-based transformations, dbt supports Python models directly. BigQuery’s dbt adapter automatically converts this Python code to work with BigFrames, the BigQuery DataFrames implementation.

This means that data scientists and other Python developers who use dbt can take advantage of the power of BigQuery without any upskilling or direct provisioning of infrastructure. dbt users can also use dbt features such as incremental materialization, and bring in their own custom Python code as user-defined functions (UDF).

To see this in action, check out the demo in the webinar (demo begins near the 34-minute mark). You can also try it yourself using our quickstart for BigQuery DataFrames with dbt Python models.

Other dbt and BigQuery features

Beyond this, there are a number of improvements coming for dbt and BigQuery integration.

Google Cloud is currently rolling out support for Workload Identity Federation. This will increase security for dbt/BigQuery connections by issuing ephemeral, short-lived tokens for any deployment credentials. This eliminates the need to download a JSON credentials file, reducing the risk of credential leaks.

Additionally, Google Cloud is rolling out support for Private Service Connect for BigQuery. This means that customers can access Google Cloud managed services via private endpoints in their VPC networks, meaning traffic never traverses the public Internet. This will make your network security teams a lot happier for those workloads that require advanced security.

A huge change that has both the teams at dbt and Google Cloud excited is support for the Iceberg open table format. Apache Iceberg is a high-performance open table format developed for modern data lakes. dbt currently supports Iceberg, and we’re excited to bring this support to BigQuery in the next few months.

Finally, down the road, we’ll be integrating cost monitoring for BigQuery and other data warehouses into dbt. A lot of customers have asked us for a way to understand how dbt is driving data warehouse costs. (We hear this in general - not just for BigQuery.) Using this information, you can optimize costs and create data governance policies around data warehouse usage.

A thriving community

Another exciting aspect of the collaboration between dbt and BigQuery is the community support. There are a ton of interesting community projects involving the two technologies that are up and coming. For example, there’s the BigQuery ML for dbt project that started in 202 and is currently under active development, which enables users to train, audit, and use BigQuery ML models from inside of dbt projects.

We’re honored that so many BigQuery users utilize dbt for their data transformation pipelines. The dbt and Google Cloud teams are excited to continue working together on even deeper integrations to make the dbt + BigQuery experience as easy and seamless as possible.