Why is this query not able to execute?-CodePudding

The query goes on without executing, any ideas on how I can help it resolve?

Using the Economy Data Atlas dataset from Knoema within Snowflake.

with payment as (
  select *
  from DATA_WAREHOUSE.ANALYTICS.PAYMENTS
),

fx_rates as (
  select
    date("Date") as date,
    split("Currency Name", '/')[0] as from_currency,
    "Value" as rate
  from economy_data_atlas.economy.exratescc2018
  where date > (select MIN(processed_at) from payment )
    and from_currency in (select distinct target_currency from payment)
    and "Currency Unit" = 'USD'
    and "Indicator Name" = 'Close'
    and "Frequency" = 'D'
)


select
  payment.payout_guid as payout_id,
  payment.status,

  payment.source_amount,
  IFF(
    source_currency = target_currency,
    source_amount,
    ROUND(
      payment.source_amount * source_fx_rates_usd.rate,
      2
    )
  ) as source_amount_usd

from payment
left join fx_rates as source_fx_rates_usd on source_fx_rates_usd.date = date(payment.processed_at) and source_fx_rates_usd.from_currency = payment.source_currency

CodePudding user response：

This might be a case where temporary tables will help your what I assume is a massive table be pruned (aka one of the reasons to avoid complex CTE's)

I would re-write your SQL like:

with payment as (
    select *
        ,date(payment.processed_at) as _payment_date
    from DATA_WAREHOUSE.ANALYTICS.PAYMENTS
), distinct_targets as (
    select distinct target_currency 
    from payment 
), fx_rates as (
    select
        date("Date") as date,
        split_part("Currency Name", '/', 0) as from_currency,
        "Value" as rate
    from economy_data_atlas.economy.exratescc2018
    join distinct_targets dt
        on from_currency = dt.target_currency
    where date > (
        select MIN(processed_at) 
        from payment 
    )
    and "Currency Unit" = 'USD'
    and "Indicator Name" = 'Close'
    and "Frequency" = 'D'
)
select
      p.payout_guid as payout_id,
      p.status,
      p.source_amount,
      IFF(
        source_currency = target_currency,
        source_amount,
        ROUND( p.source_amount * sfx.rate, 2 )
      ) as source_amount_usd
from payment as p
left join fx_rates as sfx 
    on sfx.date = p._payment_date
    and sfx.from_currency = p.source_currency
left join fx_rates as tfx 
    on tfx.date = p._payment_date 
    and tfx.from_currency = p.target_currency

so I can understand it. And I notice that you join to fx_rates twice, but both are the exact same join logic. so tis 100% pointless.. and given tfx is not used I would guess it's that.

The next things to look at is how far through the data load your process is. To get an idea of how much more data there is to load.