Snowflake と SageMaker Canvas のノーコード ML ワークフロー構築（第 2 部）

Part 1 covered the Snowflake database setup and established the foundational infrastructure for this no-code machine learning (ML) workflow.

Part 2 of this blog series covers complete data preparation and model building workflow using Amazon SageMaker Canvas, demonstrating how to connect directly to Snowflake data sources, transform and prepare data using Data Wrangler’s visual transformations, and build a fraud detection model using the XGBoost algorithm.

Amazon SageMaker Canvas is a visual, no-code machine learning service that enables business analysts and domain experts to build accurate ML models and generate predictions. Amazon SageMaker Canvas provides an intuitive interface for data preparation, model training, and prediction generation democratizing access to machine learning across organizations while maintaining enterprise security and governance.

Solution overview

This solution guides you through the complete workflow of preparing data and building a machine learning model using Amazon SageMaker Canvas, with direct integration to your Snowflake data warehouse.

Prerequisites

Complete Part 1 setting up your Snowflake environment before starting this post. You need the Snowflake account credentials and connection details from Part 1 to complete the steps in this post.

Amazon SageMaker Canvas setup

Open the AWS Management Console and search for Amazon SageMaker Canvas. Select it from the list, or press Enter.

Create your Amazon SageMaker domain

A domain provides the foundational organizational unit for your Amazon SageMaker environment. It serves as a dedicated workspace that houses user profiles, storage configurations, and security settings. Each domain delivers isolated resources and access controls for managing team collaboration and data governance.

• Navigate to environment configurations in the left pane and choose Domains.

• Choose Create domain.

• Choose Set up for single user (Quick setup) to automatically create both your domain and user profile.

Launch Amazon SageMaker Canvas

• Select Canvas from the left-hand pane.

• Choose the domain and user profile you created.

• Choose Open Canvas.

• Wait 3–5 minutes while Canvas prepares your workspace.

Figure 1: Amazon SageMaker Canvas workspace loading after opening it from the domain

Data Wrangler: ML data preparation

Amazon SageMaker Data Wrangler simplifies data preparation for machine learning workflows. With built-in transformations and an intuitive visual interface, Data Wrangler reduces the time traditionally spent on data preparation and analysis. For industries ranging from financial services to healthcare, this capability unlocks significant value so subject matter experts can directly prepare their data for analysis.

The integration with Snowflake reduces data movement challenges, so users can connect directly to their Snowflake data warehouses, transform the data within Canvas, and proceed straight to model building. This unified, no-code environment accelerates time-to-insight while maintaining data governance and security.

A. Data connection and initial setup

In this section, you connect Amazon SageMaker Canvas to a Snowflake data source.

• Navigate to Amazon SageMaker Canvas and choose Data Wrangler from the left navigation pane. Choose Import and prepare, then choose Tabular to work with structured datasets.

Figure 2: Amazon SageMaker Data Wrangler Import and prepare screen with Tabular selected

• Specify the source of your tabular dataset. From the data source menu, choose Snowflake as your connection type. Then choose Add Connection to establish the link between Amazon SageMaker Canvas and your Snowflake environment. With this integration, you can access your cloud data warehouse directly within Canvas. It avoids manual data exports and makes sure you are always working with the most current data in your Snowflake instance.

Figure 3: Selecting Snowflake as the data source and adding a connection in Data Wrangler

• In the Snowflake connection pop-up menu, provide the following:

A connection name.

• The Account ID, set to your Snowflake organization value, plus a hyphen, plus the Snowflake account ID.

• The username for the Snowflake account you set up earlier.

• The password that you set previously.

Figure 4: Snowflake connection dialog with connection name, account ID, username, and password fields

• After the connection is established, you will create card-level outlier thresholds to identify unusual spending patterns for each credit card and category combination. This helps the model detect when a transaction amount significantly deviates from a cardholder’s typical behavior. Copy the SQL query to prepare the fraud detection dataset.

select
CC_NUM,
CATEGORY,
avg(AMT) + (3* stddev_pop(AMT)) as amt_outlier,
case when sum(is_fraud)>1 then 1 else 0 end as fraud_history
from
FRAUD.PUBLIC.FRAUD_TABLE
where trans_date_trans_time<'2020-12-01'
group by
CC_NUM,
CATEGORY

• Run the query:

Choose Edit in SQL in the top right corner to open the SQL editor.

• Paste the SQL query into the editor.

• Choose Run SQL to run and preview results.

• Choose Import to bring the dataset into your Canvas workspace.

Figure 5: SQL editor in Data Wrangler showing the outlier-threshold query results ready to import

B. Data enrichment

In the following steps, you will enrich the dataset with transaction-level features and merchant-specific fraud indicators.

• Choose Data flow to add more data. In the top right corner, choose Add Data, and then choose Tabular.

Figure 6: Data flow canvas with the Add Data and Tabular options for adding a second source

• Choose the data source as Snowflake and the existing connection you created before.

Figure 7: Selecting the existing Snowflake connection for the second data source

• In the Edit SQL window, paste the SQL query. This query provides temporal patterns (hour of day, day of week), demographic information (age categories), and merchant-level outlier thresholds, all critical signals for fraud detection. Choose Run SQL, then choose Import.

select
    t.MERCHANT,
    t.CC_NUM,
    'H'||extract(hour from t.TRANS_DATE_TRANS_TIME) as TRANS_HOUR,
    'D'||extract(dow from t.TRANS_DATE_TRANS_TIME) as TRANS_DOW,
    t.state,
    t.gender,
    'A'||FLOOR(DATEDIFF(YEAR, DATEADD(DAY, 1, CAST(t.dob AS DATE)), CAST(t.TRANS_DATE_TRANS_TIME AS DATE)) / 5) * 5 AS age_category,
    t.CATEGORY,
    t.IS_FRAUD,
    t.AMT,
    m.merchant_amt_outlier,
    m.merchant_fraud_history
from
    FRAUD.PUBLIC.FRAUD_TABLE t
left join (
    select
        MERCHANT,
        avg(AMT) + (3* stddev_pop(AMT)) as merchant_amt_outlier,
        case when sum(is_fraud)>1 then 1 else 0 end as merchant_fraud_history
    from
        FRAUD.PUBLIC.FRAUD_TABLE
    where trans_date_trans_time<'2020-12-01'
    group by MERCHANT
) m on t.MERCHANT = m.MERCHANT
where t.trans_date_trans_time<'2020-12-01'

Figure 8: Data Wrangler preview of the enrichment query with temporal, demographic, and merchant features

C. Data transformation

In this section, you will join the data together.

• On the Data flow screen, you see the two data sources imported from Snowflake. Choose the first data type and choose the plus icon next to it. Choose Combine data, then Join. When the right panel opens, choose the second data type from the data flow to join.

Figure 9: Data flow showing the Combine data and Join options between the two Snowflake sources

• Choose Left outer join for the Join type. Add a Join key and choose Category as the left and right join key.

Figure 10: Join configuration with Left outer join type and Category as the join key

• Choose Preview to view the joined data. Choose Add to proceed.

Figure 11: Preview of the joined dataset before adding it to the data flow

Next, you will create outlier features and remove sensitive columns.

• Return to the Data flow screen, choose the join, choose the plus sign (+) next to it, and then choose Add transform.

Figure 12: Adding a transform to the join node in the data flow

• After the interface opens, choose Add transform in the right-hand panel, and then choose Custom Formula.

Figure 13: Choosing Custom Formula in the Add transform panel

• This option gives you the flexibility to add columns using a Spark SQL expression. Copy and paste the following Formula on the Home tab. Enter the output column as CC_FLAG and choose Add.

CASE WHEN AMT > AMT_OUTLIER THEN 1 ELSE 0 END

Figure 14: Custom formula creating the CC_FLAG column from the card outlier threshold

• To add another column, repeat the same steps to enter a custom formula, then copy and paste this query and name the output column as MERCHANT_AMT_FLAG.

CASE WHEN AMT > MERCHANT_AMT_OUTLIER THEN 1 ELSE 0 END

Figure 15: Custom formula creating the MERCHANT_AMT_FLAG column from the merchant outlier threshold

• To make sure the model remains free of sensitive information such as card numbers, merchant names, and outlier amounts tied to cards or merchants, remove these columns using built-in transformation.

Select Add transform and choose Manage Columns

• Select the Transform type to Drop column

• Select the columns you want to remove from your dataset

• Choose Add to apply the transformation.

CC_NUM.

• AMT_OUTLIER.

• MERCHANT.

• MERCHANT_AMT_OUTLIER.

Figure 16: Manage Columns transform dropping sensitive columns from the dataset

D. Quality analysis and model export

With data preparation complete, you will run a quality analysis report to get insights into the data. The insights report identifies common data issues, such as target leakage or class imbalance, helping users address them early in the workflow.

• To initiate the analysis, choose the Analyses tab. In the right-hand panel, choose Data Quality and Insights Report from the Analysis type list.

• Choose IS_FRAUD as the target column. This tells Canvas which variable you want to predict. Then choose the Classification option under Problem type. The Data size should remain as Sampled Dataset. Finally, choose Create to launch the analysis.

Figure 17: Data Quality and Insights Report configuration with IS_FRAUD target and Classification problem type

• Within a few minutes, a detailed analysis report will be created which includes a quick summary of the data, feature summary, duplicate rows, anomalous samples and much more. In the Quick model section, review the accuracy metrics in the training and validation datasets. A confusion matrix follows the accuracy statistics. The idea is to use this report after any data engineering to observe how it impacts model quality.