dbt Labs on dbt: Streamlining KPI dashboards with the dbt Semantic Layer

Last year, I was Sad Paige, adrift in the Sea of WTF. I was constantly fielding questions about discrepancies in our fundamental business metrics. Each source had different numbers, causing endless confusion and stress, especially when high-level stakeholders relied on me for accurate data. Sleepless nights and tears (not the good kind) were common.

Then, in August, we implemented the dbt Semantic Layer and transformed our company scorecard metrics. This change led to a single source of truth and made me Happy Paige. My goal is to help you achieve this “after” experience by sharing our learnings from the dbt Semantic Layer implementation.

Our data team

Our centralized data team at dbt Labs includes data engineers, analytics engineers, and data analysts. Together with folks from Finance and Revenue Operations, we form pods that support different business areas such as Product, Go-to-Market, and Corporate and G&A.

Our complex data stack has 50 different active sources feeding into our data warehouse. Last year, the lack of a single source of truth for our most important metrics made our work challenging.

The ARR reporting challenge

One major challenge was reporting our performance on company-wide OKRs at each month’s All Hands meeting. Several OKRs were tied directly to Annual Recurring Revenue (ARR). Preparing these reports meant dealing with multiple sources:

• A Looker dashboard, which was outdated
• A Salesforce report from the Revenue Org
• A Google sheet maintained by Finance
• A Hex dashboard using a dbt model developed by Data and Finance

These sources rarely agreed, leading to a frustrating and time-consuming process of reconciling numbers. Preparing for these meetings took days, and the discussions during the meetings often re-litigated the ARR numbers.

The solution: dbt Semantic Layer

Working with my Finance stakeholder, we developed a source-of-truth dashboard called the Business Overview. With executive buy-in, we put the metrics from this dashboard into the dbt Semantic Layer, providing consistent, reliable metrics from a single source of truth. Implementing the dbt Semantic Layer was a game-changer.

With dbt Semantic Layer, users define metrics and dimensions in a YAML configuration file, which is then interpreted by dbt to generate SQL code that can be executed against a data warehouse. This approach decouples the definition of metrics from their implementation, allowing changes to be made in one place and propagated throughout all consuming systems automatically.

dbt Semantic Layer integrates seamlessly with dbt's existing model system, allowing metrics to be defined on top of transformed data models. This ensures that all metrics are based on clean, well-modeled data, reducing the risk of errors and inconsistencies.

Lessons learned

Project background

We have a large, mature dbt project with many denormalized marts. If you're earlier in your dbt journey, you might skip building wide marts and use normalized models in dbt Semantic Layer for more flexibility.

Naming and organization

• Naming semantic models: Prefix with sem_ to differentiate from dbt models.
• Naming metrics: Categorize by entity/noun for better organization and understanding.
• Organizing models: Follow a directory structure similar to existing dbt models for discoverability.

Granularity and flexibility

• Avoid over-aggregating data to maintain flexibility for adding dimensions later.
• Use dbt Semantic Layer features like cumulative metrics, percentiles, and non-additive dimensions for complex logic.

The results

After launching the Semantic Layer, creating OKR performance slides became a delight. I had more time for data exploration and insights. The discussions in meetings shifted from questioning data accuracy to focusing on strategic actions.

Quantitative benefits

• Time savings: 20 hours per month creating OKR slides and 12 hours answering ad-hoc ARR questions.
• Automation: Changes in the SL propagate automatically, saving time and reducing errors.
• Quality and trust: Improved focus on data model issues and better ROI from observability and alerting.

Qualitative benefits

• Trust and motivation: Effort is spent on the right metrics, protecting motivation.
• Commitment to code: Updates in the SL are visible and impactful, encouraging careful work.
• Alignment and legitimacy: Facilitates agreement on metric definitions and emphasizes the importance of data work.

Roadmap and future possibilities

Implementing the dbt Semantic Layer brought significant improvements in our data workflow, transforming frustrating tasks into productive and enjoyable activities. As we look to the future, the applications of the dbt Semantic Layer extend beyond simple reporting. dbt Semantic Layer's API, backed by GraphQL, opens up possibilities for building custom data products and operationalizing analytics, integrating seamlessly with various systems, including CRM platforms like Salesforce. This flexibility makes it an invaluable tool for supporting machine learning processes and enhancing AI workflows by providing clean, well-defined data.

We're excited about the possibilities that lie ahead and invite you to join us in exploring the full capabilities of dbt Labs and the dbt Semantic Layer. Whether you're a data professional looking to streamline your workflows or a business leader seeking to harness the power of reliable data, dbt Labs has the tools and expertise to support your goals.

Transform your data workflow and become your company's version of Happy Paige. Discover the power of the dbt Semantic Layer and see how it can streamline your metrics, save you time, and reduce stress.

Watch our on-demand webinar and take the first step towards making your data team happy and efficient.