All accountid's in the contact-table are NULL when using Azure Synapse Link or a Synapse pipeline to parquet

Question

I've tried two ways to copy data from our Dynamics 365 to a Storage Account on Azure:

• Azure Synapse Analytics: creating a pipeline that connects to Dynamics 365 and copies the data the a parquet file on a storage account.
  I tried fetching the data in two ways: with a fetchxml and by just selecting the full table (I assume both are different)
• Azure Synapse Link: Using the link "Azure synapse link" in the make.powerapps website. This is more blackbox: you can select a table and a storage account and the system creates csv-files on the storage account. (and keeps them up-to-date)

In both cases the "accountid" column exists, but for all records it contains NULL-values.
Allthough the contact-table has 311358 records and 98499 records have accountid NULL, and 212859 records have accountid filled in.

I don't know where to look:

• do I need to change something to the table in powerapps?
• do I need to configure something in the Synapse-connection? (Using the parameters or the Advanced json?)
• is there any way to configure something in the Azure Synapse Link?
• ...

Any help is appreciated :-)

Answer 1

@Tim Derdelinckx

Thank you for posting your query!

As I understand that you are facing an issue where the accountid column in the Contact table appears as NULL when exporting data from Dynamics 365 using both Azure Synapse Link and an Azure Synapse pipeline. Since some records in the original data have values, but the exported data does not.

Here are some troubleshooting steps that might help you:

Using Azure Synapse Analytics:

Verify Schema Mapping in Synapse Pipeline

• Open the Copy Activity → Mapping Tab Ensure accountid is correctly mapped from Dynamics 365 → Parquet Auto-mapping might skip lookup fields, so try manually mapping it.
• Ensure Data Type Compatibility accountid in Dynamics is stored as a GUID but should be stored as STRING in Parquet. If there’s a type mismatch, values may silently drop.
• Debug Synapse Pipeline Run the pipeline in Debug Mode Check Data Preview in the source dataset If accountid appears in the preview but is NULL in the output, the issue is likely mapping-related.

I tried fetching the data in two ways: with a fetchxml and by just selecting the full table (I assume both are different)

Which One Should You Use? FetchXML - More control over columns and filters. Needs explicit lookup handling Full Table - Simpler, retrieves all data. May exclude lookup fields like accountid

Recommendation -

• If accountid is missing in the full table selection, try FetchXML with an explicit <attribute name="accountid" />.
• If FetchXML still returns NULL, check security roles in Dynamics 365.

Using Synapse Link:

Check Synapse Link Configuration (PowerApps) -

• Go to Make.powerapps.com → Azure Synapse Link → Edit Contact Table Configuration.
• Ensure accountid is selected for export (sometimes lookup fields are unchecked by default).
• If Synapse Link started after the accountid values were populated, only new/updated records may sync correctly.

Validate FetchXML Query (If Using a Pipeline) -

If you're using FetchXML, ensure it explicitly includes accountid:

<fetch>
  <entity name="contact">
    <attribute name="accountid" />
    <attribute name="fullname" />
  </entity>
</fetch>

If accountid is a lookup, try linking the Account table:

<fetch>
  <entity name="contact">
    <attribute name="accountid" />
    <link-entity name="account" from="accountid" to="accountid" alias="acc">
      <attribute name="name" />
    </link-entity>
  </entity>
</fetch>

This helps confirm if lookup relationships are being ignored.

Test with OData Instead of FetchXML - Retrieve data directly from Dataverse OData API:

https://yourorg.crm.dynamics.com/api/data/v9.2/contacts?$select=contactid,fullname,accountid 

If accountid is NULL in the OData response, the issue is likely with the Dynamics 365 export settings rather than Synapse.

Check Security Permissions

Ensure the integration account (Service Principal or Managed Identity) has Read permissions on accountid - Go to Advanced Settings → Security → Security Roles Open the assigned role Find Contact Entity → Ensure accountid (lookup field) has Read access.I hope this information helps. Please do let us know if you have any further queries.

I hope this information helps. Please do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.