Data Analyst Professional · Модуль 6 · Урок 21 із 51
Нормалізація форматів і керовані довідники
Нормалізація робить еквівалентні значення порівнюваними, але не повинна стирати provenance. Правильний pipeline зберігає raw value, будує typed/normalized column, застосовує versioned mapping і явно позначає unmapped values.
Три шари замість UPDATE raw
Trim і case — лише перший крок
SELECT
customer_ref AS customer_ref_raw,
UPPER(BTRIM(customer_ref)) AS customer_ref_normalized,
email_text AS email_raw,
LOWER(NULLIF(BTRIM(email_text), '')) AS email_normalized
FROM seowork_lab.raw_customer_events;Не застосовуйте case conversion до всіх identifiers без контракту: деякі keys case-sensitive. Не «виправляйте» email regex-ом до accuracy — format validation не доводить існування адреси.
Безпечне перетворення типів
Прямий cast одного invalid value може зупинити весь query. Спочатку класифікуйте format, потім cast у контрольованому branch; локаль decimal separator і timezone мають бути частиною source contract.
CASE
WHEN amount_text ~ '^[0-9]+([.][0-9](1, 2))?$'
THEN amount_text::numeric(12,2)
WHEN amount_text ~ '^[0-9]+(,[0-9](1, 2))$'
THEN REPLACE(amount_text, ',', '.')::numeric(12,2)
ELSE NULL
END AS amount_parsedОкреме amount_parse_status пояснює, чому NULL з’явився: source missing чи parse error.
Mapping table замість вкладеного CASE
CREATE TABLE region_map (
source_system text NOT NULL,
raw_value text NOT NULL,
canonical_region text NOT NULL,
valid_from date NOT NULL,
valid_to date,
PRIMARY KEY (source_system, raw_value, valid_from)
);Довідник потребує owner, effective dates, change log і uniqueness rule. Після LEFT JOIN рахуйте unmapped values; не підставляйте найближчу категорію за здогадом. Code UNKNOWN має відрізнятися від NOT_APPLICABLE.
Constraints запобігають повторенню
CREATE TABLE curated_events (
event_id bigint PRIMARY KEY,
customer_ref text NOT NULL,
event_ts timestamptz NOT NULL,
amount numeric(12,2) CHECK (amount >= 0),
status text CHECK (status IN ('paid','cancelled','refunded')),
UNIQUE (customer_ref, event_ts, amount, status)
);CHECK, NOT NULL, UNIQUE і foreign keys — prevention at write time. Пам’ятайте: CHECK із NULL може пройти, тому mandatory field потребує NOT NULL.
Практика
- Збережіть raw і створіть normalized columns.
- Розділіть missing, parse_error та unmapped.
- Створіть region/status mapping із owner/version.
- Порахуйте mapping coverage та top unmapped values.
- Запропонуйте constraints для curated table.
Офіційні джерела
Готовність до тесту
Ви будуєте raw→staging→curated flow, відрізняєте parse error від missing, керуєте mappings і переносите стабільні rules у constraints.
Закріпіть матеріал уроку
Три сценарні питання. Для зарахування уроку потрібно дати щонайменше дві правильні відповіді.