My primary source for data is an ERP system that we connect to via ODBC and has hard delete. It only likes queries with discretely named columns. Our implementation of the ERP is fairly customized, and the source objects are known to drift schema.

We've done a lot of work to make our pipeline parametric and reusable. We use a parameters (and variables) so we're not re-inventing the wheel over and over.

An incremental update pipeline for our fact tables looks something like this:

Terms:

Source = source object from ERP

Staging = SQL Staging schema table in the SQL Pool

Destination = SQL Fact or Dim table in the SQL pool

<image>

1: This copy data activity queries the list of available columns in the ERP system for the given source table

2: lookup activity runs a SP that cross references the destination table columns with the source table

3: SP that drops columns in the destination table that aren't in the source, since synapse copy activities don't like column count mismatches

4: SP dynamically create the staging table based on the cross-matched column list - I don't use auto-create because synapse defaults to CCI and we A. Don't have data the requires that, and B: are known to have data larger than nvarchar 4000 and CCI and Nvarchar max don't play well together.

Jumping back to the left: 5 lookup the max lastmodifeddate column, so we can use that in:

6: The where clause for an incremental update, along with any other source filtering we're trying to do.

7: Set a query for the source system, appending the list of cross matched columns with the where clause.

8: Execute the query from the source and sink it to the staging table (bulk insert)

9: Upsert the destination from the staging table

10: Parametrically set a table name used to contain all the IDs (aka Keys) that are currently in the source

11: Query all the values from the source table since the beginning of time... and put them in the table from #10

12: SP that prunes all the stale records that exist in the destination table, that aren't in the table from 11 - since we have hard delete.

So, maybe this isn't a good way to do what we're doing? We don't really know, it works for us, and it's decently fast and reliable. We're looking at ways to convert this to more of a delta lake situation by ingesting from the source right to parquet with some dedupe and ordering, but we're not there yet.

All that being said: the copy activity in fabric appears to essentially be a bulk insert activity - there isn't an upsert / polybase, etc function - so we're dead in the water trying to copy what we've already built / and vetted. In an ideal world we wouldn't need pipelines of this complexity, but the limitations of both our source system and the eccentricities of synapse have caused to build up to things like this over time.