ShaDey Inc. Model Governance Data Modeling Metadata Management

Enterprise Model Governance

Forward and reverse engineering, domain model composition, naming enforcement, semantic drift detection, version reconciliation, conflict resolution workflows, and graph-based lineage tracking for ShaDey Inc.'s unified data model governance framework.

1Why Enterprise Model Governance?

Ungoverned data models fragment across the enterprise. Schema drift, naming collisions, orphaned tables, and undocumented attributes create governance debt. ShaDey Inc. discovered that three independent teams had each modeled "customer" differently in SAP, Snowflake, and Neo4j, with conflicting definitions of what a customer is.

Model governance transforms data architecture from a collection of siloed designs into a disciplined, documented system. The ShaDey Inc. scenario shows what happens without it:

System Table Name Definition Cost of Drift
SAP KNA1 Sales customer with opportunity pipeline Finance doesn't trust it for AR aging
Snowflake DIM_CUSTOMER EDW customer with 42 documented columns 5 undocumented columns silently added by analytics team
Neo4j Customer node Graph customer with relationship weights No documentation; schema inferred, not enforced
Governance Maturity Model
Ad-hoc: Models exist in spreadsheets and minds. Documented: Models in modeling tools; no enforcement. Enforced: CI/CD validation, naming rules, drift alerts. Automated: Self-healing models, conflict detection, version auto-merge.

ShaDey Inc. moved from Ad-hoc to Documented in 2024, with Enforced processes rolling out in 2025. This section covers the mechanisms that make Enforced governance possible.

2Forward Engineering: Model-First Design

Forward engineering is top-down: Logical model → Physical DDL generation. The advantage: consistency, traceability, and governance-by-design. Every table, column, and constraint is traceable back to a documented requirement.

ShaDey Inc. workflow follows the three-layer model:

Layer Owner Scope Example (Customer)
CDM (Conceptual) Business Analyst Business entities, relationships, rules Entity: "Customer" with attributes like name, address, credit_limit
LDM (Logical) Data Architect Data types, normalization, keys CUSTOMER table: customer_id (PK), customer_name (VARCHAR2), credit_limit (DECIMAL)
PDM (Physical) DBA Indexes, tablespaces, partitioning, compression CREATE TABLE pdm_dim_sales_customer with compression, partition by customer_id
Forward Engineering Benefit
CDM approved → LDM derived → PDM generated → DDL deployed. Traceability: Every column in the database traces back to a business requirement. Governance: Changes flow through approval gates before DDL is generated.

Example: DIM_CUSTOMER design from conceptual through physical:

-- LDM Logical Model Definition (approved by architect) ENTITY Customer { customer_id : BIGINT -- Business key from SAP customer_name : VARCHAR(255) -- Name from KNA1.NAME1 credit_limit : DECIMAL(15,2) -- From KNB1.KLIMK country_code : CHAR(2) -- ISO 3166 created_date : DATE updated_date : TIMESTAMP } PRIMARY KEY: customer_id CONSTRAINTS: - credit_limit > 0 - country_code IN (valid_iso_codes)

Generated PDM DDL (from logical model, following ShaDey naming standard):

-- PDM Physical Model (auto-generated from LDM) CREATE TABLE pdm_dim_sales_customer ( customer_id BIGINT NOT NULL PRIMARY KEY, customer_name VARCHAR(255) NOT NULL, credit_limit DECIMAL(15,2) NOT NULL CHECK(credit_limit > 0), country_code CHAR(2), created_date DATE DEFAULT CURRENT_DATE, updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, load_timestamp TIMESTAMP, source_system VARCHAR(50), -- Governance metadata dbt_valid_from DATE, dbt_valid_to DATE ) COMPRESS FOR OLTP PARTITION BY RANGE(created_date) INTERVAL (MONTH) TABLESPACE tbs_dim_sales; CREATE INDEX idx_customer_country ON pdm_dim_sales_customer(country_code); -- Table comment with lineage reference COMMENT ON TABLE pdm_dim_sales_customer IS 'Sales customer dimension. Source: SAP KNA1/KNB1. LDM version: 2.1. Approved: 2025-03-15';
DDL Generation Rules
Naming: Prefix with layer (pdm_), type (dim_), domain (sales_). Metadata: Every table includes source_system, dbt_valid_from/to for time-travel queries. Constraints: Mirrored from LDM; enforced at database layer for data integrity.

3Reverse Engineering: Database-First Discovery

Reverse engineering is bottom-up: Existing database → Documented model. It's essential for legacy system discovery, M&A integration, and migration planning. The challenge: reverse engineering captures structure but loses business semantics.

ShaDey Inc. reverse-engineered SAP ECC's 95+ core tables into a documented model. Example: SAP's customer module:

SAP Table Rows Key Columns Mapped to LDM
KNA1 1.2M KUNNR, NAME1, SPART CUSTOMER entity
KNB1 2.8M KUNNR, BUKRS, AKONT CUSTOMER_FINANCIAL extension
KNAD 5.1M KUNNR, ADRNR, CITY CUSTOMER_ADDRESS
J_1IADDRS 3.7M MANDT, KUNNR, TAXJURCODE CUSTOMER_TAX_LOCATION

SQL query to extract metadata from INFORMATION_SCHEMA:

-- Reverse engineering query: extract table and column metadata SELECT t.table_name, t.table_rows, COUNT(c.column_name) AS column_count, GROUP_CONCAT( CONCAT(c.column_name, ' (', c.column_type, ')') ORDER BY c.ordinal_position ) AS columns_with_types, SUM(IF(c.column_key = 'PRI', 1, 0)) AS pk_count, SUM(IF(c.column_key = 'MUL', 1, 0)) AS fk_count FROM information_schema.tables t JOIN information_schema.columns c ON t.table_schema = c.table_schema AND t.table_name = c.table_name WHERE t.table_schema = 'sap_uat' GROUP BY t.table_name, t.table_rows ORDER BY t.table_rows DESC;
Semantic Loss Risk
Reverse engineering surfaces structure but loses intent. Query shows that KNNR is a foreign key to KNA1, but the query doesn't explain why the relationship exists or what business rule it represents. Mitigation: Combine reverse engineering with the business glossary to restore semantics.

Restoring semantics: After reverse engineering, assign business definitions from the glossary to each discovered entity and attribute, creating a documented model.

4Domain-Level Models & Enterprise Roll-Up

Each domain team (Sales, Supply Chain, Finance, etc.) owns their domain model. The enterprise challenge: composing eight independent domain models into one unified enterprise model without losing domain autonomy.

ShaDey Inc. composition strategy:

Domain Domain Model Tables Shared Entities Local Entities
Sales DIM_CUSTOMER, FACT_OPPORTUNITY, DIM_PRODUCT CUSTOMER (canonical) SALES_STAGE, DEAL_PROBABILITY
Finance DIM_CUSTOMER, FACT_INVOICE, DIM_GL_ACCOUNT CUSTOMER (canonical) PAYMENT_TERM, INVOICE_TYPE
Supply Chain DIM_PRODUCT, FACT_INVENTORY, DIM_SUPPLIER PRODUCT (canonical) SKU_VARIANT, WAREHOUSE_ZONE
Marketing DIM_CUSTOMER, FACT_CAMPAIGN, DIM_SEGMENT CUSTOMER (canonical) SEGMENT_RULE, CAMPAIGN_CHANNEL
Composition Rules
Canonical Entities: CUSTOMER defined once, all domains reference it. Local Entities: SALES_STAGE unique to Sales; not shared. Conflict Resolution: When Sales and Finance disagree on a CUSTOMER attribute, escalate to Data Governance Board.

Conflict example: Sales wants to add loyalty_tier to DIM_CUSTOMER; Finance objects (loyalty is transient, shouldn't be in dimension). Resolution: Create separate FACT_LOYALTY_TIER table, joined at query time.

Entity Domain Owner Table Name Columns Contributed
CUSTOMER (Canonical) Sales (primary steward) pdm_dim_sales_customer customer_id, name, address, industry, credit_limit, active_flag
CUSTOMER (Finance extension) Finance (secondary steward) pdm_xref_finance_customer customer_id, payment_terms, dunning_block, credit_exposure
CUSTOMER (Marketing extension) Marketing pdm_xref_marketing_customer customer_id, segment_id, engagement_score, email_opt_in

Enterprise join: SELECT * FROM pdm_dim_sales_customer c LEFT JOIN pdm_xref_finance_customer f USING (customer_id) LEFT JOIN pdm_xref_marketing_customer m USING (customer_id).

5Naming Pattern Enforcement

Naming conventions are governance guardrails. Enforce them via pre-commit hooks, CI/CD validation, and linting rules. ShaDey Inc. naming standard ensures every table and column signals its layer, type, domain, and purpose.

ShaDey Inc. naming standard:

-- Naming convention: [layer]_[entity_type]_[domain]_[name] pdm_dim_sales_customer -- layer: pdm (physical data model) -- entity_type: dim (dimension) -- domain: sales (Sales domain) -- name: customer (entity name) pdm_fact_supply_inventory -- layer: pdm -- entity_type: fact (fact table) -- domain: supply (Supply Chain) -- name: inventory ldm_entity_finance_gl_account -- layer: ldm (logical data model) -- entity_type: entity (normalized entity) -- domain: finance -- name: gl_account -- Valid entity_types: dim, fact, xref (cross-reference), entity, ref (reference), bridge -- Valid domains: sales, finance, supply, marketing, loyalty, ecommerce, store_ops, customer_service

Naming validation query: detect non-compliant table/column names:

-- Detect non-compliant table names WITH naming_check AS ( SELECT table_name, CASE WHEN table_name ~ '^(pdm|ldm|cdm)_(dim|fact|xref|entity|ref|bridge)_(sales|finance|supply|marketing|loyalty|ecommerce|store_ops|customer_service)_[a-z0-9_]+$' THEN 'COMPLIANT' ELSE 'NON_COMPLIANT' END AS naming_status FROM information_schema.tables WHERE table_schema = 'enterprise_model' ) SELECT table_name, naming_status FROM naming_check WHERE naming_status = 'NON_COMPLIANT' ORDER BY table_name;
Violation Example Fix Why
Wrong layer prefix staging_dim_customer pdm_dim_sales_customer Only pdm, ldm, cdm allowed; "staging" is not a layer
Wrong entity type pdm_table_sales_customer pdm_dim_sales_customer "table" is not specific; use dim, fact, xref, etc.
Wrong domain pdm_dim_crm_customer pdm_dim_sales_customer CRM is a tool, not a domain; use sales
Special characters pdm_dim_sales_customer-2024 pdm_dim_sales_customer_2024 Underscores only; hyphens forbidden in SQL
Enforcement via CI/CD
Pre-commit hook validates all table names before commit. CI/CD pipeline runs SQL linting that flags non-compliant names as blocking errors. No non-compliant DDL reaches production.

6Complete Compare: Detecting Semantic Drift

Semantic drift occurs when the model and reality diverge: the model says X, but the database has Y. ShaDey Inc. example: DIM_CUSTOMER model documents 42 columns; the database has 47. Five columns exist undocumented.

Types of drift:

Drift Type Example Severity Detection Method
Structural Column added: customer_segment (missing from model) Medium Column count mismatch
Semantic Column meaning changed: credit_limit redefined from USD to EUR Critical Manual review; hard to auto-detect
Referential Foreign key broken: customer_id no longer references KNA1 Critical FK constraint validation query
Type Mismatch Model: credit_limit is DECIMAL(15,2); DB: VARCHAR(50) High Data type comparison

SQL query for drift detection (compare model registry vs INFORMATION_SCHEMA):

-- Drift detection: model metadata vs actual database SELECT m.table_name, m.expected_column_count, COUNT(c.column_name) AS actual_column_count, CASE WHEN COUNT(c.column_name) > m.expected_column_count THEN 'COLUMN_ADDITION_DRIFT' WHEN COUNT(c.column_name) < m.expected_column_count THEN 'COLUMN_REMOVAL_DRIFT' ELSE 'IN_SYNC' END AS drift_status FROM model_registry m LEFT JOIN information_schema.columns c ON m.table_name = c.table_name WHERE m.table_schema = 'enterprise_model' GROUP BY m.table_name, m.expected_column_count HAVING COUNT(c.column_name) <> m.expected_column_count;
Severity Classification
Critical: Primary key change, foreign key broken. High: Data type mismatch, column renamed. Medium: Column added (if undocumented). Low: Comment changed, precision adjustment (15,2 to 15,4).

ShaDey Inc. runs drift detection daily at 2 AM; alerts sent to domain stewards for Critical/High drifts within 4 hours.

7Version Reconciliation

When multiple teams change the model concurrently, conflicts arise. ShaDey Inc. scenario: Sales team adds loyalty_tier to DIM_CUSTOMER; Finance team adds credit_score the same week. Both PRs are valid but touch the same table.

Version control strategy for data models:

Change Type Example Merge Strategy Approval Gate
Additive (non-conflicting) Sales adds loyalty_tier; Finance adds credit_score Auto-merge (different columns) Domain Steward only
Conflicting Both add customer_segment with different data types Manual merge required Enterprise Architect + Data Governance Board
Semantic Finance adds credit_score; Data Science adds predicted_churn_score (derived, should not be in dimension) Manual review Architecture Board review

Model version history DDL (tracks all changes):

CREATE TABLE model_version_registry ( version_id BIGINT PRIMARY KEY AUTO_INCREMENT, model_name VARCHAR(255) NOT NULL, version_number VARCHAR(20) -- e.g., 2.1.5 change_type ENUM('ADD_TABLE', 'ADD_COLUMN', 'DROP_COLUMN', 'MODIFY_COLUMN', 'ADD_FK', 'DROP_FK'), change_description TEXT, author VARCHAR(255), created_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, approved_by VARCHAR(255), approval_timestamp TIMESTAMP, effective_date DATE, git_commit_hash VARCHAR(40), rollback_version_id BIGINT -- reference to version being reverted ); -- Example records: -- v2.0: Add pdm_dim_sales_customer (base version, 2024-01-15) -- v2.1: Add pdm_fact_sales_opportunity (2024-02-20, approved by Sales Steward) -- v2.1.1: Add pdm_xref_finance_customer extension (2024-03-01) -- v2.1.2: Add loyalty_tier to pdm_dim_sales_customer (2024-03-15, conflict with v2.1.3) -- v2.1.3: Add credit_score to pdm_dim_sales_customer (2024-03-15, conflict with v2.1.2) -- Resolution: Merge both; loyalty_tier and credit_score coexist in v2.2
Rollback Safety
Every version references a prior version (version_id) and a git commit hash. Rollback is atomic: SELECT all changes in version V, generate reverse DDL, apply in reverse order. Rollback to v2.0 removes all changes from v2.0 onward.

8Conflict Resolution Workflows

When two domains disagree on entity definition or attribute placement, the governance workflow escalates for structured resolution. ShaDey Inc. has a formal escalation path: Domain Steward → Enterprise Architect → Data Governance Board.

Example conflict: Sales wants to add predicted_churn_score to DIM_CUSTOMER. Data Science objects (derived metrics shouldn't be in dimensions; they belong in a feature store).

Governance Workflow: Propose → Review → Approve → Deploy
Propose Domain Steward submits PR Review Peer review & validation Approve Enterprise Architect sign-off Deploy DDL execution in production Conflict Detected? Yes → Escalate to Data Governance Board

SLA table: change type determines review and approval authority:

Change Type Review Time SLA Approval Authority Escalation Trigger
New table, local domain only 3 business days Domain Steward If domain unknown or impacts other domains
Column addition to existing table 5 business days Enterprise Architect If column name conflicts with another domain's pending change
Semantic change (rename, redefine) 7 business days Data Governance Board Always (any semantic change requires board approval)
Cross-domain shared entity change 7 business days Data Governance Board Always (impacts multiple domains)
Conflict Example: churn_score Placement
Sales proposes: Add predicted_churn_score to DIM_CUSTOMER. Data Science review comment: "This is a derived metric, not a customer attribute. It should live in a feature store table (FACT_CHURN_FEATURES), not a dimension. Dimensions are slowly-changing master data; derived metrics change frequently." Enterprise Architect agrees. Resolution: Create pdm_fact_churn_features(customer_id, churn_score, model_version, calculation_date) and join at query time.

9User-Defined Properties (UDP) Strategy

UDPs are custom metadata fields on model objects (tables, columns, relationships). They extend the model with governance context without changing structure. ShaDey Inc. UDP catalogue captures classification, ownership, compliance, and quality metadata.

ShaDey Inc. UDP catalogue:

UDP Name Applies To Values Example
data_classification Column PII | Confidential | Internal | Public customer_email: PII
source_system Table SAP | CRM | eCommerce | POS | Custom pdm_dim_sales_customer: SAP
retention_policy Column 7yr | 3yr | 1yr | session | indefinite credit_card_number: 1yr
gdpr_relevant Column true | false customer_name: true
change_frequency Column real-time | daily | weekly | monthly | annual loyalty_tier: monthly
data_owner Table Domain team name pdm_dim_sales_customer: Sales
quality_sla Table 99.9% | 99% | 95% | SLA_not_defined pdm_dim_sales_customer: 99.9%

Metadata table design (implements UDP without modeling tool):

CREATE TABLE model_element_properties ( property_id BIGINT PRIMARY KEY AUTO_INCREMENT, element_type ENUM('TABLE', 'COLUMN', 'RELATIONSHIP'), element_schema VARCHAR(255), element_name VARCHAR(255), udp_name VARCHAR(255), udp_value TEXT, created_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, modified_timestamp TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_element_udp (element_type, element_schema, element_name, udp_name) ); -- Example inserts: -- ('TABLE', 'enterprise_model', 'pdm_dim_sales_customer', 'data_owner', 'Sales') -- ('TABLE', 'enterprise_model', 'pdm_dim_sales_customer', 'quality_sla', '99.9%') -- ('COLUMN', 'enterprise_model', 'pdm_dim_sales_customer.customer_email', 'data_classification', 'PII') -- ('COLUMN', 'enterprise_model', 'pdm_dim_sales_customer.customer_email', 'retention_policy', '1yr') -- ('COLUMN', 'enterprise_model', 'pdm_dim_sales_customer.customer_email', 'gdpr_relevant', 'true')

Query: Show all PII columns across the enterprise model:

SELECT element_schema, element_name, GROUP_CONCAT(udp_value) AS classifications FROM model_element_properties WHERE element_type = 'COLUMN' AND udp_name = 'data_classification' AND udp_value = 'PII' GROUP BY element_schema, element_name ORDER BY element_schema, element_name;
UDP Benefits
Compliance: Query all PII columns for GDPR audits. Data Quality: Flag tables below SLA. Ownership: Route alerts to domain teams. Change Management: Highlight high-retention columns when considering removal.

10Neo4j Graph for Model Governance Lineage

Graph databases excel at tracking lineage: model versions, ownership chains, drift dependencies, and impact analysis. ShaDey Inc. uses Neo4j to answer: "If we change DIM_CUSTOMER, which domains are affected? Which reports break?"

Neo4j graph model:

Node Type Properties Example
Model name, version, effective_date, approved_by pdm_dim_sales_customer v2.1
Domain domain_name, steward, team_email Sales (steward: john@shadey.in)
Drift drift_id, severity, type, detected_date DRIFT_001: Column addition, HIGH
Report/Query query_name, owner, last_execution sales_performance_monthly (owner: Analytics)

Cypher: Create model version nodes and ownership edges:

-- Create model version node CREATE (m:ModelVersion { model_name: 'pdm_dim_sales_customer', version_number: '2.1.2', effective_date: '2025-03-15', approved_by: 'jane_architect@shadey.in', approval_timestamp: '2025-03-15T14:30:00Z' }) -- Create domain ownership edge CREATE (d:Domain { domain_name: 'Sales', steward: 'john_sales@shadey.in' }) -- Create relationship: Domain owns Model CREATE (d)-[:OWNS]->(m) -- Create drift node and link to model CREATE (drift:Drift { drift_id: 'DRIFT_042', type: 'COLUMN_ADDITION', severity: 'MEDIUM', detected_date: '2025-03-20', description: 'Column loyalty_tier added to database but not in model' }) -- Link drift to model CREATE (drift)-[:DETECTED_IN]->(m) -- Create report node dependent on model CREATE (report:Report { report_name: 'sales_performance_monthly', owner: 'analytics_team@shadey.in', last_execution: '2025-03-20T06:00:00Z' }) -- Link report to model (dependency) CREATE (report)-[:DEPENDS_ON]->(m)

Cypher query: Show all model changes in the last 30 days with approval status:

MATCH (m:ModelVersion)-[:OWNS]-(d:Domain) WHERE m.effective_date >= date() - duration({days: 30}) RETURN m.model_name AS model, m.version_number AS version, m.effective_date AS effective_date, d.domain_name AS domain, m.approved_by AS approved_by ORDER BY m.effective_date DESC;

Cypher query: Which domains are affected if DIM_CUSTOMER schema changes?

MATCH (d:Domain)-[:OWNS]->(m:ModelVersion {model_name: 'pdm_dim_sales_customer'}), (d2:Domain), (m2:ModelVersion)-[:EXTENDS]->(m), (d2)-[:OWNS]->(m2) RETURN d.domain_name AS primary_owner, collect(DISTINCT d2.domain_name) AS dependent_domains, count(m2) AS num_extensions;
Impact Analysis
If Sales changes DIM_CUSTOMER schema, query returns: Finance (1 extension), Marketing (1 extension), Supply Chain (0 extensions). Alerts sent to Finance and Marketing stewards before change is deployed.