Search for a command to run...
Compare Lakehouse tables with SQL Endpoint tables for mismatch
Principal Program Manager, Microsoft Fabric CAT helping users and organizations build scalable, insightful, secure solutions. Blogs, opinions are my own and do not represent my employer.
I see a lot of the errors below. How can this be resolved?
DeltaProtocolError: The table's minimum reader version is 2 but deltalake only supports version 1 or 3 with these reader features: {'timestampNtz'}
take a look at my blog : https://fabric.guru/working-with-delta-tables-in-fabric-python-notebook-using-polars
It's a rust engine error : https://github.com/delta-io/delta-rs/issues/2631
If nothing works, use pyspark.
Last week Microsoft released an open-source text embedding model called Harrier in three sizes- 270M, 0.6B and 27B. I have been testing it in my RAG pipeline and so far it has crushed all my metrics.
For Power BI Copilot and Data agents with semantic models, you must use Prep Data for AI configuration to ground the responses in the context added in Prep for AI. In this blog, I will show you how yo
At FabCon Atlanta last week, the updated notebook Copilot for data engineering and data science was announced. It brings agentic capabilities to the Copilot and is much more intelligent and Fabric-awa
Using Fabric data agent Python SDK
Use workspace monitoring to monitor MCP server use
As you may know, the Lakehouse (Delta Lake) and SQL endpoint are two different engines, and there are differences in the features each engine supports. You can review them here. A common issue I've encountered is writing a Delta table to the Lakehouse and later discovering that a column isn't supported by the SQL Endpoint. TIMESTAMP_NTZ is one example, and an array type column is another.
For example, below I am using a Python notebook with Polars to create a Delta table.
import polars as pl
data = {
"id": [1, 2, 3],
"names": ["Survey1", "Survey2", "Survey3"],
"scores": [[85, 90, 78], [88, 92, 81], [72, 75, 80]]
}
df = pl.DataFrame(data)
df.write_delta('abfss://<>/Tables/dbo/survey')
The Lakehouse shows the table without any errors. Notice the scores column is of type array
However, if I check the SQL Endpoint, survey table shows only two columns, excluding the scores column because it’s not supported.
So I created a utility to compare the two in a Python notebook (you can refactor it to PySpark notebook) and identify the issue early.
notebookutils.data()There are number of different ways to do this. But I also wanted to highlight a recent addition to my favorite notebookutils library. You can now use notebookutils.data to connect to the SQL EP of a Lakehouse or a Warehouse. This only works in a Python notebook. (In Pyspark notebook you can use the Warehouse connector or Semantic Link Labs). In Polars, I am scanning the schema without loading the tables in memory.
import sempy.fabric as fabric
import polars as pl
lakehouse_name = "MyLakehouse"
lakehouse_workspace = "Sales"
lh_workspaceid = fabric.resolve_workspace_id(lakehouse_workspace)
abfs = notebookutils.lakehouse.getWithProperties(lakehouse_name, lh_workspaceid)['properties']['abfsPath']
schemas = [f.name for f in notebookutils.fs.ls(f"{abfs}/Tables")]
conn = notebookutils.data.connect_to_artifact(lakehouse_name, lh_workspaceid, "Lakehouse")
for schema in schemas:
try:
tables = [t.name for t in notebookutils.fs.ls(f"{abfs}/Tables/{schema}")]
for table in tables:
try:
print(f"Table name: {schema}.{table}")
delta_columns = pl.scan_delta(f"{abfs}/Tables/{schema}/{table}").collect_schema().names()
try:
sql_query = f"""
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{table}' AND TABLE_SCHEMA = '{schema}';
"""
result = conn.query(sql_query)
if result is None:
print("❌ SQL query returned None. Check SQL connection and query.")
continue
t_sql_cols = result['COLUMN_NAME'].to_list()
print(f"Delta tables has {len(delta_columns)} columns and SQL EP has {len(t_sql_cols)} columns")
if len(delta_columns) != len(t_sql_cols):
delta_only = set(delta_columns) - set(t_sql_cols)
sql_only = set(t_sql_cols) - set(delta_columns)
if delta_only:
print(f"❌Columns in Delta but not in SQL: {delta_only}")
if sql_only:
print(f"❌Columns in SQL but not in Delta: {sql_only}")
else:
print("✅Columns match")
except Exception as sql_err:
print(f"❌SQL query error: {str(sql_err)}")
print("-"*80)
except Exception as e:
print(f"Error reading table: {schema}.{table}")
print(f"Error details: {str(e)}")
except Exception as e:
print(f"Error reading schema: {schema}")
print(f"Error details: {str(e)}")
Example Output:
Above function will only work for Lakehouse with schemas. If you want a more comprehensive function, use this instead.
To fix above, either un-nest/explode the array or if you want the array (e.g. a dictionary) cast it as string. To fix TIMESTAMP_NTZ, define the timezone in teh timestamp if you are using the delta-lake library (e.g. polars, rust engine). Spark doesn’t have this issue.
At FabCon it was announced that you will soon be able to use a T-SQL cell in a Python notebook and bind the T-SQL result to a pandas df. Currently you can’t mix Python and T-SQL.
SQL EP may not show the latest data if there is a sync issue. Just wait a few minutes in that case or sync manually or programmatically. You can also use it to detect the sync issues.
If you have created a Direct Lake model off of this Lakehouse and see below CannotReteiveModelException error, unsupported data type is likely the culprit. Fixing that and reframing the model should help fix it.
