Skip to main content
Snowflake is a cloud data warehouse platform. OpenRouter can stream traces directly to your Snowflake database for custom analytics, long-term storage, and business intelligence.

​
Step 1: Create the traces table

Before connecting OpenRouter, create the OPENROUTER_TRACES table in your Snowflake database. You can find the exact SQL in the OpenRouter dashboard when configuring the destination:
Snowflake Table Setup

​
Step 2: Create access credentials

Generate a Programmatic Access Token with ACCOUNTADMIN permissions in the Snowflake UI under Settings > Authentication.
Snowflake PAT

​
Step 3: Enable Broadcast in OpenRouter

Go to Settings > Observability and toggle Enable Broadcast.
Enable Broadcast

​
Step 4: Configure Snowflake

Click the edit icon next to Snowflake and enter:
  • Account: Your Snowflake account identifier (e.g., eac52885.us-east-1). You can find your account region and your account number at the end of your Snowflake instance’s URL: https://app.snowflake.com/us-east-1/eac52885; together these make your account identifier.
  • Token: Your Programmatic Access Token.
  • Database: Target database name (default: SNOWFLAKE_LEARNING_DB).
  • Schema: Target schema name (default: PUBLIC).
  • Table: Table name (default: OPENROUTER_TRACES).
  • Warehouse: Compute warehouse name (default: COMPUTE_WH).

​
Step 5: Test and save

Click Test Connection to verify the setup. The configuration only saves if the test passes.

​
Step 6: Send a test trace

Make an API request through OpenRouter and query your Snowflake table to verify the trace was received.
Snowflake Test Trace

​
Example queries

​
Cost analysis by model

SELECT
  DATE_TRUNC('day', TIMESTAMP) as day,
  MODEL,
  SUM(TOTAL_COST) as total_cost,
  SUM(TOTAL_TOKENS) as total_tokens,
  COUNT(*) as request_count
FROM OPENROUTER_TRACES
WHERE TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP())
  AND STATUS = 'ok'
  AND SPAN_TYPE = 'GENERATION'
GROUP BY day, MODEL
ORDER BY day DESC, total_cost DESC;

​
User activity analysis

SELECT
  USER_ID,
  COUNT(DISTINCT TRACE_ID) as trace_count,
  COUNT(DISTINCT SESSION_ID) as session_count,
  SUM(TOTAL_TOKENS) as total_tokens,
  SUM(TOTAL_COST) as total_cost,
  AVG(DURATION_MS) as avg_duration_ms
FROM OPENROUTER_TRACES
WHERE TIMESTAMP >= DATEADD(day, -7, CURRENT_TIMESTAMP())
  AND SPAN_TYPE = 'GENERATION'
GROUP BY USER_ID
ORDER BY total_cost DESC;

​
Error analysis

SELECT
  TRACE_ID,
  TIMESTAMP,
  MODEL,
  LEVEL,
  FINISH_REASON,
  METADATA as user_metadata,
  INPUT,
  OUTPUT
FROM OPENROUTER_TRACES
WHERE STATUS = 'error'
  AND TIMESTAMP >= DATEADD(hour, -1, CURRENT_TIMESTAMP())
ORDER BY TIMESTAMP DESC;

​
Provider performance comparison

SELECT
  PROVIDER_NAME,
  MODEL,
  AVG(DURATION_MS) as avg_duration_ms,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DURATION_MS) as p50_duration_ms,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY DURATION_MS) as p95_duration_ms,
  COUNT(*) as request_count
FROM OPENROUTER_TRACES
WHERE TIMESTAMP >= DATEADD(day, -7, CURRENT_TIMESTAMP())
  AND STATUS = 'ok'
  AND SPAN_TYPE = 'GENERATION'
GROUP BY PROVIDER_NAME, MODEL
HAVING request_count >= 10
ORDER BY avg_duration_ms;

​
Usage by API key

SELECT
  API_KEY_NAME,
  COUNT(DISTINCT TRACE_ID) as trace_count,
  SUM(TOTAL_COST) as total_cost,
  SUM(PROMPT_TOKENS) as prompt_tokens,
  SUM(COMPLETION_TOKENS) as completion_tokens
FROM OPENROUTER_TRACES
WHERE TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP())
  AND SPAN_TYPE = 'GENERATION'
GROUP BY API_KEY_NAME
ORDER BY total_cost DESC;

​
Accessing VARIANT columns

SELECT
  TRACE_ID,
  METADATA:custom_field::STRING as custom_value,
  ATTRIBUTES:"gen_ai.request.model"::STRING as requested_model
FROM OPENROUTER_TRACES
WHERE METADATA:custom_field IS NOT NULL;

​
Parsing input messages

SELECT
  TRACE_ID,
  INPUT:messages[0]:role::STRING as first_message_role,
  INPUT:messages[0]:content::STRING as first_message_content
FROM OPENROUTER_TRACES
WHERE SPAN_TYPE = 'GENERATION';

​
Schema design

​
Typed columns

The schema extracts commonly-queried fields as typed columns for efficient filtering and aggregation:
  • Identifiers: TRACE_ID, USER_ID, SESSION_ID, etc.
  • Timestamps: For time-series analysis
  • Model Info: For cost and performance analysis
  • Metrics: Tokens and costs for billing

​
VARIANT columns

Less commonly-accessed and variable-structure data is stored in VARIANT columns:
  • ATTRIBUTES: Full OTEL attribute set
  • INPUT/OUTPUT: Variable message structures
  • METADATA: User-defined key-values
  • MODEL_PARAMETERS: Model-specific configurations
This design balances query performance with schema flexibility and storage efficiency.

​
Custom Metadata

Custom metadata from the trace field is stored in the METADATA VARIANT column. You can query it using Snowflake’s semi-structured data functions.

​
Supported Metadata Keys

KeySnowflake MappingDescription
trace_idTRACE_ID column / METADATA:trace_idCustom trace identifier for grouping
trace_nameMETADATA:trace_nameCustom name for the trace
span_nameMETADATA:span_nameName for intermediate spans
generation_nameMETADATA:generation_nameName for the LLM generation

​
Example

{
  "model": "openai/gpt-4o",
  "messages": [{ "role": "user", "content": "Forecast next quarter revenue..." }],
  "user": "user_12345",
  "session_id": "session_abc",
  "trace": {
    "trace_name": "Revenue Forecasting",
    "generation_name": "Generate Forecast",
    "department": "finance",
    "quarter": "Q2-2026",
    "model_version": "v3"
  }
}

​
Querying Custom Metadata

Use Snowflake’s VARIANT column syntax to query your custom metadata: 
SELECT
  TRACE_ID,
  METADATA:department::STRING as department,
  METADATA:quarter::STRING as quarter,
  METADATA:model_version::STRING as model_version,
  TOTAL_COST,
  TOTAL_TOKENS
FROM OPENROUTER_TRACES
WHERE METADATA:department IS NOT NULL
  AND SPAN_TYPE = 'GENERATION'
ORDER BY TIMESTAMP DESC;

​
Additional Context

  • The user field maps to the USER_ID typed column
  • The session_id field maps to the SESSION_ID typed column
  • All custom metadata keys from trace are stored in the METADATA VARIANT column for flexible querying
  • You can create materialized views on frequently queried metadata fields for better performance

​
Privacy Mode

When Privacy Mode is enabled for this destination, prompt and completion content is excluded from traces. All other trace data — token usage, costs, timing, model information, and custom metadata — is still sent normally. See Privacy Mode for details.