Back to portfolio

DATA Engineering & Database Design

Building an Operational Data Mart

Designing a dimensional PostgreSQL database to support operational analysis and create a foundation for a future customer-data and CRM architecture.

PostgreSQL

Dimensional Modelling

SQL

Data Architecture

Operational Analytics

The administrative data mart schema.

Overview

The customer division of the bank was going through a period of digital transformation after many years with limited modern data infrastructure. Despite serving around 14,700 charities in the UK, the division had little formal data architecture for operational or historical trend analysis, and no accessible operational CRM providing a consistent view of customer activity. Operational information did exist across different processes and work-streams, but it was fragmented, siloed and difficult to analyse consistently. More detailed customer data also existed within older legacy systems, but migrating that information into a workable CRM for Customer Services formed only a small part of a much larger digital transformation programme with more immediate priorities.

Understanding this situation, I set out to design and build a dimensional operational data mart in PostgreSQL that could not only support day-to-day operational analysis, but also simultaneously create a foundation for a more structured customer-data environment. The overall aim was to move towards a simple CRM-style architecture in which operational activity could gradually be linked to standardised customer and organisation records. Once established, the database could then be incorporated into the bank’s planned Microsoft Fabric data warehouse architecture alongside data from other divisions.

Approach

1

Identify the division’s immediate reporting and operational analysis needs, alongside the wider plans for modernising its data infrastructure.

2

Identify the workflows that need to be captured and understand how different stakeholders use the resulting data to support decision-making.

3

Establish the grain of the central fact table and determine the most appropriate dimensional structure for the database.

4

Create the tables, relationships and constraints required to maintain data integrity and support reliable operational analysis.

Results

After agreeing the approach with the IT team and senior stakeholders, I decided on a Kimball-inspired bottom-up dimensional design, beginning with a single operational business process: administrative cases carried out by the customer division’s administrative team. The central admin fact table was designed at the grain of one administrative transaction or case item per row. It recorded shared operational information such as task type, status, start and completion dates, (customer) organisation information and time to completion. Around this, I created reusable dimensions for dates, agents, task types, organisations, customers and accounts.

Surrogate keys were used throughout the dimensional model so the analytical database did not depend directly on source-system identifiers. Where a transaction could involve multiple agents or accounts, I used bridge tables rather than adding repeated columns to the fact table. For additional workflow-specific information, I also created a set of one-to-one extension tables. This allowed the core admin fact table to remain relatively compact, whilst specialist processes such as account closures, business cards, audits and changes of contact could retain their own additional attributes.

However, customer and organisation data required a slightly different treatment. Because the incoming operational data contained customer and organisation information in free-text or source-specific formats, it was not yet reliable enough to populate master records automatically. The plan was therefore to capture customer and organisation information through the main administrative fact table, then progressively clean and standardise it, resolve duplicates, and match it periodically against existing legacy-system data. Once validated, those records could be loaded into the customer and organisation dimension tables. Operational transactions recorded in the main administrative fact table could then be linked back to these persistent customer and organisation records using surrogate keys.

In effect, the analytical database would evolve over time from a system focused primarily on operational reporting into the data foundation for a CRM-style customer view, with the customer and organisation dimensions acting as progressively enriched master records. 

It is also why dim_customer and dim_organisation exist in the model (and the diagram) even though they are not yet fully connected in the schema or through the ETL process, which is covered in Part Two.

The intended flow was:

Operational transaction captured → source customer/organisation information recorded → data reviewed and cleaned → matched against legacy data → inserted into customer/organisation dimensions → fact record linked to a durable surrogate key

The design therefore allowed the project to deliver operational analytics immediately, whilst creating a path for legacy customer data to be progressively extracted, cleaned and consolidated into durable customer and organisation dimensions, forming the data foundation for a future CRM.

You can find a more detailed look at all the code below in the Code section.

Key Learnings

•

Database design is as much about the business process as SQL: understanding workflows, reporting needs and stakeholder use cases come first.

•

Explicit validation rules make data-quality checks more reproducible and easier to investigate.

•

Data integrity has to be designed in, not added later.

Code

All the data you see below uses real business processes with fictional data to illustrate the data mart’s design and interaction with the ETL pipeline (which you can see in the second part of this case study).

I begin by building my dimension tables that need to be pre-populated.

Python 
#Date Dimension Table

CREATE TABLE dim_date(
date_key INTEGER PRIMARY KEY,
full_date DATE NOT NULL,
day_name VARCHAR(20) NOT NULL, 
day_of_week SMALLINT NOT NULL,
day_of_month SMALLINT NOT NULL,
day_of_year SMALLINT NOT NULL,
week_number SMALLINT NOT NULL,
month_number SMALLINT NOT NULL,
month_name VARCHAR(20) NOT NULL,
quarter_number SMALLINT NOT NULL,
year_number SMALLINT NOT NULL)

INSERT INTO dim_date (
date_key, full_date, day_name,
day_of_week, day_of_month, day_of_year,
week_number, month_number, month_name,
quarter_number, year_number)

SELECT TO_CHAR(date_base, 'YYYYMMDD')::INTEGER AS date_key, 
date_base AS full_date, 

TRIM(TO_CHAR(date_base, 'Day')) AS day_name,
EXTRACT(ISODOW FROM date_base)::SMALLINT AS day_of_week,
EXTRACT(DAY FROM date_base)::SMALLINT AS day_of_month,
EXTRACT(DOY FROM date_base)::SMALLINT AS day_of_year,
EXTRACT(WEEK FROM date_base)::SMALLINT AS week_number,
EXTRACT(MONTH FROM date_base)::SMALLINT AS month_number,
TRIM(TO_CHAR(date_base, 'Month')) AS month_name,
EXTRACT(QUARTER FROM date_base)::SMALLINT AS quarter_number,
EXTRACT(ISOYEAR FROM date_base)::SMALLINT AS year_number

FROM GENERATE_SERIES(
    '2020-01-01'::DATE,
    '2030-12-31'::DATE,
    INTERVAL '1 day') AS date_base;


#Agent Dimension Table

CREATE TABLE dim_agent (
agent_key INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
agent_id VARCHAR(50) NOT NULL UNIQUE,
agent_name VARCHAR(50) NOT NULL,
manager_name VARCHAR(50) NOT NULL,
status VARCHAR(50) NOT NULL,
grade VARCHAR(2) NOT NULL,
date_hire DATE NOT NULL, 
date_left DATE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)

INSERT INTO dim_agent(agent_id, agent_name,manager_name, status, grade, date_hire, date_left)
VALUES ('AB', 'Alan Box', 'Matt', 'FTC', 'A', TO_DATE('29/09/1992', 'DD/MM/YYYY'), NULL), ('CD', 'Caroline Dumond', 'Matt', 'FTC', 'A', 
TO_DATE('30/09/1992', 'DD/MM/YYYY'), NULL), ('EF', 'Ent Fond', 'Matt', 'FTC', 'A', TO_DATE('01/10/1992', 'DD/MM/YYYY'), NULL), 
('GH', 'Gif Hello', 'Matt', 'FTC', 'D', TO_DATE('02/10/1992', 'DD/MM/YYYY'), NULL),('JK', 'Jay Kay', 'Matt', 'FTC', 'C', 
TO_DATE('03/10/1992', 'DD/MM/YYYY'), NULL), ('LM', 'Lyon Matt', 'Matt', 'FTC', 'C', TO_DATE('04/10/1992', 'DD/MM/YYYY'), NULL), 
('NP', 'Nick Pimm', 'Matt', 'Temp', 'C', TO_DATE('05/10/1992', 'DD/MM/YYYY'), NULL), ('QR', 'Quentin Rochester', 'Sara', 'Perm', 'B', 
TO_DATE('06/10/1992', 'DD/MM/YYYY'), NULL), ('ST', 'Sara Tomboli', 'Sara', 'Perm', 'B', TO_DATE('07/10/1992', 'DD/MM/YYYY'), NULL), 
('VW', 'Volks Wagon', 'Sara', 'Perm', 'B', TO_DATE('08/10/1992', 'DD/MM/YYYY'), NULL);


#Task Type Dimension Table

CREATE TABLE dim_task_type (
task_key INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
task_type TEXT NOT NULL UNIQUE,
task_category VARCHAR(20),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)


INSERT INTO dim_task_type (task_type)
VALUES ('Change of Signatory'), ('Change of Contact'), ('Change of Signatory/Change of Contact'), ('Closed Account'), ('Business Card'), 
('Business Card Replacement'), ('Misc'),('Audit Paper'), ('Audit Portal'),('2nd/Online Users'),('Bounce Back'),('Gone Away'),('Deceased'),
('Corrections'),('Migration Issues'),('QA Process'), ('Telephone Passwords');

I then created the rest of my dimension tables.

Python 
# Organisation Dimension Table
      
CREATE TABLE dim_organisation(
organisation_key INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
organisation_name TEXT NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)

#Customer Dimension Table

CREATE TABLE dim_customer (
customer_key INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id INTEGER NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)

#Accounts Dimension Table

CREATE TABLE dim_account (
account_key INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
account_number TEXT NOT NULL UNIQUE,
account_type VARCHAR(20),
organisation_name VARCHAR(100))

Next, I built my main administrative case fact table and the bridge tables.

Python 
#Main Admin Fact Table
      
CREATE TABLE fact_admin(
transaction_key INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
transaction_id TEXT NOT NULL UNIQUE,
date_key INTEGER NOT NULL REFERENCES dim_date(date_key),
date_start_key INTEGER NOT NULL REFERENCES dim_date(date_key),
date_complete_key INTEGER REFERENCES dim_date(date_key),
organisation_key INTEGER REFERENCES dim_organisation(organisation_key),
source_organisation_name TEXT NOT NULL,
customer_name TEXT, 
task_key INTEGER NOT NULL REFERENCES dim_task_type(task_key),
task_status TEXT NOT NULL,
days_to_complete INTEGER,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)

#Agent Bridge Table

CREATE TABLE bridge_admin_agent ( 
transaction_key INTEGER NOT NULL, 
agent_key INTEGER NOT NULL, 
agent_role VARCHAR(30) NOT NULL, 
CONSTRAINT pk_bridge_admin_agent PRIMARY KEY ( transaction_key, agent_role ), 
CONSTRAINT fk_bridge_admin_agent_transaction FOREIGN KEY (transaction_key) REFERENCES fact_admin(transaction_key), 
CONSTRAINT fk_bridge_admin_agent_agent FOREIGN KEY (agent_key) REFERENCES dim_agent(agent_key),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)

#Account Bridge Table

CREATE TABLE bridge_admin_account ( 
transaction_key INTEGER NOT NULL, 
account_key INTEGER NOT NULL, 
CONSTRAINT pk_bridge_admin_account 
PRIMARY KEY ( transaction_key, account_key ), 
CONSTRAINT fk_bridge_admin_account_transaction 
FOREIGN KEY (transaction_key) REFERENCES fact_admin(transaction_key), 
CONSTRAINT fk_bridge_admin_account_account 
FOREIGN KEY (account_key) REFERENCES dim_account(account_key),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)

Finally, the extension tables were then built to finish the administrative operational database.

Python 
# Business Card Extension Table
      
CREATE TABLE ext_fact_admin_bcards (
transaction_key INTEGER PRIMARY KEY REFERENCES fact_admin(transaction_key) 
ON DELETE CASCADE,
if_replacement TEXT,
other_doc TEXT,
ch_name TEXT,
return_reason TEXT,
pack_saved INTEGER REFERENCES dim_date(date_key),
date_refer INTEGER REFERENCES dim_date(date_key),
refer_diarised INTEGER REFERENCES dim_date(date_key),
date_destroyed INTEGER REFERENCES dim_date(date_key),
id_adv_request INTEGER REFERENCES dim_date(date_key),
id_adv_rec INTEGER REFERENCES dim_date(date_key),
check_process TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

#Closed Accounts Extension Table

CREATE TABLE ext_fact_admin_closed (
transaction_key INTEGER PRIMARY KEY REFERENCES fact_admin(transaction_key)
ON DELETE CASCADE,
cust_id TEXT,
request_type TEXT,
continue_end_relationship TEXT,
return_status TEXT,
return_reason TEXT,
return_date INTEGER REFERENCES dim_date(date_key),
date_fee_removed INTEGER REFERENCES dim_date(date_key),
closing_bal INTEGER,
pay_method TEXT,
date_acc_opened INTEGER REFERENCES dim_date(date_key),
date_acc_closed INTEGER REFERENCES dim_date(date_key),
closure_reason TEXT,
notes TEXT,
cust_serv_det TEXT,
pay_time_det TEXT,
price_det TEXT,
other_notes TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

#Trustees Extension Table

CREATE TABLE ext_fact_admin_trustees ( 
transaction_key INTEGER PRIMARY KEY REFERENCES fact_admin(transaction_key) 
ON DELETE CASCADE, 
transact_cust_id INTEGER, 
trutsee_name TEXT, 
transact_trustee_id TEXT, 
trutsee_dob INTEGER, 
trustee_mob INTEGER, 
trustee_email TEXT, 
trutsee_address TEXT, 
date_id_req INTEGER REFERENCES dim_date(date_key), 
date_id_rec INTEGER REFERENCES dim_date(date_key), 
id_other_process TEXT, date_case_ref INTEGER REFERENCES dim_date(date_key),
date_ref_chased INTEGER REFERENCES dim_date(date_key), 
date_ref_ret INTEGER REFERENCES dim_date(date_key), 
date_pack_saved INTEGER REFERENCES dim_date(date_key), 
comments TEXT, date_email_sent INTEGER REFERENCES dim_date(date_key),
ref_folder_y_n TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

#New Accounts Extension Table

CREATE TABLE ext_fact_admin_new_accounts ( 
transaction_key INTEGER PRIMARY KEY REFERENCES fact_admin(transaction_key) 
ON DELETE CASCADE, 
fi_name TEXT, 
cust_id INTEGER, 
contact_name TEXT, 
contact_id INTEGER,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

#Change of Contacts Extension Table

CREATE TABLE ext_fact_admin_coc ( 
transaction_key INTEGER PRIMARY KEY REFERENCES fact_admin(transaction_key) 
ON DELETE CASCADE, 
other_detail TEXT, 
risk TEXT, 
date_return INTEGER REFERENCES dim_date(date_key), 
nsia_no_match TEXT, 
nsia_inc_name TEXT, 
idv_req TEXT, 
adv_req TEXT, 
other TEXT, 
date_saved INTEGER REFERENCES dim_date(date_key), 
date_rec_back INTEGER REFERENCES dim_date(date_key), 
date_ref INTEGER REFERENCES dim_date(date_key), 
date_ref_drd INTEGER REFERENCES dim_date(date_key), 
notes TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

#Audits Extension Table

CREATE TABLE ext_fact_admin_audits ( 
transaction_key INTEGER PRIMARY KEY REFERENCES fact_admin(transaction_key) 
ON DELETE CASCADE, 
audit_change_auditor VARCHAR(20), 
dec_req TEXT, 
ret_note TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

#Miscellaneous Extension Table

CREATE TABLE ext_fact_admin_misc ( 
transaction_key INTEGER PRIMARY KEY REFERENCES fact_admin(transaction_key) 
ON DELETE CASCADE, 
dec_req TEXT, 
add_info TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

#Telephone Passwords Extension Table

CREATE TABLE ext_fact_admin_tele_pw ( 
transaction_key INTEGER PRIMARY KEY REFERENCES fact_admin(transaction_key) 
ON DELETE CASCADE, 
sign_acc TEXT, 
pwd_ok TEXT, 
all_box TEXT, 
note TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

#Online Users Extension Table

CREATE TABLE ext_fact_admin_online_users ( 
transaction_key INTEGER PRIMARY KEY REFERENCES fact_admin(transaction_key) 
ON DELETE CASCADE, 
name TEXT, 
org_transact_id TEXT, 
spotlight_status TEXT, 
date_id_req INTEGER REFERENCES dim_date(date_key), 
date_id_rec INTEGER REFERENCES dim_date(date_key),
id_other_process TEXT, 
date_case_ref INTEGER REFERENCES dim_date(date_key),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

#Account Corrections Extension Table

CREATE TABLE ext_fact_admin_acc_corrections ( 
transaction_key INTEGER PRIMARY KEY REFERENCES fact_admin(transaction_key) 
ON DELETE CASCADE, 
org_transact_id INTEGER, 
incor_transact_per TEXT, 
incor_transact_org TEXT, 
spotlit_cor TEXT, 
transact_cor TEXT, 
erasure_status TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);