Forum Discussion
[Fabric] Dataflow Gen 2: Write from CSV to Delta partitioned Table fails
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?
4 Replies
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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?
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
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:
