Hard duplicates are identical rows; soft duplicates are one entity with conflicting values, resolved by a per-column survivorship policy written as SQL.

'Deduplicate' hides two problems. Hard dupes — same fact twice — have a mechanical fix. Soft dupes — same entity, disagreeing facts — have none: someone must decide per column what survives (newest? most complete? highest-priority source?), and every rule needs a deterministic tiebreaker or the golden record flickers. The craft is making the policy visible: a survivorship CTE per rule, reviewable and auditable.

SELECT
  horse,
  paint,
  acquired
FROM
  (
    SELECT
      horse,
      paint,
      acquired,
      ROW_NUMBER() OVER (
        PARTITION BY
          horse ORDER BY
      inspected DESC
    ) AS rn
    FROM
  horses
  )
WHERE
  rn = 1;

SELECT
  h.horse,
  (
    SELECT
      paint
    FROM
      horses h2
    WHERE
      h2.horse = h.horse
    ORDER BY
      inspected DESC
    LIMIT
      1
  ) AS paint,
  MIN(acquired) AS acquired
FROM
  horses h
GROUP BY
  h.horse;

Taking the whole latest-inspection row set the horse's acquired year to 1995 — soft duplicates need a per-column survivorship policy, so the newest paint comes from the latest inspection but the earliest acquisition is its own MIN, not the winner row's.

Conflict inventory first; per-column rules as named steps; complete tiebreakers everywhere.