Snowflake · DAA-C01
SnowPro Advanced: Data Analyst (DAA-C01) Practice Test
Validates advanced skills in applying comprehensive data analysis principles using Snowflake, including data ingestion, advanced SQL, UDFs, and predictive and diagnostic analytics. Requires 1+ years of hands-on Snowflake experience and an active SnowPro Core certification.
Exam Details
Questions
600
Duration
115 minutes
Passing Score
750/1000
Difficulty
ProfessionalLast Updated
Jun 2026
Topics Covered
Data Ingestion and PreparationData TransformationAdvanced SQL Query DevelopmentSnowflake Built-in Functions and UDFsDescriptive and Diagnostic Data AnalysisPredictive Data AnalysisData Presentation and Business Requirements
SnowPro Advanced: Data Analyst (DAA-C01) Practice Exam Preparation
Use this DAA-C01 practice exam to prepare for SnowPro Advanced: Data Analyst (DAA-C01) with realistic questions, detailed explanations, and focused study modes. The practice bank includes 600 questions for Snowflake DAA-C01, so you can review the exam steadily instead of relying on one long cram session.
As you practice, pay extra attention to recurring topics such as Data Ingestion and Preparation, Data Transformation, Advanced SQL Query Development, Snowflake Built-in Functions and UDFs, and Descriptive and Diagnostic Data Analysis. Start with short sessions to identify weak areas, then move into timed quizzes once your accuracy is consistent.
The explanations are especially useful when you want to connect exam wording to the responsibilities and scenarios described in the official certification guidance. Use the free preview first, then unlock the full question bank when you are ready to build a complete study routine.
Exam Overview
The SnowPro® Advanced: Data Analyst (DAA-C01) certification is Snowflake's advanced-tier credential for data professionals who apply comprehensive analytical techniques within the Snowflake Data Cloud. The exam validates expertise across the full analytical workflow: acquiring and loading structured, semi-structured, and unstructured data; transforming and preparing it for analysis; writing and optimizing complex SQL; leveraging Snowflake built-in functions and user-defined functions (UDFs); and performing descriptive, diagnostic, and predictive analytics to surface business insights.
Unlike entry-level Snowflake certifications, this exam expects candidates to demonstrate production-grade fluency with Snowflake-specific features such as Snowsight dashboards, Time Travel, semi-structured data handling (JSON, Parquet, Avro, ORC, XML), query optimization via partition pruning and materialized views, and integration with BI tools including Tableau, Looker, and Power BI. Delivered by Pearson VUE, the exam consists of 65 scored multiple-choice and multiple-select questions to be completed in 115 minutes, with a passing score of 750 on a scaled 0–1000 range. The registration fee is $375 USD per attempt (a 20% regional discount applies in India).
Who Should Take This Exam
This certification is designed for working data professionals who spend the majority of their time querying, transforming, and analyzing data inside Snowflake. Relevant job titles include Data Analyst, Analytics Engineer, Business Intelligence Developer, ELT Developer, and BI Professional. Candidates typically have responsibility for building analytical pipelines, authoring complex SQL reports, creating dashboards in Snowsight or connected BI tools, and communicating data-driven findings to business stakeholders.
The exam is appropriate for those who have moved beyond foundational Snowflake usage and are ready to demonstrate advanced competency in forecasting, UDFs, and diagnostic analytics. It is not intended for platform administrators or data engineers focused primarily on infrastructure — those roles are better served by the SnowPro Advanced: Administrator or SnowPro Advanced: Data Engineer tracks.
Prerequisites
Snowflake requires candidates to hold an active SnowPro Core Certification before sitting for the DAA-C01 exam. The SnowPro Core validates foundational knowledge of Snowflake architecture, data loading, querying, and account management, and its concepts are assumed knowledge for the advanced exam. There is no formal academic prerequisite, but Snowflake recommends candidates have at least one year of hands-on, production-level experience working as a Data Analyst in the Snowflake Data Cloud.
Beyond the formal requirement, candidates should be fluent in advanced SQL (window functions, CTEs, recursive queries, analytic functions) and have practical experience with at least one scripting or programming language for UDF development (Python, JavaScript, or Java are common choices). Familiarity with data modeling concepts, semi-structured data formats (JSON, Parquet), and at least one BI or visualization tool is strongly recommended before attempting the exam.
Exam Format
The DAA-C01 exam consists of 65 questions in multiple-choice (single correct answer) and multiple-select (two or more correct answers) formats. Candidates have 115 minutes to complete the exam. It is delivered online through Pearson VUE, which supports both remote proctored (OnVUE) and in-person testing center formats. The exam is currently offered in English only.
Scoring uses a scaled system ranging from 0 to 1000; a minimum scaled score of 750 is required to pass. Snowflake does not publish a fixed percentage-correct passing threshold, as the scaled score accounts for question difficulty. There is no published limit on retake attempts, and each attempt requires the full $375 USD registration fee. Snowflake recommends downloading the official exam study guide from the certification page prior to registration, as it contains the most current domain objectives and any updates to exam content.
Skills Measured
- 1.Domain 1 – Data Ingestion and Preparation (approx. 15–20%): Retrieving structured (CSV), semi-structured (JSON, Parquet, Avro, ORC, XML), unstructured, and synthetically generated data; performing data discovery to assess available datasets and identify elements required for business goals; profiling data for quality, granularity, and completeness; enforcing data integrity constraints; and using Snowflake's COPY INTO, Snowpipe, and internal/external stages for data loading.
- 2.Domain 2 – Data Transformation (approx. 15–20%): Cleaning and reshaping raw data using SQL transformations and Snowflake's FLATTEN, PARSE_JSON, and LATERAL JOIN capabilities for semi-structured sources; designing and implementing scalable transformation pipelines; applying data normalization and denormalization techniques; and building reusable transformation logic using views, stored procedures, and tasks.
- 3.Domain 3 – Advanced SQL Query Development (approx. 20–25%): Writing and troubleshooting complex queries using window functions, CTEs, subqueries, and recursive SQL; optimizing query performance through partition pruning, clustering keys, materialized views, and result caching; handling data anomalies and NULLs; and interpreting query profiles in Snowsight to diagnose performance bottlenecks.
- 4.Domain 4 – Snowflake Built-in Functions and UDFs (approx. 15–20%): Applying date/time, string, numeric, conditional, and aggregate functions; working with semi-structured data functions (GET, GET_PATH, OBJECT_CONSTRUCT); creating and invoking scalar and tabular User-Defined Functions (UDFs) in SQL, Python, JavaScript, or Java; and using User-Defined Table Functions (UDTFs) for advanced analytical use cases.
- 5.Domain 5 – Descriptive and Diagnostic Data Analysis (approx. 10–15%): Summarizing datasets using statistical measures (mean, median, standard deviation, percentiles); identifying outliers, trends, and anomalies; using exploratory data analysis techniques within Snowflake; applying Snowflake's TABLESAMPLE for large-dataset profiling; and building descriptive reports and interactive dashboards in Snowsight.
- 6.Domain 6 – Predictive Data Analysis (approx. 10–15%): Applying time-series forecasting methods available natively in Snowflake (including Snowflake ML-powered forecasting functions); using regression techniques for predictive modeling; interpreting model outputs and communicating forecast uncertainty; and understanding when to leverage Snowpark for more advanced ML workflows.
- 7.Domain 7 – Data Presentation and Business Requirements (approx. 10–15%): Translating business questions into analytical requirements; exporting data in formats appropriate for downstream consumers (CSV, JSON, Parquet); connecting Snowflake to BI tools such as Tableau, Power BI, and Looker via Partner Connect or direct ODBC/JDBC; building stakeholder-facing dashboards and reports; and applying data storytelling principles to communicate insights effectively.
Study Tips
- Download and work through the official Snowflake SnowPro Advanced: Data Analyst Study Guide (available at learn.snowflake.com) before anything else — it defines the exact domain objectives and is the authoritative source for what will be tested.
- Build hands-on practice in a free Snowflake trial account: load semi-structured JSON and Parquet files using COPY INTO and Snowpipe, practice FLATTEN and PARSE_JSON queries, and profile the results in Snowsight's query editor to understand explain plans and partition pruning.
- Master window functions, CTEs, and analytic SQL — these appear heavily across multiple domains. Practice writing RANK, DENSE_RANK, LAG, LEAD, NTILE, and RATIO_TO_REPORT functions against realistic datasets, and focus on understanding when each is appropriate.
- Experiment with Snowflake's native ML forecasting functions (introduced in 2023–2024) and build at least one UDF in Python or JavaScript. The predictive analytics and UDF domains require applied experience that is difficult to gain through reading alone.
- Use Snowflake's free Hands-On Essentials and Data Warehousing Workshop labs on Snowflake University (learn.snowflake.com) to reinforce practical skills, and supplement with the Pluralsight SnowPro Advanced: Data Analyst learning path, which maps directly to DAA-C01 objectives.
- Review the SnowPro Core Certification study material as a baseline — the DAA-C01 builds on Core concepts such as micro-partitions, clustering, Time Travel, data sharing, and Snowflake architecture. Gaps in Core knowledge will surface during the advanced exam.
- Practice with timed mock exams (65 questions in 115 minutes ≈ 1.75 minutes per question). Focus especially on multiple-select questions, which require identifying all correct answers with no partial credit — a common source of lost points for candidates who know the material but rush.
Career Benefits
Earning the SnowPro Advanced: Data Analyst credential signals to employers that a candidate can operate independently at an advanced level on the Snowflake platform — a differentiated skill as Snowflake's market share in the cloud data warehouse space continues to grow across financial services, retail, healthcare, and technology sectors. Job titles commonly held by certified candidates include Senior Data Analyst, Analytics Engineer, BI Engineer, and Data Scientist, with US salaries for Snowflake-proficient analysts typically ranging from $95,000 to $145,000 depending on seniority, industry, and location.
The DAA-C01 sits within Snowflake's Advanced certification tier, positioning it above the SnowPro Core and SnowPro Associate credentials and alongside other Advanced specializations (Data Engineer, Architect, Administrator). Unlike vendor-neutral analytics certifications, the DAA-C01 directly validates platform-specific expertise that many employers explicitly list as a job requirement when Snowflake is the primary data platform. Holding both the SnowPro Core and an Advanced specialization is increasingly recognized as a strong signal of job-ready Snowflake proficiency in competitive data hiring pipelines.
Sample Questions
5 sample questions with answers and explanations. Start a practice session to test yourself across all 600 questions.
Preview — answers shown1. A data analyst at Northwind Healthcare is querying a PATIENTS table with a VARIANT column named DIAGNOSIS_CODES that stores arrays of diagnosis strings. Some patients are registered but not yet diagnosed, resulting in an empty array ([]). The analyst writes the following query: SELECT p.patient_id, f.value::STRING AS diagnosis FROM patients p, LATERAL FLATTEN(input => p.diagnosis_codes) f; The analyst notices that patients with empty arrays do not appear in the results at all. Which statement correctly explains this behavior and provides the appropriate fix? (Select one!)
AAdding RECURSIVE => TRUE to the FLATTEN call is required to process root-level arrays and ensure rows with empty arrays appear in the output
BEmpty arrays are stored as NULL in VARIANT columns; casting the column to ARRAY type before calling FLATTEN preserves those rows in the output
CFLATTEN silently drops rows where the input array is empty ([]) or NULL by default because OUTER defaults to FALSE; adding OUTER => TRUE to the FLATTEN call includes those rows with NULL values for the flattened output columns
DThe issue is caused by the default MODE setting of OBJECT; changing to MODE => ARRAY forces FLATTEN to process and include rows with empty arrays
Explanation
FLATTEN's OUTER parameter defaults to FALSE, which silently discards any row where the input value is an empty array ([]) or NULL. This is the most significant FLATTEN gotcha in Snowflake. Adding OUTER => TRUE causes FLATTEN to emit one row per input row even when the array is empty, with the KEY, INDEX, and VALUE columns returned as NULL, preserving the parent patient row in the result set. Casting to ARRAY type does not prevent the issue — empty arrays still produce no output rows without OUTER => TRUE. The MODE parameter controls whether FLATTEN processes arrays, objects, or both (default is BOTH) and is not the cause of empty arrays being dropped. RECURSIVE => TRUE enables traversal of nested structures within elements, not processing of root-level empty arrays.
2. A data engineering team at Adatum Retail has a source table configured with a 7-day Time Travel retention period. A standard stream was created on this table to capture incremental changes for a nightly ETL pipeline. Due to a pipeline failure, the stream is not consumed for 16 consecutive days. What is the state of the stream after 16 days, and why? (Select one!)
AThe stream remains valid because Snowflake automatically extends source table retention up to 90 days to protect active streams
BThe stream remains valid because Snowflake automatically extends the table's Time Travel retention to match any stream consumption cycle without limit
CThe stream is stale only after the FAIL_SAFE period of 7 additional days also elapses beyond the Time Travel window
DThe stream is stale because the stream's offset fell outside the maximum extended retention window, and the stream must be recreated
Explanation
A Snowflake stream becomes stale when its offset falls outside the source table's data retention period. Snowflake automatically extends the source table's Time Travel retention to prevent stream staleness, but only up to the maximum governed by MAX_DATA_EXTENSION_TIME_IN_DAYS, which defaults to 14 days. In this scenario the table has a 7-day base retention and the stream was unconsumed for 16 days. Snowflake would have extended retention up to 14 days (the maximum), but 16 days exceeds even this extended window. The stream's offset is therefore outside the available historical data, making it stale and irrecoverable — it must be dropped and recreated. The 90-day figure refers to the maximum Time Travel retention configurable for Enterprise Edition tables, not the stream auto-extension limit. FAIL_SAFE is a separate disaster-recovery mechanism and does not contribute to stream offset validity.
3. A data analyst at Contoso Retail is building a session summary report. For each transaction, the analyst needs to display the final (last) transaction amount within each customer session. The analyst writes: SELECT customer_id, transaction_id, amount, LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_time) AS last_amount FROM transactions; After running the query, the analyst notices that last_amount equals amount for every row instead of the session's final transaction value. What is the root cause of this behavior? (Select one!)
AWhen ORDER BY is specified without an explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so LAST_VALUE evaluates only rows up to the current row and always returns the current row's value
BLAST_VALUE is not supported with PARTITION BY in Snowflake and must be replaced with FIRST_VALUE using DESC ordering
CLAST_VALUE requires a DESC ORDER BY clause to traverse the partition in reverse and return the final value
DPARTITION BY and ORDER BY cannot be combined in the same OVER clause when using navigation window functions
Explanation
When ORDER BY is included in a window function's OVER clause without an explicit frame clause, Snowflake applies the default frame of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means LAST_VALUE only evaluates rows from the start of the partition through the current row, so its result is always the current row's own value — not the partition's final value. The fix is to add an explicit frame clause: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This expands the evaluation window to all rows in the partition, allowing LAST_VALUE to correctly return the final row's value. LAST_VALUE does not require a DESC ORDER BY; the frame specification is what controls which row is considered last. LAST_VALUE is a fully supported Snowflake window function. PARTITION BY and ORDER BY are compatible with all window functions including LAST_VALUE.
4. A data engineering team at Northwind Analytics is building a multi-step batch ETL pipeline in Snowflake. The pipeline generates several intermediate staging tables that accumulate data for a few hours before the next pipeline run completely replaces them. Multiple pipeline steps running in separate sessions must all be able to read and write the staging tables. The team does not need Time Travel or Fail-safe recovery for these tables and wants to minimize storage costs. Which table type best meets these requirements? (Select one!)
AExternal tables that reference staging data stored in cloud object storage
BTransient tables, which persist across sessions but incur no Fail-safe storage overhead
CPermanent tables with DATA_RETENTION_TIME_IN_DAYS set to 0 to disable Time Travel
DTemporary tables, which are visible across sessions and automatically expire after a configurable TTL
Explanation
Transient tables are designed precisely for intermediate staging workloads that need cross-session persistence but do not require data recovery protections. They persist across sessions and are visible to all users with appropriate privileges, allowing multiple pipeline steps running in different sessions to access the same staging tables. Transient tables incur zero Fail-safe overhead and can be configured with 0-day Time Travel retention, making them significantly cheaper than equivalent permanent tables. Permanent tables set to 0-day Time Travel still accumulate 7 days of Fail-safe storage at no additional charge to the user but at a real storage cost — they cost more than transient tables for equivalent data volumes. Temporary tables are session-scoped and dropped automatically when the creating session ends, making them unsuitable for multi-session pipeline steps that must share staging data. External tables reference data stored outside Snowflake and introduce additional complexity without reducing internal staging storage costs.
5. A financial analyst at Tailspin Trading needs to join a STOCK_PRICES table (capturing every market tick with a tick_ts timestamp) with a TRADES table (recording customer purchase events with an execution_ts timestamp). Each trade record must be matched to the stock price that was valid at the exact moment of the trade — specifically, the most recent tick_ts that is at or before the trade's execution_ts. Exact timestamp matches are rare. Which approach should the analyst use? (Select one!)
AUse a correlated subquery with MAX(tick_ts) WHERE stock_prices.tick_ts <= trades.execution_ts evaluated for each trade row
BUse an ASOF JOIN with a MATCH_CONDITION specifying trades.execution_ts >= stock_prices.tick_ts
CUse an INNER JOIN on the exact timestamp columns after truncating both to the nearest minute using DATE_TRUNC
DUse a LATERAL JOIN with a ROW_NUMBER window function ordered by tick_ts descending to identify the nearest preceding price
Explanation
ASOF JOIN is specifically designed for time-series alignment scenarios where exact timestamp matches are unavailable. The MATCH_CONDITION trades.execution_ts >= stock_prices.tick_ts directs Snowflake to find the most recent stock price at or before each trade's execution timestamp. ASOF JOIN is significantly more performant than a correlated subquery approach, which requires a separate aggregation pass per trade row. Using INNER JOIN with DATE_TRUNC alignment loses sub-minute precision and still fails when the aligned timestamps do not correspond to actual tick records. A LATERAL JOIN with ROW_NUMBER can produce the correct result but is far less efficient than ASOF JOIN, which is purpose-built for this nearest-predecessor lookup pattern. When no matching prior row exists in STOCK_PRICES, ASOF JOIN null-pads the result, preserving all trade records rather than silently dropping unmatched rows.
$7.99
One-time access to this exam
Full access to all 600 questions
Or $15/mo for all 253 exams
Detailed explanations
Free preview stays available