Databricks Data Engineer Associate Question 315
Single answerYou are tasked with creating a Delta table in Databricks to store customer orders. The table should include a 'total_cost' column, which is automatically calculated by multiplying the 'quantity' and 'unit_price' columns. Which of the following options correctly defines the 'total_cost' column as a generated column in the Delta table creation?
- A
CREATE TABLE customer_orders (order_id INT, quantity INT, unit_price DOUBLE, total_cost DOUBLE GENERATED ALWAYS AS (quantity * unit_price)) USING DELTA;
- B
CREATE TABLE customer_orders (order_id INT, quantity INT, unit_price DOUBLE, total_cost DOUBLE AS (quantity * unit_price)) USING DELTA;
- C
CREATE TABLE customer_orders (order_id INT, quantity INT, unit_price DOUBLE, total_cost DOUBLE GENERATED ALWAYS AS (quantity * unit_price) STORED) USING DELTA;
- D
CREATE TABLE customer_orders (order_id INT, quantity INT, unit_price DOUBLE, total_cost DOUBLE GENERATED BY DEFAULT AS (quantity * unit_price)) USING DELTA;
Show answer and explanation
Correct answer: C
Explanation
Delta tables in Databricks support the use of generated columns, which are computed automatically based on other columns' values. The correct syntax for defining a generated column includes the 'GENERATED ALWAYS AS' clause followed by the expression and the 'STORED' keyword to store the computed values. Option 3 correctly implements this syntax, while the other options either use incorrect syntax or omit required keywords.
- A. Incorrect.
This option is incorrect because it does not include the required 'STORED' keyword for generated columns in Delta tables.
- B. Incorrect.
This option is incorrect because it omits the 'GENERATED ALWAYS' syntax required for defining a generated column in Delta tables.
- C. Correct.
This is the correct option as it properly defines the 'total_cost' column as a generated column using the 'GENERATED ALWAYS AS' syntax and includes the 'STORED' keyword, which is mandatory in Delta tables.
- D. Incorrect.
This option is incorrect because 'GENERATED BY DEFAULT' is not valid syntax for generated columns in Databricks Delta tables.