Ingesting CSV files and appending them with ODBC Data

Hi joshua1990 

Thanks for reaching out to the Microsoft fabric community forum.

lbendlin ,

Thanks for your prompt response

joshua1990 ,

Step-by-Step Approach to Append ODBC Data to Lakehouse after Checking CSV History

1.Standardize Schema Early

You’ve likely done this in your dataflow, but just to be safe: define explicit column types and ensure both CSV and ODBC sources match. You can do this in Power Query inside your Dataflow by setting each column's data type manually  this avoids schema drift or mismatches downstream

2.Stage ODBC Data in a Temporary Table

Before appending to your main table, load the ODBC data into a staging table in the Lakehouse (e.g., stg_odbc_quarterly). You can ingest using Dataflow Gen2, Pipelines, or even a Notebooks-based Fabric data task

3.Deduplicate with a SQL Statement

Once you have both datasets available in the Lakehouse, use a MERGE or INSERT ... SELECT SQL statement with a NOT EXISTS or LEFT JOIN condition to append only new records:

INSERT INTO final_consolidated_table

SELECT *

FROM stg_odbc_quarterly AS o

WHERE NOT EXISTS (

    SELECT 1

    FROM final_consolidated_table AS f

    WHERE f.primary_key = o.primary_key

);

Make sure the primary_key (or composite key) you're comparing is consistent between both CSV and ODBC sources.

If this post helped resolve your issue, please consider the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.