Need for transformation
A leading automotive retailer's legacy on-premises system
had become a bottleneck, with an unresponsive user interface and a poor user
experience. With this, they recognized the need to modernize its reporting
infrastructure. The existing infrastructure had limited scalability, resulting
in slow response times. There was also a growing inability to quickly create or
adjust reports, making it difficult for executives and business managers to
access timely and accurate insights required for business decisions.
To address these challenges, the company went on a digital
transformation journey. The goals were to modernize its reporting platform and
automate the migration of PostgreSQL objects to Snowflake. This would improve
system performance and allow for efficient scaling to meet the growing
reporting needs across the organization.
The ask
· Modernize the platform: Transform the
outdated on-premises SQL infrastructure to a scalable and responsive
cloud-based solution.
· Automate migration: Use OpenAI to
automate the migration of on-premises PostgreSQL objects to Snowflake SQL.
· Create a reusable framework: Develop a reusable API and UI framework that can scale with the company's growing reporting needs.
Tackling
the task
 |
| Figure 1: Proof of concept diagram |
To address these requirements, the project team conducted a
comprehensive assessment and proposed a two-pronged approach: modernizing the
platform and automating the migration process.
Migration strategy: The data warehouse was migrated
from PostgreSQL stored procedures/functions to Snowflake views. OpenAI played a
crucial role in automating and expediting this process. A Python tool was
developed to take stored procedures/functions, prompts, and table mappings as
input. It generated likely executable code tailored specifically for
On-Premises to Snowflake SQL migration. This allowed the team to efficiently manage the complex migrations.
Dynamic routing and configurable UI: A dynamic routing framework was created for the API, along with a configurable UI framework. This made the solution highly adaptable, ensuring that the platform can scale seamlessly to meet new reporting requirements.
Challenges
Several technical challenges arose during the migration:
· Stored procedure length: Large stored
procedures were difficult to migrate due to the input and output token size
limitations of Azure OpenAI models. To overcome this, the team manually added
delimiters to split the code into manageable segments. This ensured accurate
migration without breaking logic or joins.
· Syntax errors: The initial proof of
concept revealed syntax errors due to differences in SQL dialects between
PostgreSQL and Snowflake. For example, functions like ISNULL in PostgreSQL were
incompatible with Snowflake. The team iteratively refined the OpenAI prompts to
avoid such issues.
· Control flow statements: Converting
control flow statements from procedural code in stored procedures to set-based
views required innovative approaches. Loops were transformed into recursive
CTEs, and conditional statements were converted into WHERE conditions.
· Handling parameters in views: Unlike
stored procedures, views do not accept parameters. This challenge was addressed
by incorporating parameters as columns in the views and applying conditions to
filter the data accordingly.
The solution and outcome
 |
| Figure 2: Results summary |
The final solution led to significant improvements in the
reporting platform:
· Efficiency gains: The Python tool
developed for the migration process enabled faster, error-free, and optimized
code migration. Simple stored procedures that previously took 4 hours to
migrate manually could now be migrated in just 1 hour using the tool. For
complex procedures, the tool reduced migration time from 13 hours to 5 hours.
· Improved user experience: The web
application was modernized, becoming more responsive. This greatly improved the
user experience. Overall response times improved, leading to a more efficient
and user-friendly application.
· Scalable framework: The reusable
frameworks developed for the API and UI ensured that the solution could easily
scale to meet future reporting needs. This reduced the overall effort required
for subsequent projects.
Results
The implementation of this solution brought impactful
benefits:
· Consistency: The centralized framework
ensured consistency across various applications, reducing discrepancies and
improving data accuracy.
· Efficiency: Automated access provisioning
reduced time and manual effort, allowing teams to focus on more strategic
tasks.
· Security: Improved data security through
role-based access control and security classification-based access, minimizing
the risk of data breaches.
Future
outlook
The configurable and reusable nature of the developed tool
and frameworks positions the client for future success. The tool can be adapted
for other engagements, offering scalability and integration potential with
existing tools. This ensures the client is well-prepared to meet the evolving
demands of the automotive industry with a robust and flexible reporting
platform.