Maybe not.  If...

Lets say in 5 years someone makes a stored procedure that should only be run after hours due to the duration and resource usage.  Since it should only be...

If it would be OK to force the NULL value to be added to lowest date:

;WITH test_data AS (
    SELECT col1 = 7213
 ...

I would say the modern approach to doing this is using ROW_NUMBER(), partly because of its inherent efficiency:

SELECT theDate, ID, val
FROM (
    SELECT *,...

Man... talk about a worse practice ever.  If you're going to do something this silly, use a GUID... at...

SELECT 
    'DataLake' AS DatabaseName, SchemaName, TableName, 
    p.Rows AS Row_Count, 'Row count same as yesterday''s row count' AS Message
FROM 
DataLake.sys.tables...

Doesn't need to be a PK, just the clustering index.  The Quality_ID by itself would typically be the (nonclustered) PK.

Create the _test table from scratch.  Add the UNIQUE CLUSTERED index...

Proper clustering would make the archiving process very fast and easy, since all rows would be contiguous by Quality_Date.

If you cluster the table properly -- Quality ( Quality_Date, Quality_ID ) -- you likely won't have to purge them now.  That is one of the benefits of properly clustering...

I can't see the point in the clustered index :

CREATE UNIQUE CLUSTERED INDEX dbo.PERSON_HISTORY__CL 
    ON dbo.PERSON_HISTORY ( OccurredDate, My )...

We need to see the index definitions as well.  It seems to me that the index definitions would need to be different to produce plans and stats that drastically different.

I can't see the point in the clustered index :

CREATE UNIQUE CLUSTERED INDEX dbo.PERSON_HISTORY__CL 
    ON dbo.PERSON_HISTORY ( OccurredDate, My ) 
 ...

A better option overall is to just have the last history row id stored in the header table (yes, denormalize it).

A trigger on the PERSON_HISTORY table can do that very...

Hmm, I remember a WHERE condition on the OccurredDate; maybe I'm mixing this query up with another one.

At any rate, one last time, as I stated earlier:

the history table should...

First, the history table should be clustered leading on OccurredDate.  That's generally true for log tables.  [Forget the stupid myth that "by default, every table should be clustered...