Databricks Data Engineer Associate Question 312
Single answerA Databricks engineer is tasked with creating a new table in a Delta Lake to store aggregated sales data. The table should be created directly from the results of a complex SQL query that calculates total sales per region and includes a filter for sales above a certain threshold. The engineer needs to ensure the table is created efficiently and includes the query results. Which approach should the engineer use?
- A
Use a CREATE TABLE statement followed by an INSERT INTO statement to populate the table with the query results.
- B
Use a CREATE TABLE AS SELECT (CTAS) statement to create the new table directly from the query results.
- C
Use a CREATE OR REPLACE TABLE statement with the query results.
- D
Use a CREATE VIEW statement to create a logical view of the query results instead of a table.
Show answer and explanation
Correct answer: B
Explanation
The CREATE TABLE AS SELECT (CTAS) statement is the most appropriate solution for creating a new table that directly includes the results of a query. It combines the creation of the table and the data population into a single step, improving efficiency and simplifying the process. Other options either require additional steps or do not fulfill the requirement of creating a new physical table.
- A. Incorrect.
While this approach works, it requires two separate operations: creating the table and then inserting the data. This is less efficient and introduces additional complexity compared to using CTAS.
- B. Correct.
CTAS (CREATE TABLE AS SELECT) is the most efficient solution for this scenario. It allows the engineer to create the table and populate it with the query results in a single operation.
- C. Incorrect.
CREATE OR REPLACE TABLE is used to create or replace a table, but it does not inherently populate the table directly from a query. Additional steps would be needed to insert data.
- D. Incorrect.
A CREATE VIEW statement creates a logical view rather than a physical table. It does not store the results as a new table, which makes it unsuitable for the given requirements.