Apache and Dataflow Help with Real-time Indices Processing for Financial Institutions
4902
Of your peers have already read this article.
4:00 Minutes
The most insightful time you'll spend today!
Financial institutions across the globe rely on real-time indices to inform real-time portfolio valuations, to provide benchmarks for other investments, and as a basis for passive investment instruments including exchange-traded products (ETPs). This reliance is growing—the index industry dramatically expanded in 2020, reaching revenues of $4.08 billion.
Today, indices are calculated and distributed by index providers with proximity and access to underlying asset data, and with differentiating real-time data processing capabilities. These providers offer subscriptions to real-time feeds of index prices and publish the constituents, calculation methodology, and update frequency for each index.
But as new assets, markets, and data sources have proliferated, financial institutions have developed new requirements. Financial institutions will need to quickly create bespoke and frequently updating indices that represent a specific actual or theoretical portfolio, with its unique constituents and weightings.
In other words, existing index providers and other financial institutions alike will need mechanisms for rapid creation of real-time indices. This blog post’s focus—an index publication pipeline collaboratively developed by CME Group and Google Cloud—is an example of such a mechanism.
The pipeline closely approximates a particular CME Group index benchmark, but with far greater frequency (in near real time vs. daily) than its official counterpart. It does so by leveraging open-source models such as Apache Beam and cloud-based technologies such as Dataflow, which automatically scales pipelines based on inbound data volume.
Machine learning’s production problem
In the past decade, advances in AI toolchains have enabled faster ML model training—and yet a majority of ML models are still not making it into production. As organizations endeavor to develop their ML capabilities, they soon realize that a real-world ML system is comprised of a small amount of ML code embedded in a network of complex and large ancillary components. Each component brings its own development and operational challenges, which are met by bringing a DevOps methodology to the ML system, commonly referred to as MLOps (Machine Learning Operations). To apply ML to business problems, a firm must develop continuous delivery and automation pipelines for ML.
This index publication collaboration is instructive because it demonstrates MLOps best practices for just such a pipeline. One Apache Beam pipeline, suited for operating on both batch and streaming data, extracts insights and packages them for downstream consumers. These consumers may include ML pipelines that, thanks to Apache Beam, require only one code path for inference across batch and real-time data sources. The pipeline is run inside Google Cloud’s Dataflow execution engine, greatly simplifying management of underlying compute resources.
But the collaboration’s value is not constrained to the ML and data science realm. The project shows that consumers of the Apache Beam pipeline’s insights may also include traditional business intelligence dashboards and reporting tools. It also demonstrates the simplicity and economy of cloud-based time series data such as CME Smart Stream, which is metered by the hour, quickly and automatically provisioned, and consumable at a per-product-code (not per-feed) level.
A focus on real-time processing for financial services
To illustrate the above points, the collaboration applies data engineering and MLOps best practices to a financial services problem. We chose the financial services domain because many financial institutions do not yet have real-time market data processing or MLOps capabilities today, owing to a significant gap on either side of their ML/AI objectives.
Upstream from ML/AI models, financial institutions often experience a data engineering gap. For many financial institutions, batch processes have sufficiently addressed business requirements. As a result, the temporal nature of the time series data underlying these processes is deemphasized. For example, the original purpose of most trade booking systems was to capture a trade and ensure that it found its way to the middle and back office for settlement. It was not built with ML/AI in mind, and its underlying data therefore has not been packaged for consumption by ML/AI processes.
And downstream from ML/AI models, financial institutions often encounter the aforementioned “ML production problem.”
As ML/AI becomes ever more strategic, these two gaps have left many financial institutions in a conundrum—unable to train ML models for lack of properly packaged time series data, and unmotivated to package time series data for lack of ML models. By recreating a key energy market index using open-source libraries and cloud-based tools, this collaboration demonstrates that for the financial services domain a solution to this conundrum is more accessible today than ever.
Creating a new index
We modeled our new index after one of CME Group’s many index benchmarks. The particular index expresses the value of a basket of three New York Mercantile Exchange—listed energy futures as a single price. Today, CME Group publishes the index at the end of the day by calculating the settlement price of each underlying futures contract, and then weighing and summing these values.
While CME Group does not currently publish this index in real time, this collaboration aims to create a near real-time solution leveraging Google Cloud capabilities and CME Group market data delivered via CME Smart Stream. However, in order to publish the value so frequently—every five seconds, with 40-second publish latency—this collaboration’s pipeline has to solve a number of challenges in near-real time.
First, the pipeline must process sparse data from three separate trades feeds in memory to create open-high-low-close (OHLC) bars. More specifically, for five-second windows for each of the three front-month (and sometimes second-month) energy contracts, a bar must be produced. This is solved by using the Apache Beam library to implement functions which, when executed on Dataflow, automatically scale out as input load increases. The bars must be time-aligned across the underlying feeds, which is greatly simplified by Beam’s watermark feature. And for intervals in which no tick data is observed, the Beam library is used to pull forward the last value received, yielding perfect gap-free bars for downstream processors.
Second, the pipeline must calculate volume-weighted average price (VWAP) in near real-time for each front-month contract. The VWAP calculations are also written using the Beam API and executed on Dataflow. Each of these functions requires visibility of each element in the time window, so the functions cannot be arbitrarily scaled out. Nonetheless, this is tractable because their input—OHLC bars—is manageably small.
Third, the pipeline must replicate CME Group’s specific settlement price methodology for each contract. The rules specify whether to use VWAP or another source as price, depending on certain conditions. They also specify how to weigh combinations of monthly contracts during a roll period. The pipeline again encapsulates these requirements as an Apache Beam class, and joins the separate price streams at the correct time boundary.
The end result is a new stream publishing bespoke index data to a Google Cloud Pub/Sub topic thousands of times daily, enabling AI models as well as traditional industry index usage, dashboards, and other tools to assist real-time decision making. The stream’s pipeline uses open source libraries that solve common time series problems out-of-the box, and cloud-based services to reduce the user’s operational and scaling burden.
The importance of cloud-based data
The promise of cloud-based pipeline execution services cannot be realized using legacy data access patterns, which often require market data users to colocate and configure servers and network gear. Such patterns inject expense and scaling complexity into the pipeline’s overall operation, diverting resources from the adoption of MLOps best practices. Instead, a newer, cloud-based access pattern—in which resources subscribe to data streams inexpensively, rapidly and programatically—is necessary.
In 2018, CME Group identified the customer need for accessible futures and options market data. CME Group collaborated with Google Cloud to launch CME Smart Stream, which distributes CME Group’s real-time market data across Google Cloud’s global infrastructure with sub-second latency. Any customer with a CME Group data usage license and a Google Cloud project can consume this data for an hourly usage fee, without purchasing and configuring servers and network gear.
CME Smart Stream met this index pipeline’s requirements for cost-effective, cloud-based streaming data, but this is just one use case. Since the launch of a CME Smart Stream offering on Google Cloud, globally dispersed firms have adopted the solution. For example, Coin Metrics has been using the offering to better inform its customers in the crypto markets. According to CME Group, Smart Stream has become popular with new customers as the fastest, simplest way to access CME Group’s market data from anywhere in the world.
Adapt the design pattern to your needs
By combining cloud-based data, open-source libraries, and cloud-based pipeline execution services, we created a real-time index using the same constituents as its end-of-day counterpart. Additionally, financial institutions will find this approach addresses many other challenges—real-time valuation of a large set of portfolios; benchmark creation for new ETPs; or external publication of new indices.
Give it a try
This approach is available to help you meet your organization’s needs. Please review our user guide, whose Tutorials section provides a step-by-step guide to constructing a simple Apache Beam pipeline to generate metrics on streaming data in real-time, and connecting a new data source to the pipeline. We’ll be discussing this topic in CME Group’s webinar End-to-End Market Data Solutions in the Cloud at 10:30 am ET on June 16th.
12004
Of your peers have already watched this video.
3:26 Minutes
The most insightful time you'll spend today!
How L&T Financial Services Processes 95% of Motorcycle Loans in Less Than Two Minutes
L&T Financial Services is one of the largest lenders in India. India’s demonetization policy in recent years has led to a shift from cash transactions to digital payments. In 2016, the government withdrew 500 and 1000 rupee notes from circulation and encouraged a heavily cash-based population to deposit their canceled notes in banks. Financial institutions needed to pivot to a new way of doing business to stay competitive. L&T Financial Services modernized its IT infrastructure to keep up with changes and capture digital opportunities.
“Working capital is crucial to stimulate growth in rural communities. Our role as a lender is to provide access to funds. We don’t want to burden borrowers with the complexities of getting a loan. Towards this end, digitization is an important step,” says Dinanath Dubhashi, Managing Director and CEO at L&T Financial Services. “Google Cloud helps us streamline service delivery and identify the right customers. By offering the fastest processing time in the industry, we want to be the go-to lender for all customers.”
L&T Financial Services considered multiple cloud providers before choosing Google Cloud. According to Dinanath, Google Cloud understands both the need for businesses to move fast and the need for IT to modernize at different speeds. “We weren’t forced to abandon existing IT systems and migrate lock, stock, and barrel to Google Cloud on day one.”
L&T Financial Services engaged Google Cloud Professional Services to guide its digital transformation journey. The smooth migration from proof of concept to full-scale deployment on Google Cloud took a matter of months.
“Collaboration: a small idea with big opportunities. G Suite helps us connect remote branches with the head office, easily access shared files to submit and track approvals, and conduct face-to-face discussions to accelerate approval processes.”
—Dinanath Dubhashi, MD and CEO, L&T Financial Services
Digitizing the workforce with G Suite
The move to the cloud at L&T Financial Services started in 2017 when the company introduced G Suite to its 14,500 employees. The legacy email system was cumbersome to use, especially for frontline staff who need email access while they are on the road. Using Gmail, employees can connect with customers and co-workers from anywhere, on any device. Employees save time by scheduling meetings with Calendar, collaborating on Docs, and conducting video calls using Hangouts Meet.
Converting data into credit insights using BigQuery
Taking data intelligence one step further, L&T Financial Services adopts a responsible lending approach by applying algorithm-based data analytics to improve credit standards. Beyond traditional data such as credit score and credit payment history, the company also considers macro-economic indicators for risk audits. For example, a farmer’s ability to pay off the loan of his new tractor depends on a successful planting and harvest. So L&T Financial Services feeds long-term data into BigQuery and runs queries to predict loan defaults based on rainfall and crop yield.
Make Meaningful Analysis with Geo Boundary Public Datasets on BigQuery
6333
Of your peers have already read this article.
4:00 Minutes
The most insightful time you'll spend today!
Geospatial data is a critical component for a comprehensive analytics strategy. Whether you are trying to visualize data using geospatial parameters or do deeper analysis or modeling on customer distribution or proximity, most organizations have some type of geospatial data they would like to use – whether it be customer zipcodes, store locations, or shipping addresses. However, converting geographic data into the correct format for analysis and aggregation at different levels can be difficult. In this post, we’ll walk through some examples of how you can leverage the Google Cloud platform alongside Google Cloud Public Datasets to perform robust analytics on geographic data. The full queries can be accessed from this notebook here.
Public US Geo Boundaries dataset
BigQuery hosts a slew of public datasets for you to access and integrate into your analytics. Google pays for the storage of these datasets and provides public access to the data via the bigquery-public-data project. You only pay for queries against the data. Plus, the first 1 TB per month is free! These public datasets are valuable on their own, but when joined against your own data they can unlock new analytics use cases and save the team a lot of time.
Within the Google Cloud Public Datasets Program there are several geographic datasets. Here, we’ll work with the geo_us_boundaries dataset, which contains a set of tables that have the boundaries of different geospatial areas as polygons and coordinates based on the center point (GEOGRAPHY column type in BigQuery), published by the US Census Bureau.
Mapping geospatial points to hierarchical areas
Many times you will find yourself in situations where you have a string representing an address. However, most tools require lat/long coordinates to actually plot points. Using the Google Maps Geocoding API we can convert an address into a lat/long and then store the results in the BigQuery table.
With a lat/long representation of our point, we can join our initial dataset back onto any of the tables here using the ST_WITHIN function. This allows us to check and see if a point is within the specified polygon.
ST_WITHIN(geography_1, geography_2)
This can be helpful for ensuring standard nomenclature; for example, metropolitan areas that might be named differently. The query below maps each customers’ address to a given metropolitan area name.
SELECTcust.id as customer_id,metro.name as metro_nameFROM `looker-private-demo.retail.customers` as cust,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metroWHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),metro.metdiv_geom)
It can also be useful for converting to designated market area (DMA), which is often used in creating targeted digital marketing campaigns.
SELECTcust.id as customer_id,dma.dma_nameFROM `looker-private-demo.retail.customers` as cust,`bigquery-public-data.geo_us_boundaries.designated_market_area` as dmaWHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),dma.dma_geom)
Or for filling in missing information; for example, some addresses may be missing zip code which results in incorrect calculations when aggregating up to the zipcode level. By joining onto the zip_codes table we can ensure all coordinates are mapped appropriately and aggregate up from there.
SELECTzip.zip_code,count(distinct cust.id) as unique_customersFROM `looker-private-demo.retail.customers` as cust,`bigquery-public-data.geo_us_boundaries.zip_codes` as zipWHERE ST_WITHIN(ST_GEOGPOINT(cust.longitude, cust.latitude),zip.zip_code_geom)GROUP BY 1
Note that the zip code table isn’t a comprehensive list of all US zip codes, they are zip code tabulation areas (ZCTAs). Details about the differences can be found here. Additionally, the zip code table gives us hierarchical information, which allows us to perform more meaningful analytics. One example is leveraging hierarchical drilling in Looker. I can aggregate my total sales up to the country level, and then drill down to state, city and zipcode to identify where sales are highest. You can also use the BigQuery GeoViz tool to visualize geospatial data!
Aside from simply checking if a point is within an area, we can also use ST_DISTANCE to do something like find the closest city using the centerpoint for the metropolitan area table.
SELECTcust.id as customer_id,ARRAY_AGG(metro.name order by ST_DISTANCE(ST_GEOGPOINT(cust.longitude, cust.latitude),metro.internal_point_geom) asc limit 1)[offset(0)] as metro_nameFROM`looker-private-demo.retail.customers` as cust,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metroGROUP BY cust.id
This concept doesn’t just hold true for points, we can also leverage other GIS functions to see if a geospatial area is contained within areas that are listed in the boundaries datasets. If your data comes into BigQuery as a GeoJSON string, we can convert it to a GEOGRAPHY type using the ST_GEOGFROMGEOJSON function. Once our data is in a GEOGRAPHY type we can do things like check to see what urban area the geo is within – using either ST_WITHIN or ST_INTERSECTS to account for partial coverage. Here, I am using the customer’s zip code to find all metropolitan divisions where the zip code polygon and the metropolitan polygon intersect. I am then selecting the metropolitan area that has the most overlap (or the intersection has the largest area) to be the customer’s metro that we use for reporting.
SELECTcust.id as customer_id,ARRAY_AGG(metro.name order by ST_AREA(ST_INTERSECTION(zip.zip_code_geom,metro.metdiv_geom)) desc limit 1)[offset(0)] as metro_nameFROM`looker-private-demo.retail.customers` as custJOIN `bigquery-public-data.geo_us_boundaries.zip_codes` as zip on cust.zip=zip.zip_code,`bigquery-public-data.geo_us_boundaries.metropolitan_divisions` as metroWHERE ST_INTERSECTS(zip.zip_code_geom,metro.metdiv_geom)GROUP BY cust.id
The same ideas can be applied to the other tables in the dataset including the county, urban areas and National Weather Service forecast regions (which can also be useful if you want to join your datasets onto weather data).
Correcting for data discrepancy
One problem that we may run into when working with geospatial data is that different data sources may have different representations of the same information. For example, you might have one system that records state as a two letter abbreviation and another using the full name. Here, we can use the state table to join the different datasets.
SELECTst.state_name,sum(ab.sales+fn.sales) as total_salesFROM `bigquery-public-data.geo_us_boundaries.states` as stLEFT JOIN abbreviated_table as ab on ab.state = st.stateLEFT JOIN fullname_table as fn on fn.state = st.state_nameWHERE COALESCE(ab.state, fn.state) IS NOT NULLGROUP BY 1
Another example might be using the tables as a source of truth for fuzzy matching. If the address is a manually entered field somewhere in your application, there is a good chance that things will be misspelled. Different representations of the same name may prevent tables from joining with each other or lead to duplicate entries when performing aggregations. Here, I use a simple Soundex algorithm to generate a code for each county name, using helper functions from this blog post. We can see that even though some are misspelled they have the same Soundex code.
Next, we can join back onto our counties table so we make sure to use the correct spelling of the county name. Then, we can simply aggregate our data for more accurate reporting.
SELECTc.county_name,sum(sales) as total_salesFROMtableJOIN `bigquery-public-data.geo_us_boundaries.counties` as con testing.dq_fm_Soundex(table.county) = testing.dq_fm_Soundex(c.county_name)WHERE c.state_fips_code = cast(36 as string)GROUP BY 1
Note that fuzzy matching definitely isn’t perfect and you might need to try different methods or apply certain filters for it to work best depending on the specifics of your data.
The US Geo Boundary datasets allow you to perform meaningful geographic analysis without needing to worry about extracting, transforming or loading additional datasets into BigQuery. These datasets, along with all the other Google Cloud Public Datasets, will be available in the Analytics Hub. Please sign up for the Analytics Hub preview, which is scheduled to be available in the third quarter of 2021, by going to g.co/cloud/analytics-hub.
4408
Of your peers have already watched this video.
31:30 Minutes
The most insightful time you'll spend today!
Smart analytics: Deep dive on roadmap
Data across organizations is growing and that organizations need a very strong analytics platform to leverage this data create insights and make real-time decisions on top of this data.
That’s driving the advent of three large trends. First is the convergence of data lakes and data warehouses, that’s enable organizations to maximize the value of their data.
Second, is the growing phenomena of real-time decision-making which is forcing enterprises to think of how they can support the needs of batch processing and streaming data.
Finally, there is the rise of artificial intelligence and machine learning, which allows enterprises to leverage their data and create competitive differentiation.
With this background, Sudhir Hasbe, Director of Product Management, Data Analytics, Google Cloud, walks us through Google Cloud’s smart analytics offerings—and what’s new.
He takes us on a tour through the technical value of Google Cloud’s smart analytics platform end-to-end. He provides a comprehensive overview and demos what’s new and what’s next in Google Cloud’s smart analytics portfolio across products like BigQuery, Dataflow, Dataproc, Data Fusion, PubSub, Data Catalog, Dataprep, and Looker.
Google’s Lesson on Leveraging AI and More to Optimize Cloud Value for Innovation
DOWNLOAD EXPLAINER4837
Of your peers have already downloaded this article
1:30 Minutes
The most insightful time you'll spend today!
Google has managed to stay ahead of the curve and demonstrate its ‘growth while staying innovative’ approach using advanced open technologies, AI/ML based analytics solutions, as well as tools for team collaboration and skill development, automation and storage security. Businesses too can achieve the same by deriving maximum value from its people and technology by following Google’s simple guide to stay innovative. Download to learn more.
The Power of Personalization: Ocado Retail’s Strategy to Boost Revenue and Lower Churn
1715
Of your peers have already read this article.
3:30 Minutes
The most insightful time you'll spend today!
Retailers are becoming more skilled at making individual customers feel heard and valued. This is a necessity given the fact that 66% of respondents to a McKinsey survey stated that they expect email marketing messages to be tailored to their needs. While marketing personalization expertise is growing, it’s still difficult to manage, especially at scale.
Ocado Retail, one of the world’s largest dedicated online grocery retailers, delivering to over 645,000 customers in the U.K., has made personalization integral to its success.
Let’s look at how Ocado retail worked with Google Cloud and partner Cognizant to develop a new data platform to power its personalization efforts from the ground up.
Unifying data for more holistic, powerful personalization
To achieve its goals of personalization at scale, Ocado Retail needed a central data warehouse that could turn all forms of merchandising, advertising, business, and customer intelligence data into actionable insights. It wanted a means to accelerate customer segment identification, as well as the ideation and launch of relevant campaigns.
“We standardized on Google Cloud, including BigQuery, as the foundation for our data platform because we knew it was the right solution for now and the future,” says Kieren Johnson, Head of IT at Ocado Retail. “We have an incredibly lean team and we needed a partner with exceptional expertise to help build an ambitious enterprise data warehouse to provide powerful insights. Cognizant was also the clear choice to help us get there.”
Cognizant worked closely with Ocado Retail to make sure its expertise in Google Cloud and other technologies aligned with Ocado Retail’s vision to drive more advanced personalization at higher scales using machine learning. The partner helped build the foundation on BigQuery, and then incorporated other Google Cloud tools such as Cloud Run, Vertex AI, and Vertex AI Natural Language to provide no-ops, all-code warehousing, and analytics capabilities.
Cognizant also took advantage of the Google Cloud Partner Success Services (PSS) program to ensure best practices were being followed throughout the project. PSS provided advisory services that guided Cognizant through the highly complex process of building the new data warehouse for Ocado Retail on Google Cloud.
Building the enterprise data platform in this way allows Ocado Retail to leverage the full power of cloud-based analytics while maintaining a lean team. It also allows the company to greatly scale up its personalization efforts.
Making customers feel valued at every touch
The work Ocado Retail has done with Google Cloud and Cognizant has positioned it to make its growing customer base feel valued, understood, and supported in every interaction. Before launching the project with Cognizant and Google Cloud, Ocado Retail was only able to run a couple of campaigns per week and knew it lacked optimal insight into each campaign’s efficacy.
“We now run 10 times the number of campaigns we used to with the help of the data platform Cognizant built on Google Cloud,” says Kieren. “We run multiple campaigns every day for different customer segments, and all of that increased activity is entirely driven by data insights. The positive impacts on our marketing and customer service performance have been clear. We’re now working to expand what we do.”
Ocado Retail has enjoyed solid growth since the new data platform went live, including a 13% rise in active customers during fiscal year 2022, and has also seen a reduction in churn. It attributed these improvements to being better able to tailor products and communications to specific customer preferences.
Throughout the project, Cognizant supported data clean up while maximizing the scalable, flexible, and future-proofed data analytics infrastructure offered by Google Cloud.
Increasing data-driven actions
Ocado Retail plans to provide more data-driven insights to its commercial suppliers through a product called Beet Insights. So far, Beet Insights offers suppliers with intelligence about how their products are performing on the shelves. The result has been improving the role data plays throughout the supply chain, from production to purchase and beyond.
“By putting real-time insights about costs, marketing spend, and supply-funded activities into the hands of our commercial team and buyers, we are better positioned to improve our profits,” says Kieren. “At the same time, building data analytics into every part of our business will allow us to build on our personalization efforts.”
From the project’s inception, Ocado Retail ensured that the platform would be scalable and dynamic. Now, it is working to feed more data sources and streams into the warehouse to accelerate time to insights. Ocado Retail believes this next step in the evolution of the platform will unlock even more opportunities to initiate high-impact programs that transform personalization and every customer interaction.
Learn more about what Google Cloud and partners like Cognizant can do for your customer intelligence and personalization.
More Relevant Stories for Your Company
Google and AI Researchers Work towards Building Data-centric AI
AI researchers and engineers need better data to enable better AI solutions. The quality of an AI solution is determined by both the learning algorithm (such as a deep-neural network model) and the datasets used to train and evaluate that algorithm. Historically, AI research has focused much more on algorithms
How Digital Simulation of Physical Stores Helped e-Commerce Companies Replenish Stocks and Fulfil Online Orders at Scale in 2020
Editor’s note: We’re inviting partners from across our retail ecosystem to share stories, best practices, and tips and tricks on how they are helping retailers transform during a time that has seen tremendous change. The original version of this blog was published by Trax Retail in October 2021. Please enjoy this updated
Data to Business Outcomes with Google’s Data Analytics Design Pattern
Companies today are inundated with vast amounts of data from various sources. This overwhelming amount of data is meant to benefit the company, but often leaves data teams feeling overwhelmed, which can create data bottlenecks and result in a slow time to value. In fact, only twenty seven percent of
Swarovski’s Journey towards Online and Offline Conversion with Predictive Analytics
Luxury brand and leader in crystals and glass production, Swarovski has charmed customers with its exquisite collections for over 125 years. To understand their customers better and map their online behaviors, Swarovski had to overcome prediction hurdles as majority of the purchases are not frequent or habitual. They are mostly
