Translate data between inconsistently-matched data structures

Question

How can my program best represent a translation between imperfectly-matched data structures?

I am tasked with a one-way translation of data from one system to another. Both systems are established, I don't have the option of changing their data structures.

If the structures corresponded item-for-item, it would be simple to translate:

• Iterate over all input items:
  • Transform the item
  • Populate the output item

(We are implementing the translation in Python, so if it were that simple I would just define the item-level transforms, then iterate the data structures in a single statement.)

That won't work though, because the systems have inconsistently-matched data structures.

The data structures have largely-overlapping correlations that we've discovered, but there are many inconsistencies; a sequence here will be a single item there; a pair of unrelated items here will be a homogeneous sequence there; and so on.

What patterns can I follow to represent the mostly-correlated but imperfectly-aligned data structures, such that the translation describes all the mapping complexity and all we need do then is connect the systems at each end?

Answer 1

What patterns can I follow to represent the mostly-correlated but imperfectly-aligned data structures, such that the translation describes all the mapping complexity and all we need do then is connect the systems at each end?

This summary question suggests there might be some kind of pattern or procedure to follow that will be simple, complete, and obviously correct. Unfortunately, there isn't.

Consider the "many inconsistencies" you mentioned, or the "correlations that we've discovered." You don't know the entire set of appropriate mappings. From experience you probably know a lot of them--but not all, and there's no guarantee a new one won't crop up tomorrow. Or that a transformation you thought was solid and reliable will prove incorrect under some to-be-discovered circumstances. Rude awakenings are an unavoidable fact of life on the ETL frontier.

So, how to move forward?

• Bogdan's suggestion of using a set of structured from_X_to_Y routines and possibly a transformation pipeline library is a good one. With or without an object mapper library to help you, you will inevitably have to do this if the structures don't match in many places and you need to write lots of custom converters for the differences. These help structure your transformer work.

• Instrumentation: Being able to find what went wrong, where, and for what reasons is invaluable. Ladle your transformations with assertions. If you believe certain conditions hold, have the code confirm those assumptions. Assertions become tripwires for identifying problems you didn't know the data had, and doing so before it causes bigger problems.

• A crash-cart: A first-class mechanism for studying and reworking error cases that emerge. When the transform fails, have code and tools ready to print out and describe the error cases in a clear, easily read fashion. Expect errors, and make them easier to study (one by one, not just as a few records among millions in the middle of a full data dump). Pilots say "plan to crash." Not because they want to crash, but because if things go wrong, they want to have been thinking about mitigation strategies long before the emergency erupts. That principle applies here.

• Manage by exception. In "dirty data" ETL projects--more or less all of them at scale--it's super-helpful to have a "set aside" ability. If you process a million records and find 13 error cases, it's so much better to be able to submit the 999,987 successes, setting aside a few problem children for "out of band" study and remediation. If you have to fix the 13 failures before any records are successfully submitted, now you have a huge potential bottleneck. How long will it take you to figure out those bad cases? An hour? A day? A week? Having faced all those delay scenarios, I'll tell you that not being able to submit the work you know is good because of a few outliers is terrible, especially when you're being held personally responsible for the process. Having to sift through 1M records to find a baker's dozen errors is also terrible. So do yourself a favor and have a process in place that assumes errors will arise, that calmly sets those aside and continues with the main submissions. Set-asides aren't always feasible in production systems that expect atomic submissions, but if you can, use them. Either way, take the errors to your study rig, figure them out, update the code, and iterate the submission process.

• Ongoing notifications and on-call process. You don't mention your operational situation, but after transforms are "done" and in service, errors and omissions can still occur. Hopefully your assertions will help find them, but some may slip through to the production data submission. Either way, you can set up automated notifications of problems back to you or the development staff. This can be as simple as automated emails or texts. But you need some way to efficiently involve those who can appropriately investigate and "work" problem cases. Automating this can enormously reduce frustration, both for you and the people who will otherwise grumble "Yeah. I have to tell the developers their code failed. AGAIN." Converting that into a process where they are aware that the transform is heuristic not perfect, and where Development always seems to be on top of any updates necessary--it's not a technology component per se, but it will make the overall process go more smoothly.

Answer 2

In Java there is a project called Dozer that handles such cases. You have an XML file where you declare what in the source corresponds to what in the destination and Dozer does the job. It handles conversions between data types, if the types have the same fields you don't need to specify anything it just matches them by name, if you need custom conversion you can do that too, etc.

There might be something similar in Python. A quick Google search found me this: https://github.com/marazt/object-mapper. There might be others. They might support what you need or get you part of the way there.

But my advice though would be to create an utils class with functions in it of the form:

def from_<TypeX>_to_<TypeY>(x):
    y = TypeY()
    # ... read x and do the mapping here to y
    return y

to map between the data structures. With or without an object mapper library to help you, you will inevitably have to do this if the structures don't match in many places and you need to write lots of custom converters for the differences.