Retention split by class, on 900 million rows

One table with two retention requirements, seven years apart, and no column distinguishing them.

the question, asked three ways and answered three ways:

  legal          seven years, financial records only
  the contract   two years, everything
  prudence       "keep it, storage is cheap"

the reconciliation: an order with a payment is a
financial record. one without is not. that is a JOIN,
not a column, and it had to become a column.

add retention_class, backfill 900M rows in batches,
then partition by it.

The technical work was a generated column and a backfill; the work that took six weeks was getting three people to agree on which of their three answers was the policy. Writing the reconciliation down as a rule — a payment makes it financial — is what turned a legal question into something a schema can express.