Automating credit card fraud detection with Google BigQuery ML and Fivetran

Exploring fraud detection with automated data integration in a cloud data warehouse: part two of two.

In this second part of our blog series, we'll demonstrate how to set up an automated fraud detection system using Google's BigQuery ML and Fivetran. This blog post demonstrates setting up an automated fraud detection system using BigQuery ML and Fivetran. We'll move a credit card transactions dataset from a source database to BigQuery using Fivetran, then run BigQuery ML classification experiments to predict fraudulent transactions. This solution enables centralized data, modernized infrastructure and scalable ML applications for combating credit card fraud, all without requiring setup or maintenance.

Set up BigQuery as a Fivetran destination

Fivetran accounts support multiple Google Cloud destinations. Setting up a BigQuery destination is quick and easy – just provide your BigQuery Project ID and grant the BigQuery User role to the Fivetran service account in the project. You can also choose your preferred Google Cloud processing region.

Setting up BigQuery as a Fivetran destination is quick and easy

Add a Fivetran to BigQuery connector

I already have multiple data sources flowing into BigQuery, including SAP, SQL Server and Oracle operational databases, Salesforce,  GA4, Kafka, S3 and Workday. Fivetran has over 500 source connectors out of the box across multiple categories, including databases, applications, file systems and event systems, and that number is growing by the day.

Fivetran supports over 500 data sources to automate data movement into BigQuery

I want to move a credit card transactions dataset from a Microsoft SQL Server database to BigQuery for use with BigQuery ML. I'll add a SQL Server connector using Fivetran, which offers various options for connecting and moving relational databases to Google Cloud, including SaaS standard connectors, HVA hybrid connectors and LDP. For this dataset, I'll use a Fivetran SaaS standard database connector with log-free change detection.

Microsoft SQL Server source options in Fivetran

Fivetran provides detailed and step by step documentation for quickly moving SQL Server data into BigQuery. The SQL Server Setup Guide outlines the steps and each source and destination page includes details on version support, configuration, features, limitations, sync overview, schema information and type transformations. I've linked the SQL Server source detail page for your reference.

Everything you need to know about connecting to SQL Server with Fivetran

The SQL Server setup page in Fivetran has a setup guide to help you quickly connect to SQL Server. You choose the destination schema name (e.g., "automate_sqlserver_to_BigQuery_for_bqml_experiments") and provide authentication details for the SQL Server database. Fivetran handles the initial sync and incremental updates using either CT/CDC or Fivetran Teleport (log-free change detection).

SQL Server setup page in Fivetran

Fivetran encrypts all data in motion using Transport Layer Security (TLS) for direct connections to SQL Server, ensuring encryption and authentication. Any data temporarily stored in the Fivetran service is encrypted with AES256.

Selecting the credit card transactions dataset to use with BigQuery ML

After successfully connecting to SQL Server, Fivetran fetches the available source schemas, tables and columns. Instead of syncing all 13 schemas, I'll select only the "financial_services" schema and the "credit_card_transactions_fraud_detection" table for my fraud detection use case, blocking the other schemas and tables from moving to BigQuery. Fivetran also offers column-level hashing for data privacy and anonymization of PII data while still allowing the hashed columns to be used in downstream ML workflows.

Dataset selection (credit_card_transactions_fraud_detection) along with schema, table and column blocking (plus column hashing)

Incremental changes and schema drift

Fivetran then needs to know how I want to handle incremental changes since the schema may change in the future. I’m going to “Allow all”, but I have a range of options here. Any and all DML and DDL changes are automatically captured by Fivetran and delivered to BigQuery - no coding is required and I can determine the polling frequency to the SQL Server source for the change detection and subsequent movement to BigQuery.

Fivetran handles any and all schema drift as part of the incremental change detection automation

Starting the initial sync from SQL Server to BigQuery

After saving and testing the selections, I'm ready to sync the credit card transactions dataset from SQL Server to BigQuery. Fivetran moves the data to BigQuery, completes error checking and doesn't store any data in its service. It only retains a cursor to the sync point for the next incremental sync.

That’s it - I’m ready to start syncing my SQL Server credit card transactions dataset to BigQuery

This was a small dataset, just a single table and you can see that the initial sync was completed very quickly.

The initial sync was extremely fast for this dataset

Fivetran automatically sets up incremental CDC syncs for the connector, defaulting to every six hours. However, you can adjust the polling time from one minute to 24 hours, depending on your use case and the data freshness requirements for the downstream BigQuery ML fraud detection application.

Fivetran SaaS connectors support incremental syncs from every 1 minute up to every 24 hours

My SQL Server connector (automate_sqlserver_to_BigQuery_for_bqml_experiments) is now in the list with all other connectors, and I have access to all of those data sources now in BigQuery.

All connectors are active persisting changes to any and all of these datasets to BigQuery

Here’s the new credit card transactions dataset in BigQuery

I exported the credit card transactions dataset to my BigQuery project, which already contains various datasets like SAP, Salesforce and Google Analytics 4. Fivetran accurately replicates the source data in BigQuery, making it organized and ready for BigQuery ML experiments. A quick query on the new dataset shows what BigQuery ML will use for the fraud detection use case.

Fivetran’s automated data movement platform provides an ML-ready dataset in BigQuery

Building a fraud detection application in BigQuery ML

BigQuery ML lets you create and run machine learning (ML) models by using GoogleSQL queries. It also lets you access LLMs and Cloud AI APIs to perform artificial intelligence (AI) tasks like text generation or machine translation.

About the data

The dataset contains 284,807 European credit card transactions from September 2013, including 492 frauds (0.172%). It has 30 features: V1-V28 (PCA-transformed), 'Time' (seconds since first transaction), 'Amount' (transaction amount) and 'Class' (1 for fraud, 0 otherwise). The dataset was collected and analyzed by Worldline and the ULB Machine Learning Group ( http://mlg.ulb.ac.be) of ULB (Université Libre de Bruxelles for fraud detection research.

Creating the mdoel using BigQuery ML

In the Query window, type out the below query for model creation. Understand the key options with this statement. Explained in this link.

  • INPUT_LABEL_COLS indicate the prediction label
  • AUTO_CLASS_WEIGHTS are used for imbalanced datasets
  • MODEL_TYPE would indicate the algorithm used; in this case, logistic regression
  • DATA_SPLIT_METHOD indicates the split between the training and testing data

The model syntax is as follows:

CREATE OR REPLACE MODEL financial_fraud.ulb_fraud_detection
TRANSFORM(
   * EXCEPT(Amount),
   SAFE.LOG(Amount) AS log_amount
)
OPTIONS(
   INPUT_LABEL_COLS=['class'],
   AUTO_CLASS_WEIGHTS = TRUE,
   DATA_SPLIT_METHOD='seq',
   DATA_SPLIT_COL='Time',
   MODEL_TYPE='logistic_reg'
) AS


SELECT
*
FROM `BigQuery-public-data.ml_datasets.ulb_fraud_detection`

Once you run the above query, the model is created as shown below:

From the UI, we can evaluate the above model:

Using the model to predict fraud

Running the below query can help predict fraudulent transactions. The transaction at this time threshold  is known to be fraudulent. We are trying to evaluate that the prediction works.

SELECT Amount, predicted_class_probs, Class
FROM ML.PREDICT( MODEL financial_fraud.ulb_fraud_detection,
(SELECT * FROM `BigQuery-public-data.ml_datasets.ulb_fraud_detection` WHERE Time = 85285.0)
)

In this instance, we are displaying the amount with the associated probability of the label. The class column here indicates what the actual results were.

Get Started Now

Building a fraud detection ML app is easy with BigQuery, BigQuery ML and Fivetran. Fivetran standardizes and automates data movement to BigQuery, while BigQuery and BigQuery ML enable creating and running machine learning models using SQL queries. You can try Fivetran for free for 14-day free trial, and if you don't have BigQuery set up, Fivetran offers a managed BigQuery data warehouse service during the trial. Simply select “I don’t have my own warehouse” during the Fivetran trial setup. Fivetran is available on Google Cloud Marketplace.

About the authors

Kelly Kohlleffel leads the Fivetran Global Partner Sales Engineering organization, working with a broad ecosystem of technology partners and consulting services partners on modern data product and solution approaches. He also hosts the Fivetran Data Drip podcast where some of the brightest minds across the data community talk about their data journey, vision and challenges. Before Fivetran, he spent time at Hashmap and NTT DATA (data solution and service consulting), Hortonworks (in Hadoop-land) and Oracle. You can connect with Kelly on LinkedIn or follow him on Twitter.

Ankit Virmani is a data and ML specialist who focuses on developing and designing scalable, reliable and ethical AI/ML platforms for Google Cloud customers. He has over a decade of progressive work experience in the data and machine learning domain and is passionate about evangelizing the use of ethical and responsible AI.

[CTA_MODULE]

Start for free

Join the thousands of companies using Fivetran to centralize and transform their data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Product
Product

Automating credit card fraud detection with Google BigQuery ML and Fivetran

Automating credit card fraud detection with Google BigQuery ML and Fivetran

April 15, 2024
April 15, 2024
Automating credit card fraud detection with Google BigQuery ML and Fivetran
Exploring fraud detection with automated data integration in a cloud data warehouse: part two of two.

In this second part of our blog series, we'll demonstrate how to set up an automated fraud detection system using Google's BigQuery ML and Fivetran. This blog post demonstrates setting up an automated fraud detection system using BigQuery ML and Fivetran. We'll move a credit card transactions dataset from a source database to BigQuery using Fivetran, then run BigQuery ML classification experiments to predict fraudulent transactions. This solution enables centralized data, modernized infrastructure and scalable ML applications for combating credit card fraud, all without requiring setup or maintenance.

Set up BigQuery as a Fivetran destination

Fivetran accounts support multiple Google Cloud destinations. Setting up a BigQuery destination is quick and easy – just provide your BigQuery Project ID and grant the BigQuery User role to the Fivetran service account in the project. You can also choose your preferred Google Cloud processing region.

Setting up BigQuery as a Fivetran destination is quick and easy

Add a Fivetran to BigQuery connector

I already have multiple data sources flowing into BigQuery, including SAP, SQL Server and Oracle operational databases, Salesforce,  GA4, Kafka, S3 and Workday. Fivetran has over 500 source connectors out of the box across multiple categories, including databases, applications, file systems and event systems, and that number is growing by the day.

Fivetran supports over 500 data sources to automate data movement into BigQuery

I want to move a credit card transactions dataset from a Microsoft SQL Server database to BigQuery for use with BigQuery ML. I'll add a SQL Server connector using Fivetran, which offers various options for connecting and moving relational databases to Google Cloud, including SaaS standard connectors, HVA hybrid connectors and LDP. For this dataset, I'll use a Fivetran SaaS standard database connector with log-free change detection.

Microsoft SQL Server source options in Fivetran

Fivetran provides detailed and step by step documentation for quickly moving SQL Server data into BigQuery. The SQL Server Setup Guide outlines the steps and each source and destination page includes details on version support, configuration, features, limitations, sync overview, schema information and type transformations. I've linked the SQL Server source detail page for your reference.

Everything you need to know about connecting to SQL Server with Fivetran

The SQL Server setup page in Fivetran has a setup guide to help you quickly connect to SQL Server. You choose the destination schema name (e.g., "automate_sqlserver_to_BigQuery_for_bqml_experiments") and provide authentication details for the SQL Server database. Fivetran handles the initial sync and incremental updates using either CT/CDC or Fivetran Teleport (log-free change detection).

SQL Server setup page in Fivetran

Fivetran encrypts all data in motion using Transport Layer Security (TLS) for direct connections to SQL Server, ensuring encryption and authentication. Any data temporarily stored in the Fivetran service is encrypted with AES256.

Selecting the credit card transactions dataset to use with BigQuery ML

After successfully connecting to SQL Server, Fivetran fetches the available source schemas, tables and columns. Instead of syncing all 13 schemas, I'll select only the "financial_services" schema and the "credit_card_transactions_fraud_detection" table for my fraud detection use case, blocking the other schemas and tables from moving to BigQuery. Fivetran also offers column-level hashing for data privacy and anonymization of PII data while still allowing the hashed columns to be used in downstream ML workflows.

Dataset selection (credit_card_transactions_fraud_detection) along with schema, table and column blocking (plus column hashing)

Incremental changes and schema drift

Fivetran then needs to know how I want to handle incremental changes since the schema may change in the future. I’m going to “Allow all”, but I have a range of options here. Any and all DML and DDL changes are automatically captured by Fivetran and delivered to BigQuery - no coding is required and I can determine the polling frequency to the SQL Server source for the change detection and subsequent movement to BigQuery.

Fivetran handles any and all schema drift as part of the incremental change detection automation

Starting the initial sync from SQL Server to BigQuery

After saving and testing the selections, I'm ready to sync the credit card transactions dataset from SQL Server to BigQuery. Fivetran moves the data to BigQuery, completes error checking and doesn't store any data in its service. It only retains a cursor to the sync point for the next incremental sync.

That’s it - I’m ready to start syncing my SQL Server credit card transactions dataset to BigQuery

This was a small dataset, just a single table and you can see that the initial sync was completed very quickly.

The initial sync was extremely fast for this dataset

Fivetran automatically sets up incremental CDC syncs for the connector, defaulting to every six hours. However, you can adjust the polling time from one minute to 24 hours, depending on your use case and the data freshness requirements for the downstream BigQuery ML fraud detection application.

Fivetran SaaS connectors support incremental syncs from every 1 minute up to every 24 hours

My SQL Server connector (automate_sqlserver_to_BigQuery_for_bqml_experiments) is now in the list with all other connectors, and I have access to all of those data sources now in BigQuery.

All connectors are active persisting changes to any and all of these datasets to BigQuery

Here’s the new credit card transactions dataset in BigQuery

I exported the credit card transactions dataset to my BigQuery project, which already contains various datasets like SAP, Salesforce and Google Analytics 4. Fivetran accurately replicates the source data in BigQuery, making it organized and ready for BigQuery ML experiments. A quick query on the new dataset shows what BigQuery ML will use for the fraud detection use case.

Fivetran’s automated data movement platform provides an ML-ready dataset in BigQuery

Building a fraud detection application in BigQuery ML

BigQuery ML lets you create and run machine learning (ML) models by using GoogleSQL queries. It also lets you access LLMs and Cloud AI APIs to perform artificial intelligence (AI) tasks like text generation or machine translation.

About the data

The dataset contains 284,807 European credit card transactions from September 2013, including 492 frauds (0.172%). It has 30 features: V1-V28 (PCA-transformed), 'Time' (seconds since first transaction), 'Amount' (transaction amount) and 'Class' (1 for fraud, 0 otherwise). The dataset was collected and analyzed by Worldline and the ULB Machine Learning Group ( http://mlg.ulb.ac.be) of ULB (Université Libre de Bruxelles for fraud detection research.

Creating the mdoel using BigQuery ML

In the Query window, type out the below query for model creation. Understand the key options with this statement. Explained in this link.

  • INPUT_LABEL_COLS indicate the prediction label
  • AUTO_CLASS_WEIGHTS are used for imbalanced datasets
  • MODEL_TYPE would indicate the algorithm used; in this case, logistic regression
  • DATA_SPLIT_METHOD indicates the split between the training and testing data

The model syntax is as follows:

CREATE OR REPLACE MODEL financial_fraud.ulb_fraud_detection
TRANSFORM(
   * EXCEPT(Amount),
   SAFE.LOG(Amount) AS log_amount
)
OPTIONS(
   INPUT_LABEL_COLS=['class'],
   AUTO_CLASS_WEIGHTS = TRUE,
   DATA_SPLIT_METHOD='seq',
   DATA_SPLIT_COL='Time',
   MODEL_TYPE='logistic_reg'
) AS


SELECT
*
FROM `BigQuery-public-data.ml_datasets.ulb_fraud_detection`

Once you run the above query, the model is created as shown below:

From the UI, we can evaluate the above model:

Using the model to predict fraud

Running the below query can help predict fraudulent transactions. The transaction at this time threshold  is known to be fraudulent. We are trying to evaluate that the prediction works.

SELECT Amount, predicted_class_probs, Class
FROM ML.PREDICT( MODEL financial_fraud.ulb_fraud_detection,
(SELECT * FROM `BigQuery-public-data.ml_datasets.ulb_fraud_detection` WHERE Time = 85285.0)
)

In this instance, we are displaying the amount with the associated probability of the label. The class column here indicates what the actual results were.

Get Started Now

Building a fraud detection ML app is easy with BigQuery, BigQuery ML and Fivetran. Fivetran standardizes and automates data movement to BigQuery, while BigQuery and BigQuery ML enable creating and running machine learning models using SQL queries. You can try Fivetran for free for 14-day free trial, and if you don't have BigQuery set up, Fivetran offers a managed BigQuery data warehouse service during the trial. Simply select “I don’t have my own warehouse” during the Fivetran trial setup. Fivetran is available on Google Cloud Marketplace.

About the authors

Kelly Kohlleffel leads the Fivetran Global Partner Sales Engineering organization, working with a broad ecosystem of technology partners and consulting services partners on modern data product and solution approaches. He also hosts the Fivetran Data Drip podcast where some of the brightest minds across the data community talk about their data journey, vision and challenges. Before Fivetran, he spent time at Hashmap and NTT DATA (data solution and service consulting), Hortonworks (in Hadoop-land) and Oracle. You can connect with Kelly on LinkedIn or follow him on Twitter.

Ankit Virmani is a data and ML specialist who focuses on developing and designing scalable, reliable and ethical AI/ML platforms for Google Cloud customers. He has over a decade of progressive work experience in the data and machine learning domain and is passionate about evangelizing the use of ethical and responsible AI.

[CTA_MODULE]

How Fivetran and Google Cloud accelerate digital transformation for financial services
Download the ebook now

Related blog posts

Automated fraud detection with Fivetran and BigQuery
Product

Automated fraud detection with Fivetran and BigQuery

Read post
Use Fivetran, BigQuery and Looker to get a handle on NetSuite data
Product

Use Fivetran, BigQuery and Looker to get a handle on NetSuite data

Read post
Five ways Fivetran lays the foundation for machine learning

Five ways Fivetran lays the foundation for machine learning

Read post
Automated fraud detection with Fivetran and BigQuery
Blog

Automated fraud detection with Fivetran and BigQuery

Read post
Building a scalable data stack: Three lessons from Backcountry
Blog

Building a scalable data stack: Three lessons from Backcountry

Read post
Webinar: Rapid prototyping in retail using real-time data insights
Blog

Webinar: Rapid prototyping in retail using real-time data insights

Read post
How we use machine learning to improve our product
Blog

How we use machine learning to improve our product

Read post
Accelerate GenAI apps with Fivetran Google Cloud BQ and Vertex AI
Blog

Accelerate GenAI apps with Fivetran Google Cloud BQ and Vertex AI

Read post
Prompt engineering and the responsible use of generative AI
Blog

Prompt engineering and the responsible use of generative AI

Read post

Start for free

Join the thousands of companies using Fivetran to centralize and transform their data.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.