Computing New Columns in Snowflake for fun and profit

Sometimes to make our lives easier, we, as database engineers, can create a table that automatically tells us the answer as we need it – or at least how we tell it we want it. In SQL Server, we create what is called “Computed Columns.”

DROP TABLE IF EXISTS dbo.Payroll;
GO

CREATE TABLE dbo.Payroll
(	[Name] [varchar](10) NOT NULL,
	[Hourly Pay Rate]  money NOT NULL,
	[Hours Worked Per Week] int NOT NULL,
	[Earned Per Week] AS [Hourly Pay Rate] * [Hours Worked Per Week]	)

INSERT INTO dbo.Payroll (Name, [Hourly Pay Rate], [Hours Worked Per Week])
SELECT 'Joseph', 14.5, 30

SELECT * FROM dbo.Payroll

You’ll notice that the “Earned Per Week” column doesn’t have a data type – SQL Server figures it out for you as it calculates the data we’ve told it to.

In Snowflake, we have something eerily similar. In Snowflake, though, it’s called a “Virtual Column.”

Notice same results! (Yay, me!)

Now, if I tried to update the data or insert it from scratch, because we just don’t like the data for whatever reason, then it will just fail.

Now, maybe you don’t have a need for anything as simple as multiplication. You can always do fun things like this:

Over40Hours is a boolean virtual column that can quickly tell us if this person worked for over 40 hours in a week! There are so many more logic functions and whatnot that we can do from here to use all kinds of complex logic right here!

Also, note that this will also be harder to troubleshoot if you ever try to update a view that shows this column. No, I won’t show it here – I have to leave something for you to figure out!

Now if we want to be like the nefarious Rob Volk and be sinister to our own users, we can create a virtual column that is defined like this:

Yes, that’s right. We defined the new column WeHateOurUsers as equal to 1/0. This will make the table completely unusable – as long as they do SELECT *. If you grab specific columns, it works just as expected.

With that, I hope you’ve learned something fun and exciting with Snowflake and SQL Server. Virtual and Computed columns can make your life easier (or the user’s life harder – up to you).