How to ensure data consistency in system with multiple databases?

Question

Let's say, in a rather big application suite with multiple more or less integrated products, data is stored across multiple databases. Some of them are SQL-ish DB clusters, some are MongoDB clusters.

Some entities (= "rows" or "documents", depending on the type of DB) are stored in several databases. Many (if not all) entities depend on entities of another kind.

Now, the problem is: Data consistency and validation. There is a lot of data that is not in sync with other data and/or not in sync with the intended schema or the intended application logic.

Let's take an example and say the system is about pets. We have an SQL table called pet and a row for dogs. In MongoDB, there is a column for food containing document IDs of a collection named petFood. However, it could happen that:

• The document with the linked ID does not exist in petFood collection.
• Some petFood documents have no pet linked to them.
• The data might contradict each other, like the dogs row could be linked to a document with suitableForDogs: false property.

Additionally, there might be data consistency problems within one MongoDB document itself. E.g MyFood could be set both to availableInAsia: false and have a distributorInAsia: ACME LTD property (which doesn't make sense because something which is not available in Asia cannot have a distributor there`).

You may ask how come that the data has these issues. Well, there could be various reasons:

• In some situations, stuff is changed directly in DB or with an ad-hoc script instead of in the application.
• There is old data not up to date with current application logic.
• Bugs in the application code leave some data in the wrong state.
• DB migration problems happen, backup restores only work partially, and stuff like that.
• ... etc etc.

These things do happen and they will happen. Hence, manually cleaning up once and then hoping that the data will never become messy again, is pointless.

The same goes for the well-meant advice you always hear regarding MongoDB: "Enforce schema and validity on application level". It simply does not work like that.

So, the question is: What to do? I need to find a solution that makes sure that the data stays consistent and valid over time.

The best solution I could come up with

• Programmatically define all the rules that play a role regarding data consistency/validity.
• Running a regular "checker script" which checks that all the rules are followed everywhere.
• If not: The checker notifies the responsible persons like "Hey, the row dogs in pet table is assigned to a petFood document which has suitableForDogs: false. Please fix it!"
• Maybe add some kind of threshold like: A problem must appear twice in a row. (In order to exclude cases where the check happens to run during an async operation.)

But that means a lot of work and there is no technical guarantee that the people who get these notifications will react accordingly.

So, what would be a better technical approach?

(I'm not asking for organizational measurements like 'Take way database access from people notoriously and regularly messing up the data'.)

Edit:

Thanks for all your answers so far. I was aware that it might be a little bit too unclear what I'm asking for. I think your thoughts help me to rephrase what I'm looking for. I know that such a problem is complex and that there are many aspects to it like financial, organizational ones etc. Some of them are off-topic here I guess.

So, what I am looking for are technical actions that a motivated dev-team could implement within, very roughly speaking, a few weeks. Either on the database layer or as an "extra" application layer or on "something in between the application and database layer".

In order to demonstrate what I was fantasizing about I'm making up something now:

"This problem is called XY and the state-of-the-art solution is: Put a sample of correct data together, pass it through tool XY which will deduce some generic rules from the data, then set up an XY server which will regularly make two copies of all your databases: one copy for the correct data, one for the wrong data. Then you can decide from here what to do with them."

If there is any generic solution along those lines out there, then I'm not aware of it and would be thankful for any tips or even hints on what keywords to google for.

"No, something like this does not exist", would thus be an answer too.

Answer 1

There are no easy solutions.

Distributed consistency is fundamentally difficult, with some hard limits to what is possible. See the CAP theorem and Fallacies of distributed computing. And doing anything in a large and complex system is inherently costly and risky, so your desire of "a few weeks development time" seem incredible optimistic to me. I have worked at companies spending years to slowly move from one database architecture to a newer architecture.

If this problem has been created by using ad-hoc development practices, it is unlikely that the problem can be solved without some fundamental changes.

Writing a "checker script" as you mentions might help, but it will likely not solve the problem. Any warnings it generates may be ignored, and the script itself may become outdated unless rigorously maintained.

Conways Law

My guess is that this is a case of Conways law, i.e. that if your architecture is a mess it is likely because your organization is a mess. A possible approach is the "Inverse Conway maneuver" - I.e. you should change your organization to mirror how you want your architecture to be. If you want consistent data, create a group that has the responsibility to manage data and data consistency, and the power to actually do that. My guess is that the first step such a group would take would be to make all data access go thru a single service/application layer/API, and once that is done, start moving towards using a single database.

Or you could embrace the micro-services philosophy of independent groups developing independent services/applications with separate databases, where interaction between applications are governed by APIs with some type of service and backward compatibility guarantees. This would mean that cross database dependencies are minimized, and that there should be a well defined behavior for any inconsistencies that do occur. But this require some serious thought about where the boundaries should be, and how to handle all the various failure modes.

Of course, suggesting to completely change the way the company works is easier said than done.

Answer 2

You don't! You don't ensure consistency across multiple databases.

If there are multiple technologies, and multiple databases in the conventional sense, then ensuring consistency means designing everything to work together as an integrated whole again.

You might find some technologies simply won't compose into a system because they aren't designed to be composable together in the first place.

Most likely what you'll end up doing by trying to make a single database out of heterogenous technologies, is impairing every possible facility each technology provides to make a database manageable - facilities that have had the world's best minds chiselling away on them for decades - and you'll be tasked with reimplementing all these facilities at the application level. Journalling, transaction management, consistent online backup and recovery, scaling and performance. In reality you won't succeed at this.

As you anticipated, your real option is limited to performing extensive and frequent reconciliation, and resolution of differences as they are found.

Answer 3

"I'm not asking for organizational measurements"

Don't get me wrong, but that sounds pretty ignorant. You are simply describing all kind of quality issues in data. Quality issues require a quality control process, and such processes usually require a combination of technical and organizational measures. Moreover, they need to be tailored to the individual case.

Lets look, for example, at the approach you described, with a checker scripts which tags data with labels "Please fix it". That's a technical measure which requires an organizational process in addition: someone who gets informed about these tags and does the fixing. Next step is to think about the importance of the data and their consistency:

• is the data consistency so important that someone will be payed to fix the data on a regular basis? For some organizations and systems, "regular" means "within a few hours", for others, it may mean "once every two years".

• maybe it is sufficient to let the one fix the data who tries to use them in some downstream use case, at the time when they stumble over the data?

• or maybe it is sufficient to leave the inconsistency as it is, just display the inconsistency as a warning for the one who is trying to order the pet food for a dog?

When creating applications which work multiple, distributed databases, it is necessary to make the applications robust enough to expect certain inconsistencies and react accordingly. But what "accordingly" means is highly case dependendent, and cannot be answered out of context, without knowing what the data is used for, and wihout knowing the cost/benefit relationship of "fixing all issues ASAP" vs "live with the issues".

Let me further mention another well-known general strategy besides your "checker script":

• if you have the same information stored in different places, declare one place as the "leading one", the "single source of truth", and sync the others in a downstream manner.

For example, you may consider to store the information of food being suitable for a certain pet primary by where the food document links to, and sync the suitableFor... fields automatically (and only automatically!) from the former information. Or, you may go a step further and remove the suitableFor... fields completely from the food documents (this, however, requires you to know all current usages of those fields in your system, and the ability to change those usages). As you see, this may require organizational changes, since the suitableFor... may have been maintained by some person X manually beforehand, which now may has to use a different application for maintaining the food - pet links, or maybe has nothing to do here any more, because maintaining the food - pet links was already someone elses job.

So in short, there isn't a "one-size-fits-all" solution to this problem.

When asking for "technical actions that a motivated dev-team could implement within, very roughly speaking, a few weeks", the answer is neither "no there are no such actions" nor "there are these actions...". The answer is "it depends heavily on the case if certain technical actions will solve the issues, and it is pointless to think about technical measures without taking their organizational impact into account.

Answer 4

Distributed redundant data

My approach would be thus (I restrict myself to relational DBMS terms, i.e. "table" would include your MongoDB analog):

• Define, for each table, which of your databases is the "master". Authoritative changes to rows in that table are made in that database only - that particular database is the only "source of truth" for those entities.
• If it makes sense, provide some kind of API to access that table, and/or some kind of pub-sub mechanism (for example, Kafka) for clients to receive upserts. If you find applications that have a copy of that data and are manipulating that, then change them to use that API.
• If you absolutely require the data to be cached or mirrored somewhere for whatever reason (there are plenty of valid ones, not the least performance in a distributed environment, but sometimes simply the ugly truth of historically grown system) make sure this is clearly documented and implemented in a form that is not a "master" database; i.e. the inferiour DB would be receiving updates to that table not via business process, but via separate caching or mirroring approaches (i.e., detect changes on the master and distribute them to the slaves in some fashion that makes sense - maybe on the DB level via a warm/hot standby, or via some pub-sub mechanism like Kafka).
• If switching a slave who is modifying data in their own copy can simply not be changed to calling an API in the master, then invent a new table that reflects "changes to $TABLE" - i.e. if you have a table PEOPLE on your master, then on the slave, when trying to change that table, you would also generate "events" in a second table PEOPLE_EVENTS_IN_SLAVE_APP (or whatever name you come up with). The slave is then the master of that table, and the rest of this section applies (those events would be transferred to the master of PEOPLE, integrated, constraints checked and so on and forth - and when due to this the PEOPLE table on the master changes, those changes will be sent back to the slave again as per the next bullet point).
• The mechanism to replicated data from master to slaves for caching purposes should be such that you can do a full transfer with relative ease - i.e., the only manual intervention should be kicking off the process, the rest should be automated.
• Whenever such a mechanism has been created, do a full transfer once. Then, if you notice deviations between master and slave later, figure out what changed the values in the slave DB, and work diligently on reducing these reasons, as far as possible. If it is simply not humanly possible (or not within budget), you can opt to do a force refresh (i.e., full transfer) regularly and fully automated.

De-normalized data

This, unfortunately, is also simply a truth in real-world distributed systems (or, heck, any kind of single DB if it has grown over sufficiently many decades).

Unfortunately, it is hard to give a generic recommendation here. Obviously, where you can, work on normalizing your data. You write that there are ad-hoc manipulations -> this can often be just fine, depending on the domain, but the database schema should be thus that even with manual intervention, an invalid state simply is not possible to achieve. There is nothing special about this, database normalization is a more or less solved problem.

For those DBs that are not really as powerful in this specific regard (i.e., MongoDB where you are sure that you cannot use schema fidelity for some reason), maybe the impact is lessened after you have strictly done the previous approach mentioned in this answer, i.e., changed every single entity so that it has a defined, single "master" in your whole distributed system.

Answer 5

This can be done with transactional databases using the two phase commit by the transaction manager:

• Transactions are prepared on all databases, transferring all data.
• Transaction manager verifies that these transactions are actually prepared and ready to be committed.

Anywhere above this point the manager can easily rollback transactions if some databases experience problems.

• Transaction manager issues commands for all nodes to commit the transactions.

A well designed database is expected to report errors, if any, when preparing the transaction and not during the final commit stage.

• Transaction manager verifies if all transactions are committed. If for some reason commit message failed to reach some nodes (unlikely), it can be re-sent.

If transaction manager crashes itself, it can always query the pending transactions after it recovers and continue from where left.

Answer 6

Your initial approach of programmatically defining rules and running a checker script is a step in the right direction. However, as you rightly pointed out, relying solely on notifications to responsible individuals may not guarantee consistent data maintenance.

A more technically oriented solution would involve considering data quality tools and techniques. You could explore the use of data quality management software that offers features for data profiling, cleansing, and monitoring. These tools can help you establish and enforce data quality rules, scan for inconsistencies, and trigger alerts or automated actions when issues are detected. By implementing data quality management solutions, you can systematically address data inconsistencies and validation concerns across multiple databases.

Additionally, you might want to investigate the field of Master Data Management. MDM systems are designed to ensure the consistency and reliability of critical data across an organization. While it may take more than a few weeks to implement a full MDM solution, some MDM platforms offer rapid deployment options and can be a valuable long-term investment for maintaining data consistency.

Your quest for a comprehensive, technically-driven solution is not unwarranted, and the right data quality and MDM tools can significantly simplify the process of keeping your data consistent and valid over time. While there might not be a one-size-fits-all "XY" solution as described, researching data quality management and Master Data Management solutions could lead you to the tools and techniques that fit your specific needs.