Database design

Schema & administration examples

Physical design choices I would defend in review: partitioning aligned to query predicates, effective-dated dimensions, explicit constraints, and classification carried on every column so masking and retention can be enforced automatically. These objects are illustrative DDL, not deployed to any database.

DW.FACT_BILLING

Grain: one row per billed order line, per revision.

Approx. rows (demo)

48.0M

Partitioning

RANGE (bill_month), monthly, interval-enabled

Columns & classification

Restricted columns are tokenised or masked before they leave the trusted zone.

Column	Type	Null	Key	Classification	Note
BILLING_SK	NUMBER(19)	N	PK	internal	—
ORDER_ID	VARCHAR2(32)	N	UQ	internal	—
CUSTOMER_SK	NUMBER(19)	N	FK	internal	—
BILL_MONTH	DATE	N	—	internal	Partition key
AMOUNT_BASE	NUMBER(18,4)	N	—	restricted	—
CURRENCY_CODE	CHAR(3)	N	—	public	—
LOAD_RUN_ID	VARCHAR2(16)	N	—	internal	Lineage

Reference DDL

Illustrative Oracle syntax — not executed anywhere in this app.

CREATE TABLE dw.fact_billing (
  billing_sk     NUMBER(19)     GENERATED ALWAYS AS IDENTITY,
  order_id       VARCHAR2(32)   NOT NULL,
  customer_sk    NUMBER(19)     NOT NULL,
  bill_month     DATE           NOT NULL,
  amount_base    NUMBER(18,4)   NOT NULL,
  currency_code  CHAR(3)        NOT NULL,
  load_run_id    VARCHAR2(16)   NOT NULL,
  CONSTRAINT pk_fact_billing PRIMARY KEY (billing_sk),
  CONSTRAINT fk_fact_billing_cust FOREIGN KEY (customer_sk)
    REFERENCES dw.dim_customer (customer_sk)
)
PARTITION BY RANGE (bill_month)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
( PARTITION p_seed VALUES LESS THAN (DATE '2024-01-01') );

Administration practices modelled

Interval partitioning so new periods need no DDL intervention.
Local indexes on partitioned facts to keep maintenance partition-scoped.
Statistics gathered immediately after load, before dependent queries run.
Compressed archive tablespace fed by partition exchange, not row deletes.
Constraint-first modelling: keys and checks declared, not assumed in ETL.
Lineage column on every fact row tying data back to the producing run.