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.

dim_dealerregion, channeldim_productsegment, rimfact_sales_lineone row per linefact_stock_dailyone row per dayregion_accesswho sees whatsales_mvrevenue, margin %,year over yearstock_mvunits on hand (last)AI/BI dashboardGenie spacePower BIagent toolrow filter

Four gold tables, one entitlements table, two metric views, four readers.

Not yet run on Databricks. The expected numbers were worked out by hand from the sample rows. Run the scripts in order and compare; tell us if anything differs.
  1. Agree the metrics first. Before any table, write the questions and the metric each needs.
    QuestionMetricSliced by
    Net revenue by region and monthnet_revenueregion, invoice_month
    Margin % by segmentmargin_pctsegment
    Growth on the same month last yearyoy_growth_pctinvoice_month (+ region)
    Active dealers in a monthactive_dealersinvoice_month, region
    Average invoice valueavg_invoice_valueany
    Units on hand at month endunits_on_handsnapshot_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.

  2. 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);
  3. 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
    $$;
  4. 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;
    QuerySliceResultWhy
    Q1North, 2025-01122,400.00margin 0.2549 (31,200 margin; cancelled I-007 left out)
    Q1South, 2025-0196,600.00margin 0.2360 (105,000 minus an 8,400 return)
    Q2North, 2025-01122,400 vs 40,000growth 2.06 (206%)
    Q2South, 2025-0196,600 vs 80,000growth 0.2075 (20.75%)
    Q32025, all regions268,100 revenue, 67,800 margin0.2529: summed first, divided last
    Q4North and South, 2025-0135 and 10the 31 Jan values, not 75 and 22
  5. 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`;
  6. 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.
  7. Serve it. An AI/BI dashboard with datasets on the metric views (one tile = one MEASURE query). A Genie space over sales_mv and stock_mv only, 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).
  8. 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.
  9. 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.

← 8 · Production

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)

Metric views change every few months. Confirm runtime minimums and preview status before a design depends on them.