Forum Discussion
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ış
Hi smeetsh ,
Thank you for reaching out to the Microsoft Community Forum.
Please try below things to fix the issue.
1. Check one failed run activity Output JSON: Open the run --> the specific Copy activity like "2000_copy_incident" --> Output.
Capture: rowsRead, rowsWritten, Any error or skip counts, Reject log path, if present. If rowsRead = 4549 and rowsWritten < 4549, you have sink rejections.
2. In the Copy activity --> Fault tolerance: Set Maximum errors = 0 and Disable Skip incompatible rows.
3. Re-run. If the activity now fails, you will get the exact cause like PK violation, conversion error, string truncation, date overflow, etc.
4. Check data types & lengths for every column: VARCHAR/NVARCHAR lengths, DECIMAL(p,s) ranges and DATETIME/DATE conversions like TIMESTAMP_TZ from Snowflake --> SQL
Note: Newest rows often introduce longer values or unexpected nullability.
5. Check Warehouse constraints & indexes, Does the target table have PRIMARY KEY or UNIQUE constraints and Computed columns with constraints. Try inserting one of the “missing” rows manually into Warehouse using the same values.
6. Check there’s no hidden filter, parameter substitution, pipeline variables, or expressions like @{formatDateTime(utcNow(), 'yyyy-MM-dd')}). Run the exact command text from the activity using the same linked service and count the rows. If counts match the manual run (4549) but Copy lands fewer, it’s a sink issue.
If counts differ, it’s a source query/session issue.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
The activity doesn't fail, it runs without any errors, if i manualy run the one step it seems to work as well.
Edit i had a look at the specific copy step and the no of rows read = no of rows copied, so we are experiencing a synch issue I reckon?
Hi smeetsh ,
As mentioned by stoic-harsh , could you please read directly from Lakehouse instead of SQL Endpoint .
Regards,
Dinesh
Reading directly from a lakehouse is sadly not an option at this time
