issue related mismatch count

Question

issue related mismatch count

Vineet S 1,410

Hi Expert,

the flow starts from source to landing, source to reference , source to trans and source what are the best practices needs to follow

Muhammad Bilal Khan 0 Reputation points

2026-09-02T11:09:56.3366667+00:00

Hi Vineet,

If the flow is moving from Source → Landing, Source → Reference, Source → Transaction, and Source, I would recommend following a few best practices to avoid data mismatch and ensure consistency:

Define a clear source of truth and make sure all downstream layers are based on the same source/version of data.

Implement record-count validation between Source and each target (Landing, Reference, and Transaction).

Use reconciliation checks for inserts, updates, and deletes rather than relying only on total counts.

Add a unique/business key to identify records and detect duplicates or missing records.

Maintain audit columns such as Batch ID, Load Date/Time, Source System, and Record Status.

Use incremental loading where appropriate and ensure the watermark/CDC logic is consistent across all flows.

Implement error handling and retry mechanisms so failed records can be identified and reprocessed.

Maintain data-quality checks for nulls, duplicates, data types, and referential integrity.

Keep detailed logging and monitoring for each pipeline, including source count, target count, rejected records, and processing status.

For the mismatch-count issue specifically, I would first compare the counts at each stage of the pipeline for the same Batch ID, then identify whether the difference is caused by filtering, duplicate handling, rejected records, joins, or incremental-load logic.

This will make it much easier to identify exactly where the count mismatch is occurring.
Himaja Y 375 Reputation points Microsoft External Staff Moderator

2026-09-03T10:43:05.9266667+00:00
Hi @Vineet S ,

Thank you for reaching out to the Microsoft Q&A forum

To avoid record count mismatches across layers, the recommended approach is to use a single ingestion path:

Source → Landing (Raw) → Reference / Transformation

Rather than loading the Reference and Transformation layers directly from the source, ingest the data once into the Landing layer and use it as the authoritative source for downstream processing. This helps ensure consistency and prevents discrepancies caused by multiple reads of the source at different points in time.

Some key best practices include:

Perform reconciliation checks between each layer (Source → Landing, Landing → Reference, and Landing → Transformation).

Maintain audit/control tables to track source counts, processed counts, rejected records, and duplicate records.

Use a unique batch or run ID to trace data throughout the pipeline.

Implement data quality validations and deduplication based on business keys.

Monitor and alert on count mismatches so issues can be identified and investigated early.

By following these practices, it becomes much easier to identify where a count mismatch is introduced and ensure data integrity across the pipeline.

I hope this helps.
Himaja Y 375 Reputation points Microsoft External Staff Moderator

2026-09-04T10:56:11.39+00:00

Hi @Vineet S ,

Just checking in to see if the information provided earlier was helpful. Please let us know if you need any additional details or assistance, and we’ll gladly continue supporting you.

Answer accepted by question author

0 additional answers

Your answer

Muhammad Bilal Khan 0 Reputation points

2026-09-02T11:09:56.3366667+00:00

Hi Vineet,

If the flow is moving from Source → Landing, Source → Reference, Source → Transaction, and Source, I would recommend following a few best practices to avoid data mismatch and ensure consistency:

Define a clear source of truth and make sure all downstream layers are based on the same source/version of data.

Implement record-count validation between Source and each target (Landing, Reference, and Transaction).

Use reconciliation checks for inserts, updates, and deletes rather than relying only on total counts.

Add a unique/business key to identify records and detect duplicates or missing records.

Maintain audit columns such as Batch ID, Load Date/Time, Source System, and Record Status.

Use incremental loading where appropriate and ensure the watermark/CDC logic is consistent across all flows.

Implement error handling and retry mechanisms so failed records can be identified and reprocessed.

Maintain data-quality checks for nulls, duplicates, data types, and referential integrity.

Keep detailed logging and monitoring for each pipeline, including source count, target count, rejected records, and processing status.

For the mismatch-count issue specifically, I would first compare the counts at each stage of the pipeline for the same Batch ID, then identify whether the difference is caused by filtering, duplicate handling, rejected records, joins, or incremental-load logic.

This will make it much easier to identify exactly where the count mismatch is occurring.
Himaja Y 375 Reputation points Microsoft External Staff Moderator

2026-09-03T10:43:05.9266667+00:00

Hi @Vineet S ,

Thank you for reaching out to the Microsoft Q&A forum

To avoid record count mismatches across layers, the recommended approach is to use a single ingestion path:

Source → Landing (Raw) → Reference / Transformation

Rather than loading the Reference and Transformation layers directly from the source, ingest the data once into the Landing layer and use it as the authoritative source for downstream processing. This helps ensure consistency and prevents discrepancies caused by multiple reads of the source at different points in time.

Some key best practices include:

Perform reconciliation checks between each layer (Source → Landing, Landing → Reference, and Landing → Transformation).

Maintain audit/control tables to track source counts, processed counts, rejected records, and duplicate records.

Use a unique batch or run ID to trace data throughout the pipeline.

Implement data quality validations and deduplication based on business keys.

Monitor and alert on count mismatches so issues can be identified and investigated early.

By following these practices, it becomes much easier to identify where a count mismatch is introduced and ensure data integrity across the pipeline.

I hope this helps.
Himaja Y 375 Reputation points Microsoft External Staff Moderator

2026-09-04T10:56:11.39+00:00

Hi @Vineet S ,

Just checking in to see if the information provided earlier was helpful. Please let us know if you need any additional details or assistance, and we’ll gladly continue supporting you.

Answer 1

In Azure Databricks, when the same source feeds landing, reference, and transformation (trans) layers, you should establish a single, authoritative ingestion point. Load the source into the landing/raw layer first, capture the ingestion timestamp, batch/run ID, source record count, and checksum or other reconciliation metadata. Downstream reference and transformation processes should consume that controlled landing data rather than independently extracting from the source.

For the mismatch-count issue, implement record-count reconciliation at every stage: source → landing, landing → reference, and landing → trans. Store expected and actual counts, rejected/error counts, duplicate counts, and processed counts in an audit/control table. A pipeline should flag or fail when counts do not reconcile according to explicitly defined rules, rather than silently continuing.

Use Delta tables with ACID transactions, idempotent processing, and a consistent business key. Avoid relying solely on COUNT(*); also reconcile key fields, aggregates, and, where appropriate, hashes. Handle late-arriving data, deletes, duplicates, and retries explicitly. Each pipeline run should have a unique batch/run ID so records can be traced from the original source through every downstream layer.

The preferred pattern is essentially Source → Landing/Raw → Reference and Trans → Curated/Serving, with centralized audit/reconciliation between each layer. This prevents multiple independent reads of the source from producing different snapshots and makes a count mismatch much easier to isolate.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin