Data Warehouse

Centralize canonical datasets in a durable storage layer so dashboards, reports, and data-driven systems can depend on consistent truth. It ingests source-of-truth extracts and modeled transformations; and produces stable canonical datasets, governed access surfaces, and visible freshness/quality signals.

Purpose

Provide a canonical storage layer that:

• consolidates key operational datasets into stable schemas,
• enforces source-of-truth rules and access boundaries,
• enables reliable downstream consumption (dashboards, reports, portals, and future systems).

This is a foundation project under the Data Visibility initiative.

Current state

Data visibility and reporting currently rely on a mix of scheduled extracts, SQL outputs, and system-specific storage. This works, but increases drift risk: multiple “truths,” repeated transformations, and inconsistent freshness/quality posture across consumers.

The warehouse establishes the canonical layer where shared datasets and definitions live, and where quality and access discipline are standardized once instead of reinvented per dashboard or report.

Implementation evidence (current)

• Repo: aeries-data-warehouse
• Runner entrypoint: PYTHONPATH=src python3 -m aeries_dw (make run-etl)
• Schedule policy config: config/schedules.yml (0 22 * * * nightly, 0 3 * * 0 weekly validation)
• Metadata and checkpoints: sql/dw_meta.sql and src/aeries_dw/meta.py
• Year-database discovery and schema sync logic: src/aeries_dw/etl.py, src/aeries_dw/schema.py
• Initial enabled table set includes IDN, ENR, LOC, STU, CSE, GTE, SSD, FOF, TST, AHS, COD, PGM, DIS, FRE, ADS, ATT in config/tables.yml

Next step

1. Define the initial canonical dataset set (the “first wedge”) and owners.
2. Establish schema/contract discipline (versioning, column meanings, deprecation).
3. Stand up ingest + modeling for the first wedge with visible freshness + basic quality checks.
4. Publish stable access surfaces (tables/views/exports) for reporting and downstream systems.
5. Align consumption workstreams (e.g., Perpetual Data Reports Portal) to use canonical datasets/definitions rather than bespoke logic.

Interfaces

Inputs

• source-of-truth system extracts
• existing SQL query outputs and normalization logic
• mapping/translation rules used in current reporting pipelines

Outputs

• canonical datasets with stable schemas (tables/views/exports)
• documented contracts (ownership, cadence, “what this means”)
• freshness and basic data quality signals
• governed access surfaces for dashboards/reports and downstream systems

Reality to Action trace

Reality Ingestion

Contributes in this stage.

Canonical Storage

Contributes in this stage.

Automation Engines

Not in scope.

Human Interfaces

Not in scope.

Operational Adoption

Contributes in this stage.

Core workflow

1. Identify a canonical dataset and its upstream truth.
2. Ingest and normalize into warehouse-ready shape.
3. Model into stable schemas (contracted columns, keys, definitions).
4. Run freshness + quality checks and publish status signals.
5. Publish access surfaces for reporting and downstream systems.
6. Version changes with a deprecation path and clear ownership.

Data integrity and contracts

Canonical schema definitions

• Canonical dataset list (certified vs staging/raw).
• Dataset-level contracts: keys, column meanings, allowed values, cadence.
• Versioning and deprecation policy for schema changes.

Source of truth rules

• Upstream operational systems remain canonical for raw truth.
• The warehouse is canonical for standardized datasets and agreed-upon transformations.
• Downstream dashboards/reports should not silently redefine metrics; shared definitions belong in the canonical layer or a shared semantic definition surface.

Data quality checks

• Freshness checks vs expected cadence.
• Row count deltas within expected thresholds for critical datasets.
• Schema drift detection for contract-protected columns.
• “Certified dataset” gating: datasets become certified only once ownership and checks exist.

Safe handling

• Least-privilege access boundaries aligned with privacy requirements.
• Explicit segmentation of sensitive datasets and fields.
• Auditability: visibility into access and change history.

Downstream integration map

• Perpetual Data Reports Portal report registry and outputs.
• Dashboards that currently depend on manual extracts.
• Data-driven tools that require stable, repeatable truth inputs.

Operational notes

Reliability posture

Implementation exists and is config-driven, with run metadata tables, resumable cursor windows, and nightly/weekly schedule definitions; production hardening still depends on host deployment and operator runbook adoption.

Observability

• dw_meta.runs and table-state metadata from sql/dw_meta.sql
• ETL logs emitted by src/aeries_dw/etl.py
• schedule/retry configuration in config/schedules.yml
• weekly validation checks (rowcount_delta, cursor_advancement)

Security and privacy

Visibility only scales when safety scales: access boundaries, review, and audit expectations must be designed in from the beginning.

Dependencies

Upstream

• source systems and their schemas/exports
• extract/query tooling used today (SQL outputs, scheduled jobs)

Downstream

• operational reporting systems and dashboards
• future automation surfaces that depend on warehouse-backed truth

Ownership

Owners

AetherDev (Data Visibility initiative umbrella)

Users

Operational teams consuming reports and dashboards; builders of data-driven systems requiring stable truth interfaces