How Snowflake Cortex Helped Me Turn an STTM into a Governed Customer 360

Amit Singh — Founder, DE Copilot | Data Platform Architect and Lead Data Engineer

---

Most organizations do not struggle because they lack customer data. They struggle because customer information is spread across CRM, billing, digital engagement, support, policy, transaction, and marketing systems. Even when the data is centralized in Snowflake, business users still face a difficult question:

Which table should I trust, which customer key should I use, what does "active customer" mean, and how should I calculate customer value?

This is where a Source-to-Target Mapping can become more than a delivery document.

In this example, I use a synthetic Customer 360 scenario to show how a governed STTM and Canonical Metadata Model can help define a Snowflake semantic layer for Cortex Analyst.

Key Message: STTM is not only a mapping document. When normalized into governed metadata, it can become the foundation for a trusted semantic layer, Customer 360 model, data quality controls, lineage, and AI-assisted analytics.

All examples, tables, metrics, and architecture diagrams in this article are synthetic demonstration material. No employer, client, internal platform, or production data is used.

---

The Customer 360 Problem

Customer data is rarely in one place. A typical enterprise may hold customer information across:

• •CRM customer records
• •Policy or account records
• •Transactions
• •Product holdings
• •Digital activity
• •Service interactions
• •Marketing campaigns
• •Customer preferences

This distribution creates predictable problems:

• •Multiple customer IDs across systems with no single trusted key
• •Conflicting customer status definitions — "active" means different things in CRM, billing, and digital
• •Inconsistent household or account relationships
• •Metrics calculated differently by different teams
• •Business users relying on analysts for simple questions
• •AI tools producing SQL without understanding business definitions

The result is that even well-engineered Snowflake environments can leave business users unable to answer basic questions with confidence.

Architecture Overview

CRM / Billing / Transactions / Support / Digital Activity
        ↓
Source-to-Target Mapping
        ↓
Canonical Metadata Model
        ↓
Snowflake Customer 360 Tables
        ↓
Semantic View
        ↓
Cortex Analyst
        ↓
Governed Business Questions

---

Step 1: Start With the STTM

The STTM is the starting point. A well-structured mapping captures everything needed to build a governed Customer 360 layer:

• •Source system and source table
• •Source column and target column
• •Target table and datatype
• •Business definition
• •Transformation logic
• •Default logic
• •Join conditions
• •Data quality expectations
• •Business owner
• •Sensitivity classification

Synthetic STTM Example

This metadata should be reviewed and approved before any code generation begins.

The goal is not to ask AI to guess what Customer 360 means. The goal is to define the customer model, relationships, metrics, and assumptions first.

---

Step 2: Normalize Into a Canonical Metadata Model

DE Copilot converts the STTM into a Canonical Metadata Model — a normalized, structured representation of the business entities, relationships, and rules that govern the Customer 360 layer.

Conceptual Entities

• •Customer
• •Account
• •Policy or Product
• •Transaction
• •Digital Interaction
• •Service Case
• •Campaign Interaction
• •Household
• •Customer Segment
• •Customer Value Metric

What the Canonical Model Records

For each entity and attribute, the model captures:

• •Business keys and technical keys
• •Source-to-target lineage
• •Join relationships
• •Transformation logic
• •Metric definitions
• •Critical data-quality rules
• •Ownership
• •Approval status
• •Confidence level
• •Open assumptions

Canonical Model Flow

Business Requirements + STTM + Source Metadata
        ↓
Canonical Metadata Model
        ↓
Customer 360 Data Model + DQ Rules + Lineage + Semantic Definitions

This normalization step is what separates a governed Customer 360 implementation from a collection of ad hoc tables.

---

Step 3: Build a Snowflake Customer 360 Layer

With the canonical model in place, the Snowflake data model follows a predictable structure.

Synthetic Snowflake Model

DIM_CUSTOMER — Trusted customer identity, status, segment, geography, consent, and master attributes. This is the single source of truth for who a customer is. DIM_ACCOUNT — Account-level attributes, product associations, account status, and relationship tenure. DIM_PRODUCT — Product or policy definitions, product type, and product hierarchy. FACT_TRANSACTION — Transaction-level records with customer key, account key, transaction type, amount, and date. FACT_CUSTOMER_VALUE — Derived measures including total balance, lifetime value, transaction value, active products, and relationship tenure. FACT_CUSTOMER_SERVICE — Open service cases, complaint indicators, interaction counts, and resolution trends. FACT_DIGITAL_ENGAGEMENT — Recent login activity, engagement score, channel usage, and customer digital behavior.

Snowflake Semantic Views can define logical tables that represent business entities and relationships, rather than exposing raw physical tables directly to business users or AI tools. This is the layer that Cortex Analyst should be pointed at.

---

Step 4: Create a Governed Semantic View for Cortex Analyst

Cortex Analyst should not be connected directly to an uncontrolled collection of raw tables. The semantic view is the governance boundary between the physical data model and natural-language queries.

A well-designed semantic view contains:

• •Logical tables representing business entities
• •Customer relationships based on shared keys
• •Facts and dimensions
• •Approved metrics with clear definitions
• •Synonyms for common business terms
• •Business descriptions for tables and columns
• •Verified example questions
• •Instructions for SQL generation

Illustrative Semantic Layer Example

logical_tables:
  - name: customer
    description: Trusted enterprise customer profile used for Customer 360 reporting.
    primary_key:
      columns:
        - customer_key
  - name: customer_value
    description: Customer-level value and balance metrics.
    primary_key:
      columns:
        - customer_key

relationships:
  - name: customer_to_customer_value
    left_table: customer
    right_table: customer_value
    relationship_columns:
      - left_column: customer_key
        right_column: customer_key

metrics:
  - name: total_customer_balance
    description: Total current balance across active customer accounts.
    expression: SUM(customer_value.total_balance)
  - name: active_customer_count
    description: Count of customers with active status.
    expression: >
      COUNT(DISTINCT CASE WHEN customer.customer_status = 'Active'
      THEN customer.customer_key END)

Note: This is illustrative YAML, not a production-ready complete semantic-view definition. The actual implementation should reflect approved table names, roles, data-access rules, and business definitions.

Snowflake supports creating and managing semantic views in Snowsight. Semantic views can include descriptions, synonyms, example questions, verified queries, and custom instructions that guide Cortex Analyst toward accurate, governed responses.

---

Step 5: Ask Governed Customer 360 Questions

From SQL Requests to Business Questions

With a governed semantic view in place, business users can ask natural-language questions through Cortex Analyst without needing to understand physical schemas:

• •How many active customers do we have by state and segment?
• •Which customers had no digital activity in the last 90 days?
• •What is the total balance for customers with open service cases?
• •Which customer segment generated the highest transaction value this quarter?
• •How many customers have both an active account and an unresolved complaint?
• •Which customers have declining engagement but high account value?

Cortex Analyst generates SQL from these questions using the semantic view, translating business intent into governed queries against approved definitions.

Governance Warning: Cortex Analyst does not automatically make a Customer 360 implementation correct. It can generate better SQL when the semantic view is well-designed, but the underlying business definitions, relationships, metric logic, data quality rules, and access controls must still be governed by data owners and engineers.

---

Step 6: Use Cortex for Metadata and Documentation Assistance

Snowflake Cortex AI Functions can support documentation-oriented use cases beyond natural-language querying. These include summarizing transformation logic, classifying metadata, extracting information from documents, and assisting with data-engineering workflows. Snowflake provides Cortex AI capabilities through SQL functions and Python interfaces.

Practical DE Copilot Examples

• •Summarize complex transformation logic from legacy ETL documentation
• •Classify source columns by business domain
• •Generate first-draft descriptions for tables and columns
• •Detect potentially sensitive customer attributes for review
• •Produce mapping-review questions for ambiguous transformations
• •Summarize data-quality failures for an engineer review queue
• •Draft a technical handoff summary from approved delivery artifacts

Important: AI-generated descriptions, classifications, and recommendations should remain reviewable artifacts. They should not become authoritative business metadata until approved by a data owner or governance team.

---

Step 7: Add the Governance Layer

The Difference Between a Demo and a Trusted Customer 360 Copilot

The difference is not the AI capability. The difference is the governance foundation underneath it.

The DE Copilot Pattern

STTM / Business Requirements
        ↓
Canonical Metadata Model
        ↓
Snowflake Data Model
        ↓
Semantic View
        ↓
Cortex Analyst
        ↓
Validation and Review
        ↓
Approved Business Insight

---

What DE Copilot Adds to This Pattern

DE Copilot can help bridge the gap between implementation metadata and a usable Snowflake semantic layer. Possible future capabilities include:

• •Generate candidate semantic-view definitions from approved STTM metadata
• •Suggest logical tables, relationships, dimensions, facts, and metrics
• •Identify undocumented joins or ambiguous business definitions
• •Generate test questions for Customer 360 metrics
• •Produce lineage from source attribute to semantic metric
• •Flag metrics with missing DQ coverage
• •Route semantic-model changes through review and approval
• •Maintain a decision register for metric definitions and Customer 360 rules
• •Generate a delivery packet containing SQL, semantic definitions, tests, lineage, and approval evidence

Practical Caution: The semantic model should be generated as a starting point, then reviewed by data engineers, business SMEs, analytics owners, and governance teams before it is used to answer business questions.

---

Final Takeaway

Snowflake Cortex can make Customer 360 data easier to explore through natural-language questions. But the real value does not come from putting AI on top of raw tables.

The value comes from building a governed foundation first:

• •Clear customer definitions
• •Trusted keys and relationships
• •Approved metrics
• •Lineage
• •Data-quality controls
• •Access policies
• •Semantic context
• •Human review

A well-maintained STTM and Canonical Metadata Model can become the bridge between delivery engineering and governed AI analytics.

That is the opportunity for DE Copilot: helping teams move from fragmented mapping documents and legacy metadata to a trusted, reviewable Customer 360 intelligence layer.

---

About DE Copilot

DE Copilot is a metadata intelligence platform focused on transforming business requirements, source-to-target mappings, spreadsheets, and legacy ETL metadata into validated, reviewable engineering deliverables.

The platform supports governed workflows for Snowflake-ready SQL, DDL, data-quality rules, reconciliation controls, documentation, lineage, migration-risk findings, approval workflows, and delivery packets.