Data Transformation

Using GPT-4 for Data Transformation Without the Pitfalls

Written by 
Kirat Pandya
March 23, 2023

Data transformation is a crucial step in making sense of raw data, and it's a task that businesses and data professionals encounter daily. With the advent of GPT-4, it's tempting to imagine the possibilities of leveraging this powerful language model to perform tasks such as data cleaning and formatting. But is GPT-4 truly ready for these challenges?

The idea of simply asking GPT-4 to "Clean my CSV, remove the repeated headers, fix the addresses and phone numbers" seems almost magical. In some cases, it can indeed perform well on small test data. However, there are several factors that make this approach less viable than we might hope:

  1. Explainability: Language models like GPT-4 can generate impressive results, but understanding how they arrive at those results is often a mystery. As we're still figuring out how to explain what a model understood from a given prompt and the steps it took to arrive at a particular output, relying on GPT-4 for data transformation can be risky.
  2. Correctness: While GPT-4 can accurately process and transform data in many cases, it might not be consistent across all rows of a large dataset. This is particularly problematic when dealing with business-critical data, where even a single error can have significant consequences. For instance, GPT-4 may perform the correct calculations for the first 10,000 rows of a CSV file, but then make a mistake on row 10,001. Ensuring that every row is correctly processed becomes a challenge when using GPT-4 for data transformation tasks.
  3. Non-Determinism: Non-determinism is a property of Large Language Models (LLMs) like GPT-4, where the model might generate different outputs for the same input, depending on factors like model configuration (random seed value) and prompt variations. In the context of data transformation, non-determinism can cause unpredictable and inconsistent results when GPT-4 is applied to the same data multiple times. This inconsistency poses a risk when using GPT-4 in automated processes, as it may produce the correct output for the first ten iterations (of a pipeline for example), but on the eleventh iteration, it might generate an entirely different and potentially incorrect output. Thus, non-determinism adds an element of uncertainty that makes GPT-4 less suitable for direct use on mission-critical data transformation tasks.
  4. Size Limits: GPT-4 can only handle a limited number of tokens (~32,000) in a single request. For a CSV file with thousands of rows, this would require multiple requests, which is considerably slower than traditional data transformation methods like Excel or Python scripts.
  5. Cost: Using GPT-4 for data transformation can be expensive. For example, processing 100,000 rows with 25 cells and 5 tokens each would cost around $2250 (at current OpenAI rates). While smaller, cheaper models could be used, they often exacerbate the issues of correctness and size limits.
Using GPT-4 for data transformation can be expensive.

While GPT-4 offers some fascinating possibilities for data transformation, it currently faces challenges related to explainability, correctness, size limits, and cost. To harness the power of GPT-4 while addressing these issues, we need a solution that leverages GPT-4 in conjunction with a human readable and understandable intermediate step. This approach would provide the benefits of GPT-4's advanced capabilities while ensuring the transformed data's accuracy, scalability, and cost-effectiveness. As these models continue to develop and improve, the combination of GPT-4 and a well-designed intermediate step could revolutionize data transformation tasks and make them more accessible to a wider audience.

Continuing from the conclusion of the problem statement, let's explore a potential solution to address the challenges of using GPT-4 for data transformation:

One possible solution is to ask GPT-4 to, instead of transforming the data directly, output a Python script (or similar program) that can perform the required data transformations. This approach has several advantages over direct transformations using GPT-4:

  • Readable & understandable
  • Verifiable, repeatable, and testable
  • Cost-effective and performant

However, one major disadvantage is that you need to know how to read and correct Python code. This is a fundamentally limiting challenge, as the number of people who know how to code is a very small minority1.

At Osmos, we believe we have a better solution - Excel-like formulas. What if, instead of asking GPT-4 for Python, we instead asked it to give us an equivalent Excel formula? The number of people who use Excel is vastly greater than the number of people who know Python2.

Data transformations using GPT-4
Transform data with GPT-4

Just like Python, this solution is not only readable and understandable but can also be versioned. This addresses challenges like data traceability, lineage, and governance, which are especially important when dealing with data from customers, vendors, and partners.

But what if, somewhere in this magical chain of formulas and Excel, something still gets missed? Osmos has a solution for that as well - powerful validations. With customizable validation rules in place, you can help detect input issues and throw errors with actionable steps for the end-user. This provides a strong line of defense to catch any data errors.

GPT in Osmos
GPT-4 in action in Osmos

Imagine the time savings if GPT-4 could write Excel formulas instead of people having to write and debug them manually all the while ensuring that any mistakes (human or AI) never managed to sneak bad data past your validation rules and quality checks.

If your organization spends any amount of time ingesting data from customers, vendors and partners, Osmos can help you improve your customer satisfaction by speeding up data ingestion end-to-end, with the power of LLMs + formulas.

Osmos empowers implementation and operations teams to independently accelerate data ingestion processes while improving data quality. With AI-powered data transformations, Osmos makes data ingestion easy, fast, and error-free, so frontline teams can rapidly activate customers and partners by automating the cleanup of messy data. We’re creating a world where anyone can work with data regardless of technical proficiency.

Discover the power of GPT-4 data transformation

Talk to someone on our team!


1 Depending on who you ask, there are roughly 18.5 million developers and 7.2 billion people on the planet, which gives 0.26%: IDC Study: How Many Software Developers Are Out There? (

2 The Irish Times noted in 2017 that Microsoft CEO Satya Nadella was calling Excel Microsoft’s most important consumer product, pointing out that it had over 750 million users: Microsoft Excel Becomes a Proper Programming Language - The New Stack

Should You Build or Buy a Data Importer?

But before you jump headfirst into building your own solution make sure you consider these eleven often overlooked and underestimated variables.

view the GUIDE

Kirat Pandya

CEO & Co-founder