SnowPro Advanced: Data Analyst Complete Guide

The SnowPro Advanced Data Analyst (ADA-C01) is the Advanced-level certification that validates your data analytics skills on Snowflake. It tests practical analytics chops including SQL window functions, data modeling, Snowsight visualization, and performance tuning. This article breaks down the 2026 exam scope and how to prepare for each domain.

Exam Overview and Specifications

The Data Analyst exam is part of the SnowPro Advanced track and evaluates your ability to perform analytics and reporting on Snowflake. It requires SnowPro Core certification as a prerequisite and demands more specialized SQL and analytics skills.

Exam Domains and Weighting

Below are the exam domains based on the official Study Guide. Snowflake SQL Analytics carries the largest weight at 35% of the exam.

Mastering SQL Window Functions

SQL window functions are the single most important topic on the Data Analyst exam. Snowflake supports the standard SQL:2003 window functions broadly, and the exam tests them through applied, real-world scenarios.

Ranking Functions (ROW_NUMBER / RANK / DENSE_RANK)

The differences between these three ranking functions show up over and over. You need to know exactly how numbers are assigned when ties occur: ROW_NUMBER always returns unique sequential numbers, RANK gives ties the same number and then skips, and DENSE_RANK gives ties the same number without skipping.

-- Compare the three ranking functions for sales
SELECT
  salesperson,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM sales_summary;

Time-Series Analysis with LEAD / LAG

LEAD (next row) and LAG (previous row) are workhorses for month-over-month comparisons and daily delta calculations. The exam tests that the second argument controls the offset and the third argument sets a default value.

-- Compute month-over-month revenue change
SELECT
  month_date,
  monthly_revenue,
  LAG(monthly_revenue, 1, 0) OVER (ORDER BY month_date) AS prev_month,
  monthly_revenue - LAG(monthly_revenue, 1, 0) OVER (ORDER BY month_date) AS mom_change
FROM monthly_sales;

FRAME Clauses (ROWS BETWEEN / RANGE BETWEEN)

Window FRAME clauses are an advanced topic, but you cannot skip them for the Data Analyst exam. They are required to implement running sums and moving averages. The key distinction to memorize: ROWS BETWEEN specifies a physical number of rows, while RANGE BETWEEN specifies a logical range of values.

-- 3-month moving average
SELECT
  month_date,
  monthly_revenue,
  AVG(monthly_revenue) OVER (
    ORDER BY month_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3m
FROM monthly_sales;

QUALIFY: Snowflake's Powerful Native Clause

QUALIFY is a clause that Snowflake adopted early. It lets you filter on the results of window functions just like WHERE does for base columns. Because you don't need a wrapping subquery or CTE, your SQL stays clean. The exam tests the QUALIFY execution order: WHERE → GROUP BY → HAVING → QUALIFY.

-- Top 3 per department, filtered directly with QUALIFY
SELECT
  department,
  salesperson,
  revenue,
  RANK() OVER (PARTITION BY department ORDER BY revenue DESC) AS dept_rank
FROM sales_detail
QUALIFY dept_rank <= 3;

Data Modeling Topics on the Exam

The Data Analyst exam tests your design decisions for analytics-oriented data models. Because Snowflake uses columnar storage, the level of normalization you choose directly impacts query performance.

• Star schema: separates fact and dimension tables. JOINs stay one level deep, making it ideal for analytics queries.
• Snowflake schema: further normalizes dimensions. Storage efficiency improves but JOIN count goes up.
• OBT (One Big Table): a denormalized mega-table. Fast with no JOINs, but causes data duplication.
• Secure View: a pattern for sharing analytics results while enforcing row-level control via Row Access Policy.

Working with Snowsight Dashboards

Snowsight is Snowflake's native visualization tool, letting you build dashboards directly from SQL worksheets. The exam targets the following points.

• How to create dashboard tiles from a worksheet
• Filter linkage: how changing one filter propagates to multiple tiles
• Choosing chart types: when to use bar, line, scatter, and heatmap
• Dashboard sharing and access control (how to grant USAGE privilege)
• Snowsight's limits: where it fits next to external BI tools like Tableau and Power BI

Performance Optimization

The Data Analyst exam also covers query acceleration techniques. Reading a Query Profile is mandatory knowledge.

How to Read a Query Profile

• Bytes Scanned: check whether full scans are happening; measure the effect of your Clustering Key
• Spillage to Local/Remote Storage: disk spills caused by insufficient memory; a signal to rethink warehouse size
• Pruning efficiency: the Partition Pruning ratio; tells you whether your Clustering Key design is right
• JOIN Explosion: detects unintended many-to-many JOINs blowing up row counts

Using Result Cache

Snowflake caches identical query results for 24 hours. Because cached results return without spinning up a warehouse, this is highly effective for accelerating reporting queries. However, the cache is invalidated when the underlying data changes. You can disable caching with the session parameter USE_CACHED_RESULT = FALSE.

Data Governance and Analytics Security

Analysts often handle data that includes personal information, so understanding governance features is essential.

• Dynamic Data Masking: masks column values based on role; for example, displaying email addresses as "***@***" for any role other than ACCOUNTADMIN
• Row Access Policy: restricts which rows a role or user can see; a classic example is showing regional sales data only to that region's owner
• Object Tagging: tags tables and columns with labels like PII or Confidential, managed centrally from the Governance Dashboard
• Access History: an audit log of who accessed what data and when; available on Enterprise Edition and above

Study Strategy for Passing

Because the Data Analyst exam translates directly to SQL skill, you must do hands-on query practice in addition to reading.

• Use the Snowflake free trial with the SAMPLE_DATA schema (TPCH_SF1) for intensive window function practice
• Drill Snowflake-specific syntax like QUALIFY and PIVOT/UNPIVOT
• Actually build a Snowsight Dashboard to experience filter linkage and chart design firsthand
• Practice reading Query Profiles: intentionally write inefficient queries and train yourself to identify the bottleneck
• Read through every reference doc listed in the official Study Guide (especially the window function reference)

Test Yourself

Frequently Asked Questions