-
Notifications
You must be signed in to change notification settings - Fork 0
Home
A data model for the U.S. broker-dealer trade lifecycle, aligned to SEC Rule 613, the FINRA CAT IM reporting series, and the FINRA CAT CAIS reporting series. The model ships as physical DDL in four dialects (Delta Lake, Hive, Fabric Lakehouse, Fabric Warehouse), with a Data Vault 2.0 Silver layer, a Gold star schema, Delta Live Tables pipelines, and a reference-data ingestion layer.
This wiki is a curated overview. The repository is the source of truth, and primary-sources/ is the source of truth for any regulatory claim made here.
- Data Vault Silver - Hub / Link / Satellite reference, PIT and Bridge patterns, business-vault views
- Gold Star Schema - dimensions, CAT-aligned facts, CAT field mapping, submission generator
- DLT Pipelines - pipeline inventory, SCD2 and quality-gate patterns, test suites, runbook pointers
- Reference Data - the reference tables, source authorities, refresh cadences
- CAT Event Mapping - per-event mapping to Silver Hubs / Links and Gold facts
The model targets the following primary sources (verified hashes in guardrails/spec_pins.json):
| Spec | Version | Effective | Coverage |
|---|---|---|---|
| CAT Reporting Technical Specifications for Industry Members | 4.1.0r15 | 2026-03-06 | All 99 Reportable Events (39 equity + 35 simple option + 25 multi-leg) |
| Full CAIS Technical Specifications | 2.2.0r4 | 2025-08-14 | FDID and Customer record state, address records, large-trader IDs, paired-file submission tracking, material-inconsistency tracking |
Coverage is enforced in CI by guardrails/validate_event_taxonomy.py, which fails the build if reference data drifts from the pinned PDFs. See guardrails/VERIFICATION_PROTOCOL.md for how to add or update reference values.
The model covers about 93 entities across thirteen subject areas (Party, Instrument, Venue, Agreement, Pre-Trade, Order Request, Order Stage, Execution, Allocation, Post-Trade, Position, Operations/Regulatory, plus Multi-Leg Option and CAIS modules).
The full catalog with attributes is in docs/entity_catalog_expanded.md. A per-entity reconciliation against the DDL is in audit/entity_reconciliation.csv.
Bronze is the raw landing zone for source feeds. It isn't materialised here because shape depends on the source systems at each implementing firm. The split-file DDL under ddl/expanded-model/ can serve as the target shape for Bronze-to-Silver loaders.
Silver is a Data Vault 2.0 implementation built for immutable, audit-ready history. Files under ddl/dv2/ cover hubs, links, satellites, reference tables, PITs, bridges, and the business vault. Multi-leg option Silver lives in ddl/multileg/01_multileg_silver_delta.sql. CAIS Silver lives in ddl/cais/01_cais_silver_delta.sql.
Gold is a Kimball-style star schema for CAT JSON submission and BI consumption. Files under ddl/gold/ cover dimensions, facts, operational tables, and consumption views. Multi-leg Gold (fact_multileg_option_events + fact_multileg_option_legs) is in ddl/multileg/02_multileg_gold_delta.sql. CAIS Gold (fact_cais_fdid, fact_cais_customer, fact_cais_submission, fact_cais_inconsistency) is in ddl/cais/02_cais_gold_delta.sql. Silver-to-Gold transformation logic is in ddl/gold/05_etl_silver_to_gold.md.
| Dialect | Consolidated file | Split files |
|---|---|---|
| Delta Lake (Databricks) | ddl/CAT_PreTrade_DDL_DeltaLake.sql |
ddl/expanded-model/, ddl/multileg/, ddl/cais/
|
| Apache Hive | ddl/CAT_PreTrade_DDL_Hive.sql |
ddl/expanded-model-hive/, ddl/multileg/03_multileg_silver_hive.sql
|
| Fabric Lakehouse | ddl/CAT_PreTrade_DDL_Fabric_Lakehouse.sql |
- |
| Fabric Warehouse (T-SQL) | ddl/CAT_PreTrade_DDL_Fabric_Warehouse.sql |
ddl/multileg/04_multileg_fabric_warehouse.sql |
diagrams/mermaid/full_model_er.mmd is the entry point. Subject-area diagrams (party_model_er.mmd, trade_lifecycle_er.mmd, multileg_option_er.mmd, cais_record_er.mmd, and so on) zoom in. The diagrams/drawio/ directory has draw.io versions of the subject-area ER diagrams.
The model supports SEC Rule 613, FINRA Rule 6800 series, FINRA CAT IM Technical Specifications v4.1.0r15, FINRA CAT CAIS Technical Specifications v2.2.0r4, BCBS 239 risk-data aggregation, MiFID II / MiFIR cross-border references, Dodd-Frank Title VII swap reporting, and the ISDA Master / CSA, GMRA, and GMSLA agreement references. ISO codes covered include 3166-1, 4217, 10383, 10962, 17442, and 20275. FIX 4.4 / 5.0 SP2 and FpML 5.x where relevant.
It isn't a substitute for regulatory advice. Implementers are responsible for validating conformance against the current specification.
Apache License, Version 2.0. See LICENSE, NOTICE, and THIRD_PARTY_LICENSES.md in the repository root.