Katkat
Back

Model Effective-Dated Compliance Restrictions for Securities

Jain GlobalData Engineer · Technical Screen
MediumData EngineerTechnical ScreenSystem Design

Last updated

This question tests the ability to design a time-series database schema capable of handling bitemporal data, identifier changes, and overlapping compliance restrictions.

Problem Statement

Design a data model to store and query compliance restrictions for securities (e.g., "Trading prohibited for Ticker XYZ in Country ABC"). Your system must support:

  1. Effective-Dating: Restrictions have an effective_date and end_date (inclusive). Some restrictions are retroactive.
  2. Versioned Security Master: Securities undergo ticker changes and corporate actions. An identifier (e.g., ISIN) remains stable, but tickers may change or be reused.
  3. Overlapping Rules: Multiple authorities may define overlapping restrictions (e.g., an internal policy and a regional regulation) with different priority levels.
  4. Auditability: You must be able to reconstruct the exact state of the compliance universe as it was known on any given historical date (System Time vs. Valid Time).

Requirements

  • Define the schema for Securities, Restrictions, and the join table connecting them.
  • How do you handle "ticker mapping"? If a user asks, "Was Ticker ABC restricted on 2022-01-01?", how does your system resolve this despite the ticker possibly being assigned to a different company today?
  • Define an API endpoint or query pattern to check if a trade is compliant given a trade_date and security_id.

Assumptions

  • Data volume: 100,000 active securities, with 500+ updates/day.
  • Compliance rules are updated asynchronously by multiple legal teams.
  • High read throughput for compliance checks during order entry.

Hints

Hint 1

Consider implementing a bitemporal model, tracking both the 'valid time' (when the rule applies) and 'system time' (when the record was inserted into your database).

Hint 2

Use surrogate keys (e.g., an internal `sec_id` that never changes) rather than relying on tickers or ISINs as primary keys to handle instrument lineage.

Hint 3

For resolving the 'ticker at time' problem, maintain an `identifier_map` table that stores the start and end ranges for each ticker-to-security mapping.

Follow-up questions

  • How would you handle a scenario where a compliance rule is updated retroactively (e.g., an error was discovered in a rule that was active last month)?
  • How do you ensure data consistency between your versioned Security Master and the Restriction engine during a migration or bulk update?
  • If you need to optimize for performance, how would you cache these rules for low-latency trade checks without sacrificing accuracy?