The Challenge
The credit union sought to gain insight into its customers’ cryptocurrency transaction statistics to launch a targeted campaign aimed at raising awareness of cryptocurrency-related risks and preventing outflows of funds from the bank. However, the challenge lay in accurately identifying crypto transactions within a vast master transaction table. This required applying various filtering criteria to each individual transaction row. Additionally, the limited availability of cryptocurrency information, primarily derived from transaction comments, posed an additional hurdle. With billions of records in the transaction table, processing such a volume efficiently was a daunting task.
The Solution
To address this challenge and enable targeted campaign planning, a comprehensive solution was implemented:
Change Data Capture (CDC) Stream: A CDC Stream was developed within the Snowflake database to capture changes in the master transaction table. This stream served to reduce the volume of data to be processed each day.
Daily Data Processing Task: A Snowflake ‘Task’ was created to consume the CDC Stream daily. This task executed various filtering criteria to identify rows corresponding to crypto transactions. The identified rows were then inserted into a new table dedicated to crypto transactions, all within the Snowflake database.
BI Application Integration: The dedicated crypto transaction table was made accessible to a Business Intelligence (BI) Application, ThoughtSpot. This allowed for in-depth analysis of various metrics and Key Performance Indicators (KPIs).
The Result
The implementation of this solution delivered substantial benefits:
The credit union gained valuable insights, such as identifying the top account holders engaged in crypto transactions, the most frequently transacted cryptocurrencies, age and region distribution of crypto users, and the volume of funds flowing in and out of the bank in relation to cryptocurrencies.
Armed with this information, the credit union executed a targeted campaign to sensitize account holders about the risks associated with cryptocurrency trading, helping prevent potential financial outflows.
The solution also revealed account holders who were using credit cards to purchase cryptocurrencies, a practice contrary to the organization’s rules, enabling the credit union to take appropriate actions.
This client success story showcases how a credit union harnessed Snowflake’s CDC Stream and Task features to enhance its monitoring of cryptocurrency transactions, enabling data-driven insights and informed decision-making to safeguard the organization’s financial interests.
Tools and Platforms Used
