I don’t know about y’all but I have to work with dirty data. A lot.
When you’re working with dirty data, one of the many things you can run into is that when you try to divide two numbers – you can cause a huge error because you tried to perform a mathematical no-no – dividing by zero.
Today, in Snowflake, we’re going to talk through a few ways to get around that particular error.
First, we should create a table and insert some data to play with.
CREATE OR REPLACE TABLE public.DivideByZero
(Number1 number(10,0) NOT NULL,
Number2 number(10,0) NOT NULL);
INSERT INTO public.DivideByZero
SELECT 1, 3 UNION ALL
SELECT 4, 2 UNION ALL
SELECT 8, 0;When we try the simple formula that we’re all used to, we get Mr. Grumpy Error:
But now, let’s try it with a function in Snowflake – Div0.
It truly is used as a function with all of the data points pushed in as parameters.
Notice that the Result column returns a 0 if Number2 – in this case – has a zero.
But what if…
Sometimes, you’ll have even more garbage-y data by some poor soul having placed a NULL in column Number2. I’ve changed the Number2 column to allow NULLs – in this case – and now we get to insert some data.
INSERT INTO public.DivideByZero
SELECT 6, NULL;If I run the same query as above, I get the following:
At least the Div0 function didn’t allow the query to break – thank goodness. Sometimes our business partners will not find the NULL to be acceptable here, but not this time! Do I have to pull out my friend the CASE statement and wrap all kinds of business logic in here?
Not this time! Div0NULL to the rescue!
But, notice the most important thing here! No freakin’ errors!
Which one of the above functions should you use? As always, it depends. If NULLs matter to you and your business partners – use the function that gives you the data you need to display. But always, use one of these functions, my friends – for safety’s sake.
With that, enjoy one of my favorite songs from Offspring about one of my favorite topics (at least for this week). Until next time my friends!