After performing a join, how do you handle matched and unmatched records?

When you perform a join between a source table and a stream, you often need to distinguish matched and unmatched rows. Use a LEFT JOIN and check for NULLs: SELECT s., st. FROM source s LEFT JOIN stream st ON s.id = st.id; If st.id IS NOT NULL, the row was matched and you can apply an UPDATE; if st.id IS NULL, the row is new and you can INSERT. For deletes, the stream will contain a row with METADATA$ACTION = 'DELETE', so you can filter those and perform a DELETE on the target. This pattern allows you to handle all three CDC actions in a single statement.