Quantitative Science Blog

December 15, 2021

Six Under-Appreciated Principles of Data Warehousing, Through the Eyes of a Data Scientist

Written by Kyle Schmitt

A sound strategy for your data warehouse can be the difference between a world-class data science organization and a large allocation on your balance sheet that’s dedicated to treading water.

Having worked in data science across three very different organizations, I’ve experienced the good, the bad, and the ugly of data warehousing. From these experiences, I have compiled an assorted set of “principles” that, from my standpoint, can go under-appreciated. In this post, I articulate these principles in the hope that it helps other data scientists, data engineers, software engineers, designers, and product owners hone their own mental models.

Lingo

Let’s start by establishing some nomenclature:

System: a real-world system of interest, e.g., at an insurance company — a policy, claim, or driver

Data model: an abstract model that organizes elements of data and standardizes how they relate to one another and to the properties of the system

State: the value of a set of properties within a data model at a given point in time

Predictor variable: one of potentially many properties of a state that can be used to predict other properties of the state or a future state

Target variable: one of potentially many properties of a state or a future state that we might aim to quantify with predictor variables

Label: a value assigned to a target variable to quantify a known or presumed outcome

Prediction: an estimate of the target variable made based on a given state; as discussed below, in principled data models, predictions might be part of the state

Data science model: a model that accepts predictor variables and returns predictions (“Data science” is redundant, but it is used to disambiguate “model” throughout this blog.)

Principles

In this section, I’ll introduce six principles of data warehousing as seen through the eyes of a data scientist.

Principle #1: Explicitly delineate between null and default labels

One common mistake in data modeling is a failure to delineate between labels that were never generated and default labels. Without this delineation, a data scientist may unwittingly assign an outcome to a target variable incorrectly, or, they may need to resort to semi-supervised learning to reconcile ambiguous labels. Or, they might not be able to rely on the label at all, squandering a ML opportunity.

Imagine your design team implements a modal to collect reason codes for users who ask to suspend their accounts. If the modal defaults to some common category, say “I no longer have a use for the app.,” a careless data strategy might conflate this default with users who choose not to interact with the modal.

Or, imagine your team has implemented a new algorithm to flag suspicious behavior associated with a transaction. Going forward, each transaction will be labeled true or false. But, prior to launch, each transaction was unlabeled. A careless data strategy might not allow for precise delineation between null and false around the time of launch.

These examples illustrate just a few cases that violate this principle.

Principle #2: Persist information about the evolution in state mutation Many systems of interest have an evolving state over time. Aptly considering this evolving state is core to the development of effective ML systems. In order to do this, we need to develop data models that support the reconstruction of the state at any point in time.

Field mutability is toxic to state reconstruction. When fields are mutated in place, we lose any record of how that field might have varied in time, and we are left only with the latest snapshot of the data model. Field mutability should only be used with careful consideration of the effects it might have on future analytics and data science.

Instead, one of two alternative approaches is often used:

• Accumulating records provide a snapshot of the state at a time cadence that is consistent with the dynamics of the system. This approach is often more convenient for downstream consumers, but may not be effective or practical for systems that a) experience frequent state changes (e.g., by the minute) or b) experience state changes at variable time scales (e.g., no changes for months followed by many changes within hours).

• Transactional records capture any changes to the state with an associated time stamp. From this information, a complete and perfect reconstruction of the evolving state can be achieved. In fact, accumulating records are often built from transactional records.

Principle #3: Design your systems to allow for on-leveling In insurance sciences, the phrase “on-leveling” refers to the application of a new pricing model to historical underwriting decisions. The resulting data allows us to ask counterfactual questions like “What if we had priced users with the latest pricing model?” This is an essential process for benchmarking new data science models.

This same concept can be generalized to virtually any application area. There are often diagnostic and modeling advantages to having predictions prepared based on the latest data science model.

At the highest standard, engineering and data science work together to integrate predictions into the state of the data model and backfill this state over the history of data (without overwriting predictions of earlier data science models). Retrofitting backfilling processes can be painful or impractical. We recommend building this into your system from day one.

However, even if the prediction is not integrated into the data model, the data model can still be built in a way that allows for prediction backfills, i.e., the data model should store values for all predictors required for the data science model. This requires that these predictor variables are not removed or overwritten. This might necessitate certain storage and computational costs to generate fields that are not used anywhere outside of the data science process.

Deprecation of predictor variables may be necessary (e.g., computational costs, vendor costs), but should be a deliberate process that includes analytics stakeholders in the discussion. To aid in this process, we should strive for data science models that effectively self-document their data lineage.

Principle #4: Prototype assuming system longevity To support proof-of-concept initiatives or A/B experimentation, it may be tempting to begin with a substandard data model and then transition to a more virtuous data model over time. After all, there can be significant overhead in designing a completely future-proof system (which, of course, does not actually exist).

In circumstances when the more virtuous data model can be backfilled after transitioning to full scale, this is highly recommended. Asking all future analysts to know how to span two or more data models for the same system is a recipe for confusion at best and disaster at worst.

In other circumstances, shortcuts taken on the substandard data model might preclude the more virtuous data model from being backfilled. While this risk cannot be mitigated entirely (Because, hey, we learn a lot in the early stages of deployment.), the use of cross-functional design in the early phases of a product or concept is recommended. By bringing analysts and data scientists into those discussions, we might grow our understanding of downstream consumers and identify data elements that are critical to collect and persist for diagnostics, machine learning, etc.

Principle #5: Tamp down unsanctioned data storage Without the right technologies and left to their own devices, many technical and non-technical domain experts will resort to using their own decentralized mechanisms for compiling data (most commonly, spreadsheets). While better than nothing at all, these mechanisms have a tendency to breed poor quality, missing records, inconsistent schemas, unstructured fields, etc. They are also time-consuming. Finally, they are inconvenient to work with, especially when one wants to join them with other records.

We should make every effort to weed these out, perhaps using occasional campaigns to hunt down their existence. We should build data entry technologies that promote input efficiency and data structure. These technologies should be connected with a backend that is centralized for consumers through the data warehouse.

Failing that, we should have protocol to ensure that spreadsheets are snapshotted to our data warehouse at some reliable cadence.

Principle #6: Avoid sequencing requirements for predictions and labels It’s not uncommon for the mechanisms that generate predictions or labels to be queued by business logic. This may be done deliberately to manage costs. But, it is often done circumstantially without an appreciation for its ramifications.

For instance, in insurance underwriting, we might apply a sequence of rules to determine whether a driver or vehicle should be declined. As a slightly contrived example:

1. If rule A is satisfied, stop and decline.

2. If not, pay for a report. Based on the report, if rule B is satisfied, stop and decline.

3. If not, run model A. If the model A prediction exceeds a threshold, stop and decline.

4. If not, run model B. If the model B prediction exceeds a threshold, request a review by a human underwriter.

5. If not, accept.

In this example, generating a prediction with model A is serialized behind rule A and rule B. Serializing rule B behind rule A is a shrewd maneuver, as we circumvent report costs. But, what if the computational cost of model A is trivial? We have forgone an opportunity to generate a prediction that might be used for diagnostics or future model building (see principle #3).

Also, you’ll notice that review by a human underwriter is only actuated under a narrow set of circumstances. There could be significant data science upside in attaining manual reviews for a more diverse set of cases. Savvy designs will randomly (or selectively via active learning principles) assign cases for manual review, even if in parallel with an automating decision engine.

This example illustrates that there is much to gain by being thoughtful about the sequencing of prediction and label generation.

That’s a wrap

When faced with a big and nebulous challenge, developing a taxonomy is often a productive first step. I hope that the six principles described above help you and your organization realize the full potential of data science.

If you’d like to learn more about Root Insurance, visit joinroot.com or contact me on LinkedIn.