Skip to main content

dbt Semantic Layer partner integration

Contact us

This guide is for dbt Semantic Layer integration partners and explains integration guidelines, product roadmap, and connectivity.

To become a formal partner, integrate with the API, or have questions/feedback — contact us for more info.

The dbt Semantic Layer allows users to dynamically generate and query datasets in downstream tools based on their dbt governed assets, such as metrics, models, and entities. It helps organizations manage complexities such as data, tools, and teams to make more efficient and trustworthy decisions.

The rapid growth of different tools in the modern data stack has helped data professionals address the diverse needs of different teams. The downside of this growth is the fragmentation of business logic across teams, tools, and workloads.

To solve this, the dbt Semantic Layer provides a platform where users can confidently leverage their data from within their tools. dbt Cloud's change management capabilities ensure that any user modifications made to core business constructs, like metrics or entities, are distributed into all the tools connected to the data platform.

The dbt Semantic Layer can be used for a variety of tools and applications of data. Here are some common use cases

  • Business intelligence (BI), reporting, and analytics,
  • Data quality and monitoring,
  • Governance and privacy,
  • Data discovery and cataloging,
  • Machine learning and data science.
Share your use case

If you'd like to share other use cases for the dbt Semantic Layer, contact the dbt Labs team.

Product overview​

The dbt Semantic Layer product architecture includes four primary components:

ComponentsInformationDeveloper plansTeam plansEnterprise plansLicense
dbt ProjectDefine models and metrics in dbt Core.✅✅✅Open source in dbt Core
dbt ServerA persisted HTTP server that wraps dbt Core to handle RESTful API requests for dbt operations.✅✅✅BSL
SQL ProxyReverse-proxy that accepts dbt-SQL (SQL + Jinja-like query models and metrics, use macros), compiles the query into pure SQL, executes the query in the data platform, and returns the data.✅
* Available during Public Preview only		✅✅Proprietary in dbt Cloud
Metadata APIAccesses metric definitions primarily via integrations and is the source of truth for objects defined in dbt projects (like models, macros, sources, and metrics). The Metadata API is updated at the end of every dbt Cloud run.❌✅✅Proprietary in dbt Cloud

Review the following current architecture to understand how the components work together:

Current product architectureCurrent product architecture

Integration guidelines​

In collaboration with dbt Labs, partners and users can build dbt Semantic Layer integrations that can import model metadata and metric definitions, query metrics, use macros, and more.

For more details, refer to the Integration roadmap and Integration best practices guidance.

Integration roadmap

Integration partners generally build and approach their roadmap in the following stages:

FeatureInfoAvailability
Model metadataImport/sync model metadata (descriptions, dimensions, test, freshness, and more) via the dbt Cloud Metadata API.✅ 
Metric definitionsImport/sync metric definitions (metric calculation, dimensions, description, and more) via the dbt Cloud Metadata API.✅ 
dbt Semantic Layer as a data sourceConnect to the dbt Semantic Layer as a data source (for example, the Snowflake Proxy Server). Users can execute dbt-SQL to query metrics or models and use macros.*✅ 
Query metricsQuery the imported metrics via a metric-centric UI (for example, a user can select a metric, time grain, and dimensions of interest).✅ 
Entity definitionsImport/sync entity definitions (descriptions, dimensions, data types, relationships, metrics, and more) and query entities via the dbt Semantic Layer.*Coming soon, see the Product Roadmap for details 
dbt Semantic Layer ConnectorA dedicated connector with the ability to query any data platform supported in dbt Cloud. (Will replace (3).)*Coming soon, see the Product Roadmap for details 

*The coming soon features are expected to launch in 2023. Refer to the Product Roadmap for more details and timelines.

Integration best practices

To build a successful and seamless dbt Semantic Layer integration, it should express the following:

  • Consistent: Have a consistent user experience (UX) incorporated into existing core user workflows.
  • Trustworthy: Treat dbt assets (metrics, models, and entities) as first-class objects and indicate that their definitions and resulting datasets come from dbt Cloud.
  • Efficient: Provide a clear advantage over the current approach to setting up metrics and analyses, and finding dimensions/datasets in the tool.
  • Accessible: Include a self-serve component so a data consumer can ask questions via the user interface (UI), if applicable.

Use the Metadata API​

This section will explain how to connect to and query the Metadata API for model and metric definitions.

To use the dbt Semantic Layer, you must meet the prerequisites.

Metadata API authorization
Refer to our Authorization documentation to learn how to authorize requests to the Metadata API.

Metrics-specific queries work identical to existing Metadata API queries. This means existing integrations that query model metadata will work perfectly in the context of metrics.
Query the Metadata API
Test out the Metadata API by using the GraphQL sandbox and use this Python client as a starting point to develop.

Query models for a project​

You can query model definitions or details about a specific model for a project from a given job.

This is an example of querying all models that utilize the schemaanalytics from a given job.

{  
 models(jobId: 181329, schema: "analytics") {
   name
   status
   compileCompletedAt
   database
   dbtVersion
   runGeneratedAt
   }
}

Query metrics for a project​

Query metrics definitions or details for a project from a given job and refer to the following resources:

  • Metrics query — Information on how to query the full list of metrics defined in a user’s project with the dbt Cloud Metadata API.
  • dbt Metrics docs — Information on the available metric properties.
  • GraphQL sandbox — Access to test the dbt Cloud Metadata API testing environment.

This is an example listing metrics from a given job:

{
  metrics(jobId: 123) {
    name
    label
    description
    model
    dependsOn
    calculation_method
    expression
    timestamp
    timeGrains
    dimensions
    window
    filters
    tags
    meta
  }
}

Query the dbt Semantic Layer​

This section explains how to connect to or query the dbt Semantic Layer Proxy Server to return model data, metric data, and so on.

When you configure the dbt Semantic Layer, dbt Cloud provides a Proxy Server endpoint that users can connect to as though it's a Snowflake-hosted endpoint. Once the queries are submitted, dbt Cloud will:

  1. Compile dbt-sql queries into valid Snowflake SQL,
  2. Execute the compiled SQL against the Snowflake data platform,
  3. Return the results to the client.

Replace the hostname in your existing data platform connection with the relevant dbt Cloud Proxy Server URL (for example, abc123.proxy.cloud.getdbt.com). All queries you submit through the endpoint will be compiled en route to the data platform.*

*Note: This approach will change with the new Semantic Layer connection in mid-2023, which will be able to query all data platforms supported in dbt Cloud through dedicated JDBC/ODBC drivers, and eventually an API.

Users can compile and execute metric queries using macros defined in the dbt-metrics package. This package:

  • Generates the SQL required to accurately calculate the metric definition,
  • Supplies helper macros for derived calculations (like month over month, year to date, and so on) time series operations
select * 
from {{ metrics.calculate(
    metric_list=[metric('customers'), metric(‘revenue’)],
    grain='week',
    dimensions=['plan', 'country'],
    secondary_calculations=[
        metrics.period_to_date(aggregate="sum", period="year"),
        metrics.rolling(aggregate="average", interval=4, alias="avg_past_4wks")
    ],
    start_date='2020-01-01',
    end_date="date_trunc('day', getdate())"
) }}

Product Roadmap​

The dbt Semantic Layer product roadmap details what features are coming soon and the timelines associated with them.

ProductEstimated launch
EntitiesEarly 2023
dbt Semantic Layer ConnectorEarly-mid 2023
dbt Semantic Layer APIMid 2023

Entities​

dbt Labs will introduce a new node type, entity, when dbt Core version 1.5 launches. It introduces a new and efficient way to define metrics by reusing logic (for example, time_grains).

Entities are semantic objects made up of curated dimensions from models with more metadata defined. Over time, users can standardize metric and entity definitions with packages to speed up development.

For integrations, entities will provide information like:

  • a way to organize metrics based on the entity they reference, and
  • a new consumable and dynamically generated dataset (versus finding a table in the data platform).

This information will be available alongside the Metadata API, and entities can be directly queried through the dbt Semantic Layer.

Entity lineage graphEntity lineage graph
🚧

Entities are a work in progress — expect continuous changes and improvements. To stay up-to-date, refer to the entity discussions page.

Define entities in your dbt project.

entities: ## The top-level path of the new node
  - name: [Required] ## The name of the entity
    model: [Required] ## The name of the model that the entity is dependent on
    description: [Optional] ## The description of the entity
    
    dimensions: [Optional] ## The list of dimensions & properties associated with the entity. 
      - include: [Optional] *
      - exclude: [Optional]
      - name: [Required] ## The name of the dimension 
        column_name: [Optional] ## The name of the column in the model if not 1:1. Serves as mapping
        data_type: [Optional] ## The data type of the dimension
        description: [Optional] ## Description of the dimension
        default_timestamp: [Optional] ## Setting datetime dimension as default for metrics
        time_grains: [Optional] ## Acceptable time grains for the datetime dimension
        primary_key: [Optional] ## Whether this dimension is part of the primary key

dbt Semantic Layer Connector​

In order to support more data platforms and enhance the user experience, users will be able to connect to a dbt Cloud-supported data platform with the dbt Semantic Layer.

Integration partners need to install the Arrow FlightSQL JDBC/ODBC driver, which will authenticate with dbt Cloud and the data platform that it queries.

Envisioned connection architectureEnvisioned connection architecture

dbt Semantic Layer API​

dbt Cloud will provide a web API that supports:

  • Compiling dbt-SQL queries to return their compiled SQL.
  • Executing dbt-SQL queries and returning the queried results from the data platform.

The API will be a viable integration point with the dbt Semantic Layer. It will be authorized by a dbt Cloud service token and eventually support the invocation of dbt commands (e.g., dbt run, dbt test, etc.) in the future.

Contact us​

For dbt Semantic Layer support​

For partner and customer support, please email the Support team. Please ensure the message includes:

  • "Semantic Layer"
  • The name of the partner software
  • The dbt Cloud account ID of the customer, if you are a partner making the inquiry

For product and partnerships​

If you'd like to become a formal partner, have product feedback/questions, or are interested in integrating, email the Product and Partnership team.

0
Edit this page