100%無料SnowPro Advanced DAA-C01問題集PDFお試しサンプル認定ガイドカバー率
PDF試験材料2025年最新の実際に出るDAA-C01問題集
質問 # 40
You have a table named 'SALES DATA' containing daily sales records. You need to identify and handle outliers in the 'SALES AMOUNT' column. Specifically, you want to replace any 'SALES AMOUNT values that fall outside of three standard deviations from the mean with the median 'SALES AMOUNT. What is the most efficient way to achieve this data transformation in Snowflake?
- A. Use a single 'CREATE OR REPLACE TABLE AS SELECT (CTAS) statement with window functions and a 'CASE expression to calculate the mean, standard deviation, and median, and replace outliers in the same query.
- B. Create a stored procedure to iterate through each row and check for outliers, updating the 'SALES_AMOUNT accordingly.
- C. Clone the table, calculate the mean, stddev and median on the original table, and use it in CASE statement, and create new sales amount column for identifying outliers and insert into the cloned table.
- D. Calculate mean and standard deviation using separate queries, then use an UPDATE statement with a WHERE clause to identify and replace outliers.
- E. Export the data to an external system, perform the outlier detection and replacement there, and then load the cleaned data back into Snowflake.
正解：A
解説：
Option C is the most efficient approach. Using a CTAS statement with window functions allows you to calculate the mean, standard deviation, and median in a single pass through the data. The 'CASE expression enables you to identify and replace outliers inline, avoiding multiple queries or iterative procedures. Options A involves multiple queries, Option B is inefficient due to row-by-row processing in a stored procedure, and Option D involves unnecessary data transfer to an external system. Option E is similar to C but uses a clone unnecessarily adding complexity and cost. Clones are useful for isolating changes not necessarily part of the transformation itself. The CTAS operation provides similar isolation within a single statement.
質問 # 41
You have identified a valuable dataset on the Snowflake Marketplace related to weather patterns. To consume this data, you perform the following actions: 1. You request and receive the data share from the provider. 2. You create a database named 'WEATHER DB' from the share. Now you want to create a secure view named 'DAILY WEATHER SUMMARY in your own database 'ANALYTICS DB.PUBLIC', which joins your internal sales data C ANALYTICS DB.PUBLIC.SALES) with the weather data from the provider's 'WEATHER DB.WEATHER SCHEMA.DAILY WEATHER' table. You only want to expose specific columns from both tables in your view to minimize data exposure. Which of the following steps are required to ensure this secure and functional integration?
- A. Grant 'SELECT privileges on 'ANALYTICS DB.PUBLIC.SALES' to the share provider.
- B. Create an outbound share and grant usage on the ANALYTICS DB database
- C. Create a warehouse with access control policies enabled.
- D. Create the view using fully qualified names, selecting only the necessary columns from both the 'SALES' table and the 'DAILY WEATHER table.
- E. No additional steps are required; you can directly query in your view definition.
正解：D
解説：
Option D is the correct answer. When consuming data from a Snowflake Marketplace data share, you do not need to grant privileges to the provider on your internal data (Option A is incorrect). Option B is incorrect because creating views and acessing tables requires proper schema names. Option C is incorrect because outbound shares are not needed to consume marketplace data and access control is managed within the consumer account. Option E is incorrect because enabling access control policies on a warehouse is irrelevant to the task of creating a secure view that joins internal data with shared data.
質問 # 42
A marketing company is analyzing customer purchase data stored in Snowflake to understand which customer demographics are most likely to purchase a newly launched product. The 'CUSTOMERS table has columns: 'customer_id', 'age' , 'gender' , 'location' , and 'household income'. The 'PURCHASES' table has columns: 'customer_id', 'purchase_date', and 'product id'. Which SQL query would most effectively identify the top three age groups with the highest purchase rate for the new product (product_id = 'NEW PRODUCT')?
- A. Option C
- B. Option D
- C. Option A
- D. Option B
- E. Option E
正解：B
解説：
Option D is the most effective because it calculates the purchase rate by dividing the count of distinct customers who purchased the new product by the total number of customers in each age group. This provides a normalized purchase rate for each age group, allowing for a fair comparison regardless of the size of each group. Options A and C only provide the raw count of purchases or unique purchasers, which doesn't account for the size of each age group. Option B uses 'SUM(CASE...V , which can work, but is less efficient than using 'COUNT(DISTINCTV in combination with a left join to account for all customers, even those who didn't make any purchases. Option E would produce an incorrect rate as it is dividing a customer's count by the distinct count of each customer, resulting in a rate close to 1.0 for each customer that made a purchase.
質問 # 43
You are building a sales performance dashboard in Snowflake. You need to incorporate a custom metric called 'Sales Efficiency', which is calculated as (Total Sales Revenue / Number of Sales Representatives) 1000. You want to expose this metric in your dashboard and allow users to filter data based on specific Sales Efficiency ranges. You also want to allow filtering by region and quarter. Which of the following approaches would be MOST performant and maintainable in Snowflake?
- A. Create a Snowflake stored procedure that calculates 'Sales Efficiency' based on the selected Region and Quarter. The dashboard calls the stored procedure and displays the returned result set.
- B. Calculate 'Sales Efficiency' directly in the dashboard tool for each query using its expression engine. Implement region and quarter filtering within the dashboard tool.
- C. Create a Snowflake user-defined function (IJDF) in JavaScript to calculate 'Sales Efficiency'. Use this IJDF in the dashboard queries, along with standard WHERE clauses for region and quarter filtering.
- D. Create a materialized view in Snowflake that pre-calculates 'Sales Efficiency', region, and quarter. Include appropriate indexes and partition keys on the view. Expose the materialized view to the dashboard.
- E. Create a Snowflake view that calculates 'Sales Efficiency'. The dashboard queries filter this view using standard WHERE clauses for region and quarter. No indexes or partition keys are created.
正解：D
解説：
A materialized view (option C) is the most performant option. It pre-calculates the 'Sales Efficiency' metric and stores the results, allowing for fast retrieval. Proper indexing and partitioning further optimize query performance. Performing the calculation in the dashboard tool (option A) is inefficient for large datasets. IJDFs (option B) can be slower than native SQL. A regular view (option D) does not store the pre- calculated data, so the calculation is performed every time the view is queried. Stored procedures are typically less efficient for dashboard queries than materialized views, as they need to be executed each time.
質問 # 44
A key aspect of performing exploratory ad-hoc analyses is:
- A. Relying solely on predefined hypotheses
- B. Limiting data sources
- C. Following a strict data model
- D. Flexibility in querying and data exploration
正解：D
質問 # 45
You have a Snowflake table named 'customer transactionS with columns: 'customer id', 'transaction date', 'transaction amount, and product_categorV. You need to identify customers who have made purchases in more than three different product categories within the last 30 days. Which of the following Snowflake SQL queries is the MOST efficient and accurate way to achieve this, considering the large size of the table?
- A. Option A
- B. Option B
- C. Option E
- D. Option D
- E. Option C
正解：E
解説：
Option C is the most efficient. It directly groups by 'customer_id' and uses the 'HAVING' clause to filter customers who have purchased from more than three distinct product categories within the specified date range. Option A is functionally correct but less concise. Option B uses an inefficient subquery. Option D returns all customer IDs who purchased from any product category within the timeframe. Option E attempts to use window functions and ARRAY AGG, which is unnecessarily complex and less performant for this task.
質問 # 46
In what way can regular views be advantageous in data analysis?
- A. Regular views simplify complex data structures for ease of analysis.
- B. Regular views don't impact query performance significantly.
- C. Regular views can only be utilized in combination with UDFs.
- D. They restrict data access, improving security but hindering analysis.
正解：A
解説：
Regular views simplify complex data structures, aiding ease of analysis by providing a streamlined representation of data.
質問 # 47
In Snowsight, what is the significance of creating diverse chart types (e.g., bar charts, scatter plots, heat grids) for data visualization?
- A. It restricts users to specific chart types for simplicity.
- B. It limits data presentation options for complex datasets.
- C. Different chart types offer varied data representation for better analysis.
- D. Snowsight doesn't support multiple chart types for visualization.
正解：C
解説：
Diverse chart types offer varied data representation, facilitating better analysis in Snowsight.
質問 # 48
You are tasked with improving the performance of queries against a very large table named 'SALES DATA, which contains sales transactions from multiple regions. The table is frequently queried based on 'REGION' , 'SALE DATE, and 'PRODUCT CATEGORY. The data in 'SALE DATE' ranges from 2020 to the present. You want to optimize query performance by leveraging Snowflake's clustering capabilities, but you also want to minimize the impact of data ingestion and maintenance. Considering the characteristics of the data and query patterns, which of the following strategies would be the most effective and cost-efficient approach for clustering 'SALES DATA'?
- A. Partition the table by 'SALE_DATE and cluster within each partition by ' REGIO!V and PRODUCT_CATEGORY.
- B. Cluster by 'REGION', 'SALE DATE , and 'PRODUCT CATEGORY in that order. Monitor clustering depth frequently and rebuild the table if clustering depth exceeds a threshold.
- C. Cluster by 'SALE DATE only. Snowflake's automatic clustering will handle the other dimensions over time, and this minimizes upfront clustering costs.
- D. Cluster by ' REGION' and only. Snowflake will automatically optimize for 'PRODUCT_CATEGORY due to its internal statistics and query optimization.
- E. Create a materialized view pre-aggregating the data by 'REGION' , 'SALE_DATE, and 'PRODUCT_CATEGORY'. No explicit clustering is required on the base table.
正解：B
解説：
Option A, clustering by 'REGION', 'SALE DATE', and 'PRODUCT CATEGORY' in that order is the most effective approach. Given the frequent filtering on these three columns, clustering on them ensures that Snowflake can efficiently prune the data and minimize the amount of data scanned during query execution. By monitoring clustering depth and rebuilding when it exceeds a threshold, you can maintain optimal performance over time as new data is ingested. Option B is not ideal because SALE_DATE alone might not be sufficient for all query patterns. Option C is not ideal because Snowflake does not automatically optimize 'PRODUCT_CATEGORY only because of stats. Option D, Materialized view might not be good because they are usually pre aggregated and in this scenario are required raw data. E is invalid because partition does not exist in snowflake.
質問 # 49
Which statement accurately describes the use of regular views in data analysis?
- A. Regular views limit data accessibility for improved security.
- B. They simplify complex data structures, aiding ease of analysis.
- C. Regular views are exclusively used for administrative tasks.
- D. Regular views offer real-time updates to reflect instantaneous database changes.
正解：B
解説：
Regular views aid analysis by simplifying complex data structures, improving comprehension.
質問 # 50
When dealing with JSON data in Snowflake and utilizing built-in functions for traversing, flattening, and nesting, what complexities might arise? (Select all that apply)
- A. Complexity in handling nested JSON structures
- B. Limitations in processing specific JSON data sizes
- C. Difficulties in parsing and querying nested JSON objects
- D. Challenges in handling specific data types only
正解：A、C
解説：
Handling nested JSON structures and complexities in parsing and querying nested JSON objects might pose challenges when manipulating JSON data in Snowflake using built-in functions.
質問 # 51
Identifying demographics and relationships in a dataset is crucial for:
- A. Data modeling
- B. Database normalization
- C. Data cleaning
- D. Error handling
正解：A
質問 # 52
A retail company wants to build a dashboard to track sales performance by region. They have implemented Dynamic Data Masking on the 'CUSTOMER PHONE' column in their 'CUSTOMERS' table. A user with the 'ANALYST ROLE' needs to see the sales data and masked phone numbers in the dashboard. However, the company also has a Row Access Policy applied to the 'SALES' table that restricts access based on the 'REGION' column, only allowing users to see data from their assigned region. The 'ANALYST ROLE is NOT assigned any specific region. Which approach will allow the dashboard to display the sales data and masked phone numbers for all regions without violating security policies?
- A. Create a secure view that joins the 'SALES' and 'CUSTOMERS' tables, granting the 'ANALYST_ROLE 'SELECT privilege on the view. The secure view preserves the Row Access Policy and dynamic masking, ensuring data security and role-based access.
- B. Create a view that joins the 'SALES' and 'CUSTOMERS' tables. Grant the 'ANALYST_ROLES 'SELECT privilege on the view. Remove the Row Access Policy from the 'SALES' table. The dynamic masking policy will still be applied.
- C. Create a view that joins the 'SALES' and 'CUSTOMERS' tables, granting the 'ANALYST_ROLE 'SELECT privilege on the view. The view will inherit the Row Access Policy of the 'SALES' table, preventing the analyst from seeing all regions. The dynamic masking policy on 'CUSTOMER PHONE will still be applied.
- D. Create a stored procedure that executes with 'CALLER rights. Inside the stored procedure, query the 'SALES and 'CUSTOMERS' tables. Grant the execute privilege on the stored procedure. This bypasses the Row Access Policy and displays all data while still applying dynamic data masking.
- E. Create a stored procedure that executes with 'OWNER rights. Inside the stored procedure, query the 'SALES' and 'CUSTOMERS' tables. Grant the 'ANALYST ROLE execute privilege on the stored procedure. This bypasses both the Row Access Policy and dynamic data masking and displays all data without any restrictions.
正解：A
解説：
A secure view is the best solution. It encapsulates the data access logic and respects both the Row Access Policy (limiting regional access) and Dynamic Data Masking (masking sensitive phone number data). Option B bypasses Row Access policies which isn't desirable. Option D is wrong as removing the Row Access Policy is generally a bad security practice. Option E bypasses the Row Access Policy and masking which is also undesirable.
質問 # 53
Consider the following Snowflake table schema and data: 'CREATE TABLE products (product_id INTEGER, product_name VARCHAR, properties VARIANT);' Data: 'INSERT INTO products VALUES (1, 'Laptop', "silver", "storage": "512GB", "price": 1200.00}'));' 'INSERT INTO products VALUES (2, 'Mouse', "wireless", "dpi": 1600, "price": 25.00}'));' 'INSERT INTO products VALUES (3, 'Keyboard', PARSE JSON('{"layout": "US", "backlit": true, "price": Which of the following SQL queries will return the 'product_name' and 'price' for all products where the 'price' is greater than 50, ensuring that the 'price' is treated as a numeric value for comparison? Select all that apply
- A.
- B.
- C.
- D.
- E.
正解：A、C
解説：
Options B and E are correct. Option B explicitly casts 'properties:price' to a 'NUMBER data type before the comparison, ensuring that the comparison is performed numerically. Option E casts 'properties:price' to a data type and uses TRY_TO_NUMBER to handle potential errors gracefully. Option A is incorrect because Snowflake treats the value extracted from the VARIANT as a string and the string comparison will lead to incorrect results. Options C and D don't work without casting to VARCHAR
質問 # 54
A financial institution uses Snowflake to store customer transaction data'. They need to create a dashboard that visualizes daily transaction volume and average transaction amount for fraud detection purposes. This dashboard needs to be automatically updated every hour. The current dashboard query performance is slow, especially during peak hours. Given that the 'TRANSACTIONS table contains billions of rows, which of the following strategies would BEST optimize both the query performance and the automated update process?
- A. Implement caching within the dashboard application to store the query results and only refresh the data once a day to avoid performance issues
- B. Create a temporary table that stores daily transaction summaries. Truncate and reload the temporary table hourly using a scheduled Snowflake task.
- C. Increase the warehouse size to X-Large and rely on Snowflake's query optimization engine without any changes to the data model or update process.
- D. Create a materialized view that pre-aggregates the daily transaction volume and average transaction amount. Schedule a Snowflake task to refresh the materialized view hourly.
- E. Create a regular view that calculates daily transaction volume and average transaction amount. Use a Snowflake stored procedure to execute the query and update a separate reporting table hourly.
正解：D
解説：
Materialized views are designed for pre-computation of aggregations, providing significant performance improvements. Scheduling a task to refresh the materialized view ensures automatic updates. Regular views are calculated at query time and would not improve performance. Increasing warehouse size (C) might help, but it's not the most efficient solution. Temporary tables are not persistent and truncation/reload is inefficient. Dashboard caching (E) does not solve underlying Snowflake performance issues.
質問 # 55
When handling erroneous data in Snowflake, what approaches can be employed to effectively address data anomalies and ensure data integrity? (Select all that apply)
- A. Utilizing clones for isolated error resolution
- B. Using error tables to capture and analyze erroneous data
- C. Dropping all erroneous records
- D. Performing data transformations without error handling
正解：A、B
解説：
Employing error tables to capture erroneous data for analysis and utilizing clones for isolated error resolution ensures effective handling of data anomalies and maintains data integrity in Snowflake.
質問 # 56
You are designing a data ingestion pipeline for a financial institution. The pipeline loads transaction data from various sources into a Snowflake table named 'TRANSACTIONS. The 'TRANSACTIONS table includes columns such as TRANSACTION , 'ACCOUNT ID', 'TRANSACTION DATE, 'TRANSACTION AMOUNT, and 'TRANSACTION TYPE. The data is loaded in micro- batches using Snowpipe. Due to potential source system errors and network issues, duplicate records with the same 'TRANSACTION ID' are occasionally ingested. You need to ensure data integrity by preventing duplicate 'TRANSACTION_ID' values in the 'TRANSACTIONS' table while minimizing the impact on ingestion performance. Which of the following approaches is the MOST efficient and reliable way to handle this deduplication requirement in Snowflake, considering data integrity and performance?
- A. Define as the primary key on the 'TRANSACTIONS' table. Snowflake will automatically reject any duplicate inserts during Snowpipe ingestion.
- B. Create a stream on the 'TRANSACTIONS' table and use it to identify newly inserted rows. Then, use a merge statement to insert new, distinct transactions into a separate staging table. Finally, periodically truncate the original 'TRANSACTIONS table and load the deduped data from the staging table.
- C. Use a materialized view built on top of the TRANSACTIONS table that selects distinct transaction ids. This ensures that querying through the materialized view returns no duplicates.
- D. Create a staging table with the same schema as 'TRANSACTIONS'. Use a 'MERGE' statement within the Snowpipe load process to insert new records from the incoming data into the 'TRANSACTIONS' table, only if the 'TRANSACTION ID does not already exist. Define 'TRANSACTION ID' as the primary key in the staging table. Use clustering on 'TRANSACTION_ID on the target 'TRANSACTIONS' table.
- E. Create a scheduled task that runs every hour to identify and delete duplicate records based on 'TRANSACTION ID. The task will use a SQL query to find duplicate ' TRANSACTION ID values and remove the older entries.
正解：D
解説：
Option E provides the most performant and robust solution. Although Snowflake doesn't enforce primary key constraints, defining them on the staging table and leveraging a 'MERGE' statement during the Snowpipe load process allows for efficient deduplication. Clustering on TRANSACTION_I[Y on the target table also helps with performance. A regular task would be less efficient and introduce latency. Snowflake does not automatically reject duplicate inserts based on defined primary keys (option A). Materialized views don't prevent duplicate data from entering the base table. Option C is possible but more complex to implement than a MERGE statement.
質問 # 57
You are using Snowpipe to continuously load JSON data from an external stage. Occasionally, some JSON records are malformed and cause the pipe to fail. You want to configure the pipe to skip these invalid records and continue loading valid data, while also capturing the error details for later analysis. Which approach provides the most efficient and appropriate solution for this scenario?
- A. Use the 'ON ERROR = 'CONTINUE'' option in the 'COPY INTO' statement used by the Snowpipe definition in conjunction with the 'VALIDATE function to capture error details.
- B. Use the 'VALIDATION MODE = RETURN ALL ERRORS parameter in the 'COPY INTO' statement and then filter the data based on the errors returned.
- C. Use the = 'SKIP_FILE" option in the 'COPY INTO' statement used by the Snowpipe definition.
- D. Implement custom error handling in your application code to pre-validate JSON records before uploading them to the stage.
- E. Configure the Snowpipe definition to use the 'VALIDATE(O)' function within the 'COPY INTO' statement.
正解：A
解説：
Option E is the most efficient and complete solution. SON ERROR = 'CONTINUE" allows Snowpipe to skip bad records and continue processing. Using it in conjunction with the 'VALIDATE function within the 'COPY INTO' statement enables capturing error information for analysis. This combines error skipping with error logging. Options A, B, C, and D are either less efficient (requiring pre-processing or post- processing of data), or do not provide a comprehensive solution for both skipping and capturing error information. Using (option B) is too coarse-grained as it skips entire files, even with only a few errors. Using 'VALIDATION MODE without ERROR=CONTINUE will still stop the pipe on errors.
質問 # 58
You are preparing to load a large dataset from Parquet files stored in an Azure Blob Storage container into Snowflake using Snowsight. The dataset contains personally identifiable information (PII) and you need to ensure that only authorized users can access this data,. You want to use Snowflake's data masking policies to protect the PII. Which of the following options represents the correct sequence of steps and considerations for achieving this, specifically using Snowsight for the loading and initial policy application phases?
- A. 1. Create an Azure external stage pointing to the Blob Storage container. 2. Create a file format object specifying PARQUET as the file type. 3. Create the target table with the desired schema, including data types. 4. Load the Parquet files directly into the target table using Snowsight. 5. Define masking policies on the target table columns after loading.
- B. 1. Create an Azure external stage pointing to the Blob Storage container. 2. Create a file format object specifying PARQUET as the file type. 3. Load the Parquet files into a staging table with all columns as VARCHAR using Snowsight. 4. Define masking policies on the staging table columns. 5. Create the final table and transfer data from staging, applying the masking policies on the target table as the data moves.
- C. 1. Create an Azure external stage pointing to the Blob Storage container. 2. Create a file format object specifying PARQUET as the file type. 3. Create the target table with the desired schema, including data types. 4. Define masking policies on the target table columns. 5. Load the Parquet files directly into the target table using Snowsight.
- D. 1. Create an Azure external stage pointing to the Blob Storage container. 2. Load the Parquet files into a staging table with all columns as VARCHAR using Snowsight. 3. Create a file format object specifying PARQUET as the file type. 4. Define masking policies on the staging table columns. 5. Create the final table and transfer data from staging, applying the masking policies on the target table as the data moves.
- E. 1. Create an Azure external stage pointing to the Blob Storage container. 2. Create a file format object specifying PARQUET as the file type. 3. Define masking policies on the target table columns. 4. Create the target table with the desired schema, referring to masking policies. 5. Load the Parquet files directly into the target table using Snowsight.
正解：C
解説：
Option E is the most efficient and recommended approach. First, you need to set up the connection to Azure Blob Storage (the external stage) and define the file format for Parquet files. Then, you create the target table with the correct schema to receive the data. The critical step is to define the masking policies before loading the data. This ensures that the masking policies are in place when the data is loaded into the target table. Finally, load the data using Snowsight. Applying masking policies after loading the data (as in option B) is less efficient and leaves a window of opportunity where PII is exposed. Defining policies and then referring to them (as in option C) isn't the typical workflow. Option A is more complex and introduces a VARCHAR staging table, which isn't optimal for Parquet data.
質問 # 59
When automating and repeating data processing tasks, what challenges might be encountered in managing and sharing Snowsight dashboards effectively?
- A. Difficulty in embedding external content
- B. Incompatibility with other BI tools
- C. Ensuring real-time data updates
- D. Limited user access control
正解：A、D
解説：
Managing Snowsight dashboards might pose challenges related to limited user access control and embedding external content seamlessly within the dashboards.
質問 # 60
What actions are involved in performing general DML (Data Manipulation Language) operations in Snowflake? (Select all that apply)
- A. Merging data from multiple tables
- B. Updating existing data
- C. Deleting data entirely
- D. Inserting new data
正解：B、C、D
解説：
General DML operations in Snowflake include inserting, updating, and deleting data.
質問 # 61
Which statistical method is commonly used in forecasting based on historical data?
- A. Simple data aggregation
- B. Regression analysis
- C. Data normalization
- D. Inferential statistics
正解：B
解説：
Regression analysis is frequently employed for forecasting based on historical data, predicting future trends based on past patterns.
質問 # 62
......
更新されたのはSnowflake DAA-C01問題集PDFオンラインエンジン：https://jp.fast2test.com/DAA-C01-premium-file.html
DAA-C01.PDFで問題解答PDFサンプル問題信頼され続ける：https://drive.google.com/open?id=1peaeO45M5ImfolIkrKvZUj5EJ9RQETft