Ok, I've got this approach... 

In my ETL, for new rows, the recnum will be null, as it's not in the source table; for existing rows, the recnum will be populated by the target table.  I'm doing a FULL OUTER JOIN to detect new/existing/deleted.

As long as my ETL code "does the right thing", this should work.  I do think the trigger is more bulletproof though. 

Does a nonclustered columnstore index have the same performance as a clustered columnstore index, it just takes up more space?

This approach requires two inserts, one for new rows (no ID), one for existing rows (includes ID).

Thoughts on this approach?

USE [tempdb]
GO

DROP TABLE IF EXISTS #t1
GO

DROP SEQUENCE IF EXISTS recnum
CREATE SEQUENCE recnum START WITH 1 INCREMENT BY 1
GO

CREATE TABLE #t1 (
  ID BIGINT DEFAULT NEXT VALUE FOR recnum NOT NULL,
  name VARCHAR(100)
)
GO

-- new rows
INSERT INTO [#t1] ([name])
SELECT name
FROM sys.databases

SELECT * FROM [#t1]

-- existing rows
INSERT INTO [#t1]
SELECT *
FROM [#t1]
ORDER BY [name]

SELECT * FROM [#t1] ORDER BY name

-- mixed
DROP TABLE IF EXISTS #t2
CREATE TABLE #t2 (ID BIGINT, name VARCHAR(100))

INSERT INTO [#t2]
VALUES (NULL,'FOO'),
   (1,'master'),
   (NULL,'BAR')

INSERT INTO #t1 (name)
SELECT name
FROM #t2
WHERE ID IS NULL

INSERT INTO #t1 (ID, name)
SELECT ID, name
FROM #t2
WHERE ID IS NOT NULL

SELECT * FROM [#t1] ORDER BY name