Data Issue Investigation Techniques

Data Issue Investigation Techniques

This guide covers advanced investigation techniques for diagnosing data issues beyond the basic workflow described in Data Issues Guide.

Investigation Strategy

1. Binary Search Approach

When investigating data issues, use binary search to narrow down the problem location:

flowchart TD
    A[Start: Data Issue] --> B{Check Vendor Response}
    B -->|Data incorrect in response| D[Vendor Side Problem]
    B -->|Data correct in response| E{Check Parsing}
    E -->|Parsing failed| F[Parsing Bug]
    E -->|Parsed correctly| G{Check Sync/Save Logic}
    G -->|Logic error| H[Sync Logic Bug]
    G -->|No error found| I[Data Race/Intermittent]
    D --> J[Contact Vendor]
    F --> K[Fix Parsing Code]
    H --> L[Fix Sync Logic]
    I --> M[Retry/Document]

Principle: Cut possibilities in half with each check - Vendor vs RC, Request vs Response, Parse vs Process.

2. File Log Structure

File logs follow this naming convention:

{YYYYMMDD-HHMMSS}_{serial}_{type}.json

Example: 20250308-123141_1a0e6_request.json

Types:

• _request.json - Full request/response logged by logRequestResponseToFile()
• _holdings.json - Holdings data subset from response
• Other vendor-specific types

Path structure: s3://rightcapital-prd/logs/integration/{VENDOR}/{ADVISOR_ID}/{REFERENCE}/

3. Understanding Reference IDs

The z{number} directory names (e.g., z084793281) are Reference IDs - the Vendor’s identifier for our data.

To find Reference IDs:

1. Go to Admin Center → Integrations
2. Enter Advisor ID
3. Find the vendor integration
4. Click “Mappings”
5. Input Household ID in “Mappable ID” filter
6. The result shows the Reference ID

4. Code Tracing Techniques

When logs don’t reveal the issue, trace through the code:

Black Diamond Integration Structure:

graph TD
    subgraph BlackDiamond Integration
        Controller[Controller.php]
        Integrator[Integrator.php]
        Connector[Connector.php]
    end
    Controller --> Integrator
    Integrator --> Connector

Key methods to trace:

• getHoldings() - Fetches holdings data
• call() - Makes HTTP request to vendor
• logRequestResponseToFile() - Logs request/response

Code locations:

• retail-api/app/Integrations/Lpl/Integrator.php
• retail-api/app/Integrations/Lpl/Connector.php

5. Debugging Yodlee Race Conditions

Yodlee’s data import can have race conditions:

Scenario:

1. YodleeSaving request saves investment account (transaction in progress)
2. Former request reads old Position list
3. YodleeSaving transaction commits
4. Latter request reads new Position list (with new positions)
5. Former caches old Position data
6. Latter reads from cache but has new positions without cache entries

Detection: Check Sentry events for Standalone position missing vendor value errors with timing patterns.

Code locations:

• retail-api/app/Models/InvestmentAccountMarketValue.php
• packages/libs/core-models/src/Models/InvestmentAccount.php
• packages/libs/core-models/src/Models/RecentVendorPositionValue.php

Common Data Models

Standalone Positions

Definition: Positions without a linked Security but have a reference. Only exist in Linked Accounts.

Investigation: When standalone positions are missing values:

1. Check recent_vendor_position_values table
2. Check historical_positions table for history
3. Verify vendor response in file logs

Investment Account Market Value

Tables involved:

• investment_accounts - Account records
• investment_account_market_values - Cached market values (4 working days)
• historical_investment_account_customs - Custom value settings
• positions - Current positions
• historical_positions - Position history
• recent_vendor_position_values - Vendor-provided values (1 month)

Investigation script: php artisan accounts:calculate-investment-account-market-value

Communication with Vendors

Email Template

When vendor-side issues are identified:

Subject: Data Issue Investigation - {Vendor Name} - {Account/Household ID}

Structure:

1. Describe the observed issue with timestamps
2. Reference the specific account/holding IDs
3. Attach relevant log excerpts
4. Request investigation timeline

Example:

We're seeing incorrect data for household {ID} synced from {Vendor}.

Timeline:
- 2025-03-07 14:30 UTC - Account shows incorrect balance
- 2025-03-08 09:15 UTC - Previous sync was correct

Reference ID in your system: {z-number}
Our Account ID: {encrypted_id}

Please investigate your records for this account during this timeframe.

Advanced Cases

Case: Black Diamond Deleted Account Still Receiving Data

Symptom: Account deleted in BD but RC still receives updates.

Investigation:

1. Check BD API response in file logs
2. Verify if BD returns the deleted account
3. If yes, BD issue; if no, check our merge logic

Result in case ENGR-10397: BD was still sending the account in their API response.

Case: Standalone Position Missing Vendor Value

Sentry Event: Standalone positions exist but missing vendor value.

Root cause analysis:

1. Position exists in positions with security_id = null
2. Historical position exists with quantity > 0
3. No matching record in recent_vendor_position_values

Investigation steps:

1. Check if vendor sent price or value in file log
2. Verify timing - was position created during Yodlee save?
3. Check if value exists in earlier logs

Scripts and Commands

Useful Commands

# Check sync status for a mapping
php artisan integration:status --mapping-id=12345

# Trigger manual sync for one mapping
php artisan integration:sync --mapping-id=12345

# Sync all mappings for a vendor
php artisan integration:sync --vendor=schwab

# Check investment account market value
php artisan accounts:calculate-investment-account-market-value --account-id=11394841 --as-of-date="2025-03-08"

Related

• Data Issues Guide - Basic investigation workflow
• DITS Roadmap - Future automation vision
• Nightly Sync - Understanding sync timing

Sources

• Notion: Data Issue 指南
• Notion: Standalone position missing vendor value
• Notion: Data Issue 真实案例：Black Diamond