If this runs once each night for 20 minutes, you might consider living with it for now and focus efforts on designing and developing a completely new process to replace it – one that separates daily data from summarized/aggregated data. But if you insist…

If you have the ability to alter the table definition, the current process can be improved if you:

1. Set column OID as the PRIMARY KEY.
2. Add a persisted computed column containing a cryptographic hash calculated from the values of the 20+ currently grouped columns.
3. Add an index in this new column that also includes the two amount columns. (Column OID is also needed, but is implicitly included as the primary key.)
4. Modify your process to update one of the rows in place and delete any remaining duplicate rows. Single (non-duplicated) rows should not be touched.

Using a single persisted computed column should greatly improve the efficiency of the grouping operation. Indexing that column is even better. Having the amount columns included in that index allows the initial scan and aggregation to be performed using a narrow index scan instead of a wide table scan. Finally, selecting one existing row to update in place saves the need to copy all those other columns and significantly reduces the insert/delete overhead.

Although the persisted computed column and associated index does have some overhead during the initial inserts, I believe this is more than offset by the potential savings.

For the persisted computed definition, the following add-column and index DDL can be used.

ALTER TABLE AW_FCT_000002_000001
    ADD HashColumn AS CONVERT(VARBINARY(32), HASHBYTES(
        'SHA2_256', -- SHA1 may be faster, but is deprecated
        ISNULL(CONVERT(VARBINARY(MAX), SCENARIO), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), PERIOD), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), DEPARTMENT), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), ACCOUNT), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), CATEGORY), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), CURRENCY), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), PROJECT), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), PARTNERSHIP), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), EMPLOYEE), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), BOOKING_DATE), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), ORIGINAL_CURRENCY), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), ANALYSIS_COMMENT), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), COMMENT_TYPE), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), NOTE), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), ALLOCATION_EXPLAINER), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), PIPELINE_VALUATION_DATE), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), PIPELINE_PROBABILITY), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), TECH_ORIGINAL_USER), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), TECH_ORIGINAL_ORIGIN), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), TECH_ORIGINAL_DATEUPD), 0x)
        + 0x00 + ISNULL(CONVERT(VARBINARY(MAX), DEPARTMENT_VISIBILITY), 0x)
    ))
    PERSISTED NOT NULL

CREATE INDEX IX_AW_FCT_000002_000001_HashColumn
    ON AW_FCT_000002_000001(HashColumn)
    INCLUDE(AMOUNT, ORIGINAL_AMOUNT)
    -- I'm assuming that OID is the PK, so is implicitly included.

The above calculation has a few limitations:

1. A null byte 0x00 is used to delimit column values, which should be acceptable for columns containing normal text values that do not contain null CHAR(0) characters.
2. A null column is treated as equivalent to an empty value ''.
3. Text values hashed exactly as represented, so are case-sensitive, accent-sensitive, etc. (If you need to match case-insensitive values, you can add UPPER() to each value above.)

I expect this should be sufficient for your use case. A more robust expression can be found in this answer. (I also recall having seen an approach that maps all of the columns to JSON or XML and then calculates the hash, but I can’t find that right now.)

The following SQL can then be used to consolidate the data:

DECLARE @Temp TABLE (
    HashColumn VARBINARY(32) NOT NULL,
    OidToKeep VARCHAR(36),
    CombinedAmount numeric(27, 9) NULL,
    CombinedOriginalAmount numeric(27, 9) NULL
)

SET XACT_ABORT ON
BEGIN TRANSACTION

-- Find and combine duplicates
INSERT @Temp
SELECT
    F.HashColumn,
    MIN(F.OID) AS OidToKeep,
    SUM(F.AMOUNT) AS CombinedAmount,
    SUM(F.ORIGINAL_AMOUNT) AS CombinedOriginalAmount
FROM AW_FCT_000002_000001 F
GROUP BY F.HashColumn
HAVING COUNT(*) >= 2

-- Update the row to be kept
UPDATE F
SET
    AMOUNT = T.CombinedAmount,
    ORIGINAL_AMOUNT = T.CombinedOriginalAmount
FROM @Temp T
JOIN AW_FCT_000002_000001 F
    ON F.HashColumn = T.HashColumn
    AND F.OID = T.OidToKeep

-- Delete the other rows
DELETE F
FROM @Temp T
JOIN AW_FCT_000002_000001 F
    ON F.HashColumn = T.HashColumn
    AND F.OID <> T.OidToKeep

COMMIT

The first statement selects and aggregates the data based on the HashColumn value and also limits results to those HashColumn values having duplicate (2 or more) rows. It then updates one chosen survivor row, and removes the other sacrificial rows.

If you had a date-added column and only need to consider data added after a certain date/time (since last successful run), you could add something like:

WHERE F.HashColumn IN (
    SELECT DISTINCT F1.HashColumn
    FROM AW_FCT_000002_000001 F1
    WHERE F1.DateAdded >= @SinceDateParameter
)

The above should be paired with an index CREATE INDEX IX_xxx ON AW_FCT_000002_000001(DateAdded) INCLUDE (HashColumn).

If you wanted to specifically preserve the row having the earliest date, you could change MIN(OID) AS OidToKeep to something like:

(
    SELECT TOP 1 F1.OID
    FROM AW_FCT_000002_000001 F1
    WHERE F1.HashColumn = F.HashColumn
    ORDER BY F1.DateAdded
) AS OidToKeep

If the above is used, the DateAdded column should be added to the earlier IX_AW_FCT_000002_000001_HashColumn index – CREATE INDEX IX_AW_FCT_000002_000001_HashColumn ON AW_FCT_000002_000001(HashColumn, DateAdded) INCLUDE(AMOUNT, ORIGINAL_AMOUNT). Your DATEUPD or EN_VERSION columns might be candidate substitutes for DateAdded for this use in selecting the earliest row..

See this db<>fiddle for a demo.