Master BI data modeling vocabulary: semantic layer, business metric definitions, date table spine, circular dependency in measures, dbt metrics — the language of modern BI data modeling.
0 / 5 completed
1 / 5
The BI architect says: 'The semantic layer abstracts the SQL so business users never write raw queries.' What is the purpose of a semantic layer?
The semantic layer (also called a business layer or metrics layer) sits between the data warehouse and the BI tool. It maps business concepts — 'revenue', 'active customers', 'churn rate' — to the underlying SQL logic. This means business users can query data using familiar terms without knowing table structures, and metric definitions are centralised so everyone uses the same definition.
2 / 5
The data team writes in the documentation: 'The business metric is defined as: Monthly Active Users = distinct user_id count where last_login > 30 days ago.' Why is a formal metric definition important?
A formal metric definition specifies exactly how a metric is calculated — which tables, which filters, which aggregation. Without this, different teams may calculate the same metric differently (e.g., 'active users' meaning different things to marketing and product). Centralising metric definitions in a semantic layer or dbt metrics file ensures a single source of truth.
3 / 5
The data engineer says: 'The date table is the spine of the model — every fact table joins to it.' Why is a date table (calendar table) so important in BI data models?
The date table (or calendar table) is a reference table containing every calendar date, along with attributes like week number, month name, fiscal period, and whether the date is a holiday. It is called the 'spine' of the model because all time-based fact tables join to it. It enables time intelligence functions (year-to-date, same period last year, rolling 90-day) and ensures that periods with no data still appear in reports.
4 / 5
The analyst reports an error: 'This measure has a circular dependency.' What is a circular dependency in a BI data model?
A circular dependency in BI measures occurs when Measure A's definition references Measure B, and Measure B's definition references Measure A (directly or through a chain). This creates a logical loop that the calculation engine cannot resolve. It typically arises from poorly designed metric hierarchies and must be resolved by restructuring the dependencies.
5 / 5
The dbt developer says: 'We define our metrics in the dbt metrics file so they are consistent across all BI tools.' What is the dbt metrics layer?
The dbt Semantic Layer (formerly dbt metrics) allows data teams to define business metrics — including their calculation logic, time grains, dimensions, and filters — in YAML files within the dbt project. BI tools that integrate with the dbt Semantic Layer query these metric definitions rather than writing their own SQL, ensuring consistent metric calculations across Tableau, Looker, Metabase, and other tools.