Metricflow Join Order between Dim and Fct, Join Dim to Fct instead of Fct to Dim

[soham-dasgupta]

Lets consider a very common use case where an Analyst needs to calculate revenue from each customer given a dim table (dim_customer) that consist of customer details and a fct table (fact_customer_revenue) that consists of customer revenue.

The semantic models would look somewhat like below

semantic_models:
  - name: fact_customer_revenue
    defaults:
      agg_time_dimension: snapshot_day
    model: ref('fact_customer_revenue')
    entities:
      - name: customer
        type: primary
        expr: customer_id
    dimensions:
      - name: snapshot_day
        type: time
        expr: snapshot_day
        type_params:
          time_granularity: day
    measures:
      - name: revenue
        expr: revenue
        label: revenue
        agg: sum
        create_metric : true


  - name: dim_customer
    defaults:
    model: ref('dim_customer')
    entities:
      - name: customer
        type: primary
        expr: customer_id
    dimensions:
      - name: customer_id
        type: categorical
        expr: customer_id
        label: customer_id
      - name: customer_related_id
        type: categorical
        expr: customer_related_id
        label: customer_related_id
      - name: customer_location
        type: categorical
        expr: customer_location
        label: customer_location

To generate a feature set consisting of revenue at customer_id level , the below command would be run

.venv/amzn-hercules-amex/bin/mf query --metrics revenue --group-by customer__customer_id,customer__customer_related_id,customer__customer_location --explain

That leads to creation of SQL

SELECT
  dim_customer_src_10000.customer_id AS customer__customer_id
  , dim_customer_src_10000.customer_related_id AS customer__customer_related_id
  , dim_customer_src_10000.customer_location AS customer__customer_location
  , SUM(fact_customer_revenue_src_10000.revenue) AS revenue
FROM "db"."dev"."fact_customer_revenue" fact_customer_revenue_src_10000
LEFT OUTER JOIN
  "db"."dev"."dim_customer" dim_customer_src_10000
ON
  fact_customer_revenue_src_10000.customer_id = dim_customer_src_10000.customer_id
GROUP BY
  dim_customer_src_10000.customer_id
  , dim_customer_src_10000.customer_related_id
  , dim_customer_src_10000.customer_location

But, Whoaa!

Suddenly In the final output, the analyst would miss a bunch of customer_ids that exist in dim_customer, that has not generated revenue.

To fix it, the Analyst would now write a dbt model as below

SELECT
  dc.customer_id
  , dc.customer_related_id
  , dc.customer_location
  , COALESCE(SUM(fcr.revenue),0) AS revenue
FROM
  "dv"."dev"."dim_customer" dc
LEFT OUTER JOIN
  "db"."dev"."fact_customer_revenue" fcr
ON 
  dc.customer_id = fcr.customer_id
GROUP BY
   dc.customer_id
   , dc.customer_related_id
   , dc.customer_location

How can Metricflow handle such common use cases?