Hey, Cloud Gurus.
Welcome to our lesson on configuring error handling
for transformations.
In this lesson, we'll talk a little bit about the need
for error handling and how we go about handling those errors
in our pipeline transformations.
We'll do a demo in Azure Data Factory,
and wrap everything up with a review.
To start off with, let's talk about the fact
that errors just happen.
They're a common part of data work
and, really, IT in general.
And yes, while it's sad, the question's not
whether there will be errors,
but what you're going to do with them.
For our transformations, you'll run into a few common types,
such as, data is truncated.
So when you're bringing the data over,
part of it is truncated when you don't really want it to be,
you can't insert NULL values into particular columns,
and maybe you run into constraint conflicts.
Each of these can be troubling,
but there are ways around them.
By default, your transformations are going to fail
on the first error.
However, you can select continue on error.
This is your first and usually best option.
Once you enable this, a few other options open up to you,
including transaction commit.
This is where you choose whether to write data
in a single transaction or in batches.
A single transaction is going to have worse performance,
but it keeps the data from being visible to others
until the transaction is complete.
So if you run into issues,
people aren't running off of inaccurate data.
You can also choose to output rejected data.
This sends error rows to a CSV file in Azure Storage,
including the SQL operation and error information,
such as the data flow error code.
and the error message on the row.
And finally, you can choose success on error,
where you simply mark it as successful,
whether an error occurs or not.
There are times when you may expect errors,
or having an error isn't very consequential to your process.
And so you can tell it to continue on its merry way,
and not interrupt what you're doing,
or if you know what the behavior is going to be,
you can mitigate it in some way.
These options are going to cover the majority
of your error handling situations.
However, you do also have the conditional split
available to you.
And this is for those times when you need
to go beyond the automated catch-all of continue on error.
You can use custom logic with specialized conditions
to route bad rows to logging,
and then continue on successfully.
So for example,
if we have our source and we run our transformation,
we can take the successful or good rows,
and send them on to Azure SQL Database,
or we can take the bad rows and log
into a CSV file in our Azure Data Lake.
Let's jump over to the portal and take a look
at setting the error handling features
that we just discussed.
Here we are an Azure Data Factory Studio,
and I've already pulled up one of the data we worked with
in a pipeline earlier, our NightlyLoad pipeline.
On our product average sync, if we come over
to settings, all the way at the bottom,
this is where we find our error row handling settings.
If we expand that and scroll some more,
you can see that by default,
it's set to stop on first error.
We can change that to continue on error.
And then we receive our additional options.
We have transaction commit, as we said earlier,
and that can be batch or single.
We can output our rejected data and we would need
to choose a linked service,
such as an Azure Data Lake, and then a directory
within there to send our bad rows off to.
And we can also report success on error.
And again, that's simply just going to flag it
as successful, whether or not there are errors.
And so all of these options are right here available to you
within the settings tab of your sync,
no matter what your sync is.
By way of review, errors are going to happen.
It's just part of life.
And by default, they will stop your transformations.
For most situations, you can set it to continue on error
in order to handle error rows,
and then utilize any of those options
that we just discussed and demoed,
depending on what your situation is.
And you can also use a conditional split,
in order to mitigate known situations.
Let's say you want to take those specific rows
and put them in another database.
Your conditional split can take care of that
by matching the pattern you know is going to occur,
and then splitting them off in that direction.
That's it for now, Gurus.
I hope you've enjoyed this video.
When you're ready, I'll see you in the next one.