Schema full load after changes

[sathyan75]

I have a table that I am loading from Salesforce. Salesforce purges the deleted records after 15 days. However, I would like to retain these records in Incorta. Whenever a change is made to the underlying salesforce object, and we include those fields in the salesforce table, Incorta is throwing an error as it is expecting to have a full reload of the table. Is there a workaround for this?

[AhmedM]

@sathyan75 there're two folds to your question. Changes in records and changes in columns.

Changes in Records (columns are unchanged):

Incorta won't purge the old records if you do incremental loads. But if you trigger a full load Incorta will discard all previously extracted records and will fetch the current records from the source.

For example, you have a table and you've triggered a full load that resulted in fetching 1000 records. then removed 500 of those records from the source table in Salesforce and added a 100 new records. Then triggered an incremental load in Incorta, you end up with 1,100 records in the Incorta table. But if you trigger a full load, you end up with 600 records (the old 500 + the new 100).

Changes in Columns:

If you're changing the table schema (changing columns), the behavior depends on whether you're adding or deleting columns. For example, assuming that the table had 10 columns (col_1, col_2, ... col_10) when you first created it. In each following load event (whether full or incremental), Incorta will look for the 10 columns in the resultset returned from Salesforce. If it doesn't find any of them (the column was delete from the source object in Salesforce), the load will fail.

But if the restultset contains more columns than what Incorta recognizes, it won't fail, it will simply discard the extra columns.

I hope this clarifies how Incorta behaves in each case. Does this describe what you're trying to do, or are you doing both at the same time? I.e., changing columns and adding new rows at the same time?

[sathyan75]

Hi Ahmed, Thanks for your response. My scenario is that I am adding new columns and there are new rows as well but I dont want to lose the old rows too. How to handle this?

[AhmedM]

@sathyan75 I've consulted the engineering team here and they confirmed that a full load is required in case you changed a table by adding or removing columns. That's by design to guarantee data consistency.

[RADSr]

I can think of a couple approaches:

1) rewrite your SFDC query select {columns}, 'a' AS NewCol1, 'a' AS NewCol2, 'a' AS NewCol3, ... from TABLE and then replace 'a' as new SFDC columns come on board.   Rename NewCol1 *in Incorta* so your SQL remains the same.

and/or 2) Before the new SFDC data comes down create a new Incorta table derived from select * from EXISTING_SFDC_INCORTA_TABLE ( probably in a different schema so it never gets accidentally overwritten ) and then update the original as new rows/columns/data comes on board in SFDC.    Author MVs as needed w/ data from both the old, archive Incorta table and the new SFDC clone.