learning-it by example

#130 Data modelling 6.7 min

Define a Metric Once: Views and a Metrics Layer

Two dashboards said 279 and 249 for one March; defined once, both said 150.

1. Two dashboards for the same made-up shop showed March revenue of 279 and 249, each using its own query. What did both show once the revenue rule was defined in one place?
2. Why did the sales dashboard say 279, which is wrong against the rule used in the video?
3. How does a database view help fix the mismatched dashboards?
4. In the Python metric registry, how does metric_sql keep outside text out of the SQL it builds?
5. The video says agreeing on a number is not the same as what?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. Run the three versions

    Copy the code folder first. Run python run.py before, python run.py view and python run.py registry. Write down the March revenue each dashboard prints in each run, and say which runs make the two dashboards agree.

    A hint

    The before run has two separate queries. The other two read a shared definition.

  2. Forget the test orders

    In dashboards.py, remove the AND is_test = 0 part from the revenue_orders view, leaving the semicolon at the end. Predict what python run.py view prints for each dashboard. On this machine both dashboards print 249, because the view now keeps the test order of 99 as well as the 150.

    A hint

    Both dashboards read the same view, so they stay in agreement even when the rule is wrong.

  3. Add a refunds metric

    In metrics.py, add a third entry to METRICS called refunds. Give it expr SUM(amount), where status = 'refunded', and a doc line of your own. Run python run.py registry. On this machine the new description line appears with the other two, and the revenue and orders lines stay the same.

    A hint

    Copy the revenue entry and change the three values. The registry run prints one line per metric in METRICS.

  4. Mark the refund as paid

    In shop.py, change order 4 so its status is 'paid' instead of 'refunded'. Predict the March revenue for python run.py before, python run.py view and python run.py registry. On this machine before prints 279 for both dashboards, and view and registry both print 180.

    A hint

    Order 4 is for 30, and its flag for test orders is 0. The shared definition now counts it as revenue.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

The orders are made up and the metrics layer is a small Python dict that builds SQL; no real semantic layer product, including dbt, is run.

Up next in Data modelling

More in Data modelling

All Data modelling videos

Related videos