With the unveiling of Fivetran Transformations, you now have even more reason to perform arithmetic operations across columns within your data warehouse. Unfortunately, a case may arise where a value is missing, but its absence should not invalidate an entire record.
Suppose you have sales records from a variety of jurisdictions and product categories. Not all jurisdictions have sales taxes and some product categories come with mandatory hazardous materials fees:
item | price | tax | hazmat_fee |
---|---|---|---|
oat_bran | 1.00 | 0.06 | NULL |
scalpel | 5.50 | NULL | NULL |
mercury_fulminate | 200.00 | NULL | 35.00 |
cesium_iodide | 450.00 | 45.00 | 35.00 |
If you tried to calculate the total cost for each record using the following query:
Your results will look like so:
item | price | tax | hazmat_fee | total_cost |
---|---|---|---|---|
oat_bran | 1.00 | 0.06 | NULL | NULL |
scalpel | 5.50 | NULL | NULL | NULL |
mercury_fulminate | 200.00 | NULL | 35.00 | NULL |
cesium_iodide | 450.00 | 45.00 | 35.00 | 530.00 |
What you really want in this case is to default to a value of 0 whenever a NULL is encountered. You can do this using the COALESCE function, which takes the first non-NULL argument from a list):
Returning:
item | price | tax | hazmat_fee | total_cost |
---|---|---|---|---|
oat_bran | 1.00 | 0.06 | NULL | NULL |
scalpel | 5.50 | NULL | NULL | NULL |
mercury_fulminate | 200.00 | NULL | 35.00 | NULL |
cesium_iodide | 450.00 | 45.00 | 35.00 | 530.00 |
Remember - when adding across columns, one plus NULL equals NULL! This is true for subtraction, multiplication, and division, too.
Learn more about Fivetran Transformations with a personalized demo or a free trial.
Kostenlos starten
Schließen auch Sie sich den Tausenden von Unternehmen an, die ihre Daten mithilfe von Fivetran zentralisieren und transformieren.
A friendly reminder: One plus NULL equals NULL
A friendly reminder: One plus NULL equals NULL
With the unveiling of Fivetran Transformations, you now have even more reason to perform arithmetic operations across columns within your data warehouse. Unfortunately, a case may arise where a value is missing, but its absence should not invalidate an entire record.
Suppose you have sales records from a variety of jurisdictions and product categories. Not all jurisdictions have sales taxes and some product categories come with mandatory hazardous materials fees:
item | price | tax | hazmat_fee |
---|---|---|---|
oat_bran | 1.00 | 0.06 | NULL |
scalpel | 5.50 | NULL | NULL |
mercury_fulminate | 200.00 | NULL | 35.00 |
cesium_iodide | 450.00 | 45.00 | 35.00 |
If you tried to calculate the total cost for each record using the following query:
Your results will look like so:
item | price | tax | hazmat_fee | total_cost |
---|---|---|---|---|
oat_bran | 1.00 | 0.06 | NULL | NULL |
scalpel | 5.50 | NULL | NULL | NULL |
mercury_fulminate | 200.00 | NULL | 35.00 | NULL |
cesium_iodide | 450.00 | 45.00 | 35.00 | 530.00 |
What you really want in this case is to default to a value of 0 whenever a NULL is encountered. You can do this using the COALESCE function, which takes the first non-NULL argument from a list):
Returning:
item | price | tax | hazmat_fee | total_cost |
---|---|---|---|---|
oat_bran | 1.00 | 0.06 | NULL | NULL |
scalpel | 5.50 | NULL | NULL | NULL |
mercury_fulminate | 200.00 | NULL | 35.00 | NULL |
cesium_iodide | 450.00 | 45.00 | 35.00 | 530.00 |
Remember - when adding across columns, one plus NULL equals NULL! This is true for subtraction, multiplication, and division, too.
Learn more about Fivetran Transformations with a personalized demo or a free trial.
Kostenlos starten
Schließen auch Sie sich den Tausenden von Unternehmen an, die ihre Daten mithilfe von Fivetran zentralisieren und transformieren.
*dbt Core is a trademark of dbt Labs, Inc. All rights therein are reserved to dbt Labs, Inc. Fivetran Transformations is not a product or service of or endorsed by dbt Labs, Inc.