Business scenario and technical implementation about world wide currency conversion with Redshift and Holistics
Hi there! When conducting a profit related analysis project and creating an visualisation to showcase your results across regions, having a currency filter is often required. Currency conversion is very common across industries such as e-commerce and financial works.
We will need a dimension table to get the universal currency and conversion rate in a daily granularity. The base currency is set to USD, every other currency conversion rate is converted to USD rate at its effective date.
CREATE TABLE dim_currency AS
(base_currency_code VARCHAR(3),
conversion_rate_to_usd NUMERIC,
effective_on DATE);Insert data into dim_currency. In real world, there are multiple ways to streaming the data insertion process. For example, Streamsets allow the ETL process to transfer Postgres to Redshift, to learn to use Streamsets, refer to article here.
INSERT INTO dim_currency VALUES(),(),(),In order to allow end users to select any currency and convert to any currency they want using a visualisation tool, we join the dim_currency table with itself to calculate the conversion rate.
SELECT…