Redwood — Silver Layer: Views, Queries & Indices¶
Layer: Silver (Curated)
Source: Bronze tables incollationdatawarehouse@10.0.0.8:5444
Schema:silver(create withCREATE SCHEMA IF NOT EXISTS silver;)
Purpose: Business-ready, joined, and cleaned views consumed by dashboards, advisors, and downstream Gold-layer aggregations.
Contents¶
- Household Master
- Current Asset Positions
- Holdings Snapshot (Trend)
- Alerts & Notifications
- Cash Flows — Transfers, Wires, Distributions
- Buy / Sell Transactions
- Salesforce Cases
- Advisor Directory
- Contracts & Fee Details
- Sales Pipeline
- Billing Transactions & Payments
- Client Review Meetings
- Relationship Health Scores
- Client Communications Log
- Financial Account Master
- Account Performance Returns
1. Household Master¶
Business need: Single canonical list of households with IDs, names, AUM, address, and assigned service team — joining Tamarac portfolio data with Salesforce CRM data.
Source tables:
- tamarac_household_information — household ID, name, address, AUM, service_team
- tableau_crm_tamarac_financial_accounts — account count, total value, primary_household_id
- salesforce_accounts — CRM household record for phone, email, relationship manager
View¶
CREATE OR REPLACE VIEW silver.household_master AS
SELECT
h.upload_household_id AS household_id,
h.household_name,
h.household_crm_id AS sf_account_id,
-- Address
h.household_address1 AS address_line1,
h.household_address2 AS address_line2,
h.household_city AS city,
h.household_state AS state,
h.household_zip AS zip,
-- AUM & wealth
h.managed_value AS managed_aum,
h.net_worth,
-- Service team
h.service_team,
-- Account count from Tamarac
COUNT(DISTINCT fa.upload_account_id) AS account_count,
SUM(fa.account_current_value) AS total_account_value,
-- CRM overlay
sa."Name" AS crm_household_name,
sa."Phone" AS phone,
sa."BillingCity" AS crm_city,
sa."BillingState" AS crm_state,
h.last_sync_at
FROM tamarac_household_information h
LEFT JOIN tableau_crm_tamarac_financial_accounts fa
ON fa.primary_household_id = h.upload_household_id
AND fa.closed_date IS NULL
LEFT JOIN salesforce_accounts sa
ON sa."Id" = h.household_crm_id
GROUP BY
h.upload_household_id, h.household_name, h.household_crm_id,
h.household_address1, h.household_address2, h.household_city,
h.household_state, h.household_zip, h.managed_value, h.net_worth,
h.service_team, sa."Name", sa."Phone", sa."BillingCity",
sa."BillingState", h.last_sync_at;
Indices¶
CREATE INDEX IF NOT EXISTS idx_tamarac_hh_id
ON tamarac_household_information (upload_household_id);
CREATE INDEX IF NOT EXISTS idx_tamarac_hh_crm
ON tamarac_household_information (household_crm_id);
CREATE INDEX IF NOT EXISTS idx_tcm_fa_primary_hh
ON tableau_crm_tamarac_financial_accounts (primary_household_id)
WHERE closed_date IS NULL;
CREATE INDEX IF NOT EXISTS idx_sf_accounts_id
ON salesforce_accounts ("Id");
2. Current Asset Positions¶
Business need: Unified position book across Schwab, Fidelity, and Tamarac — security, account, quantity, market value, and custodian in one view.
Source tables:
- schwab_rps — Schwab settled positions
- fidelity_positions — Fidelity positions
- tableau_crm_tamarac_holdings — Tamarac holdings with cost basis and G/L
View¶
CREATE OR REPLACE VIEW silver.current_positions AS
-- Schwab
SELECT
'Schwab' AS custodian,
mstracct_number AS master_account,
account_id AS account_number,
cusip,
ticker_symbol AS symbol,
security_description_line_1 AS security_name,
quantity_settled AS quantity,
closing_price AS price,
(quantity_settled * closing_price) AS market_value,
prodcatg_code AS product_category,
business_date AS as_of_date
FROM schwab_rps
WHERE quantity_settled <> 0
UNION ALL
-- Fidelity
SELECT
'Fidelity' AS custodian,
NULL AS master_account,
accountnumber AS account_number,
cusip,
NULL AS symbol,
securitydescriptionline1 AS security_name,
tradedatequantity AS quantity,
marketprice AS price,
tradedatequantity * marketprice AS market_value,
NULL AS product_category,
marketpricedate AS as_of_date
FROM fidelity_positions
WHERE tradedatequantity <> 0
UNION ALL
-- Tamarac
SELECT
'Tamarac' AS custodian,
NULL AS master_account,
account_number,
NULL AS cusip,
symbol,
NULL AS security_name,
quantity,
price,
quantity * price AS market_value,
NULL AS product_category,
imported_at::DATE AS as_of_date
FROM tableau_crm_tamarac_holdings
WHERE quantity <> 0;
Indices¶
CREATE INDEX IF NOT EXISTS idx_schwab_rps_acct_date
ON schwab_rps (account_id, business_date);
CREATE INDEX IF NOT EXISTS idx_schwab_rps_cusip
ON schwab_rps (cusip);
CREATE INDEX IF NOT EXISTS idx_fidelity_pos_acct
ON fidelity_positions (accountnumber, marketpricedate);
CREATE INDEX IF NOT EXISTS idx_fidelity_pos_cusip
ON fidelity_positions (cusip);
CREATE INDEX IF NOT EXISTS idx_tcm_holdings_acct
ON tableau_crm_tamarac_holdings (account_number);
CREATE INDEX IF NOT EXISTS idx_tcm_holdings_symbol
ON tableau_crm_tamarac_holdings (symbol);
3. Holdings Snapshot (Trend)¶
Business need: Weekly or monthly point-in-time snapshots of holdings for trend analysis, AUM drift monitoring, and performance attribution comparisons.
Source tables:
- schwab_rps — daily position file with business_date
- fidelity_positions — daily file with marketpricedate
- tableau_crm_tamarac_holdings_prior_month_end — Tamarac month-end snapshot
View¶
CREATE OR REPLACE VIEW silver.holdings_snapshot AS
-- Schwab — all available business dates
SELECT
'Schwab' AS custodian,
business_date AS snapshot_date,
'daily' AS frequency,
account_id AS account_number,
cusip,
ticker_symbol AS symbol,
quantity_settled AS quantity,
closing_price AS price,
quantity_settled * closing_price AS market_value
FROM schwab_rps
UNION ALL
-- Fidelity — all available run dates
SELECT
'Fidelity' AS custodian,
marketpricedate AS snapshot_date,
'daily' AS frequency,
accountnumber AS account_number,
cusip,
NULL AS symbol,
tradedatequantity AS quantity,
marketprice AS price,
tradedatequantity * marketprice AS market_value
FROM fidelity_positions
UNION ALL
-- Tamarac — prior month-end snapshots
SELECT
'Tamarac' AS custodian,
imported_at::DATE AS snapshot_date,
'month-end' AS frequency,
account_number,
NULL AS cusip,
symbol,
quantity,
price,
quantity * price AS market_value
FROM tableau_crm_tamarac_holdings_prior_month_end;
Tip: Materialise as a table with
CREATE MATERIALIZED VIEW silver.holdings_snapshot_mv AS ...and refresh nightly for fast trend queries.
Indices¶
-- Materialised view indices (after CREATE MATERIALIZED VIEW)
CREATE INDEX IF NOT EXISTS idx_mv_snapshot_date
ON silver.holdings_snapshot_mv (snapshot_date, custodian);
CREATE INDEX IF NOT EXISTS idx_mv_snapshot_acct
ON silver.holdings_snapshot_mv (account_number, snapshot_date);
-- Source table indices
CREATE INDEX IF NOT EXISTS idx_schwab_rps_biz_date
ON schwab_rps (business_date);
CREATE INDEX IF NOT EXISTS idx_fidelity_pos_marketpricedate
ON fidelity_positions (marketpricedate);
4. Alerts & Notifications¶
Business need: Upcoming scheduled distribution/withdrawal events from Fidelity SWP/PIP plans and unusual activity flags from Schwab for proactive client communication.
Source tables:
- fidelity_scheduled_events — Systematic Withdrawal Plan (SWP) and Periodic Investment Plan (PIP) scheduled events per account
- schwab_trn — transaction-level events with flags
View¶
CREATE OR REPLACE VIEW silver.alerts_notifications AS
-- Fidelity SWP/PIP scheduled payment events with upcoming next pay dates
SELECT
'Fidelity' AS source,
'Scheduled Distribution' AS alert_type,
accountnumber AS account_number,
cusip_1_ AS cusip,
scheduledeventtype AS event_code,
TO_DATE(nextpipswpdate, 'YYYYMMDD') AS ex_date,
TO_DATE(nextpaydate, 'YYYYMMDD') AS payable_date,
swpamortizationrate AS event_rate,
TO_DATE(swpunlockdate, 'YYYYMMDD') AS action_deadline,
pipswpamount AS amount,
created_on::DATE AS detected_date
FROM fidelity_scheduled_events
WHERE nextpaydate IS NOT NULL
AND nextpaydate <> ''
AND TO_DATE(nextpaydate, 'YYYYMMDD') >= CURRENT_DATE
AND TO_DATE(nextpaydate, 'YYYYMMDD') <= CURRENT_DATE + INTERVAL '60 days'
UNION ALL
-- Schwab — journal / transfer / corporate action transactions from last 7 days
SELECT
'Schwab' AS source,
transaction_source_code_description AS alert_type,
account_id AS account_number,
cusip,
tran_srcde AS event_code,
trade_date AS ex_date,
settlmnt_date AS payable_date,
NULL AS event_rate,
NULL AS action_deadline,
net_amount AS amount,
trade_date AS detected_date
FROM schwab_trn
WHERE trade_date >= CURRENT_DATE - INTERVAL '7 days'
AND tran_srcde IN ('JNL','CA','DIV','INT','MAT','CALL','RDMPT','SPLIT')
ORDER BY payable_date;
Indices¶
CREATE INDEX IF NOT EXISTS idx_fid_events_nextpay
ON fidelity_scheduled_events (nextpaydate)
WHERE nextpaydate IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_fid_events_acct
ON fidelity_scheduled_events (accountnumber);
CREATE INDEX IF NOT EXISTS idx_schwab_trn_date_src
ON schwab_trn (trade_date, tran_srcde);
5. Cash Flows — Transfers, Wires, Distributions¶
Business need: All cash inflows and outflows across custodians — deposits, withdrawals, wire transfers, retirement distributions, and internal transfers.
Source tables:
- schwab_trn — Schwab cash transactions
- fidelity_activity — Fidelity cash activity
- tableau_crm_tamarac_transactions — Tamarac capital flows
- tableau_crm_tamarac_aum_capital_flow_transactions — Tamarac AUM flows
View¶
CREATE OR REPLACE VIEW silver.cash_flows AS
-- Schwab cash transactions
SELECT
'Schwab' AS custodian,
account_id AS account_number,
trade_date AS transaction_date,
settlmnt_date AS settlement_date,
transaction_source_code_description AS activity_type,
net_amount AS amount,
CASE WHEN net_amount > 0 THEN 'Inflow' ELSE 'Outflow' END AS direction,
NULL AS is_internal,
cusip,
mstracct_number AS master_account
FROM schwab_trn
WHERE tran_srcde IN (
'ACH','WIRE','JRNL','DIST','RMD','RET','DEP','WD',
'XFRIN','XFROUT','CONTRIB','ROLLIN','ROLLOUT'
)
UNION ALL
-- Fidelity cash activity
SELECT
'Fidelity' AS custodian,
accountnumber AS account_number,
entrydate AS transaction_date,
NULL AS settlement_date,
transactiontype_mnemonic_ AS activity_type,
bookkeepingamount *
CASE bookkeepingamountsign
WHEN '-' THEN -1 ELSE 1
END AS amount,
CASE WHEN bookkeepingamountsign = '+' THEN 'Inflow' ELSE 'Outflow' END AS direction,
NULL AS is_internal,
cusip,
NULL AS master_account
FROM fidelity_activity
WHERE transactiontype_mnemonic_ IN (
'ACH','WD','DEP','DIST','RMD','JRNL','WIRE','XFRIN','XFROUT'
)
UNION ALL
-- Tamarac capital flows
SELECT
'Tamarac' AS custodian,
account_number,
entry_date AS transaction_date,
NULL AS settlement_date,
activity_type,
amount,
CASE WHEN amount > 0 THEN 'Inflow' ELSE 'Outflow' END AS direction,
intra_group_flow AS is_internal,
symbol AS cusip,
NULL AS master_account
FROM tableau_crm_tamarac_transactions
WHERE activity_type IN (
'Deposit','Withdrawal','Transfer In','Transfer Out',
'Distribution','Contribution','Rollover In','Rollover Out'
)
ORDER BY transaction_date DESC;
Indices¶
CREATE INDEX IF NOT EXISTS idx_schwab_trn_srcde_date
ON schwab_trn (tran_srcde, trade_date);
CREATE INDEX IF NOT EXISTS idx_schwab_trn_acct
ON schwab_trn (account_id);
CREATE INDEX IF NOT EXISTS idx_fid_activity_type_date
ON fidelity_activity (transactiontype_mnemonic_, entrydate);
CREATE INDEX IF NOT EXISTS idx_tcm_trn_type_date
ON tableau_crm_tamarac_transactions (activity_type, entry_date);
6. Buy / Sell Transactions¶
Business need: All securities buy and sell transactions across custodians for trade reporting, compliance, and portfolio activity analysis.
Source tables:
- schwab_trn — Schwab trade activity
- fidelity_activity — Fidelity trade activity
- tableau_crm_tamarac_transactions — Tamarac trade records
View¶
CREATE OR REPLACE VIEW silver.trade_transactions AS
-- Schwab buy/sell
SELECT
'Schwab' AS custodian,
account_id AS account_number,
mstracct_number AS master_account,
trade_date,
settlmnt_date AS settlement_date,
tran_srcde AS transaction_code,
transaction_source_code_description AS transaction_description,
cusip,
ticker_symbol AS symbol,
quantity,
price,
gross_amount AS principal,
commission,
net_amount,
CASE
WHEN tran_srcde IN ('BY','BUY','BUYO') THEN 'Buy'
WHEN tran_srcde IN ('SL','SELL','SLLO') THEN 'Sell'
ELSE tran_srcde
END AS side,
broker_name,
order_id
FROM schwab_trn
WHERE tran_srcde IN ('BY','BUY','BUYO','SL','SELL','SLLO','SHRT','COVER')
UNION ALL
-- Fidelity buy/sell
SELECT
'Fidelity' AS custodian,
accountnumber AS account_number,
NULL AS master_account,
entrydate AS trade_date,
NULL AS settlement_date,
transactiontype_mnemonic_ AS transaction_code,
NULL AS transaction_description,
cusip,
NULL AS symbol,
bookkeepingquantity AS quantity,
price,
bookkeepingamount *
CASE bookkeepingamountsign WHEN '-' THEN -1 ELSE 1 END AS principal,
NULL AS commission,
bookkeepingmarketvalue *
CASE bookkeepingmarketvaluesign WHEN '-' THEN -1 ELSE 1 END AS net_amount,
CASE
WHEN transactiontype_mnemonic_ IN ('BY','BUY') THEN 'Buy'
WHEN transactiontype_mnemonic_ IN ('SL','SELL') THEN 'Sell'
ELSE transactiontype_mnemonic_
END AS side,
NULL AS broker_name,
NULL AS order_id
FROM fidelity_activity
WHERE transactiontype_mnemonic_ IN ('BY','BUY','SL','SELL','SHRT','COVER')
UNION ALL
-- Tamarac trades
SELECT
'Tamarac' AS custodian,
account_number,
NULL AS master_account,
entry_date AS trade_date,
NULL AS settlement_date,
activity_type AS transaction_code,
NULL AS transaction_description,
NULL AS cusip,
symbol,
quantity,
price,
amount AS principal,
NULL AS commission,
amount AS net_amount,
CASE
WHEN activity_type ILIKE '%buy%' THEN 'Buy'
WHEN activity_type ILIKE '%sell%' THEN 'Sell'
ELSE activity_type
END AS side,
NULL AS broker_name,
NULL AS order_id
FROM tableau_crm_tamarac_transactions
WHERE activity_type ILIKE ANY (ARRAY['%buy%','%sell%','%purchase%','%redemption%'])
ORDER BY trade_date DESC;
Indices¶
CREATE INDEX IF NOT EXISTS idx_schwab_trn_trade_date
ON schwab_trn (trade_date);
CREATE INDEX IF NOT EXISTS idx_schwab_trn_cusip
ON schwab_trn (cusip);
CREATE INDEX IF NOT EXISTS idx_fid_activity_date
ON fidelity_activity (entrydate);
CREATE INDEX IF NOT EXISTS idx_fid_activity_cusip
ON fidelity_activity (cusip);
CREATE INDEX IF NOT EXISTS idx_tcm_trn_symbol
ON tableau_crm_tamarac_transactions (symbol);
CREATE INDEX IF NOT EXISTS idx_tcm_trn_entry_date
ON tableau_crm_tamarac_transactions (entry_date);
7. Salesforce Cases¶
Business need: All service cases with status, category, owner name, and linked client — for service team SLA monitoring and case trend analysis.
Source tables:
- salesforce_cases — case records
- salesforce_accounts — client name lookup
- salesforce_users — owner name lookup
View¶
CREATE OR REPLACE VIEW silver.service_cases AS
SELECT
c."Id" AS case_id,
c."CaseNumber" AS case_number,
c."Status" AS status,
c."Type" AS type,
c."Priority" AS priority,
c."Origin" AS origin_channel,
c."Subject" AS subject,
c."Description" AS description,
c."Reason" AS reason,
c."IsEscalated" AS is_escalated,
-- Dates
c."CreatedDate" AS opened_at,
c."ClosedDate" AS closed_at,
EXTRACT(EPOCH FROM (
COALESCE(c."ClosedDate", NOW()) - c."CreatedDate"
)) / 3600.0 AS hours_open,
-- Client
a."Id" AS account_id,
a."Name" AS client_name,
-- Owner
u."Name" AS owner_name,
u."Email" AS owner_email,
u."Department" AS owner_department,
-- Category (custom field)
c."Request_Sub_category__c" AS sub_category
FROM salesforce_cases c
LEFT JOIN salesforce_accounts a ON a."Id" = c."AccountId"
LEFT JOIN salesforce_users u ON u."Id" = c."OwnerId"
ORDER BY c."CreatedDate" DESC;
Indices¶
CREATE INDEX IF NOT EXISTS idx_sf_cases_status
ON salesforce_cases ("Status");
CREATE INDEX IF NOT EXISTS idx_sf_cases_account
ON salesforce_cases ("AccountId");
CREATE INDEX IF NOT EXISTS idx_sf_cases_owner
ON salesforce_cases ("OwnerId");
CREATE INDEX IF NOT EXISTS idx_sf_cases_created
ON salesforce_cases ("CreatedDate");
CREATE INDEX IF NOT EXISTS idx_sf_users_id
ON salesforce_users ("Id");
8. Advisor Directory¶
Business need: Active advisors and service staff with roles, team assignments, and office location for team reporting and ownership attribution.
Source tables:
- salesforce_users — all active Salesforce users
- tamarac_household_information — service_team assignments
View¶
CREATE OR REPLACE VIEW silver.advisor_directory AS
SELECT
u."Id" AS user_id,
u."Name" AS full_name,
u."Email" AS email,
u."Title" AS title,
u."Department" AS department,
u."UserType" AS user_type,
-- Manager
mgr."Name" AS manager_name,
mgr."Email" AS manager_email,
-- Household count and AUM this advisor is service_team for
COUNT(DISTINCT h.upload_household_id) AS households_served,
SUM(h.managed_value) AS total_aum_served
FROM salesforce_users u
LEFT JOIN salesforce_users mgr
ON mgr."Id" = u."ManagerId"
LEFT JOIN tamarac_household_information h
ON h.service_team ILIKE '%' || u."Name" || '%'
WHERE u."IsActive" = TRUE
AND u."UserType" IN ('Standard','PowerPartner')
GROUP BY
u."Id", u."Name", u."Email", u."Title", u."Department",
u."UserType", mgr."Name", mgr."Email"
ORDER BY u."Department", u."Name";
Indices¶
CREATE INDEX IF NOT EXISTS idx_sf_users_active
ON salesforce_users ("IsActive", "UserType");
CREATE INDEX IF NOT EXISTS idx_sf_users_manager
ON salesforce_users ("ManagerId");
CREATE INDEX IF NOT EXISTS idx_tamarac_hh_service_team
ON tamarac_household_information (service_team);
9. Contracts & Fee Details¶
Business need: Active and historical advisory service agreements with billing frequency, fee schedule, and line-item breakdown.
Source tables:
- salesforce_contracts — contract header
- salesforce_contract_line_items — fee line items
- salesforce_accounts — client name
View¶
CREATE OR REPLACE VIEW silver.contracts_fee_details AS
SELECT
c."Id" AS contract_id,
c."ContractNumber" AS contract_number,
c."Status" AS status,
c."StartDate" AS start_date,
c."EndDate" AS end_date,
-- CASE to derive active flag
CASE
WHEN c."Status" = 'Activated'
AND (c."EndDate" IS NULL OR c."EndDate" >= CURRENT_DATE)
THEN TRUE ELSE FALSE
END AS is_active,
-- Client
a."Id" AS account_id,
a."Name" AS client_name,
-- Owner
u."Name" AS owner_name,
-- Line items
li."Id" AS line_item_id,
li."Unit_Price__c" AS unit_price,
li."Quantity__c" AS quantity,
li."Billable_Value__c" AS total_price,
c."Description" AS contract_description
FROM salesforce_contracts c
LEFT JOIN salesforce_accounts a ON a."Id" = c."AccountId"
LEFT JOIN salesforce_users u ON u."Id" = c."OwnerId"
LEFT JOIN salesforce_contract_line_items li ON li."Contract__c" = c."Id"
ORDER BY c."Status", c."StartDate" DESC;
Indices¶
CREATE INDEX IF NOT EXISTS idx_sf_contracts_status
ON salesforce_contracts ("Status");
CREATE INDEX IF NOT EXISTS idx_sf_contracts_account
ON salesforce_contracts ("AccountId");
CREATE INDEX IF NOT EXISTS idx_sf_cli_contract
ON salesforce_contract_line_items ("Contract__c");
10. Sales Pipeline¶
Business need: Active prospects and recent conversions — opportunity stage, estimated AUM, lead source, and advisor assignment.
Source tables:
- salesforce_opportunities — pipeline opportunities
- salesforce_accounts — linked account (prospect or client)
- salesforce_users — advisor/owner
- salesforce_leads — unconverted prospects
View¶
CREATE OR REPLACE VIEW silver.sales_pipeline AS
-- Active opportunities
SELECT
'Opportunity' AS record_type,
o."Id" AS record_id,
o."Name" AS opportunity_name,
a."Name" AS account_name,
o."StageName" AS stage,
o."Amount" AS estimated_amount,
o."CloseDate" AS expected_close_date,
o."LeadSource" AS lead_source,
o."ForecastCategory" AS forecast_category,
o."IsClosed" AS is_closed,
o."IsWon" AS is_won,
u."Name" AS advisor_name,
u."Email" AS advisor_email,
o."CreatedDate" AS created_at,
o."Type" AS opportunity_type
FROM salesforce_opportunities o
LEFT JOIN salesforce_accounts a ON a."Id" = o."AccountId"
LEFT JOIN salesforce_users u ON u."Id" = o."OwnerId"
UNION ALL
-- Unconverted leads
SELECT
'Lead' AS record_type,
l."Id" AS record_id,
l."Name" AS opportunity_name,
l."Company" AS account_name,
l."Status" AS stage,
NULL AS estimated_amount,
NULL AS expected_close_date,
l."LeadSource" AS lead_source,
NULL AS forecast_category,
CASE WHEN l."IsConverted" THEN TRUE ELSE FALSE END AS is_closed,
l."IsConverted" AS is_won,
u."Name" AS advisor_name,
u."Email" AS advisor_email,
l."CreatedDate" AS created_at,
NULL AS opportunity_type
FROM salesforce_leads l
LEFT JOIN salesforce_users u ON u."Id" = l."OwnerId"
WHERE l."IsConverted" = FALSE
ORDER BY created_at DESC;
Indices¶
CREATE INDEX IF NOT EXISTS idx_sf_opp_stage
ON salesforce_opportunities ("StageName", "IsClosed");
CREATE INDEX IF NOT EXISTS idx_sf_opp_account
ON salesforce_opportunities ("AccountId");
CREATE INDEX IF NOT EXISTS idx_sf_opp_owner
ON salesforce_opportunities ("OwnerId");
CREATE INDEX IF NOT EXISTS idx_sf_leads_status
ON salesforce_leads ("Status", "IsConverted");
11. Billing Transactions & Payments¶
Business need: Advisory fee billing history and payment status — billable AUM, fee charged, payment method, and billing period.
Source tables:
- tamarac_billing_history — fee invoices per account per period
- tableau_crm_tamarac_billing_payment_details — payment records
- tamarac_billing_groups — group definitions
View¶
CREATE OR REPLACE VIEW silver.billing_transactions AS
SELECT
bh.account_number,
bh.account_name,
bh.billing_group_name,
bh.billing_definition,
bh.billing_date,
bh.start_date AS period_start,
bh.end_date AS period_end,
-- Fee economics
bh.billable_value,
bh.billable_value_currency,
bh.billed_amount,
bh.billed_amount_currency,
bh.responsible_for,
bh.current_cash AS cash_at_billing,
-- Payment linkage (payment_account_number = account fees are debited from)
pd.payment_account_number,
pd.billing_date AS payment_billing_date,
bh.last_sync_at
FROM tamarac_billing_history bh
LEFT JOIN tableau_crm_tamarac_billing_payment_details pd
ON pd.account_number = bh.account_number
AND pd.billing_date = bh.billing_date
ORDER BY bh.billing_date DESC;
Indices¶
CREATE INDEX IF NOT EXISTS idx_billing_history_acct_date
ON tamarac_billing_history (account_number, billing_date);
CREATE INDEX IF NOT EXISTS idx_billing_history_group
ON tamarac_billing_history (billing_group_name);
CREATE INDEX IF NOT EXISTS idx_billing_payment_acct
ON tableau_crm_tamarac_billing_payment_details (account_number);
12. Client Review Meetings¶
Business need: Client review meetings extracted from Salesforce Cases and Events — date, advisor, client, and outcome for annual review tracking.
Source tables:
- salesforce_cases — cases typed as client reviews
- salesforce_events — calendar meetings
View¶
CREATE OR REPLACE VIEW silver.client_review_meetings AS
-- Review cases
SELECT
'Case' AS source,
c."Id" AS record_id,
a."Name" AS client_name,
a."Id" AS account_id,
u."Name" AS advisor_name,
c."CreatedDate"::DATE AS meeting_date,
c."ClosedDate"::DATE AS completed_date,
c."Subject" AS subject,
c."Status" AS status,
c."Description" AS notes
FROM salesforce_cases c
LEFT JOIN salesforce_accounts a ON a."Id" = c."AccountId"
LEFT JOIN salesforce_users u ON u."Id" = c."OwnerId"
WHERE c."Type" ILIKE ANY (ARRAY[
'%review%','%annual%','%quarterly%','%client meeting%','%portfolio review%'
])
UNION ALL
-- Calendar events
SELECT
'Event' AS source,
e."Id" AS record_id,
a."Name" AS client_name,
a."Id" AS account_id,
u."Name" AS advisor_name,
e."StartDateTime"::DATE AS meeting_date,
e."EndDateTime"::DATE AS completed_date,
e."Subject" AS subject,
'Completed' AS status,
e."Description" AS notes
FROM salesforce_events e
LEFT JOIN salesforce_accounts a ON a."Id" = e."WhatId"
LEFT JOIN salesforce_users u ON u."Id" = e."OwnerId"
WHERE e."Subject" ILIKE ANY (ARRAY[
'%review%','%annual%','%quarterly%','%client meeting%','%portfolio review%'
])
ORDER BY meeting_date DESC;
Indices¶
CREATE INDEX IF NOT EXISTS idx_sf_cases_type
ON salesforce_cases ("Type");
CREATE INDEX IF NOT EXISTS idx_sf_events_subject
ON salesforce_events ("Subject");
CREATE INDEX IF NOT EXISTS idx_sf_events_owner
ON salesforce_events ("OwnerId");
CREATE INDEX IF NOT EXISTS idx_sf_events_start
ON salesforce_events ("StartDateTime");
13. Relationship Health Scores¶
Business need: Composite health score per household — derived from meeting recency, open case count, AUM trend, communication frequency, and advisor engagement.
Source tables:
- tamarac_household_information — AUM
- salesforce_cases — open service issues
- salesforce_events — meeting activity
- salesforce_tasks — advisor touches
- tableau_crm_tamarac_performance_data — recent returns
View¶
CREATE OR REPLACE VIEW silver.relationship_health AS
WITH
-- Days since last client meeting
last_meeting AS (
SELECT
c."AccountId",
MAX(e."StartDateTime") AS last_meeting_dt
FROM salesforce_events e
JOIN salesforce_cases c ON c."AccountId" = e."WhatId"
WHERE e."Subject" ILIKE ANY (ARRAY['%review%','%meeting%','%annual%'])
GROUP BY c."AccountId"
),
-- Open cases count
open_cases AS (
SELECT "AccountId", COUNT(*) AS open_case_count
FROM salesforce_cases
WHERE "Status" NOT IN ('Closed','Resolved')
GROUP BY "AccountId"
),
-- Tasks in last 30 days
recent_tasks AS (
SELECT "AccountId", COUNT(*) AS touches_30d
FROM salesforce_tasks
WHERE "CreatedDate" >= NOW() - INTERVAL '30 days'
AND "Status" = 'Completed'
GROUP BY "AccountId"
),
-- Latest performance
perf AS (
SELECT account_number, net_twr
FROM tableau_crm_tamarac_performance_data
WHERE end_date = (SELECT MAX(end_date) FROM tableau_crm_tamarac_performance_data)
)
SELECT
h.upload_household_id AS household_id,
h.household_name,
h.managed_value AS aum,
h.service_team,
-- Meeting recency score (0-25): full score if met within 90 days
CASE
WHEN lm.last_meeting_dt IS NULL THEN 0
WHEN lm.last_meeting_dt >= NOW() - INTERVAL '90 days' THEN 25
WHEN lm.last_meeting_dt >= NOW() - INTERVAL '180 days' THEN 15
WHEN lm.last_meeting_dt >= NOW() - INTERVAL '365 days' THEN 8
ELSE 3
END AS meeting_score,
-- Open cases penalty (0-25): deduct for unresolved issues
GREATEST(0, 25 - COALESCE(oc.open_case_count, 0) * 5)
AS case_score,
-- Advisor touches score (0-25)
LEAST(25, COALESCE(rt.touches_30d, 0) * 5)
AS touch_score,
-- Performance score (0-25): positive TWR = good
CASE
WHEN p.net_twr IS NULL THEN 12
WHEN p.net_twr >= 0.05 THEN 25
WHEN p.net_twr >= 0.00 THEN 18
WHEN p.net_twr >= -0.03 THEN 10
ELSE 5
END AS performance_score,
-- Composite
(
CASE WHEN lm.last_meeting_dt >= NOW() - INTERVAL '90 days' THEN 25
WHEN lm.last_meeting_dt >= NOW() - INTERVAL '180 days' THEN 15
WHEN lm.last_meeting_dt >= NOW() - INTERVAL '365 days' THEN 8
ELSE 3 END
+ GREATEST(0, 25 - COALESCE(oc.open_case_count, 0) * 5)
+ LEAST(25, COALESCE(rt.touches_30d, 0) * 5)
+ CASE WHEN p.net_twr >= 0.05 THEN 25
WHEN p.net_twr >= 0.00 THEN 18
WHEN p.net_twr >= -0.03 THEN 10
ELSE 5 END
) AS health_score, -- 0-100
lm.last_meeting_dt,
COALESCE(oc.open_case_count, 0) AS open_cases,
COALESCE(rt.touches_30d, 0) AS advisor_touches_30d,
p.net_twr AS latest_net_twr
FROM tamarac_household_information h
LEFT JOIN salesforce_accounts sa ON sa."Id" = h.household_crm_id
LEFT JOIN last_meeting lm ON lm."AccountId" = sa."Id"
LEFT JOIN open_cases oc ON oc."AccountId" = sa."Id"
LEFT JOIN recent_tasks rt ON rt."AccountId" = sa."Id"
LEFT JOIN tableau_crm_tamarac_financial_accounts fa
ON fa.primary_household_id = h.upload_household_id
AND fa.closed_date IS NULL
LEFT JOIN perf p ON p.account_number = fa.account_number
ORDER BY health_score;
Indices¶
CREATE INDEX IF NOT EXISTS idx_sf_tasks_created_status
ON salesforce_tasks ("CreatedDate", "Status");
CREATE INDEX IF NOT EXISTS idx_sf_tasks_account
ON salesforce_tasks ("AccountId");
CREATE INDEX IF NOT EXISTS idx_tcm_perf_end_date
ON tableau_crm_tamarac_performance_data (end_date);
CREATE INDEX IF NOT EXISTS idx_tamarac_hh_crm_id
ON tamarac_household_information (household_crm_id);
14. Client Communications Log¶
Business need: Unified log of all client interactions — emails, phone calls, logged tasks, and events — for advisor activity reporting and compliance documentation.
Source tables:
- salesforce_tasks — logged calls, emails, to-dos
- salesforce_events — calendar meetings
- salesforce_accounts — client name
View¶
CREATE OR REPLACE VIEW silver.client_communications AS
-- Tasks (calls, emails, notes)
SELECT
'Task' AS interaction_type,
t."Id" AS record_id,
a."Name" AS client_name,
a."Id" AS account_id,
u."Name" AS advisor_name,
t."CreatedDate"::DATE AS interaction_date,
t."Subject" AS subject,
t."Type" AS activity_type,
t."Status" AS status,
t."Priority" AS priority,
t."Description" AS notes
FROM salesforce_tasks t
LEFT JOIN salesforce_accounts a ON a."Id" = t."AccountId"
LEFT JOIN salesforce_users u ON u."Id" = t."OwnerId"
UNION ALL
-- Events (meetings, calls)
SELECT
'Event' AS interaction_type,
e."Id" AS record_id,
a."Name" AS client_name,
a."Id" AS account_id,
u."Name" AS advisor_name,
e."StartDateTime"::DATE AS interaction_date,
e."Subject" AS subject,
e."Type" AS activity_type,
'Completed' AS status,
NULL AS priority,
e."Description" AS notes
FROM salesforce_events e
LEFT JOIN salesforce_accounts a ON a."Id" = e."WhatId"
LEFT JOIN salesforce_users u ON u."Id" = e."OwnerId"
ORDER BY interaction_date DESC;
Indices¶
CREATE INDEX IF NOT EXISTS idx_sf_tasks_account_date
ON salesforce_tasks ("AccountId", "CreatedDate");
CREATE INDEX IF NOT EXISTS idx_sf_events_what
ON salesforce_events ("WhatId");
CREATE INDEX IF NOT EXISTS idx_sf_events_created
ON salesforce_events ("StartDateTime");
15. Financial Account Master¶
Business need: Single master list of all financial accounts across Schwab, Fidelity, and Tamarac — with account type, custodian, household linkage, and current value.
Source tables:
- tableau_crm_tamarac_financial_accounts — Tamarac account registry (most complete)
- salesforce_financial_accounts — CRM account linkage
- tamarac_account_information — Tamarac portal account master
View¶
CREATE OR REPLACE VIEW silver.financial_account_master AS
SELECT
fa.upload_account_id AS tamarac_account_id,
fa.account_number,
fa.account_name,
fa.account_type,
fa.custodian,
fa.discretionary,
fa.taxable,
fa.closed_date,
fa.closed_date IS NULL AS is_active,
-- Valuation
fa.account_current_value AS current_value,
fa.current_cash,
fa.total_cash_reserves,
-- Household
fa.primary_household_id,
h.household_name,
h.service_team,
-- Performance
fa.performance_inception_date,
fa.benchmark,
fa.year_to_date_net_return,
-- Advisor team (from Tamarac)
fa.service_strategic_advisor,
fa.service_tactical_advisor,
fa.service_client_service_associate,
fa.service_other_advisor,
-- Reconciliation
fa.last_reconciliation_date,
fa.reconciliation_status,
-- CRM link
sfa."Id" AS sf_financial_account_id,
sfa."FinServ__FinancialAccountType__c" AS sf_account_type,
sfa."FinServ__Balance__c" AS sf_balance,
fa.date_created,
fa.last_sync_at
FROM tableau_crm_tamarac_financial_accounts fa
LEFT JOIN tamarac_household_information h
ON h.upload_household_id = fa.primary_household_id
LEFT JOIN salesforce_financial_accounts sfa
ON sfa."FinServ__SourceSystemId__c" = fa.account_number
ORDER BY fa.custodian, fa.account_number;
Indices¶
CREATE INDEX IF NOT EXISTS idx_tcm_fa_acct_num
ON tableau_crm_tamarac_financial_accounts (account_number);
CREATE INDEX IF NOT EXISTS idx_tcm_fa_custodian
ON tableau_crm_tamarac_financial_accounts (custodian)
WHERE closed_date IS NULL;
CREATE INDEX IF NOT EXISTS idx_sf_fa_source_id
ON salesforce_financial_accounts ("FinServ__SourceSystemId__c");
CREATE INDEX IF NOT EXISTS idx_tamarac_acct_info_num
ON tamarac_account_information (account_number);
16. Account Performance Returns¶
Business need: TWR and IRR performance returns per account for advisor reporting, GIPS composites, and client statements.
Source tables:
- tableau_crm_tamarac_performance_data — net/gross TWR and IRR per account per period
- tableau_crm_tamarac_financial_accounts — account metadata, benchmark, advisor
- tamarac_household_information — household context
View¶
CREATE OR REPLACE VIEW silver.account_performance AS
SELECT
p.account_number,
fa.account_name,
fa.account_type,
fa.custodian,
-- Period
p.start_date,
p.end_date,
(p.end_date - p.start_date) AS period_days,
-- Returns
p.net_twr AS net_twr,
p.gross_twr AS gross_twr,
p.net_irr AS net_irr,
p.gross_irr AS gross_irr,
(p.gross_twr - p.net_twr) AS fee_drag,
-- Account context
fa.benchmark,
fa.performance_inception_date,
fa.account_current_value AS current_aum,
fa.discretionary,
-- Advisor
fa.service_strategic_advisor,
-- Household
h.household_name,
h.service_team,
p.imported_at AS data_as_of
FROM tableau_crm_tamarac_performance_data p
LEFT JOIN tableau_crm_tamarac_financial_accounts fa
ON fa.account_number = p.account_number
LEFT JOIN tamarac_household_information h
ON h.upload_household_id = fa.primary_household_id
ORDER BY p.end_date DESC, p.account_number;
Indices¶
CREATE INDEX IF NOT EXISTS idx_tcm_perf_acct_period
ON tableau_crm_tamarac_performance_data (account_number, start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_tcm_perf_latest
ON tableau_crm_tamarac_performance_data (end_date DESC);
CREATE INDEX IF NOT EXISTS idx_tcm_fa_perf_join
ON tableau_crm_tamarac_financial_accounts (account_number, primary_household_id);
Deployment Notes¶
Create Silver Schema¶
CREATE SCHEMA IF NOT EXISTS silver;
Execution Order¶
Run views in this order (dependencies first):
1. silver.advisor_directory -- no upstream silver deps
2. silver.financial_account_master -- no upstream silver deps
3. silver.household_master -- depends on financial_account_master
4. silver.current_positions -- no upstream silver deps
5. silver.holdings_snapshot -- no upstream silver deps
6. silver.trade_transactions -- no upstream silver deps
7. silver.cash_flows -- no upstream silver deps
8. silver.alerts_notifications -- no upstream silver deps
9. silver.service_cases -- no upstream silver deps
10. silver.contracts_fee_details -- no upstream silver deps
11. silver.sales_pipeline -- no upstream silver deps
12. silver.billing_transactions -- no upstream silver deps
13. silver.client_review_meetings -- no upstream silver deps
14. silver.client_communications -- no upstream silver deps
15. silver.account_performance -- depends on financial_account_master
16. silver.relationship_health -- depends on multiple views above
Materialized Views (recommended for dashboards)¶
-- Refresh nightly after Bronze sync completes
REFRESH MATERIALIZED VIEW CONCURRENTLY silver.holdings_snapshot_mv;
REFRESH MATERIALIZED VIEW CONCURRENTLY silver.relationship_health_mv;
REFRESH MATERIALIZED VIEW CONCURRENTLY silver.account_performance_mv;
Grants¶
GRANT USAGE ON SCHEMA silver TO redwood_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA silver TO redwood_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA silver
GRANT SELECT ON TABLES TO redwood_reader;