17 KiB
Exploration - MetricFlow - 2024-08-06
MetricFlow, which powers the dbt Semantic Layer, helps you define and manage the logic for your company's metrics. It's an opinionated set of abstractions and helps data consumers retrieve metric datasets from a data platform quickly and efficiently.
This is Notion page summarises the explorations we carried out to determine if MetricFlow could be a good package to integrate within our DWH to create a semantic layer and centralise KPIs definitions, in the context of Business KPIs.
Table of contents
You can jump directly to Conclusions to check the pros&cons and the recommendation. For examples without setting things up, go to Exploring MetricFlow.
How to set up your environment
This MetricFlow exploration only requires setting things up in the data-dwh-dbt-project.
You will need to have dbt-core >= 1.8 to have dbt-metricflow installed to work properly. You can install them from this requirements.txt file:
dbt-core~=1.8.4
dbt-postgres~=1.8.2
dbt-metricflow~=0.7.1
Once you have the requirements updated and installed, you will need to set up a few new files within DWH. Follow these steps:
-
Create a new folder named
semantic_models. This should be within models folder, at the same level as the folders staging, intermediate and reporting. -
In the
semantic_modelsfolder, copy-paste the following files: -
Once the files are copied, run
dbt-parseto ensure there’s no error.-
Your dwh project should look something like this
-
Exploring MetricFlow
In the README file you will find similar contents as we have in this Notion page, so feel free to skip it.
Documentation
Make sure to have these links near you when playing with MetricFlow:
- Introduction to the dbt Semantic Layer. Official documentation, you'll find tons of information here. Keep in mind that we're using dbt-core, so not all functionalities are available. But still, it's a must read.
- Query examples. This should provide the main commands to be used when playing around with MetricFlow.
- Github Example 1. It's a nice way to see a use-case of implementations, can help creating new configurations based on what other people do.
DWH test case
At this stage we only have a dummy implementation of the semantic models. Some arbitrary decisions have been taken, so this can be challenged.
Firstly, you'll notice that these semantic models are in a dedicated semantic_models folder. This is to separate it from the rest of the modelisation within DWH.
Secondly, you'll notice that we have a metricflow_time_spine.sql. It's a bit weird but apparently it's mandatory for MetricFlow to run. It serves as a master Date table, similarly as we have for int_dates.sql. The name of this model needs to be exactly metricflow_time_spine.sql, otherwise it won't run.
Thirdly, we have the actual implementation of the semantic models, that would act as data marts. These can be identified by the prefix sem_. So far we only have a sem_bookings.sql, and the respective sem_bookings.yaml. You will see we also have a sem_accommodations.sql and sem_accommodations.yaml. The model .sql are quite straight forward - a copy of the useful fields from the intermediate bookings table. What is interesting is the .yaml.
Inside of the yaml file you'll observe the usual schema documentation as we have for other DWH models. What's new though is the following:
semantic_models, for bothsem_accommodation.yamlandsem_bookings.yamlmetrics, only formetrics.yaml. Technically, metrics can be defined inside thesem_xxx.yaml, but I found it easier to have them separated since you can combine measures from different sources.
Semantic models allow for the configuration of MetricFlow based on entities, dimensions and measures. This is the main layer of configuration. You can check the configurations of the 2 models here:
-
sem_accommodations.yaml
version: 2 models: - name: sem_accommodations description: Accommodations overview data mart, offering key details for each accommodation. One row per accommodation. columns: - name: id_accommodation description: The unique key of the accommodation mart. tests: - not_null - unique - name: id_user_host description: The foreign key relating to the user who owns the accommodation. - name: country_iso_2 description: The country iso code consisting of two characters where the accommodation is located. - name: country_name description: The name of the country where the accommodation is located. - name: country_preferred_currency_code description: The currency code that is preferred in the country of the accommodation. - name: is_active description: Boolean stating if an accommodation can receive bookings (true) or not (false). - name: created_date_utc description: The date of when the accommodation is created in our systems. - name: updated_date_utc description: The date of when the accommodation has been last updated. semantic_models: - name: accommodations # The name of the semantic model description: | Accommodation fact table. This table is at the accommodation granularity with one row per accommodation. defaults: agg_time_dimension: created_date_utc model: ref('sem_accommodations') entities: # Entities, which usually correspond to keys in the table. - name: id_accommodation type: primary - name: id_user_host type: foreign expr: id_user_host dimensions: # Dimensions are either categorical or time. They add additional context to metrics and the typical querying pattern is Metric by Dimension. - name: created_date_utc expr: created_date_utc type: time type_params: time_granularity: day - name: updated_date_utc expr: updated_date_utc type: time type_params: time_granularity: day - name: country_preferred_currency_code type: categorical expr: country_preferred_currency_code - name: is_active type: categorical expr: is_active - name: country_iso_2 type: categorical expr: country_iso_2 measures: # Measures, which are the aggregations on the columns in the table. - name: accommodation_count description: The total amount of accommodations. expr: 1 agg: sum - name: hosts_with_accommodations_count_distinct description: Distinct count of host users with a booking. agg: count_distinct expr: id_user_host - name: countries_with_accommodations_count_distinct agg: count_distinct expr: country_iso_2 -
sem_bookings.yaml
version: 2 models: - name: sem_bookings description: Bookings overview data mart, offering key details for each booking. One row per booking. columns: - name: id_booking description: The unique key of the booking mart. tests: - not_null - unique - name: id_user_guest description: The foreign key relating to the user who has booked an accommodation. - name: id_user_host description: The foreign key relating to the user who owns the accommodation. - name: id_accommodation description: The foreign key relating to the accommodation of this booking. - name: id_verification_request description: The foreign key relating to the guest verification request, if any, of this booking. Can be null. - name: booking_state description: The state of the booking. - name: check_in_date_utc description: The date of when the check in of the booking takes place. - name: check_out_date_utc description: The date of when the check out of the booking takes place. - name: created_date_utc description: The date of when the booking is created in our systems. - name: updated_date_utc description: The date of when the booking has been last updated. semantic_models: - name: bookings # The name of the semantic model description: | Booking fact table. This table is at the booking granularity with one row per booking. defaults: agg_time_dimension: created_date_utc model: ref('sem_bookings') entities: # Entities, which usually correspond to keys in the table. - name: id_booking type: primary - name: id_user_guest type: foreign expr: id_user_guest - name: id_user_host type: foreign expr: id_user_host - name: id_accommodation type: foreign expr: id_accommodation - name: id_verification_request type: foreign expr: id_verification_request dimensions: # Dimensions are either categorical or time. They add additional context to metrics and the typical querying pattern is Metric by Dimension. - name: created_date_utc expr: created_date_utc type: time type_params: time_granularity: day - name: check_in_date_utc expr: check_in_date_utc type: time type_params: time_granularity: day - name: check_out_date_utc expr: check_out_date_utc type: time type_params: time_granularity: day - name: updated_date_utc expr: updated_date_utc type: time type_params: time_granularity: day - name: booking_state type: categorical expr: booking_state measures: # Measures, which are the aggregations on the columns in the table. - name: booking_count description: The total amount of bookings. expr: 1 agg: sum - name: hosts_with_bookings_count_distinct description: Distinct count of host users with a booking. agg: count_distinct expr: id_user_host - name: guests_with_bookings_count_distinct description: Distinct count of guest users with a booking. agg: count_distinct expr: id_user_guest - name: accommodations_with_bookings_count_distinct description: Distinct count of accommodations with a booking. agg: count_distinct expr: id_accommodation - name: verification_requests_with_bookings_count_distinct agg: count_distinct expr: id_verification_request
Metrics provides a way to read from the previously configured semantic models and can be used to query the data in multiple manners, including by different dimensions, orders, time periods, filters, etc. Here’s the configuration of the metrics file:
-
metrics.yaml
version: 2 metrics: - name: total_accommodations description: Count of unique accommodations. label: Total Accommodations type: simple type_params: measure: accommodation_count - name: total_bookings description: Count of unique bookings. label: Total Bookings type: simple type_params: measure: booking_count - name: bookings_growth_mom description: Percentage growth of bookings compared to 1 month ago. type: derived label: Bookings Growth % M/M type_params: expr: (current_bookings - previous_month_bookings) * 100 / previous_month_bookings metrics: - name: total_bookings alias: current_bookings - name: total_bookings offset_window: 1 month alias: previous_month_bookings
Note: when changing the configuration of the yaml file, I recommend running the command:
dbt parse
Otherwise I feel like the changes are not applied, even if saving the files.
Running queries
To run queries we need to use the MetricFlow command. The options for the command can be found by:
mf --help
For running queries you'll need to use mf query command. To access the options you can run:
mf query --help
Let's try to run some queries!
Retrieve the total amount of bookings
mf query --metrics total_bookings
Retrieve the total amount of bookings by booking state
mf query --metrics total_bookings --group-by id_booking__booking_state
Not impressed? Well, imagine you want to...: Retrieve the total amount of bookings created, ordered by date, from the period 15th May 2024 to 30th May 2024, and compare the % of growth in the total bookings vs. what was observed in the previous month.
When settling different semantic models, make sure to properly set up the foreign keys. For example, sem_bookings have a foreign key on id_accommodation, that links with the primary key of the sem_accommodations table. This ensures that you can call metrics defined in the sem_bookings model split by dimensions that are in the sem_accommodations model. For instance, if you want to retrieve the whole history of total bookings per country, in descending total booking order:
mf query --metrics total_bookings --group-by id_accommodation__country_iso_2 --order -total_bookings
Note the usage of the '-' in the order clause to order descending.
If we want to retrieve the query that MetricFlow is using, we can use the argument --explain when running a query. For instance, in the previous query:
mf query --metrics total_bookings --group-by id_accommodation__country_iso_2 --order -total_bookings --explain
Results with the following output:
✔ Success 🦄 - query completed after 0.15 seconds
🔎 SQL (remove --explain to see data or add --show-dataflow-plan to see the generated dataflow plan):
SELECT
accommodations_src_10000.is_active AS id_accommodation__is_active
, SUM(subq_2.booking_count) AS total_bookings
FROM (
SELECT
id_accommodation
, 1 AS booking_count
FROM "dwh"."working"."sem_bookings" bookings_src_10000
) subq_2
LEFT OUTER JOIN
"dwh"."working"."sem_accommodations" accommodations_src_10000
ON
subq_2.id_accommodation = accommodations_src_10000.id_accommodation
GROUP BY
accommodations_src_10000.is_active
ORDER BY total_bookings DESC
Conclusions
Pros:
- Very flexible KPI combination. Ensuring configurations are well built and models properly normalised allows a crazy flexibility since you can define dimensions wherever you want.
- Scalability. Code-wise, each time we need a new dimension or metric, all previous dimensions and metrics already exist thus it’s quite easy to add new dimensions without breaking existing models.
- Clean structure. The fact of forcing the yaml configuration ensures the structures are clear and documented - otherwise metrics won’t work… - which is very nice.
Cons:
- Learning curve, relatively new package. I struggled a lot to find information to set it up, run commands, etc. There’s very few information available and part of it it’s outdated because it uses other versions of MetricFlow so… it generates a bit of frustration.
- Unable to materialise as tables. From what I’ve read, DBT Cloud has integrations and different ways to expose this into a Data Viz tools. On DBT Core, things are much more limited at the moment. I’ve spent some time looking around for materialising tables - there’s no native command - and MAYBE with a python integration could be possible. But it’s quite overkill at this stage.
Recommendation: not to use MetricFlow for the time being. I’d gladly re-explore it once it has new features, proper documentation and more users developing with it.