Figuring out an obscure database

Challenge

In many data driven products one must look into arcane operational database to access the necessary training data or operational database must be turned to analytical database. Implementation details vary from project to project but practically always one is confronted with an arcane operational database with obsolete documentation at best and usually none at all.

At first this might seem like a daunting task. How is it possible to decrypt meaning of tens and tens of tables? But fear you not, here is how the trick is done.

Learning cycle

Start with asking why database exists because use case of application using the database is bound to show up in the database structure somehow. The best source for this information is whoever is working daily with the database.

Draw physical Entity-Relationship (ER) diagram of the database using database DDL scripts. If you have access to database you are almost guaranteed to be able to do this. Sometimes you can only access database dump without valid constraints but even then you can make educated guesses using table names, field names, data types and table structure.

Most operational databases use common database design patterns (star schema, bridge table etc.) so learn how these work and mapping business concepts to database tables becomes much easier. Physical ER diagram will help tremendously on this part. Remember though that even if database was born with solid structure by the time you come around there can be any number of creative and not so solid features so flexible mind is mandatory.

The preceding steps offer high level understanding of the database but very little to understanding on how to compute anything from database.

In almost all cases application has some kind of UI, e.g. distribution of ice cream in ice cream manufacturing company has some business user UI. Based on the database design patterns, ER diagrams and a little bit of intuition gained from discussions with business users you can guess how to map primary key of some center table of the database to UI.  When you compare what you see in database to  what the application wants so show to the user you can create first valuable hypothesis on how database tables should be interpret.

Now you have gained a little bit of understanding on the data so use your new found knowledge to ask more relevant questions from business users, understand ER diagram better an so forth.  Essentially you start with very vague understanding of the database and iteratively refine your understanding until you can compute the necessary values using the database.

Tricks of the trade

• Use mainly production data since TEST (and QA too..) can be just about anything. Without any access to PROD data probability of success is greatly diminished.
• Change TEST data to confirm hypotheses, e.g. to change database state by making a new ice cream delivery order to check if database looks what it should after the new order.
• Sometimes you want access end results of the application and compare them to database content. Quite often companies store the most relevant parts of the database information also in a different format, e.g. ice cream manufacturing company might store delivery records in pdf files or in paper records for accounting purposes since they must be able to present that data to regulatory agencies on request. 
• Beware that TEST data and PROD data can have funny differences, e.g. IDs used to map ice creams to ice cream descriptions can be different in TEST and in PROD.
• If you can access the application source code you should take a look. Be warned though, it can take a long time to understand relevant parts of complex legacy application but sometimes you have no choice.
• Quite often some logic is coded in first character of a string or there is some other black magic involved. This creates sort of hidden logic in database that is not available in ER diagrams but can be spotted by inspecting the data.
• Don't assume that database content should be interpret in same way on different time spans. If company's business has changed then the application has changed too and seemingly similar products may not behave the same. Similar wholesale ice cream package may not look the same each year in the database.
• You are solving a puzzle so combine intuition with logic.

Value creation in data science

Creating value in data science is hard work and requires down to earth attitude. One must understand business model thoroughly and provide actionable insights or better yet provide end-to-end working system that captures value and serves it to the end customer. Note that end customer may be e.g. consumer, an employee or a company.

What is real value?

A genuinely good thing. Not great because you say it is great, not credible because of your reputation nor supported because it advances somebody's personal agenda but because it helps corporate customer to solve a problem than prevents business growth (in existing or new business) or allows consumer to enjoy life more. A real value makes measurable difference and improves lives. 

Kinds of insights

A good insight is profitable and actionable. Let's break this down to components and examine them separately.

Profitable insight is a plan or a non-trivial fact that provides value to customer if acted upon. You must also estimate size of the opportunity to decide if the opportunity of worth pursuing.

Actionable insight is doable in practice. That is, you can reach the end customer some way and provide to the end customer the value created by the insight.

Insight "People who buy carrots also buy jeans" is profitable because in principal it allows you to bundle these products non-trivially together but actionable only if there is a way to serve this bundle to end customer, e.g. through shelf placement in retail store or by mobile application.

Ways to create value out of an insight

An insight can be valuable either directly or indirectly. Advising on trends or segmenting the market tend to fall into indirect category whereas recommendation system or predictive maintenance are direct ways to create value out of an insight.

When creating value directly one implements a system that serves value directly to the end customer, e.g. by providing personalized, meaningful and non-obvious leisure time activity recommendations through Facebook.

When creating value indirectly one must provide advice to somebody else who has means to make end customer's life better, e.g.. providing advice to a car dealer on car accessories that make end customer's driving safer and more enjoyable. 

Direct way to provide value has benefit that it can usually be measured and iterated in rapid manner whereas indirect way to provide value tends to have much longer feedback loop.

How to discover profitable and actionable insights?

Start from the basics. Plot this, double check that, check for data consistency, talk to all stakeholders and make sure you got the basics right. You cannot hope to gain valuable insights if you don't understand the business both quantitatively and qualitatively. 

Max out business intelligence tools and existing infrastructure.  Many insights can be found by using business intelligence tools and business intelligence tools can also be used to double check and clarify insights found using more advanced tools like Spark.

Be tools agnostic. If the job requires R then use R and if job requires Spark then use Spark. As a rule it is easier to learn to use multiple tools compared to trying to squeeze the job to the form that your favourite tool chain supports. This is especially important if you change topics in a rapid manner and build on top of existing libraries and packages. Remember that the best tool varies case by case basis. 

Be practical and never forget that your job is to provide value. If problem can be solved using whiteboard and spreadsheet all the better because you just saved everybody's time and money. Then again, if advanced tools are needed then they must be used and it is up to you to overcome any technical hinderance that you might run into.