Edit

Tutorial: Set up dbt for Fabric Data Warehouse

  • Article

Applies to: Warehouse in Microsoft Fabric

This tutorial guides you through setting up dbt and deploying your first project to a Fabric Warehouse.

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 supports version control, modularization, testing, and documentation.

The dbt adapter for Microsoft Fabric can be used to create dbt projects, which can then be deployed to a Fabric Synapse Data Warehouse.

You can also change the target platform for the dbt project by simply changing the adapter, for example; a project built for Azure Synapse dedicated SQL pool can be upgraded in a few seconds to a Fabric Synapse Data Warehouse.

Prerequisites for the dbt adapter for Microsoft Fabric

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-fabric adapter from the PyPI (Python Package Index) repository using pip install dbt-fabric.

    pip install dbt-fabric

    Note

    By changing pip install dbt-fabric to pip install dbt-synapse and using the following instructions, you can install the dbt adapter for Synapse dedicated SQL pool.

  4. Make sure to verify that dbt-fabric 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.

  5. If you don't already have one, create a Warehouse. You can use the trial capacity for this exercise: sign up for the Microsoft Fabric free trial, create a workspace, and then create a warehouse.

Get started with dbt-fabric adapter

This tutorial uses Visual Studio Code, but you can use your preferred tool of your choice.

  1. Clone the demo dbt project from https://github.com/dbt-labs/jaffle_shop onto your machine.

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

  2. Open the jaffle_shop project folder in Visual Studio Code.

    A screenshot from the Visual Studio Code, showing the open project.

  3. You can skip the sign-up if you have created a Warehouse already.

  4. 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-fabric adapter.

    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: <enter your SQL analytics endpoint here>
      schema: dbo
      threads: 4
      type: fabric

    Note

    Change the type from fabric to synapse to switch the database adapter to Azure Synapse Analytics, if desired. Any existing dbt project's data platform can be updated by changing the database adapter. For more information, see the dbt list of supported data platforms.

  5. 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.
    • For Service Principal or other Microsoft Entra ID (formerly Azure Active Directory) authentication in Microsoft Fabric, refer to dbt (Data Build Tool) setup and dbt Resource Configurations.

  6. Now you're ready to test the connectivity. Run dbt debug in the Visual Studio Code terminal to test the connectivity to your warehouse.

    dbt debug

    A screenshot from the Visual Studio Code, showing the dbt debug command.

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

  7. Now, it's time to test if the adapter is working or not. First run dbt seed to insert sample data into the warehouse.

    A screenshot from the Visual Studio Code, showing a dbt seed command.

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

    dbt test

    A screenshot from the Visual Studio Code, showing a dbt test command.

  9. Run dbt run to validate data against some tests.

    dbt run

    A screenshot from the Visual Studio Code, showing a dbt run command.

That's it! You have now deployed a dbt project to Synapse Data Warehouse in Fabric.

Move between different warehouses

It's simple move the dbt project between different warehouses. A dbt project on any supported warehouse can be quickly migrated with this three step process:

  1. Install the new adapter. For more information and full installation instructions, see dbt adapters.

  2. Update the type property in the profiles.yml file.

  3. Build the project.

Considerations

Important things to consider when using dbt-fabric adapter:

  • Review the current limitations in Microsoft Fabric data warehousing.

  • Fabric supports Microsoft Entra ID (formerly Azure Active Directory) authentication for user principals, user identities and service principals. The recommended authentication mode to interactively work on warehouse is CLI (command-line interfaces) and use service principals for automation.

  • Review the T-SQL (Transact-SQL) commands not supported in Synapse Data Warehouse in Microsoft Fabric.

  • Some T-SQL commands, such as ALTER TABLE ADD/ALTER/DROP COLUMN, MERGE, TRUNCATE, sp_rename, are supported by dbt-fabric adapter using Create Table as Select (CTAS), DROP and CREATE commands.

  • Review Unsupported data types to learn about the supported and unsupported data types.

  • You can log issues on the dbt-fabric adapter by visiting Issues · microsoft/dbt-fabric · GitHub.

Related content