Broadcast to Snowflake | OpenRouter Observability | OpenRouter

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 > Broadcast 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

1 SELECT
2   DATE_TRUNC('day', TIMESTAMP) as day,
3   MODEL,
4   SUM(TOTAL_COST) as total_cost,
5   SUM(TOTAL_TOKENS) as total_tokens,
6   COUNT(*) as request_count
7 FROM OPENROUTER_TRACES
8 WHERE TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP())
9   AND STATUS = 'ok'
10   AND SPAN_TYPE = 'GENERATION'
11 GROUP BY day, MODEL
12 ORDER BY day DESC, total_cost DESC;

User activity analysis

1 SELECT
2   USER_ID,
3   COUNT(DISTINCT TRACE_ID) as trace_count,
4   COUNT(DISTINCT SESSION_ID) as session_count,
5   SUM(TOTAL_TOKENS) as total_tokens,
6   SUM(TOTAL_COST) as total_cost,
7   AVG(DURATION_MS) as avg_duration_ms
8 FROM OPENROUTER_TRACES
9 WHERE TIMESTAMP >= DATEADD(day, -7, CURRENT_TIMESTAMP())
10   AND SPAN_TYPE = 'GENERATION'
11 GROUP BY USER_ID
12 ORDER BY total_cost DESC;

Error analysis

1 SELECT
2   TRACE_ID,
3   TIMESTAMP,
4   MODEL,
5   LEVEL,
6   FINISH_REASON,
7   METADATA as user_metadata,
8   INPUT,
9   OUTPUT
10 FROM OPENROUTER_TRACES
11 WHERE STATUS = 'error'
12   AND TIMESTAMP >= DATEADD(hour, -1, CURRENT_TIMESTAMP())
13 ORDER BY TIMESTAMP DESC;

Provider performance comparison

1 SELECT
2   PROVIDER_NAME,
3   MODEL,
4   AVG(DURATION_MS) as avg_duration_ms,
5   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DURATION_MS) as p50_duration_ms,
6   PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY DURATION_MS) as p95_duration_ms,
7   COUNT(*) as request_count
8 FROM OPENROUTER_TRACES
9 WHERE TIMESTAMP >= DATEADD(day, -7, CURRENT_TIMESTAMP())
10   AND STATUS = 'ok'
11   AND SPAN_TYPE = 'GENERATION'
12 GROUP BY PROVIDER_NAME, MODEL
13 HAVING request_count >= 10
14 ORDER BY avg_duration_ms;

Usage by API key

1 SELECT
2   API_KEY_NAME,
3   COUNT(DISTINCT TRACE_ID) as trace_count,
4   SUM(TOTAL_COST) as total_cost,
5   SUM(PROMPT_TOKENS) as prompt_tokens,
6   SUM(COMPLETION_TOKENS) as completion_tokens
7 FROM OPENROUTER_TRACES
8 WHERE TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP())
9   AND SPAN_TYPE = 'GENERATION'
10 GROUP BY API_KEY_NAME
11 ORDER BY total_cost DESC;

Accessing VARIANT columns

1 SELECT
2   TRACE_ID,
3   METADATA:custom_field::STRING as custom_value,
4   ATTRIBUTES:"gen_ai.request.model"::STRING as requested_model
5 FROM OPENROUTER_TRACES
6 WHERE METADATA:custom_field IS NOT NULL;

Parsing input messages

1 SELECT
2   TRACE_ID,
3   INPUT:messages[0]:role::STRING as first_message_role,
4   INPUT:messages[0]:content::STRING as first_message_content
5 FROM OPENROUTER_TRACES
6 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.

1	SELECT
2	DATE_TRUNC('day', TIMESTAMP) as day,
3	MODEL,
4	SUM(TOTAL_COST) as total_cost,
5	SUM(TOTAL_TOKENS) as total_tokens,
6	COUNT(*) as request_count
7	FROM OPENROUTER_TRACES
8	WHERE TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP())
9	AND STATUS = 'ok'
10	AND SPAN_TYPE = 'GENERATION'
11	GROUP BY day, MODEL
12	ORDER BY day DESC, total_cost DESC;

1	SELECT
2	USER_ID,
3	COUNT(DISTINCT TRACE_ID) as trace_count,
4	COUNT(DISTINCT SESSION_ID) as session_count,
5	SUM(TOTAL_TOKENS) as total_tokens,
6	SUM(TOTAL_COST) as total_cost,
7	AVG(DURATION_MS) as avg_duration_ms
8	FROM OPENROUTER_TRACES
9	WHERE TIMESTAMP >= DATEADD(day, -7, CURRENT_TIMESTAMP())
10	AND SPAN_TYPE = 'GENERATION'
11	GROUP BY USER_ID
12	ORDER BY total_cost DESC;

1	SELECT
2	TRACE_ID,
3	TIMESTAMP,
4	MODEL,
5	LEVEL,
6	FINISH_REASON,
7	METADATA as user_metadata,
8	INPUT,
9	OUTPUT
10	FROM OPENROUTER_TRACES
11	WHERE STATUS = 'error'
12	AND TIMESTAMP >= DATEADD(hour, -1, CURRENT_TIMESTAMP())
13	ORDER BY TIMESTAMP DESC;

1	SELECT
2	PROVIDER_NAME,
3	MODEL,
4	AVG(DURATION_MS) as avg_duration_ms,
5	PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DURATION_MS) as p50_duration_ms,
6	PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY DURATION_MS) as p95_duration_ms,
7	COUNT(*) as request_count
8	FROM OPENROUTER_TRACES
9	WHERE TIMESTAMP >= DATEADD(day, -7, CURRENT_TIMESTAMP())
10	AND STATUS = 'ok'
11	AND SPAN_TYPE = 'GENERATION'
12	GROUP BY PROVIDER_NAME, MODEL
13	HAVING request_count >= 10
14	ORDER BY avg_duration_ms;

1	SELECT
2	API_KEY_NAME,
3	COUNT(DISTINCT TRACE_ID) as trace_count,
4	SUM(TOTAL_COST) as total_cost,
5	SUM(PROMPT_TOKENS) as prompt_tokens,
6	SUM(COMPLETION_TOKENS) as completion_tokens
7	FROM OPENROUTER_TRACES
8	WHERE TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP())
9	AND SPAN_TYPE = 'GENERATION'
10	GROUP BY API_KEY_NAME
11	ORDER BY total_cost DESC;

1	SELECT
2	TRACE_ID,
3	METADATA:custom_field::STRING as custom_value,
4	ATTRIBUTES:"gen_ai.request.model"::STRING as requested_model
5	FROM OPENROUTER_TRACES
6	WHERE METADATA:custom_field IS NOT NULL;

1	SELECT
2	TRACE_ID,
3	INPUT:messages[0]:role::STRING as first_message_role,
4	INPUT:messages[0]:content::STRING as first_message_content
5	FROM OPENROUTER_TRACES
6	WHERE SPAN_TYPE = 'GENERATION';