At Scientific Financial Systems, we have mapped a lot of vendor data into Quotient – mostly from MS SQL Server databases. With the ever-growing popularity of Snowflake, vendors are releasing versions of their data products on this highly scalable, performant, and easy-to-share database. While Quotient’s architecture allows it to connect disparate data across database engines, it still requires our team of financial data scientists to write scripts with the proper flavor of SQL. So, with thousands of SQL scripts, we wondered… Can Open AI’s ChatGPT help us to translate from Microsoft’s TSQL to ANSI SQL optimized for Snowflake?
To test the viability of this idea, we ran several complex TSQL queries through ChatGPT, asking it to convert them to Snowflake SQL. The TSQL itself was fairly challenging to understand by non-DBAs, mapping as many as 14 tables, using temporary tables, multi-statement queries, multiple common table expressions, nested queries, and/or functions not available in ANSI SQL. We refined the questions to ChatGPT several times, attempting to achieve the best possible results.
Our primary goal for these ChatGPT tests was to determine how much the AI could assist our DBAs by consistently creating translated SQL which could be executed with minimal errors, was optimized, and (most importantly) returned accurate results.
Results of our tests
As a tool for the DBAs, ChatGPT was helpful in performing specific function conversions or for answering common questions around optimization.
Here are some examples of what ChatGPT gave for function differences:
-
-
- CHARINDEX() –> STRPOS()
- COLLATIONPROPERTY() –> COLLATION_NAME()
- COL_LENGTH() –> COLUMN_SIZE()
- CONCAT_WS() –> CONCAT()
- CONVERT() –> TO_VARCHAR()
- DATALENGTH() –> LENGTH()
- DATEADD() –> DATEADD() (same syntax, but different behavior for some datepart values)
- DATENAME() –> DATE_PART()
- DECODE() –> DECODE() (same syntax, but different behavior for null values)
-
ChatGPT was also very good when asked for more details on any specific function.
However, as a turnkey translation tool, ChatGPT struggled.
Inconsistent results: ChatGPT’s accuracy is dependent on the training data used by the model. As models are continuously reweighted, translations for the same result can vary. Note that we were not using “Regenerate Response” but repeating the command on different sessions.
Examples:
1. On two separate runs it translated a date subtraction as:
-
-
- “my_date – INTERVAL ‘2 DAY'”
- “my_date::DATE – 2”.
-
2. On two separate runs it converted to timestamp and date:
-
-
- DATE_PART(‘dayofweek’, my_date::timestamp_ntz)
- DATE_PART(‘dayofweek’, my_date::DATE)
-
3. Inconsistency in casting a field to date by ::Date. Possibly because ChatGPT doesn’t have context of which field should be a date. Extra work would be needed to pass that context in the question.
4. Comments were sometimes dropped.
Query size/complexity limitation: ChatGPT was consistently better at converting smaller and less complex queries. With larger, more complex queries it sometimes stops generating responses after a few lines. This might be a limitation of the free version used in our testing and might improve if upgraded to Open AI’s Plus version.
Security concerns on context creation: As mentioned above, one way to decrease the inconsistency of generated results is to pass in more context about the business logic/terms to train the model better. This left us with a couple of questions for future analysis.
How much context can be shared, and will there be a possible leak of that data to other ChatGPT users?
Will the context remain after terminating the session or would one need to re-enter the context every time? If need to re-enter, will the model understand the context in the same exact way as the previous session?
Conclusions
Overall, we were quite impressed with ChatGPT’s efforts and saw a lot of promise for this technology. As a bonus to generating translated SQL for simple queries, the AI provided comprehensive summaries of the changes it made.
Unfortunately, we also determined that this AI was not ready to replace our DBAs anytime soon. It consistently required more effort by our DBAs to review the ChatGPT converted SQL than to perform the translation manually. It was equally time-consuming if the translation requests were broken into smaller scripts and then reassembled by the DBAs.
We will continue to test future versions of ChatGPT and other promising AIs being released. Over time, we expect that our concerns will be addressed, and our DBAs will be freed up to handle more strategic and challenging tasks.