I don't believe that the MERGE JOIN is a result of the UNION ALL . usually when MERGE JOIN is the most expensive part is due to the fact that one on of the sides has a big residual. And in this case, you are having up to 100 more rows in the upper SELECT that in the lower one.

I would try something like

CREATE TABLE myData (
    Id int,
    GroupingId  INT,
    [Date] DATE,
    Whatever varchar(100)
)
INSERT INTO myData(Id,GroupingId, Date,Whatever)
          SELECT 1 Id, 1    GroupingId, '2023-01-01' Date, 'Value1' Whatever
union all select 2,    1              , '2023-01-02'     , 'Value2'
union all select 3,    2              , '2023-01-03'     , 'Value3'
union all select 4,    1              , '2040-01-01'     ,' Value1'

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY GroupingId
            ORDER BY Date DESC) 
        as rnk
    FROM myData
    WHERE Date <= SYSUTCDATETIME()
)
SELECT *
FROM cte
WHERE rnk = 1
UNION ALL 
SELECT *, 1 as rnk
FROM myData
WHERE Date > SYSUTCDATETIME()
ORDER BY GroupingId
OFFSET 0 ROWS Fetch NEXT 100 ROWS ONLY

This should remove the MERGE JOIN and leave you with CONCATENATION

[Edit 1] Thank you for posting the query plan. try creating the suggested index:

CREATE NONCLUSTERED INDEX [idx_date_test] ON [dbo].[myData]
(
    [Date] DESC
)
INCLUDE (
    groupingId, 
    id
)

You can try following approacg, instead of making a row_number over the whole table firs gather the "last" of evry groupingid, the get only 1 row per grouping incase there are multiple entries, as you only have n groupingid and some baggage you should get fatser to your result.

for this query only ([GroupingId],[Date]) need a comnied index,which must be expended with your actual data.

CREATE TABLE myData
    ([Id] int, [GroupingId] int, [Date] Datetime, [Whatever] varchar(6)
  , Primary key(Id), INDEX IX_MyTable4 NONCLUSTERED ([GroupingId],[Date]) )
;
    
INSERT INTO myData
    ([Id], [GroupingId], [Date], [Whatever])
VALUES
    (1, 1, '2023-01-01', 'Value1'),
    (2, 1, '2023-01-02', 'Value2'),
    (3, 2, '2023-01-03', 'Value3'),
    (4, 1, '2040-01-01', 'Value1')
;

4 rows affected

WITH CTE AS (SELECT m1.* FROM myData m1 
  JOIN (SELECT MAX([Date]) max_date, [GroupingId] FROM myData WHERE [Date]  <= SYSUTCDATETIME() GROUP BY [GroupingId]) m2
  ON m1.[GroupingId] = m2.[GroupingId] AND m1.[Date] = m2.max_date WHERE m1.[Date]  <= SYSUTCDATETIME()),
CTE2 as (
        SELECT *,
            ROW_NUMBER() OVER (
                PARTITION BY GroupingId
                ORDER BY Date DESC) 
            as rnk
        FROM CTE)
SELECT * FROM CTE2 WHERE rnk = 1
UNION ALL
        SELECT *,1
        FROM myData
        WHERE Date > SYSUTCDATETIME()

ORDER BY GroupingId
OFFSET 0 ROWS Fetch NEXT 100 ROWS ONLY

fiddle

Ok, some comments got me thinking and on a right track. I figured, I basically need a way to get rid of the Union/merge join from the query plan and have nested loops, so the query can be executed sequentially all the way.

SELECT TOP 100 * 
FROM (
    SELECT *
    FROM mydata o
    WHERE (o.date > SYSUTCDATETIME() OR (
        o.id = (SELECT TOP 1 id FROM mydata s WHERE s.groupingid = o.groupingid AND s.date < SYSUTCDATETIME() ORDER BY DATE DESC)))
) r
ORDER BY r.groupingid

This gets me from 1 minute in the original query (or the other answers so far) to below 100ms. New query plan: https://www.brentozar.com/pastetheplan/?id=r1sQdhM0s