Practice Free DAA-C01 SnowPro Advanced: Data Analyst Exam Exam Questions Answers With Explanation

In the field of data warehousing and business intelligence (BI), choosing the right data model is crucial for long-term maintainability and user accessibility. While a single flat table might seem simple initially, dimensional modeling (typically using Star or Snowflake schemas) provides distinct advantages for enterprise analytics.

1. Scalability and Flexibility (Option C)

Combining all attributes into a single flat table creates a highly rigid structure. Every time a new attribute is added to a dimension (e.g., adding a "Promotion Category" to a product), the entire flat table must be rewritten or altered, which is inefficient for large datasets. Furthermore, flat tables often contain redundant data, leading to "update anomalies" where a change in a dimension attribute must be propagated across millions of rows. A dimensional model separates changing business processes (Facts) from the context of those processes (Dimensions), allowing the schema to scale and evolve independently.

2. Ad-hoc Analysis for Power Users (Option D)

Dimensional models are specifically designed to be intuitive for business users and BI tools. By organizing data into Facts (measurable metrics) and Dimensions (descriptive attributes), power users can easily "slice and dice" data across different hierarchies. For example, a user can quickly run an ad-hoc query to compare "Total Sales" (Fact) by "Store Region" (Dimension) and "Calendar Month" (Dimension). This structure provides a predictable and standardized "language" for the data, making it easier for users to build their own reports without needing a Data Analyst to create a custom flat table for every specific request.

Evaluating the Distractors:

Option A and E: These are common misconceptions. Modern cloud data warehouses like Snowflake are often highly optimized for wide "flat" tables due to columnar storage and sophisticated pruning. In many cases, a flat table may actually outperform a multi-table join (dimensional model) because it avoids the computational overhead of the join itself.

Option B: This is factually incorrect. Flat tables are denormalized (repeating data), which generally takes more storage space. Dimensional modeling is a form of normalization that saves space by storing descriptive strings once in a dimension table rather than repeating them for every transaction in a fact table.

This error occurs due to a misunderstanding of how Snowflake resolves object names and the location of the Shared Snowflake Database. In Snowflake, the ACCOUNT_USAGE schema is a collection of views that provide comprehensive historical data about account activities, including credit consumption, storage, and query history. These views are stored within the system-defined database named SNOWFLAKE.

When the Data Analyst executes the query shown in the image, they are operating within the database context of DB1 and the schema context of PUBLIC. Because the query references ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY without a fully qualified name, the Snowflake compiler attempts to resolve the object starting from the current database context. Consequently, it looks for a schema named ACCOUNT_USAGE inside DB1. As the error message correctly indicates, Schema 'DB1.ACCOUNT_USAGE' does not exist.

To fix this, the Analyst has two options:

Fully Qualify the Object Name: Modify the query to reference the full path: FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY. This is the most common best practice as it allows the query to run regardless of the user's current session context.

Change the Context: Use the command USE DATABASE SNOWFLAKE; before running the query. This changes the session's database context so that ACCOUNT_USAGE can be found.

It is a common misconception (represented in Option A) that only an ACCOUNTADMIN can access these views. While access is restricted by default, the ACCOUNTADMIN can grant IMPORTED PRIVILEGES on the SNOWFLAKE database to other roles, such as a Data Analyst role. However, the specific error shown is a namespace resolution error, not a permission-denied error (which would typically say "Insufficient privileges"). Therefore, changing the context or fully qualifying the name is the direct solution to this specific failure.

In Snowsight, the Scorecard chart type is specifically designed to highlight a single key metric (the "Value") and optionally compare it against a static or dynamic benchmark (the "Comparison"). This visualization is a core component of the Data Presentation and Data Visualization domain, as it provides an immediate "at-a-glance" status for high-level KPIs.

1. Interpreting the Scorecard Components:

Primary Value: The large, bold number (38) represents the actual value calculated by the underlying query. In this scenario, it is the company's average employee age.

Secondary Metric (The Percentage): When a scorecard is configured with a comparison value, Snowsight automatically calculates the percentage difference between the primary value and that comparison.

Visual Indicators: The green upward arrow indicates that the primary value is higher than the comparison value. If the value were lower, the arrow would point downward and typically appear in red.

2. The Mathematical Calculation:

The 12% shown in the exhibit is the result of the percentage change formula:

Evaluating the Options:

Options A and D are incorrect because they misidentify the large number (38) as the "comparison." In Snowflake's UI, the large number is always the primary metric being tracked, not the target it is being measured against.

Option B is incorrect because the percentage in a scorecard represents relative difference, not a statistical measure like standard deviation.

Option C is the 100% correct interpretation. It correctly identifies 38 as the current company value and 12% as the calculated difference from the industry benchmark of 34. This level of visual literacy is expected of a SnowPro Advanced: Data Analyst to ensure dashboard insights are communicated accurately to stakeholders.

Snowsight provides special System Keywords that allow analysts to create dynamic, interactive dashboards without hard-coding dates. Two of the most critical keywords are :daterange and :datebucket.

The :daterange keyword is designed to filter the volume of data based on a time period selected by the user in the dashboard UI (e.g., "Last 30 days" or "Current Year"). Because it acts as a filter on the underlying data, it must be placed in the WHERE clause of the SQL statement (e.g., WHERE created_at = :daterange). This ensures that only the records within the selected timeframe are processed by the query.

The :datebucket keyword is used for time-series aggregation. It allows the user to dynamically change the granularity of the data—for example, switching a chart from "Daily" to "Monthly" views without rewriting the query. To achieve this, :datebucket is used inside a date-truncation function in the SELECT list and, crucially, must be included in the GROUP BY clause to correctly aggregate the metrics (e.g., GROUP BY 1 or GROUP BY :datebucket).

Evaluating the Options:

Option A is incorrect because :datebucket is for grouping/truncation, not for filtering in a WHERE clause.

Option B is incorrect because :daterange is a filter (returning a range), not a scalar value suitable for a SELECT list.

Option C is incorrect for the same reasons as A and B.

Option D is the 100% correct answer. It follows the standard Snowsight design pattern: :daterange restricts the data rows in the WHERE clause, while :datebucket defines the temporal aggregation level in the GROUP BY clause.

In Snowflake, the performance of a metadata-based query (like MAX, MIN, or COUNT) is typically near-instantaneous because Snowflake maintains constant-time statistics in its Cloud Services layer. For a standard table, SELECT MAX(num) does not even require a virtual warehouse to be active; it simply reads the value from the table's metadata.

However, when a Row Access Policy (RAP) is applied to a table, the query's behavior changes fundamentally. A row access policy is a security feature that restricts which rows are visible to a user based on their role or other attributes. To enforce this policy, Snowflake can no longer rely on the high-level metadata of the entire table because it must first determine which specific rows the user is authorized to see. Consequently, the query engine must scan the individual micro-partitions and evaluate the policy logic for every row (or block of rows) to filter out unauthorized data before calculating the maximum value. This turns a "metadata-only" operation into a data-scanning operation, which requires a running warehouse and significantly more time.

Evaluating the Options:

Option A is incorrect because the prompt states the tables are "similarly-sized." Even if it were slightly larger, a metadata lookup for a standard table would still be nearly instant.

Option C is incorrect because a multi-cluster warehouse helps with concurrency (multiple users), not the raw execution speed of a single simple aggregate query.

Option D is incorrect because USE_CACHED_RESULT refers to the Query Result Cache. While turning it off would prevent a "0ms" response from a previous run, it wouldn't explain a "significant" slowdown compared to a standard metadata fetch.

Option B is the 100% correct answer. The presence of a Row Access Policy forces a full data scan and policy evaluation, which is the most common reason for performance degradation in otherwise simple metadata queries.

This scenario highlights the rules governing Shared Databases and Cross-Database Joins in Snowflake. When a user acquires data from the Snowflake Marketplace, it arrives in their account as a "Shared Database."

A critical restriction of shared databases is that they are read-only. Users (including Data Analysts and even AccountAdmins) cannot create new objects—such as tables, schemas, or views—directly inside a database created from a share. Therefore, Options A, C, and D are architecturally impossible because they all involve trying to write a new view into the ADDRESS_DATA database.

To combine the shared data with internal data (like the ORDERS table in GLOBAL_DWH), the Analyst must create the view in a database where they have CREATE VIEW privileges and that is writeable. Since GLOBAL_DWH is an internal database owned by the organization, it is the appropriate location to host the logic that joins local data with external enrichment data.

Evaluating the Options:

Option A and C are incorrect because you cannot create objects in a database created from a share.

Option D is incorrect because while IMPORTED_PRIVILEGES are necessary to view the data, they do not grant the ability to create objects within the share.

Option B is the 100% correct answer. By creating the view in the GLOBAL_DWH database (or any other local, non-shared database), the Analyst can successfully reference ADDRESS_DATA.OPENADDRESS and GLOBAL_DWH.PUBLIC.ORDERS in a single SQL statement, effectively creating an enriched data layer for the organization.

While Snowflake provides various ways to encapsulate logic, Stored Procedures are the primary tool for procedural programming and dynamic SQL execution. Unlike standard UDFs, which are generally designed to calculate and return values within a query, stored procedures can execute administrative commands (DDL) and data manipulation (DML) that are not possible in a simple SELECT statement.

Using Snowflake Scripting (or languages like Python, Java, or JavaScript), an analyst can write a procedure that builds a SQL string as a variable and then executes it using the EXECUTE IMMEDIATE command. This is demonstrated in procedural logic flows where inputs determine specific execution paths. While Tasks can be used to schedule the execution of these procedures, the task itself is a scheduling agent and does not provide the logic-driven dynamic string construction inherent to the procedure.

Snowsight, the modern web interface for Snowflake, introduces robust collaboration features through shared worksheets. Understanding the synchronization and versioning logic is critical for the Data Presentation and Data Visualization domain.

When a worksheet is shared with collaborators, Snowflake enables real-time collaboration (Option D). This means that as one user types or modifies SQL code, those changes are immediately visible to all other users who have the worksheet open. This functionality is similar to co-authoring in modern document editors, allowing data teams to debug or develop complex queries synchronously without the need for manual copy-pasting or constant screen sharing.

Furthermore, Snowflake manages the execution history of shared worksheets through a structured versioning system (Option C). If multiple users are interacting with the same worksheet, Snowflake does not overwrite a single "master" state during execution. Instead, every time a user executes the worksheet (or a portion of it), Snowflake creates a new version of the worksheet content associated with that specific run. This ensures that the code used to generate a particular result set is preserved and tied to that specific Query ID in the history.

Evaluating the Options:

Option A is incorrect because while users can see current edits, viewing a full, navigable history of "past versions" is not a standard feature in the same way it is in dedicated version control systems like Git.

Option B is incorrect as worksheet sharing is restricted to users within the same Snowflake account. Sharing across accounts requires the use of Data Shares or Private Listings.

Option E is incorrect because Snowflake preserves the history rather than overwriting it, allowing analysts to audit who ran what code and when.

Options C and D are the 100% correct answers. They define the synchronous and persistent nature of Snowsight collaboration, ensuring that teams can work together effectively while maintaining an immutable trail of executions.

Snowflake utilizes a unique architecture known as Zero-Copy Cloning, which allows users to create a replica of a table, schema, or entire database without physically duplicating the underlying data files (micro-partitions). When you execute the CLONE command, Snowflake simply creates new metadata that points to the existing micro-partitions of the source object.

Because the data is not physically copied, the clone operation is nearly instantaneous and, crucially, incurs no additional storage costs at the moment of creation. Storage costs only begin to accumulate for the clone when the data in the source or the clone diverges—for example, if rows are updated or deleted in the clone, Snowflake creates new micro-partitions to store the changed data while preserving the original state in the source.

Evaluating the Options:

Option A (LIKE) only copies the column definitions and structure of the table. It does not copy the data itself, so while it doesn't incur storage, it also doesn't provide the "data" requested by the prompt.

Option B (CTAS - Create Table As Select) performs a full deep copy of the data. This creates entirely new micro-partitions, which immediately increases the storage footprint and associated costs.

Option D is a filtered CTAS operation. While it may result in less data than the full table, it still involves creating new physical storage for the 2019 records, thus incurring additional costs.

Option C is the 100% correct answer. It uses the CLONE keyword, which is the specific Snowflake feature designed to provide a full dataset for dev/test environments with zero initial storage impact. This is a core competency in the Data Transformation and Data Modeling domain.

To determine the correct output, a Data Analyst must understand the behavior of the ILIKE operator, wildcard characters, and the ESCAPE clause in Snowflake.

1. Pattern Matching with ILIKE: The ILIKE operator performs case-insensitive pattern matching. The provided pattern is 'p%^_j%'.

p: The string must start with the letter 'P' or 'p'.

%: This wildcard matches any sequence of zero or more characters.

^_: The ESCAPE '^' clause identifies the caret symbol as an escape character. This means the underscore (_) immediately following it is treated as a literal character rather than its usual wildcard function (which matches any single character).

j: The letter 'J' or 'j' must follow the literal underscore.

%: Matches any trailing sequence of characters.

2. Evaluating the Data: Applying this logic to the values in the cert_dem table:

'Peter*John': While it starts with 'P' and contains 'J', it lacks the required literal underscore. No match.

'Peter John': Contains a space instead of an underscore. No match.

'Peter_John': Starts with 'P', contains a literal underscore, and is followed by 'J'. Match.

'Peter_john': Because the operator is ILIKE (case-insensitive), the lowercase 'j' is accepted. Match.

null: Comparisons with NULL using LIKE or ILIKE always return NULL (unknown), so it is excluded from the results.

3. Ordering the Results: The query includes ORDER BY 1, which sorts the results alphabetically based on the first column. Between 'Peter_John' and 'Peter_john', the standard sort order typically places uppercase before lowercase in most Snowflake collations.

Evaluating the Options (image_829269.png):

Option A incorrectly excludes the case-insensitive match.

Option B incorrectly identifies values that lack the literal underscore.

Option D incorrectly includes values that do not meet the escaped underscore requirement.

Option C is the 100% correct result set, displaying both versions of 'Peter_John' that contain the literal underscore.

Secure Views in Snowflake are specifically designed for data privacy and security, particularly when data is being shared across different business units or external accounts. The primary benefit of a secure view is that it prevents users from seeing the internal definitions (the underlying SQL) and protects against "trial-and-error" data discovery.

In a standard view, a savvy user might be able to deduce information about data they are not authorized to see by observing the query optimizer's behavior or by looking at the query profile. For example, by using specific filters or functions, a user might observe how long a query takes to execute or check the statistics to guess the distribution of values in hidden columns. Secure views prevent this by ensuring that Snowflake does not expose the internal metadata or the specific row/column counts that would allow a user to make observations about the quantity or nature of the underlying data that falls outside their access privileges.

Evaluating the Options:

Option B and C are incorrect because Snowflake does not merely obfuscate bytes or micro-partitions in a way that suggests a simple "hiding" of metrics; it fundamentally changes the query plan generation to ensure security.

Option D is a common misconception. In fact, secure views can sometimes be less performant than regular views. This is because the Snowflake optimizer is restricted from using certain optimizations (like predicate pushdown) that might inadvertently reveal data patterns to an unauthorized user.

Option A is the 100% correct answer. The "secure" nature of the view ensures that the user cannot use statistical side-channels or metadata observations to infer the existence or volume of data they are restricted from seeing.

Understanding how Snowflake aggregate functions like MIN() and MAX() handle numerical data and NULL values is fundamental for accurate Data Analysis. In this scenario, we have a table with five records distributed across two departments.

The MIN() function returns the smallest non-null value in the specified column across all rows in the group (or the entire table, if no GROUP BY is present). Looking at the salary column in the employees table, the values are: 10000, 9000, 8000, 15000, and NULL. The NULL value is ignored by the calculation. Among the remaining numerical values, 8000 is the smallest. Therefore, MIN_VAL will be 8000.

The MAX() function operates similarly, returning the largest non-null value from the set. Comparing the same list of numerical values—10000, 9000, 8000, and 15000—the largest value is clearly 15000. Consequently, MAX_VAL will be 15000.

Evaluating the Options based on the exhibit (image_8ca0df.png):

Option A incorrectly identifies the minimum and maximum based on the employee_id column (where 2000 is max and 900 is min), rather than the requested salary column.

Option B is incorrect as it seems to mix values from different columns or specific rows.

Option C is incorrect because it mistakenly suggests that MIN() returns NULL if a NULL value is present in the column. In Snowflake, standard aggregate functions (except for specialized ones like ARRAY_AGG or certain window functions with specific clauses) skip NULL values entirely.

Option D is the 100% correct output. It displays MIN_VAL as 8000 and MAX_VAL as 15000, which accurately reflects the mathematical minimum and maximum of the non-null entries in the salary column.

This behavior is consistent across all relational databases adhering to ANSI SQL standards, which Snowflake follows for these core aggregate operations.

In Snowsight, worksheets and dashboards are tightly integrated to facilitate a smooth transition from data exploration to formal reporting. When a Data Analyst has already done the "heavy lifting"—cleaning and preparing data within standalone worksheets—Snowflake provides a direct way to convert those worksheets into dashboard tiles.

The "least operational overhead" method is to Move the existing worksheets into the dashboard. When you are in the dashboard view, you can select the option to add a tile and then choose an existing worksheet from your folders. This "Move" or "Add from Worksheet" action preserves the entire SQL statement, any formatting applied to the results, and the visualization settings (charts) that were already configured in the worksheet.

Evaluating the Options:

Option B and C are technically possible but require significantly more effort (manual copy-pasting, switching windows, re-naming tiles). This increases the risk of human error and is not considered a high-efficiency workflow in Snowsight.

Option D is the most complex and overhead-intensive option, requiring the analyst to sift through system logs to recover code they already have access to in their UI.

Option A is the 100% correct answer. By moving or importing the worksheet directly into the dashboard as a tile, the analyst maintains the link between the development work and the final presentation with just a few clicks. Note that once a worksheet is moved into a dashboard, it essentially becomes a "tile" and is managed as part of the dashboard object.

In Snowsight, the modern web interface for Snowflake, System Filters are specialized keywords that provide out-of-the-box interactivity for dashboards and worksheets. These filters allow non-technical users to manipulate the timeframes of visualizations (e.g., switching from "Last 7 days" to "Last 12 months") without requiring an analyst to manually rewrite the underlying SQL code.

The most critical keyword for temporal filtering is :daterange. When a dashboard contains a date filter widget (as shown in the provided exhibit), the :daterange keyword acts as a dynamic placeholder for the range selected by the user. Unlike a standard variable that might represent a single date, :daterange is specifically designed to handle the start and end boundaries of a period. When injected into a WHERE clause, Snowflake automatically expands this keyword into the appropriate logic to filter records between those two points in time.

Evaluating the Options:

Options A and B are incorrect because they use hard-coded logic (-7 days). While these would return data for the last week, they are static. Changing the filter in the dashboard UI would have no effect on these queries, failing the requirement to "easily change the time period" via the filter selection.

Option C is incorrect because :date_filter is not a reserved system keyword in Snowsight. While an analyst could create a custom filter with that name, it would not automatically link to the standard system date widget shown in the image.

Option D is the 100% correct answer. Using WHERE = :daterange is the documented standard for linking a query to the Snowsight system date filter. This ensures that every tile in the dashboard remains synchronized with the user's global date selection, providing a cohesive and interactive reporting experience.