insert into warehouse table has missing rows, but the source script has all.

Hi smeetsh,

I have sometimes observed silent records loss in copy jobs due to SQL Endpoint snapshot lag. And I want to rule out schema/data-type changes or copy logic issue, since the same setup has been running successfully for a year.

I suggest either read directly from Lakehouse (keeping a delay of 6 minutes) or increase the wait-time if you like to use SQL Endpoint only.

Hi all,

After digging deeper, this doesn’t look like a Copy activity or mapping issue. The key point is that rowsRead = rowsWritten in the Copy activity output, and the activity completes successfully without any warnings or errors. This rules out sink-side rejections, data type issues, or constraint violations in the Warehouse.

What we are actually seeing is a data freshness / synchronization issue caused by the SQL Endpoint.

Although the source query returns all expected rows when executed manually, the Copy activity reads from the SQL Endpoint snapshot, which can lag behind the Lakehouse data. This explains why:

• The latest rows are missing in the Warehouse

• The issue is intermittent

• The pipeline succeeds with correct row counts from the snapshot it sees

Increasing the wait time helps, but it is not deterministic, especially when multiple pipelines and parallel copy activities are running.

Confirmed solution / recommendation:

• Avoid using the SQL Endpoint as a source for time-sensitive ingestion.

• Read directly from the Lakehouse (Files/Delta tables) instead.

• If SQL Endpoint must be used, apply a much larger delay and treat it as eventually consistent, not real-time.

Once we switched to reading directly from the Lakehouse, the missing rows issue disappeared completely.

This confirms the root cause is SQL Endpoint snapshot lag, not the Copy activity itself.

Hope this helps anyone running into similar “silent missing rows” behavior.

Cheers
Barış