Salesforce

Each active row in salesforce_sync_config generates a Airflow task sync__{object_name}. Tasks run with trigger_rule='all_done' so one object failure does not block others. All tasks retry 3 times with 5-second delays. A final cleanup task removes the staging directory.

Configuration Table — salesforce_sync_config

CREATE TABLE public.salesforce_sync_config (
    sf_object_name  TEXT NOT NULL,   -- e.g. Account, Contact, FinancialAccount
    table_name      TEXT NOT NULL,   -- destination table
    sync_mode       TEXT NOT NULL,   -- full | incremental
    strategy        TEXT NOT NULL,   -- smart | truncate | recreate
    tracking_field  TEXT NOT NULL,   -- default: SystemModstamp
    use_bulk_api    BOOLEAN NOT NULL,
    is_active       BOOLEAN NOT NULL
);

Metadata / Log Table — salesforce_sync_metadata

Monitoring & Alerting

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

Max active runs: default · Catchup: disabled · Start date: 2024-01-01

salesforce_accounts — Account Master (607 columns)

Core standard fields plus extensive FinancialServices Cloud and Tamarac namespace custom fields.

-- Standard fields
Id                               TEXT,
Name                             TEXT,
Type                             TEXT,
RecordTypeId                     TEXT,
BillingStreet                    TEXT,
BillingCity                      TEXT,
BillingState                     TEXT,
BillingPostalCode                TEXT,
BillingCountry                   TEXT,
Phone                            TEXT,
Fax                              TEXT,
Website                          TEXT,
AnnualRevenue                    NUMERIC,
NumberOfEmployees                INTEGER,
Industry                         TEXT,
OwnerId                          TEXT,
IsDeleted                        BOOLEAN,
CreatedDate                      TIMESTAMP,
LastModifiedDate                 TIMESTAMP,
SystemModstamp                   TIMESTAMP,
-- FinancialServices Cloud (FinServ__) namespace
FinServ__PrimaryContact__c       TEXT,
FinServ__InvestmentObjectives__c TEXT,
FinServ__RiskTolerance__c        TEXT,
FinServ__TotalAssets__c          NUMERIC,
FinServ__AnnualIncome__c         NUMERIC,
FinServ__NetWorth__c             NUMERIC,
-- Tamarac namespace
Tamarac__HouseholdId__c          TEXT,
Tamarac__AccountNumber__c        TEXT,
Tamarac__CustodianCode__c        TEXT
-- ... plus ~570 additional custom fields

salesforce_contacts — Contacts (217 columns)

Id                     TEXT,
AccountId              TEXT,
FirstName              TEXT,
LastName               TEXT,
Email                  TEXT,
Phone                  TEXT,
MobilePhone            TEXT,
Title                  TEXT,
Department             TEXT,
MailingStreet          TEXT,
MailingCity            TEXT,
MailingState           TEXT,
MailingPostalCode      TEXT,
Birthdate              DATE,
DoNotCall              BOOLEAN,
HasOptedOutOfEmail     BOOLEAN,
OwnerId                TEXT,
IsDeleted              BOOLEAN,
CreatedDate            TIMESTAMP,
LastModifiedDate       TIMESTAMP,
SystemModstamp         TIMESTAMP
-- Plus custom FinServ__ and other namespace fields

salesforce_financial_accounts — Financial Accounts (213 columns)

Id                                TEXT,
Name                              TEXT,
FinServ__PrimaryOwner__c          TEXT,
FinServ__FinancialAccountType__c  TEXT,
FinServ__Balance__c               NUMERIC,
FinServ__OpenDate__c              DATE,
FinServ__HeldAway__c              BOOLEAN,
FinServ__SourceSystemId__c        TEXT,
FinServ__CustodianCode__c         TEXT,
Tamarac__AccountNumber__c         TEXT,
Tamarac__TaxId__c                 TEXT,
IsDeleted                         BOOLEAN,
CreatedDate                       TIMESTAMP,
SystemModstamp                    TIMESTAMP
-- Plus ~200 additional FinServ__ custom fields

salesforce_opportunities — Opportunities (92 columns)

Id             TEXT,
AccountId      TEXT,
Name           TEXT,
StageName      TEXT,
Amount         NUMERIC,
CloseDate      DATE,
Probability    NUMERIC,
Type           TEXT,
LeadSource     TEXT,
Description    TEXT,
OwnerId        TEXT,
IsClosed       BOOLEAN,
IsWon          BOOLEAN,
CreatedDate    TIMESTAMP,
SystemModstamp TIMESTAMP

salesforce_cases — Cases (522 columns)

Id             TEXT,
AccountId      TEXT,
ContactId      TEXT,
Subject        TEXT,
Description    TEXT,
Status         TEXT,
Priority       TEXT,
Type           TEXT,
Reason         TEXT,
Origin         TEXT,
IsClosed       BOOLEAN,
OwnerId        TEXT,
CreatedDate    TIMESTAMP,
SystemModstamp TIMESTAMP
-- Plus ~508 custom fields

salesforce_users — Users (222 columns)

Id               TEXT,
Username         TEXT,
FirstName        TEXT,
LastName         TEXT,
Email            TEXT,
Title            TEXT,
Department       TEXT,
UserType         TEXT,
IsActive         BOOLEAN,
ProfileId        TEXT,
UserRoleId       TEXT,
CreatedDate      TIMESTAMP,
LastModifiedDate TIMESTAMP,
SystemModstamp   TIMESTAMP

Other Objects