Temporal Tables Part 1: Introduction & Use Case Example

So, what is the big deal about temporal tables and queries in MariaDB Platform?  After all, nearly all applications have requirements to capture and track date and time information, and databases have been doing so without difficulty ever since DATE and DATETIME became first-class data types.  Typical temporal table and queries usage scenarios are:

  • Data audits
  • Point in time analysis (time travel)
  • Anomaly detection
  • Slowly-changing dimensions

In this blog, part one of a five-part series, we will demystify temporal data and explain how and where you might use MariaDB’s temporal features in your applications.  Parts two through five of the series will dive deeper on syntax and take a peak and some of the internals.

Basic Purchase Orders Use Case

Let’s take the example of a system used to track purchase orders that a company creates to buy items from a supplier.  A greatly simplified version of the purchaseOrderLines table might look like the following:

Basic Purchase Orders Use Case: Table 1

There is temporal information in this table. The orderDate attribute represents the date that the order was entered into the system. The promiseDate attribute is the date that the supplier committed to shipping the items and the shipDate attribute represents the actual ship date.  Users can use this data to answer questions like:

  • How many widgets did we order during May of 2019?
  • Which purchaser achieved the highest discounts in May 2019?
  • What supplier/item has the biggest/smallest delivery delay between order date and ship date?

The answer to many of the above questions are helpful in driving important business decisions.  For example, the purchasing department might work to consolidate orders of some items to achieve better discounts or some suppliers might be deemed unreliable and replaced. But this analysis is blind to the fact that the purchaseOrderLines table may have changed over time.  This means that the following questions cannot be answered:

  • After a purchase order is entered, which supplier takes the longest to even provide a promise date?
  • Which purchaser makes the most changes to quantity after the order is entered?
  • Which supplier routinely offers a steeper discount than first indicated?

These questions could be of great business value and might drive many business decisions, such as, more informed supplier changes, purchasing agent training, valuable information when negotiating with vendors.  The purchase order schema, as designed, is adequate for recording basic information and driving purchase transactions, but it cannot provide the insights needed for your business.

Temporal View of Purchase Orders Use Case

Developers have devised some workarounds for the shortcomings illustrated above, but they involve the explicit creation of additional columns and new application-side logic, extra tables for storing historical information, complex SQL statements and greatly compromise performance.  Fortunately, the SQL:2011 standard addresses these issues with a set of standards for storing and querying temporal data. Using temporal features, you can automatically save the state of a row at various points in time. You can then query the state of the row as of the current point in time, a past point in time, or over a specified time range.  Using standard SQL data definition language (DDL), you can enhance an existing table, instructing MariaDB to track changes over time and then issue SQL statements to answer the questions that are most valuable to your business.

Let’s take the simple example of a supplier that has sent several updates to their promised date on the purchase order, resulting in the eventual cancellation of the order.

Without temporal tracking, any queries on or after 16-May-2019, can only show that an order placed on 01-May-2019 and promised for 17-May-2019 was cancelled.  The history of slipped promise dates is completely lost.  Business activities that were counting on the availability of the supplies (building of assemble-to-order products, picking of other items for shipping, and communications with end customers) may have been impacted by the state of the record at different points in time, but there is no way to see the state of the row at this critical time points.

If, however, the purchaseOrderLines table has enabled the tracking of temporal changes, seeing the state of the record on 02-May-2016 is as simple as issuing the below query:

SELECT * 
FROM PurchaseOrderLines
  FOR SYSTEM_TIME AS OF TIMESTAMP '2019-05-02 23:59:59'
WHERE purchaseOrderId = 1001;

The time that we happen to execute the above query does not matter.  We can issue it on 02-May-2019, 16-May-2019 or in the year 2020 and we will get the same result — the exact state of the row as request timestamp.

Continue to Temporal Tables Part 2: System Time where we will dive into the syntax for enabling the capture and querying of this temporal data.