Data

Modeling Temporal Data - A Technology Tangent (Part 2)

This blog series continues my thoughts on how to model temporal data. This second entry is tangential and discusses how technology changes have altered what is possible today.

Mid 1990s Era

I began my career around this time. Windows started to take off with Windows 95, launching the modern PC era. Mobile phones were still a novelty and the iPhone was more than a decade away. Affordable laptop computers had a monochrome (grayscale) screen with a trackball instead of a touchpad. The database market leaders were Oracle and Sybase. IBM DB2 was prominent in the enterprise category while Microsoft SQL Server (MSSQL) was just getting off the ground after collaborating with Sybase.

In other words, life was VERY different just 30 years ago.

Virtually all of the databases in this era were transactional databases (OLTP) with data stored in rows. RAM and hard drives were small and relatively expensive, with sizes measured in MB versus GB. Hard drives were physical spinning disk drives with slow I/O (disk reads and writes). The database systems themselves and the data modeling techniques in vogue were all designed around these real technological constraints. The primary data models for new application backend databases was Third Normal Form (3NF). For analytics, popular data models were Inmon (based on 3NF) and Kimball (Star Schema).

Running generalized analytics on these databases was extremely slow by today's standards. The SQL language was also much more limited and purely OLTP focused. SAS filled the void by expanding from its origins on the mainframe to Unix and the PC. SAS had a few key advantages for analytics over the traditional OLTP databases:

• SAS datasets were similar in concept to a table, but stored on disk in a file per dataset (conceptually like Parquet, though because Parquet is column oriented it's much better suited for analytics)
• SAS "queries" processed the entire dataset row by row, applying any transformations and outputting a new dataset (or overwriting an existing dataset)
• SAS datasets inferred schema
  • no CREATE TABLE style statements were needed to move data from one dataset to another
  • column schemas for calculated columns did not need to be predefined
• SAS favored flat files with few joins; small lookups could be applied via SAS formats without needing to join a table
• When run on large-scale computers like mainframes or Unix boxes, SAS could effectively process large volumes of data because computing was not memory bound (reading datasets by rows allowed for processing data in "streams" versus the set-based actions of a traditional SQL database).

Teradata introduced Massively Parallel Processing (MPP) to the analytics space. The software came preinstalled on an expensive database appliance that only large companies could afford.

Essbase was the leader in providing OLAP cubes, which often were the presentation layer of the underlying Star Schema data model, with pre-aggregated data for fast reads. These cubes had some key shortcomings:

• the rigid data made it difficult to add new columns or data sources
• the user was locked into a single, post-processed view of the data

Also, because data was aggregated, cubes lacked the ability to fully "drill down" to the actual row-level details.

2010s Addendum

In the prior section, I noted Teradata's MPP database appliance. It was not until this era, though, that MPP-style analytical processing (OLAP) was readily available to the masses. Cloud data warehouses such as Redshift, Snowflake, and BigQuery led the way. Databricks originally delivered MPP processing exclusively via Spark, though it now has a SQL engine. These warehouses (or lakehouses) store data in columns instead of rows, which leads to significant compression of data along with fast querying (because most analytical queries only need a few columns at a time).

The other significant emergence was the concept of NoSQL databases. MongoDB popularized the document database (document db), one of the many NoSQL categories that also include key-value stores, wide-column stores, and graph databases. Document dbs are designed around schema on read vs schema on write. What does this mean? A collection, which acts like a table, can have a different schema for each row. The record itself "defines" the schema with the shape of the various key-value pairs (key = column, value = row value). Values can be text, integer, etc., and can also be other "objects" like sub-records of key-value pairs and arrays.

For analytics, document dbs appear to provide the freedom of a richer SAS dataset on an open source database with commodity hardware. Unfortunately, these databases have shortcomings that make them impractical for analytics:

• vendor-specific query languages
• poor performance on data aggregation

Snowflake solved both of these problems early on with its VARIANT (i.e. document db shaped) column type. If you have an analytical document db use case, always choose Snowflake or equivalent over an OLTP document db like MongoDB.

What About SQL?

I could discuss SQL at length in its own blog post. Instead, I want to focus on one major issue with databases today: the many "flavors" of SQL. Each database vendor has its own SQL dialect that is mostly similar but always has a few notable differences. SQL dialects differ partly out of necessity, as database features are different between databases themselves. For example, what is available in Postgres for temporal data is different than what is available in MySQL or MSSQL. Practically, these differences also enhance vendor lock-in, though GenAI SQL translations should make that less of a barrier going forward.

So What?

Why am I rehashing the 1990s and the 2010s?

The technology available today is significantly different than it was 30 years ago. GenAI cost spikes aside, RAM and disk are MUCH faster and MUCH cheaper. Cloud database providers offer endless compute and storage at (relatively) reasonable prices. Today's equivalent tech stack is a fraction of the cost of the SAS platforms I managed 15 years ago.

And yet we are largely using the same data modeling concepts.

For analytics, we continue to use techniques that were popular prior to the OLAP cloud warehouses even though those warehouses provide significant advantages in compute / storage and the SQL language. As an example, this alternative idea for Slowly Changing Dimensions Type 2 works now because of these new technological advances.

For system applications, the allure of schema on read along with the idea of fully denormalized data was irresistible. All of the data for a single business concept, including hierarchical data, could live in a single record. The application could pull just one row to get what it needed, and then write out the single row with changes.

Unfortunately, schema on read has some serious deficiencies. Every row can have its own schema, so schema "drift" needs to be separately tracked and managed; even though tracking schema "drift" or versioning schemas is not a feature included with the database. A "table" can easily have 10 to 50 to 100 true row schemas. Using traditional row / column shapes to define the data structure and enforce data standards is a real feature; schema on read in practice often drifts into the "lazy way out". Modern application data modeling should combine the benefits of document db data structures with the safety of traditional data tables.

Future posts will lean on these ideas when discussing application and analytical data models.