What does data lineage look like when auditors need to trace board KPIs back to journal entries across 9 entities?

When board KPIs need to reconcile back to journal entries across 9 entities, “data lineage” stops being a nice diagram and becomes an audit requirement. Auditors don’t care how pretty the stack is—they care whether every number on the board deck can be explained, step by step, back to a governed source with clear ownership and controls.

Below is what robust data lineage looks like in that context, how it should behave during an audit, and how we implement it in Keboola so you can defend every KPI across entities without spending weeks in spreadsheets and screenshots.

---

What “auditable” data lineage really means in a 9‑entity finance group

For multi‑entity CFO offices, proper data lineage must answer four questions instantly for any KPI on the board pack:

1. Where did this number come from?
   Exact source systems (ERP, subledgers, banks, Excel, operational systems) and specific tables/files.

2. What happened to it along the way?
   Every transformation: mappings, FX rates, eliminations, reclassifications, group adjustments, and manual topsides.

3. Who owns and approved it?
   Process ownership, access controls, and approvals linked to specific runs and changes.

4. Can I see the journals behind it?
   Drilldown all the way to journal line items, with entity, account, cost center, and document references.

If any of those are answered with “we think” instead of “here’s the exact run, query, and table,” your lineage isn’t audit‑ready.

---

End‑to‑end lineage, from board KPI to journal line

A practical lineage path for a board‑level KPI (say, “Group EBITDA” or “NPL ratio”) across 9 entities looks like this:

1. Reporting KPI → Group reporting model

   • KPI definition in your reporting layer (Power BI, Looker, Tableau, custom app).
   • The metric references a specific semantic model / dataset (e.g., group_financials_consolidated).

2. Group reporting model → Consolidated tables

   • That dataset is backed by a consolidated table in your warehouse or Keboola storage, e.g. fact_pnl_group_consolidated.
   • Lineage links the KPI to this exact table and column, including the run that produced it.

3. Consolidated tables → Entity‑level normalized tables

   • Each group table is the result of transformations combining the 9 entities’ local ledgers, mapped to a group chart of accounts.
   • Lineage shows which transformation (SQL, dbt, Python) produced the consolidated table, and which input tables it consumed.

4. Entity‑level normalized tables → Source extract tables

   • Each entity’s normalized table (e.g., fact_journal_entity_03_normalized) is derived from raw extracts from its ERP / GL / subledgers.
   • Lineage documents how local accounts, cost centers, and dimensions are mapped to the group model.

5. Source extract tables → Raw source systems

   • For each journal line, lineage identifies:
     • The original ERP
     • The extraction method (batch, CDC)
     • The job/run ID and timestamp
     • Any filters or transformations at ingestion (e.g., historical window)

6. Raw source systems → Journal line details

   • At the base, you see the journal entry itself: document number, posting date, entity code, account, amount, FX rate, and local currency.
   • Lineage ties this row back to the exact extraction run and gives auditors what they want: “this line sat in system X, pulled on run Y, used in KPI Z.”

This isn’t just a pretty “from source to dashboard” diagram. In practice, it must be actionable:

• Click KPI → see table → see transformation → see input tables → see source extracts → see original journal rows.
• Every step includes job IDs, timestamps, and owners.

---

How auditors actually use data lineage in this scenario

When auditors arrive for a group audit across 9 entities, lineage gets exercised in three high‑pressure workflows:

1. Sampling: “Show me the path for this KPI and this sample of journal entries”

Typical questions:

• “Take the EBITDA figure for Q4. Show us how it’s calculated and which entities contribute.”
• “Pick 20 random journal lines from Entity 5’s revenue accounts. Show how they roll up into the consolidated revenue figure.”

Lineage must support:

• Top‑down traceability: Board KPI → entity contribution → journal lines.
• Bottom‑up traceability: Journal line → entity P&L → group P&L → board KPI.

In Keboola, that means:

• Every dataset and dependency is tracked as active metadata.
• From any dataset, you can see both upstream (what fed it) and downstream (who consumes it).
• You can export or surface this to auditors as views or PDFs instead of hand‑drawn Visio diagrams.

2. Change impact: “What changed between last year’s audit and this one?”

Auditors ask:

• “You changed the FX logic in April. Which KPIs and periods did it affect?”
• “You added a new entity in October. How does it flow into group KPIs?”

Lineage must provide:

• Versioned transformations: clear history of SQL/dbt/Python changes and when they went live.
• Impact analysis: for any transformation or schema change, which datasets and reports are impacted.

Keboola handles this with:

• Dev/Prod mode and branching: changes are tested before promotion; promotions are logged.
• End‑to‑end data lineage: from source to dashboard, so you can say exactly which tables and KPIs were touched by a change.

3. Control evidence: “Prove that there are no uncontrolled flows or shadow AI”

In an AI‑heavy environment, auditors now care about:

• Unapproved scripts or agents producing numbers.
• Missing audit trails for code generated or executed by AI tools.
• “Shadow AI” workflows built outside governed platforms.

Modern lineage must therefore include:

• Who ran what, when, and from where (including AI tools).
• Deterministic execution: no hidden jobs or side channels.

Keboola’s stance here:

• The Keboola MCP Server lets you build workflows from tools like Cursor, Windsurf, Claude, or ChatGPT—but execution stays inside Keboola.
• Every execution, every table, every user is captured as active metadata and can be streamed to SIEM tools (Splunk, Datadog, ELK) for security and audit.
• You eliminate shadow AI: no scripts running on random laptops or unlogged cron jobs.

---

What the lineage model looks like in practice (9‑entity example)

Let’s walk through a concrete lineage chain for a consolidated KPI: “Group Operating Profit”.

1. Board KPI definition

• Location: BI semantic model or reporting DB.
• Definition: Operating Profit = Gross Profit – Operating Expenses (excluding FX and financing)
• Lineage: KPI → fact_pnl_group_consolidated (columns: operating_profit, gross_profit, opex, entity_id, period).

In Keboola’s metadata:

• Dataset: fact_pnl_group_consolidated
• Downstream consumers: board deck, finance ops reports, management dashboards.

2. Consolidation step

• Transformation: transform_group_consolidation (dbt model or Keboola SQL transformation)
• Inputs:
  • fact_pnl_entity_normalized (9 entities)
  • dim_fx_rates
  • dim_entity (ownership structure, consolidation method)

Lineage graph clearly shows:

• 1 transformation node:
  • joins fact_pnl_entity_normalized with dim_fx_rates, applies consolidation rules, writes fact_pnl_group_consolidated.
• For each output column (e.g., operating_profit), which input columns contribute to it.

3. Entity normalization

For each entity:

• Input: raw_journal_entity_03 (direct replication or batch extract from ERP).
• Transform: transform_entity_03_normalization
  • Map local chart of accounts to group chart
  • Normalize currencies, cost centers, segments
  • Flag adjustments vs. base transactions
• Output: fact_journal_entity_03_normalized → aggregated into fact_pnl_entity_normalized.

Lineage exposes:

• Mapping tables (e.g., map_coa_entity_03_to_group), including version and effective dates.
• Distinction between base journals and manual adjustments (with user/timestamp).

4. Source extraction and CDC

• Component: Keboola extractor (native or Generic REST/DB API) or log‑based CDC connector.
• Output: raw_journal_entity_03 in Keboola Storage / warehouse staging.
• Metadata tracked:
  • Source system connection details
  • Extraction type (full, incremental, CDC)
  • Run IDs, schedule, and error logs
  • Row counts, schema at extraction time

Lineage lets auditors see:

• Which ERP instance or database produced the data.
• That no intermediate “Excel corrections” or mystery scripts sit between ERP and normalized tables.

5. Journal‑level drilldown

When an auditor clicks on a value in fact_pnl_group_consolidated for Entity 3, Period 2025‑03:

• They can trace to fact_pnl_entity_normalized filtered by entity_id=3, period=2025‑03.
• From there, drill to fact_journal_entity_03_normalized with full journal lines.
• Then see raw_journal_entity_03 as imported from the ERP—same document number, same value.

The lineage graph and table views make this traversal deterministic, not guesswork.

---

How governance and lineage work together (and why it matters to auditors)

Data lineage alone is not enough. For a 9‑entity group, you also need:

1. Access controls and segregation of duties

Auditors look for:

• Who can change transformation logic?
• Who can approve or post adjustments?
• Can a single person both prepare and approve a group‑level adjustment?

In Keboola:

• Role‑based access control defines who can edit, run, or just consume flows.
• Separate projects or workspaces per entity vs. group level keep responsibilities clear.
• Active metadata plus Activity Center provide 360° monitoring of user actions, run history, and security events.

2. Change management with traceability

Every change to logic or configuration must be:

• Versioned
• Reviewable
• Rollback‑able

Keboola supports:

• Dev/Prod mode: changes made in Dev, tested, then promoted with clear logs.
• Integration with dbt and version control for transformation logic.
• Metadata that ties a given board pack back to specific run IDs and code versions.

For auditors, this means you can show:

• “This specific board pack was produced using transformation version X, deployed on date Y.”
• “These are the differences vs. last year’s logic.”

3. One glossary, one truth

When you operate across 9 entities, the hardest part is not the pipes—it’s definitions:

• What exactly counts as “operating profit”?
• How are non‑recurring items treated across entities?
• How are intercompany eliminations applied?

Lineage must therefore tie into:

• A central glossary: definitions for key metrics and dimensions.
• Consistent mappings from local charts and dimensions to group standards.

Keboola’s metadata layer and Data Catalog support this by:

• Publishing curated, governed data products (e.g., “Group P&L,” “Entity Trial Balance,” “Intercompany Matrix”) with clear descriptions and owners.
• Ensuring consumers subscribe to the latest governed version—no duplication, no drift.

---

What this replaces in a typical finance organization

Without a governed lineage model, multi‑entity teams usually rely on:

• Manual lineage diagrams in PowerPoint or Visio.
• Sprawling Excel workbooks with hidden sheets doing transformations.
• Ad‑hoc scripts on personal machines or random VMs.
• Email threads explaining how a number was produced.

The cost:

• Weeks lost before and during audits just to recreate how numbers were produced.
• High risk of inconsistent definitions across entities.
• No way to control or even see shadow AI and one‑off automations.

With Keboola:

• You run your entire data lifecycle—ingestion to AI delivery—on one governed platform, replacing dozens of tools and eliminating integration overhead.
• Data lineage is not an afterthought; it’s embedded: “from source to dashboard, Keboola tracks every dataset and dependency.”
• Activity Center turns metadata into a live control tower so you can monitor spend, performance, and security in one view.

---

How to get to this state without ripping everything apart

You don’t have to throw away your existing ERPs, warehouse, or BI tools to get audit‑ready lineage for board KPIs.

A practical rollout path:

1. Start with one critical KPI

   • Choose a board‑level KPI (e.g., Operating Profit, NPL ratio) and model its end‑to‑end flow in Keboola.
   • Ingest journals and reference data for all 9 entities into Keboola via native integrations or Generic components.

2. Normalize and document entity logic

   • Build entity‑level normalization flows with clear mapping tables and document them in the Data Catalog.
   • Ensure each entity’s logic is versioned and traceable.

3. Layer on consolidation logic

   • Implement group consolidation steps (FX, eliminations, adjustments) as Keboola transformations or dbt models.
   • Use Dev/Prod mode to test and promote changes.

4. Connect to your reporting layer

   • Feed your warehouse/BI directly from Keboola outputs.
   • Register those outputs as governed data products (“Group P&L”, “Consolidated KPIs”).

5. Turn on observability and lineage for audits

   • Enable Activity Center to monitor all runs, costs, and security events.
   • Use the lineage view to prepare audit‑ready documentation and drilldown paths.

Once the first KPI is fully governed end‑to‑end, extending to additional metrics and entities is incremental—not another multi‑month project.

---

Final verdict: what “good” looks like for 9‑entity KPI lineage

When data lineage is done right for board KPIs across 9 entities, you can:

• Click on any KPI in the board deck and trace it all the way down to the journal line and ERP instance it came from.
• Prove to auditors that every transformation, adjustment, and AI‑assisted workflow is deterministic, governed, and auditable.
• Change logic with confidence, because you know the impact on downstream KPIs and can show it.
• Run group close and board reporting in days, not weeks, without sacrificing control.

That’s the standard I use when designing flows for regulated, multi‑entity finance teams: if we can’t explain the number to an auditor with a clear, end‑to‑end lineage path, it doesn’t ship.

---

Next Step

Get Started