Failed sprint: pause, redo, other?

Create a new entry in the sprint backlog and assign it the highest priority so it gets resolved before getting to sprints involving importing data from sprint 5?

Scrum is based on doing work that is in the Product Backlog and ordered by the Product Owner.  If you really are doing Scrum, then it appears that the Product Owner was ok with moving towards the result you have now.  

But to be honest, from the way you described it, it sounds more like you are doing old style project management using time-boxed iterations.  If you had been doing Scrum, at sprint 5 you would have inspected and adapted your work to accommodate the new information found about the data instead of just moving along. With real Scrum you would not be in the position you are now because it would have been handled much earlier in the cycle.

There must be a pause or redo in SCRUM? 

There isn't really a pause or redo.  Scrum uses the empirical process which is based on transparency of information, inspection based on known information, adapt based on known information.  What should have occurred if you are truly doing Scrum is at the point where the data issues were uncovered (transparency of information), you would have inspected your plan and adapted it to accommodate for the problem that was uncovered.

As for the failed sprint it was only failed if it didn't satisfy the sprint goal and there was no inspect and adapt cycle upon completion of the time box (i.e. sprint).  So the failure is not entirely the fault of the Development Team.  The failure is the fault of the entire Scrum Team for failing to follow the inspect/adapt loops of Scrum.

I know this isn't the answer you wanted to hear.  But it should be up to the Scrum Team to come up with the solution that works best.  If the single team is doing all the work to build this "major system development and data migration initiative" then you should let them do the work as they see fit.  In Scrum, the Scrum Team is responsible for the why, how and when and outside parties do not tell them what to do.  You sound like a possible stakeholder in the system so you need to have a conversation with the Product Owner, voice your concerns and issue.  Then the Product Owner can adjust the Product Backlog and work with the team to adapt their plans to address the items as defined by the Product Owner.

I will suggest you wait for others to comment on this as well.  This is only one person's opinion and in Agile/Scrum there is no right way to do things.  So get as much input as you can (transparency of information), inspect it and then adapt.

From my point of view, one of two things happened in Sprint 5:

a) The migration data did not meet the Definition of Done. If the DoD was clear about the need for clean, consistent and accurate migration data, then this item should never have been declared "done". As a result, the remaining work to be done would've been put in the Product Backog, likely with the highest priority and been worked on in Sprint 6. The Sprint Retrospective of Sprint 5 should've been used to investigate what process issues prompted an item that wasn't done to be declared done.

b) The migration data did meet the Definition of Done, but additional requirements or knowledge was discovered that made additional work on the migration data necessary and there wasn't enough time left in Sprint 5 to do so. In this case, the additional work should've been added to the Product Backlog, estimated and prioritized.

I'm with Daniel here. A superficial glance at your situation points to a possible problem in your usage of the inspect and adapt loops. But that's just educated guessing without digging deeper.

When the work for data migration was refined and estimated, did the team consider how they would validate migrated data? Were there any relevant acceptance criteria, for example?

Despite a failed sprint that would directly impact all subsequent sprints, it was decided to continue.

Was there

1. a plan to produce a releasable increment from Sprint 5 (something that validates the work done in that Sprint)?
2. an increment produced?
3. inspection by anyone (during or shortly after Sprint 5) of the increment, so that the effectiveness of the work done and decisions made could be validated?
4. Any kind of early decision making, based on the above?

If any of these were missing, then it either wasn't Scrum, or one of the key benefits of Scrum was missed.

The "pause or redo" exists very naturally in Scrum. It exists throughout, and is referred to as Inspection and Adaptation. Beyond "pause" and "redo", it could also be "do differently", "revert", "modify", "iterate", etc…

Quite naturally, in this case, at the end of Sprint 5 (perhaps at the Sprint Review), had there been transparency about the impact of work done in that Sprint, the team and stakeholders could have decided to change direction. The more extreme options available could have been to abandon the initiative altogether, or to throw away all of the work done in Sprint 5, and find an alternative approach for Sprint 6.

However it sounds like instead, everyone ploughed forward regardless, and the flaws of Sprint 5 were ignored until too late. This may be considered a fault of the Development Team, the Product Owner, the Scrum Master, or the stakeholders (or some combination thereof); but your organization should also considered whether there is a cultural issue that encouraged these problems to go unreported for so long.

My suspicion (based on your description) is that a lot of technical layers were being delivered before anyone used the product, and Scrum would have been helpful if Sprint 1 was used to deliver a working (albeit perhaps ridiculously small) piece of software. Instead of having a Sprint dedicated to "preparation of migration data", a Scrum approach would perhaps be to not only prepare data, but actually implement and use it in the same Sprint.

If that's too much work, the logical response would be to prepare, implement and use a small part of that data in a working, releasable, feature.

The point is that each Sprint would result in an increment that could validate not only the technical performance, but also the product's ability to meet customer and/or market demands.

The plan is to start the whole process over from sprint 5.

What lessons have you learned?

Just imagine how expensive and demoralizing it would be to make the same mistakes again, or to get a technically viable, but ultimately ineffective product at the end of it.

I hope your organization now understands the value of early validation.

I recall the Scrum guide mentions the a Sprint as small project.

Does this also implies that the cancellation of sprint means failure of the project? 

It's important to understand how 'failure' is being used in that context.

A cancellation of a Sprint in some cases could be a good thing if new information was discovered that required the team to pivot as oppose to going down the wrong path longer than they have to. 

There is certainly something to be learned from a cancelled Sprint should it happen. 

I recall the Scrum guide mentions the a Sprint as small project.

Does this also implies that the cancellation of sprint means failure of the project? 

A Sprint that continues towards an obsolete goal may fail in unpredictable ways, given that inspection and adaptation are compromised. Sprint cancellation is a means to achieve controlled failure.

In my opinion it all depends on how you define "failure".  I believe that a failure only occurs when you do not learn from what went wrong.  If you find the need to cancel a Sprint, there should be reasons for it.  Retrospect on the reasons and learn from them.  If you learn why the Sprint had to be canceled and how to recognize the symptoms in the future, I would call that a successful Sprint. If you just cancel the Sprint and start a new one without any inspection of the cancelled Sprint, then I would say you failed.