Quickstart for dbt and Microsoft Fabric
Introduction
In this quickstart guide, you'll learn how to use dbt with Microsoft Fabric. It will show you how to:
- Load the Jaffle Shop sample data (provided by dbt Labs) into your Microsoft Fabric warehouse.
- Connect dbt to Microsoft Fabric.
- Turn a sample query into a model in your dbt project. A model in dbt is a SELECT statement.
- Add tests to your models.
- Document your models.
- Schedule a job to run.
Prerequisites
- You have a dbt account.
- You have started the Microsoft Fabric (Preview) trial. For details, refer to Microsoft Fabric (Preview) trial in the Microsoft docs.
- As a Microsoft admin, you’ve enabled service principal authentication. You must add the service principal to the Microsoft Fabric workspace with either a Member (recommended) or Admin permission set. The service principal must also have CONNECTprivleges to the database in the warehouse. For details, refer to Enable service principal authentication in the Microsoft docs. dbt needs these authentication credentials to connect to Microsoft Fabric.
Related content
Load data into your Microsoft Fabric warehouse
- 
Log in to your Microsoft Fabric account. 
- 
On the home page, select the Synapse Data Warehouse tile. 
- 
From Workspaces on the left sidebar, navigate to your organization’s workspace. Or, you can create a new workspace; refer to Create a workspace in the Microsoft docs for more details. 
- 
Choose your warehouse from the table. Or, you can create a new warehouse; refer to Create a warehouse in the Microsoft docs for more details. 
- 
Open the SQL editor by selecting New SQL query from the top bar. 
- 
Copy these statements into the SQL editor to load the Jaffle Shop example data: DROP TABLE dbo.customers;
 CREATE TABLE dbo.customers
 (
 [ID] [int],
 [FIRST_NAME] [varchar](8000),
 [LAST_NAME] [varchar](8000)
 );
 COPY INTO [dbo].[customers]
 FROM 'https://dbtlabsynapsedatalake.blob.core.windows.net/dbt-quickstart-public/jaffle_shop_customers.parquet'
 WITH (
 FILE_TYPE = 'PARQUET'
 );
 DROP TABLE dbo.orders;
 CREATE TABLE dbo.orders
 (
 [ID] [int],
 [USER_ID] [int],
 -- [ORDER_DATE] [int],
 [ORDER_DATE] [date],
 [STATUS] [varchar](8000)
 );
 COPY INTO [dbo].[orders]
 FROM 'https://dbtlabsynapsedatalake.blob.core.windows.net/dbt-quickstart-public/jaffle_shop_orders.parquet'
 WITH (
 FILE_TYPE = 'PARQUET'
 );
 DROP TABLE dbo.payments;
 CREATE TABLE dbo.payments
 (
 [ID] [int],
 [ORDERID] [int],
 [PAYMENTMETHOD] [varchar](8000),
 [STATUS] [varchar](8000),
 [AMOUNT] [int],
 [CREATED] [date]
 );
 COPY INTO [dbo].[payments]
 FROM 'https://dbtlabsynapsedatalake.blob.core.windows.net/dbt-quickstart-public/stripe_payments.parquet'
 WITH (
 FILE_TYPE = 'PARQUET'
 );
Connect dbt to Microsoft Fabric
- Create a new project in dbt. Navigate to Account settings (by clicking on your account name in the left side menu), and click + New Project.
- Enter a project name and click Continue.
- Choose Fabric as your connection and click Next.
- In the Configure your environment section, enter the Settings for your new project:
- Server — Use the service principal's host value for the Fabric test endpoint.
- Port — 1433 (which is the default).
- Database — Use the service principal's database value for the Fabric test endpoint.
 
- Enter the Development credentials for your new project:
- Authentication — Choose Service Principal from the dropdown.
- Tenant ID — Use the service principal’s Directory (tenant) id as the value.
- Client ID — Use the service principal’s application (client) ID id as the value.
- Client secret — Use the service principal’s client secret (not the client secret id) as the value.
 
- Click Test connection. This verifies that dbt can access your Microsoft Fabric account.
- Click Next when the test succeeds. If it failed, you might need to check your Microsoft service principal. Ensure that the principal has CONNECTpriveleges to the database in the warehouse.
Set up a dbt managed repository
When you develop in dbt, you can leverage Git to version control your code.
To connect to a repository, you can either set up a dbt-hosted managed repository or directly connect to a supported git provider. Managed repositories are a great way to trial dbt without needing to create a new repository. In the long run, it's better to connect to a supported git provider to use features like automation and continuous integration.
To set up a managed repository:
- Under "Setup a repository", select Managed.
- Type a name for your repo such as bbaggins-dbt-quickstart
- Click Create. It will take a few seconds for your repository to be created and imported.
- Once you see the "Successfully imported repository," click Continue.
Initialize your dbt project and start developing
Now that you have a repository configured, you can initialize your project and start development in dbt:
- Click Start developing in the Studio IDE. It might take a few minutes for your project to spin up for the first time as it establishes your git connection, clones your repo, and tests the connection to the warehouse.
- Above the file tree to the left, click Initialize dbt project. This builds out your folder structure with example models.
- Make your initial commit by clicking Commit and sync. Use the commit message initial commitand click Commit. This creates the first commit to your managed repo and allows you to open a branch where you can add new dbt code.
- You can now directly query data from your warehouse and execute dbt run. You can try this out now:- In the command line bar at the bottom, enter dbt runand click Enter. You should see adbt run succeededmessage.
 
- In the command line bar at the bottom, enter 
Build your first model
You have two options for working with files in the Studio IDE:
- Create a new branch (recommended) — Create a new branch to edit and commit your changes. Navigate to Version Control on the left sidebar and click Create branch.
- Edit in the protected primary branch — If you prefer to edit, format, or lint files and execute dbt commands directly in your primary git branch. The Studio IDE prevents commits to the protected branch, so you will be prompted to commit your changes to a new branch.
Name the new branch add-customers-model.
- 
Click the ... next to the modelsdirectory, then select Create file.
- 
Name the file dim_customers.sql, then click Create.
- 
Copy the following query into the file and click Save. dim_customers.sqlwith customers as (
 select
 ID as customer_id,
 FIRST_NAME as first_name,
 LAST_NAME as last_name
 from dbo.customers
 ),
 orders as (
 select
 ID as order_id,
 USER_ID as customer_id,
 ORDER_DATE as order_date,
 STATUS as status
 from dbo.orders
 ),
 customer_orders as (
 select
 customer_id,
 min(order_date) as first_order_date,
 max(order_date) as most_recent_order_date,
 count(order_id) as number_of_orders
 from orders
 group by customer_id
 ),
 final as (
 select
 customers.customer_id,
 customers.first_name,
 customers.last_name,
 customer_orders.first_order_date,
 customer_orders.most_recent_order_date,
 coalesce(customer_orders.number_of_orders, 0) as number_of_orders
 from customers
 left join customer_orders on customers.customer_id = customer_orders.customer_id
 )
 select * from final
- 
Enter dbt runin the command prompt at the bottom of the screen. You should get a successful run and see the three models.
Later, you can connect your business intelligence (BI) tools to these views and tables so they only read cleaned up data rather than raw data in your BI tool.
FAQs
Change the way your model is materialized
One of the most powerful features of dbt is that you can change the way a model is materialized in your warehouse, simply by changing a configuration value. You can change things between tables and views by changing a keyword rather than writing the data definition language (DDL) to do this behind the scenes.
By default, everything gets created as a view. You can override that at the directory level so everything in that directory will materialize to a different materialization.
- 
Edit your dbt_project.ymlfile.- 
Update your project nameto:dbt_project.ymlname: 'jaffle_shop'
- 
Configure jaffle_shopso everything in it will be materialized as a table; and configureexampleso everything in it will be materialized as a view. Update yourmodelsconfig block to:dbt_project.ymlmodels:
 jaffle_shop:
 +materialized: table
 example:
 +materialized: view
- 
Click Save. 
 
- 
- 
Enter the dbt runcommand. Yourcustomersmodel should now be built as a table!infoTo do this, dbt had to first run a drop viewstatement (or API call on BigQuery), then acreate table asstatement.
- 
Edit models/customers.sqlto override thedbt_project.ymlfor thecustomersmodel only by adding the following snippet to the top, and click Save:models/customers.sql{{
 config(
 materialized='view'
 )
 }}
 with customers as (
 select
 id as customer_id
 ...
 )
- 
Enter the dbt runcommand. Your model,customers, should now build as a view.- BigQuery users need to run dbt run --full-refreshinstead ofdbt runto full apply materialization changes.
 
- BigQuery users need to run 
- 
Enter the dbt run --full-refreshcommand for this to take effect in your warehouse.
FAQs
Delete the example models
You can now delete the files that dbt created when you initialized the project:
- 
Delete the models/example/directory.
- 
Delete the example:key from yourdbt_project.ymlfile, and any configurations that are listed under it.dbt_project.yml# before
 models:
 jaffle_shop:
 +materialized: table
 example:
 +materialized: viewdbt_project.yml# after
 models:
 jaffle_shop:
 +materialized: table
- 
Save your changes. 
FAQs
Build models on top of other models
As a best practice in SQL, you should separate logic that cleans up your data from logic that transforms your data. You have already started doing this in the existing query by using common table expressions (CTEs).
Now you can experiment by separating the logic out into separate models and using the ref function to build models on top of other models:
- 
Create a new SQL file, models/stg_customers.sql, with the SQL from thecustomersCTE in our original query.
- 
Create a second new SQL file, models/stg_orders.sql, with the SQL from theordersCTE in our original query.models/stg_customers.sqlselect
 ID as customer_id,
 FIRST_NAME as first_name,
 LAST_NAME as last_name
 from dbo.customersmodels/stg_orders.sqlselect
 ID as order_id,
 USER_ID as customer_id,
 ORDER_DATE as order_date,
 STATUS as status
 from dbo.orders
- 
Edit the SQL in your models/customers.sqlfile as follows:models/customers.sqlwith customers as (
 select * from {{ ref('stg_customers') }}
 ),
 orders as (
 select * from {{ ref('stg_orders') }}
 ),
 customer_orders as (
 select
 customer_id,
 min(order_date) as first_order_date,
 max(order_date) as most_recent_order_date,
 count(order_id) as number_of_orders
 from orders
 group by customer_id
 ),
 final as (
 select
 customers.customer_id,
 customers.first_name,
 customers.last_name,
 customer_orders.first_order_date,
 customer_orders.most_recent_order_date,
 coalesce(customer_orders.number_of_orders, 0) as number_of_orders
 from customers
 left join customer_orders on customers.customer_id = customer_orders.customer_id
 )
 select * from final
- 
Execute dbt run.This time, when you performed a dbt run, separate views/tables were created forstg_customers,stg_ordersandcustomers. dbt inferred the order to run these models. Becausecustomersdepends onstg_customersandstg_orders, dbt buildscustomerslast. You do not need to explicitly define these dependencies.
FAQs
Add tests to your models
Adding data tests to a project helps validate that your models are working correctly.
To add data tests to your project:
- 
Create a new YAML file in the modelsdirectory, namedmodels/schema.yml
- 
Add the following contents to the file: models/schema.ymlversion: 2
 models:
 - name: customers
 columns:
 - name: customer_id
 data_tests:
 - unique
 - not_null
 - name: stg_customers
 columns:
 - name: customer_id
 data_tests:
 - unique
 - not_null
 - name: stg_orders
 columns:
 - name: order_id
 data_tests:
 - unique
 - not_null
 - name: status
 data_tests:
 - accepted_values:
 arguments: # available in v1.10.5 and higher. Older versions can set the <argument_name> as the top-level property.
 values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
 - name: customer_id
 data_tests:
 - not_null
 - relationships:
 arguments:
 to: ref('stg_customers')
 field: customer_id
- 
Run dbt test, and confirm that all your tests passed.
When you run dbt test, dbt iterates through your YAML files, and constructs a query for each test. Each query will return the number of records that fail the test. If this number is 0, then the test is successful.
FAQs
Document your models
Adding documentation to your project allows you to describe your models in rich detail, and share that information with your team. Here, we're going to add some basic documentation to our project.
- 
Update your models/schema.ymlfile to include some descriptions, such as those below.models/schema.ymlversion: 2
 models:
 - name: customers
 description: One record per customer
 columns:
 - name: customer_id
 description: Primary key
 data_tests:
 - unique
 - not_null
 - name: first_order_date
 description: NULL when a customer has not yet placed an order.
 - name: stg_customers
 description: This model cleans up customer data
 columns:
 - name: customer_id
 description: Primary key
 data_tests:
 - unique
 - not_null
 - name: stg_orders
 description: This model cleans up order data
 columns:
 - name: order_id
 description: Primary key
 data_tests:
 - unique
 - not_null
 - name: status
 data_tests:
 - accepted_values:
 arguments: # available in v1.10.5 and higher. Older versions can set the <argument_name> as the top-level property.
 values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']
 - name: customer_id
 data_tests:
 - not_null
 - relationships:
 arguments:
 to: ref('stg_customers')
 field: customer_id
- 
Run dbt docs generateto generate the documentation for your project. dbt introspects your project and your warehouse to generate a JSON file with rich documentation about your project.
- Click the book icon in the Develop interface to launch documentation in a new tab.
FAQs
Commit your changes
Now that you've built your customer model, you need to commit the changes you made to the project so that the repository has your latest code.
If you edited directly in the protected primary branch:
- Click the Commit and sync git button. This action prepares your changes for commit.
- A modal titled Commit to a new branch will appear.
- In the modal window, name your new branch add-customers-model. This branches off from your primary branch with your new changes.
- Add a commit message, such as "Add customers model, tests, docs" and and commit your changes.
- Click Merge this branch to main to add these changes to the main branch on your repo.
If you created a new branch before editing:
- Since you already branched out of the primary protected branch, go to Version Control on the left.
- Click Commit and sync to add a message.
- Add a commit message, such as "Add customers model, tests, docs."
- Click Merge this branch to main to add these changes to the main branch on your repo.
Deploy dbt
Use dbt's Scheduler to deploy your production jobs confidently and build observability into your processes. You'll learn to create a deployment environment and run a job in the following steps.
Create a deployment environment
- From the main menu, go to Orchestration > Environments.
- Click Create environment.
- In the Name field, write the name of your deployment environment. For example, "Production."
- In the dbt Version field, select the latest version from the dropdown.
- Under Deployment connection, enter the name of the dataset you want to use as the target, such as "Analytics". This will allow dbt to build and work with that dataset. For some data warehouses, the target dataset may be referred to as a "schema".
- Click Save.
Create and run a job
Jobs are a set of dbt commands that you want to run on a schedule. For example, dbt build.
As the jaffle_shop business gains more customers, and those customers create more orders, you will see more records added to your source data. Because you materialized the customers model as a table, you'll need to periodically rebuild your table to ensure that the data stays up-to-date. This update will happen when you run a job.
- After creating your deployment environment, you should be directed to the page for a new environment. If not, select Orchestration from the main menu, then click Jobs.
- Click Create job > Deploy job.
- Provide a job name (for example, "Production run") and select the environment you just created.
- Scroll down to the Execution settings section.
- Under Commands, add this command as part of your job if you don't see it:
- dbt build
 
- Select the Generate docs on run option to automatically generate updated project docs each time your job runs.
- For this exercise, do not set a schedule for your project to run — while your organization's project should run regularly, there's no need to run this example project on a schedule. Scheduling a job is sometimes referred to as deploying a project.
- Click Save, then click Run now to run your job.
- Click the run and watch its progress under Run summary.
- Once the run is complete, click View Documentation to see the docs for your project.
Congratulations 🎉! You've just deployed your first dbt project!
FAQs
Was this page helpful?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


