Simple solution, huge savings

1. The origin of the issue

In any rapidly growing company, data accumulates quickly. Industries that don't necessarily need a dedicated development team (like manufacturing or distribution) often use off-the-shelf databases. As a result, the performance of their dashboards deteriorates, and they find themselves unable to perform cohort or statistical analysis. However, this is not necessarily a bad sign; it means the business has scaled to a point where data management becomes a critical issue.

The client who approached Da Vinci Technologies was no exception. They had accrued over 2.2 million purchase records, which their data analysts were downloading in chunks to Excel for analysis. After hearing the client's complaints of sluggish performance and difficulties in finding errors in their data, we took a closer look at their database. They were storing all purchase data in a single large table, resulting in query times exceeding 70 seconds for simple search tasks.

By tuning simple queries, we were able to reduce query times by over 10 seconds. (After the entire project, the same query time went down to 0.175s.)

After witnessing tangible improvements, the client hired Da Vinci Technologies.

2. Database Normilisation

What Da Vinci Technologies did can be summarized in two words: "Database Normalisation."

We launched a separate database instance for analytics and created about ten tables tailored for analytic purposes. Remember this when creating a well-normalized database for analytics - "You shouldn't need to use 'join' statements when analyzing."

Following the database redesign, we performed an ETL (Extract, Transform, Load) process to move relevant data from the old database.

Cohort analysis, which was crucial for the client, is a complex task that requires thoughtful database design. The output for cohort analysis grows horizontally (M1, M2, M3,...), but databases should be designed to grow vertically.

If you are a team lacking in development resources and want a blazingly fast database for analytics, you'll need to: (1) Write SQL scripts (2) Launch an analytics server (3) Create cronjobs (4) Integrate with dashboard tools like Tableau.

The expertise required is so broad that it's impossible for a single backend developer, frontend developer, data engineer, or database architect to handle it alone.

Most clients we meet at Da Vinci Technologies complain about difficulties in data analysis, querying, or modification. While they may refer to this as Admin, Backoffice, CMS, or ERP, the essence is the same: poor database management.

Conclusion

Are you finding it difficult to navigate your data? That's not necessarily a bad thing. Having abundant data and a reason to look at it usually indicates business growth.

No technological issue is insurmountable in business. With the right team, even the most challenging problems can eventually be solved.

And sometimes, a cost-free diagnosis alone can offer easy and quick solutions to improve your database management.

If you feel your database management isn't keeping pace with your business growth, feel free to contact us.

Until next time,

Dongeun