SOL-C01 · Question #149
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'…
The correct answer is E. Create the `EMPLOYEES table with a unique constraint on 'employee_id'. IJse a COPY INTO. Option E correctly combines both requirements: using a unique constraint on employee_id paired with COPY INTO ... ON_ERROR = 'CONTINUE', which skips rows that violate the constraint without aborting the entire load, and defining the DEPARTMENTS table's surrogate key column with…
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
How the community answered
(22 responses)- B5% (1)
- C14% (3)
- D9% (2)
- E73% (16)
Explanation
Option E correctly combines both requirements: using a unique constraint on employee_id paired with COPY INTO ... ON_ERROR = 'CONTINUE', which skips rows that violate the constraint without aborting the entire load, and defining the DEPARTMENTS table's surrogate key column with AUTOINCREMENT (or IDENTITY) so keys are generated automatically when rows are inserted later.
Why the distractors fail:
- A is likely wrong because it either uses the wrong
ON_ERRORvalue (e.g.,ABORT_STATEMENT, the default, which halts the entire load on error) or omitsAUTOINCREMENTonDEPARTMENTS. - B/C likely use
PRIMARY KEYinstead of a unique constraint - while Snowflake's primary keys are informational (not enforced), the combination with theON_ERRORhandling or the DEPARTMENTS surrogate key setup is likely incomplete or incorrect. - D drops the key constraint entirely, meaning duplicate
employee_idvalues would load silently with no rejection at all - the opposite of the requirement.
Memory tip: In Snowflake, think "CONTINUE to skip, AUTOINCREMENT to grow" - ON_ERROR = 'CONTINUE' skips bad rows without stopping the load, and AUTOINCREMENT handles surrogate key generation automatically. Also remember: Snowflake constraints (PRIMARY KEY, UNIQUE) are informational only - they don't enforce uniqueness at write time, but they still interact with ON_ERROR behavior during COPY INTO to flag violations.
Topics
Community Discussion
No community discussion yet for this question.