Warehouse data in the CRM: copy what it acts on, virtualize what people read

Warehouse fields that feed a CRM report or rule have to be copied in, while fields people only read can stay in the warehouse behind an external object.

Warehouse mart Field sorter OData gateway External object Sync job CRM fields Account page only ever displayed feeds reports and rules
Each warehouse field takes one of two lines at the sorter, fetched on view or copied nightly, and both lines end on the account page.
On this page
  1. What the CRM can do with a row it doesn’t store
  2. Sorting 14 usage fields at Halden
  3. What a day costs on each path
  4. Loud failures and quiet ones
  5. When I’d skip the external object

A health score can fall from 82 to 35 in the warehouse and the CRM won’t notice. No record was saved, so no flow fires and the account doesn’t move into anyone’s at-risk list.

That’s where I’d start when someone asks for warehouse data on a CRM record. The argument is usually about the whole table: copy it in, or leave it in the warehouse and show it from there. I’d settle it field by field. If the CRM has to do something with the value, copy it. If a person only reads it on a page, leave it where it is.

Copying is what data teams call reverse ETL: a job reads a warehouse table and writes the values into ordinary CRM fields, here through Salesforce’s Bulk API 2.0. Virtualizing, on Salesforce, is Salesforce Connect. You define an external object, and the CRM stores none of its rows. It fetches them over OData each time a page asks.

What the CRM can do with a row it doesn’t store

Show it. That’s most of the list.

Nearly everything a CRM does unprompted reads its own database. An account list view filters on account fields, and a formula can’t reference a field on an external object. The rules an admin builds, record-triggered flows mostly, run when a record is saved, and nothing in the warehouse saves a CRM record.

Salesforce Connect reaches a little past display. External objects can be reported on, within row limits and with a callout per run, and searched through the source system. I count both as display anyway. A report a manager schedules for Monday morning needs the field on the record.

So the sort asks four things of each field: does a report use it, does automation read it, does anyone search by it, does a list view filter on it. One yes means copy. Four no’s mean display, and the field goes virtual.

Sorting 14 usage fields at Halden

Halden Robotics is my own invention: it leases floor-cleaning robots and sells seats on the console that schedules them. Its CRM holds 41,000 accounts, the mart has 14 usage fields for each, and sales wants them all on the account page.

field,report,automation,search,list_view,freshness,rows,verdict
health_score,yes,yes,no,yes,daily,41000,copy
seats_used,yes,yes,no,no,daily,41000,copy
last_active_date,yes,yes,no,yes,daily,41000,copy
robots_active,no,no,no,no,hourly,41000,virtual
sites_live,no,no,no,no,weekly,41000,virtual
cleaning_hours_30d,no,no,no,no,daily,41000,virtual
sqft_cleaned_30d,no,no,no,no,daily,41000,virtual
failed_runs_7d,no,no,no,no,hourly,41000,virtual
battery_health_pct,no,no,no,no,weekly,41000,virtual
firmware_version,no,no,no,no,weekly,41000,virtual
console_logins_30d,no,no,no,no,daily,41000,virtual
schedules_active,no,no,no,no,daily,41000,virtual
api_calls_30d,no,no,no,no,daily,41000,virtual
top_feature_30d,no,no,no,no,weekly,41000,virtual

Three fields get a yes. Health score and last active date feed the flow that hands at-risk accounts to a success manager, and seats used feeds the one that flags expansion. All three are in weekly reports. The other 11 are numbers a rep glances at before a call. I’d expect most sorts to be that lopsided.

Freshness and row count never change a verdict. They size the work. Row count is 41,000 throughout, one per account, and it only bites on finer-grained tables. Salesforce counts most records at about 2 KB, so a million rows of per-robot history would be roughly 2 GB once copied.

What a day costs on each path

The 11 display fields become one external object, tied to Account by an indirect lookup on a unique external ID and shown as a related list. Account pages get about 2,300 views a day. The three copied fields go out in one Bulk API 2.0 upsert a night, keyed on the same ID, 41,000 rows at most.

External object, 11 fields Nightly copy, 3 fields
CRM storage None None extra on existing records
API calls About 2,300 OData callouts a day, under Salesforce Connect’s own hourly limit One bulk job, a handful of requests from the daily allocation
Page latency About 400 ms per view for the related list None
When it breaks The related list shows an error Yesterday’s values stay and look current

That latency is 920 seconds a day, about fifteen minutes across every rep. The bulk job barely registers in a daily API call budget.

The table is kind to both columns. The virtual side needs a service in front of the warehouse that speaks OData, and Salesforce Connect is a paid add-on. Every page view is also a warehouse query, so I’d serve it from a small table keyed by account and expect a slow first view if the warehouse suspends when idle.

On the copy side the expensive part is the write. Every row in the file is an account save, which runs the org’s triggers and flows on Account and moves the modified timestamp other integrations watch. So the job should upload only rows whose values changed, and that set grows with each field in the file. It’s my main reason for keeping the copied set small.

Either extreme costs more. Copy all 14 and most active accounts get rewritten nightly for numbers no flow reads. Virtualize all 14 and the flows have nothing to read, so someone builds the sync later anyway.

Loud failures and quiet ones

How each path breaks matters more to me than what it costs. When the gateway is down, the related list says so on every account, and reps will tell you. When the nightly job fails, the page looks exactly like yesterday’s, and the flows keep routing on Tuesday’s health score.

So the alerting goes on the copy path: the job’s final state, and the failed-results file Bulk API 2.0 keeps for each job.

Freshness separates the two less than the pitch for virtualizing suggests. If the mart rebuilds at 3 a.m., both paths show 3 a.m. numbers all day. The gain is on the two display fields that want hourly data. Refresh the mart hourly and the related list follows. Copied, that’s 24 rounds of account saves a day.

When I’d skip the external object

The split only pays when the display pile can carry its own path. Halden’s 11 fields, seen 2,300 times a day, clear that bar for me. Had the sort come out 12 and two, I wouldn’t run a gateway and buy an add-on for two numbers. I’d copy all 14 and live with the extra writes.

Fields also change sides. The day a manager wants a list of accounts with more than five failed runs this week, failed_runs_7d gets a yes under list_view and joins the nightly file. That reopens the freshness question, since it wanted hourly data and the job runs once a night.