Forum Discussion
Advocate IITIME Datatype issue while migrating from Synapse to FabricWarehouse
Hi AnmolGan81,
Thanks for the clarification and following up with the conversation and that makes complete sense especially if you're aiming for an automated daily sync process. Modifying the source datatype each time (even virtually through a view or a query cast) isn’t ideal for operational stability.
To answer your question, there’s currently no way to natively migrate TIME(n) from Synapse Dedicated Pool to Fabric Warehouse using Data Pipelines without transforming it, because Fabric pipelines internally use Parquet as the intermediate format. Also parquet doesn't have a direct representation for SQL TIME(n), it maps it to INT64 with logical annotations, and that causes schema mismatches when the destination expects a TIME type in SQL.
So even if the column is technically TIME(2) at source and destination, the Parquet layer in the middle breaks the compatibility.
You can check few possible alternatives which can help you with your case:
* Intermediate Landing Table with VARCHAR (One-Time Setup). Instead of casting at source every time have your pipeline land the data into a staging table in Fabric where the time column is just VARCHAR(12). Then run a post-copy SQL statement (as a second activity) to insert into your final table where the value is cast back to TIME(2).
This avoids touching your source and logic sits entirely in Fabric and this only needs to be configured once.
* If your project allows using Dataflow Gen2 instead of Pipelines for this table. It loads data via native Power Query transformations. You can handle time column formatting more gracefully and avoid low-level Parquet serialization issues.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Hi AnmolGan81,
As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If so, it would be really helpful for the community if you could mark the answer that helped you the most. If you're still looking for guidance, feel free to give us an update, we’re here for you.
Best Regards,
Hammad.
Hi AnmolGan81,
I just wanted to follow up on your thread. If the issue is resolved, it would be great if you could mark the solution so other community members facing similar issues can benefit too. If not, don’t hesitate to reach out, we’re happy to keep working with you on this.Best Regards,
Hammad.
Hi AnmolGan81,
We noticed there hasn’t been any recent activity on this thread. If your issue is resolved, marking the correct reply as a solution would be a big help to other community members. If you still need support, just reply here and we’ll pick it up from where we left off.Best Regards,
Hammad.
AnmolGan81We had to convert the target datatype to varchar and use custom format to migrate from synapse to fabric, seems there is no other option when performing one time migration, after this need to convert it back to timestamp when performing sync using parquet file where datatype is string and inserted back to time datatype.