🧱Calculated Columns

NEW

Also known as: Computed Columns, Virtual Column

Columns whose values are derived from expressions over other columns rather than directly stored inputs.

DESCRIPTION

Calculated columns derive values using expressions at model or database level. In relational databases they are often called computed or generated columns and may be virtual or persisted for performance. In BI semantic models, calculated columns typically compute during data refresh and store results in the model. They are useful for categorization, normalization, and join keys but increase storage. Choosing between calculated columns and measures depends on whether values should be stored and evaluated row-by-row or dynamically at query time.

ALTERNATIVE DEFINITIONS

Microsoft SQL Server Documentation

A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED.

Clarifies virtual vs persisted storage in SQL Server.

Source

PostgreSQL Documentation

A generated column is a special column that is computed from other columns.

Defines generated columns in PostgreSQL succinctly.

Source

RELATED TERMS

Generated Columns Materialized Columns DAX View Triggers Indexing Model Data Star Schema Extract-Transform-Load

COMPETING TERMS

Measure

KNOWLEDGE AREAS

Data Modeling Data Warehousing & BI Data Storage & Operations Software Engineering

TARGET AUDIENCE

Data Engineer Analytics Engineer Data Modeler Data Analyst Software Developer

EXTERNAL SOURCES

• Wikipedia - Generated column
• Microsoft - SQL Server computed columns reference
• PostgreSQL - Generated columns in PostgreSQL
• MySQL - MySQL generated columns syntax

TOP QUESTIONS

When should Calculated Columns be used instead of Measures?

Use calculated columns for row-level computations needed for relationships, grouping, or indexing. Measures are better for dynamic aggregations evaluated at query time.

Category: Practice

Do Calculated Columns impact storage?

Yes. Persisted or model-level calculated columns consume storage, which can affect model size and refresh times.

Category: Performance

Can Calculated Columns be indexed?

In databases, persisted computed or generated columns can often be indexed to improve query performance.

Category: Performance

What is a common mistake with Calculated Columns?

Encoding business logic that belongs in measures or ETL leads to duplication and maintenance complexity.

Category: Mistakes

How do Calculated Columns behave in BI models?

They compute during data refresh and store values in the model, unlike measures which compute at query time.

Category: Concept

What’s the risk of overusing Calculated Columns?

Model bloat, longer refresh times, and harder maintenance result when too many derived columns are stored.

Category: Risks

Can Calculated Columns reference other calculated columns?

Yes, but chains can complicate lineage and refresh performance. Keep dependencies clear and minimal.

Category: Requirements

Are Calculated Columns deterministic?

They should be deterministic for indexing and consistency; avoid non-deterministic functions like GETDATE() unless use case demands.

Category: Concept

How do Calculated Columns differ from Views?

Views compute at query time, while persisted calculated columns precompute and store values, trading storage for speed.

Category: Alternatives