Tutorial: Set up dbt for Fabric Data Warehouse
Applies to: Warehouse in Microsoft Fabric
This tutorial guides you through setting up dbt and deploying your first project to a Fabric Warehouse.
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:
Latest version of the dbt-fabric adapter from the PyPI (Python Package Index) repository using
pip install dbt-fabric.
pip install dbt-fabric
pip install dbt-fabricto
pip install dbt-synapseand using the following instructions, you can install the dbt adapter for Synapse dedicated SQL pool.
Make sure to verify that dbt-fabric and its dependencies are installed by using
A long list of the packages and current versions should be returned from this command.
Get started with dbt-fabric adapter
This tutorial uses Visual Studio Code, but you can use your preferred tool of your choice.
Clone the demo dbt project from https://github.com/dbt-labs/jaffle_shop 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 https://github.com/dbt-labs/jaffle_shop.git
jaffle_shopproject folder in Visual Studio Code.
You can skip the sign-up if you have created a Warehouse already.
profiles.ymlfile. 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
Authenticate yourself to Azure in the Visual Studio Code terminal.
Now you're ready to test the connectivity. Run
dbt debugin the Visual Studio Code terminal to test the connectivity to your warehouse.
All checks are passed, which means you can connect your warehouse using dbt-fabric adapter from the jaffle_shop dbt project.
Now, it's time to test if the adapter is working or not. First run
dbt seedto insert sample data into the warehouse.
dbt testto run the models defined in the demo dbt project.
dbt runto validate data against some tests.
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:
Install the new adapter. For more information and full installation instructions, see dbt adapters.
typeproperty in the
Build the project.
Important things to consider when using dbt-fabric adapter:
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,
sp_rename, are supported by dbt-fabric adapter using
Create Table as Select(CTAS),
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.