Why do we need CDC (Change Data Capture)
The everyday use of bank cards has long been familiar, and people, as a rule, do not think that each use of a payment terminal is the prompt transfer of certain information to the bank. Data volumes are growing, and I would like to process them as quickly as possible, including to send special offers in real time, because, as they say, a good spoon for dinner. And traditional ETL tools (extract, transform, load – extract, transform, load) are not so good for processing data on the fly. Here is one of the weak links: when data is taken from tables in the source system, you only need to select new rows or rows with changes. This approach additionally loads the source system and increases the amount of transmitted data.
CDC captures changes from database logs in real time. So the source is loaded much less, and the amount of data transmitted is reduced. With this technology, we reduced the need for computing resources for systems with a large volume of transactional changes: after all, for data capture even for the most highly loaded banking systems, 1-2 processor cores at the source are enough. And if we were introducing ETL, then we would have to buy processor capacities in order to read data in parallel.
We have been using Oracle GoldenGate technology, Oracle’s CDC tool, for several years at VTB. With its help, we fill the online data warehouse and distribute the functions of information systems into “warm” and “hot” zones. 90% of the use of OGG in the bank’s IT landscape is loading data from Oracle and MS SQL, but, in addition to replication, it copes with other tasks perfectly. Let's look at a few examples from our practice.
Case 1. Online reporting
We met GoldenGate in 2013. Then, at our card processing complex, transactions were simultaneously processed and reports prepared. The OLTP load was mixed with the DWH / DSS load, and large, heavy samples flushed the cache from the database memory. As a result, fast transactions had to go to the hard drive, the speed of critical business services was falling. In order to offload the processing core, we took all the procedures and reports we developed to a “warm” replica on Oracle Exadata.
How to replicate data using GoldenGate, we described in detail here. In a nutshell: for highly loaded systems where there is mixing of different types of loads, we distribute them to different OLTP and DWH / DSS servers, and for synchronization between them we use GoldenGate. This pattern of highlighting a “warm” replica came in handy in many other cases. For example, the same approach was used in our anti-fraud system – all reporting is done on Oracle Exadata integrated systems, data is replicated to them using GoldenGate.
There are no systems without failures. For example, if a developer changes the data on the receiver, then a data usage error may occur and GoldenGate processes will stop. To exclude data out of sync, we use Oracle GoldenGate Veridata as an independent arbiter. This tool does not just verify data between sources and receivers – the main thing is that Veridata eliminates the differences. It’s important that when we deal with replication, Veridata ensures accurate data comparison and the detection of lost records. We get a full report with comparison results, which can be presented to incredulous colleagues.
Case 2. Consolidated reporting and staging in online storage
A separate case is associated with the construction of operational storage. The difficulty lies in the fact that, in addition to operational reporting, we are preparing data for corporate storage (staging). It happens that you need to generate operational reporting based on data that is collected from a number of different systems. And it is most convenient to do this at the level of online storage. To obtain data at high speed and with a minimum load on resources, we once again applied GoldenGate.
For comparison, let’s explain how we found the delta of changes in some of our systems before. If the system itself did not allow the delta to be highlighted or changed the data retroactively, then the table from the source of 10 TB was compared with the table of 10 TB at the receiver for the previous day. These 10 TBs had to be captured first at the source, and the load fell not only on the source system, CPU, memory, but also on the data network, as well as on the system involved in the comparison. And all this in order to find the delta of new data in 0.01%!
GoldenGate creates virtually no load on the source: CDC just reads the magazines and produces a finished delta. This allows you to seriously save on infrastructure. It does not matter who the receiver is – a traditional repository based on Oracle, MSSQL, Teradata or just Hadoop.
Note that in this case, Oracle databases were used as the source and receiver. The solution has shown its effectiveness, so now we are connecting all new systems to a common online data warehouse, and now it is not only Oracle. Another advantage of GoldenGate is that it is suitable for downloading data from most of the databases used in the bank’s IT landscape.
Case 3. Personal offers to clients in real time.
We already mentioned streaming analytics, that is, real-time Offering (RTO) offers to clients in real time. Senior comrades say that success in the banking business directly depends on how well you know your client and how relevant you can make him an offer. In other words, the likelihood that the client will take advantage of the bank’s offer is inversely proportional to the bank’s reaction rate to the client’s needs.
How it works? For example, a transaction history shows that a customer purchases from a liquor store every Friday. Geo-positioning detects it in a shopping center where there is a store of this network, and through a mobile application we send him a personal offer for a discount to a gourmet store in the same shopping center. Such a case is the most interesting for the bank; it allows you to create co-brands and joint offers. Clients can be individuals and organizations.
There are offline and online parts. In the first, customers are pre-segmented using data from all systems. Analysts and data Scientists study behavior, historical data and create so-called traps. The main thing is to catch a significant event that can be tracked by an acquirer transaction, a mobile application or other available sources. And already this event is processed by means of streaming analytics, and the decision is made in the moment based on the prepared traps.
The goal of CDC GoldenGate is to provide real-time event data flow from source systems to the analytic platform. The GoldenGate for Big Data license also includes Oracle Stream Analytics. With its help, data Scientists can independently process the data stream on Spark Streaming, developing the application in a visual environment.
Case 4. Operational counteraction to new types of fraud
Antifraud systems are quite closed, and rightly so: the fewer people are dedicated to the details, the higher the security. They do a great job of handling standard cases, but sometimes there are situations that do not fit into standard scripts. Therefore, it is important to supplement these models with non-standard scenarios. We are constantly developing new models based on correlation of events of various systems: card and currency transactions, location, operations of payment systems, actions in mobile applications, monitoring of social networks. To change the model, you have to follow the accepted processes: business request, task setting, passing the application through all the internal stages of implementation.
Last year, we tested uploading data using Oracle GoldenGate for Big Data from the busiest traditional systems, where there were a lot of small transactions, and from our antifraud system to the main cluster on the Oracle Big Data Appliance. Both Hadoop and GoldenGate handled the amount of data transferred – we were somewhat surprised.
Terabytes per hour and other findings
Over the past two years, with GoldenGate, we have doubled the volume of logs transferred – up to almost 1 TB per hour. This practically closes our needs at the moment. Unfortunately, there is physics that we run into. But to increase throughput, active work is being done with the GoldenGate development team, so this is far from the limit. At the same time, we are watching and piloting CDC solutions from other vendors, but we did not find any reasons for migrating from Oracle GoldenGate. At the moment, this technology has proved to be the most mature of the ones on the market.