3 ms·

You are bringing data from a transactional (OLTP) system into a reporting/analytics (OLAP, lakehouse, data warehouse, data hub) system. The analytic database i

by 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

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.