Databricks Data Engineer Associate Question 309
Single answerA data engineering team is tasked with creating a new table in Databricks from the results of a complex query. The table must store the data physically and should be immediately available for querying by other users. Which approach should the team use to achieve this?
- A
Use a CREATE TABLE AS SELECT (CTAS) statement to create and populate the table.
- B
Use a VIEW to save the query output and share it with other users.
- C
Use an INSERT INTO statement to populate an existing table with the query results.
- D
Use a temporary table to store the query output for other users.
Show answer and explanation
Correct answer: A
Explanation
CREATE TABLE AS SELECT (CTAS) is the appropriate solution for creating a new table from the results of a query while also physically storing the data. It ensures that the table is immediately available for querying by other users, unlike temporary tables or views, which either have session limitations or do not store data physically. INSERT INTO does not meet the requirement of creating a new table.
- A. Correct.
This is the correct choice. CTAS (CREATE TABLE AS SELECT) creates a new table, physically stores the results of the query, and makes it immediately available for querying.
- B. Incorrect.
This is incorrect because a VIEW does not store data physically. It only stores the SQL logic, and querying the VIEW re-executes the underlying query.
- C. Incorrect.
This is incorrect because INSERT INTO populates an existing table, but the scenario specifies creating a new table.
- D. Incorrect.
This is incorrect because a temporary table is session-specific and not accessible to other users.