Sync latest changes in a normalized database to Salesforce?

Question

We have a business requirement to keep a repository of clients up to date in Salesforce, to that end it's been decided that we should implement a small rest API returning JSON that is able to be cached and paged that will be queried once a day-ish.

The current plan is to call a stored procedure inside SQL Server, to call upon a custom view.

However returning all the results would be very inefficient to both serve as JSON, page through, etc.

Luckily all the relevant rows in the database, have a LastUpdated timestamp, Unluckily it's fairly well normalized, so now I find myself in the position of merging upwards of 15 tables worth of LastUpdated dates in a max-like function, which appears fairly complex to do in SQL in a null safe manner.

I probably don't actually need the LastUpdated data in Salesforce, which makes me wonder, would it be better to Take the max of 15 LastModified columns in various nested queries, or compare them all to the input date in many different where clauses, and somehow join them all back to the root object (student in this example).

e.g. for a simple example of 3 tables (ignoring the fact my real world query has several sub queries already that I barely understand, (In reality students have 1 or more guardians, guardians have 1 or more phone numbers of varying types)

Student
-------------------------------------------------------------------------
StudentID   |   Student   |   SchoolID   |   GuardianID   | LastModified



Guardian
-------------------------------------------------------------------------
GuardianID   |   Name   |   Phone   |   LastModified



School
-------------------------------------------------------------------------
SchoolID   |   Name   |   Phone   |   LastModified

To end up with a view that would export

View
--------------------------------------------------------------------------------------------------------------------
StudentID   |   Student   |   SchoolName   |   SchoolPhone   |   GuardianName   |   GuardianPhone   |   LastModified

Where either the LastModified is the latest of all 3 tables, OR all entries in the view are somehow the most recent changes, perhaps using rowversions?

Answer 1

You might want to rethink that one. And if "doesn't matter, deliver" comes from management - cover your a$$ in writing. If performance is an issue worst case you could load the view's result to some temporary table and serve that, right? Even if it takes 5 minutes run - as long as data's ready by the time the SF scheduled call comes it should be fine. (SF developer can use CRON expression for scheduling it and you could plan accordingly)

In no specific order...

I fear that even if you manage to pull this view off it will be useless for the Salesforce. 15-to-1 denormalisation into 1 flat table makes you wonder about quality of data in SF.

Has this ever been done before? Data was manually keyed in to SF or there was some data migration, ETL process you could reuse?

It's likely your data model in SF contains multiple tables including many-to-many relationships too (ERD, bit messy). Out of the box you have Account and Contacts and they can be related directly (by Contact.AccountId foreign key) and indirectly (by AccountContactRelation many-to-many table). Out of the box Contact has 5 phone fields (AssistantPhone, Fax, HomePhone, MobilePhone, OtherPhone, Phone). They might have used these for guardians or they might have gone the (fairly new) ContactPoint route and really have a related table with however many phones and addresses you need.

And then your company might have added custom tables, fields or decided to model it completely away from the standard tables. At the very least they could show you some diagrams from Setup -> Schema Builder to help match stuff.

If there are multiple tables - ideally each of them would have 1 or more field marked as "external id" (something unique on your end)

Another consideration is size. How many changes happen daily? If SF custom code will request JSON and receive something that (together with local variables) will tip it over 6 MB RAM limit it might be fun times. (Dev could use a nightly batch job to bump it to 12 but still). How will you track errors? If integration is down and you need to rerun 1 months' worth of data - godspeed?

Will you even make the service visible from outside? It can't be just on your local company network, it has to be an endpoint accessible from internet. How will you protect it (shared certificate maybe?), will you whitelist all SF data centre IPs... If not - you might have to push to SF instead of SF pulling it.

As to how else could you do it... Push vs pull techniques are very nicely described in Integration Patterns doc (html or pdf). Part of it is knowing what to google for and whether you already have some ETL tools. BizTalk? Informatica? Mulesoft? Azure Data Factory? Hell, there's a paid SQL Server plugin that hides the nasties as linked database, from what I remember you work with stored procedures and they abstract the SF API calls away. If you don't have any of these but still want to push - Salesforce "Data Loader" and "sfdx" developer utility can be run from commandline and upsert (bit like MS SQL merge statement) data. You could schedule them, wrap them in some simple utility script, use SF API libraries for .NET/PHP/Java/Python/what have you... Many ways to skin this cat.

If you'd expose a JSON service with Swagger there's even a no-code way to do it called "external services". And if you can produce OData compatible service there's something called Lightning Connect for realtime access (and your implementation of OData decides how much power you give, which tables, just readonly or CRUD, with search or not...)