A credit limit in a customer table changes overnight from € 25,000 to € 40,000, after which the old version folds shut and moves to the version stack with the date it was valid until. A timeline then runs back to 25 September until the old value is in the table again.

What rollback in a data warehouse really means

Tuesday, 11 am. The credit department calls: 300 customers suddenly have a credit limit of €40,000 instead of €25,000. The cause is an export fault in the accounting package, which put the limits in the wrong column on Monday night. The load itself succeeded, because technically nothing was wrong.

The fix looks simple: put the table back to how it was. This article uses that example to show what happens then, and where it goes wrong when you try it by hand.

Published: · Last updated: · By , architect of Yres and founder of Plainwater

What does rollback mean in a data warehouse?

Rollback means putting a table back to the state it had at a moment you choose. Everything that arrived after that moment is removed, the values that applied then become current again and the point where the next load starts moves back with them.

That is different from a rollback in an ordinary database. An ordinary database can only undo a change while it has not been completed. In the example, that moment passed nine hours ago. The load has finished, the reports have refreshed and the dashboards show the new limits. So you pick a moment, for example Monday 23:59, and put the table back to how it was then.

It works per table, because the faulty export only touched the customer table. The orders, products and stock from the same night came in fine and have to stay. Putting two tables back is therefore two actions, each with its own moment.

Why keeping history is not the same as rollback

Yres keeps every version of every row. When the credit limit of De Jong Transport went from €25,000 to €40,000 on Monday night, the old value was not overwritten. A new version was added and the old one was given an end date. So you can always look back. What was the limit on 25 September? €25,000.

But looking back is not the same as putting back. Reports and dashboards show the current version, and after Monday night that is the wrong one. The right value is still there, but nobody sees it without going looking for it. Meanwhile the credit department is working with €40,000.

There is also something you cannot see on screen. A table that only fetches changes remembers how far it got. That last modification date from the source is called the watermark. The bad load moved the watermark forward to Monday night.

If you delete the bad rows by hand, the watermark stays there. On the next load the table only asks for changes made after Monday night. Anything from before that is never fetched again. You end up with a table that looks fine but has a gap that never fills itself.

What happens when you roll back

The administrator opens the customer table in Yres, chooses rollback and enters Monday 23:59. The moment counts to the second. Yres then builds one script of three to five steps and runs it in one go.

  1. Check. Yres checks that the table exists in its own records. If it does not, the script stops with an error in the log.
  2. Remove rows. All rows loaded after the chosen moment are removed, including the wrong version of De Jong Transport.
  3. Reopen versions. The versions that applied at that moment become current again. The limit is back at €25,000.
  4. Withdraw sequence numbers. For a table that issues its own sequence numbers for new customers, the numbers handed out after the chosen moment are withdrawn. That way a new customer never gets a number that already appears in a report.
  5. Restore the watermark. For a table that only fetches changes, Yres recalculates the watermark from what is still in the table. It lands on the last change from before the bad load by itself.

Steps 4 and 5 only apply to tables that issue sequence numbers or fetch changes. That is why the script has three to five steps.

Every rollback leaves one line in the log. It records which table, which window, who did it, how many rows went, how long it took and whether it succeeded. If something fails halfway, that is recorded too, with a pointer to the detailed log.

The monitoring screen uses that same line. Every load inside the rolled-back window shows there as deleted, with the administrator's name and the time. If two good loads ran on the customer table on Tuesday morning, those show as deleted as well. They were rolled back too and need to run again.

WhatBefore the rollbackAfter the rollback
The rows in the tableEverything is there, including what came in wrong on Monday nightEverything loaded after the chosen moment is gone. The count is in the log
The current value per customerMonday night's wrong version is current, the right one is closedThe version that applied at the chosen moment is current again
Sequence numbers of new customersThe numbers handed out after the moment still existThose numbers are withdrawn (only for tables that issue their own numbers)
The watermarkSet to Monday night, the last change the bad load sawRecalculated from what is still in the table (only for tables that fetch changes)
The logNo trace of the actionOne line with table, window, administrator, row count, duration and result

The trap: the next load

Deleting rows is the easy part. The damage is in the watermark. That is why rollback in Yres is an action of its own, and not a manual clean-up.

A table that only fetches changes asks the source every night for everything modified since a given moment. That keeps every load small. But the source's answer depends entirely on that moment.

After the rollback, the watermark is back on the last change from before the bad data. So on Wednesday night Yres asks for everything since that moment. The source delivers the credit limits again, this time from the right column. The 300 customers get their correct limit back as an ordinary change.

Two details are worth knowing:

  • An empty table. If you went back to before the very first load, the table is empty and there is no last change any more. Yres then fetches the source again in full.
  • 'From and including'. The request to the source reads 'from and including' the watermark, so rows modified at exactly that moment come along once more. Yres compares every incoming row with the existing version and only creates a new one when something really changed. So no duplicate versions appear.

When do you use it?

Rollback is meant for one table with data that has to go, where you know from which moment. In practice three situations keep coming back.

A source that delivers a period twice. After an update, a payroll package exports the September changes again, with new timestamps. The payroll cost table now holds September twice. You roll back to the moment before that export. On the next load September comes in once.

A connection with the wrong key. When a new source was set up, the customer number per branch was chosen as the key instead of the national number. A customer with three branches appears three times in the table after the first load. You correct the key, roll back to before the first load and load again.

An empty file processed as a valid delivery. The supplier of a price list accidentally drops an empty file. Yres reads zero lines and closes all prices as if they had lapsed. After rolling back to the moment before that file, the prices are current again.

If you work directly on the database, you can run the action in dry-run mode first. Yres then shows the full script with the chosen moment, without touching anything. That way you see in advance how many rows will go. From the admin screen that is not possible, because it always executes.

When it works

  • One table, one moment, and the period after it can be loaded again
  • You want to see what will happen first and you have access to the database, because the dry run shows the script and leaves the data alone

Where it stops

  • A load that touched 12 tables needs 12 rollbacks, each with its own moment
  • Removed rows are really gone, because the rollback itself cannot be rolled back
  • Only the contents of the table change. Columns, tables, views and reports stay as they are
  • Anything written outside the data warehouse, such as files in a data lake, stays where it is

When it makes little sense

  • For a table that is fully replaced on every load, because there is no older state to go back to
  • For the staging layer where loaded data is put ready, because the next load fills it again anyway

Frequently asked questions

Can I roll back one specific load?

You choose a table and a moment. Everything that table received after it is removed. So choose the moment just before the load that has to go. If good loads ran on the same table after it, they go too and need to run again. Other tables from the same night are left untouched.

How can I tell afterwards that a rollback happened?

The log holds one line per rollback with the table, the window, the administrator, the row count, the duration and the result. The monitoring screen shows every load inside that window as deleted, with administrator and time.

Can I try it first without changing anything?

Yes, but only directly on the database. In dry-run mode Yres shows the full script and leaves the data alone. A dry run does leave a line in the log, though. The monitoring screen then shows the loads in that window as deleted, even though nothing was removed.

What if I just delete the bad rows myself?

Then the watermark stays at the moment of the bad load. Next time Yres asks the source only for changes made after it. The table looks fine but is quietly missing a period. When you roll back through Yres, the watermark goes back too.

Talk to a data architect

In 30 minutes a data architect looks at your current data environment with you and at where it pinches today.

Book a call →

Further reading

Data warehouse automation that runs in your own Azure

Built for organisations on Azure and Power BI. Yres connects your sources, keeps the history and promotes changes in a controlled way, without manual work.