Databricks Data Engineer Associate Question 308
Single answerA data engineering team is tasked with creating a new table in Databricks that contains aggregated sales data from a large transactional Delta table. The team wants to ensure the new table is created efficiently and includes the schema and data from the result of their aggregation query. Which approach should they use?
- A
Use a CTAS (CREATE TABLE AS SELECT) statement to create the new table.
- B
Export the query results to an external file and load it into a new table.
- C
Manually create an empty table with the desired schema and then insert the query results into it.
- D
Schedule a Databricks job to run the aggregation query and append the results to an existing table.
Show answer and explanation
Correct answer: A
Explanation
CTAS (CREATE TABLE AS SELECT) is a widely used method in Databricks for creating a new table based on the result of a SELECT query. It simplifies the process by combining table creation and data insertion into a single step, making it the most efficient solution for this scenario. Other approaches either add unnecessary steps, are more error-prone, or do not meet the requirements of creating a new table with both schema and data.
- A. Correct.
This is correct. CTAS (CREATE TABLE AS SELECT) is a concise and efficient way to create a new table with both the schema and data directly from a query result.
- B. Incorrect.
This is incorrect. Exporting the query results to an external file and reloading it is inefficient and introduces unnecessary complexity compared to a CTAS statement.
- C. Incorrect.
This is incorrect. Manually creating the table and inserting the results is more error-prone and less efficient than using a CTAS statement.
- D. Incorrect.
This is incorrect. Scheduling a job to append data assumes an existing table and does not address the need to create a new table with the schema and data in one step.