By Luke Evans, Amperity summer Intern SDE

This summer, I had the privilege of working on new features to make the query editor’s results grid more robust and powerful. This post recaps the work that I did and some of the lessons I learned along the way.

The Project

My work was in the web app’s “Query” tab. Here, users have the ability to quickly and flexibly define and run queries on their customer databases. Analysts and marketers use a SQL editor or the VSE (visual SQL editor, which lets users create queries in a handy GUI) to write their queries, and the results are displayed on the same page. This gives users the ability to rapidly iterate and explore their data with different queries.

However, this space had some shortcomings. Writing SQL queries can be time-intensive, and the results grid offers very limited capabilities for exploring the contents of the results. Many other SQL editors used by our clients included tools for quickly filtering and copying results, and the Query tab’s inability to do so caused friction in their workflows. For instance, to search for a specific piece of data in their records, users would need to write complex SQL queries filtered by results’ contents.

The Process

To address these concerns, I built a search bar tool in the results grid. This feature allows users to surface any substring matches of a specific term in any fields and records of a query’s results. This feature occupied me for the majority of the summer, and through it I was able to cut my teeth on Amperity’s technical systems.

The process began in the backend service architecture. Results for any query are obtained in the “query-service” microservice which orchestrates queries on a SQL engine and coordinates the results’ storage and retrieval locally or in a cloud storage platform.

I built the searching feature within this existing architecture by filtering the results for substring matches as they were retrieved for storage. In this way, getting “filtered” results worked as an extension of getting any default results. This was the most complicated aspect of the design.

Once query-service was updated to be able to return filtered results, I also updated the intermediate microservices. Since query-service is only accessed through segment-service and web-api, I extended the existing endpoints of these services to accommodate calls and returns of filtered results.

Then, I exposed these new functionalities in the web app by adding a search bar to the UI. To do so, I worked with the UX and PM teams to refine the feature’s interactions with other buttons, add relevant elements like keyword highlighting in the results grid, and iterate through the design to arrive at a search bar that worked in a responsive, intuitive, and maintainable. This process also involved careful consideration of other aspects like PII privileging.

The most challenging part of this design was the work in query-service. As it was implemented, filtered results required continually re-downloading and re-searching results from their storage, which included a significant time cost. This was a tradeoff between performance and design. In the later portion of my summer, I was able to evaluate these trade-offs and decisions by using monitoring and observability tools to pinpoint the bottlenecks and performance profile of the feature.

For instance, one alternative I explored would’ve involved evaluating the search term as a programmatically constructed SQL query in the cloud storage using the provider’s APIs. While this might have won better performance for the feature, it would also require extensive reworking of the APIs and approaches the app uses to interact with these providers.

Once I was confident in the workings of this feature, it was rolled out to internal and then external users with positive feedback. This was definitely a highlight of the summer. At Amperity, I owned the code I was writing, from bug bashing and ensuring its success to receiving the gratification of watching it make a positive impact in the user’s experience of the app.

In the last few weeks of the summer, I had the chance to apply all I’d learned working on the search functionality to start building out functionality for copying large sets of results from the query editor to the clipboard. For performance reasons, the query page displays only 100 results at a time. But the queries may have up to millions of matches, and users wanted a simpler way to export more of this data using the clipboard.

This project was similar to the search bar in many ways, as it involved new ways of accessing results in the existing microservice pipeline. However, this project also had its own hurdles. For instance, accessing the clipboard involves the careful use of the client machine’s permissions and APIs. Even upon serving the web app with a large chunk of results, copying them to the clipboard is far from trivial. For instance, after several days of debugging, I learned that copying data is much faster when separated by line breaks. Who knew!?

This feature also involved performance tradeoffs. Building a feature flexible enough to support queries without a limit in the number of fields that’s also powerful enough to maximize its utility required thinking clearly about the feature’s use cases and design. While I wasn’t able to completely tie up the loose ends of the copying feature, it was rewarding to be able to dig into another challenge.

Reflections

These projects taught me so much. Each day, I was able to tackle new horizons of the features. Shepherding these projects through the whole stack, I got exposure to many different libraries and tools. I picked up Clojure and Clojurescript and saw firsthand how powerful they can be. And I was able to practice professional SDE workflows and learn from how Amperity makes magic happen.

My most important takeaway from the summer is the importance of teamwork. Jumping into a large codebase like Amperity’s isn’t easy, and I attribute my great experience to the fantastic support I was given. My mentor, Will Acton, was a phenomenal help and made me feel like a real part of the team. Graeme and Coco offered great leadership, and always made time for me and my work. The MAX team was a wonderful home, and I was able to learn so much just from watching the MAX teamwork and absorbing their skills. I’m really grateful to have been able to do this work, and thankful to everyone who made it possible!