TIL how to use snowflake's VALUES sub-clause

Today I found out about the useful VALUES()   sub-clause in snowflake that allows you to dynamically generate a fixed, known set of rows inside your query.

Problem

I had a table exchange_rates of EUR-currency pairs with their corresponding exchange rates

that I wanted to join with a table transactions which contained transaction prices in different currencies:

My goal was to standardize all prices to EUR.

The SQL query to join the two tables would be:

select 
id,
price,
price_currency,
exchange_rate
from transactions as t
inner join exchange_rates as er on er.target_currency = t.price_currency

The issue was that exchange_rates did not have a EUR-EUR currency pair and I didn’t have access to the data source. So, the inner join would filter out all EUR transactions (or leave NULLs in case of a left join). This is a bit of a constructed problem to show an application of VALUES(). One could alternatively fill the NULLs with 1.0s after a left join.

Solution

I used snowflake’s VALUES() to dynamically generate another row in the query. I use this with a UNION statement to complete the exchange_rates table before joining:

with exchange_rates_complete as (
  select * from exchange_rates
  union
  select * from (values( '2023-10-03', 'EUR', 'EUR', 1.0, 1))
)

select 
id,
price,
price_currency,
exchange_rate
from transactions as t
inner join exchange_rates_complete as er on er.target_currency = t.price_currency

The joined table looks like this: