Purpose

This document outlines the proposed technical direction for introducing governed snapshot reporting into the CDM-based Data Warehouse, while preserving the existing temporal and dynamic reporting capabilities.

The intention is to support:

• reproducible period-end reporting
• certified business reporting positions
• operational replay (asat)
• lightweight governance-driven overrides
• platform-independent reporting consumption

without reverting to large static snapshot table architectures.

---

Existing Temporal Capability

The CDM already supports temporal replay using effective-dated rows.

Core temporal fields:

Field	Purpose
ACTIVE_FLAG	Current active row
CREATED_DATE	Row became valid
EXPIRED_DATE	Row superseded

This allows:

• current reporting
• historical replay
• change tracking
• lifecycle analysis
• temporal reconstruction

Example asat logic:

CREATED_DATE <= @asAtDate
AND (
    EXPIRED_DATE IS NULL
    OR EXPIRED_DATE >= @asAtDate
)

This remains the primary historical engine.

---

Reporting Modes

The warehouse now conceptually supports four reporting modes:

Mode	Purpose
current	Latest active operational position
history	Full temporal history
asat	Reconstructed operational state at a specific date
snapshot	Certified reporting position

---

Snapshot Design Principle

Snapshots should act as:

• governance artefacts
• reporting certification references
• temporal replay anchors

Snapshots should not become full duplicated copies of all reporting tables unless absolutely necessary for performance or legal retention.

The preferred approach is:

• reconstruct historical state dynamically
• apply lightweight snapshot governance overlays where required

---

Snapshot Registry

Proposed warehouse table:

snapshot_registry

Example structure:

Column	Purpose
SNAPSHOT_ID	Unique reporting snapshot
PERIOD	Reporting period
SNAPSHOT_TYPE	Draft / Final / Forecast etc
ASAT_DATE	Temporal replay date
STATUS	Draft / Approved / Retired
CREATED_UTC	Snapshot creation date
APPROVED_BY	Governance approval
NOTES	Optional comments

Example:

SNAPSHOT_ID	PERIOD	ASAT_DATE
P06_FINAL	2026-P06	2026-03-31

---

Snapshot Replay Flow

Step 1 – Report selects snapshot

Example:

P06_FINAL

---

Step 2 – Snapshot resolves temporal context

ASAT_DATE = 2026-03-31

---

Step 3 – get.myview executes in asat mode

Equivalent internal execution:

EXEC get.myview
    @mode = 'asat',
    @asAtDate = '2026-03-31'

---

Snapshot Override Philosophy

The proposal intentionally avoids:

• mutating base temporal history
• rewriting CREATED_DATE
• modifying operational lineage

Instead, snapshot-specific interpretation rules are layered above the base temporal model.

---

Snapshot Temporal Override Rules

Proposed table:

snapshot_temporal_override

Purpose:

Allow approved late-arriving or certified rows to satisfy a snapshot replay position without altering source history.

---

Example Structure

Column	Purpose
SNAPSHOT_ID	Reporting snapshot
OBJECT_SEQ	Business object
ATTRIBUTE	Metadata attribute
APPROVED_ROW_CHECKSUM	Approved row/version
REPORTING_EFFECTIVE_DATE	Snapshot-effective interpretation date
REASON	Governance explanation
APPROVED_BY	Approval trace
CREATED_UTC	Audit timestamp

---

Override Interpretation

The override table does not hold replacement values directly.

Instead it stores:

• approved equivalence rules
• approved future row references
• snapshot interpretation metadata

Example:

SNAPSHOT_ID	OBJECT_SEQ	ATTRIBUTE	APPROVED_ROW_CHECKSUM
P06_FINAL	D1001	STATUS	0xAB1234

Meaning:

“For snapshot P06_FINAL, this later-arriving row/version is approved to represent the certified reporting position.”

---

Replay Precedence Logic

Snapshot replay should follow:

1. Attempt standard asat reconstruction
2. Check snapshot temporal override rules
3. If approved future-equivalent row exists:
       prefer approved row
4. Suppress superseded temporal row
5. Return one certified reporting value

---

Important Governance Principle

The warehouse must preserve both:

Truth Type	Purpose
Operational truth	What the source system recorded
Reporting truth	What the business certified

These must remain separable.

---

Platform Independence

The Data Warehouse remains the canonical source of truth.

Applications remain presentation/workflow layers only.

Layer	Responsibility
DWH	Temporal logic, snapshot registry, overrides
WordPress / Workflow UI	Approval workflow and governance
Power BI	Reporting consumption
APIs	Integration

This preserves:

• application independence
• governance traceability
• auditability
• long-term platform flexibility

---

Recommended Initial Scope

Initial pilot implementation should focus on:

• Project Portfolio
• Resource Forecasting
• Headcount / Workforce reporting

using:

• current
• asat
• snapshot

comparisons to validate business interpretation and replay consistency.