Discerning Design Pattern #1: The Table Reload Pattern

Welcome to a new series: Discerning Design Patterns! These are how-to tutorials on common developer tasks but with the voice of (painful) experience. Newcomers to IT will benefit greatly, and if you’re a veteran, please give it a quick read and hop down to the comments to share your own lessons and experiences.

The Table Reload Pattern

Intent: Replace all the rows of a table with new rows in a way that will handle failure.

This task is often handed to junior developers, and rightly so: it is simple to understand, it can be done with many technologies so they can become familiar with your team’s language or software of choice, and it gets them familiar with your systems and data.

The goal is to create a periodic process that copies data from system A and puts it in system B. A new developer’s first attempt will be something like this:

  1. Delete all rows from system A.
  2. Read all rows from system B.
  3. Write all rows to system A.

An experienced developer will spot the flaw — probably because they experienced it before! (we all learn the hard way sometimes) But you can be forgiven if you don’t see it. This design can run flawlessly for years before disaster strikes, and it’s not always difficult to recover from.

The problem is, if steps 2 or 3 fail, System A will be left with an empty table. A connection failure, a server crash, a clumsy janitor tripping over a network cable, rats chewing on the server’s motherboard (how clean is your data center, anyway?), any number of things could cause a failure that might leave System A crippled.

The Discerning Design Pattern brings system B’s data into the same machine with System A first, and then does the overwrite, ideally in a single transaction.

  1. Read all rows from system B.
  2. Insert into a ‘holding area’ in system A (a temp table, a flat file, whatever tools you have available).
  3. Begin transaction:
  4. Delete all rows from system A.
  5. Insert all rows from the holding area.
  6. Commit transaction.
  7. Clear the holding area.

Depending on the mechanics of your system (type of database, file system, etc) it may not be possible to use an atomic transaction, but bringing the new data into your system locally before attempting the swap will greatly reduce the risk, and most importantly if your system crashes at any point you can simply restart from step 1. No cleanup required. (Note: if your holding area is persistent, you may want to clear it as step 1)

If the table reload process fails, your system shouldn’t. Your goal is to create a process that ‘fails gracefully’. No matter what technologies (C#, Java, C++) or toolsets (SSIS, DataStage) or infrastructure (ESBs, AWS, SQL Server) or use-case (mobile app, enterprise app, ETL) this design pattern is always useful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s