Modeling with dbt MetricFlow

dbt MetricFlow is an open-source semantic model that powers dbt’s Semantic Layer. For more information, see their documentation.

Concepts

To effectively leverage your dbt MetricFlow models within Hex, check how key concepts translate between the two platforms.

MetricFlow	Hex
project	model
semantic model	dataset
dimension	dimension
entity (primary, unique, natural, foreign)	dimension
foreign entity	join
measure, if create_metric is set to “true”	measure
metric	measure

Prepare the model for import into Hex

In order to successfully import dbt MetricFlow files into Hex, the following conditions are required:

1. Specify model paths

   In order to import the dbt MetricFlow YAML files correctly, each semantic model needs to specify the underlying data warehouse table. This is specified as table: <value> in a config.meta.hex section for the dataset:

   model: ref('customers')
   config:
     meta:
       hex:
         table: analytics.prod_core.customers

   The value should be in the format database.schema.table or schema.table. This is currently case-insensitive (with no quotation (””) characters).

2. Ensure schema is up to date

   Verify that all tables and columns used in the model are present in the data connection's schema. It is recommended to add the model columns, refresh the schema, and then reference those newly added columns in the semantic model.

   Consider refreshing the data connection’s schema on a schedule.

   • If the semantic model is referencing a column that doesn’t yet exist in the data connection schema, then the import will skip any references column because Hex doesn’t have the correct type information. Add in a column type annotation as a workaround.
   • Column type annotations

     • If you’d prefer to avoid refreshing your data connection schema within Hex, you can specify the types directly in your dimension definitions like so:

       
       - name: "Customer Name"
         expr: "customer_name"
         type:  "categorical"  # @dtype: string
                      
       • dtype is one of: numeric, string, boolean, date, timestamp, timestamptz

Supported Features

Not all MetricFlow features translate directly in Hex; check the following table to see what is supported.

Feature	Supported
Dimension features
Categorical, Time, Entity, Boolean, Numeric	✅
Measure features
Sum, Count, Count distinct, Average, Min, Max, Median, Sum boolean	✅
Percentile	❌
Measures with non-additive dimensions	✅
Metric features
Simple	✅
Cumulative	✅
Window	✅
Grain to date	✅
Ratio	✅
Derived	✅
Offsets for Ratio and Derived	✅
Conversion	❌
Metrics with fill_nulls_with	✅
Metric filters	✅
Other features
Time spines	✅