STAR Interview Stories — BMO Experience Portfolio

Supplement to Interview Prep Guide — RQ09948 | Senior Data Analytical Specialist Scientist

1. TSYS Data Product Building

STAR 1.1 — End-to-End Data Product Build on TSYS TS2

"Describe a time you built an end-to-end analytics solution from data extraction through to dashboard delivery."

Situation

After BMO migrated its credit card processing from the legacy NCCS system to TSYS TS2, the bank's credit card portfolio ($12.8B outstanding, 2.4M accounts) had fragmented data across multiple systems. Risk and Fraud teams were operating on stale manual reports, with $18M+ in annual fraud losses and delayed OSFI regulatory submissions. There was no single, trusted, analytics-ready data asset from the TSYS platform.

Task

I was responsible for designing and delivering a Customer Credit Card Analytics & Risk Data Mart — a full data product on TSYS TS2 — that would serve both Fraud Analytics and Credit Risk Management from a single governed pipeline.

Action

  1. Consumer discovery first. I ran discovery sessions with both consumer groups. Fraud Analytics needed transaction-level detail with authorization data, POS entry modes, geo-location, and confirmed fraud labels — with near-real-time freshness. Risk Management needed account-level snapshots with delinquency buckets, credit limits, and payment behavior — with monthly granularity for IFRS 9 and Basel/OSFI reporting.
  2. Source data identification. I catalogued 12 TSYS core extract files — ACCT-MASTER, CARD-MASTER, STMT-HISTORY, TRAN-LOG, AUTH-LOG, PAYMENT-FILE, DELINQ-FILE, MONETARY-TRAN, DEMOGRAPHIC, REWARDS-ACCUM, PRICING-PLAN, and COLLECTION-QUEUE. Documented the TSYS hierarchy (Organization → Logo → Account → Card → Transaction).
  3. Layered architecture design. Bronze/Silver/Gold medallion architecture:
    • Bronze (Staging): Raw TSYS extracts — 1:1 from EBCDIC-to-ASCII converted files with audit columns. 90-day retention.
    • Silver (Integration): Cleansed and conformed — CDC for inserts/updates/deletes, surrogate keys, SCD Type 2 for account dimension, PII tokenized, PCI controls (PAN hashed SHA-256, CVV never extracted).
    • Gold (Presentation): Star schema with four fact tables — FACT_TRANSACTION, FACT_DAILY_BALANCE, FACT_DELINQUENCY, FACT_STATEMENT — and dimensions for Account (SCD2), Card, Merchant, Date, and Transaction Code.
  4. TSYS-specific transformations. 8 key rules: implied decimal conversion, packed decimal date to ISO, status code mapping (A/B/C/D/F/Z), utilization calculation, delinquency bucket derivation, and SCD2 merge logic.
  5. Data quality framework. Multi-layer quality framework with 8 rules — from file-level validation (record count vs. trailer exact match) to Gold-layer reconciliation (daily balance within $0.01). Data Quality Scorecard: completeness (99.95%), accuracy (100% balance reconciliation), timeliness (6 AM ET), uniqueness (0.001% duplicate rate), consistency (99.7%).
  6. Dashboard delivery. Power BI Portfolio Dashboard with KPIs: total outstandings, utilization rate, delinquency rate, roll rates, spend by MCC, vintage performance. Row-Level Security for regional access control.

Result

Likely Follow-Up Questions

  1. Why did you choose a medallion (Bronze/Silver/Gold) architecture over loading directly into a star schema?
  2. How did you handle PCI-DSS requirements for credit card data throughout the pipeline?
  3. If you had to rebuild this from scratch today, what would you do differently?

STAR 1.2 — TSYS Data Quality Issue Resolution During Migration

"Tell me about a time you discovered significant data quality issues in a source system. How did you identify them, what was the impact, and how did you resolve it?"

Situation

During the NCCS-to-TSYS migration, we discovered a 3% discrepancy in delinquency bucket totals between the migrated data in our data mart and the source system reports. This was flagged during post-load validation checks I had designed into the pipeline.

Task

I needed to identify the root cause, quantify the impact, and resolve the issue before it affected regulatory reporting. A 3% variance on a $12.8B portfolio would create a material misstatement in quarterly ECL provisions — potentially a $12.8M error in expected loss calculations.

Action

  1. Segmented the variance. Broke down the discrepancy by product (Logo), migration cohort, and account status. The variance was concentrated in accounts migrated from a specific NCCS product logo — not spread portfolio-wide.
  2. Traced the full lineage. Compared data at each layer: NCCS DELINQ-FILE → crosswalk table → TSYS DELINQ-FILE → Bronze → Silver → Gold. Variance first appeared at the crosswalk stage — a migration mapping issue, not a transformation bug.
  3. Root cause identification. The NCCS-to-TSYS crosswalk had incomplete coverage for accounts that were closed-but-delinquent. These accounts still owed money but weren't in the crosswalk because the migration team filtered on "active" accounts.
  4. Collaborated with migration team. Presented findings with specific examples — account numbers, amounts, expected vs. actual bucket totals. Extended the crosswalk to include all accounts with non-zero balances regardless of status.
  5. Permanent controls. Added a new DQ rule: "Sum of delinquency bucket amounts must reconcile to source within 0.01%" plus a crosswalk completeness validation before each load.

Result

Likely Follow-Up Questions

  1. How did you communicate the risk to leadership before the fix was in place?
  2. How do you design validation checks proactively — what's your framework for deciding what to validate?
  3. Have you seen similar "closed-but-active" data gaps in other contexts?

STAR 1.3 — Designing for Fraud Analytics Consumers

"Give an example of how you used innovative analytical thinking to create new insights from data integration across disparate systems."

Situation

After the TSYS data product went live, the Fraud Analytics team reported their detection models were missing 12–15% of counterfeit fraud cases. Models only consumed posted (cleared) transaction data from the TRAN-LOG in batch T+1 — by the time fraud was detected, the damage was done.

Task

Re-examine the data product design to enable real-time fraud pattern detection — specifically for card testing attacks, impossible travel (geo-velocity), and bust-out fraud — without rebuilding the entire pipeline.

Action

  1. Consumer re-discovery. Walked through specific detection needs with Fraud Data Scientists. Key insight: declined authorizations were fraud signals (card testing, BIN attacks) that our batch pipeline was discarding.
  2. Hybrid streaming design. Stream layer: CDC from AUTH-LOG with 5 critical fields within minutes. Batch layer: full enriched dataset every 4 hours. Combined view for investigation workflows.
  3. Fraud-specific feature engineering.
    • Card testing detection: 3+ transactions under $5 at same merchant within 10 minutes
    • Geo-velocity: Distance/time between consecutive transactions, flagging when implied speed exceeds 800 km/h
    • Bust-out scoring: Multi-signal composite — utilization spike (>90% from <50% in 60 days), cash advance emergence, minimum-payment-only shift, high-risk MCC concentration
  4. Fraud labels integration. Brought confirmed fraud flags with reason codes (counterfeit, CNP, lost/stolen, account takeover, friendly fraud) into the data product for supervised ML training.

Result

Likely Follow-Up Questions

  1. How did you validate that the streaming approach actually improved fraud detection rates?
  2. What were the trade-offs between real-time streaming and batch processing?
  3. How did you handle false positives in card testing detection?

2. Bank of the West Acquisition & Migration to BMO

STAR 2.1 — Large-Scale Data Migration: Bank of the West to BMO

"Describe a situation where you had to work with messy or incomplete data from multiple systems. How did you handle the complexity?"

Situation

In 2023, BMO completed the acquisition of Bank of the West (BancWest) — a US-based subsidiary of BNP Paribas with approximately $90B in assets, 1.8M customers, and 500+ branches. Bank of the West operated on entirely different core banking systems (FIS/Fidelity for deposits, different loan origination platforms, separate CRM) with different data standards, identifiers, and regulatory frameworks (US OCC/FDIC vs. Canadian OSFI).

Task

Part of the data migration team responsible for mapping, validating, and migrating customer, account, and transaction data. My specific responsibility: the analytical data layer — ensuring migrated data was analytics-ready for portfolio reporting, risk management, and regulatory submissions to both US and Canadian regulators.

Action

  1. Data inventory and profiling. Led profiling across 6 major source systems using Python pandas — completeness rates, format patterns, value distributions for 400+ data elements. Key issues: SSN used as primary keys (can't migrate to tokenized environment), US address formats, incompatible account numbering (10-digit vs. BMO 12-digit).
  2. Cross-system entity resolution. Multi-stage matching:
    • Stage 1: Exact SSN match — linked 72%
    • Stage 2: Fuzzy match (name + DOB + ZIP, Jaro-Winkler 0.88) — linked 18%
    • Stage 3: Probabilistic weighted scoring — linked 6%
    • Stage 4: Manual review of 4% ambiguous cases
  3. Source-to-target mapping. Detailed mapping for every element: field mapping, transformation rules (US dates to ISO, SSN tokenization), code value crosswalks, and business rule translations (different "active customer" definitions).
  4. Dual-regulatory data design. Dimension tables with dual classification attributes — DIM_ACCOUNT with both Canadian and US risk classification codes, Date dimension with both fiscal calendars and holiday flags.
  5. Parallel run validation. 90-day parallel run with automated reconciliation: total balances by product ($0.01 tolerance), customer counts, delinquency bucket totals, interest accrual amounts.

Result

Likely Follow-Up Questions

  1. What was your rollback strategy if the migration failed midway?
  2. How did you handle the 4% of records that couldn't be automatically matched?
  3. How did you manage the dual-regulatory reporting complexity long-term?

STAR 2.2 — Stakeholder Management During Bank of the West Integration

"Tell me about a time you worked in a multi-team environment spanning business and IT, especially during a high-pressure project."

Situation

The Bank of the West migration involved 200+ people across 15 workstreams. The data migration workstream sat at the intersection of all others — every team (retail banking, commercial lending, wealth, risk, compliance, finance) depended on accurate data for their go-live readiness. Conflicting priorities and competing timelines created constant tension.

Task

Coordinate across the core banking conversion team (source extracts), enterprise data architecture (target schemas), risk team (regulatory data on Day 1), and Bank of the West's legacy team (source system knowledge).

Action

  1. Shared data dictionary. Created a bilingual data dictionary mapping Bank of the West terminology to BMO terminology with explicit definitions — "active account" meant different things in each organization.
  2. Priority-based migration sequence. Wave 1 (Critical): Customer master, balances, delinquency — regulatory reporting Day 1. Wave 2 (High): 24-month transaction history, credit limits, payments — risk models within 30 days. Wave 3 (Standard): Full 7-year history, rewards, marketing — within 90 days.
  3. Weekly cross-team reconciliation. 30-minute weekly sync with shared RAG status board. When Risk needed delinquency history for stress testing but core banking said it wasn't in Wave 1, I mediated with a lightweight interim extract for 12 critical fields.
  4. Bridged US-Canada cultural gap. Adapted communication: visual data flow diagrams for visual teams, detailed field-level specs for precision-oriented teams.

Result

Likely Follow-Up Questions

  1. Can you give a specific example of a conflict between teams and how you resolved it?
  2. How did you handle pushback when teams disagreed with the migration priority order?
  3. What would you do differently in managing the stakeholders if you could redo it?

3. SWIFT MT to ISO 20022 Migration

STAR 3.1 — SWIFT ISO 20022 Payments Data Transformation

"Describe a situation where a major external change required you to redesign data structures and processes. How did you approach it?"

Situation

SWIFT mandated that all cross-border payment messaging migrate from the legacy MT format to ISO 20022 by November 2025 — a global industry transformation. At BMO, the migration impacted every downstream system consuming payment data: AML, reconciliation, regulatory reporting, and the data warehouse. The legacy MT format was fixed-field and character-limited; ISO 20022 is XML-based with 80+ structured fields (vs. ~15 in MT).

Task

Redesign the payments data layer in the enterprise data warehouse to accommodate ISO 20022 — ensuring backward compatibility with legacy MT during coexistence, while enabling richer analytics.

Action

  1. Impact analysis. Mapped every MT field (MT103 customer transfers, MT202 bank-to-bank) to ISO 20022 equivalents (pacs.008, pacs.009). Key findings: field expansion from ~15 to 80+, structured addresses replacing free-text blocks, new elements (LEI, purpose codes, Ultimate Debtor/Creditor).
  2. Dual-format canonical model. Separate parsers for MT (fixed-field) and ISO 20022 (XML) normalizing into a common staging format. Canonical table with all ISO 20022 fields as target — MT records carry NULLs for unavailable fields. Every record carries a message_format indicator (MT or MX).
  3. Enhanced analytics enablement. Structured beneficiary data improved sanctions screening accuracy. Created DIM_PURPOSE_CODE for payment categorization (SALA/salary, TAXS/tax, DIVD/dividend). Enabled correspondent banking cost analysis.
  4. Coexistence quality rules. MT-to-ISO mapping completeness checks, structured address parsing validation against postal reference data, LEI validation against GLEIF database, volume/amount reconciliation between pipelines.
  5. Stakeholder education. Created "What Changes for You" documents per consumer group (AML, Finance, Ops, Regulatory) showing before/after data structures.

Result

Likely Follow-Up Questions

  1. How did you handle messages that didn't conform to either standard during the transition?
  2. What was the biggest technical challenge in the dual-format data model?
  3. How did you ensure downstream consumers actually adopted the new enriched fields?

STAR 3.2 — Managing Data Governance During ISO 20022 Transition

"How do you approach data governance when standards and formats are changing underneath you?"

Situation

The ISO 20022 migration changed the meaning and structure of core data elements across BMO's payments ecosystem. The existing data dictionary, lineage docs, and quality rules were all built around MT fields. If governance didn't keep pace, downstream consumers would lose trust in the data.

Task

Update the data governance framework — dictionary, lineage, quality rules, metadata — for the ISO 20022 transition while maintaining governance integrity for legacy MT data during 2-3 year coexistence.

Action

  1. Data dictionary versioning. Every element received a format_context tag (MT, ISO20022, or CANONICAL). New fields documented with definitions, valid values, source mapping. Legacy fields marked with deprecation timeline. Created MT → ISO 20022 crosswalk reference.
  2. Dual lineage. Updated source-to-target for every transformation. MT103 Field 50 → parsed into debtor_name, debtor_address components. ISO 20022: pacs.008 Dbtr/Nm → debtor_name. Both paths maintained during coexistence.
  3. Format-aware quality rules. Rewrote rules with format conditions. Added ISO 20022-specific validations: LEI format (20-char alphanumeric), structured address completeness, purpose code validation.
  4. Governance committee engagement. Presented changes with impact assessment: affected reports, enriched metrics, updated processes. Established quarterly review cadence for MT-to-ISO volume tracking.

Result

Likely Follow-Up Questions

  1. How did you get buy-in from teams to adopt the new data dictionary?
  2. What was the most challenging aspect of maintaining dual lineage?
  3. How did you measure success of the governance framework?

4. Customer Unification — Golden Record

STAR 4.1 — Building a Customer Golden Record Through Entity Resolution

"Tell me about a time you integrated data from multiple sources to create a single source of truth. What challenges did you face?"

Situation

BMO had customer data fragmented across 7 systems — retail banking, credit cards (TSYS), wealth management, online banking, mortgage origination, Bank of the West, and contact centre CRM. A single customer could appear as three different records with different IDs, name spellings, and addresses. No unified view for risk exposure, personalization, or KYC compliance.

Task

Design the entity resolution logic and golden record that would become the single source of truth for customer identity across all lines of business.

Action

  1. Customer data profiling. Profiled 7 source systems totalling ~24.5M records. Found 30-35% estimated duplication. Key challenges: name variations ("Robert" vs. "Bob"), address format inconsistencies, US/Canada format differences.
  2. 5-stage entity resolution pipeline.
    • Stage 1 — Deterministic SIN/SSN match: linked 55%
    • Stage 2 — Composite key (Last Name + DOB + Postal/ZIP): linked 20%
    • Stage 3 — Probabilistic fuzzy matching (Jaro-Winkler, weighted scoring, threshold >0.85 auto-link): linked 12%
    • Stage 4 — Transitive closure (A=B and B=C → A=C): linked 5%
    • Stage 5 — Manual stewardship queue for remaining 8%
  3. Survivorship rules. Legal name from most recent KYC source. DOB from SIN/SSN-verified source. Address from most recent non-PO-Box. Phone from most recent 2FA use. Customer segment from highest tier across all relationships.
  4. Hub-and-spoke data model. Hub: MASTER_CUSTOMER with Enterprise Customer ID (ECID), golden attributes, confidence score. Spokes: link tables to every source system ID. SCD Type 2 for attribute history. Every golden attribute tagged with source system origin.
  5. Ongoing monitoring. Daily duplicate detection for new accounts, merge/split tracking dashboard, completeness scorecard, cross-sell readiness metric.

Result

Likely Follow-Up Questions

  1. How did you handle false positives — two different people incorrectly merged?
  2. What was the performance impact of the multi-stage matching on processing time?
  3. How do you keep the golden record current as new data arrives daily?

STAR 4.2 — Privacy and Compliance in Customer Unification

"How do you handle a situation where a data project involves sensitive personal information and must comply with privacy legislation?"

Situation

The Customer Unification project required linking personal information across 7 systems — names, DOB, SIN/SSN, addresses, phone numbers, financial holdings, and transaction patterns. Data subject to multiple privacy regimes: PIPEDA (federal), provincial legislation, US privacy laws (for BancWest), and internal governance policies. Cross-referencing originally siloed personal information raised the question of whether this use was consistent with original collection purposes.

Task

Ensure the data design and entity resolution approach complied with all applicable privacy requirements while delivering the analytical value of a unified customer view.

Action

  1. Privacy Impact Assessment (PIA). Conducted PIA with Privacy Office. Key determinations: unification permissible under PIPEDA's "consistent purpose" provision for service and risk management; marketing use requires explicit opt-in; US data subject to additional CCPA constraints.
  2. Privacy-by-design in the data model. Consent tracking table linked to each ECID (service, risk, marketing, analytics, third-party sharing). Purpose-based views:
    • VW_CUSTOMER_RISK — full view including SIN/SSN, total exposure (Risk/Compliance only)
    • VW_CUSTOMER_ANALYTICS — anonymized with segment, product count, tenure (analytics teams)
    • VW_CUSTOMER_MARKETING — consented customers only, no SIN/SSN (marketing)
  3. Cross-border data handling. US PII stored on US-resident infrastructure. Cross-border analytics used tokenized identifiers — actual SSN never crossed the border. Risk exposure calculations used aggregated figures only.
  4. Retention and deletion capability. ECID tombstoning for customers who closed all relationships and requested deletion, with regulatory-required retention (7-year AML) preserved.

Result

Likely Follow-Up Questions

  1. How would you adapt this approach to Ontario's FIPPA instead of PIPEDA?
  2. How did you audit that purpose-based views were being used correctly?
  3. How would you handle a data subject access request under this architecture?

5. Additional Suggested STAR Stories

STAR 5.1 — Predictive Analytics for Credit Card Customer Attrition

"Give an example of how you used predictive analytics or statistical modelling to drive a business decision."

Situation

After the TSYS data product was operational, the Retail Credit Cards team noticed attrition had increased from 8% to 12% annualized, particularly in the BMO Rewards Mastercard segment. No data-driven way to predict which customers would leave.

Task

Build a predictive attrition model identifying customers at high risk of closing their card within 90 days, enabling proactive retention offers.

Action

  1. Problem framing. Binary classification: will this customer close within 90 days? Success metric: precision at top decile — each retention call cost $15, capacity was 5,000 calls/month.
  2. Feature engineering. 45 features from the Gold layer: transaction behavior (3-month spend slope, frequency change, MCC Shannon entropy), balance/utilization trends, engagement signals (months since last transaction, rewards redemption, login frequency), tenure/lifecycle, competitive indicators (declining transactions + maintained balance).
  3. Model development. Logistic Regression baseline: 62% precision at top decile. XGBoost after hyperparameter tuning (5-fold CV): 78% precision. Key SHAP features: declining monthly spend, decreasing frequency, low rewards redemption, high tenure with no limit increase.
  4. Deployment. Monthly batch scoring of all 2.4M accounts. Top 5,000 passed to retention team with top 3 SHAP-derived risk factors per customer so agents could tailor offers.

Result

Likely Follow-Up Questions

  1. How did you handle class imbalance in the attrition model?
  2. Why XGBoost over other algorithms like Random Forest or neural networks?
  3. How did you ensure the model wasn't biased against certain customer segments?

STAR 5.2 — Data Governance Framework for Post-Acquisition Unified Environment

"What is your experience with data governance and data lineage?"

Situation

After the BancWest acquisition and TSYS migration, BMO's data environment had grown significantly — new systems, feeds, and regulatory requirements. Governance hadn't kept pace: overlapping definitions, no centralized catalog, inconsistent lineage documentation.

Task

Establish a unified data governance framework for credit cards and payments domains covering cataloging, ownership, quality standards, lineage, and access controls across Canadian and US operations.

Action

  1. Data inventory. 47 datasets identified — sources, refresh frequencies, consumers, owners. 12 had no owner documented. 8 had overlapping definitions between legacy BMO and BancWest.
  2. Ownership RACI. Assigned data owners (SVP-level accountable) and stewards (responsible) per domain: Credit Card Account → SVP Retail Credit Cards, Payments → VP Payments Ops, Customer Master → CDO function.
  3. Unified data dictionary. 520+ field definitions with: business definition, technical definition, source systems, transformation lineage, classification (public/internal/confidential/restricted), regulatory relevance (IFRS 9, Basel, OSFI, OCC, PIPEDA, CCPA).
  4. Automated DQ monitoring. Replaced manual spreadsheets with pipeline-embedded checks feeding a dashboard tracking 6 dimensions (completeness, accuracy, timeliness, uniqueness, consistency, validity). Monthly governance committee review.
  5. End-to-end lineage. Top 20 critical data products documented: source → Bronze → Silver → Gold → Dashboard. Both technical (field-level) and visual (data flow diagram) formats.

Result

Likely Follow-Up Questions

  1. How did you handle resistance from teams that didn't want to own their data?
  2. What tools did you use for the data catalog?
  3. How did you prioritize which datasets to govern first?

STAR 5.3 — Translating Vague Requirements into Actionable Analytics

"A business stakeholder comes to you and says 'we need better reporting.' How do you approach this?"

Situation

After the BancWest integration, the US retail banking leadership said "we can't see how the acquired portfolio is performing — we need better reporting." No specific metrics, no defined KPIs, weeks to get basic statistics.

Task

Translate this vague request into concrete analytical deliverables giving leadership visibility into the acquired portfolio's performance.

Action

  1. Decision-focused discovery. Three structured interviews: US Retail Head ("What decisions in next 90 days?"), Risk Director ("What keeps you up at night?"), Finance ("What does the Board expect?").
  2. Decision-to-data mapping. Each decision → specific metrics. "Adjust credit policies" → roll rates, vintage analysis, approval trends. "Board reporting" → NIM, NCL rate, customer growth vs. acquisition projections.
  3. Gap analysis. 70% from existing data mart, 20% required new calculations, 10% needed data not yet migrated (Wave 3).
  4. Phased delivery. Week 1-2: Executive summary with 8 top KPIs (quick win). Week 3-6: Full drill-down dashboard. Week 8-12: Predictive vintage forecasting layer.
  5. Prototype iteration. Power BI mockup in 3 days. Stakeholder feedback: needed branch-level granularity and separated migrated vs. organic originations. Incorporated before full build.

Result

Likely Follow-Up Questions

  1. What if the stakeholders had disagreed about which metrics were most important?
  2. How did you handle the 10% of data that wasn't yet available?
  3. How did you measure success of the dashboard beyond adoption numbers?

STAR 5.4 — Building a Power BI Dashboard for Non-Technical Stakeholders

"Tell me about your experience building dashboards for non-technical stakeholders. How did you ensure adoption?"

Situation

Credit Risk leadership — SVP, regional directors, portfolio managers — relied on a 40-page monthly PDF compiled manually over 3 days, arriving 2 weeks after month-end. Static, couldn't answer follow-up questions. Leadership wanted real-time visibility but had limited technical fluency.

Task

Design and build an interactive Power BI dashboard suite replacing the manual report. Critical constraint: intuitive enough for non-technical executives to self-serve.

Action

  1. Decision mapping. Asked "Walk me through the last decision you made using the monthly report" — not "what metrics do you want." SVP: delinquency trending up or down? Regional Director: regions deteriorating? Portfolio Manager: vintages underperforming?
  2. Power BI data model. Star schema on TSYS Gold layer. Key DAX: delinquency rate, YoY via SAMEPERIODLASTYEAR, roll rates with conditional formatting (RAG against 3-month moving average). Date dimension aligned to BMO fiscal year (November-October).
  3. Progressive disclosure (3 pages).
    • Page 1 — Executive Summary: 4 KPI cards with sparklines + RAG. One bar chart ranking products by delinquency rate change. Designed for the SVP's 30-second scan.
    • Page 2 — Regional Drilldown: Map visualization, click-to-filter for trend lines and vintage by region.
    • Page 3 — Vintage & Origination: Cumulative NCL curves by MOB, slicers for time/score/channel.
  4. Row-Level Security. RegionalDirector role (filtered by province) and Executive role (full portfolio). Tested with 5 user accounts.
  5. Adoption strategy. Clickable prototype → individual stakeholder walkthroughs → 1-page quick reference card → two 30-min training sessions → daily Gateway refresh at 6:30 AM → weekly PDF email subscription for email-preference users.

Result

Likely Follow-Up Questions

  1. How did you handle dashboard performance with large datasets?
  2. How did you handle requests for additional features after launch?
  3. How would you make this dashboard AODA/WCAG compliant for a government context?

STAR 5.5 — Production Support and Infrastructure Sustainment for the TSYS Data Pipeline

"Describe your experience supporting production data systems, managing releases, or handling infrastructure sustainment."

Situation

After the TSYS data product went to production, it became a Tier 1 critical system — Fraud, Risk, and Regulatory all depended on daily delivery by 6 AM ET. Pipeline processed ~2M records nightly from 12 extract files. Any failure meant Fraud couldn't run detection, Risk couldn't update delinquency, and OSFI reporting would be delayed.

Task

Own production operations: incident management, quarterly releases, infrastructure upgrades — maintaining 99%+ uptime and SLA adherence while evolving the data product.

Action

  1. Multi-tier monitoring.
    • Tier 1: File arrival checks every 15 min (1-5 AM), alert if missing by 3:30 AM
    • Tier 2: Pipeline DAG stage monitoring — runtime, record counts, errors. Alert at 150% of historical average
    • Tier 3: Post-load DQ gates — row count reconciliation, balance reconciliation, referential integrity. Red alert blocks downstream refresh
    • Tier 4: Consumer SLA dashboard — Gold available by 6 AM, 90-day trend
  2. Runbook. 15 common failure scenarios with symptoms, diagnostics, resolution, escalation, and estimated time. Examples: file delayed (contact mainframe ops, T-1 fallback), EBCDIC conversion failure (check schema drift), SCD2 merge conflict.
  3. Quarterly release management. SDLC: DEV → 2-week parallel UAT → Sunday PROD promotion. Change request with rollback plan, impact assessment, governance sign-off. 3-day hypercare post-release.
  4. Spark migration. Volumes grew 35% after BancWest (1.8M accounts added). SQL Server breaching SLA 2-3×/month. Migrated Silver-to-Gold transforms to PySpark on Databricks: rearchitected 8 heaviest jobs, implemented date_key partitioning, moved from full-refresh to incremental merge. Processing: 3.5 hrs → 55 min (74% reduction).
  5. Metrics tracked monthly. Uptime: 99.4% (target 99%). SLA adherence: 98.5% (target 95%). MTTD: 12 min. MTTR: 45 min. Incidents: 6.1/month → 2.3/month.

Result

Likely Follow-Up Questions

  1. How did you decide which jobs to migrate to Spark vs. keep on SQL Server?
  2. What was the hardest production incident and how did you resolve it?
  3. How did you balance new feature development with production support?

Story-to-Question Mapping Matrix

Use this to quickly select the right STAR story. Rule: Never use the same project for back-to-back questions.

Interview Question ThemePrimary StoryBackup Story
End-to-end analytics solution1.1 TSYS Data Product5.3 Vague Requirements
Data quality issues1.2 Migration DQ4.1 Entity Resolution
Machine learning / predictive model5.1 Attrition Model1.3 Fraud Detection
Messy / incomplete data4.1 Golden Record2.1 BancWest Migration
ETL pipeline design1.1 TSYS Pipeline3.1 ISO 20022
Data governance / lineage5.2 Governance Framework3.2 ISO 20022 Governance
Privacy / compliance4.2 Privacy in Unification3.1 Cross-border data
Cross-team collaboration2.2 BancWest Stakeholders1.3 Fraud Consumers
External change management3.1 ISO 200222.1 Acquisition
Innovative / creative approach1.3 Fraud Streaming5.1 Attrition Features
Stakeholder requirements5.3 Vague Requirements2.2 BancWest Integration
Master data management4.1 Golden Record4.2 Privacy in MDM
Regulatory reporting3.1 ISO 20022/SWIFT1.1 OSFI/Basel
Large-scale data migration2.1 BancWest Migration1.2 NCCS-TSYS
Power BI / R Shiny dashboard5.4 Power BI for Executives5.3 BancWest Dashboard
Production support / release mgmt5.5 TSYS Pipeline Ops1.2 Migration DQ controls
Working with non-technical executives5.4 Power BI5.3 Vague Requirements

Key Numbers to Memorize

MetricValueStory
BMO credit card portfolio size$12.8B outstanding, 2.4M accountsTSYS
Bank of the West assets~$90B, 1.8M customers, 500+ branchesBancWest
TSYS data product annual value$28M–$93MTSYS
Golden record deduplication rate54% (24.5M → 11.2M)Golden Record
Entity resolution automated rate92% automated, 8% manualGolden Record
ISO 20022 field expansion~15 → 80+ fieldsSWIFT
Sanctions screening improvement22% better match accuracySWIFT
Attrition model precision (top decile)78%Attrition
Attrition rate improvement12% → 9.5%Attrition
Migration DQ variance caught3% → 0.0%TSYS DQ
Data quality score improvement88% → 96%Governance
KYC review reduction40% fewer duplicate reviewsGolden Record
Dashboard adoption47 → 62 usersPower BI
Manual report time saved3 days/month eliminatedPower BI
Pipeline uptime99.4% over 12 monthsProduction Support
SLA breach reduction6.1 → 1.2/month (80%)Production Support
Spark processing gain3.5 hrs → 55 min (74%)Production Support

Transition Phrases Between Stories

Follow-Up Answer Bank

Detailed answers for the follow-up questions listed within each STAR story above. Click any question link in a story to jump here.

STAR 1.1

Q: Why did you choose a medallion (Bronze/Silver/Gold) architecture over loading directly into a star schema?

A: Three reasons. First, debuggability — when a data quality issue surfaces in Gold, I can trace it back through Silver and Bronze to pinpoint exactly where the transformation went wrong. With direct loading, troubleshooting is much harder because you've lost the intermediate states. Second, reusability — the Silver layer serves multiple Gold-layer consumers (Fraud wants transaction-level detail, Risk wants account-level snapshots). If I'd loaded directly into one star schema, it would've been optimized for one consumer. Third, resilience — if a Gold transformation fails, I can rerun it from Silver without re-extracting from TSYS, which involves mainframe coordination and has a narrow batch window.

Back to STAR 1.1

STAR 1.1

Q: How did you handle PCI-DSS requirements for credit card data throughout the pipeline?

A: PCI-DSS compliance was designed in from day one, not bolted on after. At the Bronze layer, PAN (Primary Account Number) was hashed using SHA-256 immediately upon ingestion — the full PAN was never stored in our environment. CVV was never extracted at all — I worked with the TSYS extract team to exclude it from the file specification. In Silver, we tokenized additional PII fields (name, address) using BMO's enterprise tokenization service. Access controls were role-based, with separate service accounts for each pipeline stage. All data in transit used TLS 1.2+, and data at rest was encrypted with AES-256. The pipeline ran inside a PCI-scoped network segment. We maintained a cardholder data flow diagram documenting every point where cardholder data existed, and this was reviewed during the annual PCI-DSS assessment.

Back to STAR 1.1

STAR 1.1

Q: If you had to rebuild this from scratch today, what would you do differently?

A: Two things. First, I'd invest more in schema evolution handling upfront. TSYS occasionally changed extract file layouts (adding fields, changing field lengths), and our initial pipeline was brittle to these changes. Today I'd implement schema registry with compatibility checks so the pipeline could gracefully handle additive schema changes without manual intervention. Second, I'd push for streaming from the start for the Fraud use case instead of retrofitting it later. We built the batch pipeline first and then added the streaming layer for AUTH-LOG — but having a unified streaming-first architecture with batch materialization would have been cleaner and avoided the dual-pipeline complexity.

Back to STAR 1.1

STAR 1.2

Q: How did you communicate the risk to leadership before the fix was in place?

A: I escalated within 24 hours of confirming the root cause. I prepared a one-page brief with three elements: (1) the size of the issue — 3% variance equals approximately $12.8M in potential ECL misstatement, (2) the scope — isolated to one product logo's migrated closed-but-delinquent accounts, not portfolio-wide, and (3) the timeline — fix estimated at 5 business days. I also confirmed that no regulatory submissions had been made using the affected data (timing was fortunate — we caught it between submission cycles). Leadership appreciated the specificity — knowing it was $12.8M on one logo was far more useful than saying "we have a data quality problem."

Back to STAR 1.2

STAR 1.2

Q: How do you design validation checks proactively — what's your framework?

A: I use a layered approach aligned to the data pipeline stages. Layer 1 — Source validation: File-level checks (record count vs. trailer, file size within expected range, expected columns present). Layer 2 — Transformation validation: Row count in = row count out (±expected filter rate), no orphan foreign keys, SCD2 merge counts within expected range. Layer 3 — Business rule validation: Domain-specific rules (balances must be non-negative, delinquency buckets must sum to total delinquent balance, status codes must be in valid set). Layer 4 — Cross-source reconciliation: Key totals must match the source system's own reports within tolerance. The key principle is that every transformation step should have at least one validation that would catch a regression.

Back to STAR 1.2

STAR 1.2

Q: Have you seen similar "closed-but-active" data gaps in other contexts?

A: Yes, this is a recurring pattern in data migrations. In the Bank of the West integration, we saw something analogous: accounts flagged as "dormant" in the BancWest system still had non-zero balances and were accruing interest. The migration team's initial extract filter excluded dormant accounts. Because we'd learned from the TSYS experience, we proactively validated that every account with a non-zero balance — regardless of status — was included in the migration scope. That same lesson applies in government: when migrating between systems, status-based filters are dangerous because "status" and "still-has-data" are independent concepts. I always recommend balance/activity-based inclusion criteria rather than status-based.

Back to STAR 1.2

STAR 1.3

Q: How did you validate that the streaming approach actually improved fraud detection rates?

A: We ran a controlled comparison over 60 days. The existing batch-only detection continued running as before. In parallel, the new hybrid streaming + batch pipeline also ran, but its alerts went to a separate review queue rather than directly to operations. After 60 days, we compared: the hybrid system flagged 14% more confirmed fraud cases that the batch-only system missed — these were primarily card testing patterns (rapid small transactions) that resolved within hours, so by T+1 batch they'd already progressed to larger fraudulent purchases. The false positive rate was slightly higher (8% vs. 6% for batch-only), but the net value of catching the additional true fraud far exceeded the cost of reviewing extra false positives.

Back to STAR 1.3

STAR 1.3

Q: What were the trade-offs between real-time streaming and batch processing?

A: Three main trade-offs. Completeness vs. speed: The stream layer had only 5 key fields available within minutes, while the batch layer had 40+ enriched fields but arrived every 4 hours. Fraud needed speed for detection but completeness for investigation — hence the hybrid. Infrastructure cost: Streaming infrastructure is more expensive to operate and monitor than batch. We justified it by quantifying the fraud losses that occurred in the T+1 gap. Complexity: Dual pipelines mean dual monitoring, dual testing, and potential consistency issues. We mitigated this with a reconciliation check: stream counts had to match batch counts within 0.5% tolerance at each 4-hour merge point.

Back to STAR 1.3

STAR 1.3

Q: How did you handle false positives in card testing detection?

A: The initial rule (3+ transactions under $5 at the same merchant within 10 minutes) had a 15% false positive rate — legitimate scenarios like transit fare taps, vending machines, and parking meters. We refined it by adding merchant category exclusions (MCC codes for transit, parking, vending), requiring a minimum of 5 transactions (not 3) for certain MCC categories, and adding a card-level historical pattern check — if the card regularly makes small transactions at that merchant, it's likely legitimate. After tuning, the false positive rate dropped to 6%. The key learning is that fraud detection rules need domain-specific tuning, and you should collaborate directly with the fraud investigators who review the alerts to understand what "obviously not fraud" looks like.

Back to STAR 1.3

STAR 2.1

Q: What was your rollback strategy if the migration failed midway?

A: We designed a "no-regret" migration approach. Before each migration wave, we took a complete snapshot of the target environment. If any wave failed validation (reconciliation breaches, missing records, incorrect transformations), we could restore the snapshot and re-run. For the analytical data layer specifically, I maintained a parallel "pre-migration" data mart that continued receiving feeds from Bank of the West's legacy systems until the 90-day parallel run confirmed parity. This meant the business was never without reporting capability. The rollback plan was tested twice during dress rehearsals before the actual migration weekend.

Back to STAR 2.1

STAR 2.1

Q: How did you handle the 4% of records that couldn't be automatically matched?

A: The 4% (~72,000 records) went to a manual stewardship queue. I designed a review UI showing side-by-side records with match confidence scores and highlighting the specific fields that differed. Data stewards from both organizations reviewed and made decisions. We categorized the ambiguous cases: 40% were name variations too extreme for fuzzy matching (married name vs. maiden name), 30% were address discrepancies (customer moved between systems), 20% were genuine separate individuals who happened to share similar attributes, and 10% were data quality issues (typos, wrong DOB). The manual review took 6 weeks with a team of 4 stewards. We also used the manual review outcomes to retrain our matching thresholds for future use.

Back to STAR 2.1

STAR 2.1

Q: How did you manage the dual-regulatory reporting complexity long-term?

A: The key was building the dual classification into the data model itself rather than handling it in reporting logic. DIM_ACCOUNT carried both OSFI and OCC risk classification codes as separate columns. The Date dimension had both Canadian and US fiscal calendars. Reporting views were then simple filters — VW_OSFI_PORTFOLIO filtered to Canadian-booked accounts and used OSFI risk buckets; VW_OCC_PORTFOLIO filtered to US-booked accounts and used OCC risk buckets. A combined enterprise view aggregated both. This approach meant the complexity was absorbed once in the data layer and didn't multiply across every report. We also documented a regulatory data dictionary showing which fields mapped to which regulatory requirement.

Back to STAR 2.1

STAR 2.2

Q: Can you give a specific example of a conflict between teams and how you resolved it?

A: The most significant conflict was between the Risk team and the Core Banking conversion team. Risk needed 24 months of delinquency history available on Day 1 for their stress testing models — this was a regulatory requirement. Core Banking said historical data migration was scheduled for Wave 3 (90 days post-go-live) because it was complex and they prioritized transactional accuracy for Day 1 operations. Both had valid reasons. My resolution: I proposed extracting a lightweight subset — just 12 fields per account per month (DPD, balance, status, limit) for 24 months — as a separate targeted extract. This was far simpler than full historical migration because it didn't require transaction-level detail. Risk got what they needed for stress testing, Core Banking didn't have to accelerate Wave 3, and the effort was about 2 weeks of work instead of the 8 weeks that full historical migration would have required.

Back to STAR 2.2

STAR 2.2

Q: How did you handle pushback when teams disagreed with the migration priority order?

A: I anchored prioritization to regulatory deadlines — which are non-negotiable — and then business impact. When Marketing pushed for customer preference data to be in Wave 1 (they wanted to run a welcome campaign), I asked: "What happens if this data arrives 30 days later?" Answer: campaign delayed by a month. Then I asked Risk: "What happens if delinquency data arrives 30 days late?" Answer: regulatory submission missed, potential OSFI finding. That contrast made the priority self-evident. The principle is to always translate priority disputes into consequence language — what's the cost of delay for each item? — rather than debating whose work is more important.

Back to STAR 2.2

STAR 2.2

Q: What would you do differently in managing the stakeholders if you could redo it?

A: I'd establish the shared data dictionary earlier — ideally in the first week, not the third week. The first two weeks had several misunderstandings that could have been avoided (e.g., "current balance" meant something different in each system). I'd also create a formal data dependency map showing which workstreams depended on which data domains, published on day one. We built this incrementally as dependencies surfaced, but having it upfront would have preempted several "why don't I have my data yet?" conversations. Finally, I'd embed a data liaison within each major workstream rather than having everyone come to the weekly sync — this would have caught issues faster.

Back to STAR 2.2

STAR 3.1

Q: How did you handle messages that didn't conform to either standard during the transition?

A: We encountered three categories of non-conforming messages. First, truncated MT messages from smaller correspondent banks that didn't fill all optional fields — these mapped cleanly to ISO 20022 with NULLs for missing optional elements. Second, hybrid messages during early adoption where some banks sent ISO 20022 messages with MT-style content stuffed into structured fields (e.g., a full unstructured address crammed into the street name field). For these, I built a "best-effort structured parsing" routine that attempted to parse the unstructured content and flagged it for manual review if parsing confidence was below 80%. Third, proprietary extensions — some banks added custom fields. These were captured in an overflow XML element and logged for analysis but not mapped to the canonical model. We tracked non-conforming message rates in a weekly report to the payments team.

Back to STAR 3.1

STAR 3.1

Q: What was the biggest technical challenge in the dual-format data model?

A: The biggest challenge was address data. MT103 stores beneficiary address as a single block of 4 lines × 35 characters — completely unstructured. ISO 20022 has separate fields for street, building number, post code, city, country, etc. During coexistence, I needed to make these comparable for analytics and sanctions screening. I built an address parsing service that attempted to decompose MT free-text addresses into structured components using pattern matching (postal code regex by country, city name lookup tables, known street suffix patterns). It worked well for US/Canadian/UK addresses (85%+ success) but poorly for addresses in non-Latin scripts or countries with different postal conventions. The fallback was to store the original free-text alongside the parsed components and let consumers choose which to use based on their accuracy requirements.

Back to STAR 3.1

STAR 3.1

Q: How did you ensure downstream consumers actually adopted the new enriched fields?

A: Adoption was a deliberate effort, not assumed. First, the "What Changes for You" documents showed each team the specific business value of the new fields — not just "new fields are available" but "here's how purpose codes let you categorize 85% of payment flows instead of 30%." Second, I worked with each consumer team to build prototype reports using the new fields so they could see the value concretely. Third, I tracked field usage metrics — which teams were querying which ISO 20022-specific fields. After 3 months, I noticed the AML team was still using the legacy unstructured address for screening instead of the new structured fields. I set up a working session, demonstrated the 22% screening accuracy improvement, and helped them update their screening queries. Adoption is a change management exercise, not just a technical delivery.

Back to STAR 3.1

STAR 3.2

Q: How did you get buy-in from teams to adopt the new data dictionary?

A: I didn't try to get buy-in through a policy mandate — that creates compliance without commitment. Instead, I used a pain-point-first approach. I identified the 3 most common data misunderstandings from the previous quarter (e.g., "payment amount" was interpreted differently by Finance and Operations, causing a $2.1M reconciliation break). I showed how the data dictionary would have prevented each incident. When people see that the governance tool solves a problem they've personally experienced, adoption follows. I also made the dictionary easy to consume — searchable, with both plain-language and technical definitions, embedded in the tools people already used (linked from SQL comments, referenced in BI report footers). Making it accessible reduces friction.

Back to STAR 3.2

STAR 3.2

Q: What was the most challenging aspect of maintaining dual lineage?

A: The hardest part was keeping lineage synchronized during schema changes. When the ISO 20022 standard issued an update (e.g., adding a new optional field to pacs.008), I needed to update the ISO lineage path and determine if the MT side had any equivalent. During the coexistence period, we had approximately 3 schema updates from SWIFT, each requiring lineage updates across 50-80 affected fields. I addressed this by building a semi-automated lineage update tool: when a new field was added to the ISO 20022 schema, it would auto-generate the lineage template with the canonical mapping and flag it for human review. This reduced the lineage update effort from 2 days per schema change to about 4 hours.

Back to STAR 3.2

STAR 3.2

Q: How did you measure success of the governance framework?

A: Four metrics. (1) Data incident rate: Number of data-related production incidents per month — decreased from 4.2 to 1.1 over 6 months. (2) Time to resolve data questions: When a consumer had a question about a field's meaning or source, how long did it take to answer? Went from "send an email and wait 2 days" to "look it up in the dictionary in 5 minutes." (3) Audit readiness: When auditors asked "where does this number come from?" we could show lineage end-to-end within 15 minutes, vs. 2-3 days of investigation previously. (4) Dictionary adoption: Monthly unique users accessing the data dictionary — grew from 45 to 180 over 6 months. The most telling metric was that teams started contributing to the dictionary voluntarily (adding their own domain-specific fields), which showed it had become a living asset, not shelf-ware.

Back to STAR 3.2

STAR 4.1

Q: How did you handle false positives — two different people incorrectly merged?

A: False merges are more dangerous than false non-merges because they combine two people's financial data into one record — creating privacy and risk issues. I built three safeguards. (1) Hard blocks: Even if the matching score was high, if two records had different genders AND different dates of birth (both populated), they could not be auto-merged — routed to manual review. (2) Post-merge monitoring: A daily anomaly detection job scanned recently merged records for suspicious patterns — e.g., a merged record suddenly showing accounts in two provinces with transactions on the same day, or drastically different income levels between linked accounts. (3) Split capability: If a false merge was identified, the golden record could be "split" — the ECID was deactivated and two new ECIDs created with the correct account assignments. We tracked the split rate: it averaged 0.02% of total merges, which was within our acceptable threshold.

Back to STAR 4.1

STAR 4.1

Q: What was the performance impact of the multi-stage matching on processing time?

A: The initial implementation took 14 hours for a full match run across 24.5M records — unacceptable for a production process. I optimized in three ways. (1) Blocking: Instead of comparing every record to every other record (O(n²)), I used blocking keys (first 3 characters of last name + birth year) to create smaller comparison groups. This reduced comparisons from ~300 billion to ~50 million. (2) Staged processing: Stage 1 (SIN exact match) was very fast and resolved 55% — only the unmatched 45% flowed to Stage 2, and only the remaining unmatched flowed to Stage 3 (the expensive fuzzy matching). (3) Incremental matching: After the initial full run, daily processing only matched new/changed records against existing golden records, not a full re-match. After optimization, the initial full run took 3.5 hours, and daily incremental runs took 15-20 minutes.

Back to STAR 4.1

STAR 4.1

Q: How do you keep the golden record current as new data arrives daily?

A: The golden record runs as a daily incremental process. Each night, new accounts and changed records from all 7 source systems flow through the same 5-stage matching pipeline but only for the delta (new/changed records). A new retail banking account opened today gets matched against existing golden records — if a match is found, it's linked to the existing ECID; if no match, a new ECID is created. Attribute survivorship rules also run daily — if a customer updates their address in online banking, and online banking is the most-recent-activity source for address, the golden record's address is updated. We track "golden record freshness" — the lag between a source system change and its reflection in the golden record. Target was <24 hours, and we consistently achieved ~16 hours (overnight batch processing).

Back to STAR 4.1

STAR 4.2

Q: How would you adapt this approach to Ontario's FIPPA instead of PIPEDA?

A: FIPPA has some key differences from PIPEDA that would change the design. (1) Collection authority: Under FIPPA, personal information can only be collected if directly related to and necessary for an operating program or activity — this is stricter than PIPEDA's "reasonable purpose." I'd need to tie every data element in the golden record to a specific ministry program mandate. (2) Consent model: FIPPA doesn't rely on consent the way PIPEDA does — instead, it relies on statutory authority for collection and use. The consent tracking table would be replaced with a "purpose authority" table mapping each data use to the specific FIPPA section authorizing it. (3) Access requests: FIPPA provides broader access-to-information rights — the golden record would need to support generating a complete report of all data held about an individual, traceable back to source, within the 30-day statutory response window. (4) IPC oversight: The Information and Privacy Commissioner of Ontario would have jurisdiction — the PIA process is similar but reviewed against FIPPA criteria rather than PIPEDA's.

Back to STAR 4.2

STAR 4.2

Q: How did you audit that purpose-based views were being used correctly?

A: Three mechanisms. (1) Access logging: Every query against any customer view was logged with the user ID, timestamp, and view accessed. Monthly reports showed which users accessed which views. (2) Anomaly detection: If a user from Marketing queried VW_CUSTOMER_RISK (which they shouldn't have access to — but in case of misconfigured permissions), it was flagged automatically. (3) Quarterly access reviews: Each data owner reviewed the list of users with access to their views and confirmed that access was still appropriate. Any user who hadn't accessed a view in 90 days had their access revoked (use-it-or-lose-it policy). We also had a "purpose declaration" field in the access request form — users had to state what business purpose they needed the data for, and this was matched against the view's intended purpose.

Back to STAR 4.2

STAR 4.2

Q: How would you handle a data subject access request under this architecture?

A: The hub-and-spoke model actually makes access requests easier to fulfill. Step 1: Look up the individual's ECID in the MASTER_CUSTOMER hub. Step 2: Follow the spoke links to every source system identifier (CIF, TSYS account, BancWest CIF, etc.). Step 3: Pull all data associated with each source identifier. Step 4: Compile into a structured report showing: golden record attributes (and which source they came from), all linked accounts, the data held in each system, and consent/purpose authority records. The lineage tagging on every golden attribute means we can tell the individual not just what data we hold, but where it originated. Under PIPEDA, the response window is 30 days; under FIPPA it's also 30 days. The architecture supported generating this report in under 2 hours, well within the response timeline.

Back to STAR 4.2

STAR 5.1

Q: How did you handle class imbalance in the attrition model?

A: With a 12% attrition rate, the positive class was about 1:7.3 — moderate imbalance but enough to bias a naive model toward always predicting "not attriting." I used three techniques. (1) SMOTE (Synthetic Minority Oversampling) on the training set only — never on validation or test sets, to avoid data leakage. (2) Class weights in XGBoost (scale_pos_weight = 7.3) to penalize misclassification of the minority class more heavily. (3) Evaluation metric alignment: I optimized for precision at top decile rather than overall accuracy, because the business cared about the quality of their call list, not the model's overall classification rate. A model with 95% accuracy but poor precision at the top decile would have been useless for the retention team's capacity-constrained workflow.

Back to STAR 5.1

STAR 5.1

Q: Why XGBoost over other algorithms like Random Forest or neural networks?

A: Three reasons for this specific problem. (1) Performance: XGBoost outperformed Random Forest by 6 percentage points on precision at top decile (78% vs. 72%) and logistic regression by 16 points. I did test a simple neural network (2-layer feedforward) — it performed comparably to XGBoost but with worse interpretability. (2) Interpretability: The retention team needed to know why a customer was flagged. SHAP values on XGBoost gave us per-customer feature importance that agents could use in their retention calls ("We noticed you've been using your card less frequently — is there something we can help with?"). Neural network explanations were less intuitive. (3) Operational simplicity: XGBoost runs efficiently in batch scoring on structured tabular data without GPU infrastructure. For 2.4M records with 45 features, it scored in under 10 minutes on standard compute. A neural network would have added infrastructure complexity without meaningful performance gain.

Back to STAR 5.1

STAR 5.1

Q: How did you ensure the model wasn't biased against certain customer segments?

A: I conducted a fairness analysis across three protected dimensions: age group, geography (province), and product type. For each, I checked whether the model's false positive rate and false negative rate were significantly different across groups. I found a slight bias: the model was 8% more likely to flag customers over 65 as attrition risks — because older customers naturally had declining transaction frequency (a key feature) even when they weren't planning to leave. I addressed this by adding an age-adjusted frequency feature that compared each customer's frequency trend to the average for their age cohort, rather than using absolute frequency. After this adjustment, the age-group bias dropped below 2%, which was within our acceptable threshold. I also excluded explicitly protected attributes (age, gender, postal code as a proxy for ethnicity) from the feature set and relied on behavioral features only.

Back to STAR 5.1

STAR 5.2

Q: How did you handle resistance from teams that didn't want to own their data?

A: Resistance usually came from two sources: "I don't have time for this" and "if I own it, I'm accountable when it breaks." For the first, I made ownership lightweight — the data owner's role was to make decisions (approve access requests, sign off on definitions, set quality targets), not to do the day-to-day work. Data stewards handled operational tasks. For the second, I reframed accountability as protection: "Right now, when this data has an issue, everyone blames everyone else. Ownership means you control the narrative — you set the quality standards, you decide the definitions, and you get credit when it's right." I also started with a willing team (SVP Retail Credit Cards was enthusiastic) and used their success as a proof point. When other teams saw that the credit cards domain had clear ownership and faster issue resolution, the "I don't want to own it" resistance faded.

Back to STAR 5.2

STAR 5.2

Q: What tools did you use for the data catalog?

A: At BMO, we used a combination of tools. The enterprise data catalog was built on Collibra for business glossary and data governance workflows (ownership assignment, approval workflows, stewardship queues). For technical metadata (table schemas, column statistics, lineage), we used Apache Atlas integrated with our Hadoop/Spark environment. For the Power BI layer, we used Power BI's built-in data lineage view. The key was integration — Collibra's business definitions were linked to Atlas's technical metadata so a business user could search for "delinquency rate" and find both the business definition and the actual table/column where it was calculated. In a government context, I'd evaluate what's available — tools like Azure Purview, open-source options like DataHub or OpenMetadata, or even a well-structured SharePoint-based catalog for smaller environments. The tool matters less than the discipline of maintaining it.

Back to STAR 5.2

STAR 5.2

Q: How did you prioritize which datasets to govern first?

A: I used a 2×2 matrix: regulatory exposure (high/low) × number of downstream consumers (many/few). Quadrant 1 (high regulatory, many consumers) went first — this was credit card delinquency and balance data used for OSFI/Basel reporting and consumed by Risk, Finance, and Audit. Quadrant 2 (high regulatory, few consumers) went second — specialized regulatory datasets. Quadrant 3 (low regulatory, many consumers) — marketing and analytics datasets. Quadrant 4 last — internal operational datasets. This meant we governed the highest-risk, highest-impact datasets first, which also generated the most visible wins and built momentum. Within each quadrant, I further prioritized by known data quality issues — datasets with existing quality complaints got attention sooner because governance could demonstrate immediate improvement.

Back to STAR 5.2

STAR 5.3

Q: What if the stakeholders had disagreed about which metrics were most important?

A: This actually happened to a degree — the US Retail Head wanted branch-level operational metrics, while Finance wanted portfolio-level financial metrics. They competed for dashboard real estate. My approach: I went back to the decisions. "What decision does this metric enable?" If two metrics enabled the same decision, the more actionable one won. If they enabled different decisions, both stayed but on different pages — I used the progressive disclosure design (executive summary → drilldown → deep analysis) to give each audience their own space. The key principle is that dashboards shouldn't be designed by committee consensus on "important metrics" — they should be designed around specific decisions, and each decision-maker gets a view optimized for their decisions. When framed this way, the disagreement resolved itself because no one was giving anything up.

Back to STAR 5.3

STAR 5.3

Q: How did you handle the 10% of data that wasn't yet available?

A: Transparency was key. For the metrics that required Wave 3 data (branch-level traffic, customer appointment data), I included them in the dashboard design with a clear "Data Available: Target Date MM/YYYY" placeholder. This served three purposes: (1) stakeholders could see the complete vision and knew it was coming, (2) it created organizational pressure to deliver Wave 3 on schedule because executives were already expecting those metrics, and (3) it prevented scope creep — stakeholders didn't keep asking "can you also add X?" because they could see it was already planned. For the interim, I provided proxy metrics where possible — for example, instead of actual branch traffic data, I used transaction volume by branch as a rough proxy for activity levels.

Back to STAR 5.3

STAR 5.3

Q: How did you measure success of the dashboard beyond adoption numbers?

A: Adoption (user counts) is a vanity metric — it tells you people opened the dashboard, not that it changed decisions. I tracked three deeper measures. (1) Decision velocity: Before the dashboard, it took ~3 weeks from month-end for the leadership team to make credit policy adjustments based on portfolio performance. After, decisions were made within 5 business days — a 75% reduction. (2) Manual report retirement: The 40-page PDF was fully retired, along with 2 other ad hoc Excel reports that analysts had been producing — freeing approximately 5 analyst-days per month. (3) Insight-to-action: I tracked specific business actions triggered by the dashboard — the vintage analysis that caught the 25% underperformance was the flagship example, but there were 6 other documented cases where the dashboard directly led to a credit policy adjustment or portfolio investigation in the first 6 months.

Back to STAR 5.3

STAR 5.4

Q: How did you handle dashboard performance with large datasets?

A: The TSYS Gold layer had hundreds of millions of transaction records — loading all of that into Power BI's in-memory engine would have been impractical. I used three performance strategies. (1) Aggregation tables: For the executive summary page, I pre-aggregated data into daily product-level summaries in the Gold layer (FACT_DAILY_SUMMARY). This reduced the data volume for Page 1 from ~200M rows to ~50K rows. (2) DirectQuery for detail: For the vintage analysis page where users needed drill-down to account-level, I used DirectQuery mode rather than import — this pushed computation to the database and avoided loading granular data into memory. The trade-off was slightly slower interaction (2-3 seconds per filter change vs. sub-second for import mode), but acceptable for an analytical drill-down page. (3) Incremental refresh: Configured Power BI's incremental refresh to only refresh the most recent 90 days of data, while historical data was cached. This reduced the daily refresh time from 45 minutes to 8 minutes.

Back to STAR 5.4

STAR 5.4

Q: How did you handle requests for additional features after launch?

A: I established a dashboard backlog process. Feature requests went into a prioritized list reviewed monthly. Each request was evaluated on: (1) number of users requesting it, (2) business decision it enables, (3) effort to implement, and (4) impact on existing dashboard performance. I batched changes into monthly releases rather than ad hoc updates — this prevented feature creep and ensured each change was tested. I also had a "one page in, one page out" guideline — if someone wanted a new dashboard page, they had to identify an existing page or report that could be retired. This kept the dashboard focused and prevented it from becoming another bloated 40-page report. The most common requests were for new slicers and filters, which were usually low-effort and could be added without architectural changes.

Back to STAR 5.4

STAR 5.4

Q: How would you make this dashboard AODA/WCAG compliant for a government context?

A: AODA requires WCAG 2.0 Level AA compliance. For Power BI dashboards, this means: (1) Color contrast: All text and data visualizations must meet 4.5:1 contrast ratio. I'd avoid using color as the only means of conveying information (e.g., RAG status would also have text labels, not just red/green). (2) Alt text: Every visual element needs descriptive alt text for screen readers — Power BI supports this natively in the visual settings. (3) Keyboard navigation: All interactive elements must be accessible via keyboard tabbing, which Power BI supports but requires testing. (4) Tab order: Set a logical tab order that matches the visual reading flow. (5) Font sizes: Minimum 12pt for body text, larger for headers. (6) No auto-playing content: Avoid animations or auto-rotating visuals. I'd also add a text-based data table alternative for every chart, so users with visual impairments can access the underlying numbers. In a government context, I'd work with the ministry's AODA compliance team to validate before publishing.

Back to STAR 5.4

STAR 5.5

Q: How did you decide which jobs to migrate to Spark vs. keep on SQL Server?

A: I used a data-driven approach. I profiled every ETL job on three dimensions: (1) execution time (jobs taking >30 minutes were candidates), (2) data volume (jobs processing >5M rows per run benefited most from distributed processing), and (3) complexity (jobs with complex window functions and multi-table joins that could be parallelized). The 8 jobs I migrated to Spark were all in the Silver-to-Gold transformation layer — they were the heaviest (accounting for 85% of total runtime) and involved joining large fact tables with dimension lookups. I kept Bronze-layer loads on SQL Server because they were simple file-to-table loads that didn't benefit from distributed processing and were already fast (<5 minutes each). I also kept the DQ validation jobs on SQL Server because they needed to query the database directly for reconciliation, and the additional Spark overhead for small comparison queries wasn't justified.

Back to STAR 5.5

STAR 5.5

Q: What was the hardest production incident and how did you resolve it?

A: The most difficult incident was a silent data corruption in the TSYS ACCT-MASTER extract. One night, TSYS sent a file that passed all structural validation (correct record count, correct trailer, correct file size) but contained stale data — the account balances were from 2 days prior, not the current day. Our Tier 1 and Tier 2 checks passed because the file looked correct. The issue was only caught by the Tier 3 DQ gate: the total balance reconciliation was off by $47M. I immediately blocked the Gold refresh, alerted the Fraud and Risk teams that today's data would be delayed, and traced the issue. Working with the TSYS mainframe team, we discovered that a batch job on their side had failed silently and re-sent the previous day's extract instead of generating a new one. Resolution: I added a new Tier 1 check — business date validation — that compared the extract date embedded in the header against the expected date. If they didn't match, the pipeline halted immediately. This turned a 3-hour detection cycle into a 5-minute detection.

Back to STAR 5.5

STAR 5.5

Q: How did you balance new feature development with production support?

A: I used a 70/30 capacity allocation model. 70% of sprint capacity was allocated to new feature development (enhancements requested by Fraud, Risk, Finance). 30% was reserved for production support, bug fixes, and technical debt. This was negotiated with stakeholders upfront — they understood that if we allocated 100% to features, production stability would degrade and they'd lose the data they depended on. I also implemented a rotation model for on-call support — each team member took one week of primary on-call per month, so no one was permanently in "firefighting mode." During weeks with high incident volume, I'd shift the ratio to 50/50 and communicate to stakeholders which planned features would slip. The key was transparency: stakeholders always knew the trade-off between "more features" and "more stability" because I tracked and shared both metrics monthly.

Back to STAR 5.5

Prepared as a supplement to the main Interview Preparation Guide for RQ09948
Stories based on BMO Financial Group experience portfolio
Last updated: 2026-02-07