SOL-C01 · Question #158
You are designing a data warehouse in Snowflake and need to load data from various sources. You have a table named 'staging_customers' that contains raw customer data. You want to create a new table n
Sign in or unlock SOL-C01 to reveal the answer and full explanation for question #158. The question stem and answer options stay visible for context.
Question
You are designing a data warehouse in Snowflake and need to load data from various sources. You have a table named 'staging_customers' that contains raw customer data. You want to create a new table named 'customers' that contains cleansed and transformed data from the 'staging _ customers' table. You need to perform the following transformations: 1) Convert the 'customer name' to uppercase. 2) Remove leading and trailing spaces from the 'customer address'. 3) Handle potential duplicate records based on the 'customer id' by only inserting the latest record (assuming 'load_date' indicates the load timestamp). Which of the following approaches, using a combination of CTAS (CREATE TABLE AS SELECT) and other Snowflake features, is the MOST efficient and recommended way to achieve this? A. B. C. D. E.
Options
- ACREATE TABLE customers AS SELECT UPPER(customer_name) AS customer_name, TRIM(customer_address) AS customer_address FROM staging_customers WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_date DESC) = 1;
- BCREATE TABLE customers AS SELECT UPPER(customer_name) AS customer_name, TRIM(customer_address) AS customer_address FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_date DESC) AS rn FROM staging_customers) WHERE rn = 1;
- CCREATE TABLE customers AS SELECT UPPER(customer_name) AS customer_name, TRIM(customer_address) AS customer_address FROM staging_customers GROUP BY customer_id;
- DCREATE TABLE customers AS SELECT DISTINCT UPPER(customer_name) AS customer_name, TRIM(customer_address) AS customer_address FROM staging_customers;
Unlock SOL-C01 to see the answer
You've previewed enough free SOL-C01 questions. Unlock SOL-C01 for full answers, explanations, the timed quiz mode, progress tracking, and the master PDF. Question stem and options stay visible so you can still see what's on the exam.