Dataset Integrity

5 Principles

1) Validity - data follows rules (규칙 준수)

• Typical constraints: Data type, Range, Mandatory/NOT NULL, Unique, Set membership / FK, Regex, Cross-field checks.

• Think: phone number format, age 0–120, required customer_id, unique license_plate, state ∈ {US states/territories}.

• Quick SQL:

  -- range + required
  WHERE age BETWEEN 0 AND 120 AND age IS NOT NULL
  
  -- set membership (lookup table)
  WHERE state IN (SELECT code FROM ref.us_states)
  
  -- regex (format)
  WHERE REGEXP_CONTAINS(phone, r'^\\(\\d{3}\\) \\d{3}-\\d{4}$')
  

2) Accuracy — matches the source of truth (정확성 = 기준값 일치)

• Needs external gold source (우편번호→city/state, official ref tables).
• Define your standard first (are GU/AP allowed or not? 표준을 먼저 합의).

3) Completeness — nothing critical missing (필수값 빠짐 없음)

• You can’t “invent” missing facts later. Use explicit Unknown/Missing codes when allowed.

• Profile for NULLs & blanks:

  SELECT
    COUNTIF(col IS NULL) AS nulls,
    COUNTIF(TRIM(col) = '') AS blanks
  FROM t;
  

4) Consistency — same fact = same value across systems (시스템 간 일관성)

• Pick a system of record + tie-break (latest timestamp, trusted source).

5) Uniformity — same units/format everywhere (단위/포맷 통일)

• Metric vs Imperial = 위험 사례(예: Mars Climate Orbiter).
• Normalize with CAST/SAFE_CAST, FORMAT, clear unit labels.

Uniqueness (중복 방지 = 현실 피해 예방)

• Real-world keys: Phone → phone number, Email → address, Car → license plate.

• Dedupe pattern (최신 1건만):

  SELECT * EXCEPT(rn)
  FROM (
    SELECT *, ROW_NUMBER() OVER(
      PARTITION BY customer_id ORDER BY updated_at DESC
    ) rn
    FROM `proj.ds.customers`
  )
  WHERE rn = 1;