AWS Redshift Query Profiler

Situation

I was working as a Design Technologist on the AWS Redshift Console engineering team. The team consisted of approximately 10 engineers and myself.

Task

I was tasked with prototyping the Query Profiler, a new feature for Redshift that would greatly enhance the observability and troubleshooting capabilities for Redshift customers. It would allow a customer to visually understand the makeup of how a query plan was executed. It would also provide a means to compare, sort and identify streams and steps that ran the longest, helping to identify performance optimization opportunities.

Action

I had to overcome great technical challenges, including learning SQL, testing a series of different tree mapping engines and prototyping several versions of the product to identify a path forward. For example, I investigated the possibility of building the entire tree with D3, but this would have required extensive library creation as it was not as compatible in a react environment. I ended up choosing ReactFlow as it supported a great many base features out-of-the-box. This allowed us to de-risk spending engineering cycles building with the wrong tech stack.

As I progressed in building the prototype, we quickly realized that there was a significant gap in the capabilities of the Redshift query processing functionality. The API was not going to deliver what we needed to produce the front-end with all of the features we wanted. So, I investigated options to work around the problem. I worked with Database engineers to define a logical ruleset that could be applied to a given output. My solution would parse the raw output from the RS system tables, apply the ruleset and transform the output into a human readable, interactive tree map.

At this point I was quickly becoming the source of knowledge on what could and couldn't be built. So, I complied my findings into what became the official technical design document. This included all known functionality, requirements, risks, technical implementation strategy, business and functional logic. I presented the plan to build the production solution to senior leadership and they agreed to proceed with my proposed plan. I lead weekly syncs with front-end and backend engineers, product managers, technical writers, UX designers, Database engineers and Solutions architects. I worked with specialists across several different teams to identify solutions and continued to iterate them into my prototype to quickly make decisions on their validity. I wrote typescript, SQL, React, SCSS. Initially I architected the project with speed of delivery in mind, but as the project progressed, I reinforced the codebase by fully typing and documenting all functionality via JSDoc style comments. This ensured that the codebase was well structured and easy for future engineers to maintain.

Result

I started with just builidng the prototype, but ultimately I ended up building the entire product and delivering it to production. As I led the project, I identified the gaps and worked across teams to build the missing functionality into the front-end solution. The end product was a stunning UX achievement and was very well received by AWS customers. It was in many ways considered to be the most advanced among the profilers across AWS services, allowing for deep understanding to quickly identify problems with customer generated queries. This project earned me the trust of leadership and I was promoted to a senior position shortly after the launch of the product.

Lessons Learned

I learned that even when faced with very challenging and ambiguous requirements, I was up to the challenge. I showed strong leadership skills in uniting many colleagues across a wide array of roles to align on what was needed. I relentlessly searched for the answers to any questions that were surfaced.