Databricks Data Engineer Associate Question 316
Single answerYou are tasked with creating a Delta table to store sales data in Databricks. The table must include a generated column that calculates the total price for each sale by multiplying the 'quantity' column with the 'unit_price' column. Which of the following SQL statements correctly accomplishes this?
- A
CREATE TABLE sales (id INT, quantity INT, unit_price DOUBLE, total_price DOUBLE GENERATED ALWAYS AS (quantity * unit_price)) USING DELTA;
- B
CREATE TABLE sales (id INT, quantity INT, unit_price DOUBLE, total_price DOUBLE AS (quantity * unit_price)) USING DELTA;
- C
CREATE TABLE sales (id INT, quantity INT, unit_price DOUBLE, total_price DOUBLE GENERATED ALWAYS AS (quantity * unit_price) STORED) USING DELTA;
- D
CREATE TABLE sales (id INT, quantity INT, unit_price DOUBLE, total_price DOUBLE GENERATED BY (quantity * unit_price)) USING DELTA;
Show answer and explanation
Correct answer: C
Explanation
In Databricks, when creating a Delta table with a generated column, the correct syntax is to use the 'GENERATED ALWAYS AS' clause followed by the expression for the column. The 'STORED' keyword is mandatory to ensure the generated column is materialized and persisted in the table. Option 3 correctly follows this syntax, making it the correct choice.
- A. Incorrect.
This syntax is incorrect because the 'STORED' keyword is missing, which is required for Delta tables with generated columns in Databricks.
- B. Incorrect.
This syntax is incorrect because the 'GENERATED ALWAYS' clause is missing, which is required for creating generated columns in Delta tables.
- C. Correct.
This is the correct syntax for creating a Delta table with a generated column in Databricks. The 'GENERATED ALWAYS AS' clause defines the generated column, and the 'STORED' keyword ensures the column is materialized in the table.
- D. Incorrect.
This syntax is incorrect because 'GENERATED BY' is not a valid clause for defining generated columns in Databricks.