joellewis/finance_skills/data-quality

Data Quality

Core Concepts

1. Data Quality Dimensions for Financial Data

Six dimensions define data quality. Each has domain-specific meaning in financial services.

Accuracy — Data values correctly represent the real-world entity or event they describe. A security price is accurate if it reflects the actual market closing price or evaluated value from the designated source. A client address is accurate if it matches the client's current legal address of record. Accuracy failures propagate: an inaccurate price produces inaccurate valuations, performance, billing, and regulatory reports. Accuracy is measured by comparing data against an independent authoritative source — cross-vendor price comparison, custodian-to-PMS reconciliation, client confirmation of personal data. In practice, accuracy is the hardest dimension to measure because it requires an independent reference point for comparison.

Completeness — All required data elements are present for every record. A security master record is incomplete if it lacks an ISIN, asset class classification, or pricing source designation. A client onboarding record is incomplete if beneficial ownership for entity accounts is missing. Completeness is measured as the percentage of records with all mandatory fields populated. Financial data completeness requirements are often regulatory: FinCEN requires complete beneficial ownership data, GIPS requires complete portfolio inclusion in composites, SEC Rule 17a-4 requires complete transaction records. Completeness must be defined per record type — a required field for an entity account (beneficial ownership) differs from a required field for an individual account (employment status).

Timeliness — Data is available when needed for its intended use. End-of-day pricing must arrive before the nightly valuation batch runs. Trade confirmations must be generated within SEC Rule 10b-10 timeframes. NAV calculations must complete before fund company deadlines. Timeliness is measured as the lag between event occurrence and data availability in consuming systems. Late data is functionally equivalent to missing data if it arrives after the processing window closes. Timeliness requirements vary dramatically by use case: real-time market data must arrive in milliseconds, EOD pricing within hours, and quarterly regulatory filings within weeks.

Consistency — The same fact is represented identically across all systems and time periods. A client's legal name must match across the CRM, custodian, PMS, and billing system. A security's sector classification must be the same in the portfolio management system and the compliance monitoring system. Inconsistency typically indicates either a missing golden source designation or a broken synchronization process. Consistency is measured by cross-system comparison for the same entity attribute. Temporal consistency also matters: a security's classification should not change retroactively without documented justification and downstream impact assessment.

Validity — Data conforms to defined formats, ranges, and business rules. A CUSIP must be exactly 9 characters with a valid check digit. An account registration type must be one of the firm's defined values. A bond coupon rate cannot be negative (for conventional bonds). A trade settlement date cannot precede the trade date. Validity is enforced through schema constraints, field-level validation, and business rule engines. Invalid data that passes into production indicates insufficient input validation. Validity rules should be versioned and maintained as a formal catalog — when rules change, the change should be documented with effective date and rationale.

Uniqueness — Each real-world entity is represented exactly once. A client appearing as two records in the CRM (duplicate due to name variation or data entry error) causes fragmented reporting, missed household billing discounts, and potential compliance failures (wash sale detection across accounts requires a unified client view). A security represented as two master records per custodian causes duplicated positions. Uniqueness is enforced through deduplication at ingestion and periodic duplicate detection scans. Common deduplication techniques include exact-match on identifiers (SSN, CUSIP), fuzzy matching on names and addresses (Jaro-Winkler, Levenshtein distance), and probabilistic matching combining multiple weak identifiers into a confidence score.

2. Golden Source Architecture

Golden-source designation (which system is authoritative per data domain), MDM patterns (registry, consolidation, coexistence, transaction hub), conflict-resolution and survivorship rules, and pricing-source hierarchies are owned by the reference-data skill (data-integration plugin) — see it for the designation tables and pattern trade-offs. What matters here: accuracy is unmeasurable without a designated authoritative source to compare against, so every validation rule and accuracy metric below presumes a golden-source designation exists.

3. Data Lineage and Provenance

Data lineage tracks the full path of data from its origin through every transformation, enrichment, aggregation, and delivery to consuming systems. Provenance records who or what created, modified, or approved data at each stage.

Lineage metadata: For each data element, lineage captures: source system and original field, extraction method and timing, every transformation applied (mapping, conversion, calculation, enrichment, aggregation), intermediate staging locations, destination systems and fields, and the timestamp and process identity at each step.

Why lineage matters in finance: When a performance report shows unexpected returns, lineage enables tracing the result back through the calculation engine, to the pricing data it used, to the vendor source and extraction timestamp, identifying exactly where an error entered. Without lineage, root cause analysis is manual, slow, and unreliable.

Impact analysis: Lineage enables forward impact analysis — if a data source changes its schema or delivery format, lineage identifies every downstream system, calculation, and report affected. This is critical for vendor migrations, system upgrades, and regulatory reporting changes.

Regulatory requirements: BCBS 239 (Principles for effective risk data aggregation and risk reporting) requires banks to maintain comprehensive data lineage for risk data, including the ability to trace any risk report value back to its source data and every transformation applied. While BCBS 239 applies to globally systemically important banks (G-SIBs), its principles are increasingly adopted by smaller institutions and non-bank financial firms as best practice. MiFID II requires investment firms to maintain records demonstrating the accuracy and integrity of transaction reports, which effectively requires lineage from trade execution through reporting. SEC examinations increasingly ask firms to demonstrate how reported figures are derived from source data.

Implementation approaches: Manual lineage documentation (spreadsheets, data dictionaries) is common but becomes stale quickly as systems evolve. Automated lineage tools parse ETL code, SQL queries, and data pipeline configurations to extract lineage automatically. Leading platforms include Collibra, Alation, Informatica, and Apache Atlas. Hybrid approaches combine automated extraction with manual annotation for business context. For smaller firms, even a manually maintained data flow diagram per critical process (pricing, performance, billing, regulatory reporting) provides significant value over no lineage documentation at all.

Lineage granularity levels: Coarse-grained lineage tracks system-to-system data flows (e.g., "custodian feed populates PMS positions"). Fine-grained lineage tracks field-to-field transformations (e.g., "custodian field ACCT_BAL maps to PMS field market_value via currency conversion using the FX rate from Bloomberg as of 4:00 PM ET"). Regulatory use cases (BCBS 239, SEC examination support) increasingly require fine-grained lineage for critical data elements.

4. Validation Rules for Financial Data

Validation rules are automated checks that evaluate data against defined criteria before it is loaded into production systems or used for downstream processing. Rules operate at multiple levels.

Field-level validation: Individual field format and range checks applied to each field independently.

• CUSIP: Exactly 9 alphanumeric characters, check digit validates via Luhn algorithm variant.
• ISIN: Exactly 12 characters, 2-letter ISO 3166-1 country prefix, check digit validates via double-add-double algorithm.
• Price: Positive numeric (with exceptions for certain derivatives), reasonable decimal precision per asset class (2 decimals for equities, 6 for bonds, 8 for FX rates).
• Date: Valid calendar date, within expected range (not in the distant past or future).
• Account number: Matches expected format per custodian (Schwab: 8 characters, Fidelity: 9 characters, etc.).
• Currency code: Valid ISO 4217 three-letter code.

Cross-field validation: Relationships between fields within a single record.

• Trade settlement date must follow trade date by the correct settlement cycle (T+1 for US equities post-May 2024, T+2 for international equities in most markets).
• Bond maturity date must be after issue date.
• Option expiration date must be after trade date.
• A tax-exempt account (Roth IRA) holding tax-exempt municipal bonds is valid but inefficient — flag for suitability review.
• Margin-enabled accounts require margin agreement documentation on file.
• Account inception date must precede first transaction date.

Cross-record validation: Relationships between records within a single system.

• Position quantities across all accounts for a security must reconcile to the custodian's total.
• Sum of portfolio weights within a composite must equal 100%.
• All accounts assigned to an advisor must reference a valid advisor record in the advisor master.
• A security referenced in a trade must exist in the security master (referential integrity).
• All accounts within a household must share a consistent billing tier assignment.

Cross-system validation: Consistency between systems holding overlapping data.

• Position quantities in the PMS must match the custodian (daily reconciliation).
• Client name and address in the CRM must match the custodian (quarterly consistency check).
• Trade records in the OMS must match confirmations from the executing broker.
• Billing AUM must reconcile to the PMS valuation within defined tolerance.
• Performance returns computed internally must reconcile to custodian-reported returns.

Temporal validation: Detecting anachronistic or temporally inconsistent data.