从Synapse同步数据至Databricks托管Delta表时日期列自动转为timestamp的问题咨询

I’ve run into this exact issue before when moving data from Synapse to Delta Lake with staging enabled—here are the concrete fixes that worked for me:

1. Explicitly Lock Down Column Types in Projections #

Data Flow’s automatic type inference can get tripped up, especially with staging in the mix. Here’s how to enforce date types end-to-end:

• Synapse Source: Go to the Projection tab, locate your date columns, and manually set their data type to date (don’t leave it as auto-inferred). This tells the flow to treat those columns as pure dates from the start.
• Delta Sink: Switch to the Projection tab here too, and mirror the source’s date type assignments. Disable any "auto-map" or "infer schema" options that might override this explicit setting.

2. Adjust Staging Storage Format Settings #

If you’re using Parquet for staging (the default for many setups), note that Parquet doesn’t have a native date type—it stores dates as INT96 values, which Data Flow often interprets as timestamps. To work around this:

• Either switch your staging format to CSV in the Data Flow’s staging configuration (CSV handles date strings more reliably if you set the correct format), or
• In the Delta Sink’s Optimize settings, enable Force Schema to ensure the Delta table’s predefined date schema takes precedence over any intermediate type conversions from staging.

3. Add a Derived Column Step for Forced Conversion #

If the above steps don’t fully resolve it, insert a Derived Column transformation right before the sink:

• For each problematic column, use the expression toDate(your_timestamp_column) to explicitly cast the timestamp back to a date.
• Replace the original column with this derived value—this acts as a final guardrail to ensure the correct type reaches the Delta table.

4. Double-Check Delta Table Schema #

Even if you think the schema matches, run this command in your Spark pool to confirm the Delta table’s columns are indeed date type:

DESCRIBE TABLE your_delta_table_name;

If any date columns are listed as timestamp, alter the table schema first before running the Data Flow:

ALTER TABLE your_delta_table_name ALTER COLUMN your_date_column TYPE date;

Quick Note #

Staging adds an extra layer of data handling, so always verify the intermediate storage’s type behavior—this is usually where the implicit conversion happens. Explicit type definitions at every step are the most reliable way to avoid this kind of issue.

内容的提问来源于stack exchange，提问作者Venkatesh