# Identify Column Mismatch Between Lakehouse Tables And SQL Endpoint Tables In Fabric

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](https://learn.microsoft.com/en-us/fabric/fundamentals/delta-lake-interoperability). 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.

```python
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`

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1744143382015/79c2a901-cc6e-42d4-bd89-4494aaff1f06.png align="center")

However, if I check the SQL Endpoint, survey table shows only two columns, excluding the `scores` column because it’s not supported.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1744143445326/1813ff76-2df9-4a78-aca6-f5d5cdbe2be8.png align="center")

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](https://fabric.guru/querying-sql-endpoint-of-fabric-lakehousewarehouse-in-a-notebook-with-t-sql) or Semantic Link Labs). In Polars, I am scanning the schema without loading the tables in memory.

```python
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:**

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1744143827441/d7f7fdb2-0f97-434d-8663-cbffb6e440ad.png align="center")

Above function will only work for Lakehouse with schemas. If you want a more comprehensive function, [use this instead](https://github.com/pawarbi/snippets/blob/main/compare_delta_sqlendpoint_columns.py).

## Notes

* 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.
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1744218369676/93cd083a-3c43-4238-ab04-feaea89c7707.png align="center")