Every Firm Formats It Differently

A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

medium

Seniority

L5

Problem

MiFID II transaction reports arrive as files from tens of thousands of regulated firms, each formatted slightly differently. The pipeline has to ingest these files, normalize them to a canonical schema, validate them against regulatory rules, and produce an immutable record that auditors can query years later. Design it end-to-end.

The regulator changed the format. Again. Handle it.

Business requirements

• Regulators read the canonical store at the start of business the next day; submissions received in the evening have to be validated and loaded by then.
• When a firm's submission fails validation, they need to know which fields failed within hours so they can resubmit before the deadline.
• Any firm can change their column names or layout without notice; an unrecognized format has to be caught on arrival, not after corrupting the canonical store.

Worked solution and explanation

This is a file-contract check dressed up as a regulatory pipeline. Anyone can parse ten thousand CSV layouts. The skill is deciding what happens when a firm silently renames or swaps a column: do you guess, or stop that file at the door? Guess, and mis-mapped values land in the store regulators open at the start of business, and nobody finds out until an auditor does.

Step 1: Keep the original, then check structure first

Every file lands untouched in the submission audit log, which is the immutable record of what each firm actually sent. The schema validator compares columns, types and order to that firm's registered schema before anyone looks at a value. A mismatch goes to validation failures for review and never reaches the normalizer.

Step 2: Normalize, then apply regulatory rules

Files with a known structure pass through the canonical normalizer, which maps each firm's layout onto one schema. The content validator then checks the MiFID II rules on that single schema, so each rule is written once instead of once per firm. Passing rows move on.

Step 3: Return failures fast and hold the deadline

Failing rows land in validation failures with firm id, file id and the failed fields. firm feedback returns them in under an hour, so the firm can resubmit before the deadline. The orchestrator runs the chain and alerts if any firm's file is at risk of missing the overnight load.

Infer the schema per file

The loader adapts to whatever columns arrive. A firm that swaps two decimal columns raises no error, and the values land in the wrong fields.

Registered schema as contract

Each firm has a versioned registered schema. Anything that breaks it is routed to validation failures, and a legitimate change becomes a config update a human approves.

Common follow-up questions

• A firm legitimately changes its layout and registers it first. What lets the new format through? (Tests whether the registered schema is versioned config, with the new version active from the agreed date.)
• A resubmitted file arrives twice. How do you keep `transaction reports canonical` free of duplicates? (Tests idempotent loads keyed on firm id and file id, so a retry replaces rather than appends.)
• Tens of thousands of firms send tiny files. What happens to the lake? (Tests small-file compaction and partitioning by submission date.)