I want to replicate one very big table from sql server to fabric. The table contains order lines with about 10 years of history. Total size is about 200M rows. There is a column for updated_at and another column for created_at. My main problem is that there are physical deletes in this table. The delete_missing functionality will always fetch all primary keys for the entire table, which is too much data. The business rule is that orderlines might be updated or deleted up to one year back. Orders that are older than one year can be considered static. Do you have a good suggestion on how to handle this situation?

@stegus64 how about this:

yaml
  source: SQLSERVER
  target: FABRIC

  streams:
    # recent mutable data - frequent updates
    order_lines_recent:
      sql: |
        SELECT * FROM order_lines
        WHERE created_at >= DATEADD(year, -1, GETDATE())
      object: order_lines
      mode: incremental
      primary_key: [order_id, line_id]
      update_key: updated_at
      target_options:
        delete_missing: soft  # Handle deletes in recent data

    # historical static data - one-time load
    order_lines_historical:
      sql: |
        SELECT * FROM order_lines
        WHERE created_at < DATEADD(year, -1, GETDATE())
      object: order_lines_historical
      mode: full-refresh

and then inside fabric, you can have a view:

SELECT * FROM order_lines_recent UNION ALL SELECT * FROM order_lines_historical

or just use a hook or something to create a final table.

How would delete_missing work in that case? For that approach to work it would need to only fetch the primary keys for the last year, and when comparing primary keys with the target it would need to only compare with data for the last year. Otherwise all data created more than year ago would be marked as deleted.

well, I think it should work because you'd be using two separate target tables: -

order_lines

- only holds recent/mutable data (with delete_missing) -

order_lines_historical

- only holds historical/static data (no delete detection) The delete_missing compares source PKs vs that specific target table's PKs - not across all tables. So historical data in

order_lines_historical

is completely isolated and won't be marked as deleted. but thinking about it more, over time, they'd need to be a process to move data older than 1 year from

order_lines

to

order_lines_historical

, I think that's just an exra hook.

As a feature idea you could add a delete_missing_subset option that contains a WHERE condition that limits the PK you read from the source, and also limits the part of the target table where you look for deleted data. That way it would be easier to implement this scenario. No need for two separate tables, or handwritten SQL

yes was just thinking that, will consider for sure

If you add support for the subset, maybe you also need two different where clauses, one for the source and one for the target databases. The SQL dialects might be incompatible. Date arithmetic in Oracle and SQL server is quite different for example.

i think this could work:

yaml
  delete_missing:
    type: soft
    where: created_at >= DATEADD(year, -1, GETDATE())

current way would still be accepted

yaml
delete_missing: hard

so, thinking through this, it would use this where clause on both side when comparing the PK: - Source:

SELECT pk FROM source WHERE {where}

- Target:

SELECT pk FROM target WHERE {where}

so it compares apple-to-apple... am I missing anything?

looks good except for possible incompatible sql dialects. As I said, Oracle and sql server treats date arithmetic differently

well it's customizable right? you would just put the correct dialect yourself

yes, but if you just have one where configuration you would have to use the same clause both in the source and the target.

ah, i see now

What you need is two different where configurations. target_where and source_where.

thanks, let me think about that

One more thing that I have a problem with is that we are forced to use NOLOCK when reading from the source system. This makes the set of primary keys unreliable for us. This means that we might have to make sure that a pk is missing for two batches in a row before considered the row deleted. This is probably not something you would want to add direct support for, but if you can think of a good way to handle this it would at least be useful for us.

This is added in latest version: https://docs.slingdata.io/examples/database-to-database/incremental#scoped-delete-with-where-clauses

Very nice! Thank you for fixing this so quickly!