Timestamp-based Change Data Capture

Introduction

Hey all!! Hope you are following the series of topics around Data Integration with PostgreSQL, The World’s Most Advanced Open Source Relational Database. In our previous blog, we explored the Change Data Capture (CDC) and its methods. If you missed it, we recommend reading the blog.

Timestamp-based CDC (Change Data Capture) is one approach to capturing and tracking changes in a database by associating a timestamp with each change. It is a versatile and widely used approach in tracking the changes in databases and supports various use cases like data replication and synchronization. In this blog, we will explore the prerequisites to execute the timestamp-based CDC and the setup with the result set.

Setup the Environment

Before diving into CDC, let’s ensure we have a timestamp column in the table (source) and the necessary components installed and configured.

PostgreSQL Database

• Install PostgreSQL on the server or your local machine.
• Create a database and the required tables that will be part of the CDC.

Pentaho Data Integration (ETL tool)

• Install PDI on the server or your local machine – you may refer to this blog for a step-by-step procedure
• Connect with the databases.
• Create the data pipeline for CDC.

Designing the CDC Pipeline

In this CDC pipeline, we will create a new transformation and building a pipeline to extract the data from one table(t1) in one PostgreSQL database and load it in another table(t2) in another PostgreSQL database in incremental basis.

Define the date range

• Go to Design → Input → Get system info
• Here, we are getting the start and end range of the transformation.
• Hop the result to the next step as input.

Export data from Source

• Go to Design → Input → Table input
• Here, we will extract the data in the table(t1) using a timestamp column by giving SQL statements.
• Hop the result to the next step as input.

Import data into Target

• Go to Design → Output → Insert/update
• Here, we would insert the data(output from the previous step) into the target table(t2) as per the given conditions.

Input and Output

• Assuming that both tables(t1 & t2) in different databases with columns (id, name & last_update) have no data.
• Insert the data in the table(t1) using the insert command.

• Check the data in table(t2) before running the transformation.

• Run the transformation. If the transformation is successful, you will get the ✅ at the top of the following steps.
• Now, check the target table(t2).

• Compare the data in both the tables(t1&t2).

Note: As discussed in the previous blog, CDC using timestamp will not work for the DELETE option.

Conclusion

In conclusion, the adoption of timestamp-based Change Data Capture (CDC) in PostgreSQL marks a pivotal stride in the realm of data integration. By leveraging this sophisticated approach, you can build systems to capture and propagate real-time changes efficiently. The journey through the nuances of timestamp-based CDC in PostgreSQL+Pentaho DI showcases not just a technical evolution but a strategic leap towards a more responsive and interconnected data ecosystem. As we witness the seamless synchronization of data across diverse platforms and applications, the potential for data-driven decision-making becomes boundless. In the ever-evolving landscape of data integration, embracing timestamp-based CDC in PostgreSQL is not just a best practice—it’s a transformative key to unlocking the true power of real-time data.

Stay tuned for more in Data Integration with PostgreSQL blog post series!!