An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
The evidence points to the PostgreSQL V2 connector or SHIR numeric decoding path rather than the Copy activity mapping. The incorrect value is already present in Preview data, while bedrag::text returns the correct value from PostgreSQL.
PostgreSQL 17 is within the connector’s documented support range of PostgreSQL 12 and later. However, the documentation does not identify this decimal-scaling behaviour as a known issue, publish a fix schedule, or list a SHIR version confirmed to work with PostgreSQL 17.8. Therefore, I would not recommend a specific downgrade based only on the earlier SHIR 5.55 and 5.64 reports. Those reports demonstrate similar behaviour, but they do not validate a particular version against PostgreSQL 17.8. PostgreSQL V2 connector documentation [learn.microsoft.com], [learn.microsoft.com], [learn.microsoft.com]
For the production migration, a safer temporary mitigation is to bypass numeric decoding for the affected columns:
SQL
SELECT "bedrag"::text AS "bedrag_text", scale("bedrag") AS "bedrag_scale" FROM public."fJournaalpost";
Land bedrag_text in a staging varchar column and convert it in Synapse using an explicit target definition, for example:
SQL
TRY_CONVERT(decimal(38, 4), bedrag_text)
Choose the destination scale based on the business definition rather than assuming scale 4 for every column. Since the PostgreSQL columns are declared as unconstrained numeric, retaining the text value in staging also prevents unintended loss of precision or scale.
Before the cutover, validate this workaround with boundary cases such as:
- Positive and negative decimals
- Zero and
NULL - Values with different scales
- Large financial amounts
- Values such as
0.0001,123.45and-772.16
A source-to-staging reconciliation using the text representation and row-level counts would provide an additional control against financial corruption.
For the 2 ques:
- I briefly wandered through thte web and I can say that cannot find a public Microsoft confirmation for SHIR 5.66.9620.1 with PostgreSQL 17.8 or a published fix schedule.
- No publicly documented SHIR version is confirmed for this exact PostgreSQL 17.8 scenario. Test any candidate version on a separate SHIR node with automatic updates disabled before using it for production. Microsoft documents that SHIR versions are released regularly, can be managed manually, and expire after one year. SHIR update and expiration documentation [learn.microsoft.com]
Help make this community better for everyone: if this answer resolved your issue, please accept it or leave an upvote. If not, share more details in a comment so we can continue the discussion and find the right solution