Databricks Data Engineer Associate Question 311
Single answerA data engineering team is tasked with creating a new table from an existing one while simultaneously applying a filter and specific transformations on the data. The team would like to ensure that the new table is created in an optimized and efficient manner without creating intermediate dataframes. Which of the following approaches should the team use?
- A
Use the CREATE TABLE AS SELECT (CTAS) statement to define and populate the new table in one step.
- B
Write the transformations into a temporary view, then use INSERT INTO to populate a pre-existing table.
- C
Use a SELECT statement to query the existing table and store the results in a new dataframe, then write it to a table.
- D
Create an empty table first with the required schema and then use a loop to insert rows after applying transformations.
Show answer and explanation
Correct answer: A
Explanation
The CREATE TABLE AS SELECT (CTAS) statement is a powerful and efficient way to create a new table by directly selecting, filtering, and transforming data from an existing table in one step. This avoids intermediate steps, reduces processing overhead, and ensures optimal performance in a distributed environment like Databricks.
- A. Correct.
This option is correct because CTAS allows the team to create a new table and populate it simultaneously, applying filtering and transformations efficiently in a single SQL operation.
- B. Incorrect.
This option is incorrect because using a temporary view and INSERT INTO requires multiple steps and is less efficient compared to CTAS.
- C. Incorrect.
This option is incorrect because it involves creating intermediate dataframes, which is less optimized and requires additional operations to save the data.
- D. Incorrect.
This option is incorrect because manually creating an empty table and inserting rows is inefficient and does not leverage the power of SQL or distributed processing in Databricks.