Medallion Architecture & Graph Modeling | ShaDey Inc. Data Platform

1Why Medallion Architecture?

The medallion architecture (Bronze, Silver, and Gold layers) has become the de facto standard for data lakehouse design. It aligns naturally with ShaDey's existing Conceptual (CDM), Logical (LDM), and Physical (PDM) data modeling tiers.

The Three Layers at a Glance

Bronze layers capture raw data exactly as received from source systems (SAP ECC, Salesforce, Shopify, POS terminals) with zero transformation. They are source-aligned: one Bronze table per feed.

Silver layers perform identity resolution, deduplication, type casting, and conformity rules. A single silver_customer table unifies SAP KNA1 masters, CRM accounts, and eCommerce shoppers into one golden record per business entity.

Gold layers deliver business-ready aggregations and dimensional models (facts and dimensions). A gold_fact_daily_sales table at grain store×product×date feeds executive dashboards and analyst self-service.

Bronze = PDM (Physical: exact copy from source). Silver = LDM (Logical: conformed and enriched). Gold = CDM (Conceptual: business semantics).

Medallion vs. Lambda vs. Kappa

Pattern	Batch/Stream	Complexity	Use Case
Medallion (3-tier)	Primarily batch; stream-friendly	Moderate; clear separation of concerns	Retail: Sales orders, inventory, customer master daily feeds
Lambda (batch + speed)	Both; parallel paths	High; code duplication risk	Real-time dashboards with batch backfill (rare in retail)
Kappa (stream only)	Streaming; event-driven	Moderate once mastered	Event log analytics, Kafka-sourced ingestion

Decision Criteria: Choose Medallion if you have scheduled batch feeds (most retail ERPs), clear source-to-target mapping, and analysts who expect dimensional models. Choose Kappa if all data flows through Kafka and you need sub-second latency. Avoid Lambda: it doubles code complexity.

Medallion is built for ShaDey's retail supply chain: SAP daily snapshots, POS nightly uploads, and Salesforce weekly syncs all land in Bronze, then flow through Silver transformation, and emerge as Gold fact tables ready for BI tools.

Medallion Architecture Three-Layer Flow: Bronze layer (amber) captures raw source data with zero transformation. Silver layer (blue) cleanses, deduplicates, and conforms data with identity resolution. Gold layer (green) creates BI-ready star schema with aggregations and dimensional models. Quality gates validate data between layers.

2Bronze Layer: Fidelity Preservation

Bronze is the raw landing zone. No transformations, no business rules, no data quality filters. One table per source feed. Columns are appended for ingestion metadata: _source_file, _ingested_at, _batch_id.

ShaDey Bronze Tables

bronze_sap_vbak bronze_sap_vbrk bronze_crm_accounts bronze_shopify_orders bronze_pos_transactions

Table	Source	Schema	Grain
`bronze_sap_vbak`	SAP ECC (table VBAK)	`vbeln, erdat, erprio, _raw_payload`	1 row per sales order header
`bronze_sap_vbrk`	SAP ECC (table VBRK)	`vbeln, fkdat, netwr, _raw_payload`	1 row per billing document
`bronze_crm_accounts`	Salesforce REST API	`AccountId, Name, Industry, _raw_json`	1 row per account
`bronze_shopify_orders`	Shopify GraphQL endpoint	`order_id, created_at, total, _raw_json`	1 row per eCommerce order
`bronze_pos_transactions`	POS CSV nightly export	`trans_id, store_id, amount, _raw_line`	1 row per POS scan

Bronze DDL: SAP Sales Order Headers

CREATE TABLE bronze_sap_vbak ( vbeln VARCHAR(10), -- Sales order number (PK in SAP) erdat DATE, -- Creation date (as received from SAP) erprio INT, -- Priority code _raw_payload STRING, -- Full VBAK row as JSON string _source_file VARCHAR(255), -- e.g., s3://shadey-bronze/sap/vbak/2026-04-15.csv _ingested_at TIMESTAMP, -- UTC load time _batch_id VARCHAR(50) -- Unique batch identifier for idempotency ) PARTITIONED BY (_ingestion_date DATE);

Data Quality at Bronze

Schema validation only. No business rules. If SAP sends erdat = NULL, it lands as-is. If a POS field is mistyped, raw bytes are preserved.

Bronze is a compliance camera. It records exactly what arrived, unaltered, so you can replay transformations or debug data issues later.

Sample Bronze Data

vbeln | erdat | erprio | _raw_payload | _source_file --------+-----------+--------+-------------------------------------------+------------------------------ SO-2026 | 2026-04-15| 01 | {"vbeln":"SO-2026","erdat":"2026-04-15"} | s3://shadey-bronze/.../daily SO-2027 | 2026-04-15| 02 | {"vbeln":"SO-2027","erdat":"2026-04-15"} | s3://shadey-bronze/.../daily SO-2028 | NULL | 01 | {"vbeln":"SO-2028","erdat":null} | s3://shadey-bronze/.../daily

3Silver Layer: Conformity & Enrichment

Silver tables are cleansed, conformed, and deduplicated. Identity resolution occurs here: cross-system records are linked to a single business entity. Type casting, null handling, and date standardization are applied. One conformed table replaces N Bronze sources.

ShaDey Silver Tables

silver_customer silver_product silver_sales_order silver_inventory

silver_customer is the golden record unifying SAP KNA1 (vendor/customer master), CRM Salesforce accounts, and Shopify shoppers into one normalized entity.

Silver DDL: Conformed Customer

CREATE TABLE silver_customer ( customer_key BIGINT PRIMARY KEY, -- Surrogate key, auto-generated customer_id_sap VARCHAR(10), -- KNA1.KUNNR (SAP key) customer_id_crm VARCHAR(18), -- Salesforce AccountId (CRM key) customer_id_shopify VARCHAR(50), -- Shopify customer ID (eCommerce key) name VARCHAR(100), -- Conformed name (cleaned, standardized) email VARCHAR(255), -- Primary email, validated phone VARCHAR(20), -- Phone, normalized E.164 country VARCHAR(2), -- ISO-3166 code customer_type VARCHAR(50), -- 'retail' | 'wholesale' | 'partner' dbt_valid_from TIMESTAMP, -- SCD Type 2 effective date dbt_valid_to TIMESTAMP, -- SCD Type 2 expiry date (NULL = current) dbt_updated_at TIMESTAMP, -- Last modification _dupe_score DECIMAL(3, 2) -- 0.0-1.0 fuzzy match confidence ) PARTITIONED BY (_effective_date DATE);

Silver Transformation Logic: Bronze → Silver (Customer)

WITH sap_customers AS ( SELECT kunnr AS customer_id_sap, CAST(name1 AS VARCHAR) AS sap_name, TRIM(adrnr) AS sap_country, NULL AS customer_id_crm, NULL AS customer_id_shopify, _ingested_at AS src_date, 'SAP' AS source FROM bronze_sap_kna1 WHERE _ingestion_date = CURRENT_DATE() ), crm_customers AS ( SELECT NULL AS customer_id_sap, Name AS crm_name, BillingCountry AS crm_country, AccountId AS customer_id_crm, NULL AS customer_id_shopify, _ingested_at AS src_date, 'CRM' AS source FROM bronze_crm_accounts WHERE _ingestion_date = CURRENT_DATE() ) SELECT * FROM sap_customers UNION ALL SELECT * FROM crm_customers;

A fuzzy matching algorithm (Levenshtein distance on names + phone) identifies duplicates across sources. Example: SAP "John Smith, 555-1234" + CRM "John Smith, (555) 123-4" → single customer_key = 42857.

Sample Silver Data

4Gold Layer: Semantic Clarity

Gold tables deliver business-ready aggregations and dimensional models. Mapped to ShaDey's 24-entity PDM, they organize data into facts (transactional events) and dimensions (reference data). Star schema enables rapid analyst queries.

Fact vs. Dimension

Fact tables record events: a sales transaction, an inventory movement. Each row is a business occurrence. Dimension tables describe entities: customers, products, stores, dates. They have slowly changing dimensions (SCD Type 2) to track historical changes.

Gold DDL: Daily Sales Fact (Grain: Store × Product × Date)

CREATE TABLE gold_fact_daily_sales ( fact_key BIGINT PRIMARY KEY, store_key BIGINT REFERENCES gold_dim_store, product_key BIGINT REFERENCES gold_dim_product, customer_key BIGINT REFERENCES gold_dim_customer, date_key INT REFERENCES gold_dim_date, units_sold DECIMAL(12, 2), gross_revenue DECIMAL(12, 2), discount_amount DECIMAL(12, 2), net_revenue DECIMAL(12, 2), cogs DECIMAL(12, 2), profit_margin DECIMAL(5, 4) ) PARTITIONED BY (date_key);

Gold DDL: Customer Dimension (SCD Type 2)

CREATE TABLE gold_dim_customer ( customer_key BIGINT PRIMARY KEY, customer_id VARCHAR(50), -- Business key name VARCHAR(100), email VARCHAR(255), country_code CHAR(2), customer_segment VARCHAR(50), -- 'platinum' | 'gold' | 'silver' | 'bronze' lifetime_value DECIMAL(12, 2), effective_date DATE, -- SCD Type 2: when this version became effective expiry_date DATE, -- SCD Type 2: when this version expired (NULL = current) is_current BOOLEAN ) PARTITIONED BY (effective_date);

Gold DDL: Product Dimension (with Hierarchy)

CREATE TABLE gold_dim_product ( product_key BIGINT PRIMARY KEY, sku VARCHAR(50) UNIQUE, -- SKU / business key product_name VARCHAR(255), subcategory_key BIGINT, -- Hierarchy level 2 category_key BIGINT, -- Hierarchy level 1 department_key BIGINT, -- Hierarchy level 0 unit_cost DECIMAL(10, 2), list_price DECIMAL(10, 2), effective_date DATE, expiry_date DATE, is_current BOOLEAN );

Gold DDL: Date Dimension (Three Calendars)

CREATE TABLE gold_dim_date ( date_key INT PRIMARY KEY, -- YYYYMMDD format: 20260415 calendar_date DATE UNIQUE, calendar_year INT, calendar_month INT, calendar_day INT, fiscal_year INT, -- ShaDey fiscal year (Feb-Jan) fiscal_quarter INT, fiscal_period INT, retail_week INT, -- Retail calendar (4-5-4 pattern) retail_period INT, day_of_week INT, is_weekend BOOLEAN, is_holiday BOOLEAN );

Gold delivers one query, instant answer. A business analyst writes: SELECT SUM(net_revenue) FROM gold_fact_daily_sales WHERE fiscal_year = 2026 and gets retail's profit in milliseconds, no joins, no complexity.

5Cross-Layer Data Flow: End-to-End Example

Trace a single ShaDey retail transaction: a customer buys a product at a store. Watch it flow from POS terminal through all three layers.

The Journey

POS Terminal (Source): Elena Costa scans a bottle of Malbec (SKU: WINE-2024-MALBEC) at Store 42 in Madrid on 2026-04-15 09:45:32 UTC. The terminal records:

POS CSV Line (Raw)

STORE_ID,TERMINAL_ID,TRANS_ID,SKU,UNITS,GROSS_AMOUNT,DISCOUNT,NET_AMOUNT,TIMESTAMP 42,T001,TXN-2026-04-15-000847,WINE-2024-MALBEC,1,18.99,0.00,18.99,2026-04-15T09:45:32Z

↓ Bronze (Ingestion at 14:00 UTC):

bronze_pos_transactions

store_id | terminal_id | trans_id | sku | units | gross_amount | discount | net_amount | timestamp | _source_file | _ingested_at | _batch_id ---------+-------------+------------------------+----------------------+-------+--------------+----------+------------+-------------------------+-----------------------------------------------+---------------------+----------- 42 | T001 | TXN-2026-04-15-000847 | WINE-2024-MALBEC | 1 | 18.99 | 0.00 | 18.99 | 2026-04-15T09:45:32Z | s3://shadey-bronze/.../store42_daily.csv | 2026-04-15 14:00:00 | B-2026-04-15-001

↓ Silver (Enrichment & Identity Resolution):

Transformations applied:

Cast timestamp to TIMESTAMP type; parse timezone
Look up sku in silver_product to find product_key = 8847
Look up store_id = 42 in store master to find store_key = 521
Extract customer from POS transaction (link card ID to silver_customer) → customer_key = 42857 (Elena Costa)
Calculate profit_margin = (net_amount - cogs) / net_amount

silver_sales_order (simplified view)

order_id | customer_key | product_key | store_key | order_date | units | gross_revenue | cogs | net_revenue | transaction_source ---------+--------------+-------------+-----------+-------------------------+-------+---------------+-------+-------------+-------------------- 48392 | 42857 | 8847 | 521 | 2026-04-15 09:45:32 UTC | 1 | 18.99 | 8.50 | 18.99 | POS

↓ Gold (Fact Table Aggregation):

The transaction becomes one row in gold_fact_daily_sales at grain store×product×date. If Elena buys another bottle later that day, it's aggregated with this row.

gold_fact_daily_sales (relevant columns)

fact_key | store_key | product_key | customer_key | date_key | units_sold | gross_revenue | net_revenue | cogs ---------+-----------+-------------+--------------+----------+------------+---------------+-------------+------ 5280847 | 521 | 8847 | 42857 | 20260415 | 1 | 18.99 | 18.99 | 8.50

Bronze preserves raw bytes; Silver resolves identity and applies data quality rules; Gold aggregates to business grain. One transaction becomes one fact row. Two transactions (same store, product, day) → one row with units_sold = 2, gross_revenue = 37.98.

6Medallion + Data Vault Coexistence

Medallion and Data Vault are not rivals. They complement each other. Bronze is a Raw Vault staging layer. Silver contains Business Vault logic and link tables. Gold is the Information Mart.

Mapping the Paradigms

Component	Data Vault Equivalent	Purpose
Bronze	Raw Vault staging + load history	Fidelity to source; audit trail
Silver	Business Vault + Link tables	Identity resolution; relationship capture
Gold	Information Mart	Business semantics; dimensional model

Example: SAP VBAK (sales order header) lands in bronze_sap_vbak. Silver creates a silver_link_customer_order table to capture the relationship between customer and order. Gold materializes gold_fact_daily_sales which pre-joins all dimensions for BI tool consumption.

Use both paradigms in one platform. Bronze (Raw Vault) loads fast. Silver (Business Vault) resolves identity. Gold (Information Mart) delivers speed. No conflict; layers of increasing business value.

7Relational → Graph Conversion Methodology

Graph databases excel at traversal: "Show me all products Elena bought, sorted by date." Relational tables must be systematically converted to graph nodes and edges. A methodical approach ensures no semantic loss.

Four-Step Conversion Framework

Relational-to-Graph Conversion: Left side shows normalized relational tables (DIM_CUSTOMER, FACT_SALES, DIM_PRODUCT, DIM_STORE) with foreign key relationships. Right side shows converted graph model with nodes (Customer, Product, Store) and edges ([:PURCHASED], [:SOLD_AT]) carrying properties. Four-step systematic conversion ensures no semantic loss.

Step 1: Identify Nouns (Entities) → Nodes

Every table with identity is a node type. ShaDey's relational model yields:

Customer from DIM_CUSTOMER
Product from DIM_PRODUCT
Order from FACT_SALES
Store from DIM_STORE
Inventory from FACT_INVENTORY

Step 2: Identify Verbs (Relationships) → Edges

Every foreign key or association becomes an edge type.

Customer → Order: [:PURCHASED]
Order → Product: [:CONTAINS]
Product → Category: [:BELONGS_TO]
Order → Store: [:SOLD_AT]
Inventory → Product: [:HOLDS]

Step 3: Identify Attributes → Properties

Every column becomes a node or edge property.

Relational Column	Target	Graph Property
DIM_CUSTOMER.customer_key	(:Customer) node	customer_id
DIM_CUSTOMER.name	(:Customer) node	name
FACT_SALES.units_sold	[:PURCHASED] edge	units
FACT_SALES.net_revenue	[:PURCHASED] edge	amount
FACT_SALES.order_date	[:PURCHASED] edge	purchased_at

Step 4: Identify Hierarchies → Relationship Chains

Dimensional hierarchies (SKU → subcategory → category → department) become relationship chains in the graph:

Cypher: Product Hierarchy

(:Product) -[:BELONGS_TO]→ (:Subcategory) -[:BELONGS_TO]→ (:Category) -[:BELONGS_TO]→ (:Department)

Conversion Rules Table

Relational Pattern	Graph Pattern	Example
Dimension table (1 PK)	Node label	DIM_CUSTOMER → (:Customer {id: 'C123'})
Fact table with FK	Relationship with properties	FACT_SALES.customer_key → [:PURCHASED {amount: 18.99}]
Slowly Changing Dimension (SCD Type 2)	Multiple versions with effective_date	(:Customer {effective_date: '2025-01-01'}) & (:Customer {effective_date: '2026-01-01'})
Parent-child hierarchy (e.g., category-subcategory)	Relationship chain	(:Product) -[:BELONGS_TO]→ (:Subcategory) -[:BELONGS_TO]→ (:Category)
Many-to-many junction table	Direct relationship	JUNCTION_TABLE (A_id, B_id) → (:A) -[:RELATED]→ (:B)

Full Cypher DDL: ShaDey Retail Graph

Create Nodes and Indexes

-- Nodes CREATE (c:Customer {id: 'C001', name: 'Elena Costa', country: 'ES'}) CREATE (p:Product {sku: 'WINE-2024-MALBEC', name: 'Malbec 2024', price: 18.99}) CREATE (s:Store {id: 'S42', city: 'Madrid', country: 'ES'}) CREATE (cat:Category {name: 'Beverages'}) CREATE (subcat:Subcategory {name: 'Wine'}) -- Relationships CREATE (c) -[:PURCHASED {amount: 18.99, units: 1, purchased_at: '2026-04-15'}]-> (p) CREATE (p) -[:SOLD_AT]-> (s) CREATE (p) -[:BELONGS_TO]-> (subcat) CREATE (subcat) -[:BELONGS_TO]-> (cat) -- Indexes for performance CREATE INDEX ON :Customer(id) CREATE INDEX ON :Product(sku) CREATE INDEX ON :Store(id)

Every relational entity and relationship has a graph equivalent. The conversion is systematic and loss-less. Graph queries enable traversals impossible in SQL (e.g., "Show me customers in Spain who bought wine, plus their purchase history").

8Temporal Relationships in Graph

Graphs excel at expressing relationships that change over time. ShaDey must model static relationships (a product belongs to a category) and temporal relationships (a customer purchased a product on a specific date with a specific amount).

Three Types of Relationships

1. Static Edges: Permanently True

(:Customer)-[:LIVES_IN]→(:City): If Elena lives in Madrid, this relationship is stable (unless she moves, handled separately).

2. Temporal Edges: Time-Stamped Events

(:Customer)-[:PURCHASED {date: '2026-01-15', amount: 47.50}]→(:Product): Properties capture the event's context. Elena purchased Malbec on Jan 15 for €47.50.

3. Versioned Edges: Period-Valid Relationships

(:Employee)-[:REPORTS_TO {from: '2025-01-01', to: '2025-12-31'}]→(:Manager): SCD Type 2 in graph form. One edge per tenure period.

ShaDey Temporal Examples

Static: Product Hierarchy (No Time Dimension)

CREATE (p:Product {sku: 'WINE-2024'}) CREATE (sc:Subcategory {name: 'Wine'}) CREATE (p) -[:BELONGS_TO]-> (sc)

Temporal: Customer Purchases (Time-Stamped Events)

CREATE (elena:Customer {id: 'C001', name: 'Elena'}) CREATE (malbec:Product {sku: 'WINE-2024'}) CREATE (elena) -[:PURCHASED { purchased_at: '2026-01-15T14:30:00Z', amount: 47.50, units: 2, order_id: 'ORD-2026-001' }]-> (malbec)

Versioned: Employee Reports-To (SCD Type 2 Pattern)

-- Employee Elena reports to Manager Bob (2025 tenure) CREATE (elena_emp:Employee {id: 'E123', name: 'Elena'}) CREATE (bob_mgr_2025:Manager {id: 'M456', name: 'Bob', tenure_start: '2025-01-01', tenure_end: '2025-12-31'}) CREATE (elena_emp) -[:REPORTS_TO { from: '2025-01-01', to: '2025-12-31' }]-> (bob_mgr_2025)

Cypher Temporal Queries

Query 1: Elena's Purchase History (Ordered by Date)

MATCH (elena:Customer {name: 'Elena'}) -[:PURCHASED]-> (product:Product) RETURN product.name, product.sku ORDER BY purchased_at DESC

Query 2: Who Was Store Manager in Q3 2025?

MATCH (store:Store {id: 'S42'}) -[:MANAGED_BY]-> (mgr:Manager) WHERE mgr.tenure_start <= '2025-07-01' AND mgr.tenure_end >= '2025-09-30' RETURN mgr.name

Query 3: Customers Who Purchased A Then B Within 30 Days

MATCH (cust:Customer) -[r1:PURCHASED]-> (prodA:Product {sku: 'WINE-2024'}) MATCH (cust) -[r2:PURCHASED]-> (prodB:Product {sku: 'VODKA-2024'}) WITH cust, r1.purchased_at AS date_a, r2.purchased_at AS date_b WHERE duration.between(date_a, date_b).days <= 30 AND date_b > date_a RETURN cust.name, date_a, date_b

Point-in-Time Snapshots vs. Temporal Properties

Approach	Pros	Cons
Snapshot (Versioned Nodes)	Simple queries; clear versions	Duplicate nodes per period
Temporal Properties (Time-Stamped Edges)	One node; edge dates; compact	Complex range queries

Use temporal properties (timestamps on edges) for event data (purchases, inventory movements). Use versioned nodes for master data (product prices, employee roles). Hybrid approach maximizes query flexibility.

9Relational + Graph Coexistence Without Duplication

ShaDey runs star schema in Snowflake for aggregation and graph in Neo4j for traversal. The trick is preventing duplicate business logic. Sync strategies ensure both systems stay in agreement.

The Dual-Model Discipline

Relational (Snowflake) wins for: "What are total sales by category this quarter?" (sum, group by, many rows to aggregate).

Graph (Neo4j) wins for: "Show me Elena's purchase history, plus products she bought alongside them" (traversal, relationships, pattern matching).

Decision Matrix: Which Query Goes Where?

Query Pattern	Relational	Graph
Aggregate (SUM, COUNT, AVG)	✓ Fast
Traversal (N hops, variable depth)		✓ Fast
Pattern matching (e.g., A→B→C)		✓ Fast
Fact table join (star schema)	✓ Optimized
Graph analytics (centrality, community)		✓ Native

Data Sync Pipeline Design

Relational → Graph (CDC-Based): Firestore logs Bronze/Silver table changes. A Kafka consumer publishes changes to Neo4j via transactional writes. Schema matches DDL in Section 7.

Graph → Relational (Materialized Views): Neo4j exports aggregated results (e.g., customer centrality scores) as CSV files. Snowflake materializes into a gold_customer_graph_metrics table.

Sync Workflow: Silver → Graph (via Kafka)

-- Event produced to Kafka topic "silver-customer-changes" when silver_customer updates { "event_type": "CUSTOMER_UPDATED", "customer_key": 42857, "customer_id": "C001", "name": "Elena Costa", "email": "elena@costa.com", "country": "ES", "timestamp": "2026-04-15T14:30:00Z" } -- Neo4j transaction (idempotent, keyed by customer_id) MATCH (c:Customer {id: 'C001'}) SET c.name = 'Elena Costa', c.email = 'elena@costa.com', c.country = 'ES', c.synced_at = datetime() RETURN c

Use CDC (Change Data Capture) to sync relational changes to graph. Implement idempotent upserts in Neo4j keyed by business ID (customer_id, product_sku). Avoid dual-write patterns; order of consistency is: Relational (source of truth) → Graph (derived).

10Enterprise Model Governance

ShaDey's data models (CDM, LDM, PDM) must be governed as living artifacts. Forward engineering (model → DDL) and reverse engineering (DDL → model) keep documents and databases in sync.

Governance Workflow

Propose: A data engineer proposes a new column in DIM_CUSTOMER: loyalty_tier VARCHAR(50).
Review: The data governance board reviews impact: which reports use DIM_CUSTOMER? Which ETL jobs need updates?
Approve: If impact is acceptable, the change is approved and scheduled.
Deploy: The DDL is applied to Snowflake. The model document is updated to reflect the change.

Forward Engineering: Model → DDL

Start with the logical model (ERD). Tool (e.g., dbt, Liquibase) generates DDL automatically.

Proposed Model Change (Text)

Entity: DIM_CUSTOMER Attribute: loyalty_tier Type: VARCHAR(50) Nullable: YES Allowed values: 'platinum', 'gold', 'silver', 'bronze', NULL Default: NULL Effective date: 2026-05-01 Impact: affects LOYALTY_REPORT_v2, CUSTOMER_SEGMENT_DASHBOARD

Generated DDL (from model)

ALTER TABLE gold_dim_customer ADD COLUMN loyalty_tier VARCHAR(50) DEFAULT NULL;

Reverse Engineering: DDL → Model

Periodically reverse-engineer the live schema back into the model document.

Reverse Engineering Query (Snowflake)

SELECT table_name, column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name = 'gold_dim_customer' ORDER BY ordinal_position;

Complete Compare: Detecting Drift

Compare the model document against the live schema. If they disagree, a data engineer has made changes without documentation (a governance violation).

Drift Detection Logic (Pseudocode)

-- Read documented model model_attrs = read_json('data_model.json') -- Read live schema live_cols = query_snowflake('INFORMATION_SCHEMA.COLUMNS') -- Compare FOR EACH attr IN model_attrs: IF attr NOT IN live_cols: drift_issues.append('MISSING_IN_DB') FOR EACH col IN live_cols: IF col NOT IN model_attrs: drift_issues.append('UNDOCUMENTED_IN_MODEL')

Model document and live schema must match. Version both. Review all changes through the governance board. Use automated drift detection to flag discrepancies. This prevents "shadow IT" schemas.

11Semantic Drift Detection

Semantic drift occurs when a column's real-world meaning changes but the schema doesn't. customer_type originally means "retail/wholesale" but gradually gets overloaded with "internal/external". This breaks reports silently.

What Is Semantic Drift?

Example: In 2023, customer_type in DIM_CUSTOMER had exactly 2 values: 'Retail', 'Wholesale'. By 2026, due to ad-hoc fixes, it has 5 values: 'Retail', 'Wholesale', 'Internal', 'Partner', 'API'. Reports filtering on customer_type = 'Retail' now miss Partner customers who should be excluded.

Detection Methods

1. Data Profiling

Run periodic profiling on all columns. Alert on domain changes (new unexpected values).

Column Value Distribution Query

SELECT customer_type, COUNT(*) AS count, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct FROM gold_dim_customer GROUP BY customer_type ORDER BY count DESC;

Profile Output (Expected vs. Observed)

-- Documented valid values (from model doc) valid_values = ['Retail', 'Wholesale'] -- Observed values (from query above) observed_values = ['Retail', 'Wholesale', 'Internal', 'Partner', 'API'] -- DRIFT ALERT! new_values = ['Internal', 'Partner', 'API'] -- Not in schema documentation

2. Lineage Analysis

Trace a column's origin. If gold_dim_customer.customer_type historically came from silver_customer.customer_type (which came from SAP KNA1.KTOKD), check if the source domain changed.

3. Report Impact Analysis

Which reports reference customer_type`? Are they still accurate?



  Report Dependency Query
  SELECT DISTINCT
  report_name,
  report_owner,
  last_run_date
FROM metadata.report_lineage
WHERE referenced_column = 'customer_type'
  AND referenced_table = 'gold_dim_customer';

  ShaDey Semantic Drift Case Study
  Problem: LOYALTY_REPORT_v2 filters on customer_type = 'Retail' to calculate "core retail customer revenue". However, by Q2 2026, the customer_type` column had 5 undocumented values. The report was silently excluding 'Partner' customers, causing an undercount of ~8% revenue.

  Solution: Automated quarterly profiling detected the domain expansion. A data engineer was assigned to document the new values and update filters in LOYALTY_REPORT_v2.

  Governance Metrics
  
    
      
        Metric
        Q1 2026
        Q2 2026
        Target
      
    
    
      
        Columns with drift detected
        0
        3
        < 5 per quarter
      
      
        Days to resolve drift
        N/A
        18 days avg
        < 7 days
      
      
        Reports affected by unresolved drift
        0
        1
        0
      
    
  

  
    Profile all columns monthly. Alert on domain expansion (new values). Trace lineage for each drift alert. Update documentation and reports synchronously. Establish SLAs for drift resolution (7 days to document or revert).

Metric	Q1 2026	Q2 2026	Target
Columns with drift detected	0	3	< 5 per quarter
Days to resolve drift	N/A	18 days avg	< 7 days
Reports affected by unresolved drift	0	1	0





  12Neo4j for Full Lakehouse Lineage
  
    Model the entire medallion architecture (layers, tables, columns, transformations) as a graph in Neo4j. Then ask graph questions: "What Gold tables are affected if bronze_sap_vbak schema changes?"
  

  Lineage Graph Schema
  Create nodes for: Layer, Table, Column, Transformation. Create edges for: CONTAINS (layer has tables), FLOWS_TO (transformation connects source → target), REFERENCES (FK relationships).

  Lineage DDL: Cypher
  -- Layers
CREATE (bronze:Layer {name: 'Bronze', purpose: 'Raw fidelity'})
CREATE (silver:Layer {name: 'Silver', purpose: 'Conformed enriched'})
CREATE (gold:Layer {name: 'Gold', purpose: 'Semantic ready'})

-- Bronze Tables
CREATE (vbak:Table {name: 'bronze_sap_vbak', source: 'SAP ECC', schema: 'vbeln, erdat, erprio, _raw_payload'})
CREATE (crm:Table {name: 'bronze_crm_accounts', source: 'Salesforce'})

-- Silver Tables
CREATE (silver_cust:Table {name: 'silver_customer', grain: '1 row per business customer'})

-- Gold Tables
CREATE (gold_fact:Table {name: 'gold_fact_daily_sales', grain: 'store × product × date'})
CREATE (gold_cust_dim:Table {name: 'gold_dim_customer', type: 'dimension'})

-- Containment edges
CREATE (bronze) -[:CONTAINS]-> (vbak)
CREATE (bronze) -[:CONTAINS]-> (crm)
CREATE (silver) -[:CONTAINS]-> (silver_cust)
CREATE (gold) -[:CONTAINS]-> (gold_fact)
CREATE (gold) -[:CONTAINS]-> (gold_cust_dim)

-- Data flow edges (transformations)
CREATE (vbak) -[:FLOWS_TO {via: 'dbt: stg_sap_vbak'}]-> (silver_cust)
CREATE (crm) -[:FLOWS_TO {via: 'dbt: stg_crm_accounts'}]-> (silver_cust)
CREATE (silver_cust) -[:FLOWS_TO {via: 'dbt: fct_daily_sales'}]-> (gold_fact)
CREATE (silver_cust) -[:FLOWS_TO {via: 'dbt: dim_customer'}]-> (gold_cust_dim)

  Lineage Queries

  Query 1: Trace Gold Table to Bronze Source
  MATCH path = (gold:Table {name: 'gold_fact_daily_sales'})
  <-[:FLOWS_TO*]-> (bronze:Table)
RETURN path

  Query 2: Impact of Schema Change in bronze_sap_vbak
  MATCH (bronze_table:Table {name: 'bronze_sap_vbak'})
  -[:FLOWS_TO*]-> (affected_gold:Table)
  -[:CONTAINS]<- (gold_layer:Layer {name: 'Gold'})
RETURN DISTINCT affected_gold.name AS affected_table

  Query 3: All Transformations Between Silver and Gold
  MATCH (silver_t:Table)
  -[flow:FLOWS_TO]-> (gold_t:Table)
  WHERE
    (silver_t) -[:CONTAINS]<- (sl:Layer {name: 'Silver'})
    AND (gold_t) -[:CONTAINS]<- (gl:Layer {name: 'Gold'})
RETURN silver_t.name, flow.via, gold_t.name

  Graph Visualization
  The Neo4j browser renders the lineage as an interactive graph. Colors represent layers: Bronze (amber), Silver (blue), Gold (yellow). Edge labels show dbt transformation names.

  
    One Cypher query discovers all downstream impact. A schema change in bronze_sap_vbak is tracked forward through transformations to all Gold tables, enabling safe change impact analysis. No blind spots.

Medallion Architecture& Relational-to-Graph Methodology