Databricks-Spark-Assoc Using Spark SQL Practice Question
A developer needs to add a computed column `discounted_price` equal to `price * 0.9` to an existing Delta table `products` and persist the change so all future queries see the new column. The table already contains data. Which statement should the developer run?
⚠ Common exam trap
The trap here is assuming that adding a plain column automatically populates it from an expression, when a bare `ADD COLUMNS` leaves existing rows null and requires a generated-column clause to derive values.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
`ALTER TABLE products ADD COLUMNS (discounted_price DOUBLE GENERATED ALWAYS AS (price * 0.9))`
Adding a persisted computed column to a Delta table is done with `ALTER TABLE ... ADD COLUMNS` using a `GENERATED ALWAYS AS` expression. Spark computes the value from the base column for existing and future rows, so queries see a populated `discounted_price` without a manual update. Statements that assume the column already exists or recreate the table either fail or risk data integrity.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
`ALTER TABLE products ADD COLUMNS (discounted_price DOUBLE)`
Why it's wrong here
`ADD COLUMNS` appends the column to the schema as null for existing rows; it does not compute or backfill values from `price`. Future queries would see the new column but populated with nulls, not the discounted price. The developer would still need an `UPDATE` to fill values, so this alone does not satisfy the requirement.
- ✗
`UPDATE products SET discounted_price = price * 0.9`
Why it's wrong here
`UPDATE` modifies row values but requires the column to already exist in the schema. Since `discounted_price` is not yet defined, this statement fails with an unresolved-column error. The developer must first add the column, and a generated column removes the need for a separate update entirely.
- ✓
`ALTER TABLE products ADD COLUMNS (discounted_price DOUBLE GENERATED ALWAYS AS (price * 0.9))`
Why this is correct
A generated column defined with `GENERATED ALWAYS AS` is computed automatically from the expression whenever rows are written, and adding it to a Delta table backfills values for existing rows. This persists the derived value in the table so all future queries see `discounted_price` populated. It is the declarative way to express the computed column in Spark SQL on Delta.
- ✗
`CREATE OR REPLACE TABLE products AS SELECT *, price * 0.9 AS discounted_price FROM products`
Why it's wrong here
While this rewrites the table with the new column, reading and overwriting the same table in one statement is risky and can fail or produce inconsistent results on Delta because the source is being replaced. It also discards table properties, constraints, and history semantics. The safe declarative approach is to alter the schema rather than recreate the table.
About these practice questions
Courseiva writes every Databricks-Spark-Assoc question from scratch — 295 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Databricks exam blueprint
This Databricks-Spark-Assoc practice question is part of Courseiva's free Databricks certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the Databricks-Spark-Assoc exam.