Deep Technical Series: Building a Modern Data Warehouse and Data Lake

After building the Gold layer of your dimensional model, the next step is to expose clean, consistent business logic to analysts, BI users, AI agents, and downstream applications. This requires a semantic layer, which defines measures, metrics, business-friendly names, hierarchies, and security rules on top of your warehouse or lakehouse.

A strong semantic layer prevents KPI inconsistencies, eliminates repeated logic in dashboards, and provides a governed, reliable analytical surface. Whether you deploy this layer in Power BI, Fabric, LookML, dbt Metrics, Databricks SQL, or Snowflake’s emerging semantic features, the architectural principles are the same.

---

What the Semantic Layer Does

A semantic layer sits between your Gold tables and your BI tools. It provides:

• Consistent metric definitions
• Business-friendly naming
• Logical relationships between facts and dimensions
• Data security and row-level permissions
• A unified model for all dashboards
• Governed logic shared across the organization

A semantic layer is required for enterprise-scale analytics because it prevents teams from redefining KPIs differently in every report.

Microsoft provides one of the clearest overviews of semantic models in Power BI
Microsoft Semantic Models.

---

Where the Semantic Layer Lives Across Platforms

Every cloud and BI tool implements the semantic layer differently, but all follow the same core principles.

Platform	Semantic Layer Type
Microsoft Fabric / Power BI	Tabular Semantic Model
Looker / LookML	Modeling layer of Views and Explores
dbt Metrics / MetricFlow	Metric definitions built in YAML
Databricks SQL	Unity Catalog plus SQL Semantic layer
Snowflake	SQL views, Dynamic Tables, and native semantic features in preview
BigQuery	Looker semantic layer, BI Engine Acceleration
Tableau	Published Data Sources
Qlik	Associative logical model

LookML has long been considered the strongest structured semantic layer
LookML Overview,
while Fabric’s semantic model integrates directly with Power BI
Fabric Semantic Models.

---

How the Semantic Layer Connects to the Gold Layer

Your Gold layer includes:

• Dimensional tables (DimCustomer, DimProduct, DimDate, DimStore)
• Fact tables (FctOrderLine, FctSubscription, FctEvents)
• Surrogate keys
• Clean conformed attributes

The semantic layer links these tables into:

• Relationships
• Measures
• Business calculations
• Hierarchies
• Columns exposed with business-readable names

The result is a governed model analysts can query without writing SQL.

---

Design Principles of a Strong Semantic Layer

1. Define Business Measures Once

Business logic must be centralized in one reusable location.

Example:
Revenue should never be written separately in each dashboard. It must be created once as a semantic-layer measure.

dbt Metrics provide a metric definition system that enforces consistency
dbt Metrics Documentation.

Power BI and Fabric use DAX measures to accomplish the same goal
Power BI Semantic Models.

---

2. Hide Technical Columns

Analysts should not see:

• Surrogate keys
• Natural key columns
• SCD Type 2 metadata
• EffectiveFrom / EffectiveTo
• Load timestamps
• Audit fields

Only expose clean attributes like:

• Customer Name
• Email
• Product Category
• Order Date
• Region

This dramatically improves usability.

---

3. Define Relationships Explicitly

Relationships must follow your dimensional model:

• Facts connect to dimensions on surrogate keys
• Date dimensions always use a dedicated DateKey
• Snowflake schemas maintain multiple relationships
• Star schemas provide simplified navigation

Power BI and Fabric handle these in the model view
Power BI Modeling.

LookML uses Explores to define them inside a model file
LookML Explore.

---

4. Build Hierarchies for Navigation

Common hierarchies include:

Date

• Year
• Quarter
• Month
• Day

Product

• Category
• Subcategory
• SKU

Geography

• Country
• Region
• State
• City

Hierarchies dramatically improve drill-down behavior in BI tools.

---

5. Build Aggregation Tables for Performance

Large fact tables often require pre-aggregated versions to accelerate BI queries.

Examples:

• Daily revenue summary
• Monthly subscription counts
• Customer lifetime value per quarter

Fabric supports aggregation tables at the semantic model level
Power BI Aggregations.

Databricks uses Delta Live Tables and cached tables
Databricks SQL.

BigQuery accelerates aggregates through BI Engine
BigQuery BI Engine.

---

How the Semantic Layer Is Implemented Across Platforms

---

Fabric / Power BI Semantic Layer

Fabric uses the Tabular engine. It is the most widely adopted semantic layer globally.

Steps

1. Import Dim and Fact tables from Lakehouse or Warehouse
2. Create relationships
3. Create DAX measures
4. Hide surrogate and technical columns
5. Apply RLS roles
6. Publish the semantic model

Example Revenue Measure (DAX)

Revenue :=
SUM ( fct_order_line[ExtendedAmount] )

Microsoft’s modeling guide covers semantic model design
Power BI Modeling Best Practices.

---

Looker / LookML Semantic Layer

LookML is a declarative modeling language and one of the most structured semantic layers in the industry
LookML Overview.

LookML Dimension Example

dimension: customer_name {
  type: string
  sql: ${TABLE}.customer_name ;;
}

LookML Measure Example

yaml
measure: revenue {
  type: sum
  sql: ${extended_amount} ;;
}

LookML Explore (Joins)

yaml
explore: orders {
  join: customers {
    sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
  }
}

LookML enforces consistent logic across all dashboards.

---

dbt Metrics / MetricFlow

dbt provides a metric layer built in YAML
dbt Metrics Documentation.

Example Metric

metrics:
  - name: revenue
    type: sum
    sql: extended_amount
    timestamp: order_date
    dimensions:
      - customer_id
      - product_id

dbt then compiles queries using MetricFlow:

SELECT *
FROM {{ metrics.calculate(metric('revenue'), grain='month') }}

This provides transparency and version control for metrics.

---

Databricks SQL Semantic Layer

Databricks semantic models are defined using Unity Catalog and SQL views
Databricks SQL.

Example Semantic View

CREATE VIEW semantic.revenue_per_customer AS
SELECT
  c.CustomerName,
  SUM(f.ExtendedAmount) AS Revenue
FROM gold.fct_order_line f
JOIN gold.dim_customer c ON f.CustomerKey = c.CustomerKey
GROUP BY c.CustomerName;

Databricks SQL exposes this to Power BI, Tableau, and Looker.

---

Snowflake Semantic Layer

Snowflake’s ecosystem includes:

• Dynamic Tables
• Materialized Views
• Secure Views
• Native Semantic Layer (Preview)

Dynamic tables can pre-aggregate measures
Snowflake Dynamic Tables.

Secure views protect data with masking
Snowflake Data Masking.

---

BigQuery Semantic Layer

BigQuery relies on:

• Looker semantic models
• BI Engine acceleration
  BigQuery BI Engine

BigQuery’s open ecosystem integrates seamlessly with Looker Explore models and dbt Metrics.

---

Designing Conformed Metrics

A strong semantic model defines metrics such as:

• Revenue
• Gross Margin
• CAC
• Churn
• Retention
• Daily Active Users
• Monthly Recurring Revenue

Metrics must be:

• Versioned
• Properly documented
• Backed by fact tables
• Dimensionally consistent

The semantic layer prevents accidental redefinition of KPIs.

---

Security in the Semantic Layer

Security belongs in the semantic layer, not the warehouse.

Includes:

• Row-level security (RLS)
• Object-level security
• Masking attributes
• Tenant-based filtering

Power BI implements RLS roles
Power BI Row Level Security.

Looker applies Access Filters
Looker Access Filters.

Databricks uses Unity Catalog permissions
Unity Catalog Security.

---

Workshops

---

Novice Workshop: Build Your First Semantic Model (Fabric)

1. Import DimCustomer and FctOrderLine from Lakehouse
2. Create relationships
3. Create Revenue and Customer Count DAX measures
4. Create a Date hierarchy
5. Build a simple report

---

Intermediate Workshop: Build a LookML Semantic Layer

1. Define LookML views for customers and orders
2. Build a measure for revenue
3. Create an Explore joining both views
4. Validate the explore in the Looker UI

---

Advanced Workshop: Build a dbt Metrics Layer + Fabric Semantic Model

1. Build dbt metrics for revenue and churn
2. Generate MetricFlow queries at day and month grain
3. Build a Fabric semantic model using the same fact tables
4. Compare results to ensure consistency
5. Publish documentation

---

References

Microsoft Semantic Models
https://learn.microsoft.com/en-us/power-bi/transform-model/semantic-models

Power BI Modeling
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

Power BI Aggregations
https://learn.microsoft.com/en-us/power-bi/guidance/aggregations-overview

LookML Overview
https://cloud.google.com/looker/docs/lookml-intro

LookML Explore
https://cloud.google.com/looker/docs/reference/explore-params

dbt Metrics
https://docs.getdbt.com/docs/build/metrics

Databricks SQL
https://docs.databricks.com/sql/index.html

Snowflake Dynamic Tables
https://docs.snowflake.com/en/user-guide/dynamic-tables

Snowflake Data Masking
https://docs.snowflake.com/en/user-guide/security-column-intro

BigQuery BI Engine
https://cloud.google.com/bi-engine/docs

Unity Catalog Security
https://docs.databricks.com/data-governance/index.html