Data loading limits in Azure Data Factory

I’m retrieving several tables for a client from Exact Online, but I’m running into the configured limits, specifically the daily limit of 50 GB of data traffic. I’d like to find out which tables or columns are generating so much data that I’m hitting this limit.

An example: the table ExactOnlineREST.CRM.Accounts contains a column called Logo, presumably an encoded image. After excluding this column, I was able to resume my data processing. In the meantime, however, I’m hitting the same limit again, presumably due to other heavy tables or columns.

How can I monitor or analyse which tables/columns are responsible for the high data usage? Are there any tools, logging options or best practices to help gain insight into this?

These are the tables I’m currently retrieving:

  • ExactOnlineREST.System.SystemDivisions@eol
  • ExactOnlineREST.CRM.Accounts@eol
  • ExactOnlineREST.Budget.Budgets@eol
  • ExactOnlineREST.Budget.BudgetScenarios@eol
  • ExactOnlineREST.Cashflow.Payments@eol
  • ExactOnlineREST.Cashflow.ReceivablesBulk@eol
  • ExactOnlineREST.Financial.GLAccounts@eol
  • ExactOnlineREST.Financial.Journals@eol
  • ExactOnlineREST.FinancialTransaction.TransactionLinesBulk@eol
  • ExactOnlineREST.Inventory.BatchNumbers@eol
  • ExactOnlineREST.Logistics.Items@eol
  • ExactOnlineREST.Financial.FinancialPeriods@eol
  • ExactOnlineREST.Inventory.AssemblyOrders@eol
  • ExactOnlineREST.Inventory.Warehouses@eol
  • ExactOnlineREST.Purchase.PurchaseInvoiceLines@eol
  • ExactOnlineREST.PurchaseOrder.PurchaseOrderLines@eol
  • ExactOnlineREST.SalesInvoice.SalesInvoiceLines@eol
  • ExactOnlineREST.SalesOrder.SalesOrderLines@eol

I honestly wouldn’t expect these tables combined to come anywhere near 50 GB.

Does anyone have any tips on how I could investigate or monitor this further?

50 GB of network traffic usage is extremely high, especially if it does not concern hundreds of environments. Some recommendations are:

Analysis

It is not known which monitoring tools Azure Data Factory itself offers for network traffic analysis. Therefore, this is described here solely from the perspective of Invantive Cloud. The most commonly used options are:

Monitoring

Many details are available from, for example, the Cloud page “Timeline”. See also:

Session I/Os

The Session I/Os screen also shows the network traffic from Invantive Cloud to Azure Data Factory, alongside the individual API calls.

Note that only the I/Os since 0:00 UTC on the current day are available.

SQL Queries

The processing of a query can be viewed in detail via queries on the data dictionary. See:

Common Optimisations

Compression

When using OData, it is advisable to exchange data in compressed form. Indicate via the standard HTTP header that the data may be sent compressed. See:

This normally saves roughly a factor of 10.

Horizontal Filtering

In general, the advice is to omit fields with large content, such as for relations and articles:

  • ExactOnlineREST.CRM.Accounts@eol
  • ExactOnlineREST.Logistics.Items@eol

Specifically for Twinfield, it is also recommended to skip null values, see also:

Vertical Filtering

In general, it is advisable to apply filters if you believe that Items should be used instead of ItemsIncremental or ItemsBulk.

Note that UniversalSQL supports “server-side filtering”. See, for example:

Filtering can also be applied to the *Bulk tables, although somewhat less effectively than on the non-bulk ones. On the *Incremental tables, a filter will only be applied within UniversalSQL. The engine processes all rows in the table. See also:

For the following tables, you may wish to consider the choice between variants and appropriate filters:

  • ExactOnlineREST.CRM.Accounts@eol
  • ExactOnlineREST.Logistics.Items@eol
  • ExactOnlineREST.Financial.GLAccounts@eol
  • ExactOnlineREST.FinancialTransaction.TransactionLinesBulk@eol
  • ExactOnlineREST.Inventory.BatchNumbers@eol
  • ExactOnlineREST.Inventory.AssemblyOrders@eol
  • ExactOnlineREST.Purchase.PurchaseInvoiceLines@eol
  • ExactOnlineREST.PurchaseOrder.PurchaseOrderLines@eol
  • ExactOnlineREST.SalesInvoice.SalesInvoiceLines@eol
  • ExactOnlineREST.SalesOrder.SalesOrderLines@eol

More optimisations can be found, for example, from:

This question was automatically closed after 1 week of inactivity. The last answer given has been marked as the solution.

If the problem occurs again, please ask a new question via a separate topic. Please include a link to this topic in the new question by pasting its URL into the text.