1
  1. Copying the data from Synapse to Managed Delta table.
  2. We enabled the staging and copied data from synapse to managed delta table.
  3. We have some of the date columns in the synapse and same schema defined in the delta table.
  4. we have designed the simple pipeline using data flows as the source is synapse and sink is delta file, but while converting all the date columns is changing to timestamp at the run time.
Alex Ott
  • 80,552
  • 8
  • 87
  • 132
Venkatesh
  • 91
  • 1
  • 9
  • all the date columns are changing to timestamp from the synapse to the parquet file. How to overcome this issue in the mapping data flows – Venkatesh Oct 30 '21 at 14:08

1 Answers1

2

I experienced a similar problem with parquet files produced by Azure Data Factory data flows being read by Synapse. In my situation, it was with a DATE data type rather than a DATETIME2 data type, but I believe the same problem may apply.

If you enter a date in ADF like toDate('0001-01-01'), the date '0000-12-30' is actually recorded to the parquet file. This is not a legitimate date because there was no year 0 (we went directly from year 1 BC to year 1 AD). The tool determines whether or not this can be read. ADF data flows would happily read it (as '0000-12-30'), but Synapse will issue an error saying "Inserting value to batch for column type DATE failed."

However, if you use toDate('0001-01-03') while writing it, it will be written to the parquet file as '0001-01-01' and Synapse will read it perfectly fine. The cause for this issue is that ADF does not handle the shift from the Julian to the Gregorian calendar accurately beginning in 1582 . This, in my opinion, is a bug. Support for 0001-01-01 HH:mm:ss is the most reasonable way to save a dateless time, thus it would be very welcomed. ToDate('1582-01-01') -> '1582-01-11', yet toDate('1583-01-01') -> '1583-01-01'

The serverless Synapse SQL pool's query Delta Lake format is now in public preview.

Please keep in mind that this preview version is supplied without a service level agreement and is not suitable for production workloads. Certain functions may not be supported or may have limited functionality.

This functionality is under public preview, and various bugs and limits are known. Examine the Synapse serverless SQL pool self-help page for known difficulties.

For further information, see Query Delta Lake files (preview) in Azure Synapse Analytics using a serverless SQL pool.

IpsitaDash-MT
  • 1,326
  • 1
  • 3
  • 7