Query history and profiling in Snowflake - Deep Dive

Overview - Query history and profiling

What is it?

Query history and profiling in Snowflake means keeping track of all the commands and queries run in the system and analyzing their details. It helps you see what queries were run, how long they took, and how much resources they used. This information is stored so you can review past activity and understand performance. Profiling adds deeper insight into how queries behave and where time or resources are spent.

Why it matters

Without query history and profiling, it would be very hard to find slow or costly queries, troubleshoot problems, or optimize performance. Imagine trying to fix a car without knowing what parts were used or how it was driven. Query history gives you a record, and profiling shows you the details needed to improve efficiency and save money.

Where it fits

Before learning query history and profiling, you should understand basic SQL queries and Snowflake's architecture. After mastering this, you can move on to query optimization, resource monitoring, and cost management in Snowflake.

Mental Model

Core Idea

Query history and profiling is like a detailed logbook and performance report for every query run, showing what happened and how well it performed.

Think of it like...

It's like a fitness tracker for your database queries, recording every run, how long it took, and how much energy it used, so you can improve your workout plan.

┌─────────────────────────────┐
│       Query History         │
│  ┌───────────────────────┐  │
│  │ Query ID              │  │
│  │ User                  │  │
│  │ Start Time            │  │
│  │ End Time              │  │
│  │ Status                │  │
│  └───────────────────────┘  │
│                             │
│       Query Profiling        │
│  ┌───────────────────────┐  │
│  │ Execution Steps       │  │
│  │ Time per Step         │  │
│  │ Resource Usage        │  │
│  │ Data Scanned          │  │
│  └───────────────────────┘  │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Query History Basics

Concept: Learn what query history is and what basic information it records.

Query history in Snowflake records every query run, including who ran it, when it started and ended, and whether it succeeded or failed. You can access this history using the INFORMATION_SCHEMA.QUERY_HISTORY view or the Snowflake web interface. This helps you see past activity and check if queries ran correctly.

Result

You can list recent queries with their start and end times, user names, and status.

Knowing that Snowflake automatically keeps a detailed record of all queries helps you track and audit database activity easily.

2

Foundation

Accessing Query History Data

Concept: Learn how to retrieve query history using SQL commands.

You can run SQL like SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) to get recent queries. This returns columns like QUERY_ID, USER_NAME, START_TIME, END_TIME, and ERROR_MESSAGE. You can filter by time, user, or status to find specific queries.

Result

You get a table of queries with details, allowing you to analyze what happened recently.

Being able to query the history data yourself gives you control to find exactly the queries you want to investigate.

3

Intermediate

Introduction to Query Profiling

🤔Before reading on: do you think query profiling shows only the total time a query took, or does it break down time by steps? Commit to your answer.

Concept: Query profiling breaks down query execution into detailed steps and resource usage.

Profiling shows how a query runs inside Snowflake, including parsing, compilation, optimization, and execution phases. It reports time spent on each step, bytes scanned, and compute resources used. This helps identify which part of a query is slow or costly.

Result

You can see detailed timing and resource use for each query step, not just total duration.

Understanding query profiling reveals hidden bottlenecks and helps target optimizations precisely.

4

Intermediate

Using QUERY_HISTORY_PROFILING View

🤔Before reading on: do you think profiling data is available for all queries or only for recent ones? Commit to your answer.

Concept: Snowflake provides a special view to access profiling details for recent queries.

The QUERY_HISTORY_PROFILING function returns detailed profiling info for queries run in the last 7 days. You can join this with QUERY_HISTORY to get both summary and detailed data. Columns include EXECUTION_TIME, BYTES_SCANNED, and STEP_DETAILS.

Result

You get a combined view showing both query metadata and detailed profiling info.

Knowing how to combine history and profiling data lets you perform deep analysis on query performance.

5

Intermediate

Filtering and Sorting Query History

🤔Before reading on: do you think sorting query history by execution time helps find slow queries? Commit to your answer.

Concept: You can filter and sort query history to focus on important queries.

Using WHERE clauses on columns like START_TIME, USER_NAME, or EXECUTION_STATUS, you can narrow down queries. Sorting by TOTAL_ELAPSED_TIME helps find slow queries. This is useful for troubleshooting and optimization.

Result

You can quickly identify problematic queries by filtering and sorting history data.

Filtering and sorting empower you to focus on queries that matter most for performance and cost.

6

Advanced

Analyzing Query Profile for Optimization

🤔Before reading on: do you think high bytes scanned always means slow query? Commit to your answer.

Concept: Use profiling data to find inefficiencies and optimize queries.

Look at steps with high execution time or resource use. High bytes scanned may indicate missing filters or large table scans. Profiling helps decide if adding clustering keys or rewriting queries will help. You can also spot if compute resources are under or overused.

Result

You identify specific causes of slow or costly queries and plan improvements.

Profiling data guides targeted optimizations, saving time and cloud costs.

7

Expert

Interpreting Query Profile Internals

🤔Before reading on: do you think query profiling data is collected synchronously during execution or asynchronously after? Commit to your answer.

Concept: Understand how Snowflake collects and stores profiling data behind the scenes.

Snowflake collects profiling data asynchronously after query execution to avoid slowing queries. It aggregates metrics from multiple compute nodes and stores them in system tables. Profiling data includes execution plans, resource usage, and wait times. This design balances detail with performance impact.

Result

You appreciate the tradeoffs in profiling design and how data accuracy and performance coexist.

Knowing profiling is asynchronous explains why some data may lag and why profiling has minimal impact on query speed.

Under the Hood

Snowflake logs every query's metadata and execution details in internal tables. Profiling data is gathered by monitoring each step of query execution across distributed compute nodes. This data is aggregated and stored asynchronously to avoid slowing down queries. The system tracks timing, resource use, data scanned, and execution plans, making it available via system views.

Why designed this way?

Profiling was designed to provide detailed insights without impacting query performance. Collecting data asynchronously and aggregating from multiple nodes balances accuracy with speed. Early designs that collected data synchronously caused slowdowns, so this approach was chosen to keep Snowflake fast and scalable.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Query Runs on │──────▶│ Compute Nodes │──────▶│ Profiling Data│
│ Snowflake     │       │ (Multiple)    │       │ Collected     │
└───────────────┘       └───────────────┘       └───────────────┘
         │                        │                      │
         │                        │                      ▼
         │                        │             ┌─────────────────┐
         │                        │             │ Aggregated in   │
         │                        │             │ System Tables   │
         │                        │             └─────────────────┘
         │                        │                      │
         │                        │                      ▼
         │                        │             ┌─────────────────┐
         │                        │             │ Query History & │
         │                        │             │ Profiling Views │
         │                        │             └─────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does query history show every single query ever run in Snowflake? Commit to yes or no.

Common Belief: Query history contains every query ever run in Snowflake.

Reality: Query history only retains data for a limited time (usually 7-14 days) depending on account settings.

Why it matters: Assuming all queries are always available can lead to missed data when investigating older issues.

Quick: Does query profiling slow down query execution significantly? Commit to yes or no.

Common Belief: Profiling data collection slows down query execution noticeably.

Reality: Profiling is collected asynchronously after query execution, so it has minimal impact on query speed.

Why it matters: Worrying about profiling overhead may prevent users from using valuable performance insights.

Quick: Does a high number of bytes scanned always mean a query is slow? Commit to yes or no.

Common Belief: If a query scans a lot of data, it must be slow.

Reality: High data scanned can be fast if compute resources are sufficient; other factors like joins and network also affect speed.

Why it matters: Focusing only on bytes scanned can mislead optimization efforts and miss real bottlenecks.

Quick: Can query history show detailed step-by-step execution times for all queries? Commit to yes or no.

Common Belief: Query history always includes detailed step-by-step execution times for every query.

Reality: Detailed profiling is only available for recent queries and requires specific views; basic history shows summary info.

Why it matters: Expecting detailed profiling for all queries can cause confusion when data is missing.

Expert Zone

1

Profiling data granularity varies by query complexity and compute cluster size, affecting detail level.

2

Some profiling metrics are estimates due to distributed execution and asynchronous collection.

3

Query history retention can be extended with Snowflake Enterprise features, impacting audit capabilities.

When NOT to use

Query history and profiling are not suitable for real-time monitoring or alerting; use Snowflake's Resource Monitors or third-party tools instead.

Production Patterns

Teams regularly review query history to identify slow or costly queries, then use profiling to pinpoint bottlenecks before applying optimizations like clustering keys or query rewrites.

Connections

Application Performance Monitoring (APM)

Similar pattern of tracking and profiling operations for performance insights.

Understanding query profiling helps grasp how APM tools monitor software performance by collecting detailed execution data.

Financial Auditing

Both involve keeping detailed records and logs for accountability and troubleshooting.

Knowing query history is like financial audit logs helps appreciate its role in compliance and error detection.

Human Fitness Tracking

Both track activity over time and analyze performance to improve results.

Seeing query profiling as fitness tracking clarifies why detailed metrics matter for continuous improvement.

Common Pitfalls

#1 Trying to query query history without specifying a time range, causing slow or large results.

Wrong approach:

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY());

Correct approach:

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE START_TIME > DATEADD(day, -7, CURRENT_TIMESTAMP());

Root cause:

Not limiting query history scope leads to large data scans and slow queries.

#2 Assuming profiling data is available for queries older than retention period.

Wrong approach:

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_PROFILING()) WHERE QUERY_ID = 'old_query_id';

Correct approach:

Only query profiling for recent queries within retention window; archive older data separately.

Root cause:

Misunderstanding retention limits causes missing data and confusion.

#3 Ignoring resource usage metrics and focusing only on query duration for optimization.

Wrong approach:

Optimizing queries solely by sorting history on TOTAL_ELAPSED_TIME.

Correct approach:

Combine duration with BYTES_SCANNED and EXECUTION_STEPS to identify true bottlenecks.

Root cause:

Overlooking multiple profiling dimensions leads to incomplete optimization.

Key Takeaways

Query history in Snowflake records recent queries with metadata like user, time, and status, enabling activity tracking.

Profiling breaks down query execution into detailed steps and resource usage, revealing performance bottlenecks.

Profiling data is collected asynchronously to avoid slowing queries and is available only for recent queries.

Filtering and sorting query history helps focus on slow or costly queries for troubleshooting and optimization.

Understanding query history and profiling is essential for efficient Snowflake use, cost control, and performance tuning.