Skip to main content

Hands-on: build and validate a sales KPI

You'll create a certified metric view and confirm the same number lands in SQL, a dashboard, and Genie, in 30 to 45 min.

Prereqs: Business Semantics overview, a serverless SQL warehouse, CREATE on a target schema

What you'll build: a sales analytics metric view sourced from samples.tpch.orders, joined out to customers and geography. By the end you'll have checked Total Revenue, Unique Customers, and Avg Order Value in SQL, in an AI/BI dashboard, and in a Genie space, and confirmed they all agree.

1. Create the metric view​

Open a SQL editor. Set your catalog and schema, then run the create statement. The body is YAML, so indentation matters.

USE CATALOG my_catalog;
USE SCHEMA my_schema;

CREATE OR REPLACE VIEW orders_metrics
WITH METRICS LANGUAGE YAML AS $$
version: 1.1

source: SELECT * FROM samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    'on': o_custkey = c_custkey
    joins:
      - name: nation
        source: samples.tpch.nation
        'on': c_nationkey = n_nationkey

filter: o_orderdate >= '1995-01-01'

comment: |-
  Sales analytics metric view for order performance analysis.
  Joins orders with customers and geography.
  Owner: Analytics Team
  Last updated: 2025-01-15

dimensions:
  - name: order_date
    expr: o_orderdate
    display_name: Order Date
  - name: order_month
    expr: "DATE_TRUNC('MONTH', o_orderdate)"
    display_name: Order Month
  - name: order_year
    expr: YEAR(o_orderdate)
    display_name: Order Year
  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END
    display_name: Order Status
    synonyms:
      - status
      - fulfillment status
  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"
    display_name: Priority
  - name: customer_name
    expr: customer.c_name
    display_name: Customer Name
  - name: market_segment
    expr: customer.c_mktsegment
    display_name: Market Segment
    synonyms:
      - segment
      - industry
  - name: customer_nation
    expr: customer.nation.n_name
    display_name: Country
    synonyms:
      - nation
      - country

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)
    display_name: Order Count
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: total_revenue
    expr: SUM(o_totalprice)
    display_name: Total Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - revenue
      - sales
  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: Unique Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)
    display_name: Avg Order Value
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - AOV
  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)
    display_name: Revenue per Customer
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')
    display_name: Open Order Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - backlog
  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')
    display_name: Fulfilled Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day
    display_name: 7-Day Rolling Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
$$;

2. Grant access and certify​

GRANT SELECT ON VIEW my_catalog.my_schema.orders_metrics TO `analysts`;
ALTER VIEW my_catalog.my_schema.orders_metrics SET TAGS ('certified' = 'true');

Grant to a group, not to individual users. If the owner isn't already a service principal or a shared group, set it in Catalog Explorer. A view owned by one person becomes a problem the day that person leaves.

3. Validate in three surfaces​

This is the point of the exercise: ask for the same metric three ways and watch the numbers line up.

SQL editor​

SELECT
  `Order Month`,
  `Market Segment`,
  MEASURE(`Total Revenue`),
  MEASURE(`Unique Customers`),
  MEASURE(`Avg Order Value`)
FROM my_catalog.my_schema.orders_metrics
WHERE `Order Month` BETWEEN '1995-01-01' AND '1995-12-31'
GROUP BY ALL
ORDER BY `Order Month`;

Now spot-check it. Run the equivalent raw aggregation against samples.tpch.orders joined to samples.tpch.customer by hand. The totals should match the metric view exactly. If they don't, the metric definition and your mental model have diverged, and that's worth catching now rather than in a board deck.

AI/BI dashboard​

In a dashboard, add a dataset bound to my_catalog.my_schema.orders_metrics and a tile showing Total Revenue by Order Month. The currency formatting carries over from the YAML, so there's nothing extra to configure.

Genie space​

Add my_catalog.my_schema.orders_metrics to a Genie space, then ask it in plain English:

  • "What was revenue by month in 1995?"
  • "Show me backlog by country." This one leans on your backlog synonym for Open Order Revenue and the country synonym for Country.
  • "How many unique customers placed fulfilled orders by market segment?"

Genie picks the metric view and returns the same totals you saw in SQL. The synonyms are what let a business user ask for "backlog" instead of open_order_revenue.

Done when​

  • All three surfaces return the same Total Revenue for the same filter
  • A business partner recognizes the metric by its display name, not the column name
  • The view is owned by a group and tagged certified=true

Where people trip​

Genie does not pick the metric view

Check that the object is actually in the space, that the user has SELECT, and that the metric has clear synonyms and a comment. When Genie keeps reaching for the wrong measure, add space-level instructions to point it at the right one.

SQL and dashboard numbers do not match

Reconcile the time zone, the fiscal vs. calendar boundaries, and any row filters in the dashboard query. The metric view's filter: clause is the official exclusion. A dashboard-level filter must not contradict it, or you're back to two definitions of the same number.

MEASURE() references fail to resolve

avg_order_value and revenue_per_customer are composed measures: they call MEASURE(total_revenue) and MEASURE(order_count). Both referenced measures have to be defined in the same metric view, and the names are case-sensitive, so check that they match exactly.

Next​