Case Study – Diff Viewer for Database Versioning

Amperity is the leading customer data platform (CDP) provider that helps companies put data to work to improve marketing performance, build long-term customer loyalty and drive revenue. Amperity is used by many of the world’s most beloved brands, such as Alaska Airlines, Endeavour Drinks, Kendra Scott, Lucky Brand, Planet Fitness, Seattle Sounders FC, Under Armour and Wyndham Hotels & Resorts

Workflow

A typical workflow is as followed:

  • Customer determines what data is to analyzed working with data analysts, marketing, and stakeholders
  • Customer data is accessed through data warehouses/DB like Snowflake, Mongo DB, SQL using custom built connections
  • Attributes are then assigned to the data that has been imported
  • Machine language AI is used to perform identity resolution analysis and delivers a 360 customer profile
  • The result of the analysis is used for targeted campaigns and fully accessible by the customers

Pain Points

Once the customer identity resolution has been analyzed and processed, the Customer 360 databases are created and accessible to Amperity, the client, or a system integrator. This database contain vast amount of information that need to be filter or query against to obtain the necessary information and insights for the data analytics and/or marketing team.  SQL is mainly used to query the Customer 360 databases and is accessed by many users. The problem in this scenario is that there is no way to track who is creating these custom queries and there is no versioning control.  As a result, many queries are created and there is much confusion when the client requests data for a particular customer profile. This causes frustration and inefficiencies providing the data to the client resulting in higher cost to Amperity and the client.

 

Solution

The solution to the problem was to used an existing technology that has great success in the past with some modifications. Developers are constantly dealing with versioning and need a tool to compare code branches to see what changed. The GitHub diff viewer is one of the most popular tools to accomplish this issue!  It allows to view the history of the changes made, ability to revert back to a particular version, view the code side-by-side in a dual pane or single pane.  We adapted this tool to be used for SQL queries to allow the operators to view changes made, compare two versions of a SQL query line by line, and the ability to revert back to changes.

The design, development cycle, and testing were done in a matter of a couple of 2-week sprint with high success.  Here are a couple of screenshots to illustrate the single and split pane view:

As noted in the video, the diff viewer is launched in the Customer 360 Tables in a submenu making it accessible to any tables in the database. Also, the following features are available:

  • Real-time editing can be made either in the single or split-view
  • Line-by-line comparison is easily visualize with background differences
  • The quantity of changes is noted by the +/- next to the author
  • The ability to revert changes can be reversed by selecting the previous version

Summary

In collaboration with development and product management, we this design was implemented and tested quickly.  It is currently used by all the operators, system integrators, and DB developers.  It has also increased efficiency by 20% based on turnaround time in providing "relevant" data back to the customer resulting in better insight in the customer profile.