Data Dictionary and Metric Lineage Builder
Document analytics definitions, metric formulas, source tables, owners, and lineage so teams stop debating numbers in meetings.
Prompt Template
You are an analytics governance lead creating a practical data dictionary and metric lineage document for a business team. **Business domain:** [sales / marketing / product / finance / support / operations] **Metrics to document:** [list metrics] **Source systems:** [CRM, billing, product analytics, data warehouse, spreadsheets, etc.] **Known confusion:** [conflicting definitions, dashboard mismatch, ownership gaps] **Warehouse or BI tools:** [Snowflake, BigQuery, Looker, Tableau, dbt, Metabase, etc.] **Audience:** [executives / analysts / RevOps / product managers / finance] **Update frequency:** [daily / weekly / monthly / ad hoc] **Governance maturity:** [none / informal / documented / formal data council] Create: 1. **Data dictionary table** — metric name, plain-English definition, formula, grain, filters, exclusions, owner, refresh cadence, source system, and trusted dashboard. 2. **Metric lineage map** — upstream systems, transformations, warehouse tables, semantic layer, dashboards, and downstream decisions. 3. **Definition decisions needed** — ambiguous terms, recommended default, alternatives, and stakeholder to approve. 4. **Quality checks** — tests for nulls, duplicates, freshness, reconciliation, and threshold alerts. 5. **Change management process** — how to request metric changes, version definitions, notify users, and deprecate old dashboards. 6. **Example SQL/dbt documentation** — sample YAML or SQL comments for one metric. 7. **Adoption plan** — how to roll this out so teams actually use it. 8. **Meeting agenda** — 45-minute metric alignment workshop. Focus on clarity for business users while preserving enough technical detail for analysts.
Example Output
Data Dictionary — Revenue Metrics
| Metric | Definition | Formula | Grain | Exclusions | Owner | Source | Trusted Dashboard |
|---|---|---|---|---|---|---|---|
| Net MRR | Monthly recurring revenue after contraction and churn | starting_mrr + expansion - contraction - churn | account/month | one-time fees, tax, credits | Finance Ops | Stripe + Salesforce | Exec Revenue Dashboard |
| Logo Churn Rate | % of customer accounts that canceled in period | canceled logos / starting logos | account/month | trials, test accounts | RevOps | Salesforce | Retention Dashboard |
Metric Lineage: Net MRR
Stripe subscriptions → Fivetran sync → `raw.stripe_subscriptions` → dbt model `fct_subscription_mrr` → semantic layer `revenue_metrics` → Looker tile “Net MRR Trend” → board reporting.
Definition Decision Needed
**Question:** Should paused subscriptions count as churn?
**Recommendation:** Exclude from logo churn for 30 days, then count as churn if not reactivated.
**Approver:** VP Finance + VP Customer Success.
dbt YAML Example
metrics:
- name: net_mrr
label: Net MRR
type: sum
sql: net_mrr_amount
timestamp: month_start
owner: finance_ops
Tips for Best Results
- 💡Start with the metrics that cause arguments in executive meetings — documentation has immediate ROI there.
- 💡Every metric needs an owner; otherwise definitions decay quietly.
- 💡Document exclusions as carefully as formulas because most metric disputes hide there.
- 💡Link each metric to a trusted dashboard so people stop screenshotting three different truths.
Related Prompts
Dataset Summary and Insights
Paste or describe a dataset and get an instant summary of key statistics, patterns, anomalies, and actionable insights.
SQL Query Writer for Business Reports
Generate SQL queries for common business reporting needs — revenue trends, cohort analysis, funnel metrics, and more.
Dashboard KPI Definition Framework
Define the right KPIs for your business dashboard with clear formulas, targets, and data sources.