CertArea
All Snowflake exams

Free DAA-C01 practice test: 30 questions with answers

30 original questions for SnowPro Advanced: Data Analyst, drawn from every topic area. Answer each one to see whether you got it right and why. No sign-up.

Free questions
30
Full bank
100 questions

DAA-C01 exam guide: topics, cost and format

All 30 DAA-C01 questions with answers

The same questions as the test above, grouped by exam topic. Open any question to see the answer and explanation.

Snowflake data model and SQL (5 questions)

  1. Question 1

    In Snowflake, which object is the top-level container that holds schemas and is typically named for an environment or business unit?

    1. AWarehouse
    2. BDatabase
    3. CStream
    4. DStage
    Show answer and explanation
    Correct answer: B

    A database contains schemas and is the top-level container under an account. Warehouses provide compute, stages hold files, and streams track changes.

  2. Question 2

    A Snowflake user needs compute resources to run queries. Which object provides this compute?

    1. AOutbound share
    2. BVirtual warehouse
    3. CInformation schema
    4. DFunctional role
    Show answer and explanation
    Correct answer: B

    Virtual warehouses supply compute for queries. Schemas organize objects, shares distribute data, and roles govern privileges.

  3. Question 3

    A team wants to query a table as it looked two hours ago without restoring it. Which Snowflake feature supports this?

    1. AClustering keys
    2. BTime Travel
    3. CData sharing only
    4. DMaterialized views only
    Show answer and explanation
    Correct answer: B

    Time Travel allows querying historical data within the retention period. Data sharing distributes data, clustering organizes storage, and materialized views store query results.

  4. Question 4

    A Snowflake analyst wants to store query results that are expensive to recompute and must be refreshed automatically when the base table changes. Which object should be used?

    1. AMaterialized view
    2. BA plain text file
    3. CA stage
    4. DTemporary table for one session
    Show answer and explanation
    Correct answer: A

    A materialized view stores precomputed results and maintains them as underlying data changes. Session tables are temporary, text files are outside the platform, and stages hold files.

  5. Question 5

    Analysts want to query semi-structured JSON data stored in a VARIANT column. Which syntax extracts a field called name from the JSON?

    1. Apayload[name]
    2. Bpayload.name()
    3. Cpayload:name
    4. Dpayload->name()
    Show answer and explanation
    Correct answer: C

    Snowflake uses colon notation for VARIANT paths such as payload:name. The other syntaxes are not Snowflake's path notation for this purpose.

Data loading and transformation (5 questions)

  1. Question 6

    A team loads a large set of CSV files daily into a table and wants to avoid loading files that were already loaded. Which Snowflake feature tracks load history for this?

    1. ACOPY INTO with load metadata that prevents re-loading the same files
    2. BRenaming the incoming files with the same name each day
    3. CDeleting the internal stage object after each load run completes
    4. DLoading with TRUNCATE TABLE on every scheduled run before the new files are read
    Show answer and explanation
    Correct answer: A

    COPY INTO tracks file load metadata so already-loaded files are skipped by default. Truncating loses history, deleting stages removes files, and reusing names confuses tracking.

  2. Question 7

    A load fails partway because some rows contain invalid values. The team wants to load the valid rows and see the rejected ones. Which option supports this?

    1. ALoading the file into a VARIANT column with no schema
    2. BON_ERROR set to continue with validation of the rejected rows
    3. CDeleting the bad rows manually in the source file
    4. DON_ERROR set to abort the whole load with no logs
    Show answer and explanation
    Correct answer: B

    ON_ERROR continue loads valid rows and validation reports the rejects. Aborting stops everything, no schema makes errors worse, and manual edits are unreliable.

  3. Question 8

    A data analyst wants to transform raw semi-structured events into a relational table on a schedule. Which combination is most appropriate?

    1. AA task that runs a SQL statement that flattens the VARIANT data into the target table
    2. BExporting the raw events to a spreadsheet and pivoting the columns by hand weekly
    3. CStoring the events as compressed image files in an archive bucket for later
    4. DA manual copy-paste of the raw events into the target table every morning before dashboards refresh
    Show answer and explanation
    Correct answer: A

    A scheduled task with SQL that flattens VARIANT data automates the transformation. Manual copies, spreadsheets and images are not reliable pipelines.

  4. Question 9

    An analyst must merge incoming change records into a target table, updating existing rows and inserting new ones. Which statement performs this in one operation?

    1. AINSERT OVERWRITE only
    2. BMERGE INTO
    3. CCREATE TABLE AS SELECT only
    4. DSELECT INTO
    Show answer and explanation
    Correct answer: B

    MERGE performs conditional updates and inserts in one statement. SELECT INTO is not Snowflake's merge, INSERT OVERWRITE replaces data, and CTAS creates new tables.

  5. Question 10

    A table is updated frequently, and a dashboard needs the latest data within minutes. Which approach keeps the dashboard current with limited manual effort?

    1. ARefreshing the dashboard manually each hour by opening the report and clicking refresh button
    2. BA stream on the table with a task that processes new changes on a short schedule
    3. CReloading the entire table from a full source extract once a month
    4. DTurning off updates to the table during the nightly reporting window
    Show answer and explanation
    Correct answer: B

    Streams with tasks process changes incrementally on a short schedule. Monthly reloads, manual refreshes and disabling updates do not keep data current efficiently.

Data analysis fundamentals (5 questions)

  1. Question 11

    An analyst needs the total sales per region for the current year from a large sales table. Which SQL clause groups rows to compute the totals?

    1. AWHERE region keeps raw rows
    2. BGROUP BY region with SUM(sales)
    3. CORDER BY region sorts result rows
    4. DHAVING region filters the output rows
    Show answer and explanation
    Correct answer: B

    GROUP BY with an aggregate computes totals per group. ORDER BY sorts, WHERE filters rows before grouping, and HAVING filters groups after aggregation.

  2. Question 12

    An analyst wants to keep only regions whose total sales exceed one million. Which clause filters the groups after aggregation?

    1. AHAVING SUM(sales) > 1000000
    2. BWHERE SUM(sales) > 100000
    3. CORDER BY SUM(sales) DESC
    4. DLIMIT 1000000 OFFSET 500
    Show answer and explanation
    Correct answer: A

    HAVING filters on aggregated values. WHERE cannot reference aggregates, ORDER BY sorts, and LIMIT caps rows.

  3. Question 13

    A query must return each order alongside the average order value of its customer's region, without collapsing the rows. Which SQL feature does this?

    1. AA window function such as AVG(...) OVER (PARTITION BY region)
    2. BGROUP BY customer_region with AVG(order_value) in SELECT
    3. CUNION ALL of orders with a regional_orders_archive table
    4. DDISTINCT region, customer_id, order_date, order_status
    Show answer and explanation
    Correct answer: A

    Window functions compute aggregates while keeping every row. GROUP BY collapses rows, DISTINCT removes duplicates, and UNION ALL stacks result sets.

  4. Question 14

    An analyst wants a running total of daily sales ordered by date. Which construct computes this?

    1. AMAX(sale_date) returned as one scalar value
    2. BSUM(sales) GROUP BY sale_date, store_id
    3. CSUM(sales) OVER (ORDER BY sale_date)
    4. DCOUNT(*) WHERE sale_date IS NULL LIMIT 1
    Show answer and explanation
    Correct answer: C

    A windowed SUM ordered by date gives a running total. A group-by without window does not keep rows, COUNT of nulls is unrelated, and MAX returns one value.

  5. Question 15

    A table contains duplicate customer rows, and the analyst needs one row per customer ID. Which technique removes the duplicates?

    1. ACROSS JOIN customers_master with a customer_ids lookup table
    2. BUNION ALL of the customer_master table with a copy of itself
    3. CORDER BY customer_id, created_at DESC, last_login NULLS LAST
    4. DSELECT DISTINCT or a window with ROW_NUMBER to keep one row per ID
    Show answer and explanation
    Correct answer: D

    DISTINCT or ROW_NUMBER partitioning keeps one row per ID. UNION ALL duplicates rows, CROSS JOIN multiplies rows, and ORDER BY does not remove them.

Analytics and visualization (5 questions)

  1. Question 16

    A business user wants a chart that shows the share of total revenue held by each product category at a single point in time. Which chart is most suitable?

    1. AA histogram of product names, binned by first letter of each name
    2. BA line chart of revenue tracked over several decades
    3. CA scatter plot of product IDs against the warehouse shipping weight
    4. DA 100 percent stacked bar or a pie chart with few categories
    Show answer and explanation
    Correct answer: D

    Part-to-whole comparisons suit stacked bars or pies with few categories. Trend lines, scatter plots of IDs and histograms of names do not show shares.

  2. Question 17

    An analyst wants to compare the distribution of order values across customer segments. Which chart type is most appropriate?

    1. AA single number tile
    2. BA table listing every order
    3. CA pie chart of customer IDs
    4. DBox plot by segment
    Show answer and explanation
    Correct answer: D

    Box plots by segment show distributions and medians across groups. Pies of IDs, single numbers and unfiltered tables do not compare distributions.

  3. Question 18

    A dashboard metric is calculated from a complex query and must be explained to stakeholders. Which practice improves trust?

    1. ADocument the metric definition, data source, and filters used alongside the dashboard
    2. BHide the calculation logic from stakeholders, presenting just the final figure on the dashboard
    3. CUse an unexplained metric name on the dashboard with no supporting description
    4. DChange the metric definition every week without recording notes or dates
    Show answer and explanation
    Correct answer: A

    Documented definitions, sources and filters build trust and reproducibility. Hidden calculations, unexplained names and undocumented changes erode confidence.

  4. Question 19

    A stakeholder asks why the current month's revenue appears lower than expected. Which first step is most appropriate?

    1. AReport the figure to the stakeholder without checking it first
    2. BRemove the revenue metric from the dashboard until the next data refresh
    3. CAssume the source data is wrong and rebuild the dashboard at once
    4. DCheck the filters, date boundaries, and whether late-arriving data is missing
    Show answer and explanation
    Correct answer: D

    Checking filters, date boundaries and late data identifies common causes. Assuming error, reporting without checks and removing metrics are unhelpful.

  5. Question 20

    An analyst must present a single KPI with a comparison to the target and a status indicator. Which approach is most suitable?

    1. AA KPI tile showing the actual, target, and variance with a conditional colour rule
    2. BA raw table listing every monthly KPI value since launch, unfiltered
    3. CA word cloud built from the KPI name, its description and its owning team's tags for review
    4. DA pie chart of the KPI's actual value split across time periods and regional segments
    Show answer and explanation
    Correct answer: A

    A KPI tile with target, variance and status rules communicates performance. Pies, word clouds and raw tables are poor fits for a single KPI status.

Governance and security (5 questions)

  1. Question 21

    A company must mask personal email addresses for analysts who do not have a specific privilege, while other analysts see the full value. Which Snowflake feature implements this?

    1. AA dynamic masking policy applied to the email column based on the role
    2. BA separate copy of the table with asterisks typed in by hand for each team member
    3. CRenaming the email column to a less revealing label such as contact_ref
    4. DEncrypting the entire database at rest with a single customer-managed key in storage
    Show answer and explanation
    Correct answer: A

    Dynamic masking policies apply role-based masking at query time. Manual copies, database-wide encryption alone and renaming do not implement role-based masking.

  2. Question 22

    An auditor asks who queried a sensitive table last month. Which source provides this information?

    1. AThe warehouse's display name
    2. BAccount usage query history views
    3. CThe table's comment metadata
    4. DThe analyst's laptop shell log
    Show answer and explanation
    Correct answer: B

    Account usage views record query history including users. Comments, warehouse names and local laptop logs do not record database access.

  3. Question 23

    A data product must be discoverable by the organization, and its owner and sensitivity classification must be visible. Which practice supports this?

    1. APublishing the data product to the shared catalogue without assigning owners
    2. BKeeping the owner name, sensitivity level, and usage notes in a forwarded email thread
    3. CCatalogue entries with owners, classification tags, and descriptions
    4. DHiding the data from the catalogue so that it stays out of search results
    Show answer and explanation
    Correct answer: C

    Catalogue entries with owners, tags and descriptions support discovery and governance. Email threads, unowned publishing and hiding data do not.

  4. Question 24

    A company wants to share a curated view with a partner without exposing the underlying table's other columns. Which object is appropriate?

    1. AA full copy of the table, refreshed nightly
    2. BA secure view that exposes only the approved columns
    3. CThe table's raw Parquet file unloaded to an internal named stage
    4. DThe base table granted to the PUBLIC role with direct read access
    Show answer and explanation
    Correct answer: B

    Secure views expose only approved columns and protect definitions. Full copies, raw stage files and public base tables expose more than intended.

  5. Question 25

    A compliance team asks how long deleted data can be recovered in the platform. Which feature determines this window?

    1. AThe number of users with login rights
    2. BTime Travel retention period for the object
    3. CThe virtual warehouse size setting
    4. DThe BI dashboard's colour and font theme settings
    Show answer and explanation
    Correct answer: B

    The Time Travel retention period defines how long deleted or changed data can be recovered. Warehouse size, user counts and themes are unrelated.

Performance and cost (5 questions)

  1. Question 26

    A query on a large table takes minutes. Its plan shows that it scans almost all micro-partitions. Which change most likely improves performance?

    1. AUse SELECT * to read more columns from each micro-partition, widening the table scan
    2. BRun the query at midnight, when the warehouse is idle and the cache is cold
    3. CIncrease the number of columns in the table definition and the stored row width
    4. DAdd a filter that matches the table's clustering or natural order, enabling partition pruning
    Show answer and explanation
    Correct answer: D

    Filters aligned with clustering enable pruning and reduce scanned partitions. SELECT * reads more data, extra columns add work, and timing does not change the scan.

  2. Question 27

    A dashboard issues the same expensive aggregate query many times per hour on data that changes slowly. Which feature reduces cost and latency?

    1. AIncreasing the warehouse size permanently for no reason
    2. BRunning the query with no caching
    3. CA materialized view or result caching for the aggregate
    4. DDeleting the aggregate
    Show answer and explanation
    Correct answer: C

    Materialized views and caching reuse computed results. Removing caching and oversizing warehouses permanently increase cost, and deletion removes the function.

  3. Question 28

    A warehouse is consuming more credits than budgeted because it runs continuously even when idle. Which setting helps reduce cost?

    1. AIncreasing the warehouse size to a larger tier
    2. BDisabling scheduled queries that run on the warehouse
    3. CAuto-suspend with an appropriate idle timeout
    4. DRemoving unused tables from the shared database schema
    Show answer and explanation
    Correct answer: C

    Auto-suspend stops compute when idle, lowering credit use. Disabling queries, enlarging warehouses and removing tables do not reduce idle cost appropriately.

  4. Question 29

    A team wants to see which queries are the most expensive in the last week. Which view provides query history with timing and resource usage?

    1. AThe user profile page of the admin console
    2. BThe table comment fields and column descriptions
    3. CThe warehouse name list with default sizes
    4. DThe query history views in the account usage schemas
    Show answer and explanation
    Correct answer: D

    Query history views show execution and resource usage. Comments, profile pages and name lists do not provide query cost data.

  5. Question 30

    An analyst wants to minimize repeated work when running a complex query many times in a session with identical inputs. Which mechanism applies?

    1. ATurning off all caching
    2. BAdding random functions to the query
    3. CResult caching
    4. DChanging the query text each time
    Show answer and explanation
    Correct answer: C

    Result caching reuses prior results when inputs have not changed. Changing text, random functions and disabling caching do not help.