Dr. Mario's 2nd 🧠

How to Build a Semantic Layer in Pieces Step-by-Step for Busy Analytics Engineers

2 min read

rw-book-cover

Metadata

Highlights

What you want is a unified development flow that handles normalized transformation in dbt models and dynamic denormalization in the dbt Semantic Layer (meaning it dynamically combines and reshapes normalized data models into different formats whenever you need them). (View Highlight)

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. (View Highlight)

New highlights added 2024-07-14

this is built on top of the above, and something we want to generate dynamically in the dbt Semantic Layer. (View Highlight)

a window function that sums customer_cac statically in the dbt model is not a field we want in our semantic model because we want to dynamically codify that calculation as a metric in the Semantic Layer. (View Highlight)

Start with the semantic model going through column by column and putting all identified columns from Step 2 into the 3 semantic buckets:

  1. Entities — these are the spine of your semantic concepts or objects, you can think of them as roughly correlating to IDs or keys that form the grain.
  2. Dimensions — these are ways of grouping and bucketing these objects or concepts, such as time and categories.
  3. Measures — these are numeric values that you want to aggregate such as an order total or number of times a user clicked an ad. (View Highlight)

Create saved queries with exports configured to materialize your new Semantic Layer-based artifacts into the warehouse in parallel with the frozen rollup. This will allow us to shift consumption tools and audit results. (View Highlight)

Graph View