Can't insert external table data into table in synapse dedicated sql pool

Question

In Synapse dedicated pool, I'm trying to create an external table, and a dedicated table, and then insert the external table into the dedicated table but I keep getting the following error:

Explicit conversion from data type bigint to date is not allowed.

These are my create and insert statements below:

External table

CREATE EXTERNAL TABLE [gold].[ExternalTable]
(
 [Column1] [DATE] NULL
,[Column2] [DATE] NULL
,[Column3] [DATE] NULL
,[Column4] [DATETIME2] NULL
,[Column5] [DATETIME2] NULL
,[Column6] [SMALLINT] NULL
,[Column7] [BIGINT] NULL
,[Column8] [BIGINT] NULL
,[Column9] [BIGINT] NULL
,[Column10] [VARBINARY] (8000) NULL
,[Column11] [VARCHAR] (8000) NULL
,[Column12] [VARCHAR] (8000) NULL
,[Column13] [VARCHAR] (8000) NULL
,[Column14] [VARCHAR] (8000) NULL
,[Column15] [VARCHAR] (8000) NULL
,[Column16] [VARCHAR] (8000) NULL
,[Column17] [VARCHAR] (8000) NULL
,[Column18] [VARCHAR] (8000) NULL
,[Column19] [VARCHAR] (8000) NULL
,[Column20] [VARCHAR] (8000) NULL
,[Column21] [VARCHAR] (8000) NULL
,[Column22] [VARCHAR] (8000) NULL
,[Column23] [VARCHAR] (8000) NULL
,[Column24] [VARCHAR] (8000) NULL
,[Column25] [VARCHAR] (8000) NULL
,[Column26] [VARCHAR] (8000) NULL
,[Column27] [VARCHAR] (8000) NULL
,[Column28] [VARCHAR] (8000) NULL
,[Column29] [VARCHAR] (8000) NULL
)
WITH (DATA_SOURCE = [gold_dbx], LOCATION = N'/Path/To/Parquet/', FILE_FORMAT = [ParquetFormat], REJECT_TYPE = VALUE, REJECT_VALUE = 0 );

Dedicated table

CREATE TABLE [gold].[Table]
      (
 [Column1] [DATE] NULL
,[Column2] [DATE] NULL
,[Column3] [DATE] NULL
,[Column4] [DATETIME2] NULL
,[Column5] [DATETIME2] NULL
,[Column6] [SMALLINT] NULL
,[Column7] [BIGINT] NULL
,[Column8] [BIGINT] NULL
,[Column9] [BIGINT] NULL
,[Column10] [VARBINARY] (8000) NULL
,[Column11] [VARCHAR] (8000) NULL
,[Column12] [VARCHAR] (8000) NULL
,[Column13] [VARCHAR] (8000) NULL
,[Column14] [VARCHAR] (8000) NULL
,[Column15] [VARCHAR] (8000) NULL
,[Column16] [VARCHAR] (8000) NULL
,[Column17] [VARCHAR] (8000) NULL
,[Column18] [VARCHAR] (8000) NULL
,[Column19] [VARCHAR] (8000) NULL
,[Column20] [VARCHAR] (8000) NULL
,[Column21] [VARCHAR] (8000) NULL
,[Column22] [VARCHAR] (8000) NULL
,[Column23] [VARCHAR] (8000) NULL
,[Column24] [VARCHAR] (8000) NULL
,[Column25] [VARCHAR] (8000) NULL
,[Column26] [VARCHAR] (8000) NULL
,[Column27] [VARCHAR] (8000) NULL
,[Column28] [VARCHAR] (8000) NULL
,[Column29] [VARCHAR] (8000) NULL
)
)WITH
                 (
                            distribution = HASH(DataHash),
                            CLUSTERED columnstore INDEX
               )  


Insert INTO [gold].[Table]
SELECT * FROM [gold].ExternalTable]

Edit: I'm noticing that if I try to just SELECT the external table I'm getting the same issue. I'm guessing the problem is between converting the parquet file into an external table. Anyone experienced this before?

Answer 1

Issue was in the order of the columns. I was creating a parquet file from a delta table in DBX and then creating an external table from this parquet file. To fix this I explicitly called out the columns when creating the parquet file instead of doing a SELECT * FROM