By creating this new table, you have pivoted the original table by turning values from the “activity” column into columns of their own, under which you have summarized the data by counting instances.

How data transformation relates to data integration

Data can be transformed at two stages of the data integration process. It can take place before or after the data is loaded to its destination, typically a data warehouse.

In traditional ETL, data is transformed into analysis-ready data models before it is loaded to the destination. Combining transformation with loading into the same step can preserve storage and compute resources, but introduces a great deal of brittleness into the data engineering workflow. This also means that the software used for transformations is typically written using scripting languages such as Python and Java. In addition, transformations in ETL may require a great deal of complex orchestration using tools such as Airflow.

Source: ProgrammerSought

As a result, transformations under ETL tend to be the purview of expert users, specifically engineers and data scientists.

In the modern ELT workflow, raw data is transformed into analysis-ready data models after data is loaded. Data in a warehouse environment can be transformed using SQL. This makes transformations accessible to analysts and other SQL-literate members of your organization, rather than only engineers, data scientists, and other people with serious coding chops.

For instance, suppose you’re a retailer with both physical and virtual storefronts. Your systems generate a lot of data, from inventory, point of sale, marketing, shipping, taxes, and more. You can replicate data from all of the systems that create it, then update it to a data warehouse for analysis — that’s the extract and load part.

How might you make use of the data?

• You might want to create a dashboard showing timely key results and historical trends over time.
• To maximize your revenue and your profits, you might generate prescriptive analytics reports. These might consider available resources, current and past performance, and desirable metrics, then suggest a strategy or specific actions.
• You might even want to use the data to train a machine learning algorithm to handle tasks like just-in-time inventory and manufacturing.

Read more about the difference between ETL and ELT in our recent post on the topic.

Modern tools for transforming data

A suite of data integration tools, called a modern data stack, that leverages ELT looks like so and funnels data through the following stages in the order specified:

1. Sources – data from operational databases, SaaS apps, event tracking
2. Data pipeline – extracts data from sources and loads it into the data warehouse, sometimes normalizing it
3. Data warehouse – stores data in a relational database optimized for analytics
4. Data transformation tool – a SQL-based tool (such as dbt) that uses data from the source to create new data models within the data warehouse
5. Analytics tool – tools for generating reports and visualizations, such as business intelligence platforms

Picking the right tool for the job

When building a modern data stack, be sure to consider where your business transformations will take place — in a data warehouse or before loading the data — as this will likely determine the agility of your projects and team.

There are a couple of tools often used to do data transformation:

• Python: This is usually used in combination with a data analysis library like pandas.
• SQL: Used in a destination cloud data warehouse to leverage the powerful capabilities of the cloud, e.g. Snowflake, BigQuery or Redshift.

If you’re interested in learning more about the benefits of either approach, Fivetran CEO George Fraser goes into more detail in his blog post on Towards Data Science.

You can also do data transformation in R, an open source programming language for data processing, statistical computing and machine learning that many developers and data scientists are familiar with.

It is certainly possible to transform data in Excel as well. It’s not a good idea, though: Excel lacks the robustness of modern data transformation tools and can only handle small numbers of records. More importantly, using Excel for data transformation generally means you’re downloading data and removing it from data governance systems where it can be controlled, shared, and updated.

In addition to the aforementioned methods, you may also use an open source tool like Airflow or dbt to orchestrate transformations.

Transforming within the data warehouse

Every transformation is custom-built to produce the specific data models a particular organization needs for analytics. Using ELT to decouple extraction and loading from transformation allows you to automate and outsource the extraction and loading stages of the data integration process and use a dedicated SQL-based transformation tool once the data is warehoused.

Roughly speaking, transformed data models within the data warehouse can be views or materialized views. Views are not stored on disk. Every time someone accesses a view, the data warehouse runs a query to return the relevant data. This includes performing joins, aggregations, and other operations on the fly. In an ideal world with zero latency and unlimited computational resources, all transformations would simply be views.

Materialized views, on the other hand, are stored on disk because views generated on the fly from a large table or complex query can be extremely non-performant. In fact, data warehouses will sometimes completely choke if you try to surface tables on-the-fly with hundreds of millions of rows.

Look for transformation tools that are SQL-based and work in the data warehouse environment. Some features to look for in transformation tools include test-driven development, collaboration and version control. Some BI platforms also offer proprietary tools and languages to build transformations.

Normalization: Transformation with a small “t”

In order for outsourced, automated ELT to work at scale, the outputs of the extraction and loading pipelines must be standardized. The simplest way to standardize a data model is through normalization.

Normalization of raw data eliminates redundancies, duplicates, and derived values, and organizes tables from the data into the clearest, simplest possible set of interrelations. This helps analysts easily interpret the underlying data model of the source app, and then construct new analysis-ready data models accordingly. Since normalization is a kind of transformation, ELT is, strictly speaking, often EtLT rather than ELT.

Normalization, the small “t,” can be an extremely difficult and labor-intensive task. To correctly normalize the data from a source, you need a keen understanding of the source application’s underlying functionality and data model.

The best way to circumvent this challenge is to outsource extraction and loading to a team that has extensive experience with data engineering for that particular source. At Fivetran, we are ready to help you get started.