Assets Converted

Purpose

Provide a reporting-ready view of asset values converted into reporting currencies.

This model takes pre-aggregated asset amounts by currency and date and applies standardized exchange rates to express those values in one or more reporting currencies. It enables consolidated reporting, net worth calculations, and cross-currency comparisons.

All conversion logic is centralized here so downstream models do not need to handle exchange rates or conversion rules.

Business Logic

• Start from the aggregated Assets by Currency transformation
• Cross join with the Currencies semantic layer to generate reporting currencies
• Join to Reporting Rates to retrieve applicable exchange rates
• Apply currency conversion logic:
  • If asset currency equals reporting currency, use the original amount
  • Otherwise, multiply by the applicable exchange rate
• Round converted values to two decimal places

Output Contract

Definitions

Sample & Implementation

{{config (materialized='view')}}

with assets_by_currency as (
    select * from  {{ref('assets_by_currency')}}
),

currencies as (
    select * from {{ref('currencies')}}
),

reporting_rates as (
    select * from  {{ref('reporting_rates')}}
)

select
    abc.date,
    abc.weeknum,
    abc.currency,
    abc.total_amount as amount,
    c.currency_code as reporting_currency,
    COALESCE(rr.value, 1) as conversion_rate,
    ROUND (
        case
            when abc.currency = c.currency_code then abc.total_amount
            else abc.total_amount * rr.value
        end, 2) as converted_value
from currencies c
cross join assets_by_currency abc
left join reporting_rates rr
    on abc.date = rr.date
    and abc.currency = rr.base_currency
    and c.currency_code = rr.quote_currency
order by abc.weeknum

Dependencies

• Transformations: Assets by Currency and Reporting Rates
• Semantic Layers: Currencies