I
Data Source
| Property | Value |
|---|---|
| Source Type | Portfolio management report system — REST API |
| Delivery Method | HTTPS API — report generation + download |
| Authentication | PFX certificate (redwood-tamarac-prod-cert-pfx) + API config (redwood-tamarac-api-config) |
| Output Format | ZIP → CSV per report template |
| Intermediate Storage | Azure Blob Storage (tamarac container, hot tier) |
| Target Databases | PostgreSQL · Azure SQL (dbo schema) |
| DAGs | redwood_tamarac_sync_v2 (PG) · redwood_tamarac_azuresql_sync (AzureSQL) |
| Blob reuse window | 4 hours — AzureSQL uploads blob at 1 PM, PG reuses it at 2:30 PM |
| Row validation gate | Rejects load if new CSV row count < 95% of current table size |
Task Pipeline per Template
generate
Queue report or reuse blob
→
download
Wait & download binary
→
upload_blob
Upload ZIP to hot storage
→
extract
Unzip CSV
→
import_tmp
Load → __tmp_{table}
→
validate
Row count >= 95%
→
promote
TRUNCATE + INSERT
→
cleanup
Remove temp dir
Airflow pools limit concurrency: generate_pool (3 slots — API rate limiting) · import_pool (1 slot — memory pressure). Max active tasks: 20 across all templates.
II
Objects Being Synced
Standard Tamarac Tables (11)
tamarac_templates
Report template definitions and configuration
tamarac_account_information
Account master — entity type, custodian, household
tamarac_account_holdings_current
Current position holdings per account
tamarac_household_information
Household data — address, managed value, net worth
tamarac_group_members
Group-to-account membership
tamarac_billing_groups
Billing group definitions
tamarac_billing_history
Historical billing records
tamarac_security_information
Security master data
tamarac_reports
Report run definitions
tamarac_aum_previous_quarter
Prior quarter AUM snapshot
tamarac_manual_security_snapshot_ytd
Manual YTD security snapshot
Tableau CRM (Tamarac Analytics) Tables (22)
tableau_crm_tamarac_holdings
Current holdings with pricing
tableau_crm_tamarac_holdings_prior_month_end
Prior month-end holdings snapshot
tableau_crm_tamarac_transactions
Transaction records
tableau_crm_tamarac_transactions_2
Supplemental transactions
tableau_crm_tamarac_transactions_history
Full transaction history
tableau_crm_tamarac_transactions_large
Large/complex transactions (separate for performance)
tableau_crm_tamarac_households
Household-level data
tableau_crm_tamarac_financial_accounts
Financial account records
tableau_crm_tamarac_group_members
Group membership
tableau_crm_tamarac_aum_capital_flow_transactions
AUM & capital flow transactions
tableau_crm_tamarac_performance_data
TWR, IRR, benchmark comparisons
tableau_crm_tamarac_billing_groups
Billing group data
tableau_crm_tamarac_billing_payment_details
Billing payment detail
tableau_crm_tamarac_model_properties
Investment model properties
tableau_crm_tamarac_model_security
Model-to-security mapping
tableau_crm_tamarac_security_level_model
Security-level model data
tableau_crm_tamarac_account_excluded_securities
Excluded securities per account
tableau_crm_tamarac_account_security_information
Per-account security info
tableau_crm_tamarac_account_security_restrictions
Security restrictions
tableau_crm_tamarac_cash_reserves
Cash reserve settings
tableau_crm_tamarac_custom_input_symbols
Custom symbol mappings
tableau_crm_tamarac_substitute_securities
Substitute security rules
III
Config, Logs, Monitoring & Schedule
Configuration Table — tamarac_templates
| Column | Type | Description |
|---|---|---|
template_id | INTEGER PK | Internal template identifier |
template_name | TEXT | Tamarac report name |
destination_table | TEXT | Target PostgreSQL/AzureSQL table |
is_enabled | BOOLEAN | Active flag — only enabled templates are synced |
| API parameters | — | Report generation configuration (filters, date ranges, etc.) |
Feature Flags
| Flag | PG Value | AzureSQL Value | Purpose |
|---|---|---|---|
ENABLE_BLOB_UPLOAD | True | True | Upload downloaded ZIP to Azure Blob |
ENABLE_BLOB_REUSE | True | True | Reuse recent blob instead of re-downloading |
BLOB_REUSE_MAX_AGE_HOURS | 4 | 2 | Max blob age before re-download required |
ENABLE_ROW_VALIDATION | True | True | Reject load if rows drop >5% vs current table |
Monitoring & Alerting
| Property | Value |
|---|---|
| Notifier | airflow_email_notifier — success + failure callbacks |
| Subject prefix | [Redwood] |
| On success / failure | Yes / Yes |
| Execution timeout | 4 hours |
| Max active tasks | 20 (across all templates) |
| generate_pool | 3 slots (API rate limiting) |
| import_pool | 1 slot (memory pressure — OOM prevention) |
| Retry callbacks | validate_records / import_tmp_table failures clear upstream tasks for fresh download |
Email recipients: prashant.surana@collation.ai · monitor@collation.ai · mschwartz@sequoia-financial.com · handerson@sequoia-financial.com · vzivich@sequoia-financial.com · hpatel@sequoia-financial.com · karishni.mehta@collation.ai
DAG Schedule
Pipeline ordering: AzureSQL DAG runs at 1:00 PM UTC and uploads blob. PG DAG runs at 2:30 PM UTC and reuses that blob (within the 4-hour reuse window) — avoiding duplicate Tamarac API calls.
Max active runs: 1 · Catchup: disabled · AzureSQL start date: 2024-01-01 · PG start date: 2026-03-20
IV
Bronze Layer — Database Schema
Schema: PostgreSQL tables reside in the
public schema. Azure SQL tables use the dbo schema. All tables include imported_at TIMESTAMP and last_sync_at TIMESTAMP. Staging tables are named __tmp_{table_name} and are dropped after atomic promotion.tamarac_templates
CREATE TABLE public.tamarac_templates ( template_id INTEGER, template_name TEXT, destination_table TEXT, is_enabled BOOLEAN -- Additional API generation parameters... );
tamarac_account_information
CREATE TABLE public.tamarac_account_information ( account_number TEXT, account_name TEXT, entity_type TEXT, custodian_code TEXT, household_id TEXT, household_name TEXT, tax_id TEXT, open_date DATE, close_date DATE, is_excluded BOOLEAN, managed_value NUMERIC, upload_account_id TEXT, imported_at TIMESTAMP, last_sync_at TIMESTAMP );
tamarac_household_information
CREATE TABLE public.tamarac_household_information ( household_id TEXT, household_name TEXT, primary_contact_name TEXT, address_line1 TEXT, address_line2 TEXT, city TEXT, state TEXT, postal_code TEXT, country TEXT, phone TEXT, email TEXT, total_managed_value NUMERIC, net_worth NUMERIC, upload_household_id TEXT, imported_at TIMESTAMP, last_sync_at TIMESTAMP );
tamarac_security_information
CREATE TABLE public.tamarac_security_information ( security_id TEXT, ticker TEXT, cusip TEXT, security_name TEXT, asset_class TEXT, sector TEXT, upload_security_id TEXT, imported_at TIMESTAMP );
Tableau CRM Tables
All tableau_crm_tamarac_* tables are populated directly from Tamarac report CSV output. Column schemas are derived from report headers and vary per template. Key fields across tables:
| Table | Key Fields |
|---|---|
…_holdings | account_number, security_id, cusip, ticker, quantity, price, market_value, cost_basis, unrealized_gain_loss |
…_transactions | account_number, security_id, transaction_type, trade_date, settle_date, quantity, price, amount, fees |
…_transactions_history | Same as transactions — full historical set |
…_performance_data | account_number, period, twr, irr, benchmark_return, inception_date |
…_aum_capital_flow_transactions | account_number, date, flow_type, amount, beginning_value, ending_value |
…_households | household_id, household_name, managed_value, net_worth, primary_advisor |
…_financial_accounts | account_number, account_name, custodian, registration_type, household_id |
…_billing_groups | group_id, group_name, fee_schedule, accounts |
…_billing_payment_details | account_number, billing_date, amount, fee_type, period |
…_model_properties | model_id, model_name, asset_class, benchmark, risk_level |
…_model_security | model_id, security_id, ticker, target_weight, min_weight, max_weight |
…_account_excluded_securities | account_number, security_id, exclusion_reason, start_date |
…_account_security_restrictions | account_number, security_id, restriction_type, constraint_value |
…_cash_reserves | account_number, reserve_amount, reserve_type, effective_date |
…_substitute_securities | original_security_id, substitute_security_id, account_number, reason |
…_custom_input_symbols | symbol_name, mapped_ticker, asset_class, price_source |
V
Archiving Policy
No dedicated file archival DAG for Tamarac. Tamarac does not maintain source files in long-term storage. The blob storage in the
tamarac container serves as a short-term intermediate layer for blob reuse between the AzureSQL and PostgreSQL DAGs. Database-level archival may be configured via redwood_database_archive.
Blob Reuse Mechanism
| Setting | Value |
|---|---|
| Container | tamarac (Azure Blob hot tier) |
| PG reuse window | BLOB_REUSE_MAX_AGE_HOURS = 4 |
| AzureSQL reuse window | BLOB_REUSE_MAX_AGE_HOURS = 2 |
| Purpose | AzureSQL DAG (1:00 PM) uploads blob → PG DAG (2:30 PM) reuses it — avoids duplicate API calls |
| Feature flag | ENABLE_BLOB_REUSE = True |
| Blob naming | {safe_template_name}.zip |
Database Table Archiving (via redwood_database_archive)
| Setting | Value |
|---|---|
| Schedule | 0 2 * * * — Daily 2:00 AM UTC |
| Cold storage path | {blob_container}/{prefix}/{YYYYMMDD}/{table}_{timestamp}.zip |
| Config table | database_archive_config — tables must be explicitly enabled |
| Log table | database_archive_log |
| Cold storage secret | redwood-cold-storage-config |