9 Quantitative Metrics to Evaluate Your Data Warehouse—A Complete Guide

1. Completeness

Completeness asks whether the warehouse contains all business‑required data.

Metric 1: Cross‑layer reference rate

Measures how many ODS tables are directly referenced by DWS/ADS/DM layers.

Formula: (Number of ODS tables referenced by DWS/ADS/DM) ÷ (Total active ODS tables) × 100%.

Why it matters: According to data‑warehouse best practice, raw ODS data should only be consumed by the DWD layer; direct references indicate missing or unusable DWD tables, leading to duplicate development and data‑inconsistency risk. The ideal value approaches 0%.

Metric 2: Summary‑data query ratio

Counts the proportion of queries that hit the summary (DWS/ADS/DM) layer.

Formula: Summary‑layer query count ÷ Total query count × 100%.

Why it matters: If most queries must drill down to detail or raw data, performance suffers and user experience degrades. A healthy warehouse typically maintains this ratio above 80% while allowing occasional deep‑dive queries.

2. Reuse

Reuse reflects the “build once, use many times” principle of a data‑mid‑platform.

Metric 3: Model reuse coefficient

Average number of downstream tables that directly reference a given table.

Formula: Total number of downstream references ÷ Number of tables with downstream references.

Example: In a DWD layer with 100 tables, 80 are referenced downstream, and those 80 receive a total of 500 downstream references, yielding a coefficient of 500÷80 = 6.25.

If the coefficient is below 2, reuse is poor—most tables are one‑off.

A value of 3 or higher is considered acceptable.

Values above 5 indicate strong model design and extensive shared logic.

Metric 4: Data‑lineage divergence degree

Assessed visually via a metadata lineage graph.

Ideal: A divergent, mesh‑like graph where one upstream table feeds many downstream tables, each of which serves multiple applications. This structure limits impact when upstream data changes.

Problematic: A linear chain (A → B → C) forces all downstream tables to change whenever the upstream source changes, raising maintenance cost.

3. Compliance

Compliance asks whether others can understand and safely use the tables.

Metric 5: Field‑description coverage

Measures the proportion of fields that have business‑meaning descriptions.

Formula: Number of described fields ÷ Total number of fields × 100%.

Observation: In many production environments, core tables have less than 20% coverage, with column names like col_001. Target: ≥95% for core tables, 100% ideal.

Metric 6: Model‑layer information coverage

Assesses whether each table is tagged with its layer (ODS, DWD, DWS, ADS).

Formula: Number of tables with layer information ÷ Total number of tables × 100%.

Reason: Without layer tags, users cannot tell if a table is raw or processed, hindering self‑service reporting.

Metric 7: Subject‑domain ownership coverage

Measures the proportion of tables assigned to a business domain (e.g., transaction, user, product).

Formula: Number of tables with domain ownership ÷ Total number of tables × 100%.

Goal: 100% coverage to avoid manual hunting among hundreds of tables.

Metric 8: Naming‑convention compliance

Evaluates how many tables follow a prescribed naming pattern.

Formula: Number of tables matching the naming rule ÷ Total number of tables × 100%.

Example of a good name: dwd_trd_order_df —clearly indicates the transaction domain, order detail, and daily full load.

Even if naming feels like formality, inconsistent names can cause serious operational errors, such as accidental deletion of production tables.

Metric 9: Same‑name same‑meaning field consistency

Checks whether identical fields across models share the same name and definition.

Formula: (Occurrences of a standard field across models – Inconsistent‑name occurrences) ÷ Total occurrences × 100%.

Implementation typically requires a data‑governance tool to scan and compare definitions.

Practical Guidance

Run these metrics monthly, prioritize tables with low scores, and adjust architecture accordingly. Early stages should focus on compliance to lay a solid foundation; mid‑term efforts shift to reuse for ROI; long‑term tracking of completeness ensures the warehouse continues to serve business needs.

Remember, metrics are tools—regular monitoring and targeted remediation are what drive real improvement.