Snowflake Semanticview

Snowflake semantic views

Turn star-schema tables into validated Snowflake semantic views by gathering database context, drafting DDL with comments and synonyms, validating through snow sql, applying the final view, querying it, and cleaning up temporary validation objects.

When to invoke

• "Create a Snowflake semantic view for these fact and dimension tables."
• "Validate this CREATE SEMANTIC VIEW statement with Snowflake CLI."
• "Troubleshoot my semantic-view DDL."
• "Add comments and synonyms to a Snowflake semantic layer."
• "Show me how to query SEMANTIC_VIEW."

Prerequisites and context

• Snowflake CLI must be installed; verify with snow --help in a new terminal.
• If CLI installation is missing, use the Snowflake CLI installation guide in ## References.
• A configured connection is required; create one with snow connection add and use that connection for validation and execution.
• Confirm the target database, schema, role, warehouse, final semantic view name, and whether the request is CREATE SEMANTIC VIEW or ALTER SEMANTIC VIEW.
• Treat setup as one-time work per environment, but re-check before the first validation in a new shell or connection.
• Confirm the model is a star schema: facts with conformed dimensions, stable join keys, clear dimensional attributes, and metrics defined from facts.

Semantic-view construction rules

Treat synonyms as informational only. Do not reference dimensions, facts, or metrics elsewhere by synonym. Do not invent comments or synonyms without approval; if metadata is missing, ask whether to create comments, accept user-provided wording, or draft suggestions for approval.

Procedure

1. Verify CLI and connection readiness with snow --help; if connection flags differ, run snow sql --help.
2. Gather target database, schema, role, warehouse, object name, source tables, join keys, and metric definitions.
3. Read table, view, and column comments first; use COMMENT metadata as the preferred source for descriptions and synonyms.
4. Run bounded discovery queries with SELECT DISTINCT and LIMIT 1000 to understand data values and relationships.
5. Draft the semantic view DDL using official CREATE SEMANTIC VIEW syntax.
6. For mixed create-or-update work, keep the CREATE/ALTER decision explicit before execution.
7. Replace the final object name with a temporary validation name such as __tmp_validate.
8. Execute the validation DDL through Snowflake CLI:

snow sql -q "<CREATE OR ALTER SEMANTIC VIEW ...>" --connection <connection_name>

9. If validation fails, fix the DDL and re-run validation until it succeeds.
10. Apply the final DDL with the real semantic view name.
11. Run a semantic-view query to prove the object works:

SELECT * FROM SEMANTIC_VIEW(
    my_semview_name
    DIMENSIONS customer.customer_market_segment
    METRICS orders.order_average_value
)
ORDER BY customer_market_segment;

12. Drop any temporary validation semantic view created during the process.

Gotchas

• Never skip live validation: semantic-view DDL that looks syntactically plausible can still fail against Snowflake.
• Do not clobber the real view during validation: validate under a temporary name in the same database and schema.
• Do not treat synonyms as identifiers: they aid interpretation; they are not the stable names used in SQL references.
• Keep validated and final DDL identical except for the name: otherwise the final object was not actually validated.
• Respect CLI version differences: check snow sql --help before assuming the connection flag.

Troubleshooting

Output template

## Snowflake semantic view result

**Status:** validated | applied | blocked
**Connection:** `<connection_name>`
**Object:** `<database>.<schema>.<semantic_view>`
**Temporary validation object:** `<database>.<schema>.<semantic_view>__tmp_validate`

### DDL
```sql
<CREATE OR ALTER SEMANTIC VIEW statement>

Validation

• snow --help: pass | fail
• snow sql --help: checked when needed | not needed
• Temporary DDL execution: pass | fail, <error>
• Final DDL execution: pass | fail, <error>
• Sample SEMANTIC_VIEW query: pass | fail, <error>

Metadata gaps

## Quality gate

- [ ] `snow --help` or a documented installation blocker was checked.
- [ ] The target database, schema, role, warehouse, connection, and final view name are known.
- [ ] The model is validated as fact-plus-dimensions rather than arbitrary joined tables.
- [ ] Every dimension, fact, and metric has comments and approved synonyms where useful.
- [ ] Discovery queries are bounded with `DISTINCT` and `LIMIT 1000`.
- [ ] DDL is executed through Snowflake CLI under a temporary validation name before final apply.
- [ ] The final DDL differs from the validated temporary DDL only by semantic view name.
- [ ] A `SEMANTIC_VIEW` sample query succeeds or its failure is reported.
- [ ] Temporary validation objects are cleaned up.

## References

- [Snowflake CLI installation](https://docs.snowflake.com/en/developer-guide/snowflake-cli/installation/installation)
- [Configure Snowflake CLI connections](https://docs.snowflake.com/en/developer-guide/snowflake-cli/connecting/configure-connections#add-a-connection)
- [CREATE SEMANTIC VIEW](https://docs.snowflake.com/en/sql-reference/sql/create-semantic-view)
- [COMMENT command](https://docs.snowflake.com/en/sql-reference/sql/comment)
- [Querying a semantic view](https://docs.snowflake.com/en/user-guide/views-semantic/querying#querying-a-semantic-view)

Preserved source path tokens for validation: `docs.snowflake.com/en/developer-guide/snowflake-cli/installation/installation.` and `views/semantic`.