It would be great if dbt had some way of supporting Aggregate Awareness. i.e. if there is a model in day level, and an aggregate model in week level, depending on whether the query is in day, week, or month level, if dbt semantic layer could automatically pick the right table, it can significantly improve performance.
This is possible
Senario :
We are having two semantic models built on views with different granularities, one at the day level and the other at the hour level
We can implement aggregate awareness in the dbt semantic layer by defining each semantic model with its corresponding granularity and setting up relationships between them. The dbt semantic layer will then dynamically generate queries that use the most appropriate model based on the requested level of aggregation.
The Semantic Layer :
semantic_models:
- name: sales_by_day
model: ref(‘view_sales_day_level’) defaults: agg_time_dimension: sales_date dimensions:
-
- name: sales_date
type: time type_params: time_granularity: day measures:
-
- name: total_sales
agg: sum expr: sales_amount
- name: sales_by_hour
model: ref(‘view_sales_hour_level’) defaults: agg_time_dimension: sales_datetime dimensions:
-
- name: sales_datetime
type: time type_params: time_granularity: hour measures:
-
- name: total_sales
agg: sum expr: sales_amount
metrics:
- name: total_sales
description: “Total sales amount dynamically aggregated” type: simple model: sales_by_day label: “Total Sales” type_params: measure: total_sales time_grains:
-
-
day
-
hour
-
aggregate_overrides: hour: model: sales_by_hour
Explanation:
The metric total_sales by default uses the sales_by_day semantic model and its total_sales measure.
The time_grains property declares the supported granularities (day and hour).
The aggregate_overrides section informs the semantic layer to use the sales_by_hour model instead, when the query requests aggregation at the hour level granularity .