How to create a Delta table with calculated columns in a Lakehouse

Creating persisted calculated columns in a Delta table in the Lakehouse improves performance and makes repeated queries easier. This guide shows how to create a Delta table with calculated columns (transformed and stored data) using SQL and PySpark, explaining why and presenting a practical example.

Prerequisites

• Account with access to Microsoft Fabric and a configured Lakehouse.
• Access to the SQL analytics endpoint or a notebook with PySpark.
• Permissions to create tables and write files in the Lakehouse.

Step 1: Understand why to create persisted calculated columns

Calculated columns can be derived expressions (e.g., formatted dates, composite keys, flags). If you calculate those columns in the query, you pay CPU cost repeatedly. Persisting them in a Delta table reduces read cost and speeds up queries, especially in reports and Power BI models.

Step 2: Plan the calculated columns

Decide which fields to derive and whether you will need to update those columns in the future (e.g., raw base + transformation). Common examples: data_particao, is_high_value, customer_region. Also plan partitioning and data types.

Step 3: Create a Delta table from raw data (SQL)

If you have a CSV or raw table, create a Delta table and add calculated columns in the SELECT. Here is an example using SQL at the SQL analytics endpoint.

CREATE OR REPLACE TABLE lakehouse.catalog.schema.sales_delta AS SELECT   sale_id,   customer_id,   amount,   sale_date,   CAST(date_trunc('day', sale_date) AS date) AS sale_day,   CASE WHEN amount > 1000 THEN true ELSE false END AS is_high_value,   concat(customer_id, '-', year(sale_date)) AS customer_year_key FROM lakehouse.catalog.schema.raw_sales;

Step 4: Create/update calculated columns with PySpark

In a PySpark notebook you can read the source, compute columns and write as Delta. Useful for more complex transformations or programmatic pipelines.

from pyspark.sql import functions as F

raw = spark.read.format("delta").table("lakehouse.catalog.schema.raw_sales")

transformed = raw.withColumn("sale_day", F.to_date(F.trunc("sale_date", "DAY"))) \
                  .withColumn("is_high_value", F.col("amount") > 1000) \
                  .withColumn("customer_year_key", F.concat(F.col("customer_id"), F.lit("-"), F.year(F.col("sale_date"))))

transformed.write.format("delta").mode("overwrite").saveAsTable("lakehouse.catalog.schema.sales_delta")

Step 5: Update calculated columns for new data (MERGE)

When new ingestion arrives, update the Delta table to compute the persisted columns using MERGE (upsert). This keeps the columns synchronized.

MERGE INTO lakehouse.catalog.schema.sales_delta AS target
USING (SELECT * FROM lakehouse.catalog.schema.raw_sales_stage) AS src
ON target.sale_id = src.sale_id
WHEN MATCHED THEN UPDATE SET
  target.amount = src.amount,
  target.sale_date = src.sale_date,
  target.sale_day = CAST(date_trunc('day', src.sale_date) AS date),
  target.is_high_value = CASE WHEN src.amount > 1000 THEN true ELSE false END,
  target.customer_year_key = concat(src.customer_id, '-', year(src.sale_date))
WHEN NOT MATCHED THEN INSERT *;

Step 6: Best practices and common pitfalls

Use appropriate data types (date, boolean) to reduce space. Avoid recalculating columns already persisted in queries. If the calculation logic changes, perform a controlled migration: create a new column, populate it, validate and then remove the old one. Common mistake: partitioning by many small calculated columns — this creates many small files.

Verify the result

Confirm that the columns exist and contain the expected values with simple queries. Also check the execution plan to observe improvement in queries that previously used the expression at runtime.

SELECT sale_id, amount, sale_date, sale_day, is_high_value, customer_year_key
FROM lakehouse.catalog.schema.sales_delta
LIMIT 20;

To validate performance, compare the query time before (without persisted columns) and after (with columns). Use EXPLAIN to see if the expression was removed from runtime.

Conclusion

Persisting calculated columns in Delta tables in the Lakehouse reduces cost and speeds up queries, especially for recurring reports and ETL. Next steps: automate the update with pipelines (Delta Live Tables or an orchestrator) and monitor storage and performance. Tip: start by persisting only the most expensive-to-compute columns — which is the first column you will persist in your table?