topic Re: Foreign Catalog Wrong Mapping - Azure SQL Database Binary Column in Data Engineering

Foreign Catalog Wrong Mapping - Azure SQL Database Binary Column

RobsonNLPT — Sat, 27 Sep 2025 15:20:41 GMT

Hi all.

I've used foreign catalog attached to azure sql databases and never had problems except in 2 situations:

1) Foreign Catalogs don't support sql schemas/objects like [xxxx.yyyy].tablename. The workaround is creating views on sql database

2) This is new issue: When I try to read a table from a foreign catalog, specifically a Binary column serialized as base64(a representation of a zip file) the resultset returns a truncated and wrong value and then I can't decode.

This is the second issue with data mappings. The first one was trying read a json column from google bigquery table and now this problem reading sql varbinary column(mapped to binary in databricks).

Using data factory I can read the sql database and copy to parquet files. It works but it should be done using only databricks.

Any help?

Re: Foreign Catalog Wrong Mapping - Azure SQL Database Binary Column

Isi — Sat, 27 Sep 2025 20:58:02 GMT

Hello @RobsonNLPT

One thing that might help to narrow this down: could you check whether the problem occurs for the entire column, or if some of the batches you receive actually contain the full (non-truncated) value?

If some batches are complete but others are cut off, it could mean the connector hit a buffer or batch size limit and decided to truncate the value instead of failing with an out-of-memory error.

You might also try a controlled test with smaller binary values (for example, shorter base64-encoded ZIPs) to see if they come through without issues. If smaller payloads work fine but larger ones get truncated, that would confirm the problem is related to size limits in the federation connector...

I understand that these things can sometimes be frustrating 😕

Hope this helps 😕
Isi

Re: Foreign Catalog Wrong Mapping - Azure SQL Database Binary Column

RobsonNLPT — Sun, 28 Sep 2025 10:45:40 GMT

Hi. I've tested multiple scenarios. It never works. Also limitations should be officially informed is applicable. Unacceptable. Like I said the same occurs with Google BigQuery and some data types including Json types.