Financial Data Intelligence

Financial Intelligence
Analytics Dashboard

A production-grade, multi-tenant financial analytics platform built on Databricks Medallion Architecture — transforming raw GL, AR, AP, and budget data into a live 5-dashboard Power BI suite with full Row-Level Security for enterprise clients.

6
Source Data Modules
11
Gold Layer Tables
5
Power BI Dashboards
30+
DAX Measures

The Problem

Disconnected financial data, no executive visibility

The client's financial data lived across 6 isolated CSV exports — general ledger, AR, AP, chart of accounts, budget, and trial balance — with no unified model, no KPIs, and no way to report across departments or fiscal periods reliably.

☷

6 siloed financial modules

General Ledger, Accounts Receivable, Accounts Payable, Chart of Accounts, Budget vs Actual, and Trial Balance each existed as separate raw exports with no shared keys or consistent formats.

△

No P&L or EBITDA calculation

There was no automated way to calculate Gross Profit, EBITDA, or Net Income. Revenue, COGS, and OpEx lines were unclassified inside a flat general ledger with no P&L mapping.

ⓘ

Dirty data across all modules

Null primary keys, duplicate transactions, inconsistent currency codes, mixed date formats, and zero-value GL rows that added noise to every aggregation and financial report.

◑

No multi-tenant capability

The platform needed to serve multiple enterprise clients from a single Databricks catalog, with strict data isolation and Row-Level Security so each client only sees their own financials.

Solution Architecture

Medallion Architecture — 4-layer data pipeline

The platform is built on the Databricks Medallion pattern with a dedicated catalog (dest_intelligence). Each layer adds quality, structure, and business logic before surfacing to Power BI via a full star schema.

Source Files
Raw CSVs
6 financial exports: GL, AR, AP, CoA, Budget, Trial Balance
6 files DBFS
→
Ingestion · Bronze
Bronze Layer
Raw copy + metadata: hash, batch ID, company ID, timestamp
Delta MD5 Hash
→
Cleanse · Silver
Silver Layer
Type casting, deduplication, nulls, derived columns per module
PySpark 6 tables
→
Aggregate · Gold
Gold Layer
Star schema: 5 dims, 5 facts, 1 exec summary pre-aggregation
11 tables Star schema
→
Serve · Power BI
Power BI
5 dashboards, 30+ DAX measures, RLS multi-tenancy
DAX RLS

Silver Layer

Per-module data cleaning pipeline

Every financial module goes through its own tailored cleaning logic in PySpark, targeting the specific data quality issues found in each source, before being written to certified Delta Silver tables.

Chart of Accounts

CoA standardisation

Deduplication on account_code, initcap on type/subtype/name, null guard on PK, boolean cast on is_active and is_summary.

General Ledger

GL transformation

Null posting dates and zero debit+credit rows dropped. Derived: net_amount, fiscal_quarter, fiscal_year_period. DecimalType(18,2) enforced on amounts.

Accounts Receivable

AR enrichment

Derived days_to_pay, is_overdue flag, fiscal_quarter. Zero-amount invoices dropped. Aging bucket and payment status standardised to Title Case.

Accounts Payable

AP enrichment

Same pattern as AR: days_to_pay, is_overdue derived. Vendor category, expense category, and cost center trimmed. Zero bills dropped.

Budget vs Actual

BvA cleaning

Variance amount and variance percent coalesced to 0. Scenario and budget_version standardised. Fiscal quarter derived from period.

Trial Balance

TB validation

Period-level debit/credit summaries validated per account. Orphan account codes cross-checked against cleaned CoA. Balance integrity verified.

Gold Layer

Star schema — 11 certified tables

The Gold layer materialises a fully modelled star schema inside Databricks. Dimension tables hold clean master data; fact tables hold pre-aggregated KPIs ready for Power BI DirectQuery or scheduled import.

Dimension
dim_date
Full date spine 2022–2024 with fiscal year, quarter, period, week, and weekday flags.
Dimension
dim_account
Active CoA accounts with type, subtype, financial statement mapping, and normal balance classification.
Dimension
dim_department
8 departments across Operations, Revenue, G&A, and Innovation groups.
Dimension
dim_customer
Customers enriched with industry segment, region, and tiering (Strategic / Key / Standard).
Dimension
dim_vendor
Vendors with category and tier classification (Critical / Important / Standard).
Fact
fact_pl
P&L fact: Revenue, COGS, Salaries, Marketing, R&D, IT, Interest, Tax — with gross profit contribution per row.
Fact
fact_ar_aging
AR aging by customer, bucket (0–30 / 31–60 / 61–90 / 90+), and payment status per period.
Fact
fact_ap_aging
AP aging by vendor, expense category, and overdue status per fiscal period.
Fact
fact_budget_variance
Budget vs Actual by department and account with variance $ and variance % pre-calculated.
Fact
fact_cash_flow
Working capital, DSO, and DPO metrics per fiscal period for liquidity tracking.
Exec Summary
gold_exec_summary
Pre-aggregated CFO summary table combining revenue, margins, AR health, and budget status for fast KPI cards.

Power BI Deliverables

5 dashboards built for every financial persona

Each dashboard targets a specific financial audience and draws from the Gold layer tables via a fully modelled star schema, with 30+ DAX measures covering time intelligence, variance, and liquidity analysis.

01

P&L / Income Statement

Full income statement from Revenue to Net Income with EBITDA, gross margin, and YoY / MoM growth trends.

Total Revenue Gross Profit EBITDA Net Income YoY Growth %
02

Balance Sheet

AR and AP balances, working capital position, and current ratio with period-over-period movement.

Total AR Total AP Working Capital Current Ratio
03

Cash Flow & Working Capital

DSO, DPO, overdue AR/AP, AR aging buckets, and collection rate efficiency for treasury teams.

DSO DPO AR Overdue % Collection Rate Aging 0-30/31-60/90+
04

Budget vs Actual

Variance analysis by department and account, with departments-over-budget count and budget attainment percentage.

Budget Variance $ Variance % Depts Over Budget Attainment %
05

CFO Executive Summary

Pre-aggregated KPI cards with RAG status indicators: On Track / Watch / Alert across revenue, AR health, and budget compliance.

Revenue Status ● AR Health ● Budget Health ● Net Margin Card

Deliverable Preview

CFO Executive Summary — live view

The Gold layer connects to Power BI via Databricks Partner Connect, with a recommended daily 6 AM refresh schedule. All KPI cards update automatically from the certified Delta tables.

Dest Intelligence — CFO Executive Summary Daily Refresh · Delta Lake
$42.6M
Total Revenue
18.4%
Net Margin
$3.1M
Working Capital
+2.4%
Budget Variance
Monthly P&L trend (Revenue vs COGS vs Net Income)
■ Revenue ■ COGS Q1 — Q2 2024
KPI Health status
Revenue On Track
AR Health Healthy
Budget Slight Overage

Results

What the client gained

  • Automated P&L from raw GL data Revenue, COGS, EBITDA, and Net Income are now computed automatically from the General Ledger every morning — no manual Excel work required.
  • AR/AP aging at a glance Treasury teams can instantly see overdue balances, DSO, DPO, and aging buckets filtered by customer, vendor, region, and fiscal period.
  • Budget variance by department Finance controllers can drill into which departments are over or under budget, down to the individual account level, with variance % tracked per period.
  • Multi-tenant enterprise delivery Row-Level Security maps each client’s Power BI login to their _company_id, enabling a single platform to serve multiple enterprise clients with complete data isolation.
Source modules 6 GL, AR, AP, CoA, Budget, Trial Balance
Gold tables 11 5 dims + 5 facts + 1 exec summary
DAX measures 30+ Across 5 Power BI dashboards
Tenants supported ∞ Via RLS + _company_id isolation

Technology

Stack used

Databricks
Apache Spark · PySpark
Delta Lake
Power BI
DAX & Time Intelligence
Star Schema Modelling
Medallion Architecture
Row-Level Security (RLS)
Spark SQL
Partner Connect
Dest Intelligence Financial Analytics · Data Engineering · Business Intelligence
Shopping Basket