DAA-C01 SnowPro Advanced: Data Analyst Exam Questions and Answers

To automate a data pipeline in Snowflake using Tasks and Streams, an analyst must understand the relationship between scheduling and conditional execution. A task is an object used to execute a single SQL statement (or a call to a stored procedure) on a recurring basis. A stream, on the other hand, tracks Change Data Capture (CDC) metadata for a table, indicating whether new rows have been added, updated, or deleted.

The core requirement of this question is to ensure the task runs automatically but only if there is new data. In Snowflake, a task requires a SCHEDULE parameter to run automatically without manual intervention. The SCHEDULE defines the frequency (e.g., ' 5 MINUTE ' or a CRON expression) at which the task " attempts " to run. However, to prevent wasting credits by running the task when no data has changed, the WHEN clause is used in conjunction with the system function SYSTEM$STREAM_HAS_DATA( ' stream_name ' ).

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

Option A is incorrect because it lacks a SCHEDULE. Without a schedule, the task is considered " standalone " and will never execute unless manually triggered by an EXECUTE TASK command.

Option B is incorrect because it incorrectly attempts to place the SYSTEM$STREAM_HAS_DATA function within the SCHEDULE parameter. The schedule must be a time-based interval or a cron string.

Option C is incorrect because ' STREAM ' is not a valid schedule interval.

Option D is the 100% correct answer. It provides a valid time-based SCHEDULE ( ' 2 minute ' ) which enables the task to heart-beat automatically. It then correctly utilizes the WHEN clause with SYSTEM$STREAM_HAS_DATA( ' MYSTREAM ' ). This configuration ensures that every two minutes, Snowflake checks the stream; if the stream contains data, the task executes the SQL; if the stream is empty, the task skips the execution, saving compute resources. This pattern is the industry standard for building efficient, automated Data Transformation pipelines in the Snowflake Data Cloud.

One of the standout features of the Snowsight interface is its ability to perform automatic Data Profiling . When a Data Analyst executes a query, Snowflake doesn ' t just return a raw grid of data; it analyzes the result set to provide immediate visual insights.

When you click on a column header in the results pane, a summary statistics panel appears. For numeric, date, and time columns , Snowflake automatically generates a histogram (Option A). This histogram provides a visual representation of the data distribution, allowing the analyst to quickly identify patterns, concentrations of values, or significant outliers without writing additional SQL code.

Evaluating the Options:

Option B: While a histogram is a type of frequency distribution, Option A is more accurate because Snowsight also provides these visualizations for date and time types, not just integers/floats.

Option C: While MIN and MAX values are displayed in the summary panel, they are text-based statistics, not the " visualized " contextual statistic (the histogram) emphasized in the question.

Option D: " Key distribution " is not a standard visualization term used in the Snowsight profiling tool.

Option A: Is the 100% correct answer. It highlights the breadth of the profiling tool (covering numbers, dates, and times) and the specific visual element (the histogram) that makes exploratory data analysis significantly faster for a Data Analyst.

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.

Manually defining table structures for complex semi-structured files like Parquet can be error-prone and time-consuming. Snowflake provides a specific automation workflow to handle this, involving the detection of the file ' s internal schema and the dynamic creation of a matching table.

The process starts with the INFER_SCHEMA function. Because Parquet files are self-describing, they contain metadata about their columns and data types. INFER_SCHEMA reads this metadata from files in a stage and returns a list of column names and types. To turn this list into an actual table, the analyst uses the CREATE TABLE ... USING TEMPLATE syntax. This command takes the output of INFER_SCHEMA as an input and automatically builds a table with the corresponding definition.

Evaluating the Options:

Option A is incorrect because CREATE TABLE LIKE is used to copy the structure of an existing table , not to build a new one from file metadata.

Option C and D are incorrect because GENERATE_COLUMN_DESCRIPTION is a helper function used to create a formatted string of column definitions, but it is not the primary command used with USING TEMPLATE for automated table creation.

Option B is the Correct answer. The combination of INFER_SCHEMA (to find the columns) and USING TEMPLATE (to build the table) is the standard Snowflake pattern for schema-on-read automation in Data Ingestion workflows.

Descriptive analytics is the process of using historical data to understand " what happened. " This typically involves summarizing large datasets into interpretable chunks using central tendency, dispersion, and frequency measures.

AVG (Average) is a cornerstone of descriptive statistics. It provides the arithmetic mean of a numeric column, allowing an analyst to understand the " typical " value within a dataset (e.g., Average Order Value).

APPROX_COUNT_DISTINCT is a descriptive tool used to understand the volume of unique entities within a dataset (e.g., " How many unique customers visited the site? " ). Similar to HLL mentioned earlier, this function provides a fast summary of data volume and variety, which is a primary goal of the descriptive phase of analysis.

Evaluating the Options:

Options A and B (REGR_INTERCEPT and REGR_SLOPE) are used for linear regression. These fall under Predictive Analytics , as they are used to model relationships and predict future outcomes, rather than just describing current data.

Option C (ROW_NUMBER) is a window function used for data ranking and ordering, but it does not provide a descriptive summary of the dataset ' s characteristics.

Options D and E are correct because they provide summary statistics (mean and cardinality) that define the " state " of the data, which is the definition of descriptive analytics.

Snowflake provides a powerful feature called Result Caching . When a query is executed, the results are persisted for 24 hours . To access the result set of a previously executed query without re-running the heavy computation (and thus saving credits), a Data Analyst can use the RESULT_SCAN table function.

The function RESULT_SCAN requires a Query ID as its input. The most common way to programmatically retrieve the ID of the most recently executed statement in the current session is by using the LAST_QUERY_ID() function. By wrapping this in the TABLE() operator, Snowflake treats the cached results as a virtual table that can be queried, filtered, or even joined with other tables.

Evaluating the Options:

Option B is incorrect because QUERY_HISTORY returns metadata about queries (like start time, user, and status) but does not return the actual data records produced by those queries.

Option C is incorrect as DESC (Describe) is used to see the columns and data types of an object, not to fetch its row data.

Option D is incorrect; while LAST_QUERY_ID(-1) might successfully retrieve a string representing a previous ID in some contexts, it does not actually " access the results " or display the data.

Option A is the 100% correct syntax. It is the standard method used by Data Analysts to perform post-processing on a large result set or to recover a result set if the worksheet was accidentally cleared. This is a key part of the Data Analysis domain, specifically regarding performance optimization and the utilization of Snowflake ' s unique caching layers.

The INFER_SCHEMA function is a powerful tool in the Snowflake ingestion toolkit, specifically designed to bridge the gap between semi-structured data and relational tables . In modern data engineering, files like Parquet, Avro, and ORC are " self-describing, " meaning they contain their own schema definitions (field names and data types) within the file header.

Before INFER_SCHEMA existed, a Data Analyst had to manually inspect a Parquet file, identify the keys, determine their data types, and then manually write a CREATE TABLE statement with the matching columns. INFER_SCHEMA automates this by scanning a set of files in a stage and returning a result set that describes the detected schema. This allows for the dynamic creation of tables that perfectly match the incoming data files.

Evaluating the Options:

Option A is incorrect because " unstructured data " (like PDFs or Images) does not have a schema that can be " inferred " in a relational sense.

Option C is incorrect because INFER_SCHEMA operates on files in a Stage , not on data already sitting in a table cell. To parse JSON in a table, you would use functions like FLATTEN or simple path notation.

Option D is incorrect because the DESCRIBE TABLE or GET_DDL commands are used to retrieve definitions for existing tables.

Option B is the Correct answer. It specifically identifies the role of the function: detecting metadata (schema) from staged semi-structured files. This function is often used in conjunction with GENERATE_COLUMN_DESCRIPTION and CREATE TABLE ... USING TEMPLATE to fully automate the schema evolution and ingestion process.

In Snowflake, table relationships established via constraints are managed at the metadata level . When a table that is referenced by a foreign key is renamed, Snowflake automatically maintains the integrity of that relationship by updating the internal metadata pointers.

Unlike some legacy database systems where a rename might break hardcoded references, Snowflake ' s architecture ensures that the Foreign Key in the son table remains valid. After executing ALTER TABLE mother RENAME TO father;, the constraint son_fk will be automatically updated by the system to reference the new table name, father. If you were to run a SHOW TRANSACTIONS or GET_DDL command on the son table after the rename, you would see that the REFERENCES clause now points to father(id).

Evaluating the Options:

Options A and B are incorrect because Snowflake is designed to prevent " orphaned " constraints during standard DDL operations like renaming. The relationship is preserved.

Option C is incorrect because Snowflake ' s query optimizer can still utilize these constraints for query rewrite and join elimination optimizations, provided the constraints are defined, regardless of the table ' s current name.

Option D is the 100% correct answer. This automatic update behavior is a core feature of Snowflake ' s metadata-driven approach to Data Transformation and Data Modeling , allowing for flexible schema evolution without breaking existing relational structures.

In Data Analysis , correlation refers to a statistical relationship between two variables. When analyzing a time-series chart like the one provided, a Data Analyst looks for patterns in how the two categories— " Enterprise " (blue line) and " Pro Edition " (yellow line)—move in relation to one another over the X-axis (Year).

A Positive Correlation would be indicated if both lines generally moved in the same direction at the same time (e.g., when Enterprise sales increase, Pro Edition sales also increase). A Negative Correlation (or inverse correlation) would be shown if the lines moved in opposite directions consistently (e.g., when one peaks, the other troughs).

Looking closely at the provided exhibit, the fluctuations for both editions are highly erratic and appear independent of each other. For instance, around the year 2008, the Pro Edition (yellow) shows a significant peak while the Enterprise edition (blue) experiences a sharp decline. Conversely, in other sections of the chart, they both dip or rise simultaneously by chance, but there is no sustained, predictable pattern of movement. The peaks and valleys do not align in a way that suggests one variable ' s movement is tied to the other.

Statistically, this lack of a discernible relationship indicates a Correlation Coefficient near zero. In the context of the Snowflake Snowpro Advanced: Data Analyst exam, identifying " No Correlation " is a key skill for interpreting Snowsight visualizations. It tells the analyst that the factors driving sales for the Enterprise tier are likely distinct from those driving the Pro Edition, and they should be analyzed as independent segments rather than interdependent variables. Therefore, based on the visual evidence of random, non-synchronous movement across the timeline, the only supported conclusion is that there is no correlation.

When visualizing " active routes " or the relationship between two categorical dimensions (like a Source and a Destination ), the goal is to identify patterns of intensity. A Heat grid (also known as a Heatmap) is the ideal visualization for this scenario.

In a heat grid, one dimension (e.g., Fulfillment Warehouse) is placed on the X-axis, and the other dimension (Destination Warehouse) is placed on the Y-axis. The individual cells at the intersection of these two dimensions are colored based on a metric, such as the total volume of transfers. High-intensity colors (e.g., dark red) immediately draw the analyst ' s eye to the " most active " routes, while lighter colors represent less frequent transfers.

Evaluating the Options:

Option A (Scatterplot) is used for identifying clusters or correlations between two numeric variables, not categorical locations.

Option B (Scorecard) is a high-level KPI visualization that usually shows a single number (e.g., Total Transfers) and does not provide the multi-dimensional breakdown required to see specific routes.

Option C (Line chart) is designed for time-series data to show trends over time, which would not effectively illustrate the static relationship between two geographic points.

Option D is the 100% correct answer. It provides the best spatial representation of density and activity between discrete origins and destinations, which is a core skill in the Data Presentation and Data Visualization domain.

In Snowflake, a Stored Procedure is the primary object used for procedural logic and the encapsulation of multiple SQL statements. Unlike standard functions that are typically restricted to returning a single value or a table based on an input, Stored Procedures are designed to perform administrative tasks, wrap complex business logic, and execute a sequence of operations—such as DDL (Data Definition Language) and DML (Data Manipulation Language) commands—within a single callable object.

Modular code development is facilitated by Stored Procedures because they allow analysts and developers to write logic once and reuse it across various workflows. For example, a procedure can be written to truncate a staging table, call a COPY INTO command, and then execute several INSERT statements into a final fact table. This " wrapper " approach ensures consistency, as any change to the logic only needs to be updated in the procedure definition rather than in every script or task that uses it.

Evaluating the Options:

Option B is incorrect because a Materialized View is a pre-computed result set used to improve query performance; it does not " encapsulate a series of statements " for procedural execution.

Option C is incorrect because while User-Defined Functions (UDFs) provide reusability, they are generally intended for calculations or data transformations that return a result. They are much more restricted than procedures; for instance, a UDF cannot execute DDL commands like CREATE TABLE.

Option D is incorrect because a Common Table Expression (CTE) is a temporary named result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. It is not a persistent database object and cannot be reused across different sessions or scripts.

Option A is the correct answer. Snowflake Stored Procedures can be written in multiple languages (JavaScript, Snowflake Scripting/SQL, Python, Java, or Scala), providing the flexibility needed for sophisticated, modular automation in a data pipeline.

To correctly predict the output of this query, a Data Analyst must understand how Snowflake handles semi-structured data (JSON), specifically regarding path notation, data type casting, and the display of VARIANT values in the Snowsight UI.

1. Variant vs. Casted Types (Display): When you access a value within a JSON object using colon notation (e.g., json_col:employee.phone[0]), the resulting value is of the VARIANT data type. In the Snowflake Snowsight results pane, VARIANT strings are displayed with double quotes (e.g., " +1 223-256-7330 " ). However, when you explicitly cast a variant to a string type using the double-colon syntax (e.g., ::varchar), Snowflake extracts the value as a native SQL string, which is displayed without quotes (e.g., +1 028-735-4521).

2. Handling Empty Strings vs. Out-of-Bounds: In the provided JSON exhibit, the phone array contains four elements at indices 0, 1, 2, and 3. The elements at indices 2 and 3 are empty strings ( " " ). When these are queried, Snowflake returns the empty string variant, which appears as " " in the result set. The query also attempts to access phone[4]. Since the array only has four elements (max index 3), index 4 is out-of-bounds . In Snowflake, accessing a non-existent index in an array or a non-existent key in an object returns NULL .

Evaluating the Options based on the exhibit:

Option B correctly reflects all these behaviors: WORK_PHONE is in quotes (Variant), OFFICE_PHONE1 is not in quotes (Casted), OFFICE_PHONE2 and OFFICE_PHONE3 show the empty strings present in the JSON, and OFFICE_PHONE4 correctly shows null for the out-of-bounds access.

Option A is incorrect because it misses the quotes for the variant column and shows the out-of-bounds index as an empty string instead of null.

Option C is incorrect because it fails to account for the empty strings present at indices 2 and 3.

Option D incorrectly mixes the casting logic and display.

This question tests the " Schema-on-Read " proficiency required of a SnowPro Advanced: Data Analyst, specifically the ability to predict exactly how transformed semi-structured data will materialize for end-users.

One of the most powerful features of the Snowsight interface for a Data Analyst is the automatic data profiling provided in the results pane. Snowflake automatically calculates statistics and visual distributions for the result set of any query executed in a worksheet, provided the result set is not excessively large.

When the Analyst views the query results, they can simply click on the column header for the " total amount " column. This action opens a summary pane that displays key descriptive statistics such as the mean, sum, and a histogram showing the frequency distribution of the values in that specific column. This allows for immediate visual analysis of data skew, outliers, or common ranges without requiring the analyst to write additional SQL or move the data to an external visualization tool.

Evaluating the Options:

Option A is the Correct answer. This is the fastest, built-in way to perform " exploratory data analysis " (EDA) on a result set within the UI.

Option B and C are incorrect because while Snowsight does have a " Chart " tab, creating a chart requires manual configuration and is a separate step from the automatic profiling features found in the column headers.

Option D is incorrect because calling the WIDTH_BUCKET function would require the Analyst to run the query again with modified SQL logic, which explicitly contradicts the requirements of the question.

This feature significantly enhances the Data Analysis workflow by providing " at-a-glance " insights into data quality and distribution directly within the development environment.

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.

In Data Transformation and Data Modeling , selecting the correct join type determines how the query handles unmatched records from the participating tables. The requirement here is to return all records from both the projects table and the employee table.

A FULL OUTER JOIN (often shortened to FULL JOIN) is designed specifically for this purpose. It combines the results of both a LEFT OUTER JOIN and a RIGHT OUTER JOIN. It returns:

Rows where there is a match between the project and the employee.

Rows from the projects table that have no matching employees (padded with NULL in the employee columns).

Rows from the employees table that are not assigned to any projects (padded with NULL in the project columns).

Evaluating the Options:

Option A (INNER JOIN) only returns rows where there is a match in both tables. Any projects without employees or employees without projects would be excluded.

Option C (LEFT OUTER JOIN) would return all projects, but would exclude employees who are not assigned to a project.

Option D (CROSS JOIN) creates a Cartesian product, matching every single employee with every single project regardless of actual relationships. This would create a massive, redundant dataset and is not what is requested.

Option B is the 100% correct answer. It ensures total data visibility from both entities, which is often required in data quality audits or comprehensive resource allocation reporting where the analyst needs to see " unlinked " data on both sides of the relationship.

In Snowsight , managing dashboard layouts requires an understanding of how tiles (queries or visualizations) are stored versus how they are displayed. When an analyst wants to remove a tile from the visible dashboard grid without destroying the underlying query logic or historical configuration, the Unplace action is the correct functional choice.

When a tile is unplaced , it is removed from the dashboard ' s active layout but remains part of the dashboard ' s " library " of available content. This is a critical distinction from the Delete action (Option C), which permanently removes the tile and its associated SQL code from the dashboard object. Unplacing allows the analyst to " archive " the work temporarily. Because the tile still technically exists within the dashboard ' s metadata, any new data added to the underlying tables will still be processed by the query whenever the tile is eventually placed back onto the grid.

Evaluating the Options:

Option A (Show/Hide) is not a standard standalone command for dashboard tile management in Snowsight; visibility is typically managed through placement on the grid.

Option B (Duplicate) creates a second copy of the tile. While this preserves the data, it does not satisfy the requirement to " hide " the current tile; it actually adds more clutter to the dashboard.

Option C (Delete) is incorrect because the prompt specifies that the data and tile will need to be available in the future. Deleting would require the analyst to rewrite the SQL and reconfigure the visualization from scratch.

Option D is the 100% correct answer. Unplacing is the " soft-remove " feature of Snowsight. It preserves the tile in the " Unplaced Tiles " sidebar, allowing for quick restoration at a later date. This feature is essential for analysts who need to manage evolving reporting requirements where certain metrics may only be relevant seasonally or during specific business cycles.