Problem 

When using the Databricks Lakehouse Federation feature to fetch data stored in Azure Synapse Analytics (formerly SQL Data Warehouse) – specifically when running a select query against tables under the schema – you receive an error message stating Invalid object name 'dbo.date' even though the table metadata is visible via the Catalog UI. 

Cause

This issue arises when the collation for the database is set to “Japanese_BIN2” in the dedicated SQL pool (formerly SQL DW). In this case, Azure Synapse Analytics treats the query pushdown request as case-sensitive. Consequently, if the original table name in the source has a combination of uppercase and lowercase letters, the table name is loaded as lowercase in the Lakehouse Federation.

Solution

Set the collation for the database in the dedicated SQL pool to the default, "SQL_Latin1_General_CP1_CI_AS". 

If changing the collation is not feasible, ensure that all table names in the source database are in lowercase to match the case used in the Lakehouse Federation.

For more information, please review query federation limitations in the What is Lakehouse Federation? (AWS | Azure | GCP) documentation.