Semantic View Materialization Recommendations

#	Semantic View	Candidate	Dims	Metrics	Hours saved (est)	IMMUTABLE WHERE	Reminders
1	`{{SV_FQN}}`	`{{CANDIDATE_NAME}}`	{{DIMS}}	{{METRICS}}	{{HOURS_SAVED}}h	`{{IMMUTABLE_WHERE}}`	{{REMINDERS}}

Semantic View	Slow queries	Avg sec	Max sec	Hours wasted	Users / Roles
`{{SV_FQN}}`	{{NUM_SLOW}}	{{AVG_SEC}}	{{MAX_SEC}}	{{HOURS}}	{{USERS_ROLES}}

Workload: {{NUM_SLOW}} slow queries, {{HOURS}} hours wasted

Owner: role {{OWNER_ROLE}} · last DDL by {{LAST_DDL_USER}} ({{LAST_DDL_TYPE}} on {{LAST_DDL_TIME}}) · dbt node: {{DBT_NODE_ID}}

Metric	Kind	Materializable?
`{{METRIC_NAME}}`	additive (SUM)	yes
`{{METRIC_NAME}}`	non-additive (AVG)	yes (exact dims only)
`{{METRIC_NAME}}`	semi-additive	NO — excluded

Candidate: `{{CANDIDATE_NAME}}`

Covers {{NUM_COVERED}} queries, ~{{HOURS_SAVED}}h saved

Dimensions: {{DIMS}}

Metrics: {{METRICS}}

IMMUTABLE WHERE proposal (refresh-cost optimization):
{{IMMUTABLE_WHERE_PREDICATE}} — {{IMMUTABLE_WHERE_RATIONALE}}

This tells the engine that source rows matching the predicate are frozen (no updates, deletes, or late-arriving inserts). Refresh skips those rows, making both full and incremental refresh substantially cheaper.

Before keeping this clause, confirm: "Will any rows matching {{IMMUTABLE_WHERE_PREDICATE}} ever be updated, deleted, or joined by new rows that change their aggregated values?" If yes → remove the IMMUTABLE WHERE line.

DDL

-- If MAX_STALENESS is missing, run first:
-- ALTER SEMANTIC VIEW {{SV_FQN}} SET MAX_STALENESS = '1 hour';

ALTER SEMANTIC VIEW {{SV_FQN}} ADD MATERIALIZATION {{CANDIDATE_NAME}}
    WAREHOUSE = {{WAREHOUSE}}
    IMMUTABLE WHERE ({{IMMUTABLE_WHERE_PREDICATE}})  -- REMOVE IF CANNOT CONFIRM
AS
    DIMENSIONS {{DIMS}}
    METRICS {{METRICS}};

How to test this materialization

Set MAX_STALENESS if not already set:

ALTER SEMANTIC VIEW {{SV_FQN}} SET MAX_STALENESS = '1 hour';

Run the ADD MATERIALIZATION DDL above.

Wait for the initial refresh to complete. Monitor via:

SELECT * FROM TABLE(INFORMATION_SCHEMA.SEMANTIC_VIEW_MATERIALIZATION_REFRESH_HISTORY(
  NAME => '{{CANDIDATE_NAME}}'
));

Re-run one of the slow queries and compare execution time:

-- Sample covered query (was {{SAMPLE_DURATION}}s):
{{SAMPLE_QUERY}}

Verify the materialization was used via the query profile — look for SemanticViewMaterializationScan in the plan.

If performance is satisfactory, keep it. If not, drop:

ALTER SEMANTIC VIEW {{SV_FQN}} DROP MATERIALIZATION {{CANDIDATE_NAME}};

Covered queries (sample)

Query ID	Duration	User	Dims	Metrics
`{{QUERY_ID}}`	{{DURATION}}s	{{USER}}	{{QUERY_DIMS}}	{{QUERY_METRICS}}

Excluded queries

Query ID	Duration	Reason
`{{QUERY_ID}}`	{{DURATION}}s	{{REASON}}

Required confirmations

Choose a warehouse for each materialization's refresh
Confirm masking policies are not blocking (if flagged above)

Reminders (handle before running DDL)

{{SV_FQN}}: run ALTER SEMANTIC VIEW {{SV_FQN}} SET MAX_STALENESS = '{{RECOMMENDED_VALUE}}';

IMMUTABLE WHERE confirmations

{{SV_FQN}}.{{CANDIDATE_NAME}}: {{PREDICATE}} — rows immutable? (keep / drop the clause)

TOTAL_ELAPSED_TIME is in milliseconds; thresholds shown in seconds.
Query parsing is best-effort — some nested SEMANTIC_VIEW() calls may not be fully parsed.
Inline expression dimensions (e.g. DATE_TRUNC('DAY', d)) ARE supported by the rewriter when the metric cluster is additive — the underlying base column just needs to be a materialization dim. They only block rewrite when combined with a non-additive metric (COUNT DISTINCT, AVG, MEDIAN), because rollup to the expression's grain isn't possible.
Estimated hours saved assumes near-zero materialization read time; actual savings depend on refresh cadence.
Semi-additive metrics (NON ADDITIVE BY) cannot be materialized today.

Semantic View Materialization Recommendations

Summary

Workload Overview

{{SV_FQN}}

Pre-flight checklist

Metric classification

Candidate: `{{CANDIDATE_NAME}}`

DDL

How to test this materialization

Covered queries (sample)

Excluded queries

Action items before applying

Required confirmations

Reminders (handle before running DDL)

IMMUTABLE WHERE confirmations

Caveats

Semantic View Materialization Recommendations

Summary

Workload Overview

{{SV_FQN}}

Pre-flight checklist

Metric classification

Candidate: {{CANDIDATE_NAME}}

DDL

How to test this materialization

Covered queries (sample)

Excluded queries

Action items before applying

Required confirmations

Reminders (handle before running DDL)

IMMUTABLE WHERE confirmations

Caveats

Candidate: `{{CANDIDATE_NAME}}`