Databricks Data Engineer Associate Question 313
Single answerYou are working with a Delta table named sales_data in Databricks. The table contains the columns product_id, price, and quantity_sold. You are tasked to add a new column total_revenue that automatically calculates the product of price and quantity_sold whenever a new row is inserted or updated. Which of the following commands will correctly create a generated column for total_revenue in the Delta table?
- A
ALTER TABLE sales_data ADD COLUMN total_revenue DOUBLE GENERATED ALWAYS AS (price * quantity_sold)
- B
ALTER TABLE sales_data ADD COLUMN total_revenue DOUBLE DEFAULT (price * quantity_sold)
- C
ALTER TABLE sales_data ADD COLUMN total_revenue DOUBLE GENERATED ALWAYS AS (price * quantity_sold) STORED
- D
ALTER TABLE sales_data ADD GENERATED COLUMN total_revenue DOUBLE AS (price * quantity_sold)
Show answer and explanation
Correct answer: C
Explanation
In Delta tables, generated columns are created using the syntax ADD COLUMN <column_name> <data_type> GENERATED ALWAYS AS (<expression>) STORED. This ensures the column is automatically calculated based on the specified expression and persisted in the table. The correct answer includes the required GENERATED ALWAYS AS clause and the STORED keyword, making it the valid option.
- A. Incorrect.
This is close, but it is missing the required
STOREDkeyword, which is necessary for defining a generated column in a Delta table. - B. Incorrect.
This is invalid because
DEFAULTis not used to create a generated column.DEFAULTis used to provide a fallback value, not for dynamic calculations. - C. Correct.
This is the correct syntax for adding a generated column in a Delta table. The
GENERATED ALWAYS ASclause defines the expression, and theSTOREDkeyword ensures the column is materialized and persisted. - D. Incorrect.
This is invalid syntax because the
ADD GENERATED COLUMNclause is not supported in Delta tables.