Dimensional modelling

Client 360 data model
for a regulated fintech

Five disconnected source systems. One unified client model. Built to satisfy compliance, commercial, and ops teams — all from the same trusted layer.

90%
Of client questions answerable from the model
11
Source systems unified into one layer
5→1
Source systems reduced to a single query surface
Industry Fintech · Regulated financial services
Engagement type Analytics engineering · Data modelling
Stack

The situation

Critical client data, scattered across five systems

The client — a regulated financial services company — had built up their data infrastructure organically over several years. Every product team owned their own system: applications lived in one database, accounts in another, funding events in a third, trading activity in a fourth, and client profiles in a fifth.

Answering even basic questions — "Is this client active?", "What products do they hold?", "When did they last fund their account?" — required a data analyst to manually join raw tables across five systems, each with their own schema conventions, nullability patterns, and surrogate key formats. A single compliance query could take two days.

The company was growing fast and approaching regulatory reporting thresholds that would require consistent, auditable client data. The manual join approach was not going to scale.

What we built

A dimensional model for the entire client lifecycle

We designed and built a suite of dbt models in Snowflake that unified the entire client lifecycle into a single, trusted analytical layer. The core was a dim_client model — a slowly changing dimension (SCD Type 2) that tracks every material change to a client's profile, products held, and regulatory status over time.

01
Source mapping & canonicalisation

Audited all five source systems, documented field-level lineage, and built staging models that normalised surrogate keys, date formats, and null conventions before anything touched the mart layer.

02
Single definition of a "client"

Worked with compliance, commercial, and ops to agree on one canonical definition — when a client is created, what constitutes an active client, and what triggers a lifecycle state change. Codified in dbt docs so the definition lives with the model.

03
Dimension & fact model suite

Built dim_client, dim_account, fct_funding, fct_trades, and a wide_client_summary mart that prejoins the most common analytical cuts — reducing 90% of query complexity to a single SELECT.

04
Tests & data contracts

Instrumented every model with dbt tests — primary key uniqueness, referential integrity across joins, not-null constraints on regulatory fields, and custom singular tests for domain-specific business rules. Failures alert before data reaches any consumer.

The outcome

One model. Three teams. No more manual joins.

90%
Of business questions about clients answerable directly from the modelled layer — no raw SQL digging required
11
Source systems and reference tables unified into a single analytical model
2 days → mins
Compliance query turnaround reduced from a two-day analyst task to a sub-minute Looker query
Compliance, commercial, and ops teams aligned on one shared definition of a "client" for the first time — codified in dbt docs and version-controlled
Ad-hoc data requests from the business dropped significantly — most questions were now self-serve via Looker dashboards built on top of the mart layer
Regulatory reporting became reproducible — the same query, run on any historical date, returns the client population as it existed at that point in time (SCD Type 2)
New analyst onboarding time reduced — instead of learning five source schemas, new hires work from documented dbt models from day one

What made it work

Alignment first, modelling second

The technical build was straightforward. The harder part was getting three teams — each with a different understanding of what "active client" meant — to agree on a single definition before we wrote a line of SQL.

We ran a short discovery phase: one workshop with the three teams, a shared doc capturing every edge case (dormant accounts, partial onboarding, deceased clients), and sign-off before modelling began. That alignment work is what made the model trusted once it shipped.

Ready when you are.

Book a free 30-minute call. We'll find the gaps, prioritise the fixes, and give you a clear path forward.

Usually responds within 24 hours