Home › Data engineering › Metric views › 9 · Example
Data engineering · BI and analytics · free guide · 9 · Example
One example end to end: tyre sales to dealers
A tyre maker sells to dealers in two regions. Sales Finance wants one set of numbers for a dashboard, a Genie space for managers, and Power BI for the monthly pack, and each regional manager must see only their region. Here it is, built end to end, small enough to run in a free workspace.
Four gold tables, one entitlements table, two metric views, four readers.
- Agree the metrics first. Before any table, write the questions and the metric each needs.
Question Metric Sliced by Net revenue by region and month net_revenue region, invoice_month Margin % by segment margin_pct segment Growth on the same month last year yoy_growth_pct invoice_month (+ region) Active dealers in a month active_dealers invoice_month, region Average invoice value avg_invoice_value any Units on hand at month end units_on_hand snapshot_month, region Two grains show up straight away: sales happen per invoice line; stock is counted per dealer per day. So: two fact tables, two metric views.
- Build atomic gold. One row per business event, keys declared, region copied onto the facts for cheap security, clustered by what dashboards filter on.
-- Gold, atomic, one row per business event. Catalog 'main' (on Free Edition use 'workspace'). CREATE SCHEMA IF NOT EXISTS main.sales_gold; CREATE OR REPLACE TABLE main.sales_gold.dim_dealer ( dealer_id STRING NOT NULL, dealer_name STRING, city STRING, region STRING, channel STRING, CONSTRAINT pk_dealer PRIMARY KEY (dealer_id) RELY); INSERT INTO main.sales_gold.dim_dealer VALUES ('D1','Sharma Tyres','Delhi','North','Retail'), ('D2','Punjab Wheels','Ludhiana','North','Fleet'), ('D3','Chennai Treads','Chennai','South','Retail'); CREATE OR REPLACE TABLE main.sales_gold.dim_product ( product_id STRING NOT NULL, sku STRING, segment STRING, rim_inches INT, CONSTRAINT pk_product PRIMARY KEY (product_id) RELY); INSERT INTO main.sales_gold.dim_product VALUES ('P1','PCR-185-15','PCR',15), ('P2','TBR-295-22','TBR',22), ('P3','2W-90-17','2W',17); -- One row per invoice line. region is copied from the dealer in gold so the -- row filter needs no join. Returns are negative lines. CREATE OR REPLACE TABLE main.sales_gold.fact_sales_line ( invoice_id STRING, line_no INT, invoice_date DATE, dealer_id STRING, region STRING, product_id STRING, qty INT, net_amount DECIMAL(18,2), cost_amount DECIMAL(18,2), status STRING) CLUSTER BY (invoice_date, region); INSERT INTO main.sales_gold.fact_sales_line VALUES ('I-001',1,'2024-01-10','D1','North','P1', 10, 40000, 30000,'INVOICED'), ('I-002',1,'2024-01-18','D3','South','P2', 4, 80000, 62000,'INVOICED'), ('I-003',1,'2025-01-08','D1','North','P1', 12, 50400, 37200,'INVOICED'), ('I-003',2,'2025-01-08','D1','North','P3', 20, 30000, 21000,'INVOICED'), ('I-004',1,'2025-01-21','D2','North','P2', 2, 42000, 33000,'INVOICED'), ('I-005',1,'2025-01-25','D3','South','P2', 5, 105000, 80000,'INVOICED'), ('I-006',1,'2025-01-28','D3','South','P1', -2, -8400, -6200,'RETURNED'), ('I-007',1,'2025-01-30','D2','North','P1', 50, 210000,150000,'CANCELLED'), ('I-008',1,'2025-02-05','D1','North','P3', 10, 15500, 10500,'INVOICED'), ('I-009',1,'2025-02-14','D3','South','P1', 8, 33600, 24800,'INVOICED'); -- Stock: one row per dealer, product and day (a snapshot, semi-additive). CREATE OR REPLACE TABLE main.sales_gold.fact_stock_daily ( snapshot_date DATE, dealer_id STRING, region STRING, product_id STRING, units_on_hand INT); INSERT INTO main.sales_gold.fact_stock_daily VALUES ('2025-01-30','D1','North','P1',40), ('2025-01-31','D1','North','P1',35), ('2025-01-30','D3','South','P2',12), ('2025-01-31','D3','South','P2',10); - Write the metric views. Atomic measures first, then composed ones; scope in the filter; names and synonyms for Genie; a window for last year; a semi-additive window for stock.
CREATE OR REPLACE VIEW main.sales_gold.sales_mv WITH METRICS LANGUAGE YAML AS $$ version: 1.1 comment: "Tyre sales to dealers, one row per invoice line. Cancelled lines excluded; returns are negative lines. Owner: Sales Finance." source: main.sales_gold.fact_sales_line filter: status <> 'CANCELLED' joins: - name: dealer source: main.sales_gold.dim_dealer on: source.dealer_id = dealer.dealer_id - name: product source: main.sales_gold.dim_product on: source.product_id = product.product_id fields: - name: invoice_date expr: invoice_date display_name: Invoice date - name: invoice_month expr: DATE_TRUNC('MONTH', invoice_date) display_name: Invoice month - name: region expr: source.region display_name: Region synonyms: ['zone', 'sales region'] - name: dealer_name expr: dealer.dealer_name display_name: Dealer - name: channel expr: dealer.channel - name: segment expr: CASE product.segment WHEN 'PCR' THEN 'Passenger car' WHEN 'TBR' THEN 'Truck and bus' WHEN '2W' THEN 'Two-wheeler' ELSE 'Other' END display_name: Segment synonyms: ['category', 'product line'] measures: - name: net_revenue expr: SUM(net_amount) display_name: Net revenue comment: "Invoiced amount after discounts, returns netted off, excluding tax." synonyms: ['revenue', 'sales', 'turnover'] - name: gross_margin expr: SUM(net_amount - cost_amount) display_name: Gross margin - name: margin_pct expr: MEASURE(gross_margin) / NULLIF(MEASURE(net_revenue), 0) display_name: Margin % - name: units expr: SUM(qty) display_name: Units sold - name: invoices expr: COUNT(DISTINCT invoice_id) - name: avg_invoice_value expr: MEASURE(net_revenue) / NULLIF(MEASURE(invoices), 0) display_name: Average invoice value - name: active_dealers expr: COUNT(DISTINCT dealer_id) FILTER (WHERE status = 'INVOICED') display_name: Active dealers - name: net_revenue_ly expr: SUM(net_amount) display_name: Net revenue, same month last year window: - order: invoice_month range: current offset: -12 month - name: yoy_growth_pct expr: (MEASURE(net_revenue) - MEASURE(net_revenue_ly)) / NULLIF(MEASURE(net_revenue_ly), 0) display_name: Growth vs last year % $$; CREATE OR REPLACE VIEW main.sales_gold.stock_mv WITH METRICS LANGUAGE YAML AS $$ version: 1.1 comment: "Dealer stock, one row per dealer, product and day." source: main.sales_gold.fact_stock_daily fields: - name: snapshot_date expr: snapshot_date - name: snapshot_month expr: DATE_TRUNC('MONTH', snapshot_date) - name: region expr: region measures: - name: units_on_hand expr: SUM(units_on_hand) display_name: Units on hand window: - order: snapshot_date range: current semiadditive: last $$; - Ask questions.
-- Q1 Net revenue and margin by region and month SELECT region, invoice_month, MEASURE(net_revenue) AS revenue, MEASURE(margin_pct) AS margin FROM main.sales_gold.sales_mv GROUP BY ALL ORDER BY region, invoice_month; -- Q2 Growth on the same month last year (January 2025 vs January 2024) SELECT region, invoice_month, MEASURE(net_revenue) AS revenue, MEASURE(net_revenue_ly) AS revenue_ly, MEASURE(yoy_growth_pct) AS growth FROM main.sales_gold.sales_mv WHERE invoice_month = DATE'2025-01-01' GROUP BY ALL ORDER BY region; -- Q3 Company margin: ratio of sums, not the average of regional margins SELECT MEASURE(margin_pct) AS company_margin FROM main.sales_gold.sales_mv WHERE year(invoice_date) = 2025; -- Q4 Stock at month end (semi-additive): last day, not the sum of days SELECT region, snapshot_month, MEASURE(units_on_hand) AS units_on_hand FROM main.sales_gold.stock_mv GROUP BY ALL ORDER BY region;Query Slice Result Why Q1 North, 2025-01 122,400.00 margin 0.2549 (31,200 margin; cancelled I-007 left out) Q1 South, 2025-01 96,600.00 margin 0.2360 (105,000 minus an 8,400 return) Q2 North, 2025-01 122,400 vs 40,000 growth 2.06 (206%) Q2 South, 2025-01 96,600 vs 80,000 growth 0.2075 (20.75%) Q3 2025, all regions 268,100 revenue, 67,800 margin 0.2529: summed first, divided last Q4 North and South, 2025-01 35 and 10 the 31 Jan values, not 75 and 22 - Add row-level security on the gold tables, from one entitlements table. Asha now sees only North; Ravi sees both. Users get SELECT on the metric views only.
CREATE SCHEMA IF NOT EXISTS main.security; CREATE OR REPLACE TABLE main.security.region_access (user_email STRING, region STRING); INSERT INTO main.security.region_access VALUES ('asha@example.com','North'), ('ravi@example.com','*'); CREATE OR REPLACE FUNCTION main.security.region_filter(p_region STRING) RETURN EXISTS (SELECT 1 FROM main.security.region_access a WHERE a.user_email = current_user() AND (a.region = p_region OR a.region = '*')); ALTER TABLE main.sales_gold.fact_sales_line SET ROW FILTER main.security.region_filter ON (region); ALTER TABLE main.sales_gold.fact_stock_daily SET ROW FILTER main.security.region_filter ON (region); GRANT SELECT ON VIEW main.sales_gold.sales_mv TO `sales-managers`; GRANT SELECT ON VIEW main.sales_gold.stock_mv TO `sales-managers`; - Decide on speed. With row filters on the facts, materialization is refused. At this size nothing is needed. At real size: clustering is already on; if the CEO dashboard is still slow, build a day × region × segment aggregate table in the gold pipeline with the same row filter and a small metric view over it. Company-wide KPIs with no security can live in a separate, materialized metric view.
- Serve it. An AI/BI dashboard with datasets on the metric views (one tile = one MEASURE query). A Genie space over
sales_mvandstock_mvonly, with example questions and benchmarks ("revenue by zone last month" must equal Q1). Power BI reads through native query with MEASURE() in DirectQuery, or a thin model (step 9). - Test it. The Q1–Q4 numbers become golden-number tests (see the table-driven tests); join-key uniqueness on dim_dealer and dim_product; RLS tests as Asha, Ravi and a user with no row. Commit the YAML, deploy from CI, certify.
- The same model in dbt and Power BI, for comparison. Notice what each needs that the metric view got from Unity Catalog: dbt has no security of its own, and Power BI needs a role when it holds a copy.
dbt Semantic Layer
# models/gold/sales.yml (dbt 1.12+ latest spec; same gold tables) models: - name: fact_sales_line semantic_model: enabled: true agg_time_dimension: invoice_date columns: - name: line_key # invoice_id || '-' || line_no, built in the model entity: { type: primary, name: sales_line } - name: dealer_id entity: { type: foreign, name: dealer } - name: product_id entity: { type: foreign, name: product } - name: invoice_date granularity: day dimension: { type: time } - name: region dimension: { type: categorical } - name: status dimension: { type: categorical } metrics: - name: net_revenue type: simple label: Net revenue agg: sum expr: net_amount filter: "{{ Dimension('sales_line__status') }} != 'CANCELLED'" - name: gross_margin type: simple agg: sum expr: net_amount - cost_amount filter: "{{ Dimension('sales_line__status') }} != 'CANCELLED'" metrics: - name: margin_pct type: ratio label: Margin % numerator: gross_margin denominator: net_revenue - name: yoy_growth_pct type: derived label: Growth vs last year % expr: (net_revenue - net_revenue_ly) / nullif(net_revenue_ly, 0) input_metrics: - name: net_revenue - name: net_revenue offset_window: 1 year alias: net_revenue_ly # Ask: mf query --metrics net_revenue,margin_pct,yoy_growth_pct --group-by sales_line__region,metric_time__month # Note: no RLS here; the warehouse identity running the query decides what rows exist.Power BI DAX
-- Power BI (thin model over the same gold tables, DirectQuery with SSO or Import) Net Revenue := CALCULATE ( SUM ( fact_sales_line[net_amount] ), fact_sales_line[status] <> "CANCELLED" ) Gross Margin := CALCULATE ( SUMX ( fact_sales_line, fact_sales_line[net_amount] - fact_sales_line[cost_amount] ), fact_sales_line[status] <> "CANCELLED" ) Margin % := DIVIDE ( [Gross Margin], [Net Revenue] ) Net Revenue LY := CALCULATE ( [Net Revenue], SAMEPERIODLASTYEAR ( dim_date[date] ) ) Growth vs LY % := DIVIDE ( [Net Revenue] - [Net Revenue LY], [Net Revenue LY] ) Units on Hand := CALCULATE ( SUM ( fact_stock_daily[units_on_hand] ), LASTDATE ( dim_date[date] ) ) -- RLS role 'Region' on fact tables (needed only for Import / Direct Lake copies): -- reads the SAME entitlements table, loaded into the model as region_access VAR me = USERPRINCIPALNAME () RETURN CONTAINS ( region_access, region_access[user_email], me, region_access[region], "*" ) || CONTAINS ( region_access, region_access[user_email], me, region_access[region], fact_sales_line[region] )
What the example shows
Six lessons in one small model
- Agreeing the metrics first decided the gold tables and their grain.
- The filter kept cancelled invoice I-007 (210,000) out of every number, in every tool.
- Margin was composed from sums, so the company margin (25.3%) is summed first and divided last, at any grain.
- Last year and stock needed windows, not new tables.
- Security lived on gold, once; the metric views needed no change to respect it.
- The same model in dbt and DAX is longer and must add the security story itself.
FAQ
Questions people ask
Can I run this example on Databricks Free Edition?
Yes. Use the catalog 'workspace' instead of 'main', run the gold script, then the metric views, then the queries on the serverless SQL warehouse. Row filters need a second user to see the effect.
Why copy region onto the fact table?
So the row filter checks a column on the row itself, without a join per row. The dealer table still holds region for slicing; gold keeps the two in step.
Why two metric views instead of one?
Sales and stock have different grains: invoice lines versus dealer-product-day snapshots. Putting them in one view would make one of them fan out.
Sources
Checked against (11 Nov 2026)
- Databricks: Unity Catalog metric views and the YAML reference
- Materialization · Advanced techniques · Relationships · BI tools · Feature availability
- Row filters and column masks · ABAC evaluation through views (KB)
- dbt semantic models · MetricFlow open-sourced · Apache Ossie
Metric views change every few months. Confirm runtime minimums and preview status before a design depends on them.