Let’s talk business IT systems integration: in this three minute article I’d like to talk about using software to integrate IT systems. For those who’ve done it before, you likely know how convoluted and problematic it can be to keep data consistent (between systems), and for those who haven’t: welcome to a world of pain, data inconsistency, incompleteness, issues with data quality, synchronicity, and data integrity violations. Yet integrat-ions are supposed to achieve just that: data integrity between ‘upstream’ (where data’s produced) and ‘downstream’ (where it’s consumed). To get it right, to do it right, and do it well, we’ll cover key aspects you should consider and get right for reliable data integration between systems.
That’s a long intro (it’ll be shorter in the next article), so I’ll use the remaining two minutes to discuss challenges with two mechanisms by which data is moved, ‘downstream’: batch-based and event-based.
‘Batch’-based, also known as ‘Bulk’-based because it runs relatively infrequently (compared to the accumulation of data that needs to be moved between systems), is at best time-based. At worst, it is count-based and is often (but not always) triggered by scanning a folder or table for data that’s been modified since the time the integration last ran.
If you don’t move the data out of the way after it’s been scanned (so that on the next scan it’s picked up again), you must keep track of the date and time the scan last ran to avoid sending data downstream when it’s previously been received, particularly if duplicates aren’t allowed. Also, the date/time is not the time the integration finished moving data; it’s the time it started scanning. If data changes turn up during the time it takes to complete the scan, they’d better be marked with a date/time stamp that’s after the time you ‘say’ the integration completed its scan, otherwise next time you scan, you won’t pick up the data that changed during this window. Furthermore, if for example you start writing a file, then it gets detected by the scan, it tries to send it, but if you haven’t finished writing the file, you might send it incompletely. In that case you’ll have to also scan or track when the file stops being written to (size doesn’t change in a certain period of time or modification; time stops being updated by the file i/o handler). You can use a ‘flag’ file to indicate a file (or database update) has been written completely, but that involves the ‘sending’ system to fulfill this requirement and sometimes systems can’t be changed, are too expensive to change, or the business owner prohibits the action.
Having a last-time-ran marker of some sort is a useful way to revisit (or replay) data moving ‘downstream’ because you can go back in time (‘rewind’ the clock ten minutes) or you can start from scratch, by removing the marker file entirely to indicate that all the data from the beginning of ‘time’ (in that system) should be ‘sent’ downstream. Being able to catch a view of all data is also a useful way to do a full load or full comparison between systems. However, with Auxillery, we believe you should always know and trust your data is synchronized between systems (without fear of duplication / re-processing) and not prohibited change, citing cost, risk, and/or out-of-scope, without having to write your own scan/detect logic, which can be challenging. Maintaining additional software in this space can be an additional burden on your cost to operate such support systems.
Let’s talk about ‘Event’-based data integration–as an alternative mechanism of sending data downstream–and discuss its pros and cons, in the next article, because event-based, ties-in with Change Data Capture (CDC) (which can also create consistency headaches!) and “Pub-Sub” (Publish/Subscribe), and both CDC and Pub-Sub deserve as much attention (representation) too.
So why is it necessary to do Batch-/Bulk-based integration in the first place? Many of these old/legacy systems were designed to run in isolation (i.e. weren’t designed to be operated in conjunction with other systems, but are still critical to business operations), or were designed as batch-based with rudimentary data export/import ability*, while modern systems–more recently designed and expected to be integrated with other systems–still may not handle receiving duplicate data very well.
Enterprise software companies are on standby to sell you solutions in this space, but unfortunately they tend to stop short of solving specific issues with synchronicity and duplication that are only solved by adding (your own) custom logic. Or they’ll also sell you a compl-e-mentary (not compl-i-mentary) system to manage ‘master’ data and de-duplication.
At Auxillery, we believe in self-care: your data ought to be able to ‘take care’ of itself, and it should be self-aware: if it needs to be moved, it should let you know (event based) – you shouldn’t have to go in and determine that for yourself (batch-based or bulk-based). If it doesn’t have integrity, it should tell you (you shouldn’t have to try and figure it out on a support call at one o’clock in the morning). At Auxillery we’re building smart, programming language-agnostic, data technology, with one RIP unified API across all major programming languages, that’ll get data where it’s needed when it’s needed, and let data types (as well as data fields) be created, read, updated, and deleted only by those authorized to do so.