Semantic models are the foundation for data definition in MetricFlow, which powers the dbt Semantic Layer:
- Think of semantic models as nodes connected by entities in a semantic graph.
- MetricFlow uses YAML configuration files to create this graph for querying metrics.
- Each semantic model corresponds to a dbt model in your DAG, requiring a unique YAML configuration for each semantic model.
- You can create multiple semantic models from a single dbt model, as long as you give each semantic model a unique name.
- Configure semantic models in a YAML file within your dbt project directory.
- Organize them under a
metrics:folder or within project sources as needed.
Semantic models have 6 components and this page explains the definitions with some examples:
|Name||Choose a unique name for the semantic model. Avoid using double underscores (__) in the name as they're not supported.||Required|
|Description||Includes important details in the description||Optional|
|Model||Specifies the dbt model for the semantic model using the ||Required|
|Defaults||The defaults for the model, currently only ||Required|
|Entities||Uses the columns from entities as join keys and indicate their type as primary, foreign, or unique keys with the ||Required|
|Primary Entity||If a primary entity exists, this component is Optional. If the semantic model has no primary entity, then this property is required.||Optional|
|Dimensions||Different ways to group or slice data for a metric, they can be ||Required|
|Measures||Aggregations applied to columns in your data model. They can be the final metric or used as building blocks for more complex metrics||Optional|
|Label||The display name for your semantic model ||Optional|
Semantic models components
The complete spec for semantic models is below:
- name: the_name_of_the_semantic_model ## Required
description: same as always ## Optional
model: ref('some_model') ## Required
defaults: ## Required
agg_time_dimension: dimension_name ## Required if the model contains dimensions
entities: ## Required
- see more information in entities
measures: ## Optional
- see more information in measures section
dimensions: ## Required
- see more information in dimensions section
if the semantic model has no primary entity, then this property is required. #Optional if a primary entity exists, otherwise Required
The following example displays a complete configuration and detailed descriptions of each field:
- name: transaction # A semantic model with the name Transactions
model: ref('fact_transactions') # References the dbt model named `fact_transactions`
description: "Transaction fact table at the transaction level. This table contains one row per transaction and includes the transaction timestamp."
entities: # Entities included in the table are defined here. MetricFlow will use these columns as join keys.
- name: transaction
- name: customer
dimensions: # dimensions are qualitative values such as names, dates, or geographical data. They provide context to metrics and allow "metric by group" data slicing.
- name: transaction_date
- name: transaction_location
measures: # Measures are columns we perform an aggregation over. Measures are inputs to metrics.
- name: transaction_total
description: "The total value of the transaction."
- name: sales
description: "The total sale of the transaction."
- name: median_sales
description: "The median sale of the transaction."
- name: customers # Another semantic model called customers.
description: "A customer dimension table."
- name: customer
- name: first_name
Define the name of the semantic model. You must define a unique name for the semantic model. The semantic graph will use this name to identify the model, and you can update it at any time. Avoid using double underscores (__) in the name as they're not supported.
Includes important details in the description of the semantic model. This description will primarily be used by other configuration contributors. You can use the pipe operator
(|) to include multiple lines in the description.
Specify the dbt model for the semantic model using the
Defaults for the semantic model. Currently only
agg_time_dimension represents the default time dimensions for measures. This can be overridden by adding the
agg_time_dimension key directly to a measure - see Dimensions for examples.
To specify the entities in your model, use their columns as join keys and indicate their
type as primary, foreign, or unique keys with the type parameter.
MetricFlow requires that all dimensions be tied to an entity. This is to guarantee unique dimension names. If your data source doesn't have a primary entity, you need to assign the entity a name using the
primary_entity: entity_name key. It doesn't necessarily have to map to a column in that table and assigning the name doesn't affect query generation.
You can define a primary entity using the following configs:
- name: bookings_monthly
- Entity types
- Sample config
Here are the types of keys:
- Primary — Only one record per row in the table, and it includes every record in the data platform.
- Unique — Only one record per row in the table, but it may have a subset of records in the data platform. Null values may also be present.
- Foreign — Can have zero, one, or multiple instances of the same record. Null values may also be present.
- Natural — A column or combination of columns in a table that uniquely identifies a record based on real-world data. For example, the
sales_person_idcan serve as a natural key in a
This example shows a semantic model with three entities and their entity types:
order (foreign), and
To reference a desired column, use the actual column name from the model in the
name parameter. You can also use
name as an alias to rename the column, and the
expr parameter to refer to the original column name or a SQL expression of the column.
- name: transaction
- name: order
- name: user
expr: substring(id_order FROM 2)
You can refer to entities (join keys) in a semantic model using the
name parameter. Entity names must be unique within a semantic model, and identifier names can be non-unique across semantic models since MetricFlow uses them for joins.
Dimensions are the different ways you can group or slice data for a metric. It can be time-consuming and error-prone to anticipate all possible options in a single table, such as region, country, user role, and so on.
MetricFlow simplifies this by allowing you to query all metric groups and construct the join during the query. To specify dimensions parameters, include the
name (either a column or SQL expression) and
time). Categorical groups represent qualitative values, while time groups represent dates of varying granularity.
Dimensions are identified using the name parameter, just like identifiers. The naming of groups must be unique within a semantic model, but not across semantic models since MetricFlow, uses entities to determine the appropriate groups. MetricFlow requires all dimensions be tied to a primary entity.
For semantic models with a measure, you must have a primary time group.
Measures are aggregations applied to columns in your data model. They can be used as the foundational building blocks for more complex metrics, or be the final metric itself.
Measures have various parameters which are listed in a table along with their descriptions and types.
|Provide a name for the measure, which must be unique and can't be repeated across all semantic models in your dbt project.||Required|
|Describes the calculated measure.||Optional|
|dbt supports the following aggregations: ||Required|
|Either reference an existing column in the table or use a SQL expression to create or derive a new one.||Optional|
|Non-additive dimensions can be specified for measures that cannot be aggregated over certain dimensions, such as bank account balances, to avoid producing incorrect results.||Optional|
|Specific aggregation properties such as a percentile.||Optional|
|The time field. Defaults to the default agg time dimension for the semantic model.||Optional|
|How the metric appears in project docs and downstream integrations.||Required|
|You can create a metric directly from a measure with ||Optional|
*Available on dbt version 1.7 or higher.
Metric nodes will reflect dependencies on semantic models based on their measures. However, dependencies based on filters should not be reflected in:
- dbt selection syntax
- Visualization of the DAG in dbt-docs and the integrated development environment (IDE).
This is because metrics need to source nodes for their
depends_on attribute from a few different places:
DERIVEDtype metrics should reference
SIMPLEtype metrics should reference
For example, when you run the command
dbt list --select my_semantic_model+, it will show you the metrics that belong to the specified semantic model.
But there's a condition: Only the metrics that actually use measures or derived metrics from that semantic model will be included in the list. In other words, if a metric only uses a dimension from the semantic model in its filters, it won't be considered as part of that semantic model.