Function dedup_view_sql 

pub fn dedup_view_sql(
    warehouse: Warehouse,
    view_fqtn: &str,
    changes_fqtn: &str,
    pk: &[&str],
    engine: SourceEngine,
) -> String

Build the current-state dedup view over a <table>__changes log for warehouse. pk is the change log’s primary key column(s); engine selects the __pos parse. The view is free to define; reading it scans __changes (billed), kept cheap by clustering the log on pk.

Soft delete. The view keeps the latest change per PK unconditionally and projects the winning row’s __op into a boolean DELETE_FLAG_COLUMN (__op = 'delete'), rather than dropping deleted rows. A tombstone therefore survives with its last-known column values — an auditable delete instead of a silent disappearance. Consumers read live state with WHERE NOT __is_deleted.

Backfill. cdc.initial: snapshot preexisting rows load from a plain full-snapshot parquet, so their __op/__pos are NULL in __changes. The flag is COALESCE(.. , FALSE) (a NULL __op is a live snapshot insert, not a delete — otherwise WHERE NOT __is_deleted drops the whole backfill), and the order ranks NULL __pos last (see below) so a later change always wins.

The subquery + __rn structure (rather than a QUALIFY) is deliberate: the flag must reflect the winning row per PK, computed after ROW_NUMBER. Note __op is both dropped from the * expansion and referenced by the flag expression — both BigQuery EXCEPT and Snowflake EXCLUDE allow that (the exclusion only affects *, not an explicit reference).