Knobit of Data modelling · K-828 · Skill

Recognises a business date stored with a time of day

What it means

Two mirror-image defects. An event timestamp with no offset is ambiguous twice a year and incomparable across a bank that books in more than one zone. A business date — value date, booking date, maturity date, due date — stored as a timestamp invites a time component nobody means and makes date equality quietly fail.

How it is assessed

Flags the deficient case and passes the acceptable one. Deficient: `created_at TIMESTAMP` with no zone on `dim_account`, written by jobs in two regions. And `payment_value_date` as a `TIMESTAMP`, so `= DATE '2026-03-31'` matches none of the rows loaded at 23:30 Acceptable: `TIMESTAMP WITH TIME ZONE` for event times, or UTC with the convention stated in the column `comment` and nowhere else; `DATE` for `payment_value_date`, `loan_maturity_date` and `valid_from_date`/`valid_to_date`, as the sample model has them

Builds on

Recognises a physical domain differing from the logical domainK-617 · Skill
Explains time zones in timestamps and business datesK-854 · Knowledge

Needed by

None