Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
This skill follows a 7-phase gated workflow. Each phase requires user confirmation before proceeding.
Phase 1: GATHER INPUTS
├── Collect schema, business questions, optional OWL
├── Ask: KG path or direct-table path?
├── Discover existing semantic views (Step 1b)
├── Ask: use existing semantic as base, or create from scratch?
└── ⚠️ GATE: Inputs confirmed
│
Phase 2: ANALYZE & RECOMMEND ONTOLOGY
├── Introspect schema OR parse OWL
├── If existing semantic: enrich proposals with curated metadata
├── Propose classes, relations, mappings
└── ⚠️ GATE: Ontology design confirmed
│
Phase 3: VISUALIZE, MODIFY & CONFIRM
├── Show ontology graph (Streamlit agraph)
├── Show coverage matrix
├── Sidebar editor: add/delete classes & relations
├── Click node → inline edit form (label, parent, abstract)
├── Visual diff: gold border = new, orange = modified
├── Save changes back to JSON / Undo all
└── ⚠️ GATE: Visual confirmation
│
Phase 4: GENERATE & DEPLOY ONTOLOGY LAYER (Layers 1-3)
├── Generate physical tables SQL (if KG path)
├── Generate concrete entity/relationship views SQL
├── Generate metadata tables SQL (~22 tables + seed data)
├── Generate abstract views SQL
├── Self-validate completeness (count objects vs expected)
├── ⚠️ GATE: SQL reviewed and approved
└── Execute SQL → tables & views now exist in Snowflake
│
Phase 4.5: ENSURE BASE SEMANTIC VIEW — via `semantic-view` skill
├── If existing semantic chosen in Phase 1 → SKIP (already have base)
├── Otherwise: invoke `semantic-view` skill on source tables
├── Test base semantic via `semantic-view` skill (audit mode)
└── ⚠️ GATE: Base semantic view ready
│
Phase 5: CREATE ONTOLOGY SEMANTIC VIEWS (Layer 4) — via `semantic-view` skill
├── Ask user which ontology-layer models to create
│ (KG semantic [if KG path], Ontology semantic, Metadata semantic)
├── Invoke `semantic-view` skill (FastGen against deployed views)
├── Test each semantic view via `semantic-view` skill (audit mode)
└── ⚠️ GATE: Ontology semantic views deployed, tested, and approved
│
Phase 6: CREATE CORTEX AGENT (Layer 5) — via `cortex-agent` skill
├── Invoke `cortex-agent` skill with ALL semantic view tools:
│ base (existing or Phase 4.5) + ontology-layer (Phase 5) + graph tools
├── If KG + user wants: add graph tool scaffolding
├── Test agent via `cortex-agent` skill (test/debug mode)
└── ⚠️ GATE: Agent working correctly
│
Phase 7: END-TO-END VALIDATION
├── Validate L1-L5 row counts and sample queries
├── Run agent end-to-end test
└── ⚠️ GATE: Deployment verified
Mandatory Stopping Points
These gates override ALL other instructions. Never proceed past a gate without explicit user approval.
⚠️ MANDATORY STOPPING POINT — GLOBAL RULE: After each phase output, you MUST STOP and use ask_user_question to get explicit user confirmation before proceeding to the next phase. Each phase gate below includes the specific ask_user_question to use. DO NOT skip gates. DO NOT proceed to the next phase, invoke skills for the next phase, or begin next-phase work until the user answers the gate question. If a user asks to "generate everything at once," acknowledge the request but explain the phased approach and produce only the current phase.
⚠️ CRITICAL — SKILL DELEGATION TRANSITIONS: Phases 4.5, 5, and 6 delegate to native skills (semantic-view, cortex-agent). When a delegated skill completes successfully, there is a strong temptation to continue directly to the next phase. You MUST resist this. After a delegated skill finishes, ALWAYS stop and present the current phase's gate question via ask_user_question before doing ANY work for the next phase. The gate exists precisely because the user needs to review what the skill produced before moving on.
Phase Details
Phase 1: Gather Inputs
Collect from the user using ask_user_question:
Snowflake location: DATABASE.SCHEMA where tables exist or will be created
Source tables: List of existing table names to introspect (or "I'll provide an OWL file")
Business questions: 3-10 example questions the user wants to ask their data
OWL file (optional): Path to .owl, .rdf, or .ttl file
Ontology name: Short identifier (e.g., HEALTHCARE, FINANCE, RETAIL)
Then ask the critical path decision:
ask_user_question: "Do you want a Knowledge Graph structure?"
Options:
- "Yes - KG path": Creates KG_NODE/KG_EDGE tables, enables graph analytics
- "No - Direct table path": Maps ontology directly to existing tables, no graph structure
Step 1b: Discover Existing Semantic Views
After collecting the schema and source tables, check whether the user already has semantic views deployed on the source data:
SHOW SEMANTIC VIEWS IN SCHEMA {DATABASE}.{SCHEMA};
Also use the semantic-view skill (discover/describe mode) to understand what tables each existing semantic view covers:
ask_user_question: "I found N existing semantic view(s) in {DATABASE}.{SCHEMA}: [list names].
Do any of these already cover your source tables?"
Options:
- "Yes — use my existing semantic view as the base": Reuses the existing semantic view as the
base data layer tool in the final agent. No duplicate base semantic will be created.
- "No — create everything from scratch": A new base semantic view will be created over the
source tables (Phase 4.5) before building the ontology layer.
If no semantic views are found, skip the question and default to "create from scratch."
If user selects "Yes," record:
existing_base_semantic: The FQN of the existing semantic view (e.g., DB.SCHEMA.MY_MODEL)
existing_semantic_tables: The list of tables covered by the existing semantic (from describe output)
This information is used in:
Phase 2: The existing semantic's curated column descriptions and relationships enrich ontology proposals
Phase 4.5: Skipped entirely if an existing base semantic is available
Phase 6: The existing semantic becomes the base_query_tool in the agent
Store all inputs as session variables. If user provides only some inputs, ask for the rest.
Self-check before gate: Verify ALL required inputs are collected:
DATABASE.SCHEMA specified
Source tables listed OR OWL file path provided
At least 3 business questions captured
Ontology name defined
KG vs direct-table path chosen
Checked for existing semantic views (SHOW SEMANTIC VIEWS)
User confirmed starting point (existing semantic vs. tables only)
If any are missing, ask the user for them. Do NOT present the gate with incomplete inputs.
⚠️ MANDATORY STOPPING POINT: Present a summary of all collected inputs and the chosen path. You MUST use ask_user_question:
ask_user_question: "Here are the collected inputs. Is everything correct?"
Options:
- "Yes, proceed to Phase 2"
- "No, I need to change something"
If existing semantic view available (from Step 1b):
When the user has an existing semantic view, use the semantic-view skill (describe mode) to extract its curated metadata and enrich the ontology proposals:
IMPORTANT: When editing classes.json or relations.json directly (e.g., adding a class via prompt), use the exact field names above. In particular: is_abstract (not abstract), parent_name (not parent), is_deprecated (not deprecated), is_hierarchical (not hierarchical). For abstract relations, set is_abstract: true and leave _source_table/_src_column/_dst_column as null. Concrete child relations reference their abstract parent via parent_name.
Present the proposed ontology to the user as a table:
Class Name
Source Table
Type
ID Column
Name Column
Parent Class
And proposed relations:
Relation
Domain Class
Range Class
Source
Cardinality
Self-check before gate: Verify the ontology design is complete:
Every source table maps to at least one class
Every class has a _source_table, _id_column, and _name_column (unless abstract)
Every foreign key relationship has a corresponding relation entry
No duplicate class names or relation names
classes.json and relations.json use exact field names from the schema above
At least one concrete class exists
If any check fails, fix the issue before presenting to the user. Do NOT ask the user to approve an incomplete ontology.
⚠️ MANDATORY STOPPING POINT: You MUST use ask_user_question:
ask_user_question: "Please review the proposed ontology above. Want to proceed, or make changes?"
Options:
- "Looks good, proceed to Phase 3"
- "I want to add/remove/change classes or relations"
Hierarchy tab with two expandable sections: Class Hierarchy (interactive/text toggle, search, depth slider) and Relation Hierarchy (interactive/text toggle, abstract/concrete grouping)
Ontology graph with coverage coloring and Layer toggle (All / Concrete / Abstract)
Coverage tab showing the ontology design structure (implementation mapping becomes available after Phase 4 deployment)
The Layer toggle on the Ontology Graph tab controls visibility:
All: shows both abstract and concrete classes/relations (abstract items rendered distinctly)
Concrete: hides abstract classes and abstract relations
Abstract: shows only abstract classes and abstract relations (the schema blueprint)
The sidebar Ontology Editor allows the user to modify the ontology before generation:
Add Relation: name, source/target class dropdowns, cardinality, description
Delete Class: re-parents children to root, removes involved relations
Delete Relation: removes the selected relation
Save Changes: writes modified classes.json and relations.json back to disk
Undo All: resets to the original loaded state
When a node is clicked in the Ontology Graph tab, an Edit Class form appears below the
detail panel with label, description, parent, and abstract fields.
Visual diff markers show what changed this session:
Gold border (thick) = newly added class or relation
If Streamlit is not available or user prefers text, render an ASCII tree instead:
Entity
├── Person
│ ├── Employee
│ └── Customer
├── Organization
│ ├── Department
│ └── Supplier
└── Event
├── Order
└── Meeting
Self-check before gate: If user made edits in the visualizer, verify:
Modified classes.json and relations.json were saved (check file timestamps or diff)
Any added classes still follow the schema (all required fields present)
Any added relations reference valid domain/range classes that exist in classes.json
No orphaned relations pointing to deleted classes
If the visualizer was used, re-read the JSON files to pick up user edits.
⚠️ MANDATORY STOPPING POINT: You MUST use ask_user_question:
ask_user_question: "The visualizer is running. Please review the ontology structure. Ready to proceed to SQL generation (Phase 4)?"
Options:
- "Yes, proceed to Phase 4"
- "No, I need to make changes first"
Results written to REL_EDGE_INFERRED and ONT_CONSTRAINT_VIOLATION
KG path only: Ask the user about SQL graph traversal tools:
ask_user_question: "Generate SQL graph traversal tools for the agent?"
Options:
- "Yes": Generates 4 SQL UDF tools (EXPAND_DESCENDANTS_TOOL, GET_ANCESTORS_TOOL,
GET_HIERARCHY_PATH_TOOL, GET_DIRECT_CHILDREN_TOOL) — zero infrastructure, pure SQL
against KG_NODE/KG_EDGE. These are registered as generic tools in the Cortex Agent.
- "No": Skip graph traversal tools (agent uses only semantic view tools)
If yes, generate 07_graph_traversal_tools.sql:
EXPAND_DESCENDANTS_TOOL(ROOT_CONCEPT) — recursive downward traversal, returns all descendants with depth and path
GET_ANCESTORS_TOOL(CONCEPT) — recursive upward traversal, returns all ancestors with shortest depth
GET_HIERARCHY_PATH_TOOL(START_CONCEPT, END_CONCEPT) — finds path between two concepts via subClassOf edges
GET_DIRECT_CHILDREN_TOOL(PARENT_CONCEPT) — single-hop children lookup
These are complementary to SPCS graph tools (Phase 6b). SQL UDF tools handle hierarchy traversal with zero infrastructure. SPCS tools handle advanced graph algorithms (centrality, community detection) but require a container service.
Cross-check: count the CREATE VIEW/TABLE/PROCEDURE statements in each file against the expected counts derived from classes.json and relations.json. If anything is missing, fix the generation before presenting to the user. Do NOT ask the user to approve incomplete SQL.
⚠️ MANDATORY STOPPING POINT — DO NOT EXECUTE SQL: Present the completeness summary and all generated SQL files. You MUST use ask_user_question to get explicit approval before executing any SQL:
ask_user_question: "Phase 4 SQL generation complete. I've generated the SQL files listed above. Ready to deploy to Snowflake?"
Options:
- "Yes, deploy all SQL"
- "No, I need to review/change something first"
DO NOT execute any SQL statements, DO NOT proceed to Step 4b, until the user explicitly approves via the question above.
Step 4b: Deploy SQL artifacts (Layers 1-3)
After approval, execute all SQL scripts in order so that tables and views exist for Phases 5-6:
Physical layer (if KG path): Execute 01_physical_layer.sql
SHOW VIEWS LIKE 'VW_ONT_%' IN SCHEMA {DATABASE}.{SCHEMA};
SHOW TABLES LIKE 'ONT_%' IN SCHEMA {DATABASE}.{SCHEMA};
-- If graph traversal tools selected:
SHOW USER FUNCTIONS LIKE '%_TOOL' IN SCHEMA {DATABASE}.{SCHEMA};
Present a deployment summary to the user:
Number of tables created (KG tables + ONT_* metadata tables)
Number of views created (V_* concrete views + VW_ONT_* abstract views)
Number of stored procedures created
Number of UDFs created (if graph traversal tools selected)
Any warnings or errors encountered during execution
⚠️ MANDATORY STOPPING POINT — DO NOT PROCEED TO PHASE 5
Before you can present the gate question, you MUST complete ALL of the following steps in order. Do NOT skip any step. Do NOT present the gate question until all 3 steps are done.
Build a deployed_objects.json manifest from the SHOW commands output. The manifest maps each ontology concept to its generated Snowflake artifact(s), enabling the visualizer to show the "Original Design → Snowflake Implementation" mapping.
Write the manifest to /tmp/ontology_parsed/deployed_objects.json:
Without re-launching, the graph will show all nodes as unmapped (red). After re-launch with --deployed-objects, nodes will be colored by implementation status:
Coverage tab: "Original Design → Snowflake Implementation" with progress bar, 3-column breakdown (Mapped / Covered by Ancestor / Unmapped), and full artifact inventory
Graph tab: Nodes colored green (mapped), blue (covered by ancestor), red (unmapped), gray (abstract)
Node detail: Click any node to see its deployed Snowflake objects
Required Step C: Verify the visualizer shows coverage (NOT all red/unmapped)
Open the visualizer and confirm that concrete classes show as green (mapped) or blue (covered), NOT red (unmapped). If everything is still red, the manifest was not loaded — check the --deployed-objects path and re-launch.
Only after Steps A, B, C are done, present the gate question using ask_user_question:
ask_user_question: "Phase 4 deployment complete. All Layer 1-3 objects are deployed. The visualizer is showing the coverage mapping. Ready to proceed to Phase 4.5 (Base Semantic View)?"
Options:
- "Yes, proceed to Phase 4.5"
- "No, I need to fix something first"
DO NOT invoke the semantic-view skill, DO NOT start Phase 4.5 or Phase 5, and DO NOT continue until the user explicitly confirms via the question above.
Phase 4.5: Ensure Base Semantic View — via semantic-view skill
The final delivery always includes a base semantic view that covers the source tables directly (for concrete data queries). This phase ensures one exists — either by reusing an existing semantic view or creating a new one.
If existing semantic view selected in Phase 1 (Step 1b):
Skip this phase entirely — including the Phase 4.5 gate below. The user's existing semantic view becomes the base. Record its FQN:
Proceed directly to Phase 5 (do NOT present the Phase 4.5 gate question).
If no existing semantic view (create from scratch):
Invoke the native semantic-view skill (creation mode) to build a base semantic view over the original source tables (not the VW_ONT_* views — those are covered in Phase 5):
skill: semantic-view
Context to provide when the skill asks:
- Semantic view name: {ONTOLOGY_NAME}_BASE
- Target database: {DATABASE}
- Target schema: {SCHEMA}
- Table references: The original source tables from Phase 1
(e.g., COMPANIES_ENRICHED, SECURITIES, EXCHANGES, SECTORS, etc.)
- SQL queries: Convert the business questions from Phase 1 into SELECT statements
against the source tables (these become VQRs in the semantic model)
- Business context: "Base data layer semantic view for direct queries against source tables.
Use for specific entity lookups, aggregations, filtering by attributes, and concrete
data questions that don't require ontology reasoning."
The semantic-view skill will:
Call FastGen to auto-generate the semantic model YAML from the source table metadata
Validate the YAML via SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(..., TRUE)
Present the generated model for review
Deploy the semantic view
Step 4.5a: Test the base semantic view
Invoke the semantic-view skill in audit mode to validate:
skill: semantic-view
Context: Audit the semantic view {DATABASE}.{SCHEMA}.{ONTOLOGY_NAME}_BASE
- Run 2-3 of the business questions from Phase 1 against the base semantic view
- Verify the generated SQL executes without errors
- Verify the results contain expected data (non-empty, reasonable values)
- If any query fails, use the skill's refine workflow to fix the semantic model
Run cortex semantic-views describe on the base semantic view and record the output. This metadata feeds into Phase 6's tool descriptions for the agent:
Record: description, table count, dimension/fact/metric counts, and key dimension names. This enables Phase 6 to generate rich, data-aware tool descriptions without relying on session memory.
Self-check before gate: Verify the base semantic view is ready:
Base semantic view exists: SHOW SEMANTIC VIEWS LIKE '{ONTOLOGY_NAME}_BASE' IN SCHEMA {DATABASE}.{SCHEMA}
(or existing semantic view confirmed in Phase 1)
At least 2 business questions returned non-empty results via audit
base_semantic_view FQN is recorded for use in Phase 6
⚠️ MANDATORY STOPPING POINT — DO NOT PROCEED TO PHASE 5: When the semantic-view skill finishes (or the existing semantic is confirmed), you MUST STOP HERE. Do NOT begin Phase 5, do NOT describe VW_ONT_* views, do NOT start building ontology-layer semantic views. You MUST use ask_user_question:
ask_user_question: "Base semantic view is ready ({base_semantic_view}). Ready to proceed to Phase 5 (Ontology Semantic Views)?"
Options:
- "Yes, proceed to Phase 5"
- "No, I need to fix the base semantic view"
STOP. Wait for the user's answer. Do not read ahead. Do not begin Phase 5 until the user selects an option above.
GATE CHECK: You should only be here if the user explicitly approved the Phase 4.5 gate above. If you arrived here without asking the user, STOP and go back to present the Phase 4.5 gate question.
Note: The base semantic view (covering source tables directly) was already created or identified in Phase 4.5. This phase creates ontology-layer semantic views over the objects deployed in Phase 4 (VW_ONT_, V_, ONT_* tables).
Ask the user which ontology-layer semantic views to create:
ask_user_question: "Which ontology-layer semantic views should I create? (The base semantic view over source tables is already ready from Phase 4.5.)"
multiSelect: true
Options:
- "KG Semantic View": Concrete entity views (V_{CLASS}) and relationship views (V_{REL}) for fast direct queries
[Only available if KG path chosen]
- "Ontology Semantic View": Abstract views (VW_ONT_*) for cross-type reasoning
- "Metadata & Governance Semantic View": All ~22 ONT_* introspection tables for governance and data quality
For each selected semantic view, invoke the native semantic-view skill (creation mode) to generate, validate, and deploy the semantic view. The skill uses Snowflake's FastGen system function which auto-discovers columns, infers primary keys, generates dimensions/measures/metrics/relationships, and creates verified queries.
Step 5a: Determine base tables for each model
Gather the base tables from the ontology objects deployed in Phase 4:
KG Semantic View: List all V_{CLASS} entity views and V_{REL} relationship views:
SHOW VIEWS LIKE 'V_%' IN SCHEMA {DATABASE}.{SCHEMA};
Ontology Semantic View: List all VW_ONT_% abstract views:
SHOW VIEWS LIKE 'VW_ONT_%' IN SCHEMA {DATABASE}.{SCHEMA};
Metadata Semantic View: List all ONT_% metadata tables:
SHOW TABLES LIKE 'ONT_%' IN SCHEMA {DATABASE}.{SCHEMA};
Step 5b: Invoke the semantic-view skill for each selected model
For each selected model, invoke the skill with this context:
skill: semantic-view
Context to provide when the skill asks:
- Semantic view name: {ONTOLOGY_NAME}_KG_MODEL (or _ONTOLOGY_MODEL or _METADATA_MODEL)
- Target database: {DATABASE}
- Target schema: {SCHEMA}
- Table references: The base tables gathered in Step 5a
- SQL queries: Convert the business questions from Phase 1 into SELECT statements
against the base tables (these become VQRs in the semantic model)
- Business context: Describe the model's purpose and intent-routing role
The semantic-view skill will:
Call FastGen to auto-generate the semantic model YAML from the actual Snowflake metadata
Validate the YAML via SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(..., TRUE)
Present the generated model for review
Offer options: audit, deploy, or refine
Step 5c: Deploy each semantic view
Use the semantic-view skill's upload workflow to deploy each approved semantic view to Snowflake:
-- The skill handles this via upload_semantic_view_yaml.py, which runs:
-- CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML('{DATABASE}.{SCHEMA}', $${yaml}$$, FALSE);
Verify deployment:
SHOW SEMANTIC VIEWS IN SCHEMA {DATABASE}.{SCHEMA};
Step 5d: Test each semantic view
For each deployed semantic view, invoke the semantic-view skill in audit/test mode to validate it returns correct results:
skill: semantic-view
Context: Audit the semantic view {DATABASE}.{SCHEMA}.{SEMANTIC_VIEW_NAME}
- Run 2-3 of the business questions from Phase 1 against each semantic view
- Verify the generated SQL executes without errors
- Verify the results contain expected data (non-empty, reasonable values)
- If any query fails, use the skill's refine workflow to fix the semantic model
The semantic-view skill will test queries via Cortex Analyst, show generated SQL and results, and flag any issues.
Step 5e: Record describe metadata for Phase 6
For EACH deployed ontology-layer semantic view, run cortex semantic-views describe and record the output. This metadata feeds into Phase 6's tool descriptions for the agent:
FOR EACH deployed semantic view from Step 5b:
cortex semantic-views describe {DATABASE}.{SCHEMA}.{SEMANTIC_VIEW_NAME}
Record for each: description, table count, dimension/fact/metric counts, and key dimension names. This enables Phase 6 to generate rich, data-aware tool descriptions without relying on session memory of what was created in this phase.
Self-check before gate: Verify all semantic views are complete:
SHOW SEMANTIC VIEWS IN SCHEMA {DATABASE}.{SCHEMA} returns one row per selected model
Each semantic view name matches expected naming ({ONTOLOGY_NAME}_KG_MODEL, _ONTOLOGY_MODEL, _METADATA_MODEL)
Step 5d tests passed — at least 2 business questions per view returned non-empty results
No unresolved validation errors from the semantic-view skill
If any semantic view is missing or tests failed, fix before presenting the gate.
⚠️ MANDATORY STOPPING POINT — DO NOT PROCEED TO PHASE 6: When the last semantic-view skill invocation finishes, you MUST STOP HERE. Do NOT invoke the cortex-agent skill, do NOT begin agent creation, do NOT start discovering deployed assets. You MUST use ask_user_question:
ask_user_question: "All semantic views are deployed. Please review and test them. Ready to proceed to Phase 6 (Agent creation)?"
Options:
- "Yes, proceed to Phase 6"
- "No, I need to fix a semantic view"
STOP. Wait for the user's answer. Do not read ahead. Do not begin Phase 6 until the user selects an option above.
GATE CHECK: You should only be here if the user explicitly approved the Phase 5 gate above. If you arrived here without asking the user, STOP and go back to present the Phase 5 gate question.
Step 6-pre: Discover deployed assets
Before building anything, dynamically discover all assets from prior phases using Snowflake discovery commands. This makes Phase 6 resilient to session resume — it works even if earlier phases ran in a different session.
1. Discover semantic views:
cortex semantic-views list --like '%' --in database {DATABASE} schema {SCHEMA}
Record for each: name, FQN, description, table references, dimension/fact/metric counts, and key dimension names.
2. Classify each semantic view by role using the describe output:
Base: covers source tables from Phase 1 (table names are original source tables, not VW_ONT_* or V_* views)
KG: covers V_* typed views and/or KG_NODE/KG_EDGE
Ontology: covers VW_ONT_* abstract views
Metadata: covers ONT_* introspection tables
Use the table references from the describe output to classify. If ambiguous, check the semantic view name for hints (_BASE, _KG_MODEL, _ONTOLOGY_MODEL, _METADATA_MODEL).
3. Discover graph UDFs (if KG path):
SHOW USER FUNCTIONS LIKE '%_TOOL' IN SCHEMA {DATABASE}.{SCHEMA};
For EACH function found, run:
DESCRIBE FUNCTION {DATABASE}.{SCHEMA}.{FUNCTION_NAME}(VARCHAR);
Record: name, FQN, parameters, return type.
4. Discover SPCS service functions (if any):
SHOW FUNCTIONS LIKE '%GRAPH_%' IN SCHEMA {DATABASE}.{SCHEMA};
Step 6a: Build the tool inventory from discovered assets
Build TOOL_INVENTORY from the discovery results — NOT from memory of earlier phases:
TOOL_INVENTORY = []
For each classified semantic view:
Base semantic view → add base_query_tool (type: cortex_analyst_text_to_sql, FQN from discovery)
KG semantic view → add kg_query_tool (type: cortex_analyst_text_to_sql, FQN from discovery)
For each SPCS service function → add to TOOL_INVENTORY with type generic.
Only add tools for assets that were actually found by discovery. Do NOT add tools based on session memory or assumptions about what should exist.
Cross-check: Verify every discovered semantic view maps to exactly one tool. Flag any unclassified semantic views or missing expected assets.
Record the final inventory with discovery metadata:
TOOL_INVENTORY = [
{ name: "base_query_tool", type: "cortex_analyst_text_to_sql",
source_fqn: "{FQN}", role: "base",
describe_metadata: { tables: N, dimensions: N, facts: N, metrics: N,
key_dimensions: [...], description: "..." } },
...
]
Total tools: {N}
Present this to the user before proceeding: "I discovered {N} deployable assets. The agent will have {N} tools: {list}. Correct?"
Step 6b: Check for graph analytics tools (KG path only)
If KG path, check what graph tools were deployed in Phase 4:
SQL UDF graph traversal tools: If 07_graph_traversal_tools.sql was generated and deployed, the 4 UDFs (EXPAND_DESCENDANTS_TOOL, GET_ANCESTORS_TOOL, GET_HIERARCHY_PATH_TOOL, GET_DIRECT_CHILDREN_TOOL) are already available and should be registered as generic tools in the agent.
SPCS graph analytics tools: If user also wants advanced graph algorithms (centrality, community detection), offer SPCS:
ask_user_question: "Include SPCS graph analytics tools in the agent? (SQL hierarchy tools are already deployed)"
Options:
- "Yes": Adds centrality, community detection, shortest path tools (requires SPCS container service)
- "No": Agent uses semantic view tools + SQL graph traversal tools only
If SPCS selected, generate SPCS scaffolding only (no agent spec):
spcs_graph_service.py — NetworkX-based graph analytics service
spcs_setup.sql — SPCS service creation, compute pool, service functions
Guide the user through deploying the SPCS service before proceeding:
-- Execute spcs_setup.sql to create the compute pool, image repository,
-- service, and service functions in {DATABASE}.{SCHEMA}
Step 6c: Invoke the cortex-agent skill to create the agent
Invoke the native cortex-agent skill in create mode. Provide the discovered tool inventory and domain context — let the skill generate the orchestration instructions, tool descriptions, and agent spec. Do NOT hand-write the orchestration prompt.
skill: cortex-agent
Context to provide when the skill asks:
- Agent name: {ONTOLOGY_NAME}_AGENT
- Target database: {DATABASE}
- Target schema: {SCHEMA}
- Domain: {ONTOLOGY_NAME} — {1-sentence domain description from Phase 1}
- Tool inventory with discovery metadata (from Step 6-pre/6a):
FOR EACH tool in TOOL_INVENTORY, provide:
Tool #{N}: {tool.name}
Type: {tool.type} (cortex_analyst_text_to_sql | generic)
Source: {tool.source_fqn}
Role: {tool.role} (base | kg | ontology | metadata | graph_traversal)
Discovery metadata:
- For semantic view tools: "{tool.describe_metadata.description}.
Covers {tool.describe_metadata.tables} tables with
{tool.describe_metadata.dimensions} dimensions,
{tool.describe_metadata.facts} facts,
{tool.describe_metadata.metrics} metrics.
Key dimensions: {tool.describe_metadata.key_dimensions}."
- For graph UDF tools: "Parameters: {tool.parameters}.
Returns: {tool.return_type}."
Sample questions this tool should answer (from Phase 1 business questions):
- "{question mapped to this tool's role}"
- "{another question mapped to this tool's role}"
- Routing principles (high-level guidance for the skill to use when generating orchestration):
* base tools: concrete source data — entity lookups, aggregations, filtering
* ontology tools: cross-type reasoning via abstract views
* kg tools: KG entity/relationship queries via typed views
* metadata tools: governance, structure introspection, data quality
* graph tools: hierarchy traversal, path finding, ancestor/descendant exploration
(include only lines for tool roles present in TOOL_INVENTORY)
- The `cortex-agent` skill should generate the full orchestration instructions,
tool descriptions (with "When to Use" / "When NOT to Use" routing hints),
and the complete agent spec including tool_resources.
- For graph traversal UDF tools, note these are SQL UDFs (not stored procedures),
so tool_resources should use "type": "function".
The cortex-agent skill will:
Verify admin setup (warehouse, role, permissions)
Use the tool inventory and discovery metadata to generate rich tool descriptions
Generate orchestration instructions with proper routing between all tools
Build the agent spec with all tools, tool_resources, and orchestration
Execute CREATE OR REPLACE AGENT via $spec$ DDL
Run verification queries to confirm the agent is functional
After the skill creates the agent, REVIEW the orchestration:
Every tool in TOOL_INVENTORY has a routing block in the orchestration
No routing block references a tool NOT in TOOL_INVENTORY
Sample questions from Phase 1 would route correctly based on the generated rules
"When NOT to Use" in each tool description only references other tools that exist
If the agent has >2 tools, the orchestration includes multi-tool combination patterns
If any issue found, ask the cortex-agent skill to refine — do NOT hand-edit the orchestration yourself.
Step 6d: Add SPCS graph tools to the agent (if selected in 6b)
If SPCS graph tools were selected in Step 6b, invoke the cortex-agent skill again in edit mode to add the SPCS service functions as stored-procedure tools:
skill: cortex-agent
Context: Edit agent {DATABASE}.{SCHEMA}.{ONTOLOGY_NAME}_AGENT
- Add tools: Each SPCS service function as a stored-procedure tool
(e.g., graph_centrality, graph_community_detection, graph_shortest_path)
- Update orchestration instructions to include graph analytics routing
Each tool name in TOOL_INVENTORY is registered in the agent (no missing, no extras)
Each semantic view FQN in the agent matches the discovered FQN from Step 6-pre
Each graph UDF function identifier matches the discovered FQN
Orchestration mentions every tool by name with routing guidance
3. Test with Phase 1 business questions via cortex-agent skill:
Invoke the cortex-agent skill in test/debug mode. Map each business question from Phase 1 to its expected tool based on role:
skill: cortex-agent
Context: Test the agent {DATABASE}.{SCHEMA}.{ONTOLOGY_NAME}_AGENT
- The agent has {len(TOOL_INVENTORY)} tools: {list from TOOL_INVENTORY}
- Run one sample question per tool, using Phase 1 business questions:
Map each question to its expected tool by role:
- Source data questions → base_query_tool
- Cross-type reasoning questions → ontology_query_tool (if in inventory)
- KG entity/relationship questions → kg_query_tool (if in inventory)
- Governance/coverage questions → metadata_query_tool (if in inventory)
- Hierarchy questions → graph tools (if in inventory)
- Verify each question routes to the CORRECT tool
- Verify responses contain expected data (non-empty, reasonable values)
4. If routing is wrong: Ask the cortex-agent skill to refine the orchestration — do NOT hand-edit the orchestration prompt yourself.
Self-check before gate: Verify the agent matches TOOL_INVENTORY:
Each tool name in TOOL_INVENTORY appears in the agent description
Step 6e tests passed — each tool received at least one correctly-routed question with non-empty response
No unresolved errors from the cortex-agent skill
If agent is missing, has wrong tool count, or tests failed, fix before presenting the gate.
⚠️ MANDATORY STOPPING POINT — DO NOT PROCEED TO PHASE 7: When the cortex-agent skill finishes, you MUST STOP HERE. Do NOT begin validation, do NOT run validation queries. You MUST use ask_user_question:
ask_user_question: "The Cortex Agent is deployed. Please test it with the questions above. Ready to proceed to Phase 7 (Validation)?"
Options:
- "Yes, proceed to Phase 7"
- "No, I need to fix the agent"
STOP. Wait for the user's answer. Do not read ahead. Do not begin Phase 7 until the user selects an option above.
Phase 7: End-to-End Validation
GATE CHECK: You should only be here if the user explicitly approved the Phase 6 gate above. If you arrived here without asking the user, STOP and go back to present the Phase 6 gate question.
Note: SQL artifacts (L1-L3) were deployed in Phase 4b. Base semantic view (L4-base) was deployed in Phase 4.5. Ontology-layer semantic views (L4-ontology) and Cortex Agent (L5) were deployed by native skills in Phases 5 and 6. This phase validates the full stack.
Step 7a: Validate all layers
Run validation queries across the full stack:
L1 Physical: Row counts for KG_NODE and KG_EDGE (if KG path)
L2 Metadata: Row counts for all ~22 ONT_* tables
L3 Abstract Views: Sample queries against each VW_ONT_* view
L4 Semantic Views: Verify via SHOW SEMANTIC VIEWS IN SCHEMA {DATABASE}.{SCHEMA} — must include base semantic (Phase 4.5) + ontology-layer semantics (Phase 5)
L5 Cortex Agent: Run one end-to-end question through the agent:
Present a summary table of all deployed objects with row counts and status.
Self-check before gate: Verify the full stack is healthy:
L1: KG_NODE and KG_EDGE have non-zero row counts (if KG path)
L2: All ~22 ONT_* tables exist and have seed data (row count > 0 for at least ONT_CLASS, ONT_RELATION_DEF, ONT_CLASS_MAP)
L3: Every VW_ONT_* view returns rows without errors
L4: SHOW SEMANTIC VIEWS count matches expected: 1 base (Phase 4.5) + N ontology-layer (Phase 5 selections)
L5: Agent query returned a meaningful response (not an error or empty)
If any layer fails validation, report the specific failure and attempt to fix (re-execute SQL, re-deploy view, etc.) before presenting the gate. Do NOT present a "deployment complete" gate with known failures.
⚠️ MANDATORY STOPPING POINT — FINAL GATE: You MUST use ask_user_question:
ask_user_question: "End-to-end validation complete. All layers are deployed and verified. Is the deployment complete?"
Options:
- "Yes, deployment is complete"
- "No, there are issues to address"
Degrees of Freedom
Decision
Freedom
Default
KG vs direct-table path
High
Ask in Phase 1
Use existing semantic vs create new base
High
Ask in Phase 1 (Step 1b)
Which ontology-layer semantic models (1-3)
High
All 3 if KG, 2 if direct
Graph analytics tools
High
Only offered if KG path
Ontology class/relation design
Medium
AI recommends, user confirms
View naming convention
Low
VW_ONT_ prefix
Agent routing instructions
Low
Auto-generated from model descriptions
Error Handling
No tables found: Verify DATABASE.SCHEMA exists and user has SELECT permission
SQL execution error: Show exact error, suggest GRANT statements if permission issue
Empty views: Verify filter conditions in ONT_CLASS_MAP match actual data values
Semantic model validation failure: Check that all referenced views exist and have data
Session Resume
If invoked mid-session, use discovery commands to detect the current state from Snowflake — do not rely on session memory or local files alone.
Discovery-first state detection:
Check local artifacts: /tmp/generated/, /tmp/ontology_parsed/
Check Snowflake objects:
cortex semantic-views list --like '%' --in database {DATABASE} schema {SCHEMA}
cortex agents list --like '%' --in database {DATABASE} schema {SCHEMA}
SHOW VIEWS LIKE 'VW_ONT_%' IN SCHEMA {DATABASE}.{SCHEMA};
SHOW VIEWS LIKE 'V_%' IN SCHEMA {DATABASE}.{SCHEMA};
SHOW TABLES LIKE 'ONT_%' IN SCHEMA {DATABASE}.{SCHEMA};
SHOW USER FUNCTIONS LIKE '%_TOOL' IN SCHEMA {DATABASE}.{SCHEMA};
Resume decision tree:
If classes.json exists → resume from Phase 3 (visualize)
If *.sql files exist but not yet executed → resume from Phase 4b (deploy SQL)
If VW_ONT_* views exist but no base semantic view → resume from Phase 4.5 (base semantic via semantic-view skill)
If base semantic view exists but no ontology-layer semantic views → resume from Phase 5 (ontology semantic views via semantic-view skill)
If ontology-layer semantic views exist but no agent → resume from Phase 6 (agent via cortex-agent skill)
Phase 6 will run its own discovery (Step 6-pre) to build TOOL_INVENTORY from Snowflake, so no prior session context is needed
If agent exists → resume from Phase 7 (validation)
To classify semantic views, run cortex semantic-views describe {FQN} for each and check the table references:
Covers source tables (not VW_ONT_* or V_*) → base semantic view
Covers V_* views → KG semantic view
Covers VW_ONT_* views → ontology semantic view
Covers ONT_* tables → metadata semantic view
Always confirm the detected state with the user before proceeding.
Output Files
SQL artifacts are saved to /tmp/generated/ during the session. Semantic views and the Cortex Agent are deployed directly to Snowflake by the native skills (no local YAML or JSON files).
/tmp/generated/
├── 01_physical_layer.sql (KG path only)
├── 02_concrete_views.sql (V_{CLASS}, V_{REL} typed views)
├── 03_metadata_tables.sql (~22 tables + auto-populated seed data)
├── 04_abstract_views.sql
├── 05_view_generator_sp.sql
├── 06_inference_engine.sql (optional, KG path + user opted in)
├── 07_graph_traversal_tools.sql (optional, KG path + user opted in — 4 SQL UDF tools)
├── spcs_graph_service.py (if SPCS graph tools selected)
└── spcs_setup.sql (if SPCS graph tools selected)
Deployed directly to Snowflake (via native skills):
├── Base semantic view (created by `semantic-view` skill in Phase 4.5, or existing)
├── Ontology semantic views (created by `semantic-view` skill in Phase 5)
└── Cortex Agent (created by `cortex-agent` skill in Phase 6)
