How to Build Compliant Data Tables: Best Practices for Data Warehouse Governance

Background of Data Table Governance

Data warehouse teams often face downstream complaints about hard‑to‑use tables, unknown metric locations, and inconsistent naming, making table design a knowledge‑intensive task that frequently results in non‑compliant tables requiring continuous governance.

Pre‑Governance Considerations

Rapid business iteration and expanding data domains increase data volume and create siloed tables lacking standards, lifecycle management, and consistent technical quality, leading to "chimney" tables, poor reusability, and delayed data delivery.

Governance Process

1. Redefine Data Standards – Re‑segment data and subject domains, establish metadata rules for table names, field names, formats, and documentation.

2. Decommission Unused/Temporary Tables – Scan lineage and dependencies, retire long‑standing unused tables, and collect metadata via platforms such as DataHub.

3. Public Metric Reuse – Align application‑layer metrics, consolidate by domain and granularity, and reference shared tables.

4. Resolve ODS Penetration – Identify cross‑layer references, rebuild CDM layers (DWD/DWS), and migrate ADS/DWS usage to new tables.

5. Rebuild/Retire Chimney Tables – Consolidate duplicate tables into shared structures to improve clarity and reduce dependency chains.

6. Refactor Non‑Compliant Metadata – Update metadata according to new standards, adjust downstream dependencies, and coordinate migration plans.

7. Ongoing Maintenance – Score tables by usage and compliance, publish red‑black lists, automate alerts via Python, enforce design‑center reviews, and apply strict naming conventions.

Results

Decommissioned 80+ unused/temporary tables, freeing 3.7 TB storage.

Integrated 30+ chimney tables and 370+ shared metrics.

Reduced ODS penetration from 23 % to 4 %.

Maintained overall asset health score above 80 %.

Shortened daily data delivery window from 8:30 AM to 7:10 AM.

Established six governance standards covering naming, fields, types, partitions, and lifecycle.

Challenges Encountered

Coordinating table migrations with downstream teams proved difficult, causing delays; repeated negotiations were required to align on schema changes.

Governance Reflections

Effective governance requires motivating downstream teams, introducing incentive mechanisms, sharing success reports, and fostering a culture of continuous improvement.

Data Standard Appendix

Table Naming Conventions

ODS: ods__{db}_{table} DWD: dwd_{domain1}_{domain2}_{domain3}_{process}_{storage} DWS:

dws_{domain1}_{domain2}_{domain3}_{granularity}_{process}_{period}

ADS: ads_{app}_{granularity}_{process}_{schedule} DIM: dim_{definition}_{cycle} TMP: tmp_{name}_{id} VIEW: {name}_view Backup: {name}_bak Field Naming Rules

Boolean: is_{content} Enum: {name}_type Timestamp: {name}_date or {name}_time Percentage: {name}_rate Integer count: {name}_cnt_{period} Decimal amount: {name}_amt_{period} Field Type Guidelines

String for text and dates

Bigint for integers

Decimal/Double for decimals

String for enums (Y/N or multiple values)

IDString for identifiers

Other Metadata Requirements

Owner

Chinese name and description

Field Chinese name

Granularity

Primary key(s)

Storage Lifecycle

ODS: 1 year

DWD: 3‑5 years

DWS/ADS/DIM: up to 10 years (some permanent)

Partitions should not exceed two levels; the first level is business date, the second level is scenario‑specific.