Getting Started with dbt (Data Build Tool) in Microsoft Fabric

Introduction

DBT (Data Build Tool) is a modern data transformation and modeling tool designed to streamline and simplify data engineering workflows. Unlike traditional ETL (Extract, Transform, Load) processes, dbt focuses exclusively on the transformation phase, leveraging SQL and YAML files. This makes it accessible to both data engineers and analysts.

With dbt’s adapter for Microsoft Fabric, you can create dbt projects and deploy them to a Fabric Data Warehouse for efficient data transformation.

To understand what dbt is, check out my article: https://www.c-sharpcorner.com/article/data-build-tool-dbt-kickstart/

Prerequisites

1. Fabric SQL database
2. Repository (project)
3. Python version 3.7 (or higher) – While installing, check "Add Python to environment variables" so Python will be added in all the terminals
4. The Microsoft ODBC Driver for SQL Server.
5. Microsoft C++ Build Tools: https://visualstudio.microsoft.com/visual-cpp-build-tools/
6. Dbt adaptor → Either dbt-fabric or dbt-SQL server (both work with Fabric SQL Database)

Let's get started.

First, we will be focusing on the creation of a Fabric SQL database.

Step 1. Create a new SQL database in Fabric.

1. You need an existing Fabric capacity. If you don't, start a Fabric trial
2. Enable SQL Database in Fabric via the Admin Portal Tenant Settings (Otherwise, you'll see an error: "SQL database failed to create")
   
3. Create a Workspace in the fabric portal.
   
4. Click New Item → SQL Database.
   
   
5. This will create an empty database.
   
6. Click on Settings → Copy the Connection String for future use.
   

Now, our fabric SQL database is ready. Let's focus on setting up DBT.

Step 2. Setting Up dbt.

Clone the repo

• Clone the jaffle_shop demo dbt project onto your machine.
• You can clone a repo with Visual Studio Code's built-in source control.

Or, for example, you can use the git clone command.

git clone https://github.com/dbt-labs/jaffle_shop.git

Create a profiles.yml file. Add the following configuration to profiles.yml. This file configures the connection to your warehouse in Microsoft Fabric using the dbt-sqlserver adapter.

jaffle_shop:
  target: dev
  outputs:    
    dev:
      authentication: CLI
      database: <put the database name here>
      driver: ODBC Driver 18 for SQL Server
      host: <enter your SQL analytics endpoint here>
      schema: dbo
      threads: 4
      type: sqlserver

Install dbt-core & dbt-sqlserver.

pip install dbt-core

pip install dbt-sqlserver

Verify all dependencies using.

pip list

Authenticate yourself to Azure by using the Run az login.

az login

Now, you're ready to test the connectivity. To test the connectivity to your warehouse, run dbt debug in the Powershell terminal.

dbt debug

All checks are passed, which means you can connect your warehouse using the dbt-sqlserver adapter from the jaffle_shop dbt project.

Step 3. Running dbt Commands.

Now, it's time to test if the adapter is working or not. First, run dbt seed to insert sample data into the database. You can see the table is created in DB and the same data is inserted. This is the beauty of DBT. Here the seed file is like a master data which is the .csv file. dbt will automatically create and insert the data into the table.

dbt seed

Define Models & Materialization: Configure dbt_project.yml to define models as tables or views.

Execute “dbt run” or “dbt build” to build the entire project.

dbt run

Run dbt test to run the models defined in the demo dbt project.

dbt test

Step 4. Exploring dbt DAG (Directed Acyclic Graph)

Another feature of dbt is DAG (Directed Acyclic Graph).

dbt provides DAG visualization, which helps in understanding dependencies between models.

A DAG is nothing more than a step-by-step plan on when and under what conditions to execute the nodes. When we run the command dbt build, each node represents a model to be run, a test to be tested, a seed to be loaded, or a snapshot to be taken.

To achieve this, execute “dbt docs generate” and “dbt docs serve”. It will open the browser, which will show all the details of the project along with the graph.

dbt docs generate
dbt docs serve

Conclusion

I hope this guide helps you understand dbt and set it up in the Microsoft Fabric SQL Database. Now it’s your turn to implement dbt in your projects and unlock its full potential!

Stay tuned for my next article.

Signing off,

TharunKumar M P