You know a Semantic Layer would be hugely valuable, but how do you actually build such a thing? Pro tip: Crafting a Semantic Layer is about building iterative velocity alongside accuracy, so that when your stakeholders ask about Revenue MoM grouped by Attribution Channel, you can answer instead of adding a ticket to the backlog. Start with these four steps: 1. Identify a Data Product that is impactful: Find something that is in heavy use and high value, but fairly narrow scope. Don’t start with a broad executive dashboard that shows metrics from across the company because you’re looking to optimize for migrating the smallest amount of modeling for the highest amount of impact that you can. For example, a good starting place would be a dashboard focused on Customer Acquisition Cost (CAC) that relies on a narrow set of metrics and underlying tables that are nonetheless critical for your company. 2. Catalog the models and their columns that service the Data Product, both in dbt and the BI tool, including rollups, metrics tables, and marts that support those. Pay special attention to aggregations as these will constitute metrics. 3. Melt the frozen rollups in your dbt project, as well as variations modeled in your BI tool, into Semantic Layer code. 4. Create a parallel version of your data product that points to Semantic Layer artifacts, audit, and then publish. Creating in parallel takes the pressure off, allowing you to fix any issues and publish gracefully. You’ll keep the existing Data Product as-is while swapping the clone to be supplied with data from the Semantic Layer. Dig deeper into the step-by-step process of how to ship a Semantic Layer in pieces at our link in the comments.
Thanks for The Great advice on how to proceed regarding an implementing of a semantic layer. We have started building a semantic layer in DBT, and it is a bit challenging due to missing direct Power bi support (When will that be available?) We needed to install The cli client even if we are in the DBT Cloud setup. It is also a bit unclear how to build a standard Kimball Star schema model (no OBT here).
The two main reasons, why our customers choose a semantic layer are: 1) flexibility, you don't need to persist all KPI granularities manually 2) single source of truth, one technical definition that can be used in multiple tools (Getdot.ai, Hex, Google Sheets)
Very informative content. Would love to see 1. Tool agnostic semantic layer solution 2. Non duplication of logic on BI tool and dbt (one singular place of logic in dbt)
Agree with the first step mentioned. It might be the most important - finding a valuable, narrow use-case to allow fast iteration and user feedback. That’s a post unto itself.
Very informative
Check out the step-by-step here: https://docs.getdbt.com/blog/semantic-layer-in-pieces