Snowflake Reuse Computed column – Derived Columns

  • Post author:
  • Post last modified:November 18, 2022
  • Post category:Snowflake
  • Reading time:6 mins read

The derived columns in Snowflake are columns that are derived from the previously calculated columns in same SELECT statement. In this article, we will check how to reuse previously computed or calculated column in Snowflake. These kind of columns are sometime called lateral column alias.

Snowflake Reuse Computed column - Derived Columns

What are derived columns?

Derived columns or computed columns are virtual columns that are not physically stored in the table. Their values are re-calculated every time they are referenced in a query. In other word, Derived columns are columns that you derive from other previously computed column in same query context.

Firstly, let us understand the derived columns with an example.

For example, consider below SQL query:

select col1,
col2*10 as col2_tmp,
col2_tmp *100 as col3_tmp
from your_table;

As you can see in above SQL query example, col3_tmp uses previously derived or computed column col2_tmp for its calculations.

Many PostgreSQL relational databases such as Netezza supports reuse of the calculated column within the same query context.

Snowflake Reuse Computed column

The referring previously derived columns is common and extensively used in complex queries.

Following are three methods that you can use to reuse derived columns in Snowflake.

Now, let us check these methods in brief.

Directly Use Previously Calculated Columns

Snowflake allows you to use the previously calculated columns within same query context. This is one of the easy and commonly used methods.

For example, consider following example to reuse derived columns within same query context.

select ID, 
Price, 
price+10 as new_price, 
new_price*2 as another_price 
from TEST1;

+----+-------+-----------+---------------+
| ID | PRICE | NEW_PRICE | ANOTHER_PRICE |
|----+-------+-----------+---------------|
|  1 |   100 |       110 |           220 |
|  2 |   200 |       210 |           420 |
|  3 |   300 |       310 |           620 |
|  4 |   400 |       410 |           820 |
+----+-------+-----------+---------------+

Note that, newprice is reused during another_price calculation.

Compute Column Using Snowflake WITH clause

This is another easiest method that you can use to derive columns.

For example, consider below query that compute derive column.

WITH cte1 
     AS (SELECT ID, Price, price+10 as new_price 
         FROM TEST1) 
SELECT a.ID, 
       b.Price, 
       b.new_price * 2 AS another_price
FROM   TEST1 a 
       JOIN cte1 b 
         ON( a.ID = b.ID );

+----+-------+---------------+
| ID | PRICE | ANOTHER_PRICE |
|----+-------+---------------|
|  1 |   100 |           220 |
|  2 |   200 |           420 |
|  3 |   300 |           620 |
|  4 |   400 |           820 |
+----+-------+---------------+

As you can see, the another_price column is calculated using new_price which is derived in CTE.

You cam read more about Snowflake WITH clause in my other post:

Snowflake Subquery to Derive Column

This is another simple method in which you can use a subquery to derive column and reuse that column in the upper SELECT statement.

For example, consider below query with subquery.

SELECT sub.ID, 
       sub.Price, 
       sub.new_price * 2 AS der_col3
FROM
(
SELECT ID, Price, price+10 as new_price
FROM TEST1
) as sub;

+----+-------+----------+
| ID | PRICE | DER_COL3 |
|----+-------+----------|
|  1 |   100 |      220 |
|  2 |   200 |      420 |
|  3 |   300 |      620 |
|  4 |   400 |      820 |
+----+-------+----------+

As you can see, the another_price column is calculated using new_price which is derived in subquery.

Hope this helps 🙂

Tags: ,