Synapse, error inserting data into table with materialized view

Question

Setting the stage:

When I insert data into a table with a specific materialized view, I get the following error:

Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'Dimension Code' in table 'QTable_394f9354e5c5439e8e6e5d2d47a7e97a_55' is specified more than once.

The table looks like this:
CREATE TABLE [History].[DimensionValue_PYT]
(
[Country] nvarchar NULL,
[Dimension Code] nvarchar NULL,
[Code] nvarchar NULL,
[Name] nvarchar NULL,
[cycle_id] [int] NULL,
[src_company_name] nvarchar NULL,
[SYS_CHANGE_VERSION] [bigint] NULL,
[SYS_CHANGE_OPERATION] char NULL,
[timestamp] [bigint] NULL
)
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX
)

The materialized view looks like this:

CREATE MATERIALIZED VIEW [Current_MAT].[DimensionValue_PYT_MAX1]
WITH ( DISTRIBUTION =  HASH (Code), FOR_APPEND)
AS SELECT  [Country]
      ,[src_company_name]
      ,[Dimension Code]
      ,[Code]
      , MAX(cycle_id * 10000000000000 + [SYS_CHANGE_VERSION]) AS Cycle_Change
      , count_big(*) AS cb 
FROM [History].[DimensionValue_PYT] 
GROUP BY 
    [Country]
    ,[src_company_name]
    ,[Dimension Code]
    ,[Code]

I have another materialized view on the same table, but it is not causing the same issues. When I drop the above materialized view, then I can insert data into the underlying table. With the above materialized view, I'm getting the error in the top.

Any ideas to why this is happening?

Cheers
Peter

Answer 1

Found the issue.

Behind the scene Synapse creates a temp table that is used for inserting the data into the table backing the materialized view.

For some of these temp tables, the standard on field names is not respected. If basically can't work out, the the name is [Dimension Code] but for whatever reason creates the field twice (most likely because of the space in the name).

When removing the space in the [Dimension Code] field (to [DimensionCode] ) I no longer get the error.

Recommendation: Avoid spaces in field names, even though they are syntactical valid.