ISSUE WITH THE ADDITION OF COLUMNS OF A TABLE

Question

We are getting problem for one the tables that is being archived to blob storage from SQL Server and this is production environment

let's consider, A table has 20 columns and we added 2 more columns in that table, it archives or copies to Blob Storage from SQL ON-PRREM to ADF, no issues till here but we got the problem when we run the select statement in DataBricks, just wondering if we can do something differently in ADF, Please let's us know as soon as possible because it is production environment and I can be reached at zhaque2@forddirect.com

Answer 1

Hi @Zaeemul Haque

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

As I understand that you are facing an issue with a table that is being archived to Blob Storage from SQL Server using Azure Data Factory. The issue occurs when you run a select statement in Databricks after adding two columns to the table.

Could you please share more details to help us understand the issue better?

1. What is the exact error message you are getting in DataBricks?
2. Are you using the same schema in DataBricks as the one in SQL Server?
3. Have you tried recreating the DataBricks table or refreshing the metadata?

Here are a few steps and considerations to troubleshoot and potentially resolve the issue:

Schema Mismatch: The schema in Databricks might not be updated to reflect the new columns added to the SQL Server table. Verify the schema of the table in Databricks and ensure it matches the updated schema from SQL Server.

File Format Issues: Verify the file format in Blob Storage (e.g., CSV, Parquet). If it is a structured format like Parquet, ensure that the schema is compatible with the new table structure.

Schema Drift Handling: Schema drift might not be enabled, causing the pipeline to ignore new columns. Check if schema drift is enabled in the copy activity settings.

Data Type Incompatibility: The data types of the new columns in SQL Server might not be compatible with the existing schema in Blob Storage or Databricks. Verify the data types of the new columns and ensure they are compatible.

I hope this information helps. Please do let us know if you have any further queries.

Thank you.