Create Empty Athena Table from Existing Schema

DEA-C01 By islever Editorial Last updated Oct 12, 2026

Transform and process data.

Short answer

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

Question

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

Key Takeaways

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.

Common mistake: 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.

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

This link opens the official vendor documentation in a new tab.

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 →

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.

Discussion 4 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 4

AgboolaKun 👍 2 Answered 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 Answered 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 Answered D

D is correct
Parandhaman_Margan 👍 1

Answer: D

Reader comments & corrections

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

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