## Model Effective-Dated Compliance Restrictions for Securities Design a relational system that maps compliance restrictions to every affected security. The security master contains companies, countries, securities, and FIGIs. A restriction can target a company, a country, or a specific company-and-country combination, and every restriction has an effective interval. The system must remain auditable as the security master changes, identifiers evolve, and a legacy implementation is replaced. ### Constraints & Assumptions - A security can change ticker without changing its stable identity. - Company and country relationships in the security master are versioned. - A restriction may refer to a company that is absent from a particular security-master version. - Effective intervals use a documented boundary convention. - Compliance consumers need both current results and reproducible historical results. ### Clarifying Questions to Ask - Does a company-and-country restriction mean both predicates must match, and are broader company-only or country-only restrictions combined by union? - Which country relationship is relevant: incorporation, issuer domicile, listing venue, or another defined field? - Can restrictions be amended retroactively, and must prior published decisions remain reproducible? - Is an unresolved reference allowed to publish an incomplete result, or must the affected batch fail closed? ### Part 1 — Define the Schema and Temporal Semantics Propose tables, keys, constraints, and interval semantics for the security master and restrictions. Represent company-only, country-only, and company-and-country targets without ambiguity. #### What This Part Should Cover - Stable internal keys separated from mutable tickers. - Versioned company, country, FIGI, and ticker relationships. - Effective-dated restriction records with explicit matching semantics. - Source, version, author, and change-reason fields needed for audit. ```hint Make conjunction explicit Represent the difference between a company-only rule, a country-only rule, and a rule requiring both predicates in the schema. ``` ### Part 2 — Resolve Restrictions to Securities Describe the as-of query or materialization that expands each active restriction to all matching securities. Explain how duplicate matches and overlapping restrictions are retained or consolidated. #### What This Part Should Cover - Temporal joins against one named security-master version or as-of snapshot. - Correct handling of company-only, country-only, and combined predicates. - Stable decision keys and lineage back to every contributing restriction. - Deterministic treatment of multiple active restrictions on one security. ```hint Keep decisions explainable Even if the final answer is one restricted flag, retain the set of restriction IDs that caused it. ``` ### Part 3 — Handle Missing References and Master Freshness A restriction references a company that does not exist in the selected security-master version. Define the system response. Then explain how ticker changes, late master updates, and snapshot freshness affect publication. #### What This Part Should Cover - An unresolved-reference state that cannot silently become “no affected securities.” - A policy for blocking, quarantining, or escalating incomplete results. - Stable-identifier joins and effective-dated ticker aliases. - A freshness contract binding each published decision set to a master version. ```hint Do not turn unresolved into unrestricted A missing company reference is an integrity failure, not evidence that the restriction affects zero securities. ``` ### Part 4 — Validate a System Migration Design the reconciliation and tests used to compare a new implementation with the legacy system. Include missing references, interval boundaries, overlapping date ranges, and special company-country rules. #### What This Part Should Cover - Parallel runs over frozen snapshots and representative as-of dates. - Set-based diffs at security, restriction, and date granularity. - Classification of expected versus unexplained differences. - Referential-integrity, temporal, versioning, and audit-log tests. ```hint Compare causes, not just flags Diff the contributing restriction IDs for each security and date so equal totals cannot hide different compliance decisions. ``` ### What a Strong Answer Covers - A normalized, effective-dated model with explicit combination semantics. - Fail-closed handling of unresolved compliance references. - Reproducible expansion results tied to exact restriction and master versions. - Migration evidence that explains every difference rather than comparing only totals. ### Follow-up Questions 1. How would you represent a restriction that targets two companies as one logical rule? 2. What should happen when two active restrictions overlap but have different reasons or owners? 3. How can a compliance analyst explain why a security was restricted on a past date after its ticker changed? 4. Which database constraints can prevent invalid or ambiguous restriction intervals?