Three scenarios illustrating what happens when key stakeholders are excluded from the data modeling and pipeline creation process, with detailed metadata, ontology, business glossary, attribute definitions, quality dimensions, and regulatory impact at every layer.
How the business term "Customer" flows (or fails to flow) from conceptual definition through to pipeline implementation in each scenario.
Customer with attributes: customer_id (PK, UUID), full_name (VARCHAR 200, NOT NULL), customer_type (ENUM: B2B|B2C|Wholesale), registration_date (DATE). Business rule: customer_type determines pricing tier.dim_customer, partitioned by region, indexed on customer_id + customer_type. PII columns (full_name, email) flagged for masking. Lineage: maps to LDM Customer entity and CDM glossary term.pipe_erp_to_dim_customer. Source: SAP KNA1/BUT000. Transform: deduplicate on tax_id, standardize addresses, apply customer_type classification. Data contract: SLA 15-min freshness, null rate < 0.1%.Cust with attributes: cust_id (INT), nm (VARCHAR 50), tp (VARCHAR 10, no validation). Business rules guessed from data sampling. Missing: loyalty status, registration channel.tbl_customer, no partitioning strategy. PII not flagged. No lineage link to LDM. Column tp contains values "1", "2", "3" with no reference data table.load_customers. Copies raw ERP data with minimal transformation. No dedup. Business discovers wholesale customers mixed with retail customers 3 months later.customers, cust_data_v2, customer_export_final. Each has different columns. No one knows which is authoritative. 40% record overlap.Side-by-side comparison showing the difference between a properly governed attribute definition and one built without business input. Example: customer_type in a Retail context.
| Property | Value |
|---|---|
| Business Name | Customer Type |
| Business Definition | Classification of a customer based on their purchasing relationship: B2C (individual consumer), B2B (business buyer), or Wholesale (bulk distributor) |
| Logical Name | customer_type |
| Data Type | ENUM ('B2C', 'B2B', 'Wholesale') |
| Nullable | No; every customer must be classified |
| Default Value | 'B2C' (most common segment) |
| Valid Values | B2C, B2B, Wholesale (managed in ref_customer_type) |
| Business Rule | Determines pricing tier, discount eligibility, and payment terms |
| Data Owner | VP, Customer Operations |
| Glossary Term | Linked to "Customer Classification" in business glossary |
| PII Classification | Non-PII (classification, not personal data) |
| Quality Rule | Must be one of 3 valid values; completeness ≥ 99.9% |
| Property | Value |
|---|---|
| Business Name | Not documented |
| Business Definition | Not documented. Engineers assume "some kind of customer flag" |
| Logical Name | tp (in one table), cust_type (another), type_cd (third) |
| Data Type | VARCHAR(50), no constraints |
| Nullable | Yes; 12% of records are NULL |
| Default Value | None |
| Valid Values | Contains: 1, 2, 3, "B2C", "b2c", "RETAIL", "wholesale", NULL, "", "unknown" |
| Business Rule | Not documented. Each pipeline applies different logic |
| Data Owner | Nobody. "IT owns the data" |
| Glossary Term | No business glossary exists |
| PII Classification | Never assessed |
| Quality Rule | None. Issues found when CFO asks "why don't these numbers match?" |
Tracing a Product entity from business concept through all four layers in the ideal governed scenario, showing how ontology, metadata, and data contracts preserve meaning at each step.
Product: product_id (PK, UUID), sku_code (VARCHAR 20, UNIQUE, NOT NULL), product_name (VARCHAR 200, NOT NULL), category_id (FK → Category), brand_code (FK → Brand), unit_of_measure (ENUM: EA|KG|LB|L), is_active (BOOLEAN), launch_date (DATE).dim_product (Snowflake, partitioned by category_id, clustered on sku_code).product_sk (BIGINT, surrogate key), product_id (UUID, natural key), sku_code, product_name, category_sk (FK → dim_category), brand_sk (FK → dim_brand), uom_code, is_active_flag, launch_dt, effective_from_ts, effective_to_ts (SCD Type 2).pipe_sap_to_dim_product_v3| Requirement | Regulation | Ideal | Scenario 1 | Scenario 2 |
|---|---|---|---|---|
| Data Lineage: trace data from source to report | SOX, BCBS 239 | Compliant | Partial | Non-compliant |
| Right to Erasure: locate and delete personal data | GDPR Art. 17 | Compliant | Partial | Non-compliant |
| Data Quality & Accuracy: ensure data is correct | GDPR Art. 5, BCBS 239 | Compliant | Partial | Non-compliant |
| Data Catalog / Inventory: know what data you have | GDPR Art. 30 | Compliant | Partial | Non-compliant |
| Access Controls: restrict data by role | GDPR, SOX | Compliant | Partial | Non-compliant |
| Audit Trail: log who accessed/changed data | SOX, GDPR | Compliant | Partial | Non-compliant |
| Data Ownership: clear accountability | BCBS 239, SOX | Compliant | Non-compliant | Non-compliant |
| Consent Management: track data processing basis | GDPR Art. 6-7 | Compliant | Partial | Non-compliant |
Estimated annual cost impact for a mid-size retailer ($500M revenue) based on industry benchmarks (Gartner, MIT CDOIQ). Costs include rework, lost revenue, compliance penalties, and opportunity cost.
ROI of governance: Spending $250K to prevent $5.2M in losses = 20x return. Per Gartner: organizations estimate the average cost of poor data quality at $12.9M per year.
Mapping each scenario to DAMA DMBOK maturity levels and EDM Council's DCAM framework.
| Capability | Ideal (Level 4-5) | Scenario 1 (Level 2) | Scenario 2 (Level 0-1) |
|---|---|---|---|
| Data Architecture | Enterprise data model with CDM/LDM/PDM alignment. Ontology-driven design. | Models exist but disconnected from business meaning. No ontology validation. | No formal architecture. Tables created ad-hoc per request. |
| Data Quality | Proactive: rules at every layer, automated monitoring, SLAs enforced. | Reactive: issues found in reports, some checks at pipeline level. | None: quality issues discovered by end users in production. |
| Metadata Management | Active metadata: business glossary, data catalog, automated lineage, classification. | Passive metadata: some technical metadata in tools, no business context. | No metadata: column names are the only documentation. |
| Data Governance | Embedded: RACI, certification gates, change management, stewardship program. | Defined: policies exist on paper but not enforced at model creation. | Ad-hoc: no policies, no ownership, no accountability. |
| Data Stewardship | Active stewards review changes, maintain glossary, enforce standards. | Named but not engaged at modeling stage. Stewards react to issues. | No stewards. "IT owns the data" is the default assumption. |
| Regulatory Compliance | Built-in: PII tagging, retention policies, consent tracking, audit trail. | Bolted-on: compliance addressed after models built, gaps in PII coverage. | Non-existent: unknown PII exposure, no audit trail, fine risk. |
Step-by-step path from ungoverned pipelines to full governance maturity. Each phase builds on the previous one.
Stop the bleeding. Catalog existing pipelines and tables. Identify data owners.
Reverse-engineer logical and conceptual models from existing physical tables, validated with business stakeholders.
Introduce certification gates. No new pipeline goes to production without an approved logical model.
Embed governance into culture. Measure and optimize. Achieve DAMA Level 4-5.
When Business Owners and Data Owners are excluded from data modeling, the resulting physical layer and pipelines encode technical assumptions instead of business reality. The ontology, business glossary, and attribute definitions (the foundation of shared meaning) are either wrong or missing entirely.
Cost of fix at CDM stage: 1x; at Pipeline stage: 50-100x"We'll validate with business later," but later never comes. Pressure to deliver pipelines fast leads teams to skip the conceptual and logical layers. Without certification gates, metadata requirements, and data contracts, there is no structural mechanism to enforce collaboration.
70% of data projects fail due to poor requirements, not poor technologyEmbed Business Owners at the CDM stage and Data Owners at the LDM stage as mandatory approvers. Implement certification gates between layers. Require data contracts before any pipeline goes live. Maintain an active business glossary and ontology that every model must reference.
Prevention is 20x cheaper than remediation