0

Source: Query on a Teradata table

Sink: Csv file in Azure Blob Storage

Aim: I want the Copy activity to fail if the count in the source query and copied rows do not match.

Looking at the documentation for Supported data stores and scenarios, it does seem that the copy will fail (this is our aim) if the source to target row count does not match and I do not set anything in Fault Tolerance like skip incompatible rows (Pic for reference)

When copying a table in single copy activity with data consistency verification enabled, copy activity fails if the number of rows read from the source is different from the number of rows copied to the destination plus the number of incompatible rows that were skipped.

Microsoft documentation.

However, I am unable to check this for sure as it is not possible for me to change data at the source for testing purposes. Hence, my queries:-

  • It mentions "tables". Want to ensure that Teradata tables are also covered and not just Azure resources like Sql DB.
  • It mentions "table" as a source. Would it also fail similarly if I have a query instead of the entire table in source for Copy activity? (Pic for reference).
  • If I do NOT select any "Fault Tolerance", am I correct to understand that it will not skip any row or any other exceptions, and hence do a row count for everything?

Different sources would act differently (e.g. Binary copy, csv copy, etc) but I am mainly concerned about Teradata query as source and csv in Azure Blob as sink.

Appreciate any suggestion. Thanks in advance for your time. Source

Sink

Data Consistency selections

Fault Tolerance options

Nirupam Nishant
  • 209
  • 3
  • 10

1 Answers1

0

I tried to reproduce the above but not successful with the Consistency option enabled.

Aim: I want the Copy activity to fail if the count in the source query and copied rows do not match

Instead, you can try this user defined workaround for this (in this case).

First copy your source to a temporary table, then check whether the rows read and copied are same or not in If activity.

enter image description here

In if activity expression, give the below condition.

@equals(activity('Copy data1').output.rowsRead,activity('Copy data1').output.rowsCopied)

enter image description here

In True activities of if, give your copy activity from source to target.

enter image description here

In Failed activities of if, give a fail activity.

enter image description here

In this approach, If the rows from source to a temporary table in target type are not equal, then the pipeline fails and prevents the copy to your original target table.

I have checked the above approach with the query from source and it is working for that as well.

If it results true, then the same number of rows from same source can be copied to your target table.

Rakesh Govindula
  • 5,257
  • 1
  • 2
  • 11
  • Hey @Rakesh. Thanks for the suggestion. I was mainly trying to take benefit of the inbuilt capability of COPY activity so that I do not have to implement the extra manual check. Ideally, turning on the Data Consistency check in COPY activity should do the same task as you have done manually as a workaround. However, till date I have not been come across an error message. https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-data-consistency#supported-data-stores-and-scenarios – Nirupam Nishant Oct 31 '22 at 08:45