Databricks Data Engineer Associate Question 310
Single answerA data engineering team is building an ETL pipeline in Databricks to create a new table that consolidates customer data from multiple source tables. The team wants to: 1) Create the table in one step, 2) Define the schema dynamically based on the SELECT query, and 3) Ensure the table is written in a specific location in the data lake in Delta format. Which approach should the team use?
- A
Use a CREATE TABLE statement with a predefined schema and an INSERT INTO statement to load data.
- B
Use a CREATE TABLE AS SELECT (CTAS) statement with a SELECT query and specify the Delta location.
- C
Use a SELECT query combined with a DataFrame API to write data to the Delta table.
- D
Use an INSERT INTO statement along with a pre-created Delta table.
Show answer and explanation
Correct answer: B
Explanation
CREATE TABLE AS SELECT (CTAS) is the appropriate solution when you want to create a new table in one step, define the schema dynamically based on the result of a SELECT query, and store the table in a specific location in the data lake in Delta format. This method simplifies the process and meets all the requirements outlined in the scenario.
- A. Incorrect.
This approach requires two separate steps: creating the table with a predefined schema and then loading data using INSERT INTO. It does not dynamically create the schema based on the SELECT query in a single step, so it does not meet all the requirements.
- B. Correct.
This is the correct approach. CREATE TABLE AS SELECT (CTAS) allows the team to dynamically define the schema based on the SELECT query, create the table in one step, and specify the Delta location for the table.
- C. Incorrect.
While the DataFrame API can be used to write data to a Delta table, it does not involve a single SQL statement to create the table and define the schema dynamically. This does not fulfill the requirements for a CTAS solution.
- D. Incorrect.
INSERT INTO requires a table to be pre-created, so it does not allow for creating the table and defining the schema dynamically in one step as CTAS does.