Streamline Your ETL Processes: A Comprehensive Guide to Using dbt in Databricks 

In today’s fast-paced, data-driven world, businesses generate vast amounts of information daily. With over 328.77 million terabytes of data being created every day, the need for scalable, efficient data pipelines has never been more critical. Many organizations struggle with the complexity of traditional ETL (Extract, Transform, Load) processes—where moving, cleaning, and transforming data can be slow, error-prone, and challenging to scale. These bottlenecks often lead to delays in making data-driven decisions, impacting competitiveness in industries where timely insights are key. 

In this post, we’ll explore how to streamline ETL processes by combining two powerful tools: dbt (data build tool) and Databricks. We’ll dive into the pain points that data teams face in today’s dynamic business environments, particularly in managing transformations, maintaining transparency, and scaling pipelines. We’ll show you how dbt and Databricks can simplify your data workflows, improve collaboration, and ensure your data is always ready for analysis—helping you unlock faster, more reliable insights. 

What is dbt?  

dbt (data build tool) is an open-source command-line tool that empowers data analysts and engineers to  transform raw data into actionable insights with minimal friction. Operating on a “transform after load” architecture, dbt focuses solely on the transformation step, assuming that data is already loaded into your database. It enables users to write modular SQL or Python code, turning select statements into tables and views while managing dependencies automatically. dbt compiles this code into raw SQL and executes it directly on your database, such as in Databricks. With features like version control, testing, and documentation, dbt promotes best practices in collaborative coding, ensuring that teams can safely deploy transformations through CI/CD workflows and dynamically share documentation with stakeholders. By allowing analysts to own the entire transformation process without relying heavily on data engineering resources, dbt streamlines the development of complex data workflows.  

For example, imagine your company has a raw sales table in your database containing transaction data with inconsistencies like varying date formats, missing values, and duplicate records. Using dbt, you can create a model—a SQL script—that cleans and transforms this data into a new clean_sales table. In this model, you might write SQL code to standardize date formats, fill in missing values, and remove duplicates. dbt enables users to write such modular SQL or Python code, turning select statements into tables and views while managing dependencies automatically. If you have another model that calculates monthly revenue based on the clean_sales table, dbt will understand the dependency and ensure the clean_sales model runs first. When you execute dbt, it compiles this code into raw SQL and runs it directly on your database, such as in Databricks. 

What is Databricks?  

Databricks is a cloud-based data engineering and analytics platform that unifies data science, engineering, and business. Built on top of Apache Spark, it offers a collaborative workspace where teams can process large volumes of data, build machine learning models, and generate actionable insights. Databricks provides fully managed Spark clusters, interactive notebooks for exploration and visualization, and tools for scheduling and deploying data pipelines. By integrating with tools like dbt, Databricks allows organizations to streamline their entire data workflow—from data ingestion and transformation to advanced analytics and machine learning. 

Benefits of Integrating dbt with Databricks 

Improved Data Quality and Consistency 

Integrating dbt with Databricks significantly enhances data quality and consistency. dbt’s built-in testing features allow you to validate data at each transformation step, catching errors early and ensuring that only high-quality data moves through your pipeline. Additionally, dbt’s automated documentation keeps track of data models and transformations, providing transparency and alignment across your team. Combined with Databricks, these features ensure that your data remains reliable and consistent, ready for accurate analysis. 

Efficient Data Pipelines 

The combination of dbt’s transformation capabilities with Databricks’ powerful Spark engine creates highly efficient data pipelines. dbt allows you to break down complex transformations into modular SQL models, which can be executed in parallel, leveraging Spark’s distributed computing power. This setup accelerates the transformation process, enabling faster data processing and reducing the time from raw data to actionable insights. 

Scalability 

Scalability is seamless with the integration of dbt and Databricks. Databricks’ dynamic scaling capabilities allow you to handle growing data volumes and increasingly complex transformations without major infrastructure changes. dbt’s modular approach ensures that as your data models expand, they remain organized and manageable. Together, they provide a scalable solution that grows with your business, maintaining performance and efficiency. 

Collaborative Environment 

The integration of dbt with Databricks fosters a collaborative environment that enhances team productivity. Databricks notebooks allow multiple users to work together in real-time, supporting various languages like SQL, Python, and Scala. When combined with dbt, teams can collaboratively develop and refine data transformations within the same platform. This setup encourages cross-functional collaboration, streamlined version control, and faster iteration, resulting in more reliable data pipelines. 

Step-by-Step Guide to Integrating dbt with Databricks  

In this step-by-step guide, I’ll walk you through how to initialize and run dbt within Databricks, ensuring a seamless and efficient setup for your data transformation workflows. 

Requirements: 

• Install libraries dbt-core and dbt-databricks in your databricks cluster  

• Generate a Personal Access Token (PAT). Here’s how to generate a PAT: 

1. In Databricks, click on your profile icon in the top right corner and select Settings. 

2. In the Settings menu, go to Developer > Access tokens. 

3. Click on Generate new token. 

4. Give it a comment or label (e.g., “dbt integration”) to help you recognize it later, and set an appropriate expiration date. Be sure to save it somewhere safe because you cannot see it afterwards. 

5. Copy the generated token and store it securely, as you will need it to configure dbt to connect with Databricks. 

This PAT will be used during the dbt init setup, where you’ll be prompted to enter the token for secure access to your Databricks environment. 

By installing these libraries and setting up your PAT, you are ensuring that dbt can seamlessly interact with Databricks to transform your data. 

Step 1: Initialize Your dbt Project 

The first step in utilizing dbt with Databricks is to initialize a dbt project. To do this, use the dbt init command followed by your project name. First, open your terminal in Databricks, and make sure you have created a folder where you want to store your dbt project files. You can then change the directory to that folder using the cd (change directory) command. For example, I created a folder named dbt_projects and navigated into it:  

> mkdir /<folderpath>/dbt_projects 

> cd ./dbt_projects 

Once you are in the desired folder, you can initialize your dbt project (here called dbt_demo) by running: 

> dbt init dbt_demo 

This will create a new dbt project named dbt_demo inside the dbt_projects directory. Once initialized, navigate into the project directory by running: 

> cd ./dbt_demo 

Step 2: Configure dbt for Databricks 

When setting up your dbt project, you will be prompted to select the database adapter you want to use. In our case, since we are using Databricks, choose the option for “Databricks.” Here, dbt will configure the connection settings to ensure seamless integration with your Databricks environment. 

You will then be prompted to enter various credentials and connection parameters, such as your Databricks host, HTTP path, and an access token. Make sure to enter these correctly to ensure a successful connection. 

Host: This refers to the Databricks workspace URL. 

HTTP Path: This is provided by Databricks in you cluster configuration. 

Access Token: Highlighted on how to do it in the requirements. 

You will also have to specify whether you are using Unity Catalog. If not, specify the schema and the number of threads you want dbt to use. 

Step 3: Validate Your Setup 

After configuring the connection, it is important to validate that everything is set up correctly. This is where dbt debug comes in handy. Running this command verifies that dbt can successfully connect to your Databricks instance and that all required dependencies are met. 

> dbt debug 

If everything is configured properly, you should see a confirmation message stating “All checks passed!” This means that dbt is now fully connected to your Databricks environment, and you are ready to start building and running models.  

If all setup correctly, your folder should look like this:  

One issue you might encounter in Databricks is the disappearance of the profiles.yml file after restarting the cluster. This happens because Databricks clusters use ephemeral storage, meaning files stored in certain system directories (like /root/) are wiped when the cluster is restarted. To avoid having to recreate the profiles.yml file manually each time, there are a couple of ways you can solve this. 

One way is that you can automate the creation of the profiles.yml file using a simple Python script within a Databricks notebook. The script will recreate the profiles.yml file whenever you restart the cluster, ensuring that your dbt environment is always correctly configured. 

Here’s a sample code snippet to recreate your profiles.yml: 

import os 

# Path to the profiles.yml file in the root directory 

profiles_dir = “/root/.dbt/” 

profiles_file = os.path.join(profiles_dir, “profiles.yml”) 

# Create the directory if it doesn’t exist 

os.makedirs(profiles_dir, exist_ok=True) 

# Define your profiles.yml content (replace the placeholders with your actual values) 

profiles_content = “”” 

dbt_demo: 

  target: dev 

  outputs: 

    dev: 

      catalog: <your-catalog>  # or null if you are not using Unity Catalog 

      type: databricks 

      schema: <your-schema> 

      host: <your-host>  # Replace with your Databricks host URL 

      http_path: <your-http-path>   

      token: <your-access-token>   

      threads: 1 

“”” 

# Write the content to the profiles.yml file 

with open(profiles_file, “w”) as f: 

    f.write(profiles_content) 

print(f”profiles.yml created at: {profiles_file}”) 

An alternative approach is to store your profiles.yml file in a persistent storage location, such as the Databricks File System (DBFS), which persists across cluster restarts. You can store the profiles.yml in DBFS and then copy it to /root/.dbt/ each time the cluster starts using an init script or a notebook command. 

First, upload your profiles.yml file to a persistent location in DBFS. This ensures that the file is not lost when the cluster restarts. You can do this using either the Databricks UI or through the terminal: 

> cp /local/path/to/profiles.yml /dbfs/path/to/profiles.yml 

For example: 

> cp /local/path/to/profiles.yml /dbfs/.dbt/profiles.yml 

Then use a command in a notebook or an init script to copy the file from DBFS to /root/.dbt/: 

> cp /dbfs/dbt/profiles.yml /root/.dbt/profiles.yml 

This ensures that your profiles.yml file is always available, even after cluster restarts, without the need to manually recreate it. 

Use a Databricks notebook or an init script to copy the profiles.yml file from DBFS to the /root/.dbt/ directory each time the cluster starts. Here’s a general Python script you can use: 

import os 

import shutil 

# Source path in DBFS where profiles.yml is stored 

dbfs_profiles_file = “/dbfs/dbt/profiles.yml” 

# Destination path in /root/.dbt/ where dbt looks for the profiles.yml file 

profiles_dir = “/root/.dbt/” 

profiles_file = os.path.join(profiles_dir, “profiles.yml”) 

# Create the /root/.dbt/ directory if it doesn’t exist 

os.makedirs(profiles_dir, exist_ok=True) 

# Copy the profiles.yml from DBFS to the required location 

shutil.copy(dbfs_profiles_file, profiles_file) 

print(f”profiles.yml successfully copied to: {profiles_file}”) 

This script: 

• Copies the profiles.yml file from DBFS (/dbfs/dbt/profiles.yml) to the expected location in /root/.dbt/profiles.yml. 

• Automatically creates the /root/.dbt/ directory if it doesn’t already exist. 

You can automate this process by adding it to a Databricks init script. This will ensure that the profiles.yml file is copied to the correct location every time the cluster starts. 

To set up an init script: 

> cp /dbfs/dbt/profiles.yml /root/.dbt/profiles.yml 

Upload the script to DBFS: 

> cp copy_profiles.sh /dbfs/dbt/copy_profiles.sh 

After this is done, configure the cluster to use this init script: 

• Go to your Databricks cluster configuration. 

• Under Advanced Options, find the Init Scripts section. 

• Add the path to your init script, e.g., /dbfs/dbt/copy_profiles.sh. 

Step 4: Build Your First Models 

With your environment set up, you can now define your transformation logic. In this example, I’ve created a basic model, sales_summary. For this, I used sales data from a public dataset available on Kaggle, demonstrating how to go from raw data to actionable insights. You can define the model by adding it as a .sql-file in your models directory. Here as an example, I created a model that aggregates sales data by year, month, and country to provide insights like total sales and order counts over time. 

After defining the transformation logic in the SQL model file, the next step is to ensure that your data is both well-documented and meets the required quality standards. This is where the schema.yml file comes into play. In dbt, the schema.yml file allows you to define your source data, add descriptions to your models, and, most importantly, apply data quality tests. These tests help validate the accuracy and completeness of the data, ensuring that your transformations are reliable and consistent. 

First, let’s define the raw_sales table as a source in the schema.yml. This is where we document the table and apply basic tests to ensure the integrity of the raw data. For the raw_sales table, we want to ensure that the ORDERNUMBER column, which uniquely identifies each order, is always present (i.e., no null values).  

Next, we move on to documenting and testing the sales_summary model, which aggregates sales data by year, month, and country. Here, we can add both descriptions to make the model understandable for future users and tests to validate the results of the transformation. For instance, we want to ensure that the total_sales column, which represents the total sales for a given period, never contains null values. 

Now that both the source and the model are defined with their respective tests, the full schema.yml file looks like this: 

This schema.yml file captures both the raw data source and the transformation logic in a structured and transparent way. By combining the SQL model with the schema.yml file, you ensure that your transformations are correctly implemented, and your data remains reliable and well-documented. 

Once you have defined your model and tests, you can run them together using the dbt build command. This command not only runs your models but also executes any defined tests, ensuring that the transformations are accurate and the data meets your quality standards. Simply run the following in your terminal: 

> dbt build 

This command compiles your SQL code, executes it on your Databricks environment, and runs all associated tests in a single workflow. It creates the sales_summary table or view in your Databricks database with the aggregated data, and then verifies the integrity of the transformed data by applying the tests defined in your schema.yml. 

After running dbt build, you should see output like this: 

This output shows that dbt successfully built your model and passed all the data quality tests. The ORDERNUMBER and total_sales columns have been validated to meet the expected quality standards. By using the dbt build command, you have streamlined the process of running transformations and validating your data, ensuring a smooth, automated workflow. 

Step 5: Review the Results 

Once the dbt run command completes successfully, you can check the Databricks Catalog Explorer (or your preferred metastore) to see the tables and views created by your dbt models. In this case, the view sales_summary successfully created in the default schema. 

Step 6: Organize and Collaborate 

Your dbt project directory is structured to help keep your work organized. You can find folders for models, seeds, snapshots, and tests, all of which help ensure that your data transformation workflows are modular and easy to manage. Additionally, dbt automatically tracks your changes in version control, promoting collaboration among team members. 

At this point, you have successfully built your first dbt model in Databricks, transforming raw sales data into useful metrics, and you have validated the data in the same step. This is a powerful approach to building robust, reliable data pipelines. As you continue, you can further expand your models and tests to handle more complex transformations and ensure data quality at scale. 

This article covers an introduction to dbt, including how to install dbt Core and set up your connection to Databricks. Additionally, dbt offers a hosted version called dbt Cloud, which simplifies setup and management. For more details, you can explore how to connect with dbt Cloud from the Databricks documentation manually or via the Partner Connect. 

Closing 

In conclusion, integrating dbt with Databricks can significantly streamline your ETL processes, making data transformations faster, more efficient, and easier to manage. By leveraging dbt’s modular approach and Databricks’ powerful compute engine, you can ensure that your data pipelines are not only scalable but also collaborative and transparent. This powerful combination empowers you to take full control of your data workflows and deliver timely insights with confidence. So why wait? Start building your streamlined ETL process today and unlock the full potential of your data. 

Written by: