Set up dbt for Fabric SQL Database

Introduction

The dbt (Data Build Tool) open-source framework simplifies data transformation and analytics engineering. It focuses on SQL-based transformations within the analytics layer, treating SQL as code.dbt enables analytics engineers to transform data in their warehouses by writing select statements and turns these select statements into tables/views. dbt supports version control, modularization, testing, and documentation.

Prerequisites for the dbt adapter for Microsoft Fabric SQL Database

Follow this list to install and set up the dbt prerequisites:

1. Python version 3.7 (or higher).
2. The Microsoft ODBC Driver for SQL Server.
3. Latest version of the dbt-sqlserver adapter from the PyPI (Python Package Index) repository using pip install dbt-sqlserver.

Powershell

pip install dbt-sqlserver

4. Make sure to verify that dbt-sqlserver and its dependencies are installed by using pip list command:

pip list

A long list of the packages and current versions should be returned from this command.

Get started with dbt-sqlserver adapter

Clone the jaffle_shop demo dbt project onto your machine via Powershell through the git clone command.

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

Open the jaffle_shop project folder in Visual Studio Code.

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

config:
  partial_parse: true
jaffle_shop:
  target: fabric-dev
  outputs:
    fabric-dev:
      authentication: ServicePrincipal
      database: <Put the database name here>
      driver: ODBC Driver 18 for SQL Server
      host: <Put the server name here>
      schema: dbo
      tenant_id: <Enter Tenant Id>
      client_id: <Enter Client Id>
      client_secret: <Enter Client Secret>
      threads: 4
      type: sqlserver

In case of Azure CLI authentication, Authenticate yourself to Azure in the Visual Studio Code terminal.

Run az login in Visual Studio Code terminal if you’re using Azure CLI authentication.

config:
  partial_parse: true
jaffle_shop:
  target: fabric-dev
  outputs:    
    fabric-dev:
      authentication: CLI
      database: <put the database name here>
      driver: ODBC Driver 18 for SQL Server
      host: <Put the server name here>
      schema: dbo
      threads: 4
      type: sqlserver

4. To test the connectivity to the SQL database, run dbt debug in the Code terminal.

dbt debug

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

5. Now, it’s time to test if the adapter is working or not. First run dbt seed to insert sample data into the database.

dbt seed

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

dbt run

7. Run dbt test to validate data against some tests.

dbt test

The dbt project is now deployed to Fabric SQL Database.