Forum Discussion
Copy Data show Null on Table
So weve been testing Fabric and its good, but the Copy Data is inconsistent.
Sometimes it will show/store data sometimes it doesnt.
When it doesnt, it will have all the rows, but all values are null.
Anyone have this kind of issue? would love to hear your solution to this.
Hi
These are few things which I have seen most often, and what you might need to review to get it resolve.Schema drift between source and sink
If the source schema changes or arrives late, Copy still creates rows but fails value mapping.
Action. Turn off auto schema mapping. Explicitly map columns. Re-publish after any schema change.Data type mismatch
Copy writes rows even when type conversion fails. Values end up null.
Action. Compare source types with sink types. Watch decimals, datetime precision, and boolean fields. Add explicit casting in the source query if needed.Copy runs before source commit completes
If upstream jobs are still writing, Copy reads empty or partial values.
Action. Add a dependency or wait condition. Avoid parallel writes to the same source.OneLake shortcut or external source latency
Shortcuts sometimes resolve structure before data becomes readable.
Action. Retry with a short delay. Check storage logs for throttling or transient errors.Parallelism and batch size issues
High parallelism with wide tables leads to silent value drops.
Action. Reduce degree of copy parallelism. Lower batch size. Test again.Implicit column reordering
If column order changes and mapping relies on position, values shift to null.
Action. Always map by name, not position.
Solution I reccomand.
Run the same Copy with a single column. Then add columns in small groups. You will see the break point.
Also enable detailed Copy diagnostics and check per-column error counts.Try these in order -
A. Add resilience to the pipeline
- Insert a Wait (30–60s) between write and subsequent reads; enable a Retry policy on downstream Copy Data.
- Turn on detailed logging and capture row counts for each step (especially when the sink shows all NULLs).
B. Verify mapping & schema
- In Copy Data → Mapping, explicitly map columns (don’t rely on default). Ensure names, casing, and order match the sink schema; default mapping is case-sensitive.
- If your source is text without headers, use explicit ordinal mapping; otherwise NULLs are almost guaranteed.
C. Clean the source format
- For Excel/CSV, re-export to clean CSV and remove blank trailing rows; check for quoted delimiters.
- If using compressed files (.gz), confirm the format detection (some scenarios treat data as Parquet vs text) and align the sink types accordingly.
D. Align data types end-to-end
- Make sure each source field’s logical type can be written to the sink type (e.g., string → FLOAT won’t auto-cast). As a safe test, ingest stringy columns as VARCHAR(8000); then transform later.
E. Eliminate UI drift
- Open the pipeline JSON and confirm tableActionOption equals "Overwrite" (if you expect overwrites). If you see "OverwriteSchema", change it and republish.
F. Lakehouse vs SQL Endpoint
- If you query via the SQL endpoint and see NULLs, cross-check by reading the Delta table directly from Lakehouse Files (or use notebooks) to ensure the data itself isn’t NULL. Differences in engine support can make values look missing.
G. Alternative ingestion path (if the issue persists)
- Try a Dataflow Gen2 to stage into Lakehouse Parquet (no DW table) and finish with Table.StopFolding so data doesn’t get pushed to a DW table—this avoids specific DW-table nulls some users hit.
Hope this helps - please appreciate leaving a Kudos or accepting as a Solution!
5 Replies
Hi
These are few things which I have seen most often, and what you might need to review to get it resolve.Schema drift between source and sink
If the source schema changes or arrives late, Copy still creates rows but fails value mapping.
Action. Turn off auto schema mapping. Explicitly map columns. Re-publish after any schema change.Data type mismatch
Copy writes rows even when type conversion fails. Values end up null.
Action. Compare source types with sink types. Watch decimals, datetime precision, and boolean fields. Add explicit casting in the source query if needed.Copy runs before source commit completes
If upstream jobs are still writing, Copy reads empty or partial values.
Action. Add a dependency or wait condition. Avoid parallel writes to the same source.OneLake shortcut or external source latency
Shortcuts sometimes resolve structure before data becomes readable.
Action. Retry with a short delay. Check storage logs for throttling or transient errors.Parallelism and batch size issues
High parallelism with wide tables leads to silent value drops.
Action. Reduce degree of copy parallelism. Lower batch size. Test again.Implicit column reordering
If column order changes and mapping relies on position, values shift to null.
Action. Always map by name, not position.
Solution I reccomand.
Run the same Copy with a single column. Then add columns in small groups. You will see the break point.
Also enable detailed Copy diagnostics and check per-column error counts.Try these in order -
A. Add resilience to the pipeline
- Insert a Wait (30–60s) between write and subsequent reads; enable a Retry policy on downstream Copy Data.
- Turn on detailed logging and capture row counts for each step (especially when the sink shows all NULLs).
B. Verify mapping & schema
- In Copy Data → Mapping, explicitly map columns (don’t rely on default). Ensure names, casing, and order match the sink schema; default mapping is case-sensitive.
- If your source is text without headers, use explicit ordinal mapping; otherwise NULLs are almost guaranteed.
C. Clean the source format
- For Excel/CSV, re-export to clean CSV and remove blank trailing rows; check for quoted delimiters.
- If using compressed files (.gz), confirm the format detection (some scenarios treat data as Parquet vs text) and align the sink types accordingly.
D. Align data types end-to-end
- Make sure each source field’s logical type can be written to the sink type (e.g., string → FLOAT won’t auto-cast). As a safe test, ingest stringy columns as VARCHAR(8000); then transform later.
E. Eliminate UI drift
- Open the pipeline JSON and confirm tableActionOption equals "Overwrite" (if you expect overwrites). If you see "OverwriteSchema", change it and republish.
F. Lakehouse vs SQL Endpoint
- If you query via the SQL endpoint and see NULLs, cross-check by reading the Delta table directly from Lakehouse Files (or use notebooks) to ensure the data itself isn’t NULL. Differences in engine support can make values look missing.
G. Alternative ingestion path (if the issue persists)
- Try a Dataflow Gen2 to stage into Lakehouse Parquet (no DW table) and finish with Table.StopFolding so data doesn’t get pushed to a DW table—this avoids specific DW-table nulls some users hit.
Hope this helps - please appreciate leaving a Kudos or accepting as a Solution!
Hi ChristianDGreat ,
Thank you deborshi_nag for the response provided!
Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you.I am facing the same issue in multiple pipelines with copy activity. There seems to be a bug in the SQL endpoint. It's showing all NULL for each value but the Spark sessions can see the data (and the lakehouse view). Does anyone know how to report this bug? Is it reported?
Also the replies to this person are really generic and do not even address the issue.
