05-snowhouse-querying

Snowhouse Querying Patterns & Best Practices

PURPOSE: Master patterns for querying Snowhouse ETL views and log tables efficiently and accurately.

🎯 Core Principles

1. ALWAYS filter by account_id
2. ALWAYS start with job_etl_v for timestamps
3. ALWAYS use time bounds for log queries
4. NEVER query ACCOUNT_USAGE views in Snowhouse

🗺️ Snowhouse Structure

snowhouse_import.<deployment>.<table>

Deployments:
- prod1: Production deployment 1
- prod2: Production deployment 2
- prod3: Production deployment 3
- aws_us_west_2, azure_westus2, gcp_us_central1, etc.

📊 Core Query Patterns

Pattern 1: Find Recent Queries

-- ALWAYS start here for timestamps and context
SELECT 
    query_id,
    query_text,
    user_name,
    warehouse_name,
    start_time,
    end_time,
    execution_time,
    error_code,
    error_message
FROM snowhouse_import.<deployment>.job_etl_v
WHERE account_id = <account_id>
  AND start_time >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
  AND error_code IS NOT NULL  -- Only failed queries
ORDER BY start_time DESC
LIMIT 100;

Pattern 2: Query with Session Context

-- Join jobs with sessions for user context
SELECT 
    j.query_id,
    j.query_text,
    j.start_time,
    j.error_message,
    s.session_id,
    s.user_name,
    s.client_type,
    s.client_version
FROM snowhouse_import.<deployment>.job_etl_v j
LEFT JOIN snowhouse_import.<deployment>.session_etl_v s
    ON j.session_id = s.session_id
    AND j.account_id = s.account_id
WHERE j.account_id = <account_id>
  AND j.start_time >= '<start_time>'
  AND j.end_time <= '<end_time>'
ORDER BY j.start_time DESC;

Pattern 3: Logs with Time Bounds

-- CRITICAL: Always get timestamps from job_etl_v first!
WITH query_context AS (
    SELECT 
        query_id,
        request_id,
        start_time,
        end_time
    FROM snowhouse_import.<deployment>.job_etl_v
    WHERE account_id = <account_id>
      AND query_id = '<query_id>'
)
SELECT l.*
FROM snowhouse_import.<deployment>.gs_logs_v l
JOIN query_context q ON l.request_id = q.request_id
WHERE l.account_id = <account_id>
  AND l.timestamp BETWEEN q.start_time - INTERVAL '5 minutes'
                      AND q.end_time + INTERVAL '5 minutes'
ORDER BY l.timestamp;