[Fabric] Dataflow Gen 2: Write from CSV to Delta partitioned Table fails

[FrancescoPwc]

I set up a Dataflow where I read from a csv, add a column X_DATE_REF valued "2024-07-05" and write to a previously created table in lakehouse.

The table is partitioned by column X_DATE_REF STRING. 

The Dataflow gives an error after running it:

WriteToDataDestination: Mashup Exception Data Format Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataFormat.Error: We don't support inserting records into a partitioned Delta Lake table when the records might end up in more than one partition. Details: Reason = DataFormat.Error;Microsoft.Data.Mashup.Error.Context = User

I am sure my input data shouldn't end in different partitions because I personally set the column to a fixed value. What is the matter?

[FrancescoPwc]

Hi @v-kongfanf-msft ,

X_DATE_REF is a an added column and it is not present in the csv.

Here is the code of the dataflow:

let
Source = Lakehouse.Contents([]),
#"Navigation 1" = Source{[workspaceId = "xxxx-xxxx-41bf-xxxx-xxxxx"]}[Data],
#"Navigation 2" = #"Navigation 1"{[lakehouseId = "xxxx-xxx-xxxx-a6b7-xxxx"]}[Data],
#"Navigation 3" = #"Navigation 2"{[Id = "Files", ItemKind = "Folder"]}[Data],
#"Navigation 4" = #"Navigation 3"{[Name = "DataSources"]}[Content],
#"Navigation 5" = #"Navigation 4"{[Name = "input_csv.csv"]}[Content],
#"Imported CSV" = Csv.Document(#"Navigation 5", [Delimiter = ";", Columns = 38, QuoteStyle = QuoteStyle.None]),
#"Promoted headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars = true]),
#"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"Promoted headers", "X_DATE_REF", each DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd")), {{"X_DATE_REF", type text}})
in
#"Added custom"

Any suggestions?

[miguel]

@FrancescoPwc  I'd probably start with what @v-kongfanf-msft  suggested. Ensure that the table schema of your query matches the table schema of your destination table. It could be that your lakehouse table expects a date (or other data type) and you're trying to insert a text. Check the definition of your Lakehouse table to see what the table schema is

[FrancescoPwc]

Hi  @miguel ,

the schema of the lakehouse table is as follow (every field is string):

{
"col_name": "X_DATE_REF",
"data_type": "string",
},
{
"col_name": "DATA_RIFERIMENTO",
"data_type": "string",
},
{
"col_name": "TIMESTAMP_ESTRAZIONE",
"data_type": "string",
},
{
"col_name": "ABI_BANCA",
"data_type": "string",
},
{
"col_name": "SERVIZIO",
"data_type": "string",
},
{
"col_name": "RAPPORTO",
"data_type": "string",
},
{
"col_name": "CAG_CLIENTE",
"data_type": "string",
},
{
"col_name": "CAG_RISCHIO",
"data_type": "string",
},
{
"col_name": "CAG_GRUPPO",
"data_type": "string",
},
{
"col_name": "CAG_GRUPPO_RISCHIO",
"data_type": "string",
},
{
"col_name": "COD_SAE",
"data_type": "string",
},
{
"col_name": "COD_ATECO",
"data_type": "string",
},
{
"col_name": "COD_FINREP",
"data_type": "string",
},
{
"col_name": "COD_TIPO_IMMOBILE_GARANZIA",
"data_type": "string",
},
{
"col_name": "COD_DEST_ECONOMICA_INVEST",
"data_type": "string",
},
{
"col_name": "FLAG_PICCOLA_MEDIA_IMPRESA",
"data_type": "string",
},
{
"col_name": "COD_TIPO_OPERAZIONE",
"data_type": "string",
},
{
"col_name": "COD_TIPO_GARANZIA",
"data_type": "string",
},
{
"col_name": "FLAG_ESCUSSIONE_IN_CORSO",
"data_type": "string",
},
{
"col_name": "IMP_SALDO_IAS_FINE_MESE",
"data_type": "string",
},
{
"col_name": "DATA_SCADENZA_MUTUO",
"data_type": "string",
},
{
"col_name": "DATA_SCADENZA_GARANZIA",
"data_type": "string",
},
{
"col_name": "NUM_MESI_SCAD_RESIDUA_GARAN",
"data_type": "string",
},
{
"col_name": "IMP_ESCUSSIONE_PERIODO",
"data_type": "string",
},
{
"col_name": "COD_GRADO_RISCHIO_FINE_MESE",
"data_type": "string",
},
{
"col_name": "NUM_GIORNI_SCADUTO_FINE_MESE",
"data_type": "string",
},
{
"col_name": "FLAG_RAPP_CONC_FORBORNE",
"data_type": "string",
},
{
"col_name": "COD_STAGE_IFRS9",
"data_type": "string",
},
{
"col_name": "IMP_SVALUTAZ_IMPAIRMENT",
"data_type": "string",
},
{
"col_name": "IMP_IMPATTI_VOCE140_PERIODO",
"data_type": "string",
},
{
"col_name": "FLAG_PRESENZA_GARANZIA_COVID",
"data_type": "string",
},
{
"col_name": "IMP_GARANZIA_COVID",
"data_type": "string",
},
{
"col_name": "PERC_COPERTURA_GARANZIA",
"data_type": "string",
},
{
"col_name": "DATA_INGRESSO_DEFAULT",
"data_type": "string",
},
{
"col_name": "DATA_PRIMA_LIQUIDAZIONE",
"data_type": "string",
},
{
"col_name": "IMP_INTERESSI_PERIODO_IAS",
"data_type": "string",
},
{
"col_name": "IMP_INTERESSI_ANNO",
"data_type": "string",
},
{
"col_name": "IMP_SPESE_RATA_DATA_RIF_ANNO",
"data_type": "string",
},
{
"col_name": "IMP_RISK_WEIGHTED_ASSETS",
"data_type": "string",
}

Also in Dataflow every field in the preview is a string:

[v-kongfanf-msft]

Hi @FrancescoPwc ,

Although you mentioned setting columns to fixed values. However, based on the error reporting information you provided, it appears that one of your conversions has a changed type causing the data type to report an error. This is why you are getting the following error: DataFormat.Error;Microsoft.Data.Mashup.Error.Context = User.

You can troubleshoot the error in several ways as follows:

• Ensure that the column X_DATE_REF in the CSV is of the same data type as in the table schema and is set to STRING correctly.
• Verify the conversion in Dataflow to ensure that no other operation has accidentally changed the value of column X_DATE_REF.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.