*.Wildcard Select in Synapse View on customized table can shift results set to wrong column

Combining views and wildcard selects on customized tables in Synapse may cause unforeseen problems. For example, I’ve created a view that combines all fields one table with additional fields from a joined table.

SELECT table1.*, table2.field1, table2.field2, table3.field3
from
table1 left outer join table2 on
table1.id = table2.id and table1.dataareaid = table2.dataareaid

In Synapse Workspace I’ve added a SQL Database called “Dynamics” and create the view. PowerBI Users would use the views from the Dynamics database instead of loading and joining raw tables by themselves.

A customization was made on table1. An additional field was added. The new field was synced to Synapse. Querying table1 in Synapse returned all its fields. Executing the SQL query on which the view is based als returned all the fields from table1 and the joined table2. However, the view itself in the Dynamics database in Synapse did not show the new field in the returned column headers.

The Lake Database in Synapse returned correct dataset but the view used its original field definition. So the data was shifted 1 field to the right and all values after the customized field were in the wrong column. For example, the value 1 from the new field was returned in the column Partition. The value 2024 from the Partition was returned as Field1. The value from Field1 was returned in Field2.

A quick and simple fix is to rerun the ALTER VIEW statement with the original code.