Reposting question as Previous post on same Topic was not clear.

Currently our financial application receives multiple feeds in csv format from client, there are usually 100k to 5M rows of data. When this information is processed we need to enrich the data and store them, various look ups are performed and based on data we need to categorize or assign more values.

An example mapping looks like below,

Firm     Category   Sub-Category   Code   Acct   = InternalCode( Enriched )
A         a1        sc1            c1     acc1   =   ACCT1
B                                         acc2   =   ACCT2
B         b1                                     =   ACCT3
B         b1        sc3            c3     acc4   =   ACCT4

Here (from data feed), if firm =B and category =b1, then InternalCode is ACCT3, if firm=B and Acct =acc2, then InternalCode is ACCT2.

Currently all of these are hard-coded in various programs with duplication and hard to maintain/technical debt. We want remove hard coding to configurable way.

There are many such lookups happening and each lookup has hundreds of such business rules.

What is the best way to remove hard coding ? I am thinking about using rules engine but not sure which criteria to apply to pick one for such use case. Also, are there any other methods to move this kind of logic out of code ?


  • I took the freedom to make a slight change to your question to remove the red herring of a "3rd party resource request", for which you already got a close vote by an anonymous voter (someone who is obviously more interested in close voting for picky reasons than answering your question, which is IMHO a good one).
    – Doc Brown
    Sep 22, 2018 at 7:15
  • 3
    For future reference: When a question gets closed/put on-hold, you have the option to edit the question to improve it. If the edit is good enough to make the question answerable, it will get reopened. That mechanism is generally preferred by the community over asking a new question. Sep 22, 2018 at 8:45

4 Answers 4


I think it is important to realize your goals first - it is typically not just "better maintainability". A common motivation behind removing hard-coded rules from code to configuration is often to shift responsibility for maintaining the rules from the developer team back to the business teams (and I bet this is your case here as well).

That means, configuring and maintaining the rules in some configuration files should be less complex than implementing them in code, and it should be doable by some people from the business team. Otherwise a configurable solution won't bring you any benefit.

Unfortunately, there is seldom a "one size fits all" solution to this. You have probably lots of different use cases, each one requiring a different level of complexity. I would recommend to try out different approaches, start with a simple one and look how well it works. Here are some ideas

  1. For some cases, using a decision table like the one shown in your example may be fully sufficient as a configuration. Put the table into a spreadsheet or a database (pick what your business team prefers), and write a program which evaluates the table and processes the data according to those rules.

  2. For some cases, it may be sensible to implement your own Domain Specific Language. This can be very simple, or it can be as complex like other full-scale programming languages. (For example, I did this a few times using a tabular form, with a column for preconditions, one for actions, and one for parameters, which is a very simple approach, easy to implement but still powerful enough for lots of cases.) Then one can implement an interpreter for the DSL. Note the DSL needs to stay simple enough your business people (or at least some "power users" amongst them) can handle it.

    To give you another example: I once had some users who were no devs, but had some experience with SQL. They required a validation tool for a database with >100 validation rules, and they wanted to manage those by themselves. The data model was quite cryptic, but the business team had a good understanding of its semantics, better that every one else in our dev team. We made them an Excel sheet where they could store the relevant parts for a SELECT statement by themselves, and defined something like a "DSL" for processing the result sets in various ways. The program we gave them then read the SQLs, run them against the DB and interpreted the results according to the DSL. The solution is still in production, over years, with zero maintenance requirements for the devs.

  3. Using a rule engine. This is some form of a "predefined, general-purpose DSL" for maintaining rules. It may be a good solution for your case, but it also bears the risk that you end up needing specialists for writing and maintaining the rules, ending with a solution which can be harder to maintain and debug than your current system (just by different people). I recommend you go through all the answers to this older SO question "When should you NOT use a Rules Engine?", and check which of the pro and con arguments apply to your specific situation.


The wrong way

It would be tempting to tell you to put each mapping in a database table and query by providing all the relevant fields. This would however miss the fact that rules may be based on partial matching, and that when several matching rules are found, the more specific should apply:


Input data:                                  Output Explanations on rules

Firm     Category   Sub-Category   Code
B        b1         sc2            c3        ACCT3  (R2,R3 match but R3 is the most specific)
B        b1         sc3            c3        ACCT4  (R2,R3,R4 matches but R4 is the most specific) 
B        b2         sc3            c3        ACCT2  (only R2 matches, for B)

Solution 1: sophisticated matching algorithm for each mapping

Of course you could elaborate on your mapping table by adding a rule precedence value and generate a more complex query that would find all candidates and take the one with the highest precedence.

This is error prone (comlex query generation, risk of unexpeted rules being selected, need for defining precedence manueally). In addition, it might lead to a lot of expensive queries.

Solution 2: matching algorithm using successive mappings

Another approach could be to determine a set of mapping table, and let your rule engine iterate through successive conditions, stoping whenever a matching rule is found:

Mapping 1:  
Firm     Category   Sub-Category   Code   Acct   = InternalCode( Enriched )
A         a1        sc1            c1     acc1   =   ACCT1
B         b1        sc3            c3     acc4   =   ACCT4

Mapping 2:  
Firm     Category                                = InternalCode( Enriched )
B         b1                                     =   ACCT3

Mapping 3
Firm                                             = InternalCode( Enriched )
B                                         acc2   =   ACCT2

This kind of approach is used in high performance pricing engines (and even resulted in patent litigations that concluded that it was a well known approach: disclaimer: I'm not lawyer, and that's my personal understanding and not legal advice).

This approach can be used either in a record by record way (going through each set of mapping for each record). But it can also be used in a more efficient way if you've uploaded your CSV in a database, using some successive update statements that don't update account values already filled by previous steps.

Other solutions

Another approach could be to use some more sophisticated rule engine, and translate all your if-then-else into business rules. The advantage is that you don't have to determine the mapping tables as you have done. You don't have to think about mapping dependencies (e.g. if on ogf the mapping field is in fact determiend by a previous mapping). It's also easy to add new rules.

The inconvenience is that the rule engine is invoked for each CSV record. So it might be more heavy than the table based approach (see solution 2). Also, it's difficult for the rule writer to understand the interaction between different kind of rules.

If you don't want to use an existing rule based engine and develop your own, you could be interested in this SE question.


You can make the mappings centralised by keeping them in database and exposing get/update/delete/edit functionality of a single/some/all mappings via RESTful APIs. Mapping could be fetched before a processing of each batch.

  • The idea looks appealing but misses the problem: if the mappings are all centralized, they may nonetheless be defined in a generic manner by not specifying some of the fields. For example how would you determine the ACCT for a CSV record (B, b1,sc2,c3) for which several rules would match (taking into account the null fields in the rule, which mean any value).
    – Christophe
    Sep 23, 2018 at 18:24

I think the simple and fast solution is to convert the conditions and corresponding look ups in Redis database like this :

enter image description here

In this scenario you should consider all possible conditions for once and set theme in Redis and for the lookup you can use Redis clients directly or write a web service or etc.

The advantage of this method is time complexity for Set and Get operations in Redis which is O(1) and you can retrieve values corresponding to keys very fast.

  • The idea looks good but misses the problem: the records may have all the fields all the time but the rules do not require all the fields. So the keys generated for records will not necessary find the right rule.
    – Christophe
    Sep 23, 2018 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.