What are some design ideas for a data mapping and transformation application?

Question

Here is a high level outline of the project:

• We frequently need to convert data from a new incoming system to our in house system (sort of a basic ETL process)
• We would prefer to do this dynamically, allowing an analyst to use a GUI to map source fields to target fields and define basic transformations
• The basic flow will be as follows:
  1. Client provides flat files with source data
  2. Program dumps these flat files into a SQL Server database (no transformations)
  3. User maps data and defines transformations in a web-based GUI
  4. GUI calls web services that perform the transformations and generate new files in the target system format

My responsibility will mainly be the services mentioned in step #4.

My question is: does anyone have any suggestions on best practices for designing an application like this? My initial thought was to to have the services accept objects that represent a business object (say an Account), and all the fields and mapping/transformation info for those fields.

Really rough example:

//BusinessObject --> Field

//Example: Account

{
    fields:[

    {
        targetFields: ["NUMBER"],
        sourceFields: ["AccountNum"],
        transformations:
        [
            {
                type:"trimspaces",
                expression:""
            },

            {
                type: "format",
                expression: "%03d"
            }
        ] 
    },

    {
        targetFields: ["NAME"] //etc...
    }

    ]

}

In this example, a target field belonging to the Account object called "NUMBER" would be populated with the source field "AccountNum" with two transformations applied (trim spaces and pad with 3 leading zeros).

The user would have a finite number of transformation options to choose from, so this would make back end coding a little easier.

I am also thinking that the transformations would each their own sub-classes of a Transformation super-class so as to avoid having to do special logic for each one. Each sub-class would just implement a common interface. I have only used the "type" attribute here for example purposes.

Does this approach make sense at all for the API side? Or am I totally off with this?

Any suggestions would be appreciated!

Answer 1

Immediately you will be tempted to create a configuration system for the mapping and transforming, and when you hit complex edge cases it won't be long before you're creating a "programming language" with your configuration system; an inferior programming language because you would be doing that by accident.

Therefore, you should aim for a "code" based solution from the beginning.

I recently learned about Lua, so I'll use that option today in this answer. Lua is generally much faster than Python and Javascript which might be important for batch processing of data. Lua is better than T-SQL at doing conditional logic and loops.

Design:

• A Web App user interface that is Desktop-first
• Each new type of CSV is a new table in the database (don't fall into Database-in-Database anti-pattern)
• Capture "documentation" - let the analyst capture human-readable information about what they are trying to do. This is where JSON might be used - but only at the high-level, and only to keep the human on track and able to communicate with other humans.
• Data transformations are performed on the server-side, using Lua bytecode.
• You should support a per-item API for the Lua functions; and you might leave batch-set kinds of operations to be direct SQL.
• You can start with some very basic Lua functions that can be "configured" (params). The UI might be specialised to wire these up.
• When you have corner cases, the analyst might write new Lua code, but importantly you might get help from a professional developer - because the power is there. Then the Analyst can reuse those Lua scripts in the future.
• Specialised UI screens can then be built on this foundation