Design a finance BI catalog: metric definitions, source contracts, refresh SLAs, and…
- User
Design a finance BI catalog: metric definitions, source contracts, refresh SLAs, and ownership.
Assistant
The user wants a design for a "Finance BI Catalog." This is a metadata management or data governance artifact. The core components requested are:
- Metric Definitions: What are we measuring?
- Source Contracts: Where does the data come from and what is the agreement?
- Refresh SLAs: How often is it updated and by when?
- Ownership: Who is responsible?
Audience/Context:
- Target Audience: Data Analysts, Business Users, Executives, Compliance Officers.
- Tooling: Could be a spreadsheet, a wiki (Confluence), or a dedicated Data Catalog tool (DataHub, Alation, Atlan).
- Goal: To create a structured, actionable template that serves as a single source of truth for financial reporting.
Structure of the Response: I need to provide a comprehensive design framework, not just a definition. I should structure it as follows:
- Overview/Philosophy: Why do we need this? (Trust, Compliance, Speed).
- Catalog Architecture (The Schema): The detailed fields for each section requested.
- Example Entries: Concrete examples make abstract designs understandable.
- Process/Workflow: How is this maintained?
- Technology Stack: Where does this live?
Drafting the Content - Section by Section:
-
1. Metric Definitions:
- Need standard fields: Name, ID, Formula, Description, Granularity (Currency, %), Business Context.
- Self-Correction: Don't just say "Revenue." Break it down into "Gross Revenue" vs "Net Revenue."
-
2. Source Contracts:
- This is interesting. "Source contracts" usually implies the data lineage and the SLA of the source system (e.g., SAP sends data by 5 AM).
- Fields: System Name, Object/Table, Extraction Method (API, CSV), Criticality (Tier 1, 2).
-
3. Refresh SLAs:
- Fields: Frequency, Target Time (T+1 8 AM), Dependency Chain.
-
4. Ownership:
- Two types: Data Steward (Technical) and Business Owner (Definition).
Developing the Example (The "Meat"): I need a table or a structured card layout.
- Metric 1: ARR (Annual Recurring Revenue). Good for SaaS/Finance.
- Metric 2: Operating Margin.
Refining the "Source Contract" concept: I should interpret "Source Contracts" as the agreement between the upstream system (e.g., NetSuite, Salesforce) and the BI platform. It includes schema expectations and delivery times.
Design Approach: I will structure this as a "Data Product" approach.
- Tier 1 Metrics: Board level (Revenue, Burn Rate).
- Tier 2 Metrics: Operational (Headcount Cost).
Structuring the Output:
- Introduction: Purpose of the catalog.
- The Schema (The Blueprint): Detailed fields for the four requested areas.
- The Catalog View (Examples): Visualizing what an entry looks like.
- Governance Model: The lifecycle of the metadata.
Specific Details to Include:
- Metric ID:
FIN_REV_001. - Formula:
SUM(invoices) - SUM(discounts). - Source:
ERP_GL_Table. - SLA: Available by 7:00 AM EST daily.
- Owner: CFO / Head of Data.
Review against constraints:
- Metric Definitions? Yes.
- Source Contracts? Yes.
- Refresh SLAs? Yes.
- Ownership? Yes.
Let's build the "Source Contract" section carefully. It shouldn't just be the table name. It should be: "The ERP guarantees the GL extract is available in S3 by 5 AM."
Final Polish: Use formatting (Markdown tables, bolding) to make it readable. Add a section on Implementation (how to actually build this).
Self-Correction during drafting: Make sure to distinguish between Technical Owner (who fixes the code) and Business Owner (who defines what "Revenue" means). This is a common failure point in finance BI.
Let's write it out. Designing a Finance Business Intelligence (BI) Catalog requires a structure that balances technical precision (for engineers/analysts) with business context (for CFOs/Executives). This catalog acts as the "Contract" between data producers and data consumers.
Below is a comprehensive design framework for a Finance BI Catalog.
1. Catalog Architecture: The Schema
The catalog should be organized into four distinct pillars. Every entry in the catalog follows this taxonomy.
A. Metric Definitions (The "What")
This section defines the business logic. It prevents the classic debate of "Is this GAAP or Non-GAAP revenue?"
- Metric Name: (e.g.,
Annual Recurring Revenue - ARR) - Business Definition: A plain English explanation (e.g., "The annualized value of all active subscription contracts.").
- Calculation Logic: The mathematical formula.
- Formula:
SUM(MRR) * 12 - Inclusions: Includes full-paying customers, trials with a credit card.
- Exclusions: Churned customers, freemium users, one-time setup fees.
- Formula:
- Granularity: Level of detail (e.g., Customer_ID, Contract_ID, or GL_Account).
- Data Type: Currency, Percentage, Integer.
B. Source Contracts (The "Where" & "How")
This defines the handshake between the source system (ERP, CRM) and the Data Warehouse.
- System of Record: The authoritative source (e.g., NetSuite, Salesforce, Workday).
- Raw Object: The specific table or API endpoint (e.g.,
NS_Transaction_Line,SF_Opportunity_History). - Ingestion Method: How we get the data (e.g., Fivetran connector, Custom Python Lambda, dbt staging model).
- Data Quality Checks (DQ):
- Validation:
Revenuecannot be negative. - Referential Integrity: Every
Invoice_IDmust link to a validCustomer_ID.
- Validation:
C. Refresh SLAs (The "When")
This manages expectations regarding data freshness.
- Refresh Frequency: Real-time, Daily, Monthly (Day 3 Close).
- Update Window: The specific time the data is available for consumption (e.g., "07:00 AM EST").
- Dependency Chain: What must happen before this updates? (e.g., "Wait for NetSuite Daily GL Post").
- Tiers:
- Tier 1 (Executive): Must be available by 8 AM; 99.9% uptime.
- Tier 2 (Operational): Must be available by 12 PM; 99% uptime.
D. Ownership & Governance (The "Who")
Who is accountable for accuracy?
- Business Owner: The person who defines the metric (e.g., Controller, VP of Sales).
- Technical Owner: The person who writes the code/maintains the pipeline (e.g., Data Engineer).
- Certification Status:
Draft,Certified(Ready for Board), orDeprecated.
2. The Catalog Interface (Example View)
Here is how these components look when combined into a tangible catalog entry.
Entry ID:
FIN-REV-001Category Attribute Detail 1. Definition Metric Gross Revenue (GAAP) Definition Total income recognized in the general ledger for goods/services delivered in the period. Formula SUM(NetSuite.Invoice_Line.Amount)excluding tax and shipping.Context Used for Board Reporting and Audited Financial Statements. 2. Source System NetSuite (ERP) Raw Table transaction_linejoined withaccounting_periodTransformation dbt_model: fact_revenue_recognizedDQ Rules No nulls in Account_ID;Amountmust balance to Trial Balance.3. SLA Frequency Daily (T+1) Availability Data for Day D is visible in BI by 06:30 AM EST on Day D+1. Dependency Requires NetSuite "End of Day" batch process to complete. 4. Ownership Business Owner Sarah Jenkins (Controller) Tech Owner Data Platform Team (Slack: #data-help) Last Certified 2023-10-01
3. Standardizing Metrics (The Dictionary)
To make the catalog useful, you must standardize definitions across the finance function.
Metric Category Example Metric Definition Common Pitfall (Why you need a catalog) Revenue ARR Annualized value of recurring contracts. Confusing ARR with GAAP Revenue (which includes one-time fees). Costs COGS Direct costs attributable to production. Including non-recurring engineering salaries in COGS. Margins Gross Margin % (Revenue - COGS) / Revenue.Calculating on "Booked" sales rather than "Recognized" revenue. Cash Flow Burn Rate Monthly net cash outflow. Calculating based on "Cash out" vs "Accrued expenses." Headcount FTE Cost Total cost of employee (Salary + Benefits + Tax). Reporting "Headcount" (people) vs "FTE" (full-time equivalent).
4. Implementation Strategy
How do you actually build and distribute this?
Phase 1: The Repository (Technical)
- Tooling: Use dbt (data build tool) for the definitions.
- Why? dbt allows you to write the definition directly in the code (
.ymlfiles). - Action: Every metric column in your data warehouse has a
descriptionfield that serves as the source of truth.
Phase 2: The User Interface (Business)
- Tooling: Use a Data Discovery tool (like DataHub, Atlan, or Notion/Confluence for smaller teams).
- Action: Connect the tool to your dbt repo. This auto-generates a searchable website for non-technical finance users to look up "ARR" and see the definition and owner.
Phase 3: The Service Level Agreement (SLA) Monitoring
- Tooling: Monte Carlo or custom SQL monitors.
- Action: Set up an alert.
- Rule: If
fact_revenueis not updated by 07:00 AM, alert the #finance-ops Slack channel and pause the dashboard with a "Data Stale" banner.
- Rule: If
5. Sample "Source Contract" Document
This is the agreement signed by the ERP team (Source) and the Analytics team (Consumer).
Contract: NetSuite to Snowflake
Producer: Finance Ops Team Consumer: Business Intelligence Team
The Commitment:
- Data Format: The
GL_Transactiontable will be flattened and uploaded to S3 in Parquet format. - Freshness: Files will land in the S3 bucket by 03:00 AM EST daily.
- Schema Stability: Any column changes (renaming, deleting) will be communicated 7 days in advance via JIRA ticket
FIN-CHANGE. - Validity: The sum of
transaction_amountin the extract will match the NetSuite Trial Balance report for the corresponding period.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.