AI Observability Solution

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search

This page describes how to configure the QPR ProcessAnalyzer AI Observability solution. The solution transforms Snowflake AI observability data generated by AI agents using dynamic tables into a format suitable for a QPR ProcessAnalyzer model. The dynamic tables refresh automatically on the defined schedule and keep the process mining model up-to-date.

Installation overview

Configure the solution in the following order:

  1. Install the QPR ProcessAnalyzer Native App, or use a QPR ProcessAnalyzer connected to a Snowflake account.
  2. Create dynamic tables to the Snowflake account, transforming the AI observability data for QPR ProcessAnalyzer (as instructed below).
  3. Give the Native App (or the Snowflake user in case of a connected app) permissions to read the dynamic tables (Native App access to data).
  4. Import a project into QPR ProcessAnalyzer, containing the datatables, models and dashboards for the AI observability solution (Importing project).
  5. Configure the imported project to use the Snowflake schema where the dynamic tables are located (Project Properties dialog). Select the project and click Properties. In the opening dialog, select the Data location tab, and define the Snowflake database name and Snowflake schema name. Click Save.

The supplied pipeline uses a one-day target lag for the tables consumed by the QPR ProcessAnalyzer model. Adjust target lag according to the requirements of the implemented solution. The more often the dynamic tables are refreshed, the more recent data is available in the solution, but the Snowflake warehouse costs are also higher.

The solution has QPR ProcessAnalyzer datatables with the same names for each of the Snowflake dynamic tables. The following dynamic tables will be created:

  • AI_OBSERVABILITY_BASE_EVENTS: parsed event-level observability data.
  • AI_OBSERVABILITY_TRACE_EVENTS: events having a trace identifier.
  • AI_OBSERVABILITY_TRACE_CASES: one aggregated row for each trace, representing one agent invocation.
  • AI_OBSERVABILITY_THREAD_EVENTS: events belonging to a conversation thread.
  • AI_OBSERVABILITY_THREAD_CASES: one aggregated row for each conversation thread.

Required Snowflake resources and permissions

Create or select the following Snowflake resources:

  • A database and schema for the dynamic tables.
  • A warehouse used to refresh the dynamic tables.
  • A role that owns and refreshes the dynamic tables.

The role creating the tables needs appropriate access to the target database, target schema, and warehouse. It also needs permission to create dynamic tables in the target schema. The role must retain the required privileges for the source used by the selected alternative and for AI_CLASSIFY.

Choose data access option

Two alternatives are available for reading the AI observability data used by the dynamic tables:

  • Direct event-table access reads all account-level telemetry from the SNOWFLAKE.LOCAL.AI_OBSERVABILITY_EVENTS view.
  • Scoped function access reads events for one specified Snowflake Cortex Agent, External Agent, or Cortex Search service using the SNOWFLAKE.LOCAL.GET_AI_OBSERVABILITY_EVENTS function.

Use direct event-table access when the solution needs account-wide observability analysis, such as analysing Snowflake CoWork activity or multiple agents without maintaining a list of objects. This approach bypasses object-level scoping and is intended for limited administrative scenarios. The source table includes telemetry such as conversation threads, traces, spans, inputs and outputs, feedback, evaluation runs, and eligible Cortex Search request logs.

Use scoped function access when the solution is intended to monitor a particular Cortex Agent, External Agent, or Cortex Search service. The function returns only events for the that agent without giving access to any other data. This is Snowflake's recommended access pattern for routine monitoring. This is preferable where account-wide raw-table access is not appropriate.

Alternative 1: Direct access to AI_OBSERVABILITY_EVENTS

Required permissions

A users with the ACCOUNTADMIN role must grant the following privileges to the role that creates and refreshes the dynamic tables: 

USE ROLE ACCOUNTADMIN;
GRANT READ UNREDACTED AI OBSERVABILITY EVENTS TABLE ON ACCOUNT TO ROLE <pa_role>;
GRANT APPLICATION ROLE SNOWFLAKE.AI_OBSERVABILITY_READER TO ROLE <pa_role>;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE <pa_role>;

SNOWFLAKE.AI_OBSERVABILITY_READER grants read access to the raw AI observability event table. READ UNREDACTED AI OBSERVABILITY EVENTS TABLE permits access to unredacted conversation content, tool inputs and outputs, and feedback text; without it, metadata such as tool names, token usage, latency, model name, and error severity remains available.

The SNOWFLAKE.CORTEX_USER database role is required by the supplied pipeline because AI_OBSERVABILITY_TRACE_CASES calls AI_CLASSIFY. The DDL uses this function to classify the initial agent message into intent categories.

In addition, grant or otherwise provide the role with the required privileges to use the target database, schema, and pipeline warehouse, and to create dynamic tables in the target schema.

Create dynamic tables

The following Snowflake SQL script creates the needed dynamic tables. The script uses <your_database>, QPR_AI_OBSERVABILITY_SOLUTION, <pa_role>, and <your_warehouse>. Replace these values before execution. 

-- ============================================================================
-- AI Observability: Single-Root Dynamic Table Pipeline
-- ============================================================================
--
-- Pipeline architecture:
--
--   SNOWFLAKE.LOCAL.AI_OBSERVABILITY_EVENTS  (raw, append-only)
--       |
--       v
--   AI_OBSERVABILITY_BASE_EVENTS  (single incremental reader, flattens all JSON once)
--       |
--       +---> AI_OBSERVABILITY_TRACE_EVENTS    (event-level rows filtered to trace scope)
--       +---> AI_OBSERVABILITY_TRACE_CASES     (one row per trace_id, with AI_CLASSIFY)
--       +---> AI_OBSERVABILITY_THREAD_EVENTS   (event-level rows filtered to thread scope)
--       +---> AI_OBSERVABILITY_THREAD_CASES    (one row per thread_id)
--
-- Benefits of the single-root design:
--   - Consistency: all downstream tables read the same materialized snapshot.
--     A trace_id or thread_id in any table is guaranteed to exist in all others.
--   - JSON parsing done once: RECORD_ATTRIBUTES, TRACE, RESOURCE_ATTRIBUTES,
--     and VALUE are extracted in AI_OBSERVABILITY_BASE_EVENTS only.
--   - AI_CLASSIFY runs once per trace (not once per raw event row).
--   - TARGET_LAG = DOWNSTREAM on the base means it refreshes only when
--     children need data, then all children see the same data_timestamp.
--
-- Parameterization:
--   Edit only the configuration block below. PIPELINE_WAREHOUSE is used by
--   every dynamic table in this file
--   Note: there is a target_lag of 1 day hardcoded into the tables PA 
--   Model uses.
--
-- Grants:
--   The role that creates these dynamic tables is also the role the
--   ProcessAnalyzer (PA) user assumes to query them. It needs the account
--   privilege for unredacted observability content, plus 
--   APPLICATION ROLE SNOWFLAKE.AI_OBSERVABILITY_READER to actually read the logs.
--   Database role CORTEX_USER is used for AI_CLASSIFY (used in AI_OBSERVABILITY_TRACE_CASES).
-- ============================================================================

-- ----------------------------------------------------------------------------
-- Grants for the pipeline role (run once, as ACCOUNTADMIN)
-- Grant roles to ProcessAnalyzer role
-- ----------------------------------------------------------------------------

GRANT READ UNREDACTED AI OBSERVABILITY EVENTS TABLE ON ACCOUNT TO <pa_role>;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE <pa_role>;
GRANT APPLICATION ROLE SNOWFLAKE.AI_OBSERVABILITY_READER TO ROLE <pa_role>;

USE ROLE <pa_role>;
USE DATABASE <your_database>;
USE SCHEMA QPR_AI_OBSERVABILITY_SOLUTION;
SET PIPELINE_WAREHOUSE  = '<your_warehouse>';

-- 1. BASE_EVENTS — The single root
-- ============================================================================
-- Reads raw AI observability events, parses all JSON fields into typed columns.
-- This is the only table that touches SNOWFLAKE.LOCAL.AI_OBSERVABILITY_EVENTS.
-- All other tables in this file read from here.
-- Uses TARGET_LAG = DOWNSTREAM so it only refreshes when a child table needs data.
-- ============================================================================

CREATE OR REPLACE DYNAMIC TABLE AI_OBSERVABILITY_BASE_EVENTS
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE = $PIPELINE_WAREHOUSE
    REFRESH_MODE = AUTO
    INITIALIZE = ON_CREATE
AS
WITH parsed AS (
    SELECT
        e.*,
        TRY_PARSE_JSON(e.VALUE) AS val_json,
        PARSE_JSON(TRY_PARSE_JSON(e.VALUE):"snow.ai.observability.request_body"::STRING) AS req_json,
        TRY_PARSE_JSON(TRY_PARSE_JSON(e.VALUE):"snow.ai.observability.response"::STRING) AS resp_json
    FROM SNOWFLAKE.LOCAL.AI_OBSERVABILITY_EVENTS e
)
SELECT
    -- === Timestamps ===
    TIMESTAMP::TIMESTAMP_NTZ AS TIMESTAMP,                    -- when the event was recorded
    START_TIMESTAMP::TIMESTAMP_NTZ AS START_TIMESTAMP,        -- when the span started
    OBSERVED_TIMESTAMP::TIMESTAMP_NTZ AS OBSERVED_TIMESTAMP,  -- when the collector received the event
    RECORD_TYPE,

    -- === Trace & Span identifiers ===
    TRACE:"trace_id"::STRING AS TRACE_ID,   -- groups all events for one agent invocation
    TRACE:"span_id"::STRING AS SPAN_ID,     -- unique span within the trace

    -- === Session & User context (from resource attributes) ===
    RESOURCE_ATTRIBUTES:"snow.session.id"::STRING AS SESSION_ID,
    RESOURCE_ATTRIBUTES:"snow.session.role.primary.id"::STRING AS ROLE_ID,
    RESOURCE_ATTRIBUTES:"snow.session.role.primary.name"::STRING AS ROLE_NAME,
    RESOURCE_ATTRIBUTES:"snow.user.id"::STRING AS USER_ID,
    RESOURCE_ATTRIBUTES:"snow.user.name"::STRING AS USER_NAME,

    -- === Scope ===
    SCOPE:"name"::STRING AS SCOPE_NAME,     -- the instrumentation scope (e.g. agent name)

    -- === Record (span metadata from RECORD column) ===
    RECORD:"kind"::STRING AS RECORD_KIND,
    RECORD:"parent_span_id"::STRING AS RECORD_PARENT_SPAN_ID,
    RECORD:"name"::STRING AS RECORD_NAME,                            -- span/event name with ordering suffix
    RECORD:"dropped_attributes_count"::NUMBER AS RECORD_DROPPED_ATTRIBUTES_COUNT,
    RECORD:"status":"code"::STRING AS RECORD_STATUS_CODE,

    -- === VALUE: top-level request/response metadata ===
    val_json:"snow.ai.observability.response_status_code"::NUMBER AS VALUE_RESPONSE_STATUS_CODE,
    val_json:"snow.ai.observability.response_time_ms"::FLOAT AS VALUE_RESPONSE_TIME_MS,

    -- === VALUE > request_body: parsed request fields ===
    req_json:"thread_id"::STRING AS REQUEST_THREAD_ID,               -- thread ID from request payload
    req_json:"origin_application"::STRING AS REQUEST_ORIGIN_APP,     -- e.g. Snowsight, VS Code
    req_json:"parent_message_id"::STRING AS REQUEST_PARENT_MESSAGE_ID,
    req_json:"background"::BOOLEAN AS REQUEST_IS_BACKGROUND,         -- background/async request
    req_json:"stream"::BOOLEAN AS REQUEST_IS_STREAMING,              -- streaming response enabled
    req_json:"tool_choice":"type"::STRING AS REQUEST_TOOL_CHOICE_TYPE,
    req_json:"skill_choice":"type"::STRING AS REQUEST_SKILL_CHOICE_TYPE,
    req_json:"messages"[0]:"content"[0]:"text"::STRING AS REQUEST_USER_MESSAGE,  -- the user's question/task
    req_json:"messages"[0]:"role"::STRING AS REQUEST_MESSAGE_ROLE,
    req_json:"experimental":"fallbackWarehouse"::STRING AS REQUEST_FALLBACK_WAREHOUSE,
    req_json:"experimental":"snowflakeIntelligence"::BOOLEAN AS REQUEST_IS_SF_INTELLIGENCE,
    req_json:"experimental":"reasoningAgentFlowType"::STRING AS REQUEST_REASONING_FLOW_TYPE,
    req_json:"experimental":"reasoningAgentToolConfig":"orchestrationType"::STRING AS REQUEST_ORCHESTRATION_TYPE,
    req_json:"experimental":"responseSchemaVersion"::STRING AS REQUEST_RESPONSE_SCHEMA_VERSION,

    -- === VALUE > response: parsed response fields ===
    resp_json:"role"::STRING AS RESPONSE_ROLE,
    ARRAY_SIZE(resp_json:"content") AS RESPONSE_CONTENT_ITEMS_COUNT,

    -- === Core identifiers (from RECORD_ATTRIBUTES) ===
    RECORD_ATTRIBUTES:"ai.observability.input_id"::STRING AS INPUT_ID,
    RECORD_ATTRIBUTES:"ai.observability.record_id"::STRING AS RECORD_ID,
    RECORD_ATTRIBUTES:"ai.observability.record_root.input"::STRING AS RECORD_ROOT_INPUT,
    RECORD_ATTRIBUTES:"ai.observability.record_root.output"::STRING AS RECORD_ROOT_OUTPUT,
    RECORD_ATTRIBUTES:"ai.observability.span_type"::STRING AS SPAN_TYPE,
    RECORD_ATTRIBUTES:"request_id"::STRING AS REQUEST_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.span_kind"::STRING AS SPAN_KIND,

    -- === Coding agent context ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.coding_agent.client_type"::STRING AS CODING_AGENT_CLIENT_TYPE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.coding_agent.origin_application"::STRING AS CODING_AGENT_ORIGIN_APP,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.coding_agent.private_mode"::STRING AS CODING_AGENT_PRIVATE_MODE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.coding_agent.session_id"::STRING AS CODING_AGENT_SESSION_ID,

    -- === Agent turn-level attributes ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.duration"::FLOAT AS AGENT_DURATION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.first_message_in_thread"::STRING AS AGENT_FIRST_MSG_IN_THREAD,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.message_id"::STRING AS AGENT_MESSAGE_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.messages"::STRING AS AGENT_MESSAGES,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.parent_message_id"::STRING AS AGENT_PARENT_MESSAGE_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.request_id"::STRING AS AGENT_REQUEST_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.research_mode"::STRING AS AGENT_RESEARCH_MODE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.response"::STRING AS AGENT_RESPONSE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.status"::STRING AS AGENT_STATUS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.status.code"::STRING AS AGENT_STATUS_CODE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.status.description"::STRING AS AGENT_STATUS_DESCRIPTION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.suggested_queries"::STRING AS AGENT_SUGGESTED_QUERIES,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.thinking_response"::STRING AS AGENT_THINKING_RESPONSE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.thread_id"::STRING AS AGENT_THREAD_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.thread_id.messages"::STRING AS AGENT_THREAD_MESSAGES,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.warnings"::STRING AS AGENT_WARNINGS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool_choice.input_timestamp"::STRING AS TOOL_CHOICE_INPUT_TS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool_choice.type"::STRING AS TOOL_CHOICE_TYPE,

    -- === Planning: LLM reasoning step attributes ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.duration"::FLOAT AS PLANNING_DURATION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.instruction"::STRING AS PLANNING_INSTRUCTION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.messages"::STRING AS PLANNING_MESSAGES,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.model"::STRING AS PLANNING_MODEL,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.query"::STRING AS PLANNING_QUERY,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.request_id"::STRING AS PLANNING_REQUEST_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.response"::STRING AS PLANNING_RESPONSE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.status"::STRING AS PLANNING_STATUS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.status.code"::STRING AS PLANNING_STATUS_CODE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.step_number"::NUMBER AS PLANNING_STEP_NUMBER,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.thinking_response"::STRING AS PLANNING_THINKING_RESPONSE,

    -- === Planning: token usage per LLM call ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.token_count.cache_read_input"::NUMBER AS PLANNING_TOKENS_CACHE_READ,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.token_count.cache_write_input"::NUMBER AS PLANNING_TOKENS_CACHE_WRITE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.token_count.input"::NUMBER AS PLANNING_TOKENS_INPUT,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.token_count.output"::NUMBER AS PLANNING_TOKENS_OUTPUT,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.token_count.plan"::NUMBER AS PLANNING_TOKENS_PLAN,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.token_count.total"::NUMBER AS PLANNING_TOKENS_TOTAL,

    -- === Planning: tool the LLM decided to call ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool.description"::STRING AS PLANNING_TOOL_DESCRIPTION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool.name"::STRING AS PLANNING_TOOL_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool.parameters"::STRING AS PLANNING_TOOL_PARAMETERS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool.type"::STRING AS PLANNING_TOOL_TYPE,

    -- === Planning: tool execution results ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_execution.argument.name"::STRING AS PLANNING_TOOL_EXEC_ARG_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_execution.argument.value"::STRING AS PLANNING_TOOL_EXEC_ARG_VALUE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_execution.id"::STRING AS PLANNING_TOOL_EXEC_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_execution.name"::STRING AS PLANNING_TOOL_EXEC_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_execution.results"::STRING AS PLANNING_TOOL_EXEC_RESULTS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_execution.type"::STRING AS PLANNING_TOOL_EXEC_TYPE,

    -- === Planning: tool selection metadata ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_selection.argument.name"::STRING AS PLANNING_TOOL_SEL_ARG_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_selection.argument.value"::STRING AS PLANNING_TOOL_SEL_ARG_VALUE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_selection.description"::STRING AS PLANNING_TOOL_SEL_DESCRIPTION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_selection.id"::STRING AS PLANNING_TOOL_SEL_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_selection.is_client_side"::STRING AS PLANNING_TOOL_SEL_IS_CLIENT_SIDE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_selection.name"::STRING AS PLANNING_TOOL_SEL_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.tool_selection.type"::STRING AS PLANNING_TOOL_SEL_TYPE,

    -- === Tool: Bash (cortex CLI commands) ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.bash.cortex_sub_command"::STRING AS TOOL_BASH_CORTEX_CMD,

    -- === Tool: Chart generation ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.data"::STRING AS TOOL_CHART_DATA,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.data_source"::STRING AS TOOL_CHART_DATA_SOURCE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.duration"::FLOAT AS TOOL_CHART_DURATION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.input_chart_spec"::STRING AS TOOL_CHART_INPUT_SPEC,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.request_id"::STRING AS TOOL_CHART_REQUEST_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.response"::STRING AS TOOL_CHART_RESPONSE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.response.type"::STRING AS TOOL_CHART_RESPONSE_TYPE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.status"::STRING AS TOOL_CHART_STATUS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.status.code"::STRING AS TOOL_CHART_STATUS_CODE,

    -- === Tool: Code execution (Python/JS in sandbox) ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.argument.name"::STRING AS TOOL_CODE_EXEC_ARG_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.argument.value"::STRING AS TOOL_CODE_EXEC_ARG_VALUE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.duration"::FLOAT AS TOOL_CODE_EXEC_DURATION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.name"::STRING AS TOOL_CODE_EXEC_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.request_id"::STRING AS TOOL_CODE_EXEC_REQUEST_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.status"::STRING AS TOOL_CODE_EXEC_STATUS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.status.code"::STRING AS TOOL_CODE_EXEC_STATUS_CODE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.status.description"::STRING AS TOOL_CODE_EXEC_STATUS_DESC,

    -- === Tool: Custom tools (user-defined) ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.argument.name"::STRING AS TOOL_CUSTOM_ARG_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.argument.value"::STRING AS TOOL_CUSTOM_ARG_VALUE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.duration"::FLOAT AS TOOL_CUSTOM_DURATION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.name"::STRING AS TOOL_CUSTOM_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.request_id"::STRING AS TOOL_CUSTOM_REQUEST_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.status"::STRING AS TOOL_CUSTOM_STATUS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.status.code"::STRING AS TOOL_CUSTOM_STATUS_CODE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.status.description"::STRING AS TOOL_CUSTOM_STATUS_DESC,

    -- === Tool: Generic execution (fallback for untyped tools) ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.execution.duration"::FLOAT AS TOOL_EXECUTION_DURATION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.execution.status"::STRING AS TOOL_EXECUTION_STATUS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.execution.status.code"::STRING AS TOOL_EXECUTION_STATUS_CODE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.execution.status.description"::STRING AS TOOL_EXECUTION_STATUS_DESC,

    -- === Tool: Cortex Analyst / Semantic context ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.cortex_analyst.semantic_model"::STRING AS TOOL_ANALYST_SEMANTIC_MODEL,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.id"::STRING AS TOOL_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.duration"::FLOAT AS TOOL_SEM_CTX_DURATION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.request_id"::STRING AS TOOL_SEM_CTX_REQUEST_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.semantic_model"::STRING AS TOOL_SEM_CTX_MODEL,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.status"::STRING AS TOOL_SEM_CTX_STATUS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.status.code"::STRING AS TOOL_SEM_CTX_STATUS_CODE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.status.description"::STRING AS TOOL_SEM_CTX_STATUS_DESC,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.tables"::STRING AS TOOL_SEM_CTX_TABLES,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.user_question"::STRING AS TOOL_SEM_CTX_QUESTION,

    -- === Tool: MCP (Model Context Protocol external tools) ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.argument.name"::STRING AS TOOL_MCP_ARG_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.argument.value"::STRING AS TOOL_MCP_ARG_VALUE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.duration"::FLOAT AS TOOL_MCP_DURATION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.name"::STRING AS TOOL_MCP_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.request_id"::STRING AS TOOL_MCP_REQUEST_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.results"::STRING AS TOOL_MCP_RESULTS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.server_category"::STRING AS TOOL_MCP_SERVER_CATEGORY,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.server_name"::STRING AS TOOL_MCP_SERVER_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.status"::STRING AS TOOL_MCP_STATUS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.status.code"::STRING AS TOOL_MCP_STATUS_CODE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.status.description"::STRING AS TOOL_MCP_STATUS_DESC,

    -- === Tool: Server skill (loaded skill modules) ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_skill.already_loaded"::STRING AS TOOL_SKILL_ALREADY_LOADED,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_skill.duration"::FLOAT AS TOOL_SKILL_DURATION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_skill.skill_name"::STRING AS TOOL_SKILL_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_skill.source_category"::STRING AS TOOL_SKILL_SOURCE_CATEGORY,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_skill.status"::STRING AS TOOL_SKILL_STATUS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_skill.status.code"::STRING AS TOOL_SKILL_STATUS_CODE,

    -- === Tool: SQL execution (queries run by the agent) ===
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.analyst_tool_name"::STRING AS TOOL_SQL_ANALYST_TOOL_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.duration"::FLOAT AS TOOL_SQL_DURATION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.error_correction"::STRING AS TOOL_SQL_ERROR_CORRECTION,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.execution_status"::STRING AS TOOL_SQL_EXEC_STATUS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.final_sql"::STRING AS TOOL_SQL_FINAL_SQL,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.query"::STRING AS TOOL_SQL_QUERY,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.query_id"::STRING AS TOOL_SQL_QUERY_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.request_id"::STRING AS TOOL_SQL_REQUEST_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.result"::STRING AS TOOL_SQL_RESULT,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.result.num_rows"::NUMBER AS TOOL_SQL_RESULT_NUM_ROWS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.result_truncated"::STRING AS TOOL_SQL_RESULT_TRUNCATED,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.status"::STRING AS TOOL_SQL_STATUS,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.status.code"::STRING AS TOOL_SQL_STATUS_CODE,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.status.description"::STRING AS TOOL_SQL_STATUS_DESC,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.validation_error.0.message"::STRING AS TOOL_SQL_VAL_ERR_0_MSG,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.validation_error.0.source"::STRING AS TOOL_SQL_VAL_ERR_0_SRC,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.validation_error.1.message"::STRING AS TOOL_SQL_VAL_ERR_1_MSG,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.validation_error.1.source"::STRING AS TOOL_SQL_VAL_ERR_1_SRC,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.validation_error.2.message"::STRING AS TOOL_SQL_VAL_ERR_2_MSG,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.validation_error.2.source"::STRING AS TOOL_SQL_VAL_ERR_2_SRC,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.verified_query_used"::STRING AS TOOL_SQL_VERIFIED_QUERY_USED,
    RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.warehouse"::STRING AS TOOL_SQL_WAREHOUSE,

    -- === Object identification (which Cortex Agent produced this event) ===
    RECORD_ATTRIBUTES:"snow.ai.observability.database.id"::STRING AS OBJECT_DATABASE_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.database.name"::STRING AS OBJECT_DATABASE_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.object.id"::STRING AS OBJECT_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.object.name"::STRING AS OBJECT_NAME,
    RECORD_ATTRIBUTES:"snow.ai.observability.object.type"::STRING AS OBJECT_TYPE,
    RECORD_ATTRIBUTES:"snow.ai.observability.object.version.id"::STRING AS OBJECT_VERSION_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.schema.id"::STRING AS OBJECT_SCHEMA_ID,
    RECORD_ATTRIBUTES:"snow.ai.observability.schema.name"::STRING AS OBJECT_SCHEMA_NAME,

  
     -- Consolidated: Tool status (mutually exclusive per event type)
    COALESCE(
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.status"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.status"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.status"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.status"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.status"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.status"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.status"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_skill.status"::STRING
    ) AS SPAN_STATUS,
    COALESCE(
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.status.code"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.status.code"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.status.code"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.status.code"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.status.code"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.status.code"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.status.code"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_skill.status.code"::STRING
    ) AS SPAN_STATUS_CODE,
    COALESCE(
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.status.description"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.status.description"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.status.description"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.status.description"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.status.description"::STRING,
        RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.execution.status.description"::STRING
    ) AS SPAN_STATUS_DESC,

    -- Consolidated: Tool duration (sum of all non-empty durations)
    NULLIF(
        ZEROIFNULL(RECORD_ATTRIBUTES:"snow.ai.observability.agent.planning.duration"::FLOAT)
        + ZEROIFNULL(RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.sql_execution.duration"::FLOAT)
        + ZEROIFNULL(RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_mcp.duration"::FLOAT)
        + ZEROIFNULL(RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.semantic_context.duration"::FLOAT)
        + ZEROIFNULL(RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.chart_generation.duration"::FLOAT)
        + ZEROIFNULL(RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.code_execution.duration"::FLOAT)
        + ZEROIFNULL(RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.custom_tool.duration"::FLOAT)
        + ZEROIFNULL(RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.server_skill.duration"::FLOAT)
        + ZEROIFNULL(RECORD_ATTRIBUTES:"snow.ai.observability.agent.tool.execution.duration"::FLOAT),
    0) AS SPAN_DURATION,


    -- Token counts (from CORTEX_AGENT_REQUEST events)
    RECORD_ATTRIBUTES:"snow.ai.observability.token_count.cache_read_input"::NUMBER AS TOKENS_CACHE_READ,
    RECORD_ATTRIBUTES:"snow.ai.observability.token_count.cache_write_input"::NUMBER AS TOKENS_CACHE_WRITE,
    RECORD_ATTRIBUTES:"snow.ai.observability.token_count.input"::NUMBER AS TOKENS_INPUT,
    RECORD_ATTRIBUTES:"snow.ai.observability.token_count.output"::NUMBER AS TOKENS_OUTPUT,
    RECORD_ATTRIBUTES:"snow.ai.observability.token_count.total"::NUMBER AS TOKENS_TOTAL,

    -- Derived: activity name without ordering suffix
    REGEXP_REPLACE(RECORD:"name"::STRING, '-[0-9]+$', '') AS RECORD_NAME_BASE


FROM parsed;


-- ============================================================================
-- 2. TRACE_EVENTS — Event-level rows scoped to traces
-- ============================================================================
-- All events that have a trace_id. Reads from BASE_EVENTS (no JSON parsing).
-- Use for detailed event-level analysis within a trace.
-- ============================================================================
CREATE OR REPLACE DYNAMIC TABLE AI_OBSERVABILITY_TRACE_EVENTS
    TARGET_LAG = '1 day'
    WAREHOUSE = $PIPELINE_WAREHOUSE
    REFRESH_MODE = AUTO
    INITIALIZE = ON_CREATE
AS
SELECT *

FROM AI_OBSERVABILITY_BASE_EVENTS
WHERE TRACE_ID IS NOT NULL;


-- ============================================================================
-- 3. TRACE_CASES — One row per trace (case-level aggregation)
-- ============================================================================
-- Aggregates events to one row per trace_id. This is the "case" table for
-- process mining: each trace = one agent invocation = one case.
--
-- Uses a CTE to first aggregate to one row per trace, then applies AI_CLASSIFY
-- on the outer SELECT. This way AI_CLASSIFY runs exactly once per trace,
-- not once per event row. The CTE does not prevent incremental refresh --
-- proven by TRACE_EVENTS which uses the same CTE pattern and refreshes
-- incrementally (inserting only new rows each cycle).
-- ============================================================================
-- select AGENT_FIRST_MSG_CATEGORY, count(*) from AI_OBSERVABILITY_TRACE_CASES group by AGENT_FIRST_MSG_CATEGORY;
CREATE OR REPLACE DYNAMIC TABLE AI_OBSERVABILITY_TRACE_CASES
    TARGET_LAG = '1 day'
    WAREHOUSE = $PIPELINE_WAREHOUSE
    REFRESH_MODE = AUTO
    INITIALIZE = ON_CREATE
AS
WITH trace_agg AS (
    SELECT
        TRACE_ID,

        -- User/session context (constant per trace)
        MAX(USER_NAME) AS USER_NAME,
        MAX(ROLE_NAME) AS ROLE_NAME,

        -- Thread and input linkage
        MAX(AGENT_THREAD_ID) AS AGENT_THREAD_ID,
        MAX(INPUT_ID) AS INPUT_ID,
        MAX(AGENT_REQUEST_ID) AS AGENT_REQUEST_ID,

        -- Object identification (which agent handled the trace)
        MAX(OBJECT_NAME) AS OBJECT_NAME,
        MAX(OBJECT_TYPE) AS OBJECT_TYPE,
        MAX(OBJECT_DATABASE_NAME) AS OBJECT_DATABASE_NAME,
        MAX(OBJECT_SCHEMA_NAME) AS OBJECT_SCHEMA_NAME,

        -- Agent outcome
        MAX(AGENT_STATUS) AS AGENT_STATUS,
        MAX(AGENT_STATUS_CODE) AS AGENT_STATUS_CODE,
        MAX(AGENT_STATUS_DESCRIPTION) AS AGENT_STATUS_DESCRIPTION,
        MAX(AGENT_RESEARCH_MODE) AS AGENT_RESEARCH_MODE,
        MAX(TOOL_CHOICE_TYPE) AS TOOL_CHOICE_TYPE,

        -- Conversation context
        MAX(AGENT_FIRST_MSG_IN_THREAD) AS AGENT_FIRST_MSG_IN_THREAD,
        MAX(AGENT_MESSAGES) AS AGENT_MESSAGES,
        MAX(RECORD_ROOT_INPUT) AS RECORD_ROOT_INPUT,
        MAX(RECORD_ROOT_OUTPUT) AS RECORD_ROOT_OUTPUT,
        MAX(AGENT_RESPONSE) AS AGENT_RESPONSE,
        MAX(AGENT_SUGGESTED_QUERIES) AS AGENT_SUGGESTED_QUERIES,

        -- Coding agent context
        MAX(CODING_AGENT_CLIENT_TYPE) AS CODING_AGENT_CLIENT_TYPE,
        MAX(CODING_AGENT_SESSION_ID) AS CODING_AGENT_SESSION_ID,

        -- Timestamps and metrics
        MIN(START_TIMESTAMP) AS TRACE_START_TIMESTAMP,
        MAX(TIMESTAMP) AS TRACE_END_TIMESTAMP,
        SUM(AGENT_DURATION) AS TRACE_DURATION,
        COUNT(*) AS EVENT_COUNT

    FROM AI_OBSERVABILITY_BASE_EVENTS
    WHERE TRACE_ID IS NOT NULL
    GROUP BY TRACE_ID
)
SELECT
    *,
    -- AI_CLASSIFY: categorize the user's first question into one of 10 intent categories.
    -- Runs once per trace (on the pre-aggregated CTE output, not per event row).
    AI_CLASSIFY(
        AGENT_FIRST_MSG_IN_THREAD,
        [
            'Simple lookup / fact retrieval',
            'Data analysis & insight',
            'Reporting & visualization',
            'SQL / query generation',
            'Code & pipeline development',
            'Debugging & troubleshooting',
            'Workflow automation & action',
            'Governance, security & permissions',
            'Configuration & administration',
            'Explanation & traceability'
        ],
        {
            'task_description': 'Classify the user question or task into one of the provided categories based on the intent of the request'
        }
    ):labels[0]::STRING AS AGENT_FIRST_MSG_CATEGORY
FROM trace_agg;


-- ============================================================================
-- 4. THREAD_EVENTS — Event-level rows scoped to threads
-- ============================================================================
-- Events that belong to a valid thread (multi-turn conversation).
-- Reads from BASE_EVENTS. Filters out empty/placeholder thread IDs.
-- ============================================================================
CREATE OR REPLACE DYNAMIC TABLE AI_OBSERVABILITY_THREAD_EVENTS
    TARGET_LAG = '1 day'
    WAREHOUSE = $PIPELINE_WAREHOUSE
    REFRESH_MODE = AUTO
    INITIALIZE = ON_CREATE
AS
SELECT *
FROM AI_OBSERVABILITY_BASE_EVENTS
WHERE AGENT_THREAD_ID IS NOT NULL
  AND AGENT_THREAD_ID NOT IN ('', '0');


-- ============================================================================
-- 5. THREAD_CASES — One row per thread (case-level aggregation)
-- ============================================================================
-- Aggregates events to one row per thread_id. A thread is a multi-turn
-- conversation; each thread contains one or more traces.
-- Reads from AI_OBSERVABILITY_BASE_EVENTS for consistency with all other tables.
-- ============================================================================
CREATE OR REPLACE DYNAMIC TABLE AI_OBSERVABILITY_THREAD_CASES
    TARGET_LAG = '1 day'
    WAREHOUSE = $PIPELINE_WAREHOUSE
    REFRESH_MODE = AUTO
    INITIALIZE = ON_CREATE
AS
SELECT
    AGENT_THREAD_ID,

    -- Scope and agent metadata
    MAX(SCOPE_NAME) AS SCOPE_NAME,
    MAX(AGENT_STATUS) AS AGENT_STATUS,
    MAX(AGENT_STATUS_CODE) AS AGENT_STATUS_CODE,
    MAX(AGENT_STATUS_DESCRIPTION) AS AGENT_STATUS_DESCRIPTION,
    MAX(AGENT_RESEARCH_MODE) AS AGENT_RESEARCH_MODE,

    -- Coding agent context
    MAX(CODING_AGENT_CLIENT_TYPE) AS CODING_AGENT_CLIENT_TYPE,
    MAX(CODING_AGENT_ORIGIN_APP) AS CODING_AGENT_ORIGIN_APP,
    MAX(CODING_AGENT_PRIVATE_MODE) AS CODING_AGENT_PRIVATE_MODE,
    MAX(CODING_AGENT_SESSION_ID) AS CODING_AGENT_SESSION_ID,

    -- Tool and request metadata
    MAX(TOOL_MCP_SERVER_NAME) AS TOOL_MCP_SERVER_NAME,
    MAX(REQUEST_SKILL_CHOICE_TYPE) AS REQUEST_SKILL_CHOICE_TYPE,
    MAX(REQUEST_ORIGIN_APP) AS REQUEST_ORIGIN_APP,
    MAX(REQUEST_IS_STREAMING) AS REQUEST_IS_STREAMING,
    MAX(REQUEST_TOOL_CHOICE_TYPE) AS REQUEST_TOOL_CHOICE_TYPE,
    MAX(REQUEST_IS_SF_INTELLIGENCE) AS REQUEST_IS_SF_INTELLIGENCE,
    MAX(REQUEST_ORCHESTRATION_TYPE) AS REQUEST_ORCHESTRATION_TYPE,
    MAX(REQUEST_THREAD_ID) AS REQUEST_THREAD_ID,
    MAX(REQUEST_FALLBACK_WAREHOUSE) AS REQUEST_FALLBACK_WAREHOUSE,
    MAX(REQUEST_REASONING_FLOW_TYPE) AS REQUEST_REASONING_FLOW_TYPE,

    -- Object identification
    MAX(OBJECT_VERSION_ID) AS OBJECT_VERSION_ID,
    MAX(OBJECT_TYPE) AS OBJECT_TYPE,
    MAX(OBJECT_NAME) AS OBJECT_NAME,
    MAX(OBJECT_DATABASE_NAME) AS OBJECT_DATABASE_NAME,
    MAX(OBJECT_SCHEMA_NAME) AS OBJECT_SCHEMA_NAME,

    -- User context
    MAX(USER_NAME) AS USER_NAME,

    -- Timestamps and metrics
    MIN(START_TIMESTAMP) AS THREAD_START_TIMESTAMP,
    MAX(TIMESTAMP) AS THREAD_END_TIMESTAMP,
    COUNT(*) AS EVENT_COUNT

FROM AI_OBSERVABILITY_BASE_EVENTS
WHERE AGENT_THREAD_ID IS NOT NULL
  AND AGENT_THREAD_ID NOT IN ('', '0')
GROUP BY AGENT_THREAD_ID;

Alternative 2: Scoped access using GET_AI_OBSERVABILITY_EVENTS

Required permissions

The role that creates and refreshes the dynamic tables needs access to the parent database and schema of the monitored agent, plus the following object-specific privilege:

  • For a Cortex Agent, grant MONITOR, or use a role that has OWNERSHIP on the Cortex Agent.
  • For a Cortex Search service, grant MONITOR, or use a role that has OWNERSHIP on the service. Request logging must be enabled.
  • For an External Agent, grant USAGE on the External Agent.
  • Grant SNOWFLAKE.CORTEX_USER where required by the monitored feature. It is required for an External Agent call to this function.
  • Grant READ UNREDACTED AI OBSERVABILITY EVENTS TABLE on the account when the dynamic tables must retain unredacted content.
  • Grant SNOWFLAKE.CORTEX_USER for the supplied trace-cases table because it uses AI_CLASSIFY.

Example of giving the grants: 

USE ROLE ACCOUNTADMIN;
GRANT USAGE ON DATABASE <agent_database> TO ROLE <pa_role>;
GRANT USAGE ON SCHEMA <agent_database>.<agent_schema> TO ROLE <pa_role>;
GRANT MONITOR ON CORTEX AGENT <agent_database>.<agent_schema>.<agent_name> TO ROLE <pa_role>;
GRANT READ UNREDACTED AI OBSERVABILITY EVENTS TABLE ON ACCOUNT TO ROLE <pa_role>;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE <pa_role>;

Reading single agent data

Use the above code to create the dynamic tables, but replace the direct reading of the AI_OBSERVABILITY_BASE_EVENTS table with the GET_AI_OBSERVABILITY_EVENTS function call as described below.

Replace: 

FROM SNOWFLAKE.LOCAL.AI_OBSERVABILITY_EVENTS e

with: 

FROM TABLE(SNOWFLAKE.LOCAL.GET_AI_OBSERVABILITY_EVENTS(
  '<agent_database>',
  '<agent_schema>',
  '<agent_name>',
  '<agent_type>'
)) e

For example: 

FROM TABLE(SNOWFLAKE.LOCAL.GET_AI_OBSERVABILITY_EVENTS(
  'MY_DATABASE',
  'MY_SCHEMA',
  'MY_AGENT',
  'CORTEX AGENT'
)) e

Reading multiple agents data

If the solution must analyse several agents, create a source query with one function call per agent and combine results with UNION ALL. Identify the source object in an additional literal column if the object identity is needed in reporting.

Example: 

WITH source_events AS (
SELECT 'AGENT_A' AS AGENT_NAME, e.*
FROM TABLE(SNOWFLAKE.LOCAL.GET_AI_OBSERVABILITY_EVENTS(
  'MY_DATABASE', 'MY_SCHEMA', 'AGENT_A', 'CORTEX AGENT'
)) e

UNION ALL

SELECT 'AGENT_B' AS AGENT_NAME, e.*
FROM TABLE(SNOWFLAKE.LOCAL.GET_AI_OBSERVABILITY_EVENTS(
  'MY_DATABASE', 'MY_SCHEMA', 'AGENT_B', 'CORTEX AGENT'
)) e
)
SELECT *
FROM source_events;
Retrieved from "https://wiki.onqpr.com/pa/index.php?title=AI_Observability_Solution&oldid=29179"