Data Flow Architecture - Analytics & AI

Current Capabilities

📅

Reliable Daily Processing - Consistent batch processing ensures regular data delivery every morning

🏢

Comprehensive Data Coverage - Complete customer data across all business touchpoints and interactions

🔒

Secure File Transfer - Established IBM File Transfer protocol provides secure and reliable data transmission

📊

High Volume Processing - Handles 40-50 files daily containing extensive customer datasets

Critical Issues

⏰
Growing File Delays - Transaction file increasing in size by an extra day each day and arriving progressively later

🔧
Upgrade Impact - CMP system upgrades causing cascading delays to other files, creating downstream processing bottlenecks

🏗️

Legacy Infrastructure - Dependency on legacy IBM File Transfer system limits modernisation and integration options

📋 Daily Processing Overview

Files Generated: 40-50 daily

Processing Schedule: Morning batch

Transfer Method: IBM File Transfer

Destination: SFTP Server Lin51

Essential customer data pipeline supporting downstream analytics, reporting, and business intelligence processes.

⚠️ Performance Impact

File Size Growth: Increasing by one additional day daily

Delivery Time: Progressively later arrivals affecting downstream processes

System Dependencies: Upgrade-related delays cascade through entire pipeline

Connected Data Sources

📈

Google Analytics - Website performance, user behaviour analytics, and digital customer journey insights

📋

Qualtrics - Customer satisfaction surveys, NPS scores, and structured feedback collection

⭐

Trustpilot - Customer reviews, reputation management data, and sentiment analysis

🔗

Awin - Affiliate marketing performance, conversion tracking, and partnership analytics

🌐

Cosmos - Business intelligence metrics, operational data, and performance indicators

Enhancement Opportunities

🤖
Cosmos Automation - Currently manually extracted from web portal each day, subject to human error and access issues

🔧

API Standardisation - Unified API integration patterns across all sources for consistent data ingestion

⚡

Real-Time Streaming - Enhanced streaming capabilities for faster insights and immediate decision making

📊

Data Quality Monitoring - Advanced validation frameworks and automated quality checks across all sources

🏗️ Integration Architecture

Primary Path: API via Databricks

Secondary Path: API via ADF

Data Types: Analytics, Surveys, Reviews

Update Frequency: Multiple intervals

Multi-platform digital insights feeding comprehensive customer experience analytics and business intelligence processes.

🎯 Strategic Value

Customer Insights: 360-degree view across digital touchpoints

Experience Optimisation: Data-driven improvements to customer journey

Business Intelligence: Real-time performance monitoring and analytics

Google Analytics Integration

📊

Automated BigQuery Transfer - Google Analytics data transferred automatically using 4 timed intervals

🔄

Synchronized Reporting - Automated data synchronisation ensures consistent reporting metrics across platforms

🏗️

Architecture Integration - Seamless integration with existing data warehouse architecture and workflows

⚡

Pipeline Efficiency - Simplified batch and streaming data pipeline management with automated orchestration

Enhancement Opportunities

🚀

Real-Time Streaming - Advanced streaming capabilities for instant analytics insights and real-time decision making

🔍

Data Quality Monitoring - Enhanced validation and quality monitoring frameworks for improved data reliability

⚙️

Performance Optimisation - Advanced scheduling optimisation and resource management for improved performance

📈 Current Data Processing

Source: Google Analytics (BigQuery)

Frequency: 4 timed intervals daily

Method: Automated synchronisation

Output: Structured data warehouse

Unified data engineering platform enabling reliable batch and streaming data pipeline management across the organisation.

🎯 Platform Benefits

Simplified Management: Unified solution for complex data engineering workflows

Automated Orchestration: Reduced manual intervention and improved reliability

Scalable Architecture: Supports both batch and streaming data processing requirements

Current Implementation

✅

Standardised metric definitions across workflow and calendar data

✅

Create a model of existing conformed tables using joins and define measures that can be used across multiple outputs such as Genie and Dashboards

✅

Consistent business logic for workflow counting and user attribution

Blockers

❌

Omni is only currently compatible with the basic 0.1 version of metric views

YAML Configuration Example

version: 0.1

source: |
  SELECT c.*, 
    w.* EXCEPT (DATE_SK),
    r.ACCOUNT_NAME
  FROM idm_conformed.staging.fact_workflow w
  JOIN idm_conformed.staging.dim_calendar c USING (DATE_SK)
  JOIN idm_redacted.Cmp.AccountName r on w.CreatedAccountName_RSK = r.AccountName_RSK

dimensions:
  - name: EVENT_TYPE_DESCRIPTION
    expr: EVENT_TYPE_DESCRIPTION
  - name: CREATED_USER
    expr: ACCOUNT_NAME
  - name: WEEK_STARTING
    expr: WEEK_STARTING

measures:
  - name: Workflow Count
    expr: COUNT(1)

📖 View Full Documentation on Microsoft Learn

Current Capabilities

🏪

Retail Transaction Capture - Comprehensive sale and return transaction data from all retail store locations

🔄

Data Conformation - Netezza-based data processing and transformation before extraction to downstream systems

📂

Established Pipeline - Proven data extraction process feeding raw layer storage for further processing

💼

Business Operations Support - Reliable transaction processing system supporting critical retail operations

Critical Migration Risks

☁️
Cloud Migration Required - Netezza solution needs urgent migration to modern cloud-based data warehouse

🔄
Double Ingestion Process - iD data requires ingestion then re-ingestion for consistent redacted key management

🏢

On-Premise Dependencies - Legacy IBM data warehouse infrastructure hosted on-premise limits flexibility

📈

Scalability Constraints - Legacy infrastructure dependency limits scalability and modernisation opportunities

⚠️

Pipeline Complexity - Complex extraction process creates potential delays and operational risks

🏪 Transaction Processing Flow

Source: Carphone Warehouse Retail Stores

Data Types: Sales & Return Transactions

Processing: Netezza Data Warehouse

Output: Raw Layer Data Files

Legacy transaction system providing essential retail data through established on-premise infrastructure before cloud migration.

🚨 Migration Urgency

Infrastructure Risk: On-premise Netezza solution requires immediate cloud migration planning

Data Processing: Complex double-ingestion process for iD creates operational complexity

Modernisation Blocked: Legacy dependencies prevent scalability and advanced analytics integration

Platform Advantages

📁

Bespoke Lookup Files - Business users can provide and update custom lookup files for data enrichment

🏢

Microsoft Ecosystem - Familiar Microsoft integration provides intuitive experience for business users

🔗

Databricks Integration - Direct API integration enables automated data ingestion from SharePoint to Databricks

👥

Collaborative Access - Multi-device accessibility enabling team collaboration and file management

Integration Challenges

🔧
Custom API Pattern - Databricks API connection lacks established pattern for this specific ingestion method

🔍

Data Validation Gap - Lack of standardised data validation and quality checks for user-provided files

📊

Format Inconsistency - Potential for inconsistent file formats and data structures across different users

⚠️

Monitoring Limitations - Limited monitoring and error handling capabilities for custom integration processes

📋 User File Management

File Type: Bespoke Lookup Files

Access Method: Multi-device SharePoint

Integration: Databricks API

Processing: Automated Ingestion

Microsoft collaborative platform enabling business users to provide custom data enrichment files through familiar interface.

🔧 Development Priorities

API Standardisation: Establish proven patterns for SharePoint-Databricks integration

Quality Framework: Implement automated validation and quality checks for user uploads

Monitoring Enhancement: Advanced error handling and process monitoring for custom integrations

Current Infrastructure

🔐

Secure File Hosting - Encrypted file storage with secure access controls before cloud processing

🔗

IBM File Transfer Integration - Established integration with IBM File Transfer protocol for reliable data reception

⚙️

Processing Staging Area - Reliable intermediate location for decryption and upload preparation processes

☁️

Azure Integration - ADF integration enables automated transfer to Azure Blob Storage for downstream processing

Critical Decommissioning Risk

🚨
Decommissioning Scheduled - Server identified for decommissioning but no working alternative available to IBM

🏗️

Legacy Infrastructure Risk - Dependency on legacy infrastructure creates significant operational and continuity risks

⚠️

Single Point of Failure - Critical bottleneck in data pipeline with no immediate failover capabilities

📈

Scalability Constraints - Limited scalability options prevent future growth and modernisation initiatives

🔄 Data Flow Pipeline

Input: CMP via IBM File Transfer

Processing: Decryption & Staging

Integration: ADF Automation

Output: Azure Blob Storage Raw

Secure intermediary server enabling encrypted file processing and automated cloud storage integration in the data pipeline.

🚨 Business Continuity Risk

Decommissioning Timeline: Server scheduled for retirement with no confirmed replacement solution

Pipeline Dependency: Critical staging point with no alternative path currently available

Immediate Action Required: Urgent need for alternative infrastructure solution to maintain data flow

💡 Migration Strategy

Cloud-Native Replacement: Develop Azure-based secure file staging solution

Enhanced Security: Implement modern encryption and access control mechanisms

Scalable Architecture: Design for future growth and improved operational resilience

Current Capabilities

✅

Multi-Source Ingestion - Receives data from Online Sources, CMP via SFTP, SharePoint, and PIE systems

✅

Cost-Effective Storage - Provides economical storage for large volumes of raw data with multiple access tiers

✅

Data Processing Integration - Seamlessly integrates with Group Framework for data transformation and cleaning

✅

Retention Management - Automated lifecycle policies for cost optimisation and compliance

Critical Risks

🔒

Restricted Access - Contains raw PII data requiring strict access controls and security governance

⚠️

Unity Catalog Compatibility - Requires Data Lake Gen2 upgrade to be fully compatible with Unity Catalog governance

🔧

Legacy Mounting - Currently mounted to Databricks via legacy methods, limiting security and governance features

📋

Compliance Requirements - Must ensure GDPR and data protection compliance for all stored PII data

Key Advantages

✅

Data Lake Gen2 Architecture - Modern storage solution with hierarchical namespace and optimised performance

✅

Delta Lake Format - Contains structured delta tables providing ACID transactions and data versioning

✅

Group Framework Integration - Seamlessly processes data from raw storage using enterprise-grade transformation pipelines

✅

Multi-Path Processing - Feeds both iD Framework for conformed data and ADF for data extracts

✅

Unity Catalog Ready - Fully compatible with Databricks Unity Catalog for advanced governance

Enhancement Opportunities

💡

Real-Time Streaming - Potential for streaming data ingestion for near real-time analytics

💡

Data Quality Monitoring - Enhanced automated data quality checks and validation frameworks

💡

Performance Optimisation - Further partition strategy optimisation for large-scale analytics workloads

💡

Data Lineage - Enhanced data lineage tracking throughout the transformation process

Key Features

🤖

Claude AI Integration - Powered by Claude for intelligent, context-aware responses to data queries

💬

Natural Language Interface - Users can ask questions in plain English and receive meaningful insights

🔗

Genie Spaces Integration - Seamlessly connected to Databricks Genie spaces for comprehensive data access

🏗️

Databricks Native - Built directly on Databricks ensuring security, scalability, and performance

📊

Interactive Visualisations - Generates charts and graphs automatically based on conversation context

Enhancement Opportunities

💡

Multi-Modal Capabilities - Future integration of document and image analysis alongside text conversations

💡

Personalised Insights - User-specific dashboards and recommendations based on conversation history

🚀 Access the Live Application

Experience the power of conversational analytics with our intelligent data chat interface.

🔗 Open Data App

Current Capabilities

✅

Privacy Compliance - Provides a layer for handling sensitive customer data with appropriate redaction

✅

Azure Integration - Native Azure SQL server ensuring compatibility with existing infrastructure

✅

Data Pipeline Integration - Feeds into both iD Framework pathways for conformed and abstract layers

Critical Issues

⏱️
Extremely Slow Redaction - Data redaction pipelines taking 4-6 hours at times, creating significant processing bottlenecks

🔒
Data Locking Issues - Data becomes locked during processing and the underlying process is not well understood

💰
High Cost Impact - Adding approximately £200k to the annual Azure invoice, representing significant operational expense

🐌

Slow Connection Performance - Connecting to the SQL server is extremely slow, impacting downstream processes

📏

Restrictive Data Types - Data type size limitations prevent storage of complex data structures

💬

Rich Data Incompatibility - Rich data formats like chat transcripts are incompatible with current schema constraints

🔗

Complex Join Requirements - Requires two separated outputs to be joined when PII data is needed, adding complexity

⚠️ System Performance Impact

Pipeline Duration: 4-6 hours for redaction processes

Annual Cost Impact: Additional £200k Azure expense

Process Understanding: Limited visibility into data locking mechanisms

Current Model Portfolio

🎯

Churn at Point of Eligibility - Predictive model identifying customers likely to churn when their contract becomes eligible for changes

⚡

In-Life Churn Risk & Sleeper Durations - Advanced model predicting churn probability during active contract periods and dormant account patterns

💬

Trustpilot Satisfaction Predictor - Model analysing Trustpilot reviews to predict general satisfaction levels across the entire customer base

🏗️

Feature Store Integration - Built using Databricks Feature Store Client for consistent, reusable feature engineering

🚀

Inference Endpoints - Automated endpoint creation for real-time model serving and inference capabilities

Advanced Predictive Models

💰

Trading Margin / Profitability - Predict expected profit and margin at point of sale based on historical sales data and customer patterns

📈

Customer Lifetime Value - Utilise survival models combined with profit data to forecast long-term customer value and retention probability

⭐

NPS Prediction - Analyse survey responses from Qualtrics to predict Net Promoter Score trends and customer advocacy likelihood

💬

Live Chat Transcript Mining - Leverage Large Language Models (LLMs) to extract insights, sentiment, and actionable intelligence from customer chat transcripts

🔬 Technical Architecture

Platform: MLflow on Databricks

Feature Store: Databricks Feature Store Client

Inference: Automated endpoint creation

Data Source: Curated feature tables

Models utilise feature tables from the conformed layer ensuring consistent, high-quality input data for reliable predictions.

Current Production Suite

📊

30 Production Reports - Comprehensive suite of Power BI reports covering all major business areas

👥

Active User Base - Approximately 40 users per month actively engaging with the reporting platform

📈

Regular Usage - Around 500 report views per month demonstrating consistent business value delivery

🔧

Key Report Types - Base management, invoice analysis, and sales interactive reports providing critical business insights

🔗

Semantic Model Integration - Direct connection to curated semantic models ensuring data consistency and governance

Critical Challenges

👨‍💻
Limited Certified Developers - Small number of users qualified to create certified reports, creating development bottlenecks

⚡
Performance Issues - System performance compromised by poor code quality submitted to Databricks from Power BI queries

💰
High Resource Costs - Significant people resource costs relative to quality and expertise delivered, impacting ROI

📚

Training Expense - High cost of training users on Power BI capabilities and best practices

🔍

Granular Data Limitations - Platform struggles with detailed granular data analysis requirements

📁

Excel Export Dependency - Users frequently download data to Excel rather than working natively within Power BI environment

📊 Usage Statistics

30

Production Reports

~40

Users per Month

~500

Views per Month

🚀 Access Power BI Workspace

Explore the full suite of production reports and analytics dashboards.

📊 Open Power BI Workspace

Current Architecture

🏗️

Star Schema Design - Models built using industry best practice star schema architecture for optimal performance and usability

🔗

Conformed Table Integration - Leverages multiple conformed tables from the data warehouse ensuring data consistency and integrity

📊

Multi-Report Support - Single semantic models can serve multiple reports, promoting reusability and consistency

📏

Custom Metrics Integration - Business logic and custom metrics built directly into the model layer for standardised calculations

🎯

Power BI Native - Fully integrated within Power BI ecosystem for seamless report development and deployment

Critical Challenges

👨‍💻
Limited Certified Developers - Small number of users qualified to create certified semantic models, creating development bottlenecks

💰
High Resource Costs - Significant people resource costs relative to quality and expertise delivered, impacting ROI

⚡
Performance Issues - System performance compromised by poor code quality submitted to Databricks from Power BI queries

📚

Training Expense - High cost of training users on Power BI semantic modelling capabilities and best practices

🚀 Development Opportunities

💡

Metric Views Integration - Utilise Databricks Metric Views once Power BI compatibility is achieved to simplify and consolidate all models using a single, standardised method

This migration would reduce complexity, improve consistency, and potentially address many of the current performance and maintenance challenges by centralising business logic in the Databricks layer.

🏗️ Technical Architecture

Design Pattern: Star Schema

Data Source: Conformed Tables

Platform: Power BI Service

Deployment: Multi-Report Support

Models incorporate custom business metrics and serve as the semantic layer between raw data and business intelligence reporting.

Key Advantages

🚀

Native Connectivity - Direct integration with Databricks infrastructure improves speed and simplifies data access

📊

Multi-Source Data Integration - Utilises tables, queries and metric views for comprehensive analytics capabilities

🔗

Cross-Platform Sharing - Visualisations seamlessly shared across notebooks, Genie, and SQL interface

⚡

Performance Optimisation - Native architecture eliminates external connectivity bottlenecks for faster rendering

🔄

Continuous Evolution - Platform continuously improving with regular feature updates and enhancements

🎯

Interactive Capabilities - Provides interactive reporting features for dynamic data exploration

Considerations

📋

Customisation Scope - Not as tailored or feature-rich as dedicated Power BI reporting solutions

🎨

Design Flexibility - Limited advanced formatting and styling options compared to specialised BI tools

📈

Advanced Analytics - May lack some sophisticated analytical features available in dedicated BI platforms

🏗️ Technical Architecture

Platform: Databricks Native

Data Sources: Tables, Queries, Metric Views

Connectivity: Native Integration

Sharing: Cross-Platform Compatible

Dashboards leverage the Databricks ecosystem for simplified, high-performance analytics with seamless integration across notebooks, Genie AI, and SQL interfaces.

💡 Strategic Value

Speed & Simplicity: Native connectivity eliminates external bottlenecks

Ecosystem Integration: Unified experience across all Databricks tools

Future-Ready: Continuously evolving platform with regular improvements

Key Capabilities

📊

Semantic Data Models - Business-friendly semantic layer generated from Metric Views for intuitive data exploration

🤖

Native AI Integration - Built-in AI capabilities enabling intelligent data insights and natural language interactions

🎯

Multi-Output Support - Generates both interactive dashboards and self-service datasets from a single semantic model

⚡

Enhanced Performance - Optimised for native Databricks integration with superior performance compared to external BI tools

🔗

Metric View Foundation - Built directly from Metric Views ensuring consistent business logic and governance

Strategic Advantages

💡

Unified Analytics Platform - Single semantic model powers multiple analytics outputs reducing complexity and maintenance

🚀

Advanced AI Features - More native AI capabilities than standard Databricks Dashboards for enhanced user experience

👥

Self-Service Enablement - Empowers business users with AI-powered self-service analytics similar to Genie capabilities

📈

Scalable Architecture - Designed to scale with growing analytics needs while maintaining performance and governance

🏗️ Architecture Flow

Source: Metric Views (0.1 version)

Generation: Automated topic creation

Outputs: Dashboards & Datasets

Platform: Omni (Databricks)

Semantic data models that bridge Metric Views with business analytics, enabling AI-powered dashboards and self-service datasets.

🎯 Business Value

Simplified Analytics: Business-friendly semantic layer reduces technical barriers to data exploration

AI-Enhanced Insights: Native AI capabilities provide intelligent recommendations and natural language interactions

Unified Experience: Single semantic model supports multiple analytics outputs for consistent business logic

Enhanced Capabilities

🤖

Advanced AI Integration - More native AI capabilities than standard Databricks Dashboards for intelligent insights and recommendations

📊

Omni Topics Foundation - Built directly from Omni Topics ensuring consistent semantic models and business logic

⚡

Superior Native Integration - Enhanced native connectivity with Databricks infrastructure for optimal performance

🎯

Interactive Analytics - Rich interactive features for dynamic data exploration and visualisation

🔗

Seamless Ecosystem Integration - Unified experience across Databricks tools with enhanced cross-platform capabilities

Advantages Over Standard Dashboards

💡

AI-Powered Features - Native AI capabilities provide intelligent data insights, recommendations, and natural language interactions

🚀

Enhanced Performance - Optimised for Omni platform with improved query performance and faster rendering

📈

Semantic Layer Benefits - Leverages Omni Topics semantic models for consistent business logic and governance

🔄

Continuous Innovation - Access to latest Omni platform features and AI enhancements as they become available

🏗️ Technical Architecture

Platform: Omni (Databricks)

Data Source: Omni Topics

Integration: Native Omni Platform

AI Capabilities: Enhanced Native Features

Dashboards leverage Omni Topics semantic models for AI-enhanced analytics with superior native integration and performance.

💡 Strategic Value

AI-Enhanced Analytics: More advanced AI capabilities than standard dashboards for intelligent insights

Native Performance: Optimised Omni platform integration for superior speed and responsiveness

Unified Semantic Layer: Consistent business logic through Omni Topics foundation

AI-Powered Self-Service

🤖

AI Similar to Genie - Powered by advanced AI capabilities enabling natural language queries and intelligent data exploration

💬

Natural Language Interface - Ask questions about data using conversational language without technical SQL knowledge

🔍

Intelligent Insights - AI provides context-aware responses and recommendations based on data relationships

📊

Omni Topics Foundation - Built from Omni Topics ensuring consistent semantic models and business logic

👥

Democratised Analytics - Enables all users to act as analysts, breaking down technical barriers to data exploration

Key Advantages

🎯

Self-Service Capability - Independent data exploration without requiring technical dependencies or SQL expertise

⚡

Rapid Insights - Instant answers to business questions through AI-powered natural language processing

🔒

Governed Access - Built on Omni Topics semantic layer ensuring data governance and business logic consistency

🔄

Continuous Learning - AI improves responses through user feedback and interaction patterns

🤖 AI-Powered Analytics

Input: Natural Language Questions

Processing: AI Interpretation & Query Generation

Output: Data Insights + Explanations

Source: Omni Topics

Self-service datasets powered by AI similar to Genie, enabling natural language data exploration through Omni Topics semantic models.

🎯 Business Transformation

Universal Access: Every user becomes a data analyst without technical training requirements

Accelerated Insights: Instant data exploration without complex query building or BI tool expertise

Governed Self-Service: AI-powered analytics with consistent business logic through Omni Topics foundation

💡 Comparison with Genie

Similar AI Capabilities: Both powered by advanced AI for natural language data exploration

Omni Topics Foundation: Datasets built on semantic models from Omni Topics for enhanced consistency

Self-Service Focus: Designed specifically for independent data exploration with AI assistance

Revolutionary Capabilities

💬

Natural Language Queries - Ask questions about Databricks data using conversational, everyday language without technical expertise

🔍

Code Transparency - Returns both data insights and the SQL code that generated results for full analytical transparency

👥

Democratised Analytics - Enables all users to act as analysts, breaking down technical barriers to data exploration

🎯

Self-Service Intelligence - Empowers business users with independent data exploration capabilities without technical dependencies

🧠

Intelligent Understanding - Advanced AI comprehension of business context and data relationships for accurate responses

Success Requirements

📝
User Feedback Investment - Requires dedicated user engagement to provide feedback and improve AI responses through continuous learning

📚
BI Documentation Commitment - Demands significant BI team investment to properly document tables, fields, and business logic

🏗️

Metric View Integration - Requires creation of comprehensive models using new metric view functionality for optimal performance

⚡

Rapid Response Capability - Needs quick turnaround on user feedback to maintain engagement and system improvement

🎓

Change Management - Requires user training and adoption strategies to maximise platform utilisation

🤖 AI-Powered Analytics

Input: Natural Language Questions

Processing: AI Interpretation & SQL Generation

Output: Data Insights + SQL Code

Platform: Databricks Native

Revolutionary natural language interface transforming complex data analytics into conversational interactions for universal accessibility.

📈 Business Transformation

Universal Access: Every user becomes a data analyst without technical training requirements

Accelerated Insights: Instant data exploration without complex query building or BI tool expertise

Transparent Analytics: Full visibility into analytical processes through code generation and explanation

🎯 Implementation Strategy

Documentation Excellence: Comprehensive table and field documentation by BI team

Metric View Foundation: Robust models using advanced metric view functionality

Feedback Loop Excellence: Rapid response to user feedback for continuous improvement

User Engagement: Active participation in feedback and system training for optimal results

Core Capabilities

💾

Saved Function Execution - Specific coded outputs extracted directly from Databricks using pre-built, reusable functions

👥

User-Driven Execution - Executed by designated sections of the user base with appropriate access and permissions

🔄

Direct Databricks Integration - Native extraction capabilities leveraging Databricks processing power and data access

📊

Targeted Data Solutions - Customised outputs designed for specific business requirements and analytical needs

⚡

Automated Processing - Streamlined data extraction workflows reducing manual intervention and processing time

Third-Party Integration

🏢

ADF Distribution - Seamless sharing to external third parties through Azure Data Factory integration

🔐

Secure Data Sharing - Controlled data distribution with appropriate security and access governance

🌐

External Partnership Support - Enables data collaboration and business intelligence sharing with external partners

📋

Compliance Framework - Maintains data governance and regulatory compliance for external data sharing

🔄 Extract Processing Flow

Source: Databricks Direct

Method: Saved Functions

Execution: Designated User Base

Distribution: Internal + ADF Third-Party

Coded extract solutions providing targeted data outputs for internal analytics and external third-party business intelligence sharing.

🏗️ Technical Architecture

Function Repository: Saved coded functions stored within Databricks environment

User Access Control: Designated user segments with appropriate execution permissions

External Integration: ADF-enabled third-party data sharing capabilities

Processing Power: Leverages full Databricks computational capabilities for extract generation

🎯 Business Value

Targeted Solutions: Specific data outputs designed for precise business requirements

Partnership Enablement: Facilitates external data collaboration and business intelligence sharing

Operational Efficiency: Automated extraction processes reducing manual workload and processing time

Production Implementation

📸

Daily Subscriber Snapshots - Captures daily snapshots of subscriber movements with foreign keys to type 2 dimensions for comprehensive joins

⏱️

Time-Windowed Features - Includes last 3 bill average, usage in last 30 days, and other temporal analytics essential for ML models

🏪

Centralised Feature Store - Databricks Feature Store providing consistent, reusable features across all ML models and data science teams

🔗

ASOF Joins - Advanced as-of joins to fetch temporally relevant records for accurate feature engineering

⚡

Real-Time Inference - Feature serving capabilities with mega cluster in-memory processing for live model predictions

🔧

Reduced Engineering Overhead - Significant reduction in data engineering tasks through centralised, pre-processed features

Feature Engineering Excellence

🔬

Experimentation Framework - Advanced feature selection capabilities with conformed layer extension for rapid model prototyping

📊

Reporting Integration - Feature tables designed as snapshots enabling direct use for business reporting and analytics

🔄

Reusability & Consistency - Standardised feature definitions eliminating duplicate engineering across data science teams

⚡

Performance Optimisation - Native Databricks integration providing optimal feature computation and serving performance

📊 Feature Store Structure

Core Structure: valid_from date, subscription, device brand

Snapshot Frequency: Daily subscriber movements

Join Strategy: Foreign keys to type 2 dimensions

Access Method: ASOF joins for temporal accuracy

Centralised logic for key subscriber movements with structured attributes including Tariff Plan Name and comprehensive usage metrics.

⚙️ Real-Time Inference Pipeline

Feature Serving: Post REST server retrieval with mega cluster in-memory processing

Temporal Windows: Last 3 bill average, 30-day usage patterns, and historical trend analysis

Experimentation Support: Feature selection capabilities with conformed layer extension

Platform Integration: Native Databricks Feature Store with ML Runtime connectivity

🎯 Data Science Impact

Engineering Efficiency: Dramatic reduction in data engineering time through centralised feature store

Model Applications: Powers churn prediction, engagement analysis, roaming intelligence, and personalisation models

Consistency & Reuse: Standardised features across teams eliminating duplicate engineering and ensuring model reproducibility

Current Implementation

✅

Independent Architecture - Data components are modelled separately from the reporting tables in conformed to ensure fewer dependencies

✅

Complex Output Generation - Complex code generates tailored output files which are then sent back to be uploaded into CMP for business process integration

✅

Automated Daily Processing - Daily extracts sent and ingested into CMP to update Subscription Attributes and drive business operations

✅

Business Logic Centralisation - Consolidates complex business rules and logic separate from standard reporting infrastructure

✅

Operational Integration - Seamlessly feeds back into core business systems to influence customer experiences and operational decisions

Strategic Applications

🎯

Customer Segmentation - Powers customer segmentation processes to drive recommendations, app behaviour, and CRM messaging strategies

📋

Contract Management - Supports End of Contract notifications and communications for customer retention and upgrade strategies

🔧

Operational Intelligence - Provides tailored data outputs for specific operational requirements and business process optimisation

📊

Business Rule Enforcement - Implements complex business logic separate from reporting constraints for maximum operational flexibility

🏗️ Architecture Design

Data Source: Azure Data Lake - Clean

Processing: Independent from Reporting

Output: Tailored Business Files

Integration: CMP Upload & Attribution

Strategic layer enabling business-specific data transformations independent of reporting infrastructure, ensuring operational flexibility and reduced dependencies.

🔄 Processing Pipeline

Data Ingestion: Cleaned tables from Azure Data Lake providing high-quality source data

Business Logic Application: Complex code implementing specific business rules and transformations

Output Generation: Tailored files created for specific business process requirements

System Integration: Automated upload and integration with CMP for operational use

🎯 Business Impact

Operational Excellence: Enables sophisticated business logic implementation separate from reporting constraints

Customer Experience: Powers personalised segmentation and targeted communication strategies

Architectural Flexibility: Independent design reduces dependencies while maintaining integration capabilities

Core Architecture

🏗️

Unified Data Integration - Consolidates data from Redacted SQL Server and Azure Data Lake Clean via iD Framework processing

🔧

Data Standardisation - Applies consistent field naming, business logic, and data quality rules across all sources

📊

Business-Ready Structure - Contains valid_from dates, Subscription and other consistent keys, and customer features designed for analytical consumption

🔒

Privacy Compliance - Implements data redaction strategies while maintaining analytical integrity and regulatory compliance

⚡

Multi-Purpose Serving - Feeds Power BI, Feature Tables, Data Extracts, and Genie AI through optimised data structures

🌐

Schema Evolution Management - Handles changes in data schema without disrupting downstream data pipelines and applications

Data Normalisation Excellence

🎯

Redundancy Elimination - Organises data to reduce redundancy by storing information once and referencing it, rather than duplicating across multiple rows

🔒

Data Integrity Assurance - Updates to data (like customer addresses) only happen in one place, ensuring consistency across all references

⚙️

Maintenance Simplification - Smaller, well-structured tables with clear relationships are easier to manage, query, and maintain

📋

Normal Form Implementation - Follows normalisation rules through 3NF (Third Normal Form) to balance structure and performance optimally

Technical Challenges

🔄

Data Consistency - Ensuring consistent data definitions and quality across multiple diverse sources and complex transformations

⚡

Advanced Spark Functionality - Leveraging Liquid Clustering, primary keys, and optimised joins for enhanced performance and data integrity

🛡️

Unity Catalog Integration - Adapting framework implementation for enhanced Unity Catalog governance and security features

🔧

Framework Evolution - Continuously improving iD Framework capabilities to support expanding business requirements

🔧 Data Normalisation Process

Data normalisation is the process of organising data to reduce redundancy and improve data integrity. Think of it as tidying up a messy spreadsheet—splitting it into smaller, more focused tables and defining clear relationships between them.

1NF (First Normal Form): No repeating groups—each field contains only atomic values

2NF (Second Normal Form): Every non-key column is fully dependent on the entire primary key

3NF (Third Normal Form): Removes transitive dependencies between non-key columns

Target Achievement: 3NF balance between structure and performance

Our conformed layer implements normalisation principles to ensure data efficiency, consistency, and maintainability across the entire analytics platform.

🏗️ Data Integration Pipeline

Input Sources: Redacted SQL Server + Azure Data Lake

Processing Framework: iD Framework Transformation

Data Tools: ADF, MuleSoft Integration

Output Applications: BI, ML, Analytics, Self-Service

Central data hub enabling standardised field naming, business logic application, and consistent customer data management across all analytical applications.

📊 Downstream Applications

Power BI Semantic Models: Standardised data foundation for enterprise reporting and dashboards

Feature Tables: ML-ready data structures supporting churn prediction, engagement analysis, and advanced analytics

Data Extracts: Targeted business outputs for operational systems and third-party integrations

Genie AI/BI: Self-service analytics platform enabling natural language data exploration

🎯 Business Value

Single Source of Truth: Eliminates data inconsistencies and provides unified customer view across all business applications

Analytical Excellence: Enables sophisticated customer behaviour analysis, predictive modelling, and data-driven decision making

Operational Efficiency: Reduces data engineering overhead through standardised, reusable data structures across teams

🔬 Implementation Leadership

Technical Architecture: Designed and implemented by the Analytics Engineers

Privacy & Compliance: Advanced data redaction maintaining analytical capability while ensuring regulatory compliance

Framework Innovation: Continuous enhancement of iD Framework supporting evolving business and technical requirements

Interactive Data Flow Architecture

CMP (Customer Management Platform)

Current Capabilities

Critical Issues

📋 Daily Processing Overview

⚠️ Performance Impact

Online Sources

Connected Data Sources

Enhancement Opportunities

🏗️ Integration Architecture

🎯 Strategic Value

Databricks Lakeflow

Google Analytics Integration

Enhancement Opportunities

📈 Current Data Processing

🎯 Platform Benefits

Metric View

Current Implementation

Blockers

PIE

Current Capabilities

Critical Migration Risks

🏪 Transaction Processing Flow

🚨 Migration Urgency

SharePoint

Platform Advantages

Integration Challenges

📋 User File Management

🔧 Development Priorities

SFTP Server Lin51

Current Infrastructure

Critical Decommissioning Risk

🔄 Data Flow Pipeline

🚨 Business Continuity Risk

💡 Migration Strategy

End of Contract

Current Implementation

Development Opportunities

Azure Blob Storage - Raw

Current Capabilities

Critical Risks

Azure Data Lake - Clean

Key Advantages

Enhancement Opportunities

Data App

Key Features

Enhancement Opportunities

🚀 Access the Live Application

Redacted Data - SQL Server

Current Capabilities

Critical Issues

⚠️ System Performance Impact

AI/ML Models

Current Model Portfolio

Advanced Predictive Models

🔬 Technical Architecture

Reports

Current Production Suite

Critical Challenges

📊 Usage Statistics

🚀 Access Power BI Workspace

Semantic Models

Current Architecture

Critical Challenges

🚀 Development Opportunities

🏗️ Technical Architecture

Segmentation

Current Implementation

Development Opportunities

Dashboards

Key Advantages

Considerations

🏗️ Technical Architecture

💡 Strategic Value

Omni Topics

Key Capabilities

Strategic Advantages

🏗️ Architecture Flow

🎯 Business Value

Dashboards via Omni