Published at Nov 26, 2024
Start supercharging your PostgreSQL today.
Written by Junaid Ahmed
Data transformation is the key to unlocking the true value of raw data. While data is everywhere, in its raw form, it often lacks clarity or actionable meaning. Without proper treatment, it remains indistinct and unusable.
By transforming raw data into structured formats, businesses can uncover trends, identify correlations, and reveal opportunities. Data transformation enables the creation of insightful reports, supports outcome predictions, and drives better decision-making.
This is where PostgreSQL, one of the most popular relational database management systems, can help. This powerful open-source solution provides an extended set of features that allow it to make such transformations possible, efficient, and intuitive.
The broad functionality, flexible data types, and native capabilities make PostgreSQL the database of choice for any organization seeking deeper insights into stored data. In this article, we’ll briefly examine what data transformation entails and why PostgreSQL is such a strong candidate to handle these operations.
Data transformation involves converting or organizing data from one format to another. This process can range from simple format changes to complex adjustments in data structure. It ensures data suits a specific purpose, whether for databases, machine learning models, or business reporting.
A primary goal of data transformation is format conversion. Format conversion involves changing data types or converting files to formats like CSV, JSON, or XML to make data usable across different systems. An example is converting dates from a text file into a structured format for better querying and analysis.
Another vital task is adjusting data structure. Often, raw data isn’t in a format directly valid for analysis. Transforming data may include creating new columns, summarizing data, or merging information from multiple sources to make it easier to manage in subsequent processes.
Cleaning data is another crucial step. Raw data can be messy and needs extensive cleanup to be useful. Data cleaning includes removing duplicates, fixing errors, addressing missing values, and ensuring consistency. Accurate, complete data better supports quality data analysis and reduces potential errors from low-quality data.
Enhancing data adds context or combines information to create new insights. For example, in e-commerce, you can combine individual transactions with customer demographics to better understand buying behaviors.
Data transformation also supports repeatable data pipelines for organizations dependent on business intelligence. Automating data transformation with tools like dbt or Apache Airflow ensures consistent, accurate insights and results in data workflows that are repeatable, scalable, and less prone to manual errors.
Examples of data transformation
Data transformation in PostgreSQL reshapes raw data into a database table format for analysis. Data ingested through connectors (tools or processes for batch or streaming data loading, such as COPY) is loaded into tables and then transformed using SQL queries. Common transformations include filtration, joining, aggregation, and reshaping the data down into insights.
A wide variety of data loading connectors can manifest data into structured tables by pulling from sources such as CSV files, JSON APIs, and streaming data. When combined with SQL's capabilities, a firm basis is established for customized data transformation. This combination enables users to perform data transformations at any point after or during the ingestion process to maintain consistency and reduce processing costs.
A major part of data transformation in PostgreSQL is done with SQL queries. In other words, SQL selects functions that take an input set of data and provide an output set of data. Now, let's go into detail for a couple of the most common types of SQL queries used for transformation in PostgreSQL: SELECT statements and JOIN statements.
For this part, we will use the following tables:
Customer table
Orders table
Orders_items table
A SELECT statement in PostgreSQL selects various columns, filters rows, and formats data in the way you desire. SELECT provides the capability to extract just the information needed, perform calculations like aggregation, and eliminate superfluous rows from the dataset.
For instance:
SELECT customer_id, order_date, total_amount
FROM orders
WHERE total_amount > 100;
This query fetches information on customer_id, order_date, and total_amount from the orders table. It only displays those rows whose total_amount exceeds 100, thereby focusing on high-value transactions.
JOIN statements combine data from multiple tables based on one common column value. They permit distributed data to be viewed as if it were all stored in a single table. This is useful in handling normalized data, which is divided into several tables to avoid data redundancy and ensure consistency.
For instance:
SELECT customers.customer_name, orders.order_date, orders.total_amount
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;
The result of the query is:
It joins customers and orders tables based on customer_id, combining customer names, order dates, and their orders. This can help in finding purchasing patterns and understanding customer behavior.
There are several different types of JOIN operations in PostgreSQL, each designed to better fit a particular use case:
INNER JOIN: The most common type of JOIN, which returns only matching rows from both tables. If there is no match, it will exclude that row from the result.
Here is the query example for this:
SELECT customers.customer_name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
In this case, the result includes only customers who have placed orders.
LEFT JOIN (or LEFT OUTER JOIN): It returns all the records from the left table and matched records for the right table. In case of no match, the result is going to have NULL values in columns coming from the right table.
SELECT customers.customer_name, orders.order_date
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
This query will return all customers, even those who have not placed any orders, which can be useful for finding customers without recent activity.
RIGHT JOIN (or RIGHT OUTER JOIN): RIGHT JOIN returns all data from the right table and the matched instances from the left table similar to LEFT JOIN.
Using our customer data example:
SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers ON customers.customer_id = orders.customer_id;
From this query, we will get all the customer names from the customer table. The order_id will be NULL if there is no order for the customer. Here is the result of our query:
FULL JOIN or FULL OUTER JOIN: The join combines records when there is a match in either the left table or right table. If no matches are found, the result set (left or right) will populate NULL values for the missing side.
SELECT customers.customer_name, orders.order_date
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id;
That's helpful when you want to see everything from both tables: both where a match exists and where it doesn't. Here is the result of our query:
CROSS JOIN: This returns the Cartesian product of both tables. In other words, every row from the first table is combined with every row from the second table. This type of join can produce very large result sets and will usually be used for special cases only, such as generating combinations.
SELECT customers.customer_name, products.product_name
FROM customers
CROSS JOIN products;
Result is:
Here, each customer is matched against each product—a scenario that could be useful in applications such as generating possible product recommendations.
The types of joins make a big difference in understanding how to combine data that is dispersed in two or more related tables; these might require detailed analysis and management.
Some advanced transformation features make PostgreSQL particularly suitable for complex data manipulation. In this section, we will examine two such functions: LATERAL JOIN and WITH ORDINALITY.
A LATERAL JOIN is a special form of a join that enables subqueries or functions that appear on the right side of the join to reference columns from the left side. This enables complex calculations that are based on values from both tables being joined.
A simple example is as follows:
SELECT customer_id, order_date, item_name
FROM orders
CROSS JOIN LATERAL (
SELECT item_name
FROM order_items
WHERE order_items.order_id = orders.order_id
) AS items;
The result of this query is:
In this example, the LATERAL JOIN lets the subquery refer to every row of the orders table and return matching items in the order_items table. Such a join simplifies the syntax of queries involving correlated subqueries and allows such computation to be succinct for reuse over tables.
Now consider another example. Here, we will retrieve the details of each item ordered by customers from January 1, 2024, onwards:
SELECT o.customer_id, o.order_date, oi.item_name, oi.quantity
FROM orders o
CROSS JOIN LATERAL (
SELECT item_name, order_id, quantity
FROM order_items
WHERE order_items.order_id = o.order_id
) AS oi
WHERE o.order_date >= '2024-01-01'
ORDER BY o.customer_id, o.order_date DESC;
Result
The query retrieves information on orders whose date is after or on January 1, 2024. It selects a customer_id, order_date, item_name, and quantity of each order by joining two tables: orders aliased as 'o' and order_items as 'oi'.
It further does a cross join lateral to fetch items matching each ordered order_id. WHERE filters the orders to have a date from 2024 onwards; the results are sorted by customer_id and then order_date in descending order to show the latest orders first for each customer.
Another real-life application of LATERAL JOIN is when operating with data types such as JSON or arrays. One would need to expand each element into individual rows based on the corresponding value taken from the left table.
This is especially useful in PostgreSQL, which has very rich support for JSON manipulation. You can use LATERAL to flatten JSON arrays easily while still knowing what originating row that element came from.
The WITH ORDINALITY functionality in PostgreSQL introduces a way of enumerating the rows of a table to assign a numerical order to each. Although row enumeration is available in general SQL, the PostgreSQL version allows the use of WITH ORDINALITY with any table function. Thus, it becomes quite useful for tracking row orders produced by functions or complex queries.
For example:
SELECT UNNEST(array['apple', 'banana', 'cherry']) WITH ORDINALITY;
The query results will provide each fruit and the number of that fruit's appearance in an array, which can be particularly important when trying to identify the sequence or ranking of an item in a data set.
Besides working with arrays, WITH ORDINALITY can be used together with set-returning functions returning complex outputs. It helps the developer to mark each row with a unique identifier. This helps when combining data from several sources or when order tracking becomes crucial.
Consider the following higher-order example:
SELECT * FROM jsonb_each_text('{"a": "apple", "b": "banana", "c": "cherry"}') WITH ORDINALITY;
In this example, WITH ORDINALITY creates a row number for each key-value pair received from the JSON object. Hence, it does not provide only the key-value pairs but also the order in which they are being processed. Sometimes this capability can be very valuable in any such JSON data situations where the ordering of key-value pairs could be of importance downstream for analysis or reporting.
Another very good real-world application of WITH ORDINALITY is pagination, where the ordering of the rows is important for the presentation of data in blocks across many pages in a user interface. Enumerating the rows as it pulls them out makes it easy to tell exactly which rows are on which page, greatly simplifying the implementation of pagination logic.
Several of PostgreSQL's features make it a highly effective platform for data transformation:
PostgreSQL is an open-source database system with over 35 years of development and exceptionally thorough documentation. One of its greatest strengths is the complete transparency of its functionality, making it an excellent choice for learning and exploration.
As an open-source tool, PostgreSQL benefits from continuous innovation in the form of plugins, extensions, and tools built on top of its core functionality. This open ecosystem not only empowers users to create their own plugins or suggest improvements but also ensures that PostgreSQL evolves to meet changing data requirements.
One of PostgreSQL's standout features is its extensibility. Its extensive library of extensions adds new capabilities, allowing PostgreSQL to be tailored for a wide range of use cases. Users can even develop custom extensions to address unique requirements.
Entire products have been built on top of PostgreSQL, significantly expanding its functionality. For example, the PostGIS extension enables PostgreSQL to manage geospatial data, transforming it into a powerful spatial database. PostGIS provides tools for manipulating geographic data, such as converting between coordinate systems and calculating distances between points.
Another example is TimescaleDB, which optimizes PostgreSQL for managing time-series data and other demanding workloads, from vector data to real-time analytics. It supports efficient operations on large datasets, making it ideal for applications like IoT monitoring, financial analysis, and observability.
Extensions like these, along with many others, make PostgreSQL a versatile database system capable of addressing diverse, industry-specific needs.
The PostgreSQL community is large and highly active, with numerous developers and users contributing to its growth. This robust community ensures that answers to common questions are easily accessible. Developers can connect with peers working in similar fields to seek advice, share insights, and collaborate on best practices.
Additionally, the community provides comprehensive documentation that thoroughly explains PostgreSQL's features and capabilities. This resource is invaluable for both beginners and experienced users, enabling them to fully harness PostgreSQL’s transformative potential.
Most transformations in PostgreSQL are geared towards optimization. These data transformations are performed by tools that help load, process, and update data. Here are some of the tools often used in working with PostgreSQL transformations.
In simple terms, data source connectors link data from various sources for smooth integration with PostgreSQL. PostgreSQL has a set of built-in connectors to load data from common data sources, such as CSV files, spreadsheet software files, and JSON data.
These make it quite easy to ingest structured data directly into PostgreSQL. For example, users can easily load data from CSV files to database tables using the COPY command. More specialized connectors also exist.
A popular one includes Apache Kafka, which provides a robust means to connect systems with real-time data streams. Integrating PostgreSQL with Apache Kafka enables an organization to stream data directly into its database. This is important in an application that involves streaming IoT device data or even a financial transactional application.
PostgreSQL is well-known for its rich ecosystem, which allows developers to create a custom data system based on PostgreSQL's open-source baseline. Each extension is intended to add new and specific capabilities or improve PostgreSQL's performance for particular use cases, especially as data volumes grow.
It should be noted that while PostgreSQL is flexible and reliable, it’s also likely to suffer performance problems when the datasets become very large. To address these challenges, extended databases, such as TimescaleDB, have been developed.
TimescaleDB extends PostgreSQL with features designed to handle large volumes of time-series data. However, since TimescaleDB is PostgreSQL under the hood, it inherits its flexibility, handling many other demanding workloads (real-time analytics, vector data, events) with ease and supporting a wide range of applications and use cases.
For those who don’t think of PostgreSQL as a scalable database, Timescale’s solutions enable PostgreSQL to scale to petabytes of data and billions of new records per day. Demanding workloads like IoT, log management, and financial market monitoring are managed, stored, and queried with ease and speed. With efficient compression, continuous aggregation, and improved query performance, TimescaleDB extends PostgreSQL's capabilities and addresses performance bottlenecks that may arise when dealing with large-scale data.
Transformation coordination tools automatically schedule data transformation in the PostgreSQL setup. Such functionality lets organizations create complex workflows that lead to organized and consistent data transformation, making it relevant and precise.
dbt (an acronym for data build tool) is currently the most popular transformation coordination tool for PostgreSQL. As a modern data transformation platform, dbt gives data engineers and analysts powers to declare and orchestrate SQL-based transformations.
Using this software, users can write smaller SQL queries that represent transformations, establish dependencies between them, and then schedule transformations to execute at desired times.
For instance, an organization might need to run daily data transformations to keep its analytics current. Thanks to dbt, such transformations can be automated, and business stakeholders will always have fresh insights with minimal human touch involved. What's more, dbt integrated with PostgreSQL enables users to effortlessly run even the most sophisticated transformations, thus enabling the creation of analytically-oriented data models.
Data transformation is the backbone of any data system. Thanks to its flexibility, optimization, and strong community support, PostgreSQL has become one of the leading open-source platforms. Its transformation is based on strong, powerful, and optimized capabilities, making PostgreSQL a great fit for both ELT (extract-load-transform) and ETL (extract-transform-load) processes.
PostgreSQL opens wide opportunities for developing scalable, secure, and automated systems of data transformation, which can handle several types of basic data migration and complex, high-volume transformations. Using PostgreSQL will help streamline your workflow with data while keeping everything in your system working efficiently.
Need even more power? Enhance your PostgreSQL setup with tools like TimescaleDB, which boosts PostgreSQL’s efficiency for managing large datasets, from vector data, real-time analytics, and events to time-series data. With this combination, you can effectively implement scalable, secure, and automated systems tailored to your needs.
Ready to take your data transformation to the next level and scale PostgreSQL infinitely? Get started with a free Timescale Cloud account and make all the data transformations you need in PostgreSQL.