This domain is about 21% of the exam, roughly 21 of its 100 questions.

Every point below comes from Snowflake's official documentation; each section lists its sources.

Think first A query is slow or costly: which QUERY_HISTORY column, query insight or Account Usage view shows you why?

Why it matters: Questions describe a slow or costly query and ask which column, insight or view reveals the cause.

QUERY_HISTORY records each query's spilling, pruning and queuing. QUERY_INSIGHTS turns problems into labeled insights, one row each. QUERY_ATTRIBUTION_HISTORY shows what each query cost on a warehouse.

QUERY_HISTORY shows spilling, pruning and queuing for each query. bytes_spilled_to_remote_storage shows data spilled to remote disk. Compare partitions_scanned with partitions_total to judge pruning. The view covers 365 days, while the Information Schema function covers only 7.

Exam trap Not every query in QUERY_HISTORY also appears in ACCESS_HISTORY. When you query ACCESS_HISTORY, filter on query_start_time with a narrow range.

Two queue time columns show why a query waited. Overload means the current workload is too much for the warehouse. Provisioning means the warehouse was still being created, resumed or resized.

Two queue time columns compared

QUERY_INSIGHTS holds one row per insight produced for a query. Remote spillage and queued overload appear under the WAREHOUSE topic. Exploding joins appear under the JOIN topic, as QUERY_INSIGHT_EXPLODING_JOIN or QUERY_INSIGHT_NESTED_EXPLODING_JOIN.

Pruning history views help you choose clustering keys. A good cluster key means fewer micro-partitions to scan for filters, joins and aggregates. TABLE_QUERY_PRUNING_HISTORY finds the tables queries access, and COLUMN_QUERY_PRUNING_HISTORY finds their often-used columns. Its ACCESS_TYPE column shows WHERE or JOIN use. TABLE_PRUNING_HISTORY compares pruning before and after Automatic Clustering or search optimization.

Exam trap COLUMN_QUERY_PRUNING_HISTORY reflects the plan actually executed, not your SQL text, so a HAVING clause can show as WHERE. It has no data for hybrid tables.

QUERY_ATTRIBUTION_HISTORY shows each query's warehouse compute cost. Use it for standard warehouses. Attributed credits leave out warehouse idle time. For an accelerated query, add CREDITS_USED_QUERY_ACCELERATION to CREDITS_ATTRIBUTED_COMPUTE for the total.

Exam trap Queries of about 100 ms or less never appear in QUERY_ATTRIBUTION_HISTORY. They are too short for per-query cost attribution.

Adaptive Warehouse queries use QUERY_METERING_HISTORY instead. It gives per-query credits for 365 days, with one row per metering hour while a query runs. Charges can take up to 1 hour to appear. Roles granted USAGE_VIEWER or GOVERNANCE_VIEWER can see it.

AGGREGATE_QUERY_HISTORY groups repeated statements by the minute. It holds data like QUERY_HISTORY, aggregated in one-minute intervals. It suits high-throughput workloads that repeat a few distinct statements, such as Unistore workloads. Queries that differ only in literal values can share one QUERY_PARAMETERIZED_HASH.

Account Usage views lag by different amounts. Check a view's latency before you decide that a recent query is missing from it.

Latency of the main query views

• Remote spilling shows in bytes_spilled_to_remote_storage; pruning shows in partitions scanned against the total.
• Queued overload means a busy warehouse; queued provisioning means compute was still starting or resizing.
• QUERY_ATTRIBUTION_HISTORY leaves out idle time and queries of about 100 ms or less.
• Account Usage QUERY_HISTORY keeps 365 days; the Information Schema function keeps 7.

Sources: QUERY_HISTORY view | Snowflake Documentation · QUERY_INSIGHTS view | Snowflake Documentation · QUERY_ATTRIBUTION_HISTORY view | Snowflake Documentation · TABLE_PRUNING_HISTORY view | Snowflake Documentation · QUERY_METERING_HISTORY view | Snowflake Documentation · ACCESS_HISTORY view | Snowflake Documentation · AGGREGATE_QUERY_HISTORY view | Snowflake Documentation · Optimizing storage for performance | Snowflake Documentation

Think first A query runs slowly: when do you reach for query acceleration, search optimization, a clustering key or a materialized view?

Why it matters: Questions describe a slow query or table and ask which performance feature, setting, cost or limit fits it.

Each feature speeds up a different kind of query. Query acceleration offloads parts of large outlier queries to shared compute, and search optimization builds a search access path for point lookups. Clustering keys co-locate data in micro-partitions, and the optimizer can swap in a materialized view for its base table.

Query acceleration suits large scans with a selective filter or aggregation. It also helps large scans that insert or copy many new rows. Turn it on with ENABLE_QUERY_ACCELERATION = TRUE on CREATE WAREHOUSE or ALTER WAREHOUSE. Gen2 standard and multi-cluster warehouses have it on by default. Its total cost is the warehouse plus the service.

Exam trap Queries with nondeterministic functions such as SEQ or RANDOM are not eligible. A query with LIMIT also needs ORDER BY to qualify.

SYSTEM$ESTIMATE_QUERY_ACCELERATION tests whether one query is eligible. The QUERY_ACCELERATION_ELIGIBLE view finds queries and warehouses that might benefit. The two services can also work together: search optimization prunes micro-partitions first, then query acceleration offloads part of the remaining work.

Search optimization targets selective point lookups. It works best when a filtered column has about 100,000 or more distinct values. Queries that already run in under a second rarely gain much. The optimizer decides when to use it, and a query needs only SELECT on the table.

Example: Adds search optimization to a whole table.

ALTER TABLE my_table ADD SEARCH OPTIMIZATION;

• ADD SEARCH OPTIMIZATION: with no ON clause, columns added later also get EQUALITY

External, hybrid and temporary tables are not supported. Queries that use Time Travel gain nothing, because it works only on active data. FLOAT, DECFLOAT and GEOMETRY values do not benefit, and neither do most predicates that cast the column's values.

Exam trap FULL_TEXT search optimization serves the SEARCH function, and its ALTER TABLE command names an analyzer. The SEARCH call names an analyzer too. If the two differ, the search access path is not used.

Maintenance needs no warehouse of yours, but it is not free. Its storage and compute are charged. SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS gives an estimate, but real costs can differ by up to 50%. The access path is usually about 1/4 of the table's size. Enabling specific columns, not the whole table, is the usual best practice.

Exam trap On a high-churn table, adding Automatic Clustering to search optimization can raise maintenance costs above search optimization alone.

Keep a clustering key to 3 or 4 columns or expressions. More columns tend to add cost faster than benefit. Clustering pays off most on tables that are queried often and change rarely.

Clustering compared with search optimization

A materialized view can query only one table. Joins, including self-joins, are not supported. The optimizer can rewrite base-table queries to use the view automatically. Materialized views can also speed up queries on external tables and let you cluster the same data on a different key.

Exam trap A materialized view that is behind its base table still returns up-to-date results. The query may just take a little longer.

• Query acceleration offloads parts of large outlier queries to shared compute.
• Search optimization speeds up point lookups on columns with about 100,000 or more distinct values.
• Keep a clustering key to 3 or 4 columns or expressions.
• A materialized view queries a single table and supports no joins.

Sources: Using the Query Acceleration Service (QAS) | Snowflake Documentation · Search optimization service | Snowflake Documentation · Clustering Keys & Clustered Tables | Snowflake Documentation · Working with Materialized Views | Snowflake Documentation · Enabling and disabling search optimization | Snowflake Documentation · Tutorial: Improve Workload Performance with the Query Acceleration Service | Snowflake Documentation · Optimizing query performance | Snowflake Documentation · Identifying queries that can benefit from search optimization | Snowflake Documentation

Think first When does Snowflake reuse a cached result, and when does a warehouse setting throw a cache away?

Why it matters: Questions describe a query or a warehouse setting and ask whether a cache is reused, lost or worth keeping.

Snowflake persists query results for reuse, and each running warehouse caches table data. Result reuse needs an exactly matching query. The warehouse cache is dropped when the warehouse suspends, so auto-suspend trades saved credits against speed.

Result reuse needs the new query to match the old one exactly. Even a change in letter case or a table alias stops full reuse. Reuse is on by default, and USE_CACHED_RESULT can override it for an account, user or session.

Exam trap Functions such as UUID_STRING, RANDOM and RANDSTR block reuse. So do external functions and queries on hybrid tables.

Persisted results expire after 24 hours. Each reuse resets the 24 hours, up to 31 days from the first run. For results over 100 KB, the access token expires after 6 hours. A cached SHOW result is reused only by the role that produced it.

RESULT_SCAN turns an earlier result into a table you can query. With no argument, it reads the most recent command. It works on results from any of your sessions within 24 hours, a period you cannot adjust. Add ORDER BY when row order matters.

Exam trap Even ACCOUNTADMIN cannot use RESULT_SCAN on another user's results. For manual queries, access belongs to the user who ran them, not the role.

Some metadata commands, such as SHOW TABLES, need no warehouse. With no warehouse involved, no warehouse charges apply. To post-process SHOW output with RESULT_SCAN, double-quote the column names, because they are lowercase.

A running warehouse caches table data for its own queries. Later queries on the same warehouse can read the cache instead of the tables. A larger warehouse has a larger cache. Suspending drops it, and after a resume some queries run slower until it is rebuilt.

Exam trap Resizing down also costs cache. The removed compute resources take their cache with them, much like a suspend.

Set auto-suspend by how much the workload needs the cache. For tasks, suspend immediately. AUTO_SUSPEND is set in seconds and defaults to 600, which is 10 minutes. A value of 0 or NULL means the warehouse never suspends.

Suggested auto-suspend by workload

Exam trap AUTO_SUSPEND is not precise. The background process that suspends warehouses runs only about every 30 seconds.

ALTER WAREHOUSE ... ADD TABLES starts cache warming. It attaches interactive tables to an interactive warehouse, up to 100 tables. A standard warehouse cache only rebuilds as queries run; this warms the cache for the chosen tables. DROP TABLES stops using the cache for those tables but does not drop the tables.

• Result reuse needs an exact query match; even a case or alias change prevents full reuse.
• Persisted results last 24 hours, reset by each reuse up to 31 days.
• Suspending or downsizing a warehouse drops its cache.
• Keep BI warehouses running at least 10 minutes before suspending; ad-hoc ones about 5.

Sources: DESCRIBE RESULT | Snowflake Documentation · Optimizing the warehouse cache | Snowflake Documentation · Using Persisted Query Results | Snowflake Documentation · Warehouse considerations | Snowflake Documentation · ALTER WAREHOUSE | Snowflake Documentation · RESULT_SCAN | Snowflake Documentation

Think first For a given data shape and result, how do you choose between aggregate and window functions, and handle VARIANT data or staged files?

Why it matters: Questions show a query, a data shape or a result and ask which function, clause or setting produces it.

An aggregate function returns one row per group, while a window function returns one row per input row. Semi-structured data sits in VARIANT, OBJECT and ARRAY columns. Unstructured files stay on a stage and are reached through scoped, file or pre-signed URLs, or FILE references.

Aggregates collapse a group; window functions keep every row. Use a window function for running totals, moving averages and rankings. An empty OVER() clause is valid.

Scalar, aggregate and window functions compared

ORDER BY in OVER orders the window, not the query output. Ranking functions such as RANK and NTILE require it. Frames for running totals or moving averages need it too, or the frame is meaningless. Declare window frames explicitly instead of relying on an implied one.

Exam trap ORDER BY 2 inside OVER does not mean the second column. A number there is treated as the constant 2.

In a RANGE frame, CURRENT ROW covers every row with the same ORDER BY value. A RANGE BETWEEN frame with explicit offsets must have exactly one ORDER BY expression. Correlated columns inside window functions, for example in PARTITION BY within a correlated subquery, are not supported.

A VARIANT value holds up to 128 MB of uncompressed data. TYPEOF returns a stored value's native type. Dates and timestamps loaded from JSON are stored as strings, so they can be slower and larger than typed columns. Strings and dates come back in double quotes until you cast them.

Exam trap SQL NULL in a VARIANT means a missing value, while a VARIANT null is a real value. IS_NULL_VALUE tests JSON nulls, not SQL NULL.

Array indexes start at 0. Reading past the end of an array returns NULL, not an error. ARRAY_SLICE returns elements from the start index up to, but not including, the end index. In an OBJECT, each key is a VARCHAR and each value is a VARIANT.

Semi-structured and structured columns need the ON clause. Table-level search optimization covers neither kind. It can speed up point lookups that cast a semi-structured column to VARCHAR. Substring search uses only tokens of at least 5 characters, so LIKE '%TEST%' gains nothing.

Exam trap On a VARIANT column, enabling an element also covers its nested elements. On a structured column, nested elements are not covered.

Unstructured files must sit on an internal or external stage. A FILE value stores only a reference to the file, and it does not change when the file does.

Three ways to reach a staged file

Exam trap Internal stages encrypt files client-side by default, and the type cannot change later. If you want unstructured data access, consider server-side encryption at stage creation.

• Aggregates return one row per group; window functions return one row per input row.
• ORDER BY inside OVER orders only the window, and RANK and NTILE require it.
• Array indexes start at 0, and reading past the end returns NULL.
• Search optimization on VARIANT, OBJECT or ARRAY columns needs the ON clause.

Sources: Analyzing data with window functions | Snowflake Documentation · Semi-structured data types | Snowflake Documentation · Speeding up queries of semi-structured data with search optimization | Snowflake Documentation · Unstructured data types | Snowflake Documentation · Introduction to unstructured data | Snowflake Documentation · REST API for unstructured data support | Snowflake Documentation · Window functions | Snowflake Documentation · Scalar functions | Snowflake Documentation