CREATE SINK alpheya_experience_bff.report_pnl_daily_sink AS
SELECT
portfolio_id,
account_group_type,
dim_balance_date,
currency_code,
market_value,
total_average_cost,
fair_value,
market_value_system_currency,
total_average_cost_system_currency,
fair_value_system_currency,
cumulative_adjusted_netflow,
cumulative_adjusted_netflow_system_currency,
total_gain_loss,
unrealized_gain_loss,
realized_gain_loss,
total_gain_loss_system_currency,
unrealized_gain_loss_system_currency,
realized_gain_loss_system_currency
FROM alpheya_experience_bff.report_pnl_daily_mv AS report_pnl_daily_mv_next
WITH (
connector='postgres',
host='psql-nonprod-uae-derived.postgres.database.azure.com',
port='6432',
user='migration_user',
password='***',
database='ai-hub-int-alpheya-exp-bff',
table='report_pnl_daily',
schema='advisor_portal_bff',
type='upsert',
force_compaction='true',
primary_key='portfolio_id,account_group_type,dim_balance_date,currency_code'
)