Here are the core schema-design principles I follow:
- Start from the business grain: define exactly what one row represents, e.g. “one trade execution” or “daily account balance per portfolio and currency.” Everything follows from this.
- Preserve raw data: keep an immutable Bronze/source-aligned layer so you can replay, audit, and adapt when requirements change.
- Model entities and events separately: customers, accounts, instruments and portfolios are entities; trades, payments and price updates are events.
- Use stable keys: retain source-system identifiers; introduce surrogate keys where needed. Clearly distinguish business keys from technical record IDs.
- Enforce data types and constraints: timestamps with timezone semantics, decimals for money, valid codes, required fields, uniqueness, and referential integrity where practical.
- Make time explicit: usually track event time, ingestion time, and effective-from/effective-to dates. This is essential for corrections, reporting “as was known then,” and SCDs.
- SDC = Slowly Changing Dimensions, i.e. a way to handle changes to descriptive entity data over time, such as a customer’s address, portfolio classification, or product category.
- Type 1 (overwrite): update the old value. No history.
- Type 2 (retain history): insert a new versioned row, typically with valid_from, valid_to, and is_current. Best when reports must reflect what was true at a given time.
- Standardise shared concepts: one agreed definition for currency, legal entity, client, instrument, country, fiscal period, etc. Avoid each team inventing its own version.
- Design for change: schema evolution should be deliberate and backward-compatible where possible. New nullable columns are easy; silently changing a field’s meaning is dangerous.
- Normalise operational/core data; shape analytical data for use: avoid duplicated facts in canonical Silver data, then create denormalised Gold tables/star schemas for dashboards and fast reporting.
- Treat data quality as schema work: define acceptable ranges, reconciliation rules, freshness expectations, ownership, and quarantine handling—not merely column names.
- Document semantics and lineage: every table and important field should say what it means, who owns it, its grain, source, transformation logic, and caveats.
- Avoid premature optimisation: first get the model correct and understandable; then optimise physical layout—partitioning, clustering, file sizes—for real query patterns.