Skip to content
TheChatArchiveThe Chat Archive
GLMGLM-5 / Pony Alpha· unknownHugging Face Frontier Reasoning Traces

Design a finance BI catalog: metric definitions, source contracts, refresh SLAs, and…

  1. User

    Design a finance BI catalog: metric definitions, source contracts, refresh SLAs, and ownership.

  2. GLM 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:

    1. Metric Definitions: What are we measuring?
    2. Source Contracts: Where does the data come from and what is the agreement?
    3. Refresh SLAs: How often is it updated and by when?
    4. 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:

    1. Overview/Philosophy: Why do we need this? (Trust, Compliance, Speed).
    2. Catalog Architecture (The Schema): The detailed fields for each section requested.
    3. Example Entries: Concrete examples make abstract designs understandable.
    4. Process/Workflow: How is this maintained?
    5. 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.
    • 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: Revenue cannot be negative.
      • Referential Integrity: Every Invoice_ID must link to a valid Customer_ID.

    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), or Deprecated.

    2. The Catalog Interface (Example View)

    Here is how these components look when combined into a tangible catalog entry.

    Entry ID: FIN-REV-001

    CategoryAttributeDetail
    1. DefinitionMetricGross Revenue (GAAP)
    DefinitionTotal income recognized in the general ledger for goods/services delivered in the period.
    FormulaSUM(NetSuite.Invoice_Line.Amount) excluding tax and shipping.
    ContextUsed for Board Reporting and Audited Financial Statements.
    2. SourceSystemNetSuite (ERP)
    Raw Tabletransaction_line joined with accounting_period
    Transformationdbt_model: fact_revenue_recognized
    DQ RulesNo nulls in Account_ID; Amount must balance to Trial Balance.
    3. SLAFrequencyDaily (T+1)
    AvailabilityData for Day D is visible in BI by 06:30 AM EST on Day D+1.
    DependencyRequires NetSuite "End of Day" batch process to complete.
    4. OwnershipBusiness OwnerSarah Jenkins (Controller)
    Tech OwnerData Platform Team (Slack: #data-help)
    Last Certified2023-10-01

    3. Standardizing Metrics (The Dictionary)

    To make the catalog useful, you must standardize definitions across the finance function.

    Metric CategoryExample MetricDefinitionCommon Pitfall (Why you need a catalog)
    RevenueARRAnnualized value of recurring contracts.Confusing ARR with GAAP Revenue (which includes one-time fees).
    CostsCOGSDirect costs attributable to production.Including non-recurring engineering salaries in COGS.
    MarginsGross Margin %(Revenue - COGS) / Revenue.Calculating on "Booked" sales rather than "Recognized" revenue.
    Cash FlowBurn RateMonthly net cash outflow.Calculating based on "Cash out" vs "Accrued expenses."
    HeadcountFTE CostTotal 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 (.yml files).
    • Action: Every metric column in your data warehouse has a description field 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_revenue is not updated by 07:00 AM, alert the #finance-ops Slack channel and pause the dashboard with a "Data Stale" banner.

    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:

    1. Data Format: The GL_Transaction table will be flattened and uploaded to S3 in Parquet format.
    2. Freshness: Files will land in the S3 bucket by 03:00 AM EST daily.
    3. Schema Stability: Any column changes (renaming, deleting) will be communicated 7 days in advance via JIRA ticket FIN-CHANGE.
    4. Validity: The sum of transaction_amount in the extract will match the NetSuite Trial Balance report for the corresponding period.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.