Troubleshoot ingestion errors or corrupt data

Article
06/03/2024

Note

On September 1, 2023, we merged and renamed Dynamics 365 Marketing and Dynamics 365 Customer Insights. Dynamics 365 Marketing is now called Dynamics 365 Customer Insights - Journeys. Dynamics 365 Customer Insights is now called Dynamics 365 Customer Insights - Data. For more information, see Dynamics 365 Customer Insights FAQs.

This article introduces common reasons for data ingestion errors or corrupt data when using Azure Data Lake Storage or Power Query in Microsoft Dynamics 365 Customer Insights - Data.

Ingestion errors or corrupt data with Azure Data Lake Storage

During data ingestion, some of the most common reasons a record might be considered corrupt include:

The data types and field values don't match between the source file and the schema.
The number of columns in the source file doesn't match the schema.
Fields contain characters that cause the columns to skew compared to the expected schema. For example, incorrectly formatted quotes, unescaped quotes, newline characters, or tabbed characters.
Partition files are missing.
datetime, date, or datetimeoffset fields don't follow the standard format.

Schema or data type mismatch

If the data doesn't conform to the schema, the ingestion process completes with errors.

To solve this issue, correct either the source data or the schema and re-ingest the data.

Partition files are missing

If the ingestion process is successful without any corrupt records, but you can't see any data, edit your model.json or manifest.json file to make sure partitions are specified. Then, refresh the data source.
If data ingestion occurs at the same time as data sources are being refreshed during an automatic schedule refresh, the partition files might be empty or unavailable to the system process. To align with the upstream refresh schedule, change the system refresh schedule or the refresh schedule for the data source. Align the timing so that refreshes don't all occur at once.

Datetime fields are in the wrong format

The datetime fields in the table aren't in the ISO 8601 or en-US format. The default datetime format in Dynamics 365 Customer Insights - Data is en-US. All the datetime fields in a table should be in the same format. Customer Insights supports other formats provided annotations or traits are made at the source or table level in the model or manifest.json. For example:

Model.json

  "annotations": [
    {
      "name": "ci:CustomTimestampFormat",
      "value": "yyyy-MM-dd'T'HH:mm:ss:SSS"
    },
    {
      "name": "ci:CustomDateFormat",
      "value": "yyyy-MM-dd"
    }
  ]

In a manifest.json file, the datetime format can be specified at the table level or attribute level. At the table level, use "exhibitsTraits" in the table in *.manifest.cdm.json to define the datetime format. At the attribute level, use "appliedTraits" in the attribute in tablename.cdm.json.

Manifest.json at the table level

"exhibitsTraits": [
    {
        "traitReference": "is.formatted.dateTime",
        "arguments": [
            {
                "name": "format",
                "value": "yyyy-MM-dd'T'HH:mm:ss"
            }
        ]
    },
    {
        "traitReference": "is.formatted.date",
        "arguments": [
            {
                "name": "format",
                "value": "yyyy-MM-dd"
            }
        ]
    }
]

table.json at the attribute level

   {
      "name": "PurchasedOn",
      "appliedTraits": [
        {
          "traitReference": "is.formatted.date",
          "arguments" : [
            {
              "name": "format",
              "value": "yyyy-MM-dd"
            }
          ]
        },
        {
          "traitReference": "is.formatted.dateTime",
          "arguments" : [
            {
              "name": "format",
              "value": "yyyy-MM-ddTHH:mm:ss"
            }
          ]
        }
      ],
      "attributeContext": "POSPurchases/attributeContext/POSPurchases/PurchasedOn",
      "dataFormat": "DateTime"
    }

Ingestion errors or corrupt data with Power Query

Datetime values are parsed incorrectly or a parsing failure occurs

The most common data type mismatch occurs when a date field isn't set to the correct date format. This mismatch can be caused by the incorrectly formatted source data or an incorrect locale.

Symptoms of the incorrect locale issue:

When the source data can't be parsed by the locale used, an ingestion failure occurs. For example, if "29/08/2023" is parsed with "MM/DD/YYYY," the ingestion fails because it can't parse month 29.
When the source data is parsed successfully using an incorrect locale, the datetime values are incorrect. For example, the source data is formatted as "MM/DD/YYYY," while the default locale used to parse the data during ingestion uses "DD/MM/YYYY." As a result, "December 8, 2023" is ingested as "August 12, 2023".

Resolution

To fix an incorrect format, update the source data and re-ingest.
To fix an incorrect locale, change the type of all datetime fields to use the correct locale using Change type > Using locale in the Power Query transformations. For example:

For more information, see Document or project locale.

Share via