Experimenting with Copy Job. Yeah, OK, but not great. Some suggestions:
1) Make the option for OVERWRITE available for Warehouse destinations. Currently available for Lakehouses only. This article (What is Copy job in Data Factory - Microsoft Fabric | Microsoft Learn) does not differentiate that option. Honestly, how hard it is to program in a DROP TABLE IF EXISTS statement in the back end code? Warehouses support such actions.
2) Give the user the abiltiy to DISABLE a table in the list. If I have a list of 50 tables in my job, but during development only want to refresh 2 of them, I have to run the whole job. Waste of time and resources.
12 Comments
- ToddChitt
Super User
(this one is listed under a separate Idea:) 3) Allow the Copy Job INCREMENTAL to use a TIMESTAMP or ROWVERSION SQL data type as the watermark column. This data type column is ABSOLUTELY the best type for watermarks. It is: a) immutable, b) non-null always, c) system managed, and d) immune to updates from user processes, and finally, e) managed by the SQL engine (no triggers or app code required to keep it updated). The Azure Data Factory Metadata Driven Copy Wizard is a precursor to the Fabric Copy Job and even THAT did not support TIMESTAMP or ROWVERSION types as watermark. But I have been able to hack those pipelines and Control JSON to make it work. Think about it, this is what this datatype was BORN to do! And conceived and delivered by Microsoft in the SQL engine 20 or so years ago. Yet the Fabric people seem to shun it. So disappointing! - ToddChitt
Let's add another one: 4) When copying FROM as SQL Server database TO a Lakehouse (that already has the dbo schema available), AND the source tables are already IN the dbo schema, please DO NOT default the destination table names to [dbo].[dbo_<table name>]. Yes, I know I can manually edit the destination table names, but it has to be done for EVERY SINGLE table.
Miwa1Microsoft Employee
Thanks for your feedback! (3) is already supported, you should see it working by end of Dec 2025 We are evaluating (1) & (2). For (4), during my testing, I pick Azure SQL as source & Lakehouse (with schema support: https://blog.fabric.microsoft.com/en-US/blog/lakehouse-schemas-generally-available/) as destination. They all have a dbo schema, and UI correctly generated the destination table & schema in 2 separate input boxes. Did I miss anything?
- ToddChittI think you missed the fact that a Copy Job can also send data to a Lakehouse that does NOT have schema support. Recall that lakehouses with schema support are relatively new, and there are already a LOT of lakehouses out there that do NOT support schemas. Please test again against a legacy lakehouse (NON schema support) and tell me what you get. Is your result acceptable?
- Miwa1
I reproduced the behavior that you encountered. I agree that for destination data stores that don't support schema, we should not prefix schema in the auto-generated table name. We will enhance UI and let you know when it's ready, thanks!
- ToddChittI went to EDIT a Copy Job that sourced from Excel, and the space granted for me to navigate the Lakehouse Files hierarchy was so small I could not even read a single file name, let alone scroll up and down. It would be like trying to steer your car, know where you are by staring down through a hole in the floorboards between your feet! There is TONS of real estate in the dialog box, but the space is just not laid out very well.
- ToddChittMiwa1 Copy Jobs are great for keeping Fabric data stores (lakehouse / warehouse) synchronized from a source system, of which there are many. I see that TIMESTAMP is now available as an Incremental Column. Great. Testing it out, I configured my Copy Job to run every 15 minutes. But the run history or schedule do NOT show up in the main Fabric Workspace view, under columns of "Refreshed" and "Next refresh". Pipeline schedules don't show up there either. Please add these run tracking properties for ALL objects that can be scheduled.
- I came here for this idea... it would be nice to disable a table or 10 when I need to only full load one or two tables that are in a full copy job. "Give the user the abiltiy to DISABLE a table in the list. If I have a list of 50 tables in my job, but during development only want to refresh 2 of them, I have to run the whole job. Waste of time and resources."
- ToddChittHere's another issue: I spun up a simple Copy Job to bring in ONE table from an ODBC source because the original mechanism responsible for loading the data wasn't doing its job. To protect from hosing up the table in question (in a Warehouse) I decided that I would append the table name with "_CopyJob" to differentiate between the two. The original source AND destination table are named [PUB].[oeeh]. So I set the new destination table name as [oeeh_CopyJob]. But what I ended up with was a table in the dbo schema named, ["PUB"."oeeh_CopyJob"]. I didn't look closely enough at the Destination Mapping, which DEFAULTED by table to the [dbo] schema, with a table name of "Pub"."oeeh". Come ON, Microsoft. PLEASE READ THE SOURCE SCHEMA!!! There is a Destination Schema OF THE SAME NAME. Figure it out and use it by default!
- ToddChittThe documentation on this item: (https://learn.microsoft.com/en-us/fabric/data-factory/what-is-copy-job) mentions this: You can also optionally truncate destination data before the full load, ensuring your source and destination are fully synchronized without duplicates. By default, Copy job doesn't delete any data in destination. When you enable this option: The first run of incremental copy will truncate all data in the destination before loading the full dataset. Why is there NOT an option to TRUNCATE on a FULL load? This is a basic requirement. I'm willing to bet that when they were developing this thing, they worked on the FULL load part of it first. If I were the PM overseeing this, IT WOULD NOT HAVE BEEN MARKED COMPLETE UNTIL A TRUNCATE OPTION WAS MADE AVAILABLE TO THE USER!!!
