How to Migrate from Looker (LookML Semantic Layer) to Snowflake Semantic Views (Snowflake)

Migrating from LookML to Snowflake Semantic Views consolidates metric definitions into the database as native schema objects. This technical guide maps LookML views, explores, and measures to Snowflake's logical tables, relationships, dimensions, and metrics. Organizations consolidate semantic logic directly into the database and gain native Cortex AI integration.

---

Executive Summary: Key Technical Mappings

Core Translation Requirements

LookML to Snowflake Component Mapping:

LookML Construct	Snowflake Semantic View Equivalent	Translation Complexity
View (table-based)	Logical table with PRIMARY KEY	Direct 1:1 mapping
View (derived table)	Materialized view or table	Requires pre-computation
Dimension (categorical)	Dimension	Direct mapping
Dimension (numeric)	Fact	Type reclassification required
Measure	Metric	Aggregation syntax translation
Explore + joins	Relationships clause	Explicit path definition needed
Model file	Database schema grouping	Organizational boundary

Technical Capabilities Comparison

Feature	LookML	Snowflake Semantic Views
Definition Location	External YAML files	Schema-level database objects
Query Interface	Looker Explore UI / SQL	SEMANTIC_VIEW() SQL clause
Join Resolution	Automatic multi-hop	Explicit relationship paths
Primary Key Requirements	Optional	Mandatory for relationships
Derived Tables	Inline SQL or persistent	Must materialize first
Liquid Templating	Supported	Not supported
Row-Level Security	User attributes	Snowflake RLS policies
Cortex AI Integration	External	Native

Performance Characteristics

Both systems generate SQL executed by Snowflake's query engine. Performance differences stem from architectural positioning:

LookML: Adds network hop through Looker API, generates SQL remotely, returns results to Looker before downstream tools.

Snowflake Semantic Views: Execute natively within Snowflake, zero middleware latency, direct result streaming to query clients.

Measured Impact: This architectural consolidation removes the need for an external semantic layer and centralizes metric logic within Snowflake.

---

Architectural Translation Foundations

LookML Structure

LookML organizes semantic definitions across three file types:

View Files (.view.lkml):

view: orders {
  sql_table_name: raw.orders ;;

  dimension: order_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.order_id ;;
  }

  dimension: customer_id {
    type: number
    sql: ${TABLE}.customer_id ;;
  }

  measure: total_revenue {
    type: sum
    sql: ${TABLE}.amount ;;
  }
}

Model Files (.model.lkml):

connection: snowflake_prod

include: "*.view.lkml"

explore: orders {
  join: customers {
    sql_on: ${orders.customer_id} = ${customers.id} ;;
    relationship: many_to_one
  }
}

Explores: Define queryable data combinations with join paths between views.

Snowflake Semantic Views Structure

Snowflake consolidates these into single schema-level objects:

sql
CREATE SEMANTIC VIEW orders_analysis
TABLES (
  orders PRIMARY KEY (order_id),
  customers PRIMARY KEY (customer_id)
)
RELATIONSHIPS (
  orders.customer_id REFERENCES customers.customer_id
)
FACTS (
  orders.amount AS order_amount
)
DIMENSIONS (
  customers.region AS customer_region,
  orders.order_date AS order_date
)
METRICS (
  orders.total_revenue AS SUM(orders.amount),
  orders.order_count AS COUNT(orders.order_id)
);

Key Architectural Differences

LookML Approach:

• Metric logic stored in Git repositories
• SQL generation happens in Looker application layer
• Explores define queryable scope
• Multi-hop joins resolved automatically within explore context

Snowflake Semantic Views Approach:

• Metric logic stored as database objects
• SQL generation happens in Snowflake query planner
• Relationships define all join paths explicitly
• Multi-hop joins require explicit relationship chains

---

Pre-Migration Technical Assessment

Cataloging LookML Components

Extract metadata from existing LookML codebase:

Model inventory:

bash
# Count models and their explores
find . -name "*.model.lkml" -exec grep -c "explore:" {} \;

View classification:

bash
# Identify base vs derived views
grep -r "sql_table_name:" *.view.lkml  # Base views
grep -r "derived_table:" *.view.lkml    # Derived views

Measure complexity analysis:

bash
# Count measure types
grep "type: sum" *.view.lkml | wc -l
grep "type: count" *.view.lkml | wc -l
grep "type: number" *.view.lkml | wc -l  # Calculated measures

Identify Translation Barriers

Simple translations (minimal modification required):

• Views mapping to single tables
• Dimensions with direct column references
• Measures using standard aggregations (sum, count, average)
• Explores with 2-3 table joins

Moderate translations (requires restructuring):

• Derived tables with complex SQL
• Measures using window functions
• Explores with 4-6 table joins
• Calculated dimensions with CASE statements

Complex translations (custom solutions required):

• Persistent derived tables with incremental logic
• Liquid templating for dynamic SQL
• Symmetric aggregates with fan-out prevention
• User attribute-based row filtering
• Period-over-period calculations

Not supported (alternative implementation paths):

• Drill fields with dynamic selection
• Dashboard-level filters as default explore filters
• Cross-database joins (requires Snowflake federation)

---

Technical Migration Process

Step 1: Establish Snowflake Data Foundation

Semantic views require explicit metadata not mandatory in LookML:

Add primary key constraints:

sql
-- Identify tables without primary keys
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'MY_SCHEMA'
  AND table_name NOT IN (
    SELECT table_name
    FROM information_schema.table_constraints
    WHERE constraint_type = 'PRIMARY KEY'
  );

-- Add constraints
ALTER TABLE customers ADD PRIMARY KEY (customer_id);
ALTER TABLE products ADD PRIMARY KEY (product_id);
ALTER TABLE orders ADD PRIMARY KEY (order_id);

Validate referential integrity:

sql
-- Check foreign key validity
SELECT COUNT(*) as orphaned_records
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Materialize complex derived tables:

sql
-- LookML derived table → Snowflake materialized view
CREATE MATERIALIZED VIEW order_facts AS
SELECT
  order_id,
  COUNT(*) as line_item_count,
  SUM(quantity) as total_quantity,
  SUM(amount) as total_amount
FROM order_items
GROUP BY order_id;

Step 2: Translate Views to Logical Tables

Map each LookML view to logical table definition:

Base view translation:

sql
-- LookML: orders.view.lkml
view: orders {
  sql_table_name: raw.orders ;;
}

-- Snowflake semantic view
TABLES (
  orders PRIMARY KEY (order_id)
    SOURCE my_database.my_schema.orders
)

Derived table translation:

sql
-- LookML derived table
view: customer_metrics {
  derived_table: {
    sql: SELECT
           customer_id,
           COUNT(*) as order_count,
           SUM(amount) as lifetime_value
         FROM orders
         GROUP BY customer_id ;;
  }
}

-- Step 1: Create Snowflake view
CREATE VIEW customer_metrics AS
SELECT
  customer_id,
  COUNT(*) as order_count,
  SUM(amount) as lifetime_value
FROM orders
GROUP BY customer_id;

-- Step 2: Reference in semantic view
TABLES (
  customer_metrics PRIMARY KEY (customer_id)
    SOURCE my_database.my_schema.customer_metrics
)

Step 3: Map Explore Joins to Relationships

Translate join logic from explore definitions:

Simple join translation:

sql
-- LookML explore
explore: orders {
  join: customers {
    sql_on: ${orders.customer_id} = ${customers.id} ;;
    relationship: many_to_one
  }
}

-- Snowflake relationship
RELATIONSHIPS (
  orders.customer_id REFERENCES customers.customer_id
)

Multi-hop join translation:

sql
-- LookML (automatic chaining)
explore: orders {
  join: customers {
    sql_on: ${orders.customer_id} = ${customers.id} ;;
  }
  join: regions {
    sql_on: ${customers.region_id} = ${regions.id} ;;
  }
}

-- Snowflake (explicit chain)
RELATIONSHIPS (
  orders.customer_id REFERENCES customers.customer_id,
  customers.region_id REFERENCES regions.region_id
)

Fan-out prevention:

LookML uses relationship: one_to_one to prevent measure inflation. Snowflake handles this through proper key constraints:

sql
-- Ensure unique constraints exist for one-to-one relationships
ALTER TABLE user_profiles ADD UNIQUE (user_id);

RELATIONSHIPS (
  users.user_id REFERENCES user_profiles.user_id  -- Unique constraint prevents fan-out
)

Step 4: Translate Dimensions

Map LookML dimensions to Snowflake dimensions or facts:

Categorical dimensions:

sql
-- LookML
dimension: customer_region {
  type: string
  sql: ${customers.region} ;;
}

-- Snowflake
DIMENSIONS (
  customers.region AS customer_region
)

Numeric dimensions (become facts):

sql
-- LookML
dimension: order_amount {
  type: number
  sql: ${TABLE}.amount ;;
}

-- Snowflake (numeric values are facts, not dimensions)
FACTS (
  orders.amount AS order_amount
)

Calculated dimensions:

sql
-- LookML
dimension: customer_tier {
  type: string
  sql: CASE
         WHEN ${lifetime_value} > 10000 THEN 'VIP'
         WHEN ${lifetime_value} > 1000 THEN 'Standard'
         ELSE 'Basic'
       END ;;
}

-- Snowflake
DIMENSIONS (
  customers.customer_tier AS
    CASE
      WHEN customers.lifetime_value > 10000 THEN 'VIP'
      WHEN customers.lifetime_value > 1000 THEN 'Standard'
      ELSE 'Basic'
    END
)

Time dimensions:

sql
-- LookML dimension group
dimension_group: created {
  type: time
  timeframes: [date, week, month, year]
  sql: ${TABLE}.created_at ;;
}

-- Snowflake (define each grain separately or use DATE_TRUNC in queries)
DIMENSIONS (
  DATE(orders.created_at) AS order_date,
  DATE_TRUNC('week', orders.created_at) AS order_week,
  DATE_TRUNC('month', orders.created_at) AS order_month
)

Step 5: Translate Measures to Metrics

Convert aggregation logic from LookML measures:

Simple aggregations:

sql
-- LookML measures
measure: order_count {
  type: count
}

measure: total_revenue {
  type: sum
  sql: ${amount} ;;
}

measure: average_order_value {
  type: average
  sql: ${amount} ;;
}

-- Snowflake metrics
METRICS (
  orders.order_count AS COUNT(orders.order_id),
  orders.total_revenue AS SUM(orders.amount),
  orders.average_order_value AS AVG(orders.amount)
)

Distinct count measures:

sql
-- LookML
measure: unique_customers {
  type: count_distinct
  sql: ${customer_id} ;;
}

-- Snowflake
METRICS (
  orders.unique_customers AS COUNT(DISTINCT orders.customer_id)
)

Calculated measures:

sql
-- LookML
measure: net_revenue {
  type: number
  sql: ${total_revenue} - ${total_discounts} ;;
}

-- Snowflake derived metric (unqualified by table)
METRICS (
  orders.total_revenue AS SUM(orders.amount),
  orders.total_discounts AS SUM(orders.discount),
  net_revenue AS orders.total_revenue - orders.total_discounts
)

Ratio measures (correct aggregation):

sql
-- LookML
measure: revenue_per_customer {
  type: number
  sql: ${total_revenue} / NULLIF(${customer_count}, 0) ;;
}

-- Snowflake
METRICS (
  revenue_per_customer AS
    SUM(orders.amount) / NULLIF(COUNT(DISTINCT orders.customer_id), 0)
)

Step 6: Handle Persistent Derived Tables (PDTs)

LookML PDTs require translation to Snowflake materialization strategies:

Non-incremental PDTs:

sql
-- LookML
view: daily_metrics {
  derived_table: {
    sql: SELECT
           DATE(order_date) as day,
           COUNT(*) as orders,
           SUM(amount) as revenue
         FROM orders
         GROUP BY 1 ;;
    sql_trigger_value: SELECT CURRENT_DATE ;;
  }
}

-- Snowflake materialized view
CREATE MATERIALIZED VIEW daily_metrics AS
SELECT
  DATE(order_date) as day,
  COUNT(*) as orders,
  SUM(amount) as revenue
FROM orders
GROUP BY DATE(order_date);

-- Refresh strategy
CREATE TASK refresh_daily_metrics
  WAREHOUSE = compute_wh
  SCHEDULE = 'USING CRON 0 1 * * * UTC'
AS
  ALTER MATERIALIZED VIEW daily_metrics REFRESH;

Incremental PDTs:

sql
-- LookML incremental logic
view: order_facts {
  derived_table: {
    sql: SELECT * FROM order_facts_staging ;;
    sql_trigger_value: SELECT MAX(updated_at) FROM orders ;;
    increment_key: "order_id"
    increment_offset: 3
  }
}

-- Snowflake streams and tasks
CREATE STREAM order_stream ON TABLE raw_orders;

CREATE TABLE order_facts (
  order_id NUMBER,
  item_count NUMBER,
  total_amount NUMBER,
  updated_at TIMESTAMP
);

CREATE TASK incremental_update_order_facts
  WAREHOUSE = compute_wh
  SCHEDULE = '15 MINUTE'
WHEN
  SYSTEM$STREAM_HAS_DATA('order_stream')
AS
  MERGE INTO order_facts t
  USING (
    SELECT
      order_id,
      COUNT(*) as item_count,
      SUM(amount) as total_amount,
      MAX(updated_at) as updated_at
    FROM order_stream
    GROUP BY order_id
  ) s
  ON t.order_id = s.order_id
  WHEN MATCHED THEN UPDATE SET
    item_count = s.item_count,
    total_amount = s.total_amount,
    updated_at = s.updated_at
  WHEN NOT MATCHED THEN INSERT VALUES
    (s.order_id, s.item_count, s.total_amount, s.updated_at);

Step 7: Translate Access Controls

LookML user attributes for row-level security require Snowflake RLS policies:

LookML user attribute filtering:

view: orders {
  sql_table_name: raw.orders ;;

  dimension: region {
    sql: ${TABLE}.region ;;
  }

  sql_always_where: ${region} = '{{ _user_attributes["region"] }}' ;;
}

Snowflake row access policy:

sql
-- Create mapping table for user regions
CREATE TABLE user_region_mapping (
  user_name VARCHAR,
  allowed_region VARCHAR
);

-- Define row access policy
CREATE ROW ACCESS POLICY region_filter
  AS (region VARCHAR) RETURNS BOOLEAN ->
    region IN (
      SELECT allowed_region
      FROM user_region_mapping
      WHERE user_name = CURRENT_USER()
    )
    OR IS_ROLE_IN_SESSION('ADMIN_ROLE');

-- Apply to table
ALTER TABLE orders
  ADD ROW ACCESS POLICY region_filter ON (region);

-- Queries automatically respect policy
SELECT * FROM SEMANTIC_VIEW(
  orders_analysis
  METRICS total_revenue
  DIMENSIONS customer_region
);  -- Only returns user's permitted regions

Step 8: Implement Semantic View

Combine translated components into complete semantic view definition:

sql
CREATE SEMANTIC VIEW orders_analysis
TABLES (
  orders PRIMARY KEY (order_id),
  customers PRIMARY KEY (customer_id),
  products PRIMARY KEY (product_id),
  order_facts PRIMARY KEY (order_id)
)
RELATIONSHIPS (
  orders.customer_id REFERENCES customers.customer_id,
  orders.product_id REFERENCES products.product_id,
  orders.order_id REFERENCES order_facts.order_id
)
FACTS (
  orders.amount AS order_amount,
  orders.quantity AS order_quantity,
  order_facts.item_count AS items_per_order
)
DIMENSIONS (
  customers.region AS customer_region,
  customers.tier AS customer_tier,
  products.category AS product_category,
  DATE(orders.order_timestamp) AS order_date,
  DATE_TRUNC('month', orders.order_timestamp) AS order_month
)
METRICS (
  orders.order_count AS COUNT(orders.order_id),
  orders.total_revenue AS SUM(orders.amount),
  orders.average_order_value AS SUM(orders.amount) / COUNT(orders.order_id),
  orders.unique_customers AS COUNT(DISTINCT orders.customer_id),
  revenue_per_customer AS orders.total_revenue / orders.unique_customers
)
WITH COMMENT = 'Migrated from LookML orders explore. Owner: data_team@company.com';

Validate compilation:

sql
-- Verify semantic view created successfully
SHOW SEMANTIC VIEWS IN SCHEMA my_schema;

-- List available metrics
SHOW SEMANTIC METRICS IN orders_analysis;

-- List available dimensions
SHOW SEMANTIC DIMENSIONS IN orders_analysis;

Step 9: Query Semantic View

Execute queries using SEMANTIC_VIEW() SQL clause:

Basic query:

sql
SELECT * FROM SEMANTIC_VIEW(
  orders_analysis
  METRICS order_count, total_revenue
  DIMENSIONS customer_region
)
ORDER BY total_revenue DESC;

Filtered query:

sql
SELECT * FROM SEMANTIC_VIEW(
  orders_analysis
  METRICS order_count, total_revenue, average_order_value
  DIMENSIONS customer_region, product_category
)
WHERE order_date >= CURRENT_DATE - 30
  AND customer_tier = 'VIP';

Join with other tables:

sql
-- Query semantic view and join results with external data
WITH semantic_results AS (
  SELECT * FROM SEMANTIC_VIEW(
    orders_analysis
    METRICS total_revenue, unique_customers
    DIMENSIONS customer_region
  )
)
SELECT
  sr.customer_region,
  sr.total_revenue,
  sr.unique_customers,
  rt.target_revenue,
  sr.total_revenue / rt.target_revenue as attainment_pct
FROM semantic_results sr
JOIN revenue_targets rt ON sr.customer_region = rt.region;

---

Automated Validation with Fenic

Schema-Driven Metric Validation

Use Fenic's semantic operators for automated reconciliation:

python
import fenic as fc
from pydantic import BaseModel
from typing import Literal

class MetricComparison(BaseModel):
    dimension: str
    lookml_value: float
    snowflake_value: float
    variance_absolute: float
    variance_percent: float
    validation_status: Literal["pass", "fail", "review"]

# Configure Fenic session
config = fc.SessionConfig(
    app_name="looker_migration_validation",
    semantic=fc.SemanticConfig(
        language_models={
            "gpt4": fc.OpenAILanguageModel(
                model_name="gpt-4",
                rpm=100,
                tpm=100
            )
        }
    )
)
session = fc.Session.get_or_create(config)

# Load baseline data from LookML exports
lookml_baseline = fc.read_csv("lookml_baseline_metrics.csv")

# Note: This section requires actual Snowflake integration through your data pipeline
# Fenic is used for data processing and validation, not as a SQL execution engine
# You would need to:
# 1. Execute the Snowflake query using your Snowflake connector
# 2. Export results to CSV/Parquet
# 3. Load into Fenic for validation processing

# Load pre-exported Snowflake results
semantic_view_results = fc.read_csv("snowflake_semantic_view_results.csv")

# Join and calculate variances
comparison = (
    lookml_baseline
    .join(
        semantic_view_results,
        on=fc.col("region") == fc.col("dimension"),
        how="full_outer"
    )
    .with_column("variance_absolute",
        fc.col("snowflake_value") - fc.col("lookml_value")
    )
    .with_column("variance_percent",
        (fc.col("variance_absolute") / fc.col("lookml_value")) * 100
    )
    .with_column("variance_percent_abs",
        fc.when(fc.col("variance_percent") >= 0, fc.col("variance_percent"))
        .otherwise(fc.col("variance_percent") * -1)
    )
    .with_column("validation_status",
        fc.when(fc.col("variance_percent_abs") < 0.01, "pass")
        .when(fc.col("variance_percent_abs") < 1.0, "review")
        .otherwise("fail")
    )
)

# Identify failures
failures = comparison.filter(fc.col("validation_status") == "fail")
if failures.count() > 0:
    print(f"Validation failures: {failures.count()} metrics")
    failures.write_csv("validation_failures.csv")

Classification-Based Metric Categorization

Organize migrated metrics using semantic.classify:

python
import fenic as fc
from fenic.core.types.classify import ClassDefinition

# Define business domains
domain_classes = [
    ClassDefinition(
        label="Finance",
        description="Revenue, cost, margin, and financial performance metrics"
    ),
    ClassDefinition(
        label="Sales",
        description="Order volume, conversion, pipeline, and sales activity metrics"
    ),
    ClassDefinition(
        label="Marketing",
        description="Campaign performance, acquisition, and engagement metrics"
    ),
    ClassDefinition(
        label="Operations",
        description="Fulfillment, inventory, and operational efficiency metrics"
    )
]

# Classify metrics by domain
categorized_metrics = metrics_df.with_column(
    "domain",
    fc.semantic.classify(
        fc.col("metric_name"),
        domain_classes
    )
)

# Group metrics by domain for phased migration
finance_metrics = categorized_metrics.filter(
    fc.col("domain") == "Finance"
)
sales_metrics = categorized_metrics.filter(
    fc.col("domain") == "Sales"
)

# Generate migration documentation
categorized_metrics.write_parquet("metrics_by_domain.parquet")

Semantic Join Validation

Validate relationship translations using semantic.join:

python
# Load LookML join definitions
lookml_joins = fc.read_json("lookml_join_definitions.json")

# Load Snowflake relationship definitions
snowflake_relationships = session.sql("""
    SELECT * FROM INFORMATION_SCHEMA.SEMANTIC_VIEW_RELATIONSHIPS
    WHERE semantic_view_name = 'ORDERS_ANALYSIS'
""")

# Use semantic join to identify missing translations
validation_prompt = """
Do these join definitions match?
LookML: {join_definition:left}
Snowflake: {relationship_definition:right}
"""

join_validation = lookml_joins.semantic.join(
    other=snowflake_relationships,
    predicate=validation_prompt
)

# Report untranslated joins
missing_joins = lookml_joins.join(
    join_validation,
    on="join_id",
    how="left_anti"
)

if missing_joins.count() > 0:
    print(f"Untranslated joins: {missing_joins.count()}")
    missing_joins.show()

---

Advanced Translation Patterns

Handling Liquid Templating

LookML's Liquid templating generates dynamic SQL. Snowflake semantic views don't support templating. Translation options:

Option 1: Pre-compute variations

sql
-- LookML with Liquid
measure: filtered_revenue {
  type: sum
  sql: ${amount} ;;
  filters: [
    date: "{% if _filters['timeframe'] == 'mtd' %}
           {% date_start date %} to {% date_end date %}
           {% endif %}"
  ]
}

-- Snowflake: Create separate metrics per variation
METRICS (
  orders.revenue_mtd AS
    SUM(CASE WHEN order_date >= DATE_TRUNC('month', CURRENT_DATE)
        THEN orders.amount ELSE 0 END),
  orders.revenue_qtd AS
    SUM(CASE WHEN order_date >= DATE_TRUNC('quarter', CURRENT_DATE)
        THEN orders.amount ELSE 0 END),
  orders.revenue_ytd AS
    SUM(CASE WHEN order_date >= DATE_TRUNC('year', CURRENT_DATE)
        THEN orders.amount ELSE 0 END)
)

Option 2: Move logic to application layer

python
# Generate query dynamically based on parameters
def query_revenue(timeframe: str):
    date_filter = {
        'mtd': "DATE_TRUNC('month', CURRENT_DATE)",
        'qtd': "DATE_TRUNC('quarter', CURRENT_DATE)",
        'ytd': "DATE_TRUNC('year', CURRENT_DATE)"
    }[timeframe]

    return f"""
        SELECT * FROM SEMANTIC_VIEW(
          orders_analysis
          METRICS total_revenue
          DIMENSIONS customer_region
        )
        WHERE order_date >= {date_filter}
    """

Translating Period-Over-Period Calculations

LookML supports offset functions. Snowflake requires explicit window functions or self-joins:

LookML period-over-period:

measure: revenue_last_month {
  type: sum
  sql: ${amount} ;;
  filters: [date: "1 month ago for 1 month"]
}

measure: revenue_growth {
  type: number
  sql: (${revenue_this_month} - ${revenue_last_month}) / ${revenue_last_month} ;;
}

Snowflake implementation:

sql
-- Create base metric query
WITH monthly_revenue AS (
  SELECT * FROM SEMANTIC_VIEW(
    orders_analysis
    METRICS total_revenue
    DIMENSIONS order_month
  )
)
-- Calculate period-over-period
SELECT
  order_month,
  total_revenue as current_month_revenue,
  LAG(total_revenue, 1) OVER (ORDER BY order_month) as prior_month_revenue,
  (total_revenue - LAG(total_revenue, 1) OVER (ORDER BY order_month))
    / LAG(total_revenue, 1) OVER (ORDER BY order_month) as growth_rate
FROM monthly_revenue;

Handling Symmetric Aggregates

LookML's symmetric_aggregates: yes prevents double-counting in joins. Snowflake handles this through proper key constraints:

LookML approach:

view: order_items {
  measure: total_items {
    type: count
  }
}

explore: orders {
  symmetric_aggregates: yes

  join: order_items {
    relationship: one_to_many
  }
}

Snowflake approach:

sql
-- Ensure proper constraints exist
ALTER TABLE orders ADD PRIMARY KEY (order_id);
ALTER TABLE order_items ADD FOREIGN KEY (order_id) REFERENCES orders(order_id);

-- Define relationship correctly
RELATIONSHIPS (
  order_items.order_id REFERENCES orders.order_id
)

-- Snowflake prevents fan-out through key constraints
METRICS (
  orders.order_count AS COUNT(orders.order_id),  -- Correct count
  order_items.item_count AS COUNT(order_items.item_id)  -- Also correct
)

Migrating Complex Explores with Many Joins

Explores with 5+ table joins may require denormalization:

Option 1: Create wide denormalized table

sql
CREATE TABLE orders_wide AS
SELECT
  o.order_id,
  o.amount,
  o.order_date,
  c.customer_id,
  c.region as customer_region,
  c.tier as customer_tier,
  p.product_id,
  p.category as product_category,
  p.brand as product_brand,
  s.store_id,
  s.store_name,
  s.region as store_region
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
JOIN stores s ON o.store_id = s.store_id;

-- Create simple semantic view
CREATE SEMANTIC VIEW orders_analysis_wide
TABLES (
  orders_wide PRIMARY KEY (order_id)
)
FACTS (
  orders_wide.amount AS order_amount
)
DIMENSIONS (
  orders_wide.customer_region,
  orders_wide.product_category,
  orders_wide.store_region,
  orders_wide.order_date
)
METRICS (
  orders_wide.total_revenue AS SUM(orders_wide.amount)
);

Option 2: Domain-specific semantic views

sql
-- Instead of one massive semantic view, create focused views

-- Sales domain view
CREATE SEMANTIC VIEW sales_analysis
TABLES (orders, customers)
RELATIONSHIPS (orders.customer_id REFERENCES customers.customer_id)
...;

-- Product domain view
CREATE SEMANTIC VIEW product_analysis
TABLES (orders, products, categories)
RELATIONSHIPS (
  orders.product_id REFERENCES products.product_id,
  products.category_id REFERENCES categories.category_id
)
...;

-- Operations domain view
CREATE SEMANTIC VIEW operations_analysis
TABLES (orders, stores, regions)
RELATIONSHIPS (
  orders.store_id REFERENCES stores.store_id,
  stores.region_id REFERENCES regions.region_id
)
...;

---

Performance Optimization

Query Execution Analysis

Monitor semantic view query patterns:

sql
-- Identify expensive semantic view queries
SELECT
  query_text,
  total_elapsed_time,
  rows_produced,
  bytes_scanned,
  credits_used_cloud_services
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_text LIKE '%SEMANTIC_VIEW%orders_analysis%'
  AND start_time >= DATEADD(day, -7, CURRENT_DATE)
ORDER BY total_elapsed_time DESC
LIMIT 50;

Clustering Optimization

Add clustering keys to tables frequently filtered in semantic view queries:

sql
-- Analyze filter patterns
SELECT
  query_text,
  COUNT(*) as query_count
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_text LIKE '%WHERE%customer_region%'
GROUP BY query_text
ORDER BY query_count DESC;

-- Add clustering on high-cardinality filtered columns
ALTER TABLE orders
  CLUSTER BY (customer_region, order_date);

ALTER TABLE customers
  CLUSTER BY (region, tier);

Search Optimization Service

Enable for columns frequently used in equality filters:

sql
-- Identify columns in WHERE clauses
SELECT
  REGEXP_SUBSTR(query_text, 'WHERE.*?([a-z_]+)\\s*=', 1, 1, 'i', 1) as filtered_column,
  COUNT(*) as usage_count
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_text LIKE '%SEMANTIC_VIEW%'
GROUP BY filtered_column
ORDER BY usage_count DESC;

-- Enable search optimization
ALTER TABLE customers
  ADD SEARCH OPTIMIZATION ON (customer_id, region);

ALTER TABLE orders
  ADD SEARCH OPTIMIZATION ON (order_id, customer_id, product_id);

Materialized View Strategies

Pre-compute expensive metric combinations:

sql
-- Identify frequently queried metric combinations
SELECT
  query_text,
  COUNT(*) as query_count
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_text LIKE '%SEMANTIC_VIEW%'
  AND query_text LIKE '%METRICS%'
GROUP BY query_text
ORDER BY query_count DESC;

-- Create materialized view for common pattern
CREATE MATERIALIZED VIEW daily_revenue_by_region AS
SELECT * FROM SEMANTIC_VIEW(
  orders_analysis
  METRICS total_revenue, order_count, average_order_value
  DIMENSIONS customer_region, order_date
);

-- Query materialized view directly for better performance
SELECT * FROM daily_revenue_by_region
WHERE order_date >= CURRENT_DATE - 30;

---

Cortex AI Integration

Sample Values for LLM Context

Add representative sample values to improve Cortex Analyst accuracy:

sql
CREATE OR REPLACE SEMANTIC VIEW orders_analysis
TABLES (
  orders PRIMARY KEY (order_id),
  customers PRIMARY KEY (customer_id)
)
RELATIONSHIPS (
  orders.customer_id REFERENCES customers.customer_id
)
DIMENSIONS (
  customers.region AS customer_region,
  orders.order_date AS order_date
)
METRICS (
  orders.total_revenue AS SUM(orders.amount)
)
WITH EXTENSION (
  CA = '{
    "tables": [{
      "name": "orders",
      "dimensions": [{
        "name": "customer_region",
        "sample_values": ["North America", "Europe", "Asia Pacific", "Latin America"]
      }, {
        "name": "order_date",
        "sample_values": ["2025-01-15", "2025-02-20", "2025-03-10"]
      }],
      "metrics": [{
        "name": "total_revenue",
        "sample_values": ["125000.50", "98000.25", "156000.75"]
      }]
    }]
  }'
);

Verified Query Patterns

Provide example questions and SQL to guide Cortex Analyst:

sql
WITH EXTENSION (
  CA = '{
    "verified_queries": [
      {
        "question": "What was total revenue by region last quarter?",
        "sql": "SELECT * FROM SEMANTIC_VIEW(orders_analysis METRICS total_revenue DIMENSIONS customer_region) WHERE order_date >= DATEADD(quarter, -1, CURRENT_DATE) AND order_date < CURRENT_DATE"
      },
      {
        "question": "Show me top 10 customers by revenue",
        "sql": "SELECT * FROM SEMANTIC_VIEW(orders_analysis METRICS total_revenue DIMENSIONS customer_id) ORDER BY total_revenue DESC LIMIT 10"
      }
    ]
  }'
)

Query Semantic Views with Natural Language

Enable Cortex Analyst queries:

sql
-- Natural language query through Cortex Analyst
SELECT SNOWFLAKE.CORTEX.COMPLETE(
  'claude-3-5-sonnet-v2',
  'Using the orders_analysis semantic view, what was revenue by region last month?'
) as analyst_response;

---

BI Tool Integration

Tableau Integration

Connect Tableau to Snowflake and use custom SQL with SEMANTIC_VIEW() clause:

Create Tableau data source:

1. Connect to Snowflake
2. Select "Custom SQL" option
3. Enter semantic view query:

sql
SELECT * FROM SEMANTIC_VIEW(
  orders_analysis
  METRICS <Parameters.Metrics>
  DIMENSIONS <Parameters.Dimensions>
)
WHERE <Filter Conditions>

Configure parameters for dynamic metrics:

• Create parameter for metric selection
• Create calculated field referencing parameter
• Use parameter in custom SQL

Power BI Integration

Connect via Snowflake ODBC driver:

sql
-- Power BI custom query
SELECT * FROM SEMANTIC_VIEW(
  orders_analysis
  METRICS total_revenue, order_count
  DIMENSIONS customer_region, product_category, order_date
)
WHERE order_date >= DATEADD(month, -3, CURRENT_DATE)

Mode Integration

Reference semantic views in Mode SQL editor:

sql
-- Mode SQL query
WITH semantic_data AS (
  SELECT * FROM SEMANTIC_VIEW(
    orders_analysis
    METRICS total_revenue, order_count, average_order_value
    DIMENSIONS customer_region, order_month
  )
)
SELECT
  customer_region,
  order_month,
  total_revenue,
  LAG(total_revenue) OVER (
    PARTITION BY customer_region
    ORDER BY order_month
  ) as prior_month_revenue,
  (total_revenue - LAG(total_revenue) OVER (
    PARTITION BY customer_region
    ORDER BY order_month
  )) / LAG(total_revenue) OVER (
    PARTITION BY customer_region
    ORDER BY order_month
  ) * 100 as growth_rate_pct
FROM semantic_data
ORDER BY customer_region, order_month;

---

Governance Implementation

Access Control Configuration

Grant semantic view permissions without exposing underlying tables:

sql
-- Create role for analysts
CREATE ROLE analyst_role;

-- Grant semantic view access
GRANT SELECT ON SEMANTIC VIEW orders_analysis TO ROLE analyst_role;

-- Restrict raw table access
REVOKE SELECT ON TABLE orders FROM ROLE analyst_role;
REVOKE SELECT ON TABLE customers FROM ROLE analyst_role;

-- Assign role to users
GRANT ROLE analyst_role TO USER analyst_user;

Documentation and Metadata

Add comprehensive documentation to semantic views:

sql
-- Semantic view level documentation
COMMENT ON SEMANTIC VIEW orders_analysis IS
  'Revenue and order metrics for Sales team reporting.
   Migrated from LookML orders explore on 2025-01-15.
   Owner: data_engineering@company.com
   Refresh: Real-time (direct table queries)
   Contains: Order counts, revenue metrics, customer segmentation
   Related views: customer_analysis, product_analysis';

-- Metric level documentation
CREATE OR REPLACE SEMANTIC VIEW orders_analysis
...
METRICS (
  orders.total_revenue AS SUM(orders.amount)
    COMMENT 'Sum of all order amounts excluding refunds. Matches LookML total_revenue measure.',
  orders.net_revenue AS SUM(orders.amount * (1 - orders.discount_pct))
    COMMENT 'Revenue after discounts applied. Used for financial reporting.'
);

Change Tracking

Track semantic view modifications:

sql
-- Query modification history
SELECT
  query_text,
  user_name,
  start_time,
  execution_status
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_text LIKE '%CREATE OR REPLACE SEMANTIC VIEW orders_analysis%'
ORDER BY start_time DESC;

-- Monitor access patterns
SELECT
  user_name,
  COUNT(*) as query_count,
  AVG(total_elapsed_time) as avg_query_time_ms
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_text LIKE '%SEMANTIC_VIEW%orders_analysis%'
  AND start_time >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY user_name
ORDER BY query_count DESC;

---

Migration Validation Checklist

Pre-Migration Validation

• All base tables have PRIMARY KEY constraints defined
• Foreign key relationships match LookML join definitions
• Derived tables materialized as views or tables in Snowflake
• Data types align with dimension/fact classifications
• User access patterns documented for RLS policy creation
• LookML measure definitions extracted and categorized

Translation Validation

• Each LookML view maps to Snowflake logical table
• Explore joins translated to relationship definitions
• Categorical dimensions classified correctly (not as facts)
• Numeric columns classified as facts (not dimensions)
• Simple measures translated to appropriate metric aggregations
• Calculated measures translated with correct SQL expressions
• Derived metrics reference other metrics correctly
• Persistent derived tables implemented with refresh logic

Query Result Validation

• Baseline metrics extracted from LookML explores
• Snowflake semantic view queries return matching results
• Variance analysis completed using Fenic validation pipeline
• All variances beyond tolerance threshold investigated
• Join cardinality matches between systems (no fan-out)
• Distinct counts match exactly across systems
• Aggregate calculations (SUM, AVG, COUNT) match

Governance Validation

• Row-level security policies implemented and tested
• Role-based access controls configured
• Underlying table access restricted appropriately
• Semantic view documentation complete
• Metric definitions documented with business context
• Owner and contact information specified
• Change tracking queries validated

Performance Validation

• Query execution times measured and compared
• Clustering keys added to frequently filtered tables
• Search optimization enabled on high-cardinality columns
• Materialized views created for expensive query patterns
• Warehouse sizing appropriate for workload
• Query result caching verified functional

Integration Validation

• BI tool connections tested (Tableau, Power BI, Mode)
• Cortex Analyst natural language queries validated
• Sample values added for LLM context
• Verified query patterns defined
• Dashboard queries translated and tested
• Report results match LookML equivalents

---

Common Translation Errors and Resolutions

Error: Primary Key Not Defined

Symptom:

SQL compilation error: Semantic view requires PRIMARY KEY on table 'customers'

Resolution:

sql
ALTER TABLE customers ADD PRIMARY KEY (customer_id);

Error: Relationship References Invalid Column

Symptom:

SQL compilation error: Column 'customer_id' not found in table 'customers'

Resolution:

sql
-- Verify column exists and matches spelling/case
SHOW COLUMNS IN TABLE customers;

-- Fix relationship definition
RELATIONSHIPS (
  orders.customer_id REFERENCES customers.id  -- Use actual column name
)

Error: Cannot Mix Facts and Metrics in Query

Symptom:

SQL execution error: Cannot specify both FACTS and METRICS in same query

Resolution:

sql
-- Incorrect: Mixing facts and metrics
SELECT * FROM SEMANTIC_VIEW(
  orders_analysis
  FACTS order_amount
  METRICS total_revenue
);

-- Correct: Use only metrics
SELECT * FROM SEMANTIC_VIEW(
  orders_analysis
  METRICS total_revenue, average_order_value
  DIMENSIONS customer_region
);

Error: Metric Variance Between Systems

Symptom: LookML query returns $125,000 revenue; Snowflake returns $250,000

Root Cause: Join fan-out due to missing unique constraints

Resolution:

sql
-- Identify duplicate joins causing fan-out
SELECT
  order_id,
  COUNT(*) as join_count
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Fix by ensuring proper aggregation in derived table
CREATE VIEW order_facts AS
SELECT
  order_id,
  SUM(amount) as total_amount,  -- Aggregate before join
  COUNT(*) as item_count
FROM order_items
GROUP BY order_id;

-- Update semantic view to use aggregated table
TABLES (
  order_facts PRIMARY KEY (order_id)
)

Error: Liquid Template Not Translating

Symptom: LookML measure uses Liquid; no Snowflake equivalent exists

Resolution: Pre-compute metric variations or move logic to application layer (see Advanced Translation Patterns section)

---

Post-Migration Maintenance

Version Control Integration

Export semantic views to YAML for version control:

sql
-- Export semantic view definition
SELECT SYSTEM$GET_SEMANTIC_VIEW_AS_YAML(
  'my_database.my_schema.orders_analysis'
);

Store YAML in Git repository:

bash
# Save to version control
snowflake sql -q "SELECT SYSTEM$GET_SEMANTIC_VIEW_AS_YAML('db.schema.view')" \
  > semantic_views/orders_analysis.yaml

git add semantic_views/orders_analysis.yaml
git commit -m "Update orders_analysis metric definitions"
git push

Deploy changes through CI/CD:

bash
# Deploy from YAML
snowflake sql -q "
  CREATE OR REPLACE SEMANTIC VIEW orders_analysis
  FROM @my_stage/orders_analysis.yaml
"

Monitoring Query Patterns

Set up alerts for anomalous behavior:

sql
-- Create view tracking semantic view usage
CREATE VIEW semantic_view_monitoring AS
SELECT
  DATE_TRUNC('hour', start_time) as query_hour,
  REGEXP_SUBSTR(query_text, 'SEMANTIC_VIEW\\(([^\\s]+)', 1, 1, 'i', 1) as semantic_view_name,
  COUNT(*) as query_count,
  AVG(total_elapsed_time) as avg_duration_ms,
  SUM(credits_used_cloud_services) as total_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_text LIKE '%SEMANTIC_VIEW%'
  AND start_time >= DATEADD(day, -7, CURRENT_DATE)
GROUP BY query_hour, semantic_view_name;

-- Alert on unusual patterns
SELECT *
FROM semantic_view_monitoring
WHERE query_count > (SELECT AVG(query_count) * 3 FROM semantic_view_monitoring)
   OR avg_duration_ms > 10000;

Cost Optimization

Track semantic view query costs:

sql
SELECT
  DATE_TRUNC('day', start_time) as query_date,
  REGEXP_SUBSTR(query_text, 'SEMANTIC_VIEW\\(([^\\s]+)', 1, 1, 'i', 1) as semantic_view_name,
  COUNT(*) as query_count,
  SUM(credits_used_cloud_services) as credits_used,
  SUM(bytes_scanned) / POW(1024, 3) as gb_scanned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_text LIKE '%SEMANTIC_VIEW%'
  AND start_time >= DATEADD(month, -1, CURRENT_DATE)
GROUP BY query_date, semantic_view_name
ORDER BY credits_used DESC;

---

Additional Resources

Technical guides and platform documentation:

• Build Reliable AI Pipelines with Fenic's Semantic Operators - Automated validation techniques
• Semantic Processing Statistics - Infrastructure efficiency data
• Fenic Open Source - DataFrame capabilities
• Typedef Launch - Platform overview

Migration from LookML to Snowflake Semantic Views consolidates metric definitions into native database objects, eliminating external semantic layer dependencies. Use Fenic's semantic operators for automated validation during translation. Accurate translation requires aligning LookML constructs with corresponding Snowflake semantic components.