4 ms·

The problem with plain/primitive upset/merge operations is they don’t always support handling of what should happen when a row is deleted from the source. Peopl

by gregw2 3y ago

The problem with plain/primitive upset/merge operations is they don’t always support handling of what should happen when a row is deleted from the source. People new to data engineering don’t always recognize this. Im those cases, one needs “delete-then-upsert” or “soft-delete-then-upsert” in your upset/merge implementation. I was a tad disappointed to see this only peripherally alluded to. With some flavor of merge implementations you can do this with a pair of merge calls but in my opinion, ideally it should be one merge operation and db vendors should wise up to this. Per https://modern-sql.com/caniuse/merge https://modern-sql.com/caniuse/merge only SQL Server and BigQuery currently support “when not matched by source”. But Snowflake lets you have multiple “when not matched” clauses with “and” expressions so perhaps you can do it on that. Redshift docs show examples of using two merge statements.

hn_throwaway_99 3y ago
> The problem with plain/primitive upset/merge operations is they don’t always support handling of what should happen when a row is deleted from the source. I didn't understand what you meant by this - you seem to be referencing a larger workflow (e.g. keeping 2 tables in sync), and I'm not understanding how is relevant to the "upsert" primitive. Can you give an example?
- gregw2 3y ago
  You are bringing data from a transactional (OLTP) system into a reporting/analytics (OLAP, lakehouse, data warehouse, data hub) system. The analytic database is expected to maintain a list of all customers and their transactions going back years. The transactional system stores a more limited history for operational performance reasons. You daily pull the list of customers from source transactional system and upsert/merge it into your analytic database. Over time the source transaction team removes old unused or mistakenly created customers. If you use a pure upsert without awareness/handling of source-side deletes, your analytic list of customers grows without any way to indicate to reporting users which customers are still in your transaction system (or when they got deleted from the transactional system.) But various analytics and/or APIs from your analytic system need to exclude such no-longer-present customers… “Why am I seeing these customers in analytics that aren’t in the source system?” “Can I just get the current customer list from the analytic system?” “Can your analytics component X quit transmitting to me these old/bogus customers I don’t see in the transactional system?” people will ask you…
  - nicoburns 3y ago
    UPSERT support is really for OLTP databases with frequent transactions that may conflict. If you're doing periodic batch jobs then you can just use plain insert/update/delete (and I believe a common pattern for syncing to an analytics database would be to first dump all the data from the transactional system into a staging table, from which you can run easily comparative queries (e.g. to check for records no longer present in the transactional database) before updating the main tables.
    - gregw2 3y ago
      Re ‘UPSERT is really for OLTP’, I find that claim surprising. If you mean the oracle UPSERT command, you may be right and should ignore my post, but if you mean upsert concept/terminology/MERGE-usage, I’m not sure I agree. I admit I have no visibility into the true history of Oracle UPSERT vs ANSI merge (2003) and their initial target audience or the audience for early implementations. A web search on my part turned up no clarity. But I have three reasons I don’t perceive upsert as as OLTP-not-OLAP feature: 1) I vaguely recall seeing documentation from Microsoft around 2008 when they first implemented merge (I was excited at the time, forgive me) that it was for analytic use cases. 2) Anecdotally I did telecom+web “OLTP” programming for many years without hearing about the upset concept, but heard about upserts in my first few weeks working in the analytic (ETL) space and it was clearly a fundamental operation there. I suppose the upsert concept was also fundamental for web crud operations and ensuring idempotency but nobody called it upsert back when I was doing that — we just wrapped an insert and update in a transaction. I acknowledge this is an anecdotal claim but it is my Bayesian prior. 3) Merge is a bit notorious for wonky transactional behavior in corner cases… so I’m not confident OLTP was/is the target audience. Additionally I am not sure why you suggest batch usage implies you wouldn’t upsert. That’s a bit bizarre to me. The operation for moving data from staging tables (for dimensional/attribute data) to main target tables often uses upsert logic of some form.
      - nicoburns 3y ago
        I guess I would consider the defining feature of true UPSERT support as being an atomic operation that does not exhibit "wonky transactional behaviour". Which means Postgres's ON CONFLICT...DO UPDATE, and not MERGE. Given that MERGE has this "wonky transactional behaviour", I don't really see the point of using it at all. I guess it gives a more convenient syntax in some cases, but it seems like it will always be possible to replicate the functionality by something like separate INSERT/UPDATE operatiosn (optionally wrapped in a transaction). And OLAP use cases are unlikely to have lots of concurrent transactions that are liable to issues with this approach. So basically, while you might want to UPSERT in an OLAP use case, you probably don't need watertight atomic UPSERTs, which are the feature that requires special database support.

by gregw2 3y ago

hn_throwaway_99 3y ago

> The problem with plain/primitive upset/merge operations is they don’t always support handling of what should happen when a row is deleted from the source. I didn't understand what you meant by this - you seem to be referencing a larger workflow (e.g. keeping 2 tables in sync), and I'm not understanding how is relevant to the "upsert" primitive. Can you give an example?

gregw2 3y ago
You are bringing data from a transactional (OLTP) system into a reporting/analytics (OLAP, lakehouse, data warehouse, data hub) system. The analytic database is expected to maintain a list of all customers and their transactions going back years. The transactional system stores a more limited history for operational performance reasons. You daily pull the list of customers from source transactional system and upsert/merge it into your analytic database. Over time the source transaction team removes old unused or mistakenly created customers. If you use a pure upsert without awareness/handling of source-side deletes, your analytic list of customers grows without any way to indicate to reporting users which customers are still in your transaction system (or when they got deleted from the transactional system.) But various analytics and/or APIs from your analytic system need to exclude such no-longer-present customers… “Why am I seeing these customers in analytics that aren’t in the source system?” “Can I just get the current customer list from the analytic system?” “Can your analytics component X quit transmitting to me these old/bogus customers I don’t see in the transactional system?” people will ask you…
- nicoburns 3y ago
  UPSERT support is really for OLTP databases with frequent transactions that may conflict. If you're doing periodic batch jobs then you can just use plain insert/update/delete (and I believe a common pattern for syncing to an analytics database would be to first dump all the data from the transactional system into a staging table, from which you can run easily comparative queries (e.g. to check for records no longer present in the transactional database) before updating the main tables.
  - gregw2 3y ago
    Re ‘UPSERT is really for OLTP’, I find that claim surprising. If you mean the oracle UPSERT command, you may be right and should ignore my post, but if you mean upsert concept/terminology/MERGE-usage, I’m not sure I agree. I admit I have no visibility into the true history of Oracle UPSERT vs ANSI merge (2003) and their initial target audience or the audience for early implementations. A web search on my part turned up no clarity. But I have three reasons I don’t perceive upsert as as OLTP-not-OLAP feature: 1) I vaguely recall seeing documentation from Microsoft around 2008 when they first implemented merge (I was excited at the time, forgive me) that it was for analytic use cases. 2) Anecdotally I did telecom+web “OLTP” programming for many years without hearing about the upset concept, but heard about upserts in my first few weeks working in the analytic (ETL) space and it was clearly a fundamental operation there. I suppose the upsert concept was also fundamental for web crud operations and ensuring idempotency but nobody called it upsert back when I was doing that — we just wrapped an insert and update in a transaction. I acknowledge this is an anecdotal claim but it is my Bayesian prior. 3) Merge is a bit notorious for wonky transactional behavior in corner cases… so I’m not confident OLTP was/is the target audience. Additionally I am not sure why you suggest batch usage implies you wouldn’t upsert. That’s a bit bizarre to me. The operation for moving data from staging tables (for dimensional/attribute data) to main target tables often uses upsert logic of some form.
    - nicoburns 3y ago
      I guess I would consider the defining feature of true UPSERT support as being an atomic operation that does not exhibit "wonky transactional behaviour". Which means Postgres's ON CONFLICT...DO UPDATE, and not MERGE. Given that MERGE has this "wonky transactional behaviour", I don't really see the point of using it at all. I guess it gives a more convenient syntax in some cases, but it seems like it will always be possible to replicate the functionality by something like separate INSERT/UPDATE operatiosn (optionally wrapped in a transaction). And OLAP use cases are unlikely to have lots of concurrent transactions that are liable to issues with this approach. So basically, while you might want to UPSERT in an OLAP use case, you probably don't need watertight atomic UPSERTs, which are the feature that requires special database support.