Athena INSERT INTO SELECT for Data Subset
A data engineer needs to create an Amazon Athena table based on a subset of data from an existing Athena table named cities_world. The cities_world table contains cities that are located around the world. The data engineer must create a new table named cities_us to contain only the cities from cities_world that are located in the US. Which SQL statement should the data engineer use to meet this requirement?
Community Votes
100% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
The core trap is confusing standard SQL INSERT syntax with non-existent commands like MOVE or UPDATE, and ensuring column lists match when inserting into specific columns.
This question tests the correct SQL syntax for copying a subset of data from an existing Amazon Athena table to a new or existing target table using the INSERT INTO ... SELECT statement.
Learners often select Option C because it looks simpler, but they fail to realize that explicitly listing columns in the INSERT clause (Option A) is safer and more precise, especially if the target table has more columns than the source selection.
Community Discussion (5 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Option A uses the standard SQLINSERT INTO <table> (<columns>) SELECT <columns> FROM <source> WHERE <condition> syntax. This correctly specifies the target table (cities_usa), the columns to populate (city, state), and filters the source data (cities_world) to include only rows where country='usa'. This is the robust way to insert a subset of data.Why the Other Options Are Wrong
Option B (MOVE) is not a valid SQL command in Athena or standard SQL for this purpose. Option C is syntactically valid but less precise; if cities_usa has additional columns beyond city and state, omitting the column list might cause errors or unexpected behavior depending on schema definitions. Option D (UPDATE) modifies existing rows rather than inserting new ones, which contradicts the requirement to 'create' a table with the subset (or populate it).Community Comment Notes
The community overwhelmingly voted for A. One user noted that while the prompt says 'create a new table', none of the options actually contain aCREATE TABLE statement, implying the table already exists or the question focuses purely on the insertion logic. Another comment highlighted that specifying the column list in Option A is best practice to avoid failures if the target table schema differs slightly from the source selection. Exam Strategy
Always look for explicit column lists in INSERT INTO statements when the target schema might have more columns than the selected source data. It prevents ambiguity and errors.
Frequently Asked Questions
Why is CREATE TABLE AS SELECT not an option?
The question asks to create a table based on a subset, but all options use INSERT. This implies the table structure is already defined, and we are populating it.
Is Option C also correct?
Option C is valid SQL but less safe. Explicitly listing columns (Option A) ensures data goes to the right place even if the target table has extra columns.
Related Analysis
Practice All DEA-C01 Questions
Access 100 questions with complete answers and detailed explanations.
View Full DEA-C01 Practice Test →