Databricks Data Engineer Associate Question 254
Single answerA data engineer is tasked with creating a managed Delta table in Databricks to store customer transactions. The data source is located in a Delta format in an external cloud storage location. The engineer wants to ensure that the table is fully managed by Databricks. Which of the following SQL commands should they use to create the managed table?
- A
CREATE TABLE customer_transactions USING DELTA LOCATION '/mnt/external_storage/customer_data';
- B
CREATE TABLE customer_transactions AS SELECT * FROM delta.
/mnt/external_storage/customer_data; - C
CREATE OR REPLACE TABLE customer_transactions USING DELTA LOCATION '/mnt/external_storage/customer_data';
- D
CREATE TABLE customer_transactions (id INT, amount DOUBLE) USING DELTA;
Show answer and explanation
Correct answer: D
Explanation
To create a managed table in Databricks, the table must be stored in the Databricks-managed storage. This is achieved by not specifying the LOCATION clause in the CREATE TABLE statement. The correct command is the one that creates the table schema explicitly without referencing external data or a specific storage location.
- A. Incorrect.
This command creates an unmanaged table because it explicitly specifies the LOCATION of the data. The table will not be managed by Databricks.
- B. Incorrect.
This command creates a managed table, but the data is directly read from the external location and stored in the default Databricks-managed location. However, the question specifies that the table should be created from scratch, not using existing external data.
- C. Incorrect.
This command attempts to create a table but specifies an external LOCATION, which makes it unmanaged. Managed tables do not allow specifying external locations.
- D. Correct.
This command creates a fully managed Delta table in Databricks, as it does not specify any external location and uses the default Databricks-managed storage for the table.