How and when to choose between Data Consolidation/Data Integration and Data Synchronisation?

Question

I am having some confusion as to how to decide what to choose between Data Consolidation/Data Integration and Data Synchronisation? I know that:

Data Consolidation/Data Integration: refers to the collection and integration of data from multiple sources into a single destination. During this process, different data sources are put together, or consolidated, into a single data store.

Data Synchronisation: refers to establishing consistency among systems and data stores and subsequent continuous updates to maintain consistency.

Now suppose I have a case like this regarding a company:

- Data is present in multiple sources often department wise

- Because of this majority of time company spends in validating data rather than on useful analysis for planning

- The data definition also varies between different sources

Now given such a case which is better to use - Data integration/consolidation or leaving the data in different sources and synchronize it?

Also what other useful and relevant information should I look for that may help in making the decision and how those additional information will reflect on my decision making?

Answer 1

Preliminary remark

First of all, the case that you present (single company, many departmental sources, lots of validations to overcome incomplete / inconsistent / unmatchable departmental data, and inefficiencies at the expense of useful analysis of data), is the typical business case for reengineering your systems or adopt an integrated software system such as an ERP.

But I realize that this is not always possible nor desirable.

The case for data integration

Data integration is a popular approach, as shows the rich offer for ETL solutions and the trend for entreprise data warehouses.

Despite its old roots, this approach is effective, feasible in almost any landscape (batch or realtime, complementary or disparate data, clean data or data to be cleaned and corrected, etc...) and robust.

The case for synchronization

On the other hand, recent technological developments, such as for example big data architectures or microservices, try to decouple systems and facilitate scaling (by avoiding shared database that might become a bottleneck).

Data synchronisation through event sourcing and messaging is a very effective way to decouple the systems and achieve scalability, if direct consumption from a service API is not worth the added complexity of syncrhonization.

Synchronization requires however a consistent model between the applications which is good DDD practice but not always possible in an heterogenous historically grown system landscape.

Answer 2

Looking at this from the late 2017 perspective, data integration would be a good choice, coupled with a review of existing systems to determine which are candidates for upgrades or replacements.

By using a data lake or data warehouse to consolidate the important business data from each of the disparate systems, you provide users with one source for research and analysis. The data is validated as part of the ETL process and therefore you have checks and balances to assure quality data in. In addition, moving the querying and analysis off of the operational systems allows their power to focus on day to day transactions.

Another alternative is to use an ELT (extract, load, transform) system. This may not be appropriate for all of these disparate systems, but it offers advantages for the compatible ones. ELT would move the data off of operational systems and then perform the transformation process or transform at query time. This approach eases the maintenance burden, since there's no cumbersome transform process before the data is available to users. Some types of systems (both in function and in data types) are likely more suited to ELT than others. To get a better understanding of the difference, here's a good ETL/ELT 101 article from Panopoly's blog.

To keep the warehouse up to date, I would recommend using real-time or close to real-time data streaming from transactional systems and regular dataflows from non-transaction systems. With the available tools, these processes could be automated to a great extent once the original workflow was verified.

Answer 3

This depends on a lot of external factors. If you just look at the different data schemas it would probably not be that hard to consolidate, to either drag one of the data sources into the other or start fresh with a third one, build that from scratch taking into account the needs of both existing ones and migrate the data. But there will be dependencies like

• applications, technically and logically tied to either store;
• organisational responsibilities for quality of service, continuity, keeping the data current, privacy issues, commercial exploitation of the data, et cetera;
• licencing fees for the different database systems which may vary enormously, the contract with the vendor may just have been extended for a number of years which would be regarded an "investment" by upper management;
• general company politics: who gets to own the bew data?;
• ?

So it is impossible to tell which would be preferrable in general.

Answer 4

When I think in synchronization, a few ideas comes to my mind:

• High availability of the data among systems.
• Equating data among these systems
• Data exchange between these systems
• High volatiliy of the data
• High frequency of the data exchange (once a min, an hour, a day)
• Lightweigth process
• Simplicity

A prime example could be SCMs. We synchronize code among several data stores: our local file system, server's file system and co-worker's file system.

We want the repositories to be available as long as possible to allow us to synchronize the code often.

The higher is the frequency of the synchronization, the easier is merge for everyone.

On the other hand, when I think in consolidation (integration), I tend to think in:

• Large amount of data
• Less frequency. Daily, weekly, monthly, yearly.
• No data exchange, just systems bulking data in a single data store
• Heavy processes
• Complexity
• Different data formats transformed into a single data model
• Centric data store
• Quality of the data

A prime example could be ETLs. Usually ETLs take place less often than synchronizations.

Because the amount of data to move and transform, the process is usually heavier and complex. Heavy loads and complex transformations require more resources. Or dedicated environments.

The data usually comes from several sources but it rarely flows back to these sources.

That being said, we come to realise that both processes are not mutually exclusive. We could decide to perform a ETL overnight first and later perform continous synchronizations among the different systems.

Back to your specific case, depends on the requirements. Usually we detect the priorities during the requirements gathering and we choose the proper strategy during the analysis of the requirements.

According to your scenario, looks like the company needs a data consolidation to simplify the access and the validation of data that come from different sources.

So, I would first put together all the data sources in a single data store and later (if needed) allow synchronizations between the centric data store and the differents departments.