schema-design — independently scanned and version-tracked by SaferSkills.
SaferSkills independently audited schema-design (Agent Skill) and scored it 100/100 (green). The audit ran 55 deterministic rules across Security, Supply Chain, Maintenance, Transparency, and Community; it found 0 high-severity and 0 lower-severity findings. The full rule-by-rule trace and per-finding evidence are below. Free, methodology-open.
Findings & checks · 0 flagged
Every scanned point with the score it earned and what moved between them.
First recorded scan — no prior version to compare against.
The primary manifest — the file an agent reads to learn what this artifact does.
The single most important decision in schema design is the grain: what does one row represent? Be explicit and precise.
Bad grain definition: "orders" Good grain definition: "one row per order line item, at the time of fulfillment"
Every column in a fact table must be true at that grain. If you try to mix grains in one table (order-level and line-item-level facts), you'll produce incorrect aggregations and confuse every analyst who touches it.
A star schema has one central fact table surrounded by dimension tables. It's optimized for BI tool query patterns — simple JOINs, fast aggregations.
fct_orders
├── order_id (PK)
├── customer_key (FK → dim_customers)
├── product_key (FK → dim_products)
├── date_key (FK → dim_date)
├── quantity
└── revenue_usd
dim_customers
├── customer_key (surrogate PK)
├── customer_id (natural/source key)
├── name
├── country
└── segment
dim_date
├── date_key
├── full_date
├── year, month, week, day_of_week
└── is_holidayRules:
What happens when a customer changes their country, or a product changes its category? Choose the SCD type that matches how history matters.
No history. Just update the row.
Use when: history doesn't matter (e.g., fixing a typo in a name).
Keep the old row, insert a new one. Mark which is current.
CREATE TABLE dim_customers (
customer_key INT PRIMARY KEY, -- surrogate key, never reused
customer_id VARCHAR, -- source system natural key
name VARCHAR,
country VARCHAR,
segment VARCHAR,
valid_from DATE,
valid_to DATE, -- NULL or '9999-12-31' = current
is_current BOOLEAN
);This preserves historical accuracy: an order placed in 2022 when the customer was in "Germany" still shows Germany even if they moved to "France" in 2024.
dbt implementation: use dbt snapshot — it handles the valid_from, valid_to, and is_current columns automatically.
Store only one prior value alongside the current.
Use when: you only ever need "current" and "previous" — not full history.
ALTER TABLE dim_customers ADD COLUMN prev_country VARCHAR;Rare in practice — SCD Type 2 is usually more flexible.
Always use surrogate keys (synthetic integers or UUIDs) as primary keys in dimension tables, not the source system's ID.
Why:
Keep the natural key (customer_id) as a separate column for traceability back to the source.
| Type | Description | Example |
|---|---|---|
| Transaction fact | One row per event at a point in time | Orders, page views, payments |
| Periodic snapshot | One row per entity per time period | Daily account balance, weekly active users |
| Accumulating snapshot | One row per process lifecycle, updated as milestones complete | Order fulfillment pipeline, loan application |
Choose the type based on the business question, not the source data shape.
Denormalize everything into one wide table — no joins required for queries.
Pros: Blazing fast queries in columnar stores; great for dashboards that always join the same tables. Cons: Data redundancy; hard to maintain when dimensions change; not suitable for SCD Type 2.
Use OBT for:
Avoid OBT as your only model layer — maintain normalized dimension tables upstream and derive OBTs as mart-layer views.
| Normalized (3NF) | Denormalized (star/OBT) | |
|---|---|---|
| Storage | Efficient | Redundant |
| Query complexity | High (many JOINs) | Low |
| Write performance | Better | Worse |
| Analytics queries | Slow on large scans | Fast on columnar store |
| Data consistency | Enforced by DB | Managed by pipeline |
In a data warehouse, lean toward denormalization. The warehouse doesn't do OLTP writes — you won't pay the write-performance penalty, and you gain enormously on read performance.
Consistent naming makes schemas self-documenting:
fct_ prefix for fact tables (fct_orders, fct_sessions)dim_ prefix for dimension tables (dim_customers, dim_products)stg_ prefix for staging models (stg_raw_orders)_key suffix for surrogate keys (customer_key)_id suffix for natural/source keys (customer_id)_at suffix for timestamps (created_at, updated_at)_date suffix for date columns (order_date)is_ prefix for booleans (is_active, is_deleted)noun_unit: revenue_usd, quantity_items, duration_seconds~30 seconds. Free. No account. Every finding cites a rule and a line of evidence.