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

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

FrancescoPwc — Fri, 05 Jul 2024 11:05:04 GMT

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?

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

Anonymous — Mon, 08 Jul 2024 03:00:04 GMT

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.

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

FrancescoPwc — Mon, 08 Jul 2024 07:03:19 GMT

Hi Anonymous ,

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?

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

miguel — Mon, 08 Jul 2024 18:17:28 GMT

FrancescoPwc I'd probably start with what Anonymous 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

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

FrancescoPwc — Wed, 10 Jul 2024 09:49:33 GMT

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: