CertSafari

    Free Snowflake SnowPro Advanced: Data Analyst (DAA-C01) Sample Questions

    35 free sample questions from our bank of 569+, covering every exam domain, with answers and detailed explanations. Updated August 2026.

    Practice all 569 questions in quiz mode

    Domain 1: Data Ingestion and Data Preparation

    1.4 Outline and use best practice considerations relating to data integrity structures.

    1.A company ingests customer data from multiple sources into a staging table. They want to create a final `DIM_CUSTOMER` table where `CUSTOMER_NK` (natural key) is unique. Since Snowflake doesn't enforce uniqueness, which combination of Snowflake features should be used to identify and handle duplicate `CUSTOMER_NK` values during the ELT process before inserting into the final table?(Select 2)

    1. A.Define a UNIQUE constraint on `CUSTOMER_NK` in the `DIM_CUSTOMER` table to automatically reject duplicates.
    2. B.Use a `MERGE` statement with a `WHEN NOT MATCHED` clause to insert new customers.
    3. C.Use the `QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NK ORDER BY load_timestamp DESC) = 1` clause in a SELECT statement from the staging table.
    4. D.Use the `GROUP BY CUSTOMER_NK HAVING COUNT(*) > 1` clause to identify duplicates before the main load.
    5. E.Rely on the `COPY INTO` command's `VALIDATION_MODE` to report uniqueness violations.
    Show answer & explanation

    Correct answers: B, C — Use a `MERGE` statement with a `WHEN NOT MATCHED` clause to insert new customers.; Use the `QUALIFY ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NK ORDER BY load_timestamp DESC) = 1` clause in a SELECT statement from the staging table.

    • A. Incorrect. While Snowflake allows the definition of UNIQUE constraints, it does not enforce them (with the exception of NOT NULL). These constraints serve primarily as metadata for query optimization and data modeling tools and will not prevent duplicate rows from being inserted.
    • B. Correct. A `MERGE` statement is ideal for synchronizing two tables. Its `WHEN NOT MATCHED` clause allows for the insertion of rows from a source that do not exist in the target based on a specified key. This effectively prevents the creation of duplicate records that are already present in the final dimension table, making it a key part of an idempotent loading process.
    • C. Correct. This is a highly efficient and common Snowflake pattern to de-duplicate data. The `ROW_NUMBER()` window function assigns a rank to each row within a partition of the `CUSTOMER_NK`. By using the `QUALIFY` clause to filter for rows where the rank is 1, you can select a single, consistent record (e.g., the most recent one) for each natural key, thereby handling duplicates within the staging table itself before the final load.
    • D. Incorrect. This clause is effective for *identifying* which natural keys have duplicate entries in the staging table. However, it does not *handle* them, as it only returns the duplicated keys themselves, not a clean set of unique records to be inserted. Additional logic would be required to select the desired record from among the duplicates.
    • E. Incorrect. The `COPY INTO` command's `VALIDATION_MODE` is used to validate data files for parsing errors, data type mismatches, or structural issues before loading. It does not check for uniqueness against data already existing in the target table.

    1.4 Outline and use best practice considerations relating to data integrity structures.

    2.A data warehouse uses surrogate keys for all dimension tables, generated using a SEQUENCE. The DIM_CUSTOMER table has a primary key CUSTOMER_SK which also has a NOT NULL constraint. During a data loading process, a bug causes NULL values to be inserted for CUSTOMER_SK into the staging table. The subsequent INSERT INTO DIM_CUSTOMER SELECT * FROM STG_CUSTOMER fails. Why did this operation fail?

    1. A.The informational PRIMARY KEY constraint blocked the insert.
    2. B.The SEQUENCE object detected the invalid NULL and raised an error.
    3. C.The enforced NOT NULL constraint on the `CUSTOMER_SK` column blocked the insert.
    4. D.The table's clustering key, which was defined on `CUSTOMER_SK`, rejected the NULL values.
    Show answer & explanation

    Correct answer: C — The enforced NOT NULL constraint on the `CUSTOMER_SK` column blocked the insert.

    • A. Incorrect. In Snowflake, PRIMARY KEY constraints are informational and not enforced by default, meaning they do not block duplicate values. While defining a PRIMARY KEY automatically creates an enforced NOT NULL constraint on the column, it is the NOT NULL constraint itself that causes the failure, not the informational PRIMARY KEY constraint.
    • B. Incorrect. A SEQUENCE object is used to generate a series of unique numbers. It is not involved in the validation of data being inserted from another table. The error is raised by the constraints defined on the target table, not by the sequence generator.
    • C. Correct. Unlike most other constraints, Snowflake strictly enforces NOT NULL constraints. The attempt to insert a NULL value from the staging table into the CUSTOMER_SK column, which is defined as NOT NULL, violates this integrity rule and causes the INSERT statement to fail.
    • D. Incorrect. A clustering key is a performance optimization feature in Snowflake used to co-locate data within micro-partitions to improve query performance. It does not enforce data integrity rules and has no role in rejecting or validating NULL values during an insert operation.

    1.5 Implement data processing solutions.

    3.A data engineer needs to build a report that ranks products by sales within each product category. The report should only show the top 3 products for each category. Which combination of a window function and a clause should be used to achieve this?

    1. A.`COUNT(*) OVER (PARTITION BY category)` in the `SELECT` and `WHERE rank <= 3`
    2. B.`RANK() OVER (PARTITION BY category ORDER BY sales DESC)` in the `SELECT` and `QUALIFY rank <= 3`
    3. C.`ROW_NUMBER() OVER (ORDER BY sales DESC)` in the `SELECT` and `HAVING row_num <= 3`
    4. D.`RANK() OVER (PARTITION BY category ORDER BY sales DESC)` in the `SELECT` and `LIMIT 3`
    Show answer & explanation

    Correct answer: B — `RANK() OVER (PARTITION BY category ORDER BY sales DESC)` in the `SELECT` and `QUALIFY rank <= 3`

    • A. This option is incorrect for two reasons. First, `COUNT(*)` counts the number of rows within each partition (category), it does not rank them based on sales. Second, the `WHERE` clause is evaluated before window functions are computed, so it cannot be used to filter on an alias of a window function result.
    • B. This is the correct and most efficient solution in Snowflake. The `RANK() OVER (PARTITION BY category ORDER BY sales DESC)` window function correctly assigns a rank to each product based on its sales, restarting the ranking for each new category. The `QUALIFY` clause is a Snowflake-specific feature designed to filter the results of window functions after they are calculated, making it perfect for selecting the top N rows per partition.
    • C. This option is incorrect because the `ROW_NUMBER()` function is missing the `PARTITION BY category` clause, which would cause it to rank all products across the entire dataset instead of within each category. Additionally, the `HAVING` clause is used to filter aggregated results from a `GROUP BY` clause, not for filtering individual rows based on a window function's output.
    • D. This option is incorrect. While the `RANK()` function is defined correctly, the `LIMIT 3` clause is applied to the final result set of the entire query. This would return only the first three rows from the overall result, not the top three rows for each individual product category.

    1.3 Enrich data by identifying and accessing relevant data from the Snowflake Marketplace.

    4.Which of the following are valid types of data listings available in the Snowflake Marketplace?(Select 3)

    1. A.Free
    2. B.Personalized
    3. C.Open-Source
    4. D.Trial
    5. E.Paid
    Show answer & explanation

    Correct answers: A, B, E — Free; Personalized; Paid

    • A. Correct. The Snowflake Marketplace offers free listings that allow consumers to access datasets without any cost. This is one of the primary public listing categories.
    • B. Correct. 'Personalized' listings refer to private listings. A data provider can create a listing that is not publicly visible and offer it directly to specific consumer accounts. This allows for custom data offerings, terms, or pricing for specific clients.
    • C. Incorrect. 'Open-Source' describes the origin or license of the data itself, but it is not a formal listing type or category within the Snowflake Marketplace. A listing that contains open-source data would be categorized as either 'Free' or 'Paid'.
    • D. Incorrect. A 'Trial' is a feature of a 'Paid' listing, not a distinct listing type. Data providers can offer a limited-time trial to allow potential consumers to evaluate a paid dataset before committing to a purchase.
    • E. Correct. 'Paid' listings require a payment, typically on a subscription or usage basis, for ongoing access to the data. This is the other primary public listing category alongside 'Free'.

    1.3 Enrich data by identifying and accessing relevant data from the Snowflake Marketplace.

    5.After a consumer creates a database from a share, which of the following actions are they permitted to perform on a table within that database?(Select 2)

    1. A.`UPDATE` specific rows in the table.
    2. B.`CREATE VIEW` in their own database that selects from the shared table.
    3. C.`SELECT` data from the table.
    4. D.`ALTER TABLE` to add a new column.
    5. E.`TRUNCATE TABLE` to clear its contents.
    Show answer & explanation

    Correct answers: B, C — `CREATE VIEW` in their own database that selects from the shared table.; `SELECT` data from the table.

    • A. Incorrect. A database created from a share is read-only for the consumer. Data Manipulation Language (DML) operations like `UPDATE` that modify the data in the shared table are not permitted.
    • B. Correct. While consumers cannot create objects within the shared database itself, they are permitted to create objects, such as views, in their own separate databases that reference and query the data from the shared tables. This is a primary method for integrating shared data into their own analytics workflows.
    • C. Correct. The fundamental purpose of a share is to grant read-access to data. Consumers are always permitted to perform `SELECT` operations to query and read data from the tables within a database created from a share.
    • D. Incorrect. A shared database is read-only for the consumer in terms of both its data and its schema. Data Definition Language (DDL) operations like `ALTER TABLE` that would modify the structure of a shared object are forbidden.
    • E. Incorrect. `TRUNCATE TABLE` is a data modification operation that removes all rows from a table. As consumers have read-only access to shared databases, they are not allowed to perform operations that delete or alter the data.

    1.7 Given a scenario, use Snowflake functions.

    6.A marketing team at a large e-commerce company needs a daily dashboard showing the number of unique visitors to their website. The underlying table, `WEB_LOGS`, contains billions of rows per day. The dashboard query is slow and consuming significant credits because of a `COUNT(DISTINCT VISITOR_ID)` operation. An acceptable margin of error for the unique count is around 2%. Which function should be used to optimize the query?

    1. A.APPROX_COUNT_DISTINCT(VISITOR_ID)
    2. B.COUNT(VISITOR_ID) IGNORE NULLS
    3. C.BITMAP_COUNT(BITMAP_CONSTRUCT(TO_ARRAY(VISITOR_ID)))
    4. D.HASH_AGG(VISITOR_ID)
    Show answer & explanation

    Correct answer: A — APPROX_COUNT_DISTINCT(VISITOR_ID)

    • A. The `APPROX_COUNT_DISTINCT` function is specifically designed for high-performance cardinality estimation on very large datasets. It provides an approximate count of unique values much faster and with significantly lower credit consumption than an exact `COUNT(DISTINCT)`. Its standard error is approximately 1.625%, which falls well within the acceptable 2% margin specified in the scenario.
    • B. The `COUNT` function calculates the total number of non-null rows for the specified column. It does not calculate the number of unique or distinct values, and therefore fails to meet the fundamental business requirement of counting unique visitors.
    • C. While bitmap functions like `BITMAP_COUNT` and `BITMAP_AGG` can be used for calculating exact distinct counts, they are not the primary tool for this approximate counting scenario. `APPROX_COUNT_DISTINCT` is the more direct, optimized, and appropriate function for cardinality estimation with an acceptable error margin.
    • D. `HASH_AGG` is a valid aggregate function in Snowflake, but its purpose is to return a single signed 64-bit hash value for a group of input rows. It is typically used for tasks like detecting changes in data, not for counting distinct values.

    1.7 Given a scenario, use Snowflake functions.

    7.A developer is loading JSON data into a VARIANT column named `PAYLOAD`. An example payload is: `{"products": [{"id": "A1", "price": 10}, {"id": "B2", "price": 20}]}`. They need to write a query to extract the `id` of the first product in the `products` array. Which syntax is correct?

    1. A.PAYLOAD:products.0.id
    2. B.PAYLOAD:products[0].id
    3. C.GET(PAYLOAD, 'products[0].id')
    4. D.JSON_EXTRACT_PATH_TEXT(PAYLOAD, 'products', '0', 'id')
    Show answer & explanation

    Correct answer: B — PAYLOAD:products[0].id

    • A. Incorrect. This syntax is invalid for querying semi-structured data in Snowflake. While dot notation is used to access object keys, it is not used for array indexing. Snowflake requires bracket notation (e.g., `[0]`) to access elements within an array.
    • B. Correct. This is the standard and correct syntax in Snowflake for querying data within a VARIANT column. The colon (`:`) is used to access the top-level key ('products') from the VARIANT. The bracket notation (`[0]`) is used to access the first element of the JSON array. Finally, the dot notation (`.id`) is used to access the 'id' key within that object.
    • C. Incorrect. While the `GET` function can be used to traverse semi-structured data, the provided syntax is invalid. The `GET` function does not accept a single string path with brackets and dots. A nested series of `GET` or `GET_PATH` calls would be required, making it more complex than the native path notation.
    • D. Incorrect. The `JSON_EXTRACT_PATH_TEXT` function is specifically designed to operate on explicit JSON text strings (VARCHAR), not on columns of the native `VARIANT` data type. For querying `VARIANT` columns, the colon/bracket/dot path notation is the direct and preferred method.

    1.6 Given a scenario, prepare data and load into Snowflake.

    8.When is it most appropriate to use a temporary internal stage?

    1. A.For long-term storage of staging files that are loaded on a recurring basis.
    2. B.When multiple users or processes need to share the same set of staging files.
    3. C.For a single session's transient staging files that are not needed after the session ends.
    4. D.When loading data that requires encryption with a customer-managed key.
    Show answer & explanation

    Correct answer: C — For a single session's transient staging files that are not needed after the session ends.

    • A. This is incorrect. Temporary stages are, by definition, not suitable for long-term storage. They are designed for transient data and are automatically dropped at the end of the session. A permanent internal stage should be used for recurring loads or long-term storage.
    • B. This is incorrect. Temporary internal stages are session-specific and are not visible or accessible to other users or processes. For sharing files, a named permanent internal stage should be created and granted appropriate privileges.
    • C. This is the correct use case. A temporary internal stage exists only for the duration of the user's session in which it was created. It is ideal for staging files that are used for a single, immediate data loading operation and do not need to persist after the session concludes, as Snowflake automatically purges the stage and its files.
    • D. This is incorrect. The requirement for encryption with a customer-managed key (Tri-Secret Secure) is a feature associated with permanent stages, not temporary ones. The choice between temporary and permanent stages is based on the data's persistence and sharing requirements, not the encryption method.

    1.6 Given a scenario, prepare data and load into Snowflake.

    9.Which three `COPY INTO <table>` parameters or options can be used to control which specific files from a stage are loaded in a single execution?(Select 3)

    1. A.The `FILES` option, to specify an explicit list of filenames.
    2. B.The `PATTERN` option, to use a regular expression to match filenames.
    3. C.The `ORDER BY` clause in a `SELECT` transformation on the staged files.
    4. D.A specific file path included in the `FROM` clause (e.g., `FROM @my_stage/path/to/files/`).
    5. E.The `START_TRANSACTION` copy option.
    Show answer & explanation

    Correct answers: A, B, D — The `FILES` option, to specify an explicit list of filenames.; The `PATTERN` option, to use a regular expression to match filenames.; A specific file path included in the `FROM` clause (e.g., `FROM @my_stage/path/to/files/`).

    • A. Correct. The `FILES` copy option allows you to provide an explicit, comma-separated list of file names. This gives you precise, granular control to load specific files from a stage in a single command.
    • B. Correct. The `PATTERN` copy option uses a regular expression to filter and load only those files from the stage whose names match the specified pattern. This is an effective way to load files based on a common naming convention.
    • C. Incorrect. The `ORDER BY` clause, when used within a `SELECT` transformation during a `COPY` operation, sorts the data rows being loaded from the files. It does not filter or select which files are chosen from the stage for loading.
    • D. Correct. By specifying a path within the stage location in the `FROM` clause (e.g., `FROM @my_stage/path/`), you restrict the `COPY` command to only consider files within that specific path or prefix. This is a fundamental way to filter the set of files to be loaded.
    • E. Incorrect. `START_TRANSACTION` is a SQL command used to explicitly begin a transaction. It is not a valid copy option or parameter for the `COPY INTO <table>` command and has no function related to selecting files from a stage.

    1.2 Perform data discovery to identify what is needed from the available datasets.

    10.An analyst is investigating a `WEB_EVENTS` table and suspects that the `EVENT_TIMESTAMP_UTC` column, stored as a `VARCHAR`, is not always in a valid ISO 8601 format, causing downstream casting errors. The analyst needs to identify the problematic rows and understand the different invalid formats present. Which two queries would be most effective for this discovery?(Select 2)

    1. A.SELECT EVENT_TIMESTAMP_UTC, COUNT(*) FROM WEB_EVENTS WHERE TRY_TO_TIMESTAMP(EVENT_TIMESTAMP_UTC) IS NULL GROUP BY 1;
    2. B.SELECT * FROM WEB_EVENTS WHERE EVENT_TIMESTAMP_UTC IS NULL;
    3. C.SELECT DISTINCT EVENT_TIMESTAMP_UTC FROM WEB_EVENTS ORDER BY 1;
    4. D.SELECT * FROM WEB_EVENTS WHERE NOT IS_TIMESTAMP_NTZ(EVENT_TIMESTAMP_UTC);
    5. E.SELECT * FROM WEB_EVENTS QUALIFY TRY_CAST(EVENT_TIMESTAMP_UTC AS TIMESTAMP_NTZ) IS NULL;
    Show answer & explanation

    Correct answers: A, E — SELECT EVENT_TIMESTAMP_UTC, COUNT(*) FROM WEB_EVENTS WHERE TRY_TO_TIMESTAMP(EVENT_TIMESTAMP_UTC) IS NULL GROUP BY 1;; SELECT * FROM WEB_EVENTS QUALIFY TRY_CAST(EVENT_TIMESTAMP_UTC AS TIMESTAMP_NTZ) IS NULL;

    • A. Correct. This query effectively addresses the analyst's need to 'understand the different invalid formats'. The `TRY_TO_TIMESTAMP` function attempts to parse the string, returning `NULL` upon failure. The `WHERE` clause isolates these failures, and the `GROUP BY` with `COUNT(*)` aggregates the distinct invalid strings, providing a summarized view of the problematic formats and their frequencies.
    • B. Incorrect. This query only identifies rows where the `EVENT_TIMESTAMP_UTC` column itself is `NULL`. It does not help find non-NULL string values that are in an invalid timestamp format, which is the core of the problem.
    • C. Incorrect. While this query shows all unique values in the column, it doesn't programmatically separate valid formats from invalid ones. For a table with many distinct values, it would require manual inspection and would be an inefficient method for discovery.
    • D. Incorrect. This query will fail because the function `IS_TIMESTAMP_NTZ` does not exist in Snowflake. Even if the valid function `IS_TIMESTAMP` were used, it is designed to check the data type of a variant, not to validate if a string can be cast to a timestamp.
    • E. Correct. This query effectively addresses the analyst's need to 'identify the problematic rows'. The `TRY_CAST` function attempts to convert the string to a timestamp and returns `NULL` on failure. Filtering where the result `IS NULL` successfully identifies the rows with invalid data. Note: While the query's logic is sound, the syntax is flawed; `QUALIFY` requires a window function and would raise an error. The standard `WHERE` clause should be used instead. However, among the given choices, the intended logic of using `TRY_CAST` makes it one of the two best answers.

    1.2 Perform data discovery to identify what is needed from the available datasets.

    11.Which two `INFORMATION_SCHEMA` views are most useful for discovering table-level relationships and constraints within a database?(Select 2)

    1. A.INFORMATION_SCHEMA.COLUMNS
    2. B.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    3. C.INFORMATION_SCHEMA.TABLES
    4. D.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
    5. E.INFORMATION_SCHEMA.LOAD_HISTORY
    Show answer & explanation

    Correct answers: B, D — INFORMATION_SCHEMA.TABLE_CONSTRAINTS; INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

    • A. Incorrect. The `INFORMATION_SCHEMA.COLUMNS` view provides detailed information about individual columns within tables, such as data type and nullability, but it does not directly describe table-level constraints or relationships between tables.
    • B. Correct. The `INFORMATION_SCHEMA.TABLE_CONSTRAINTS` view is designed to list all constraints (e.g., PRIMARY KEY, UNIQUE, FOREIGN KEY) defined on tables. This makes it a primary tool for discovering the constraints applied at the table level.
    • C. Incorrect. The `INFORMATION_SCHEMA.TABLES` view provides high-level metadata about the tables themselves, such as table name, owner, and creation timestamp, but it does not contain information about the constraints on those tables or the relationships between them.
    • D. Correct. The `INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS` view specifically describes foreign key relationships (referential integrity) between tables. It is the most direct way to discover how tables are linked to one another.
    • E. Incorrect. The `INFORMATION_SCHEMA.LOAD_HISTORY` view is specific to tracking the history of data loaded into tables using the `COPY INTO` command. It is used for monitoring data ingestion, not for discovering schema definitions like relationships or constraints.

    1.1 Use a collection system to retrieve data.

    12.A large e-commerce company needs to update its product recommendation engine. The machine learning model is retrained every 4 hours using customer clickstream data that is streamed into an Apache Kafka topic. The primary goal is to ensure data is available for this 4-hour retraining cycle in the most cost-effective manner, minimizing unnecessary real-time ingestion costs while meeting the deadline. Which data collection strategy is most appropriate?

    1. A.Configure Snowpipe Streaming to ingest records directly from Kafka as they arrive.
    2. B.Use the Snowflake Kafka Connector to sink data into a staging table, and run a scheduled task every 4 hours to process it.
    3. C.Set up a daily batch job using `COPY INTO` that runs at midnight to load all data from the previous day.
    4. D.Use a third-party ETL tool to perform a full reload of the entire clickstream history from the source systems every 4 hours.
    Show answer & explanation

    Correct answer: B — Use the Snowflake Kafka Connector to sink data into a staging table, and run a scheduled task every 4 hours to process it.

    • A. Incorrect. Snowpipe Streaming is designed for low-latency, real-time ingestion. While it would make data available immediately, it incurs higher costs for continuous, row-by-row loading. This approach is not cost-effective for a 4-hour batch retraining cycle and contradicts the requirement to minimize unnecessary real-time ingestion costs.
    • B. Correct. The Snowflake Kafka Connector efficiently micro-batches data from Kafka into a Snowflake staging table using Snowpipe. This decouples ingestion from processing. A scheduled task can then run every 4 hours to process the staged data, perfectly aligning with the model retraining cycle. This strategy is cost-effective by leveraging serverless Snowpipe for ingestion and a scheduled warehouse for processing, avoiding the higher costs of continuous real-time streaming.
    • C. Incorrect. A daily batch job would only make new data available once every 24 hours. This frequency does not meet the requirement to retrain the machine learning model every 4 hours.
    • D. Incorrect. Performing a full reload of the entire clickstream history is extremely inefficient, costly, and resource-intensive. The requirement is to process new data for the retraining cycle, not to reload the entire historical dataset repeatedly.

    1.1 Use a collection system to retrieve data.

    13.A data engineer has configured a Snowpipe to load data from an Azure Blob Storage container using event notifications. After setup, new files are added to the container, but the data does not appear in the target table. A query to `SYSTEM$PIPE_STATUS` shows that no new messages have been received for hours. What are two likely misconfigurations that could cause this specific issue?(Select 2)

    1. A.The virtual warehouse linked to the pipe is suspended.
    2. B.The target table for the pipe does not exist.
    3. C.The Event Grid subscription includes a prefix/suffix filter that does not match the new file paths.
    4. D.The `COPY INTO` statement embedded in the pipe definition has a syntax error.
    5. E.The Storage Integration object in Snowflake is missing the `STORAGE_ALLOWED_LOCATIONS` parameter for the specific container.
    Show answer & explanation

    Correct answers: C, E — The Event Grid subscription includes a prefix/suffix filter that does not match the new file paths.; The Storage Integration object in Snowflake is missing the `STORAGE_ALLOWED_LOCATIONS` parameter for the specific container.

    • A. Incorrect. Snowpipe uses Snowflake-managed serverless compute resources for message queuing and file ingestion. A user-managed virtual warehouse is not required for the pipe to receive event notifications. Therefore, a suspended warehouse would not cause the `SYSTEM$PIPE_STATUS` to show no new messages received.
    • B. Incorrect. If the target table were missing, the event notification would still be received by the pipe. The pipe would then attempt the `COPY` operation, which would fail. The pipe's status would show an updated `lastReceivedMessageTimestamp` and a processing error, which contradicts the symptom of no new messages being received.
    • C. Correct. The Azure Event Grid subscription is responsible for monitoring the storage container and sending notifications to Snowflake when new files are created. If this subscription has a filter (e.g., a path prefix) that does not match the location of the newly added files, Event Grid will not generate or send an event. Consequently, Snowflake's pipe will not receive any new messages, which perfectly matches the observed status.
    • D. Incorrect. A syntax error in the `COPY INTO` statement would cause a failure during the data loading phase, *after* the event notification has been received by the pipe. The pipe's status would indicate a received message followed by a processing error, not an absence of new messages.
    • E. Correct. The `STORAGE_ALLOWED_LOCATIONS` parameter in the storage integration acts as a critical security control, defining the specific storage locations a pipe is authorized to access. If an event notification arrives for a file in a location not explicitly listed, Snowflake will reject the operation for security reasons. This rejection happens before the `COPY` command is executed and can manifest as the pipe not registering a new received message, as the event is deemed invalid for that integration.

    Domain 2: Data Transformation and Data Modeling

    2.4 Use data modeling to manipulate the data to meet BI requirements.

    14.What are the three fundamental entity types that form the core of a Data Vault 2.0 model?(Select 3)

    1. A.Hubs
    2. B.Facts
    3. C.Links
    4. D.Dimensions
    5. E.Satellites
    Show answer & explanation

    Correct answers: A, C, E — Hubs; Links; Satellites

    • A. Correct. Hubs are a fundamental component of a Data Vault 2.0 model. They represent core business entities or concepts (e.g., customer, product, order) and contain the unique business keys, ensuring integration and preventing duplication across source systems.
    • B. Incorrect. Fact tables are central components of dimensional modeling (e.g., star or snowflake schemas), not Data Vault 2.0. While a Data Vault can be used as a source to build fact tables for an information mart, they are not a core entity type within the Data Vault model itself.
    • C. Correct. Links are a fundamental component of a Data Vault 2.0 model. They establish the relationships, associations, or transactions between two or more business entities (Hubs). Links are essentially many-to-many join tables that capture the interactions between core business concepts.
    • D. Incorrect. Dimension tables are a key part of dimensional modeling, used in star and snowflake schemas to store descriptive attributes. This modeling paradigm is distinct from the core structure of a Data Vault.
    • E. Correct. Satellites are a fundamental component of a Data Vault 2.0 model. They store the descriptive, contextual, and historical attributes related to a specific Hub or Link. Satellites track changes over time, providing a complete, audited history of the data associated with a business key or a relationship.

    2.4 Use data modeling to manipulate the data to meet BI requirements.

    15.An organization has a mature Data Vault model serving as its data warehouse integration layer. A business unit requires a performant, easy-to-query data set containing only the *current* state of all customer attributes. The existing solution of using a complex view with multiple joins to satellites is too slow. What is the most effective Snowflake-native solution to provide this data?

    1. A.Create a transient table that is rebuilt nightly using a CTAS statement.
    2. B.Create a materialized view that joins the customer hub with the latest record from each satellite, identified by the load date.
    3. C.Instruct the users to use a larger warehouse size when querying the existing view.
    4. D.Export the data to an external stage as Parquet files for them to query.
    Show answer & explanation

    Correct answer: B — Create a materialized view that joins the customer hub with the latest record from each satellite, identified by the load date.

    • A. This is a plausible but suboptimal solution. A nightly rebuild using CTAS means the data has significant latency and is only current once per day. While transient tables can reduce storage costs, a materialized view provides a more effective solution by automatically keeping the data up-to-date in the background.
    • B. This is the most effective Snowflake-native solution. A materialized view pre-computes and physically stores the result of the complex query. This eliminates the expensive joins at query time, drastically improving performance. Snowflake automatically and incrementally maintains the materialized view as the underlying source tables change, ensuring the data remains current with low latency and without manual intervention.
    • C. While increasing the warehouse size can improve query speed, it is a brute-force approach that does not fix the underlying inefficiency of the complex view. This method leads to higher credit consumption and is not an architectural solution to the root performance problem. The goal is to optimize the data access pattern, not just apply more resources.
    • D. This approach is counterproductive as it moves data out of Snowflake's optimized storage and query engine. Querying data in external stages is generally slower than querying native Snowflake tables and introduces unnecessary architectural complexity for data export and file management. It fails to leverage Snowflake's core performance capabilities.

    2.3 Given a dataset or scenario, work with and query the data.

    16.Which of the following aggregate functions will produce a different result for `COUNT(*)` versus `COUNT(expression)` when the table contains rows where `expression` evaluates to SQL NULL?

    1. A.They will always produce the same result.
    2. B.`COUNT(*)` counts all rows, while `COUNT(expression)` only counts rows where `expression` is not NULL.
    3. C.`COUNT(expression)` counts all rows, while `COUNT(*)` only counts rows where at least one column is not NULL.
    4. D.`COUNT(expression)` will raise an error if `expression` evaluates to NULL, while `COUNT(*)` will not.
    Show answer & explanation

    Correct answer: B — `COUNT(*)` counts all rows, while `COUNT(expression)` only counts rows where `expression` is not NULL.

    • A. Incorrect. These two forms of `COUNT` can and will produce different results if the `expression` evaluates to NULL for any rows. `COUNT(*)` includes all rows in its count, whereas `COUNT(expression)` excludes rows where the expression is NULL.
    • B. Correct. This statement accurately describes the fundamental difference. `COUNT(*)` is a special form that counts every row in the specified group or table. In contrast, `COUNT(expression)` evaluates the expression for each row and only increments its counter for rows where the expression is not NULL.
    • C. Incorrect. This statement incorrectly describes the functionality of both functions. `COUNT(*)` counts all rows unconditionally, not just rows with non-NULL columns. `COUNT(expression)` does not count all rows; it specifically excludes rows where the expression is NULL.
    • D. Incorrect. SQL aggregate functions are designed to handle NULL values by ignoring them in calculations, not by raising errors. `COUNT(expression)` will simply not include rows with a NULL result for the expression in its final count.

    2.3 Given a dataset or scenario, work with and query the data.

    17.A gaming company wants to rank players based on their high scores. If multiple players have the same score, they should all receive the same rank, and the next rank should be the count of all players ranked so far plus one. For example, if three players tie for 1st place, the next player should be ranked 4th. Which ranking function provides this specific behavior?

    1. A.`ROW_NUMBER()`
    2. B.`RANK()`
    3. C.`DENSE_RANK()`
    4. D.`NTILE(100)`
    Show answer & explanation

    Correct answer: B — `RANK()`

    • A. Incorrect. `ROW_NUMBER()` assigns a unique, sequential integer to each row according to the specified order. It does not assign the same rank to rows with tied values, which contradicts the company's requirement.
    • B. Correct. The `RANK()` function provides the exact behavior required. It assigns the same rank to rows with identical values (ties). Crucially, it then leaves a gap in the ranking sequence. For example, if three players tie for rank 1, the next player will be assigned rank 4. This perfectly matches the scenario described.
    • C. Incorrect. While `DENSE_RANK()` also assigns the same rank to rows with tied values, it does not leave gaps in the ranking sequence. In the example of three players tying for 1st place, the next player would be ranked 2nd, not 4th. This does not meet the specified requirement.
    • D. Incorrect. `NTILE(n)` is a window function that distributes rows into a specified number of groups (in this case, 100, often used for percentiles). It is used for bucketing data, not for the specific ranking logic described in the question.

    2.5 Optimize query performance.

    18.A data pipeline performs a series of transformations on a staging table `STG_DATA` to populate a production table `PROD_DATA`. The pipeline needs to be idempotent. The final step is a `MERGE` statement. To optimize the pipeline, the team clones `PROD_DATA` into `PROD_DATA_CLONE` at the beginning of the run and performs the `MERGE` into the clone. If the `MERGE` is successful, they swap the clone with the production table. How does this pattern improve performance and reliability?(Select 2)

    1. A.It reduces the amount of micro-partitions scanned during the `MERGE` operation.
    2. B.It allows analytical queries against the `PROD_DATA` table to run without lock contention during the `MERGE` process.
    3. C.It uses Time Travel to automatically revert the `MERGE` if it fails.
    4. D.The `SWAP WITH` operation is a fast, metadata-only transaction, minimizing downtime for the production table.
    5. E.Cloning the table automatically reclusters the data for better `MERGE` performance.
    Show answer & explanation

    Correct answers: B, D — It allows analytical queries against the `PROD_DATA` table to run without lock contention during the `MERGE` process.; The `SWAP WITH` operation is a fast, metadata-only transaction, minimizing downtime for the production table.

    • A. Incorrect. A zero-copy clone creates a new table by copying the metadata of the original, pointing to the same underlying micro-partitions. It does not alter the physical data layout, so the `MERGE` operation on the clone will scan the same number of micro-partitions as it would on the original table.
    • B. Correct. This is a primary benefit of the clone-and-swap pattern. By performing the resource-intensive `MERGE` operation on a separate clone, the original `PROD_DATA` table remains unaffected and unlocked, allowing concurrent analytical queries to run without any contention or performance degradation.
    • C. Incorrect. This pattern provides an alternative recovery mechanism to Time Travel. If the `MERGE` operation on the clone fails, the clone can simply be dropped without any impact on the production table. The pattern itself does not automatically use Time Travel for rollback.
    • D. Correct. The `SWAP WITH` command is a metadata-only transaction. It atomically and almost instantaneously renames the two tables. This minimizes the cutover window, ensuring that the production table is updated with minimal downtime and high reliability.
    • E. Incorrect. Cloning is a metadata operation and does not physically reorganize the data. Therefore, it does not recluster the table. If reclustering is needed for performance, it must be performed as a separate, explicit operation.

    2.5 Optimize query performance.

    19.A query's execution plan shows a `Filter` operator that is taking a long time to execute and is processing a very large number of rows. The `Filter` is applied after a `Join` operator. What is this a common symptom of, and what is a potential optimization?

    1. A.Symptom: Inefficient pruning. Optimization: Add a clustering key to the table.
    2. B.Symptom: Late filtering. Optimization: Rewrite the query to push the filter predicate down to the `TableScan` phase, possibly by applying it to one of the tables before the join.
    3. C.Symptom: Data spilling. Optimization: Increase the warehouse size.
    4. D.Symptom: A cold warehouse cache. Optimization: Rerun the query to warm the cache.
    Show answer & explanation

    Correct answer: B — Symptom: Late filtering. Optimization: Rewrite the query to push the filter predicate down to the `TableScan` phase, possibly by applying it to one of the tables before the join.

    • A. Incorrect. Inefficient pruning describes a situation where the query optimizer is unable to effectively eliminate micro-partitions during the `TableScan` phase. While adding a clustering key can improve pruning, the core problem described here is the *order* of operations (filtering after joining), not the efficiency of the initial data scan.
    • B. Correct. This scenario is a classic symptom of 'late filtering'. The `Filter` is applied after the `Join`, which means the `Join` operator has to process a much larger, unfiltered dataset. The optimal approach, known as predicate pushdown, is to apply the filter as early as possible, ideally during the `TableScan` phase on one of the tables *before* the join. This significantly reduces the amount of data flowing into the join, improving overall query performance.
    • C. Incorrect. Data spilling occurs when the memory available to an operator is insufficient to hold intermediate results, forcing data to be written to local or remote disk. While this is a performance issue, it is a symptom of resource constraints, not an inefficient operator order. The solution for spilling is typically to increase the warehouse size, which is not the correct fix for late filtering.
    • D. Incorrect. A cold warehouse cache means the data needed for the query is not present in the warehouse's local SSD cache and must be fetched from remote storage. This typically affects the initial `TableScan` operation and makes the first run of a query slower. Rerunning the query would warm the cache, but it would not fix the underlying inefficient query plan where the filter is applied too late.

    2.1 Prepare different data types into a consumable format.

    20.Which file format parameter removes leading and trailing whitespace from fields in a CSV file during a `COPY INTO` operation?

    1. A.`STRIP_WHITESPACE = TRUE`
    2. B.`TRIM_SPACE = TRUE`
    3. C.`REMOVE_SPACES = TRUE`
    4. D.`VALIDATE_UTF8 = FALSE`
    Show answer & explanation

    Correct answer: B — `TRIM_SPACE = TRUE`

    • A. Incorrect. The `STRIP_WHITESPACE` parameter does not exist as a valid file format option in Snowflake for the `COPY INTO` command.
    • B. Correct. The `TRIM_SPACE = TRUE` file format parameter is specifically used to remove leading and trailing whitespace from fields in a CSV file during a `COPY INTO` operation. This is a common requirement for data cleaning during the ingestion process.
    • C. Incorrect. The `REMOVE_SPACES` parameter is not a valid file format option in Snowflake for `COPY INTO` operations.
    • D. Incorrect. The `VALIDATE_UTF8` parameter is used to validate the UTF-8 character encoding of the data being loaded. It is unrelated to removing or trimming whitespace from fields.

    2.2 Given a dataset, clean the data.

    21.A data pipeline loads website clickstream data into a VARIANT column named `EVENT_DATA`. A sample event is `{'user_id': 101, 'event': 'click', 'properties': {'page': '/home'}}`. Sometimes the `properties` key is missing or `null`. A query needs to extract the `page` property, but it must not fail if the `properties` key is absent. Which expressions can safely extract the page value, returning `NULL` when it's not present?(Select 3)

    1. A.GET(EVENT_DATA, 'properties'):page::STRING
    2. B.TRY_CAST(EVENT_DATA:properties.page AS STRING)
    3. C.EVENT_DATA:properties:page::STRING
    4. D.JSON_EXTRACT_PATH_TEXT(EVENT_DATA, 'properties', 'page')
    5. E.EVENT_DATA['properties']['page']::STRING
    Show answer & explanation

    Correct answers: B, D, E — TRY_CAST(EVENT_DATA:properties.page AS STRING); JSON_EXTRACT_PATH_TEXT(EVENT_DATA, 'properties', 'page'); EVENT_DATA['properties']['page']::STRING

    • A. Incorrect. This expression is syntactically invalid in Snowflake. The colon notation (`:`) for path traversal cannot be used on the result of a function like `GET()`. This query will fail with a compilation error.
    • B. Correct. The path traversal using colon and dot notation (`EVENT_DATA:properties.page`) is inherently null-safe in Snowflake; it will return `NULL` if any element in the path does not exist. The `TRY_CAST` function provides an additional layer of safety by ensuring the final value can be converted to a STRING, returning `NULL` if the conversion fails, thus making the entire expression robust.
    • C. Incorrect. While this expression is technically correct and uses Snowflake's null-safe colon notation for path traversal, it is functionally identical to option E. In the context of selecting three distinct, safe methods, the combination of path traversal with an explicit safe cast (`TRY_CAST` in option B), a dedicated JSON function (option D), and a standard path traversal syntax (option E) represents a better set of distinct patterns.
    • D. Correct. The `JSON_EXTRACT_PATH_TEXT` function is specifically designed for this use case. It safely traverses the JSON structure following the provided path elements ('properties', 'page') and returns the final value as text. If the path does not exist, it returns `NULL` without error.
    • E. Correct. The bracket notation (`['properties']['page']`) is a standard, null-safe method for traversing semi-structured data in a VARIANT column. If `EVENT_DATA['properties']` does not exist or is null, the expression evaluates to `NULL` without raising an error, fulfilling the requirement.

    2.2 Given a dataset, clean the data.

    22.An analyst is preparing a report and needs to handle NULL values in the SALES_REP column by replacing them with the string 'Unassigned'. They also want to replace NULL values in the COMMISSION_PCT column with 0. Which two functions are the most direct and appropriate for these respective tasks?(Select 2)

    1. A.NVL(COMMISSION_PCT, '0')
    2. B.COALESCE(SALES_REP, 'Unassigned')
    3. C.IFNULL(SALES_REP, 'Unassigned')
    4. D.ZEROIFNULL(COMMISSION_PCT)
    5. E.REPLACE(SALES_REP, NULL, 'Unassigned')
    Show answer & explanation

    Correct answers: C, D — IFNULL(SALES_REP, 'Unassigned'); ZEROIFNULL(COMMISSION_PCT)

    • A. NVL is equivalent to IFNULL and could work, but it is not the most direct function for numeric NULL-to-zero conversion. Snowflake provides ZEROIFNULL specifically for this purpose, returning 0 when the input is NULL without relying on implicit conversion from a string '0'.
    • B. COALESCE can handle NULL substitution, but it is designed for multiple fallback values. For a single default, IFNULL is more direct and readable. While COALESCE is a powerful general-purpose function, IFNULL is purpose-built for this exact scenario.
    • C. IFNULL is the most direct function for replacing NULLs in a string column with a default value. According to Snowflake documentation, IFNULL returns the second argument if the first is NULL, making it a clear and straightforward choice for handling unassigned sales representatives.
    • D. ZEROIFNULL is the dedicated function for replacing NULLs with 0 in numeric expressions, as documented by Snowflake. It returns 0 if the input is NULL, ensuring that arithmetic operations on COMMISSION_PCT are not disrupted by NULL propagation.
    • E. REPLACE is a string function that substitutes substrings and does not handle NULL values. If SALES_REP is NULL, the function returns NULL, not 'Unassigned'. It is not appropriate for NULL replacement.

    Domain 3: Data Analysis

    3.2 Perform a descriptive analysis.

    23.An analyst is performing a time-series analysis and needs to compare each month's sales to the sales of the same month in the previous year. For example, compare February 2023 sales to February 2022 sales. Which window function is ideal for fetching this prior year data point within the same row?

    1. A.`LAG(monthly_sales, 1) OVER (PARTITION BY MONTH(sale_date) ORDER BY sale_date)`
    2. B.`LAG(monthly_sales, 12) OVER (ORDER BY sale_date)`
    3. C.`FIRST_VALUE(monthly_sales) OVER (PARTITION BY YEAR(sale_date) ORDER BY sale_date)`
    4. D.`LAG(monthly_sales, 1) OVER (PARTITION BY YEAR(sale_date) ORDER BY sale_date)`
    Show answer & explanation

    Correct answer: B — `LAG(monthly_sales, 12) OVER (ORDER BY sale_date)`

    • A. Incorrect. This function partitions the data by the calendar month (e.g., all Januarys, all Februarys, etc.) and then finds the previous row within that partition. While this would correctly retrieve the prior year's sales for that month, it is a less direct and less common method than using a simple 12-month offset on an ordered dataset, which is considered the ideal approach.
    • B. Correct. This is the ideal and most direct way to solve the problem. The `LAG` function with an offset of 12, ordered by the date, looks back exactly 12 rows (months) in the time series. This correctly fetches the sales data from the same month of the previous year, assuming the monthly data is contiguous.
    • C. Incorrect. The `FIRST_VALUE` function, when partitioned by year, would return the sales of the first month of that year (e.g., January's sales) for every row within that year. It does not compare to the previous year's data.
    • D. Incorrect. Partitioning by year and using `LAG` with an offset of 1 would retrieve the sales from the previous month within the same year (e.g., for February, it would get January's sales). This does not meet the requirement of comparing to the same month in the prior year.

    3.1 Use SQL extensibility features.

    24.A UDF is created to mask credit card numbers, returning '****-****-****-' followed by the last four digits. This UDF is applied to a column in a view. For security reasons, the data governance team wants to ensure that the logic of the UDF is not exposed to users who can see the view's definition. What type of UDF should be created?

    1. A.An IMMUTABLE UDF
    2. B.A VOLATILE UDF
    3. C.A SECURE UDF
    4. D.An OWNER'S RIGHTS UDF
    Show answer & explanation

    Correct answer: C — A SECURE UDF

    • A. Incorrect. The IMMUTABLE property indicates that the UDF is deterministic, meaning it will always return the same result for the same input. This allows Snowflake to optimize queries by caching results. It does not, however, hide the underlying logic of the function from users.
    • B. Incorrect. The VOLATILE property indicates that the UDF is non-deterministic, meaning it can return different results even for the same input (e.g., a function that returns the current timestamp). This property is the opposite of IMMUTABLE and is unrelated to hiding the function's implementation details.
    • C. Correct. A SECURE UDF is specifically designed to address this security requirement. When a UDF is marked as SECURE, its internal logic and definition are not exposed to users who do not have OWNERSHIP privilege on the function, even if they can see the definition of a view that uses it. This prevents sensitive business logic from being revealed.
    • D. Incorrect. 'OWNER'S RIGHTS' is a concept related to stored procedures (EXECUTE AS OWNER), which determines the privilege set under which the procedure runs. It is not a type of UDF and does not control the visibility of the UDF's definition.

    3.1 Use SQL extensibility features.

    25.A stored procedure is designed to loop through a list of table names and grant `SELECT` privileges on each to a specific role. The procedure is written in Snowflake Scripting. Which construct would be most appropriate for iterating through the results of a `SHOW TABLES` command within the procedure?

    1. A.A `WHILE` loop with a counter variable.
    2. B.A `REPEAT ... UNTIL` loop.
    3. C.A `FOR ... IN ... DO` loop using a cursor for the `SHOW TABLES` result.
    4. D.A `LOOP ... END LOOP` with an `IF ... BREAK` condition.
    Show answer & explanation

    Correct answer: C — A `FOR ... IN ... DO` loop using a cursor for the `SHOW TABLES` result.

    • A. Incorrect. While a `WHILE` loop can be used for iteration, it is not the most appropriate for processing a result set. It would require manual management of a counter, fetching rows individually, and checking for the end of the result set, making the code more complex and less efficient than a cursor-based approach.
    • B. Incorrect. A `REPEAT ... UNTIL` loop executes the loop body at least once before checking the condition. This is unsuitable for iterating over a result set which might be empty, as it would cause an error or unexpected behavior.
    • C. Correct. The `FOR ... IN ... DO` loop using a cursor is the most idiomatic, efficient, and appropriate construct in Snowflake Scripting for iterating over the rows of a result set. It automatically handles opening the cursor, fetching each row into a variable, and closing the cursor when all rows have been processed, resulting in cleaner and more readable code.
    • D. Incorrect. A generic `LOOP ... END LOOP` is an unconditional loop that requires manual implementation of fetching logic and an explicit `IF ... BREAK` statement to exit. This is more verbose and less straightforward than using a `FOR` loop with a cursor, which is specifically designed for iterating through result sets.

    3.4 Perform forecasting.

    26.A manufacturing company is analyzing the relationship between production volume and machine temperature. They use `REGR_R2` to evaluate their linear model and get a result of 0.05. What does this value indicate?

    1. A.A strong positive linear relationship exists.
    2. B.The model is an excellent fit for the data.
    3. C.There is almost no linear relationship; the model explains very little of the variability in the data.
    4. D.A strong negative linear-relationship exists.
    Show answer & explanation

    Correct answer: C — There is almost no linear relationship; the model explains very little of the variability in the data.

    • A. Incorrect. The `REGR_R2` function, also known as the R-squared or coefficient of determination, measures the proportion of variance explained by the model. A value of 0.05 is very close to 0, indicating a very weak relationship, not a strong one. A strong relationship, whether positive or negative, would have an R-squared value close to 1.
    • B. Incorrect. An R-squared value of 0.05 signifies that the model only explains 5% of the variability in the data. This indicates a very poor fit, not an excellent one. An excellent fit would be represented by an R-squared value approaching 1.
    • C. Correct. An `REGR_R2` value of 0.05 means that only 5% of the variability in the dependent variable (e.g., machine temperature) can be explained by the independent variable (e.g., production volume). This indicates an extremely weak or practically non-existent linear relationship, making the model a poor predictor.
    • D. Incorrect. The R-squared value measures the strength of the linear relationship, not its direction. A strong negative linear relationship would still result in an R-squared value close to 1. The direction of the relationship (positive or negative) is determined by the slope (e.g., from `REGR_SLOPE`), not by `REGR_R2`.

    3.4 Perform forecasting.

    27.You need to present a forecast to business leaders and must explain the uncertainty in the prediction. The model was generated with the default `prediction_interval` of 95. What is the correct interpretation of the `lower_bound` and `upper_bound` values for a given future timestamp?

    1. A.There is a 95% probability that the actual value will be higher than the `upper_bound` or lower than the `lower_bound`.
    2. B.The model predicts with 95% certainty that the actual value will fall somewhere within the range defined by the `lower_bound` and `upper_bound`.
    3. C.95% of the historical data points fell between the `lower_bound` and `upper_bound`.
    4. D.The `forecast` value is exactly 95% of the way between the `lower_bound` and `upper_bound`.
    Show answer & explanation

    Correct answer: B — The model predicts with 95% certainty that the actual value will fall somewhere within the range defined by the `lower_bound` and `upper_bound`.

    • A. Incorrect. This statement describes the inverse of the prediction interval. With a 95% prediction interval, there is a 5% (100% - 95%) probability that the actual value will fall outside the range, i.e., be higher than the `upper_bound` or lower than the `lower_bound`.
    • B. Correct. This is the definition of a prediction interval. The `prediction_interval` parameter, which defaults to 95, specifies the probability that the true future value will fall within the range defined by the `lower_bound` and `upper_bound` columns generated by the forecasting model.
    • C. Incorrect. The prediction interval and its bounds (`lower_bound`, `upper_bound`) apply to future predicted values, not the historical data used to train the model. They quantify the uncertainty of the forecast, not describe the distribution of past data.
    • D. Incorrect. The `forecast` value is the point estimate, or the single most likely predicted value. The `lower_bound` and `upper_bound` create a range of uncertainty around this point estimate. The forecast value is not positioned at a specific percentage within this interval.

    3.3 Perform a diagnostic analysis.

    28.An analyst is tasked with identifying the root cause of a recent surge in `OUT_OF_STOCK` errors on an e-commerce website. They have access to `SALES_TRANSACTIONS`, `INVENTORY_LEVELS`, and `MARKETING_CAMPAIGNS` tables. Which three investigative paths using Snowflake SQL would be most effective for this diagnostic analysis?(Select 3)

    1. A.Identify products with high sales velocity by joining `SALES_TRANSACTIONS` with `MARKETING_CAMPAIGNS` to see if a specific promotion is driving unexpected demand.
    2. B.Use the `LAG` window function on the `INVENTORY_LEVELS` table (partitioned by `product_id`) to find products with the sharpest recent drop in stock.
    3. C.Check the `SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY` to see if the warehouse was overloaded, preventing inventory updates.
    4. D.Query the `COPY_HISTORY` for the inventory table to ensure the data feeds from suppliers have been loading successfully and on schedule.
    5. E.Calculate the `CORR()` between marketing spend and out-of-stock errors to prove a general relationship.
    Show answer & explanation

    Correct answers: A, B, D — Identify products with high sales velocity by joining `SALES_TRANSACTIONS` with `MARKETING_CAMPAIGNS` to see if a specific promotion is driving unexpected demand.; Use the `LAG` window function on the `INVENTORY_LEVELS` table (partitioned by `product_id`) to find products with the sharpest recent drop in stock.; Query the `COPY_HISTORY` for the inventory table to ensure the data feeds from suppliers have been loading successfully and on schedule.

    • A. This is a highly effective diagnostic path. A sudden increase in demand driven by a specific marketing campaign is a common cause for stockouts. Joining sales and marketing data allows the analyst to directly test this hypothesis by correlating a sales spike for specific products with promotional activities, leading to a clear potential root cause.
    • B. This is a very effective method for pinpointing the specific products most affected by the issue. By using the `LAG` window function partitioned by `product_id` and ordered by a timestamp, an analyst can precisely calculate the change in inventory over time. This allows them to identify which products experienced the most significant recent drops, thereby focusing the investigation.
    • C. This is not an effective path for this business problem. Warehouse load history relates to Snowflake compute resource performance. An `OUT_OF_STOCK` error is a business data issue. While extreme warehouse overload could theoretically delay data updates, it's a much less direct and less likely root cause than investigating the business data (sales, inventory levels) or the data ingestion process itself.
    • D. This is a critical and effective investigative path that addresses data integrity. A plausible root cause is that inventory replenishment data is not being loaded into Snowflake correctly or on time. If the data ingestion fails, the inventory levels in the database become stale, leading to incorrect stock counts. Querying `COPY_HISTORY` (either in `INFORMATION_SCHEMA` or `ACCOUNT_USAGE`) is the direct way to verify that data loading jobs are running successfully.
    • E. This is an ineffective path for root cause analysis. The `CORR()` function provides a single statistical value for an overall linear relationship, which is too general for this diagnostic task. It lacks the granularity to identify the specific products, campaigns, or timeframes causing the recent surge, making it a descriptive tool rather than a diagnostic one.

    3.3 Perform a diagnostic analysis.

    29.A manufacturing company's sensor data, stored in a table `SENSOR_READINGS`, shows an anomalous spike in temperature readings from a specific assembly line. To diagnose if this is a sensor malfunction or a real event, the analyst wants to compare the readings of the suspect sensor with the average readings of all other sensors on the same assembly line at the same time. Which two SQL features are best suited for this comparison within a single query?(Select 2)

    1. A.A recursive common table expression (CTE).
    2. B.A window function like `AVG(temperature) OVER (PARTITION BY assembly_line_id, reading_timestamp)` to calculate the line's average temperature at that moment.
    3. C.A self-join on the `SENSOR_READINGS` table to compare each sensor's reading to others.
    4. D.The `PIVOT` function to transform sensor IDs into columns.
    5. E.A conditional `CASE` statement to isolate the suspect sensor's readings from the others for direct comparison in the `SELECT` clause.
    Show answer & explanation

    Correct answers: B, E — A window function like `AVG(temperature) OVER (PARTITION BY assembly_line_id, reading_timestamp)` to calculate the line's average temperature at that moment.; A conditional `CASE` statement to isolate the suspect sensor's readings from the others for direct comparison in the `SELECT` clause.

    • A. Incorrect. A recursive CTE is designed for querying hierarchical data, such as organizational charts or bills of materials, or for graph traversal. It is not suitable for this type of aggregate comparison.
    • B. Correct. A window function is ideal for this scenario. It allows the calculation of an aggregate function, like `AVG()`, over a specific 'window' of data defined by the `PARTITION BY` clause (in this case, for each assembly line at each timestamp) without collapsing the rows. This enables a direct, row-by-row comparison between an individual sensor's reading and the calculated average for its group.
    • C. Incorrect. While a self-join could theoretically be used to achieve this comparison, it is significantly less efficient and more complex to write and maintain than a window function. For large sensor data tables, a self-join would likely have poor performance.
    • D. Incorrect. The `PIVOT` function is used to transform data from a row-level representation to a columnar one. While it could be used to display each sensor's readings in its own column, it does not directly facilitate the comparison of one sensor against the average of all others.
    • E. Correct. A conditional `CASE` statement is a fundamental tool for implementing the required logic. It can be used in the `SELECT` list to isolate the suspect sensor's value into its own column. More powerfully, it can be used *inside* an aggregate window function (e.g., `AVG(CASE WHEN sensor_id <> 'suspect_id' THEN temperature END) OVER (...)`) to precisely calculate the average of only the *other* sensors, directly addressing the core requirement of the diagnostic task.

    Domain 4: Data Presentation and Data Visualization

    4.3 Given a use case, incorporate visualizations for dashboards and reports.

    30.You need to present the breakdown of a company's $50M annual budget. The presentation must clearly show how the starting balance is affected by a sequence of positive values (revenues from different streams) and negative values (costs like salaries, marketing, R&D) to arrive at the final net profit. Which visualization is specifically designed to show this kind of sequential build-up and breakdown of a total?

    1. A.A 100% stacked bar chart
    2. B.A donut chart
    3. C.A waterfall chart
    4. D.A bullet chart
    Show answer & explanation

    Correct answer: C — A waterfall chart

    • A. A 100% stacked bar chart is used to show the relative composition or proportion of categories that make up a whole. It is unsuitable for this scenario because it cannot illustrate the sequential effect of positive and negative values (revenues and costs) on a starting balance to reach a final total.
    • B. A donut chart, similar to a pie chart, is designed to show the proportions of categories that constitute a whole. It lacks the ability to represent a sequential flow of additions and subtractions to an initial value, making it inappropriate for visualizing a budget breakdown from a starting balance to a net profit.
    • C. A waterfall chart is the ideal visualization for this use case as it is specifically designed to show how an initial value is affected by a series of intermediate positive (revenues) and negative (costs) values. It clearly illustrates the cumulative effect of these sequential changes, leading from a starting balance to a final net value, which directly addresses the requirements of the question.
    • D. A bullet chart is a variation of a bar chart designed to compare a primary measure (e.g., year-to-date revenue) against a target or goal, often with qualitative ranges. It is excellent for tracking performance but is not designed to show the sequential build-up or breakdown of a value from its constituent positive and negative parts.

    4.3 Given a use case, incorporate visualizations for dashboards and reports.

    31.A project manager is building a dashboard to track multiple software development projects. For each project, they need to visualize: 1) The percentage of tasks completed against the total tasks (progress towards a 100% goal). 2) The current project status ('On Track', 'At Risk', 'Delayed'). 3) The proportional breakdown of remaining tasks by priority ('High', 'Medium', 'Low'). Which THREE visualizations are most appropriate for these specific requirements?(Select 3)

    1. A.A bullet chart to show the percentage of tasks completed against the 100% target line.
    2. B.A data table that includes a 'Status' column with conditional formatting (e.g., colored icons or text) for each project.
    3. C.A line chart tracking the cumulative number of completed tasks over time for all projects combined.
    4. D.A 100% stacked bar chart to show the proportion of tasks by priority for each project.
    5. E.A scatter plot correlating project budget with the total number of tasks.
    Show answer & explanation

    Correct answers: A, B, D — A bullet chart to show the percentage of tasks completed against the 100% target line.; A data table that includes a 'Status' column with conditional formatting (e.g., colored icons or text) for each project.; A 100% stacked bar chart to show the proportion of tasks by priority for each project.

    • A. Correct. A bullet chart is an ideal visualization for tracking progress towards a goal. It concisely displays the current value (percentage completed) against a target (the 100% line), making it perfect for fulfilling the first requirement.
    • B. Correct. A data table is a clear way to list multiple projects and their attributes. Adding conditional formatting (e.g., coloring the text or using status icons) to a 'Status' column allows for rapid, at-a-glance assessment of project health ('On Track', 'At Risk', 'Delayed'), satisfying the second requirement.
    • C. Incorrect. A line chart is used to show trends over time. The requirements ask for the current state of projects, not their historical performance trend.
    • D. Correct. A 100% stacked bar chart is specifically designed to show part-to-whole relationships. It would effectively visualize the proportional breakdown of remaining tasks by priority ('High', 'Medium', 'Low') for each project, directly addressing the third requirement.
    • E. Incorrect. A scatter plot is used to explore the relationship or correlation between two numerical variables (budget and task count). This does not align with any of the stated requirements.

    4.2 Given a use case, maintain reports and dashboards to meet business requirements.

    32.A team of analysts is building a dashboard that joins customer data from the `CRM_PROD` database with financial data from the `FINANCE_PROD` database. The dashboard queries must run using the `ANALYST_WH` virtual warehouse. The team's role, `FINANCE_ANALYST`, owns the dashboard. What privileges are required for the `FINANCE_ANALYST` role to successfully run and display data on the dashboard?(Select 3)

    1. A.`USAGE` on the `ANALYST_WH` warehouse.
    2. B.`SELECT` on all tables being queried in both databases.
    3. C.`OWNERSHIP` on both the `CRM_PROD` and `FINANCE_PROD` databases.
    4. D.`USAGE` on both the `CRM_PROD` and `FINANCE_PROD` databases and their respective schemas.
    5. E.`CREATE DASHBOARD` on the `FINANCE_PROD` database.
    Show answer & explanation

    Correct answers: A, B, D — `USAGE` on the `ANALYST_WH` warehouse.; `SELECT` on all tables being queried in both databases.; `USAGE` on both the `CRM_PROD` and `FINANCE_PROD` databases and their respective schemas.

    • A. Correct. To execute any query or DML statement in Snowflake, a role must have the `USAGE` privilege on the virtual warehouse being used for computation. Since the dashboard queries must run on `ANALYST_WH`, this privilege is mandatory for the `FINANCE_ANALYST` role.
    • B. Correct. The `SELECT` privilege is required to read data from tables. As the dashboard joins data from tables in both the `CRM_PROD` and `FINANCE_PROD` databases, the `FINANCE_ANALYST` role needs `SELECT` privileges on all the specific tables being queried.
    • C. Incorrect. `OWNERSHIP` is an administrative-level privilege that grants full control over an object. It is excessive for a role that only needs to read data. Adhering to the principle of least privilege, only the necessary permissions (`USAGE` and `SELECT`) should be granted.
    • D. Correct. Snowflake's security model is hierarchical. To access an object like a table, a role needs the `USAGE` privilege on all parent containers. Therefore, to select data from tables within the `CRM_PROD` and `FINANCE_PROD` databases, the `FINANCE_ANALYST` role must have `USAGE` on both databases and also on the specific schemas containing the tables.
    • E. Incorrect. `CREATE DASHBOARD` is not a valid or recognized privilege in Snowflake's role-based access control system. This option is a distractor. The question concerns the privileges needed to run the queries that populate a dashboard, not a non-existent object creation privilege.

    4.1 Given a use case, create reports and dashboards to meet business requirements.

    33.A marketing analyst is building a dashboard to show the effectiveness of different advertising campaigns. The data is spread across three tables: `CAMPAIGNS` (campaign details), `CLICKS` (click events with campaign_id), and `CONVERSIONS` (conversion events with click_id). To create a chart showing conversions per campaign, what is the best approach for sourcing the data?

    1. A.Create a dashboard tile that queries only the `CONVERSIONS` table.
    2. B.Write a query for the dashboard tile that joins `CAMPAIGNS`, `CLICKS`, and `CONVERSIONS`.
    3. C.Create a secure view that joins the three tables and then build the dashboard tile on top of the view.
    4. D.Export the data from all three tables to a CSV and re-upload it to a single summary table.
    Show answer & explanation

    Correct answer: B — Write a query for the dashboard tile that joins `CAMPAIGNS`, `CLICKS`, and `CONVERSIONS`.

    • A. Incorrect. Querying only the `CONVERSIONS` table is insufficient as it does not contain campaign information. To attribute conversions to specific campaigns, a join with the `CAMPAIGNS` and `CLICKS` tables is necessary to establish the relationship.
    • B. Correct. This is the most direct and efficient approach for this use case. Writing a single SQL query that joins the `CAMPAIGNS`, `CLICKS`, and `CONVERSIONS` tables allows the analyst to aggregate the data precisely as needed for the chart, directly linking conversions back to their source campaigns.
    • C. Incorrect. While creating a view is a valid and often good practice for reusability, abstraction, and enforcing security, it adds an extra layer of abstraction that is not strictly required by the question. For the specific task of creating a single dashboard tile, a direct query is simpler and more straightforward, making it the best answer in this context.
    • D. Incorrect. This is an anti-pattern that goes against data warehousing best practices. Exporting and re-importing data is inefficient, introduces data latency, creates redundant data, and fails to leverage Snowflake's core strength of performing complex joins on live data.

    4.1 Given a use case, create reports and dashboards to meet business requirements.

    34.How does Snowflake's Dynamic Data Masking (DDM) affect the results of a query used in a BI tool or a Snowsight dashboard?

    1. A.DDM prevents the query from running if the user's role does not have UNMASK privileges.
    2. B.DDM returns NULL values for all masked columns, regardless of the policy definition.
    3. C.DDM transparently rewrites the column value in the result set based on the user's role and the policy logic, after the query has been executed.
    4. D.DDM pre-filters rows from the table before any query predicates are applied.
    Show answer & explanation

    Correct answer: C — DDM transparently rewrites the column value in the result set based on the user's role and the policy logic, after the query has been executed.

    • A. Incorrect. Dynamic Data Masking does not prevent a query from running. The query executes successfully for all users, but the masking policy is applied to the results for unauthorized roles, altering the data they see. It does not block the query itself.
    • B. Incorrect. The behavior of the mask is determined entirely by the policy's definition. A policy can be written to return NULL, a fixed string like '*****', a hash of the value, or a partially masked value. The statement that it returns NULL 'regardless of the policy definition' is false.
    • C. Correct. This is the most accurate description. DDM works at query time, transparently to the BI tool or user. When a query is run, Snowflake applies the logic within the masking policy—which typically checks the user's current role—to the column data being returned. This dynamically rewrites the values in the final result set for unauthorized users, while the underlying data in the table remains unchanged.
    • D. Incorrect. This describes the function of a Row Access Policy, not Dynamic Data Masking. DDM is a column-level security feature that masks data within a column. Row Access Policies are used to filter entire rows from a result set based on the user's role.

    4.2 Given a use case, maintain reports and dashboards to meet business requirements.

    35.A company wants to track all queries executed by a critical financial dashboard in Snowsight. They need to know which user executed the query and when it occurred for audit purposes. Which Account Usage view should they query to retrieve this information?

    1. A.QUERY_HISTORY
    2. B.ACCESS_HISTORY
    3. C.DASHBOARD_HISTORY
    4. D.OBJECT_MODIFIED
    Show answer & explanation

    Correct answer: A — QUERY_HISTORY

    • A. Correct. The `QUERY_HISTORY` view in the `ACCOUNT_USAGE` schema records all executed queries, including those that populate Snowsight dashboard tiles. It captures query text, user, execution time, and other metadata, making it suitable for auditing dashboard query activity.
    • B. Incorrect. `ACCESS_HISTORY` records when user queries read or write data to source objects. While it can be joined with `QUERY_HISTORY` to correlate access, it does not directly provide query execution details; it focuses on data access events rather than the queries themselves.
    • C. Incorrect. There is no `DASHBOARD_HISTORY` view in Snowflake. This is a distractor; Snowsight dashboards do not have a built-in version history. Tracking changes to dashboard definitions requires external version control of the underlying SQL.
    • D. Incorrect. There is no `OBJECT_MODIFIED` view in Snowflake's `ACCOUNT_USAGE` schema. This is a distractor; modifications to Snowflake objects are generally not tracked in a single centralized view.

    Want the full experience?

    These are just samples. Practice the full Snowflake SnowPro Advanced: Data Analyst (DAA-C01) question bank in quiz mode — free, no signup, with domain practice and exam simulation.

    Practice all 569 questions in quiz mode