Virtual columns are one of the small, but powerful features available in traditional relational database management system (RDBMS) platforms, and now they are generally available in Snowflake® too. They bring expressive flexibility directly at the table level. This removes the need for extra complexity when simple business rules don't justify the architectural overhead: a tiny yet massive upgrade for developer experience.
Simple problems, not so simple solutions
There is a clear shift in data engineering, where business logic is moved as close to the raw data as possible rather than being scattered across a dozen downstream BI dashboards. The term 'data gravity,' introduced in 2010, described this concept, and it is no coincidence that Snowflake started its journey just a couple of years later. Data teams are handling richer, more complex nested data structures than ever before, but the appetite for maintaining labyrinthine data pipelines is rapidly shrinking. Developers and analysts want self-serve, agile data access, but they need it without sacrificing schema hygiene. Keep it super simple. That's the way to go, both for humans and AI agents.
As with many ideas, even this one is easier said than done. In fact, historically, if a data engineer needed to expose a standardized profit_margin or extract a simple zip code from a deeply nested JavaScript Object Notation (JSON) payload, they had two choices. They could create a dedicated view on top of the table, which could mean that data pipelines must be updated to reflect using the view instead of the base table, and maybe even redeployed, or they could run an expensive, time-consuming UPDATE or INSERT OVERWRITE to backfill millions of rows, burning compute credits and running for hours just to provide to the upstream users a common, consistent, simple calculation.
Bringing logic directly to the data
To solve this, Snowflake introduced support for Virtual Columns: a way to attach business logic directly to a table without actually materializing the data and without the need to create a view just for something that simple.
Virtual Columns are expressions evaluated dynamically at query time:
-- Extracting a field from JSON without creating a View
ALTER TABLE user_events
ADD COLUMN user_zip STRING AS (TRY_CAST(raw_payload:"user"."address"."zip" AS VARCHAR));
-- Adding a business metric instantly
ALTER TABLE sales
ADD COLUMN profit_margin NUMBER(16,2) AS ((revenue - cost) / revenue);Since no data movement is involved, creating virtual columns happens almost instantly, and once defined, they can be immediately used. If a virtual column is not referenced in a query, the expression is not calculated, adding no overhead to that query.
Identifying if a table has virtual columns is very easy, as the INFORMATION_SCHEMA.COLUMNS view returns the expression used to create the virtual column.
The following example:
SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, EXPRESSION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tires'
AND "KIND" = 'VIRTUAL_COLUMN'
ORDER BY ORDINAL_POSITION;Returns the following result:
Why do Snowflake Virtual Columns matter?
We see three primary advantages:
- Performance and cost: Since Snowflake Virtual Columns are computed on demand and avoid data materialization, they reduce or eliminate the need for costly compute resources typically spent on massive table backfills.
- Developer experience: By leveraging Snowflake Virtual Columns, our developers avoid the burden of maintaining labyrinthine data pipelines just for simple logic. Teams can bypass the friction of redirecting pipelines toward new views rather than base tables.
- Enterprise AI foundation: Modern AI agents require dependable, standardized features. Snowflake Virtual Columns offer a streamlined path to delivering immediate value at the source, supporting the high-quality data necessary for successful Enterprise AI implementations while simplifying the transition to higher-level semantic views.
Building a smarter, agile foundation
Virtual Columns streamline architecture by reducing the need for expensive backfills or complex workarounds. In an era increasingly defined by AI, maintaining clean and well-structured data is essential. Since high-quality data is a fundamental requirement for successful AI implementation, this feature helps to eliminate some of the common friction associated with achieving it.
To explore the capabilities of Virtual Columns and begin using them today, please refer to the Virtual Columns Documentation. Stay tuned, as this release represents only the beginning of our journey with Virtual Columns.
