CERTIFIED-DATA-ANALYST-ASSOCIATE Question #6: Real Exam Question with Answer & Explanation

Question

A data analysis team is working with the table_bronze SQL table as a source for one of its most complex projects. A stakeholder of the project notices that some of the downstream data is duplicative. The analysis team identifies table_bronze as the source of the duplication. Which of the following queries can be used to deduplicate the data from table_bronze and write it to a new table table_silver?

Options

• ACREATE TABLE table_silver AS
• CCREATE TABLE table_silver AS
• DINSERT INTO TABLE table_silver
• EINSERT OVERWRITE TABLE table_silver

Explanation

Option A uses CREATE TABLE table_silver AS SELECT DISTINCT * FROM table_bronze, which both creates the new table and deduplicates the source data in a single atomic operation - exactly what's needed when the target table doesn't yet exist and the data must be clean from the start.

Option C uses the same CREATE TABLE ... AS structure but omits DISTINCT, so it copies all rows including duplicates, solving nothing. Options D and E use INSERT INTO and INSERT OVERWRITE respectively, which both require table_silver to already exist - since the goal is writing to a new table, these will fail or are semantically wrong for the scenario. INSERT INTO also appends rather than replaces, which would multiply duplicates on repeated runs.

Memory tip: Think "Create = birth, Insert = delivery." When you're creating a brand-new table from deduplicated data, reach for CREATE TABLE ... AS SELECT DISTINCT - it handles both the table creation and the deduplication in one step, with no preconditions.

No community discussion yet for this question.