Create Empty Table Copy in Amazon Athena

DEA-C01 By islever Editorial Last updated

Orchestrate data pipelines.

Short answer

B Use CREATE TABLE AS SELECT ... WHERE FALSE to create an empty table structure without copying data.

Question

A data engineer needs to create an empty copy of an existing table in Amazon Athena to perform data processing tasks. The existing table in Athena contains 1,000 rows. Which query will meet this requirement?
  1. CREATE TABLE new_table -
  2. CREATE TABLE new_table - Correct Answer
  3. CREATE TABLE new_table -
  4. CREATE TABLE new_table -

Key Takeaways

To create an empty table structure without copying data, use 'CREATE TABLE new_table AS SELECT * FROM old_table WHERE FALSE'.

This question tests the correct SQL syntax for creating an empty copy of an existing table in Amazon Athena using a CREATE TABLE AS SELECT (CTAS) statement with a false condition.

Common mistake: Learners often choose options that include a WHERE clause filtering specific data or omitting the WHERE clause entirely, which results in copying all rows instead of an empty structure.

Expert Analysis

Why the Answer Is Correct

The requirement is to create an empty copy of an existing table's schema. The correct approach is to use a CTAS (Create Table As Select) statement combined with a condition that always evaluates to false, such as WHERE FALSE or WHERE 1=0. This ensures the table structure is created based on the source table, but no rows are inserted because the query returns zero results.

Why the Other Options Are Wrong

Options that copy data (e.g., without a restrictive WHERE clause) would populate the new table with the 1,000 rows, violating the "empty" requirement. Options that do not use CTAS might fail to replicate the schema correctly or require separate DDL steps. Any option missing the SELECT * or using a non-false condition will result in data duplication.

Community Comment Notes

The community consensus strongly supports answer B. Users noted that the key to this question is identifying the query that creates the table structure without ingesting data. One user referenced AWS documentation confirming that CTAS with a false predicate is the standard method for this task.

Official Reference

https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.html#ctas-example-empty-table

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

Exam Strategy

When asked to create a table schema without data in Athena, look for a CTAS statement paired with a WHERE FALSE or WHERE 1=0 clause. This is a common pattern for partitioning strategies or pre-creating tables for future loads.

Frequently Asked Questions

Why not just CREATE TABLE like normal SQL?

In Athena, you typically need to define the schema explicitly if not using CTAS. Using CTAS with a false condition is a concise way to replicate the exact schema of an existing table instantly.

Does WHERE 1=0 work the same as WHERE FALSE?

Yes, both evaluate to false for every row, resulting in zero rows being selected. Both are valid methods to create an empty table copy via CTAS.

More DEA-C01 FAQ →

Community Votes

B
100%

100% of anonymous learners picked answer B. 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

  1. truongnguyen86 👍 3 Answered B
    should be B with no data option to create empty table from CTAS
  2. 2022MMTT 👍 2
    B https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.html#ctas-example-empty-table
  3. pikuantne 👍 2 Answered B
    Definitely B
  4. Parandhaman_Margan 👍 1
    Answer: B

Reader comments & corrections

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

Log in to comment, report an error, or add a note about this question.

Amazon Redshift Compound Sort Key Query Optimization

S-grade

Secure Redshift Spectrum Access with VPC Endpoints

S-grade

Athena Federated Query for Multi-Source Iceberg Pipelines

S-grade

Reclaiming Storage in Amazon Redshift Materialized Views

S-grade

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