SOL-C01 Question #149: Real Exam Question with Answer & Explanation

Question

You are tasked with creating a table `EMPLOYEES in Snowflake to store employee data. The table should have columns for 'employee_id' (INT, primary key), 'first_name' (VARCHAR(50)), 'last_name' (VARCHAR(50)), 'email' (VARCHAR(IOO)), and 'hire_date' (DATE). You want to ensure that when loading data, any rows with duplicate 'employee_id' values are rejected without failing the entire load . Furthermore, you need to automatically generate surrogate keys for any new departments added to the 'DEPARTMENTS' table, which is not currently populated but will be loaded later. Which of the following approaches correctly combines these requirements?

Options

• ACreate the 'EMPLOYEES table with a unique constraint on 'employee_id' and use a COPY INTO
• BCreate the 'EMPLOYEES' table with 'employee_id' as the primary key. Use a COPY INTO
• CCreate the 'EMPLOYEES' table with 'employee_id' as the primary key. Use a COPY INTO
• DCreate the 'EMPLOYEES' table without a primary key constraint. Use a COPY INTO statement
• ECreate the `EMPLOYEES table with a unique constraint on 'employee_id'. IJse a COPY INTO

Explanation

Option E correctly addresses both requirements. Creating a unique constraint on allows Snowflake to identify duplicate rows. The = SKIP FILE' option, combined with 'VALIDATION_MODE = RETURN ERRORS', ensures that duplicate rows will not be inserted, and the COPY INTO operation will continue. Using an IDENTITY column for "DEPARTMENTS' simplifies the automatic generation of surrogate keys. Options A, B, and C will not properly handle the error situation and may stop the load completely. Option D doesn't prevent duplicates and the Snowflake Task isn't the simplest approach.

No community discussion yet for this question.