Integrating DBT Cloud Semantic Layer with Power BI

Introduction

The integration of dbt Cloud’s Semantic Layer with Power BI creates a unified environment were trusted business metrics flow directly into interactive dashboards. Instead of redefining logic in every report, dbt centralizes models, sources, and metrics, then exposes them through a connector that Power BI can consume in real time. This approach reduces duplication, improves governance, and enables teams to focus on insight generation rather than manual query building. With dbt handling the data transformation layer and Power BI handling the visualization layer, organizations unlock a modern analytics stack that is both scalable and business friendly.  

Set Up dbt Cloud and SnowflakeStep 1: Create a dbt Cloud Account

• Go to: https://cloud.getdbt.com/signup/

• Sign up using: Email address, Full name, Password, Organization name.

• Click Create Account and You'll be logged into dbt Cloud

Step 2: Create a Project

• After logging in, click "New Project," enter a name (e.g., my_dbt_project), and click "Create Project."

Step 3: Configure Development Environment

• Go to the "Environments" tab in your project, locate the default Development environment,click"Edit".

• You will configure the Snowflake connection in the next step.

Step 4: Add New Connection (Snowflake) & Test It

• Under Connection, select Snowflake as the adapter, fill in the required fields (account, user, password, warehouse, database, schema).

• Click Test Connection to validate; if successful (green message appears), click Save to apply.

In this project, Snowflake was connected using a public dataset:      Database: SNOWFLAKE_SAMPLE_DATA  Schema: TPCH_SF1  Table: CUSTOMER — this table was used throughout the project for all demonstrations.

Step 5: Set Up a Repository (Git Integration) dbt Cloud needs a Git repo to track your project files:

• Under Environment → Repository, click Add Repository, choose your Git provider, authorize access, enter your repo name, and click Create to complete the setup.

• After clicking Create, you’ll be redirected to a confirmation page—this indicates your project is now successfully configured with both Snowflake and your Git repository.

Step 6: Initialize the dbt Project

• Click Start Developing in the IDE, then select Initialize dbt Project to begin.

• You’ll then be redirected to a page where dbt automatically sets up the default project structure with folders like models and dbt_project.yml.

• After setup, edit files in the browser IDE and use Commit & Sync to push changes to your Git repo.

Before building models and metrics, we first load customer data from the Snowflake Sample Data into our own Snowflake environment under a custom database and schema. With the data in place, we define models, YAML sources, and metrics—laying the foundation for a centralized dbt Semantic Layer that powers consistent analytics across BI tools.

Build Models and Metrics

Step 7: Create Models and Define Sources & Metrics In the File Explorer, under models/, create two folders:

• src/ → add the following files:

• customer.sql

• customer_schema.yml

• time_spine.sql

• time_spine.yml

• metrics/ → add:

• customer_metrics.yml

Step 8: Save and Commit Changes Click Commit and sync, add a commit message like "added source models and customer metric", then confirm to push your changes.  

Step 9: Run and Validate Your Models in dbt Cloud

• Run dbt run in the IDE to execute your models, sources, and tests, then validate the results in Run History and explore your assets in the Documentation tab.

Step 10: Create a Production Job to Build Models

• Go to the Orchestration tab, navigate to Jobs, and click Create Job -> Deploy Job

• Fill in Job Configuration

• Set the Job Name (e.g., dbtjob2), choose your Production Environment, and add the dbt commands under Execute Steps.

• Save and Run the Job

• Click Save Job, then on the job run page, click Run Now to start your first manual run.

• Monitor the Job Run

o   Monitor logs in real-time to track each step (e.g., dbt deps, dbt run) and review the outcome to address any failures. Enable the dbt Semantic Layer

Step 11: Set up dbt Semantic Layer

Select environment

1. Select the environment where you want the Semantic Layer to be enabled.
2. Go to Account Settings from the main navigation panel.
3. Under the Settings section, click on Projects and choose the project where you want to configure the Semantic Layer.

1. On the Set Up Semantic Layer Configuration page, choose your preferred deployment environment and click Save to activate the Semantic Layer in that environment.

Step 12: Configure Credentials & Create Tokens for dbt Semantic Layer

1. Choose Deployment Environment

• Go to Environments > Credentials & Service Tokens in your dbt Cloud project.

• Click on “Add Semantic Layer Credential”.

2. Add Credentials

• Enter credentials from your data platform (e.g., Snowflake).

• Use read-only access to schemas containing dbt models.

3. Create & Link Service Token

• After adding the credential, create a service token.

• Link the token to the credential.

• Use this token for authenticating downstream tools (like Tableau, Power BI, or Postman).

Step 13: Verify Semantic Layer Configuration

• Navigate to Catalog → Resources and select a Semantic Model.

• In the right-hand panel, check if key metrics (like total_customers, average_account_balance, etc.) are visible.

• Ensure all expected metrics are listed correctly for that model.

Power BI Desktop Integration

The Power BI Desktop integration is currently in preview as part of the same dbt Cloud update. It allows users to connect to the dbt Semantic Layer through GraphQL endpoints, making it possible to build reports using centrally managed metrics. While still under development, this integration supports better collaboration and governance by ensuring that metrics used in Power BI reports align with those in other BI tools. It’s a step forward in unifying analytics workflows across platforms.

Step 14: Install the dbt Power BI Connector (on your local machine)

• Download and run the .msi installer from dbt Labs to install the ODBC driver and Power BI connector.

• Open ODBC Data Sources (64-bit), check System DSN for the dbt Lab's entry, and confirm the ODBC driver is installed.

• In Power BI Desktop, go to File → Options and Settings → Data Source Settings and confirm the dbt Semantic Layer connector is listed.

Step 15: Use the Connector in Power BI Desktop

• Open Power BI Desktop and click Get Data.

• Search for Semantic Layer, select it, and click Connect.

• Enter the Host and Environment ID from dbt Cloud and select DirectQuery as the connection mode (Import not supported).

• After clicking OK, you’ll be prompted for a Service Token — paste the one you created earlier in Step 12, then click Connect to continue.

• When prompted, select METRICS.ALL or specific saved queries from dbt Cloud, then click Load to import.

You're now set to build reports using dbt-defined metrics and dimensions directly in Power BI.  

In Power BI, open the Data and Visualization panes on the left. Then, from the Fields panel, simply drag and drop metrics like total_customers or average_account_balance onto the report canvas. Power BI will automatically generate visualizations—such as bar or line charts—based on the selected metric.

Dragging “Customer: Market Segment” to the axis and “Total Account Balance” to values generates a visual chart in Power BI, clearly showing how account balances vary by market segment. Conclusion: Bringing together dbt Cloud’s Semantic Layer and Power BI transforms the analytics workflow into a streamlined, governed, and collaborative process. Teams no longer struggle with inconsistent definitions or duplicated SQL logic—every metric in Power BI is backed by dbt’s centralized source of truth. The result is faster reporting cycles, more reliable insights, and a data culture where technical and non-technical users operate with shared confidence. This integration marks a significant step toward building analytics that are not only powerful but also scalable and intuitive and genuinely usable by everyone in the organization.