BFSI: Conversational Risk
Available
Intermediate ~6h

Conversational Risk Assistant — Data Pipeline

Turn noisy customer chatter, complaints, and KYC notes into a clean risk table.

The situation

The Retail Risk team is piloting a Conversational Risk Assistant that surfaces early-warning signals from customer conversations. As the Risk Data Analyst, you own the data pipeline: ingest support tickets, complaints, account attributes, chatbot logs and risk notes; clean, mask, encode and normalize them into a single analysis-ready risk table; and deliver a first cut of top risk topics by customer segment.

Context

  • Sources: CFPB-style complaint text, in-app support tickets, synthetic banking chatbot logs, KYC notes, and a credit-risk attribute table (German Credit-style).
  • Data quality is poor: ~18% duplicates, PII in free text (PAN, Aadhaar, phone, email), inconsistent product labels ('CC' vs 'Credit Card' vs 'CrdCard'), and 6–22% missing fields per column.
  • Compliance requires PII masking before any downstream analysis; the assistant must never see raw identifiers.
  • Downstream consumer: a LangChain-based conversational assistant that expects one row per customer-conversation with encoded categories and normalized numerics.

Your objectives

  • Produce one clean, deduplicated risk table joining structured and unstructured sources.
  • Mask PII in free-text fields and drop it from structured joins.
  • Standardize product / channel / segment labels and encode categoricals.
  • Normalize numeric attributes (transaction count, complaint frequency, account age, tenure).
  • Deliver a first EDA: top risk topics by customer segment and complaint-category × risk-flag correlation.

Phases

  1. Scope & source audit

    Inventory the 5 sources, confirm join keys, agree PII policy.

  2. Ingest & dedupe

    Load, hash messages, drop duplicates, log rejects.

  3. Clean text & mask PII

    Lowercase, strip noise, regex-mask PAN/Aadhaar/phone/email.

  4. Standardize & encode

    Map product/channel/segment; one-hot or ordinal encode.

  5. Normalize numerics

    Z-score / min-max scaling with rationale per column.

  6. EDA & handover

    Top risk topics by segment, correlation heatmap, data dictionary.

Tasks

  • Draft PII policy + masking regex list (PAN, Aadhaar, phone, email, card)
    Day 1, 12:00 PM
  • Ingest 5 sources into staging (tickets, complaints, chatbot, KYC, credit)
    Day 1, 04:00 PM
  • Dedupe on (customer_id, sha256(message)) and log reject rate
    Day 2, 10:00 AM
  • Clean noisy text: unicode normalize, strip URLs, collapse whitespace
    Day 2, 01:00 PM
  • Mask PII in message_text; assert 0 hits on re-scan
    Day 2, 03:00 PM
  • Build canonical product/channel/segment mapping (YAML)
    Day 3, 11:00 AM
  • Encode categoricals (product ordinal, query_type one-hot, risk_category label)
    Day 3, 02:00 PM
  • Handle missing values per column with documented rule
    Day 3, 04:00 PM
  • Normalize txn_count, complaint_freq, account_age_months, tenure
    Day 4, 11:00 AM
  • EDA: top-5 risk topics × segment; complaint_category vs risk_flag chi-square
    Day 4, 03:00 PM
  • Write data dictionary + lineage note for the risk assistant team
    Day 5, 12:00 PM

Inbox for this scenario

Open inbox
AM

Anjali Menon · Head of Retail Risk

Mon, 09:10 AM

Kickoff — Conversational Risk Assistant data layer

We're piloting the assistant with the collections team next month. I need a clean risk table by Friday — one row per customer-conversation, PII masked, categories encoded. Ping me if scope creeps.

Urgent
RK

DPO — Ravi Kulkarni · Data Protection Officer

Mon, 10:25 AM

PII policy — non-negotiables

Before ANY join with the credit attributes: PAN, Aadhaar (masked to last 4), phone, email, card PAN, DOB must be tokenized or dropped. Send me the regex list before you run it in staging.

High
PN

Priya Nathan · Data Engineering

Mon, 11:00 AM

Sources are landed in s3://risk-raw/

All five sources are in the raw bucket: cfpb_complaints.csv, support_tickets.jsonl, chatbot_logs.parquet, kyc_notes.csv, credit_attributes.csv. Heads-up: ~18% dupes in chatbot logs from retries.

FYI
FS

Farah Sheikh · Product — Risk Assistant

Mon, 02:15 PM

What the assistant expects

One row per (customer_id, conversation_id). Give me risk_category as a label, complaint_topic as free text (post-clean), and normalized numerics. I'll handle embeddings on my side.

FYI
VB

Vikram Bhatt · Credit Risk Modeler

Tue, 09:40 AM

Please keep the risk_flag join

I want to validate whether complaint_category correlates with our existing risk_flag from the scorecard. Can you include both columns in the final table? A chi-square would be great.

FYI
CO

Compliance Ops · Compliance

Tue, 11:20 AM

Audit trail required

Every masking rule and every dropped row must be logged. We will pull a sample next quarter — please emit a reject_log.parquet and a masking_report.json alongside the final table.

High

Success criteria

  • Zero PII leakage in the final risk table (validated by regex + spot check).
  • Duplicate rate < 0.5% after dedupe on (customer_id, message_hash).
  • ≥ 95% of product labels mapped to the canonical taxonomy.
  • Missing values handled with a documented rule per column (impute / drop / flag).
  • EDA notebook shows top-5 risk topics per segment with sample sizes.

Stakeholders

  • AM

    Anjali Menon

    Head of Retail Risk

    tense
  • RK

    Ravi Kulkarni

    Data Protection Officer

    neutral
  • PN

    Priya Nathan

    Data Engineering Lead

    supportive
  • FS

    Farah Sheikh

    PM — Risk Assistant

    supportive
  • VB

    Vikram Bhatt

    Credit Risk Modeler

    neutral
  • CO

    Compliance Ops

    Compliance

    tense

Deliverables

  • risk_conversations.parquet

    pending

    Final analysis-ready table: one row per (customer_id, conversation_id), PII-masked, encoded, normalized.

  • Product/Channel/Segment taxonomy (YAML)

    pending

    Canonical labels + synonym mapping used by the pipeline.

  • Masking report (JSON)

    pending

    Counts of PII patterns matched and masked per column, per source.

  • EDA notebook

    pending

    Top risk topics × segment, complaint_category vs risk_flag chi-square, missingness heatmap.

  • Data dictionary & lineage note

    draft

    Column-by-column definition, source, transform, and imputation rule.

Competencies assessed

  • Structured + Unstructured Data IntegrationWeight 20%
  • Text Cleaning & PII MaskingWeight 20%
  • Missing Value HandlingWeight 10%
  • Categorical Encoding & StandardizationWeight 15%
  • Numeric NormalizationWeight 10%
  • Exploratory Data AnalysisWeight 15%
  • Basic Risk Scoring & InterpretationWeight 10%

Tools

SQL WorkbenchSpreadsheetDoc EditorPDF ViewerData Explorer