source_db: art_relations
dim_table: currency_exchange_rates
export_sql: >-
  SELECT
  cer.period_id,
  c1.ISO_4217_code AS currency_from_code,
  c2.ISO_4217_code AS currency_to_code,
  cer.exchange_rate
  FROM currency_exchange_rates cer
  INNER JOIN currencies c1 ON c1.id = cer.currency_from_id
  INNER JOIN currencies c2 ON c2.id = cer.currency_to_id
insert_sql:
  - >-
    INSERT INTO
      {db}.{schema}.{dim_table}
    select scer.period_id,
      dc.currencyid as currency_from_id,
      dc2.currencyid as currency_to_id,
      scer.exchange_rate,
      '{current_timestamp}',
      '{current_timestamp}'
    FROM
      {db}.{schema}.{staging_table} scer
    INNER JOIN {db}.{schema}.dim_currency dc ON dc.currency_code = scer.currency_from_code
    INNER JOIN {db}.{schema}.dim_currency dc2 ON dc2.currency_code = scer.currency_to_code
    LEFT JOIN {db}.{schema}.{dim_table} s ON s.period_id = scer.period_id AND s.currency_from_id = dc.currencyid
      AND s.currency_to_id = dc2.currencyid
    WHERE s.period_id is null
related_sql:
  - >-
    UPDATE {db}.{schema}.dim_currency
    SET
      conversion_rate = cer.exchange_rate,
      date_updated = cer.last_updated
    FROM {db}.{schema}.{dim_table} cer
    WHERE cer.currency_from_id = currencyid and currency_to_id = 170
      AND cer.period_id = (
        SELECT max(period_id)
        FROM
        {db}.{schema}.{dim_table}
      )