We use an AWS DMS task to capture the changes in the source RDS instance, Kinesis Data Streams as a destination of the AWS DMS task CDC replication, and an AWS Glue streaming job to read changed records from Kinesis Data Streams and perform an upsert into the Amazon Redshift cluster.

The following diagram depicts the architecture of the solution that we deploy using AWS CloudFormation. To meet this requirement, we merge these four tables into one table and denormalize, tokenize, and mask the data. They also want to mask the field phone_number and tokenize the field email_address as sensitive information. To make the data easy to analyze, the data analyst wants to have only one table that includes all the information instead of joining all four tables every time they want to analyze. Teresa following is the fact table ticket_activity (the field purchased_by is the foreign key for the field customer_id in the table customer). The following is the dimension table customer. The following is the dimension table ticket (the field event_id is the foreign key for the field event_id in the table sport_event). The following is the dimension table sport_event. The following are examples of the data in each table. To use Amazon Redshift as a primary data mart, the data analyst needs to enrich and clean the data so that users like business analysts can understand and utilize the data easily. We assume a persona, a data analyst, who is responsible for analyzing trends of the sports activity from this continuous data in real time. The records ingested into the table ticket_activity are only updated when needed, when an administrator maintains the data. One record is inserted into the table ticket_activity every time a customer buys a ticket, so new records are being ingested into this fact table continuously. who purchased the ticket) is recorded in the table ticket_activity. When a customer buys a ticket, the activity (e.g. The table customer stores individual customer names, email addresses, and phone numbers, which are sensitive information. The table ticket stores seat level, location, and ticket policy for the target sport event. The table sport_event stores sport type (such as baseball or football), date, and location. We have three dimension tables: sport_event, ticket, and customer, and one fact table: ticket_activity. Example use caseįor our example use case, we have a database that stores data of a fictional organization that holds sports events. This post demonstrates how this second approach works in a customer scenario. To have more flexibility to denormalize, transform, and enrich the data, we recommend using Amazon Kinesis Data Streams and AWS Glue streaming jobs between AWS DMS tasks and Amazon Redshift. This approach works well for simple data replication.

To achieve CDC from Amazon Relational Database Service (Amazon RDS) or other relational databases to Amazon Redshift, the simplest solution is to create an AWS Database Migration Service (AWS DMS) task from the database to Amazon Redshift. With Amazon Redshift, you can query and combine exabytes of structured and semi-structured data across your data warehouse, operational database, and data lake using standard SQL. For analytic workloads such as reporting, business analysis, and ad hoc queries, Amazon Redshift is powerful option. Change data capture (CDC) is one of the most common design patterns to capture the changes made in the source database and relay them to other data stores.ĪWS offers a broad selection of purpose-built databases for your needs. In most real-world use cases, it’s important to replicate the data from a source relational database to the target in real time. Due to the exponential growth of data volume, it became common practice to replace such read replicas with data warehouses or data lakes to have better scalability and performance. Traditionally, read replicas of relational databases are often used as a data source for non-online transactions of web applications such as reporting, business analysis, ad hoc queries, operational excellence, and customer services.

Aws redshift postgres