Create Empty Athena Table from Existing Schema

Transform and process data.

Answer Correct answer: D — Use CREATE TABLE new_table AS (SELECT * FROM old_table) WITH NO DATA to create the table structure without copying data.

A data engineer needs to create a new empty table in Amazon Athena that has the same schema as an existing table named old_table. Which SQL statement should the data engineer use to meet this requirement?

CREATE TABLE new_table AS SELECT * FROM old_tables;
INSERT INTO new_table SELECT * FROM old_table;
CREATE TABLE new_table (LIKE old_table);
CREATE TABLE new_table AS (SELECT * FROM old_table) WITH NO DATA; Correct Answer

Community Votes

100%

100% of anonymous learners picked answer D. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

The question tests knowledge of Athena's CTAS syntax for schema replication without data copying, specifically requiring the 'WITH NO DATA' modifier.

Learn how to create a new empty table in Amazon Athena with the same schema as an existing table using the CREATE TABLE AS SELECT (CTAS) statement combined with the WITH NO DATA clause.

Many users select option A because they confuse creating a table with populating it; standard CTAS creates the table and inserts data simultaneously unless explicitly prevented.

Community Discussion (4 comments)

AgboolaKun 👍 2 Selected: D

D is the correct answer. Here is why: The AS clause allows you to define the new table's schema based on a SELECT statement. The WITH NO DATA clause at the end explicitly tells Athena to create the table structure without copying any data. For more information, see the "Creating an empty copy of an existing table" section in this documentation - https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.html

Eleftheriia 👍 1 Selected: D

with no data, creates a new table with the same schema as the old one. https://docs.aws.amazon.com/athena/latest/ug/create-table-as.html

pikuantne 👍 1 Selected: D

D is correct

Parandhaman_Margan 👍 1

Answer: D

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

Option D is correct because Amazon Athena supports the CREATE TABLE... AS SELECT... WITH NO DATA syntax. This allows you to define the schema of a new table based on the result set of a query (in this case, selecting all columns from old_table) but prevents the actual data transfer. This is the standard SQL approach supported by Athena for creating structural copies.

Why the Other Options Are Wrong

Option A (CREATE TABLE new_table AS SELECT * FROM old_tables) creates the table and immediately populates it with data from old_tables. Additionally, it contains a typo (old_tables vs old_table). Option B attempts to insert into a table that does not yet exist, which will fail. Option C uses (LIKE old_table), which is valid syntax in PostgreSQL or Oracle but is not supported in Athena's dialect for copying schemas directly in this manner.

Community Comment Notes

The community consensus strongly favors Option D. As AgboolaKun noted, "The AS clause allows you to define the new table's schema... The WITH NO DATA clause at the end explicitly tells Athena to create the table structure without copying any data." Eleftheriia also confirmed this by referencing the official AWS documentation on create-table-as.html.

Official Reference

https://docs.aws.amazon.com/athena/latest/ug/create-table-as.html

Exam Strategy

When asked to replicate a schema without data in Athena, look for the WITH NO DATA clause appended to a CTAS statement. Remember that standard CTAS always includes data insertion unless modified.

Frequently Asked Questions

Why is option C incorrect for Athena?

Athena does not support the LIKE clause syntax for copying table schemas directly within the CREATE TABLE statement like some other RDBMS engines do.

Can I use INSERT INTO to create a table schema?

No, INSERT INTO requires the target table to already exist. You must use CREATE TABLE first.

More DEA-C01 FAQ →

Related Analysis

Practice All DEA-C01 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DEA-C01 Practice Test →

← Back to DEA-C01 Study Guide