Skip to content

Metrics Layer

The Metrics Layer (Phase 7 of the Lakehouse roadmap, now complete) provides canonical, reusable metric definitions as DuckDB views. Instead of each dashboard card containing its own raw SQL for calculating revenue or AOV, all cards reference shared metric views that enforce consistent business rules.

Why a Metrics Layer

Without it, the same metric is calculated differently across dashboards:

  • "Revenue" might include or exclude cancelled orders depending on who wrote the query
  • Average order value calculations get copy-pasted and drift over time
  • New analysts have no way to discover what metrics exist

The metrics layer solves this with a single canonical SQL definition per metric, versioned in code.

Available Metrics

7 metrics at 3 time grains (daily, weekly, monthly) = 21 DuckDB views:

Metric View Pattern Definition
Revenue _metrics.revenue_{daily,weekly,monthly} SUM(total_price) excluding cancelled/refunded
AOV _metrics.aov_{daily,weekly,monthly} AVG(total_price) excluding cancelled/refunded
Order Count _metrics.order_count_{daily,weekly,monthly} COUNT(DISTINCT order_id) excluding cancelled/refunded
Customer Count _metrics.customer_count_{daily,weekly,monthly} COUNT(DISTINCT user_id) excluding cancelled/refunded
Repeat Rate _metrics.repeat_rate_{daily,weekly,monthly} % of customers with more than one order
Fulfillment Rate _metrics.fulfillment_rate_{daily,weekly,monthly} % of orders completed/delivered
Churn Rate _metrics.churn_rate_{daily,weekly,monthly} % of customers with no order in 90 days

Usage

-- Query revenue by restaurant
SELECT metric_date, SUM(revenue) AS total_revenue
FROM _metrics.revenue_daily
WHERE restaurant_id = '...'
GROUP BY metric_date
ORDER BY metric_date;

The _metrics.all_metrics_daily unified view combines all 7 metrics into a single wide table, enabling the Metrics Registry dashboard to show cross-metric comparisons and drill-down trend charts from one query.

Metrics Registry Dashboard

The Metrics Registry Metabase dashboard provides a browsable catalog of all defined metrics:

  • Metric name, description, and SQL definition
  • Time grain selector (daily / weekly / monthly)
  • Trend chart per metric with drill-down to restaurant-level
  • Owner attribution
  • Filters by metric name (dropdown)

How Metrics Are Defined

Each metric is a SQL file in metabase/metrics/ (e.g., revenue_daily.sql). The seed script reads these files, creates DuckDB views in the _metrics schema, and registers them in the metric_definitions table.

Adding a new metric is a code change: write a SQL file, add a registry entry, and re-run the seed script.

Key Files

  • smackz-lakehouse/metabase/metrics/ -- SQL view definitions (21 files)
  • smackz-lakehouse/metabase/seed/metrics.py -- Metric registration logic
  • smackz-lakehouse/docs/Lakehouse-Metrics-Layer-FRD.md -- Full FRD