How Dealer Trade Network delivered 60 X faster analytics

About Dealer Trade Network (DTN)

Dealer Trade Network (DTN) has the largest new car dealer-to-dealer trading network in the United States supporting over 4000 dealerships. Proprietary analytics built on tracking current/ historical vehicle inventory and market trends are core to the functioning of this trading network. Additionally, DTN is developing digital products that allow dealers to integrate supply and demand analytics into their business. 

The Data Challenge

To deliver data products, DTN has created a data platform built on Cloud-based data stores consisting of an  Aurora MySQL based database  and an Amazon Redshift based data warehouse. Data from internal systems and third party sources are ingested into Redshift for analysis with a 30-day data retention period. Data visualization and end user access are provided through PowerBI and Retool based client apps.  

As data volumes and access requirements have grown, analyzing data beyond the 30-day window and comparing snapshots of inventory over multiple time windows have become a challenge.  These snapshot comparisons are critical to proactively identifying potential trades within the DTN network. Hence, the effectiveness of digital products depends on the ability to provide timely trend reports covering a 12-month data retention period. To address the need for fast response times while analyzing large volumes of data, Firebolt was evaluated at the recommendation of consulting partner, Untitled Firm.

The Firebolt Evaluation

DTN provides dealerships with a nationwide vehicle sale trends report at no cost.  With the addition of digital products specific to dealerships, it became imperative to provide granular reporting specific to each dealership. Performance challenges inhibited DTN from creating this report in a timely manner during a narrow daily time window. At the recommendation of Firebolt consulting partner Aaron Peabody, CEO of Untitled Firm, Daniel Delgado, Chief Technology Officer at DTN, then engaged Firebolt to explore technology fit. He challenged Firebolt by providing workloads that were viewed as critical bottlenecks in the previous environment.   

The Firebolt team worked closely with Untitled and DTN’s technology teams to move data from existing databases to a Firebolt database on AWS. As a part of this effort, the team moved existing queries to the new instance. This onboarding process was acknowledged by Mr. Delgado, “Onboarding by the Firebolt team was a great experience. I was really impressed by the speed of the process. Also, our team is very familiar with SQL, which made the transition smooth.”

The performance evaluation showed that Firebolt’s indexing capabilities resulted in 60X faster query times compared to the previous analytics platform. With the performance evaluation behind them, DTN explored various scenarios to test out data ingestion and environment management. “Firebolt’s documentation is phenomenal and it helped us with various scenarios leveraging REST APIs for management and data ingestion. We also tested interfacing with ReTool low code/no code tool for application access. "All this was done with minimal compute resource in the form of a General Purpose Firebolt engine” according to Mr. Delgado.

Enhancing reporting through Aggregations

Prior to Firebolt, generating on-demand, dealer-specific reports required running aggregations on a nightly basis and took over an hour on the previous system. DTN leveraged Firebolt’s aggregating indexes which reduced the time to create reports down to 2 minutes, delivering a 30X improvement, further validating Firebolt capabilities towards delivering data products. 

Transition to Production and  architecture

Figure below shows DTN’s hybrid architecture that combines data from on-premises MySQL database and third party sources into Cloud data stores with Amazon Redshift functioning as the primary analytics platform. Currently, data from an Amazon Aurora read replica is loaded into Firebolt through an S3 bucket. Using SQL and REST APIs, DTN creates reporting aggregates on Firebolt. This aggregated data is used to serve end user reporting needs.

With the initial validation behind, DTN moved forward to address large-scale ingest of data for 20+ million vehicles with over a billion records. Firebolt’s ability to scale to a large engine and switch back to a lower cost General Purpose engine for typical workloads  helped address the demands during ingestion in a cost effective manner.

As additional workloads come online, DTN plans to use Firebolt’s decoupled compute and storage capability to offload/isolate new workloads to separate engines without the need to replicate storage. 

Business Impact

Leveraging Firebolt has opened new revenue streams for the Dealer Trade Network with the ability to provide dealer-specific reports on a daily basis. Internally, DTN’s ability to analyze historical data effectively has helped sales teams identify new sales leads.

On the marketing front, DTN’s email marketing team  leveraged additional analytics generated from historical snapshot comparison on Firebolt, helping drive 2X revenue than previous campaigns. DTN has also launched a new Market Index to help dealerships evaluate wholesale trading prices against invoice prices.  

The Future

As the business expands, DTN plans to make data available directly to dealers through an interactive portal with Firebolt serving as the consolidated data analytics platform. DTN also plans to modernize the current visualization stack and add automation/orchestration to streamline ingestion.

Read other case studies

Get started with Firebolt

Start your Firebolt trial with $200 credits