Podcast Notes Analytics Power Hour #190: Metrics Layer

Episode Info

Analytics Power Hour Episode #190: I See Your Data Layer And Raise You A Metrics Layer

Interviewee: Benn Stancil (substack) (LinkedIn)

---

Executive Summary

Data Warehouse & Metrics logic used to be locked up inside BI tools, and therefore only available for that BI Tool to use. With modern analytics tooling (specifically dbt & "metrics layer"), it's possible to define that logic in a way that's consumable by whatever downstream tools need it (like other data pipelines, ML Feature Stores, as well as BI Tools)

---

Notes on Terms

• "Warehouse", "Data Warehouse", and "DW" all mean Cloud Data Warehouse (e.g. BigQuery, Snowflake)
• Using "BI Tool" to mean something like Tableau or Power BI
• Using "dbt" to mean...dbt (they're kind of unique in their space right now)

---

Version 1: Table Relationships, Metrics in BI Tool

Graph Notes:

• It's a mess...lots of redundant metrics & table joining logic

graph LR;
    BigQuery-->BI_Custom_SQL;
    BI_Custom_SQL-->BI_Data_Source;
    BI_Data_Source-->BI_Dashboard;
    BI_Metrics_Logic-->BI_Data_Source;
    BigQuery-->Data_Warehouse_SQL;
    BigQuery-->ML_Tools;
    ML_Metrics_Logic-->ML_Tools;

Loading

How things work at this stage:

• Bunch of data in a warehouse (e.g. BigQuery) that was all raw
• BI tools do everything: 1) Define relationships between data, and 2) Define metrics on top of these relationships
• Example: Turning list of purchases into revenue metric
  • BI Tool defines relationship between purchases, customers, and order line items
  • Then it creates a data model to define how to join stuff together
  • On top of that, there are formulas baked into the BI tool (e.g. Tableau calculated fields)
    • "here's how we computer revenue from a giant list of orders...include gift cards? Orders that get returned? Sales Tax?"
• All joining and metric logic locked away in BI tool (e.g. Tableau) & have drag & drop UI to do stuff with it
• Under the surface, it's a data cube

---

Version 2: Table Relationships in dbt, Metrics in BI Tool

Graph Notes

• No more Custom SQL...dbt is handling the modeling logic (so ML Tools & BI Data Sources can refer to same source of truth)
• Metrics logic still locked away in BI tool (so ML tools have to redo it)

graph LR;
    BigQuery-->BI_Data_Source;
    BI_Metrics_Logic-->BI_Data_Source;
    BI_Data_Source-->BI_Dashboard;
    BigQuery-->dbt;
    dbt-->BigQuery;
    BigQuery-->ML_Tools;
    ML_Metrics_Logic-->ML_Tools;

Loading

How things work at this stage:

• dbt pulled the first part out
  • What if instead of defining those relationships in a BI tool, you defined them in a way so it's actually clean in the data warehouse? (e.g. dim_purchases & dim_customers)
    • Modeling logic is well-defined before getting into a consumption tool
• Example (revisited): Turning list of purchases into revenue metric
  • dbt code in github grabs raw data, tests it, and defines relationship between purchases, customers, and order line items
  • BI tool uses output of dbt model - some calculated fields still present to aggregate metrics to level needed for dashboard
  • Metric logic is still locked away in BI Tool

---

Version 3: Table Relationships, Metrics Layer in dbt

Graph Notes:

• "dbt" means the data warehousing logic that dbt is doing (essentially everything before the metrics layer)
• Everything is now connected with dbt as the glue

graph LR;
    BigQuery-->dbt;
    dbt-->BigQuery;
    BigQuery-->dbt_metrics_layer;
    dbt_metrics_layer-->BI_Dashboard;
    dbt_metrics_layer-->BI_Data_Source;
    dbt_metrics_layer-->ML_Tools;

Loading

How things work at this stage:

• Thesis of metrics layer: "What if we put the metric logic further upstream from consumption, so downstream tools can use it?"
• Alternatives
  • dbt: Metrics
    • note: at time of writing this, dbt is planning on announcing their metrics layer at Coalesce conf Oct 2022, and for now they've published metrics in beta
  • Transform.co's MetricFlow

Cool Use Cases

• Now, can ask questions like, "Show me Daily Active Users by Region, by Quarter"; "Show me % Change in Revenue, by Sales Manager, by Week"
• Once all key data defined in a dbt project w/ metrics, can refresh all upstream data that flows into the metric with graph operators: dbt run +metric:*