Unknown date [UD]

Context

When we want to determine the active customer base we use this SQL:

Or if you prefer this SQL:

To make this SQL easier we might replace the NULL values for column billing_term_date in Fct_Contract by a constant max date (e.g. 2079-01-01). This constant should be interpreted as billing was not terminated (equal to NULL value).

Pros:

  • We can define this max date once in the ETL and not in every query that calculates the active customer base.
  • We don’t have to check for NULL values. (this is also a performance improvement). We might as well set the billing_term_date column not to allow NULL values.
  • The sql gets simpler:

  • This pattern might be used in an architecture where we don’t allow NULL values in our datamart (especially for foreign keys). Just because it makes our querying easier and faster. (See UFK pattern)

Cons:

  • We cannot use the query part  where billing_term_date is not null to find terminated customers. Instead we could use this:

or this

  • It feels a bit like a dirty trick, because you are tweaking the data to make your querying easier. Currently I’m not using this pattern.