About Typeform

Launched in 2012, Typeform drives more than 500 million digital interactions annually and integrates with hundreds of other tools. Most recently, Typeform has embedded AI at the core of their business with Formless – AI forms that can ask and answer questions.

With Looker used across the organization, data is central to most decisions made at Typeform. Data sits in the middle of business-critical operations by providing customer lifetime value predictions, customer recommendations, churn prediction, and lead scoring.

The challenge
The Analytics Engineering team found themselves at a crossroads with growing demands and technical debt, resulting in slow dashboard load times and a complex development workflow for analysts. The team identified two gaps they wanted to address:

• First – reduce Looker dashboard load time from minutes to seconds to create a better end-user experience.
• Second – make it as easy as possible for analysts to build dashboards and manage the part of the data stack they’re responsible for.

The objective was simple: move logic from LookML to dbt and improve dashboard load times while keeping the lights on, which is where Synq came in.

“We recognized the need to improve both the business user and developer experience to unlock the full potential of our data. In addition to reducing dashboard and query runtimes, we focused on improving data reliability. Synq played a crucial role in helping us achieve this.” – Rory O’Connor, Analytics Engineer

Improving dashboard load times by moving LookML logic to dbt

In 2020 and 2021, the business grew like crazy, and the data team had to support new use cases and metrics quickly.

“Our fast growth led to many layers of convoluted logic in Looker. As a result, dashboards took minutes to load, and maintaining LookML was a constant struggle. Certain combinations of dimensions and fact tables would produce nonsense results if the query ever even finished.” - Ilmari Aalto, Analytics Engineer

To tackle this, the Analytics Engineering team put together a plan to refactor Looker:

• Refrain from adding further technical debt and complexity – enforce best practices such as keeping logic in dbt instead of LookML across the data team, complemented by CI tests and PR reviews to ensure new Looker content wouldn’t add additional complexity.

• Deprecate unused LookML code – the Looker project contained a lot of irrelevant content, such as large Explores, which were extensions of extensions, creating barriers to maintaining Looker and onboarding new developers. Looker’s System Activity and Synq’s column-level lineage provided the end-end visibility needed to reduce bloat across Looker and dbt repositories.

• Move complex and inefficient LookML logic to dbt – the focus was on Persistent Derived Tables (PDTs), SQL-based derived tables, and particular Explores with many joins. This migration was crucial to getting acceptable performance, making the project more accessible, and increasing development speed and quality.

The challenge was figuring out the source dbt models feeding specific LookML code and ensuring the team was not shifting the complexity from one tool to another. Synq’s lineage played a key role here. It visualizes dbt models, their status, and how they connect to Looker. And it works blazingly fast even when juggling thousands of data assets simultaneously.

The column-level lineage made it possible to trace where columns originated from and delete those that weren’t used altogether.

“As a new joiner in the team, you can imagine the first impression of facing a project needing heavy refactoring. Synq’s lineage made this effort less daunting - along with speeding up my onboarding process, I felt way more confident in making big changes in the project.” – Pedro Martins, Analytics Engineer

“Synq allows both my team and our stakeholders to easily explore and understand complex data transformations in an accessible manner. The deep integration with dbt tests and Slack alerts has improved our confidence in making significant changes to our data models.” – Rory O’Connor, Analytics Engineer

Getting metrics in place was key for justifying the time spent on the refactoring efforts and tracking progress. Even though the performance issues were widely known, no one had a real sense of the magnitude and, more importantly, where it came from. The System Activity Explores from Looker were used to set up these metrics in a dashboard, where the team could track overall performance over time and drill down to particular Explores, Dashboards, or Query Sources. Checking these metrics also motivated the team, as this type of work risks becoming overwhelming.

The refactoring project took around a year. Along with deleting 20,000 lines of LookML code, the team improved the average query runtime by 60%, with even better improvements for the main Explores. This was accomplished with minimal friction and downtime to avoid negatively impacting user engagement.

Speedy workflows for data analysts

With over 600 data models and hundreds of dashboards, no person can keep track of everything in their head. This is especially painful for the Data Analytics team, the primary internal customer of the Analytics Engineering team. Following the improvements to Looker, next up was ensuring that the data analysts had the tools to be as productive as possible to get back to what matters most – providing insights to their stakeholders.

Typeform runs dbt Core, but dbt Docs didn’t work well for their scale and are limited to dbt only. The team experimented with an open-source data catalog, which required too much maintenance.

“Our data analysts now use Synq as their go-to data catalog. Synq shows the docs, together with column-level lineage and model status. The lineage from dbt to Looker has been a game-changer for us. Data analysts and scientists are our main stakeholders, so we’re very pleased with how their productivity has improved.” - Ilmari Aalto, Analytics Engineer

Having metadata extracted directly from .yml definitions in dbt and LookML in Looker helps keep documentation and development workflows closely tied.

“As a member of the Data Analytics team, Synq has been an invaluable tool for doing root cause analysis myself. The column-level lineage functionality makes it much easier to understand the exact impact when I change an upstream model.” - Héber Brandão, Data Analyst

Next for the Analytics Engineering team is data robustness and ownership. The data team recently moved under technology and, with closer ties, plans to extend the ownership of data quality to backend data producers. Teams can be tagged in Synq alerts based on source ownership definitions in dbt and sent directly to their Slack channels. The recent adoption of Synq’s new incident management functionality helps spread awareness of ongoing issues quickly and brings visibility to the broader tech organization.