Metric Layer Design Prompts
This drill evaluates metric governance decisions in system-design interviews.
Hard Prompt #
Design a metric layer for revenue, margin, retention, and conversion used by 5 teams with different dashboard tools.
Must Cover #
- contract fields and ownership
- semantic interface and allowed dimensions
- versioning/deprecation policy
- release and rollback flow
- reconciliation and incident checks
Runnable Contract Check #
Create a canonical monthly metric output and validate that every row has deterministic contracts. The runnable check below demonstrates the contract shape for one metric (net revenue); margin, retention, and conversion are design-discussion only here: the seed has no cost, subscription, or session data to compute them.
Loading SQL editor...
sql
-- Contract validation: no duplicate metric rows per grain
WITH monthly_metrics AS (
SELECT
DATE_TRUNC('month', o.order_date) AS month_start,
'net_revenue' AS metric_name,
SUM(oi.line_total) - SUM(COALESCE(r.return_amount, 0)) AS metric_value
FROM ecom_orders o
JOIN ecom_order_items oi
ON oi.order_id = o.order_id
LEFT JOIN ecom_returns r
ON r.order_item_id = oi.order_item_id
WHERE o.order_status = 'completed'
GROUP BY DATE_TRUNC('month', o.order_date)
)
SELECT month_start, metric_name, COUNT(*) AS row_count
FROM monthly_metrics
GROUP BY month_start, metric_name
HAVING COUNT(*) > 1;Scoring Rubric #
- governance completeness (35%)
- practical rollout strategy (25%)
- risk awareness and rollback clarity (25%)
- communication (15%)