Build a Credit Union Data Pipeline

Load, transform, and analyze credit union data in Snowflake using SQL.

Introduction

30 Second Summary

Every time you check your bank balance or transfer money, a data pipeline is working behind the scenes to move and organize that information. Data engineers are the people who build and maintain these pipelines.

In this project, you will build a data pipeline for a fictional credit union using Snowflake. You will load raw member data, transform it with SQL, and create analytics-ready views for a reporting team.

What You'll Build

You will create an ELT data pipeline in Snowflake that takes raw credit union member data and transforms it into analytics-ready views using SQL.

By the end of this project, you'll have:

A Snowflake database with separate layers for raw data and transformed analytics.
Three raw data tables storing member, account, and transaction records loaded with sample data.
Two analytics views that join and aggregate the raw data into reporting-ready summaries.
A loan risk classification view that categorizes members by risk level using advanced SQL (Secret Mission).

Are there any prerequisites?

None. This project starts from scratch with a free Snowflake trial account and covers SQL basics along the way. No credit card is required.

Set Up Your Snowflake Trial Account

Before you can build a data pipeline, you need a place to build it. Snowflake is a cloud data platform that runs entirely in your browser. No downloads or installations required.

In this step, you will create a free Snowflake trial account and explore the web interface. You will also set up a SQL worksheet where you will write all of your code for this project.

In this step, get ready to:

Sign up for a Snowflake free trial account.
Explore the Snowsight web interface.
Create a SQL worksheet for writing queries.

Sign up for a Snowflake trial account

The Snowflake trial gives you 30 days and $400 in free credits. No credit card is required.

Open your web browser and go to signup.snowflake.com.
Fill in your first name, last name, and email address.
For Country, select the country you are located in.
For Why are you signing up?, select Personal learning and development.
Click Continue to move to the next page.
Select Enterprise as the Snowflake edition.
Select AWS as the cloud provider.
Choose the region closest to your location.
Check the terms checkbox, then click GET STARTED.
Check your email inbox for an activation email from Snowflake. This can take a few minutes to arrive.

Why Enterprise edition?

Enterprise gives you access to features like multi-cluster warehouses and Time Travel. These are useful for learning Snowflake's full capabilities, and the trial covers the cost.

The activation email contains a link you will use to set your password and log in for the first time.

Don't see the activation email?

Check your spam or junk folder.
Make sure you entered your email address correctly on the signup form.
Wait a few minutes and refresh your inbox. Snowflake emails can take up to 5 minutes to arrive.
I'm not receiving my Snowflake activation email. What should I check?

Log in and explore Snowsight

Snowsight is Snowflake's web interface. Everything you do in this project happens inside Snowsight.

Click the activation link in the email from Snowflake.
Create a username and password when prompted.
Log in to Snowsight.

You will land on the Snowsight home page. Take a moment to look at the left-side navigation menu.

What are the key sections in Snowsight?

Projects is where you write and run SQL queries.
Data is where you browse databases, schemas, and tables.
Admin is where you manage warehouses, users, and account settings.

Create your first SQL worksheet

Snowflake is transitioning its SQL editor from Worksheets to Workspaces. Your account may show either one. Both work the same way for writing and running SQL.

I see Workspaces

Most new accounts default to Workspaces. Follow these steps to create a SQL file.

In the left navigation menu, click Projects.
Click Workspaces.
You will see a default workspace called My Workspace. Click into it.
Click the + button to create a new file.
Select SQL Worksheet from the options.
Click the file name tab at the top of the editor to rename it.
Type Credit Union Pipeline as the new name and press Enter.

You should see a blank SQL editor with Credit Union Pipeline as the file name.

I see Worksheets

Some accounts still show legacy Worksheets. Follow these steps to create a SQL worksheet.

In the left navigation menu, click Projects.
Click Worksheets.
Click the blue + button in the top right corner.
Select SQL Worksheet.
Click the worksheet tab name at the top of the editor to rename it.
Type Credit Union Pipeline as the new name and press Enter.

You should see a blank SQL editor with Credit Union Pipeline as the worksheet name.

Can't find where to create a SQL file?

Make sure you are in the Projects section, not Data or Admin.
Look for either Workspaces or Worksheets under Projects in the left menu. Your account will show one or the other.
I can't find where to create a new SQL worksheet in Snowsight. Can you help me troubleshoot?

Your Snowflake environment is ready to go. Next up, you will create your database and warehouse to organize and power your data pipeline.

Create Your Database and Warehouse

Your Snowflake trial account is set up and you have a SQL worksheet ready to go. Now it's time to build the foundation of your data pipeline.

Every data pipeline needs organized storage and compute power. In this step, you'll create a database with two schemas for separating raw data from analytics-ready data. You'll also create a virtual warehouse that powers all of your queries.

In this step, get ready to:

Set your role and create a database with two schemas.
Create a virtual warehouse for running queries.
Verify everything was created correctly with SHOW commands.

Set up your role and create the database

Before creating any objects in Snowflake, you need to tell it which role you're using. Roles control what you're allowed to do. Your trial account comes with a built-in role called ACCOUNTADMIN that has full privileges.

In your SQL worksheet (the one you named "Credit Union Pipeline"), set your role and create the database by running the following statements one at a time.
Place your cursor on a statement and press Cmd+Return (macOS) or Ctrl+Enter (Windows) to run it.

-- Set the role
USE ROLE ACCOUNTADMIN;

-- Create the database
CREATE OR REPLACE DATABASE credit_union_db
  COMMENT = 'Summit Federal Credit Union data pipeline';

What does this code do?

USE ROLE ACCOUNTADMIN activates the admin role for your session. This gives you permission to create databases, schemas, and warehouses.
CREATE OR REPLACE DATABASE creates a new database called credit_union_db. This database holds all of the credit union's data.
The COMMENT parameter adds a human-readable description. This helps other team members understand what the database is for.

Run each statement one at a time. After each one, check the results panel at the bottom of the worksheet.
You should see a confirmation message like Database CREDIT_UNION_DB successfully created after the second statement.

Seeing an error after running a statement?

Make sure your cursor is placed inside the statement you want to run. If the cursor is between two statements, Snowflake may try to run the wrong one.
Check that you have the semicolons at the end of each statement. Snowflake uses semicolons to mark where one statement ends.
Make sure you ran USE ROLE ACCOUNTADMIN first. Without this, you may not have permission to create a database.

Help me debug my SQL statement in Snowflake

Now that the database exists, you need a way to organize the data inside it. This is where schemas come in. A schema is like a folder inside a database that groups related tables together.

You'll create two schemas to separate your data into layers. The raw schema holds data exactly as it comes from source systems. The analytics schema holds transformed, analytics-ready data.

Create both schemas by running these statements one at a time:

-- Create schemas for raw and analytics layers
CREATE OR REPLACE SCHEMA credit_union_db.raw
  COMMENT = 'Raw data ingested from source systems';

CREATE OR REPLACE SCHEMA credit_union_db.analytics
  COMMENT = 'Transformed and analytics-ready data';

Why two schemas instead of one?

This two-layer pattern is standard in data engineering. Raw data lands in one schema untouched, and transformed data lives in another. If a transformation goes wrong, you always have the original data to fall back on.

This matches the ELT pattern (Extract, Load, Transform) that Snowflake is designed for. Load data into raw first, then transform it into analytics.

After running each statement, you should see a confirmation message like Schema RAW successfully created in the results panel.

Schema creation failed?

Check that you spelled the database name credit_union_db exactly the same as in the CREATE DATABASE statement above.
Make sure the database was created successfully before running the schema statements. You can check by scrolling up to your CREATE OR REPLACE DATABASE statement, placing your cursor on it, and running it again.

Help me fix my schema creation in Snowflake

Create a virtual warehouse

In Snowflake, storage and compute are completely separate. The database you just created handles storage. A virtual warehouse handles compute. It provides the processing power for loading data and running queries.

Create your warehouse and activate it by running these statements one at a time:

-- Create a virtual warehouse
CREATE OR REPLACE WAREHOUSE credit_union_wh
  WITH WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Warehouse for credit union data pipeline';

-- Use the warehouse
USE WAREHOUSE credit_union_wh;

What do these warehouse settings mean?

WAREHOUSE_SIZE = 'X-SMALL' is the smallest (and cheapest) size. It's more than enough for this project.
AUTO_SUSPEND = 60 tells Snowflake to automatically pause the warehouse after 60 seconds of no activity. This saves your trial credits.
AUTO_RESUME = TRUE means the warehouse starts back up automatically when you run your next query. You never need to manually restart it.
INITIALLY_SUSPENDED = TRUE creates the warehouse in a paused state so it doesn't consume credits until you actually need it.
USE WAREHOUSE credit_union_wh sets this warehouse as the active compute for your session. All queries you run from this point forward use this warehouse.

After running the CREATE statement, you should see Warehouse CREDIT_UNION_WH successfully created in the results panel.
After running the USE statement, you should see Statement executed successfully.

Warehouse not creating?

Double check that you're using the ACCOUNTADMIN role. Scroll up to your USE ROLE ACCOUNTADMIN statement and run it again to be sure.
Make sure X-SMALL is wrapped in single quotes. Without the quotes, Snowflake won't recognize the warehouse size.

Help me troubleshoot my warehouse creation in Snowflake

Verify your setup

Snowflake has built-in SHOW commands that list all objects of a certain type. These are useful for confirming that everything you just created actually exists.

Verify your database, schemas, and warehouse by running these three statements one at a time:

-- Verify setup
SHOW DATABASES LIKE 'CREDIT_UNION_DB';
SHOW SCHEMAS IN DATABASE credit_union_db;
SHOW WAREHOUSES LIKE 'CREDIT_UNION_WH';

What should I see?

SHOW DATABASES returns a row with your CREDIT_UNION_DB database and the comment you added.
SHOW SCHEMAS returns rows for RAW, ANALYTICS, PUBLIC, and INFORMATION_SCHEMA. Snowflake automatically creates the last two in every new database.
SHOW WAREHOUSES returns a row with your CREDIT_UNION_WH warehouse showing its size, auto-suspend setting, and current state.

SHOW returned empty results?

Check the spelling in your SHOW command matches the exact name you used when creating the object. For example, CREDIT_UNION_DB must match the name in your CREATE OR REPLACE DATABASE statement.
Make sure your role is still set to ACCOUNTADMIN. If your session role changed, you may not have visibility into the objects you created.

Help me verify my Snowflake database and warehouse setup

Awesome, I've got everything!

Double check you've saved your worksheet. You can use Cmd+S (macOS) or Ctrl+S (Windows) to save the worksheet so you don't lose your SQL.

I'd like to double check the full code

Here is the complete SQL you should have in your worksheet so far. Compare your worksheet against this code.

-- ============================================
-- STEP 2: Create Database and Warehouse
-- ============================================

-- Set the role
USE ROLE ACCOUNTADMIN;

-- Create the database
CREATE OR REPLACE DATABASE credit_union_db
  COMMENT = 'Summit Federal Credit Union data pipeline';

-- Create schemas for raw and analytics layers
CREATE OR REPLACE SCHEMA credit_union_db.raw
  COMMENT = 'Raw data ingested from source systems';

CREATE OR REPLACE SCHEMA credit_union_db.analytics
  COMMENT = 'Transformed and analytics-ready data';

-- Create a virtual warehouse
CREATE OR REPLACE WAREHOUSE credit_union_wh
  WITH WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  COMMENT = 'Warehouse for credit union data pipeline';

-- Use the warehouse
USE WAREHOUSE credit_union_wh;

-- Verify setup
SHOW DATABASES LIKE 'CREDIT_UNION_DB';
SHOW SCHEMAS IN DATABASE credit_union_db;
SHOW WAREHOUSES LIKE 'CREDIT_UNION_WH';

Your database, schemas, and warehouse are all set up. Next up, you'll create tables in the raw schema and load them with sample credit union member data.

Create Raw Data Tables and Load Sample Data

Your database, schemas, and warehouse are all set up. Now it's time to build the actual tables that hold Summit Federal Credit Union's data.

You'll create three tables in the raw schema: members, accounts, and transactions. Then you'll load each table with sample data using INSERT INTO statements. These three tables form the raw data layer of your pipeline.

In this step, get ready to:

Create the members, accounts, and transactions tables in the raw schema.
Load sample data representing credit union members, their accounts, and their transactions.
Verify that all data loaded correctly.

Create the members table

Every credit union starts with its members. The members table stores core information like names, emails, join dates, and membership types.

In your Credit Union Pipeline worksheet, set your database context and create the members table by running this code:

USE DATABASE credit_union_db;
USE SCHEMA raw;

-- Create members table
CREATE OR REPLACE TABLE members (
  member_id INTEGER,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  phone VARCHAR(20),
  join_date DATE,
  membership_type VARCHAR(20),
  status VARCHAR(10)
);

What does this code do?

USE DATABASE and USE SCHEMA tell Snowflake which database and schema you're working in. Every table you create after this lands in credit_union_db.raw.
CREATE OR REPLACE TABLE creates a new table (or replaces it if one already exists with the same name). Each column has a name and a data type like VARCHAR(50) for text or DATE for dates.
membership_type and status let you categorize members (Premium, Standard, Basic) and track whether they're Active or Inactive.

Pro tip: running multiple statements

You can highlight all the statements above and press Cmd+Return (macOS) or Ctrl+Enter (Windows) to run them all at once. Snowflake executes each statement in order.

Check the results panel at the bottom of your worksheet. You should see a message saying Table MEMBERS successfully created.

Seeing an error instead?

If you see Object does not exist, make sure you created the credit_union_db database and raw schema in Step 2.
If you see a syntax error, check that every column definition ends with a comma except the last one (status).
My CREATE TABLE statement isn't working. What should I check?

The table exists but it's empty. Time to fill it with sample member data.

Load 10 sample members into the table by running this INSERT INTO statement:

-- Insert sample members
INSERT INTO members VALUES
  (1, 'Maria', 'Garcia', 'maria.garcia@email.com', '555-0101', '2019-03-15', 'Premium', 'Active'),
  (2, 'James', 'Wilson', 'james.wilson@email.com', '555-0102', '2020-07-22', 'Standard', 'Active'),
  (3, 'Sarah', 'Chen', 'sarah.chen@email.com', '555-0103', '2018-01-10', 'Premium', 'Active'),
  (4, 'Robert', 'Johnson', 'robert.j@email.com', '555-0104', '2021-11-05', 'Standard', 'Active'),
  (5, 'Emily', 'Davis', 'emily.davis@email.com', '555-0105', '2017-06-30', 'Premium', 'Active'),
  (6, 'Michael', 'Brown', 'michael.b@email.com', '555-0106', '2022-02-14', 'Basic', 'Active'),
  (7, 'Lisa', 'Martinez', 'lisa.m@email.com', '555-0107', '2019-09-01', 'Standard', 'Inactive'),
  (8, 'David', 'Taylor', 'david.t@email.com', '555-0108', '2023-04-20', 'Basic', 'Active'),
  (9, 'Jennifer', 'Anderson', 'jen.a@email.com', '555-0109', '2016-12-01', 'Premium', 'Active'),
  (10, 'Kevin', 'Thomas', 'kevin.t@email.com', '555-0110', '2020-08-15', 'Standard', 'Inactive');

What's in this sample data?

These 10 members represent a realistic mix of credit union customers. Four are Premium members, three are Standard, and two are Basic. Two members (Lisa and Kevin) have an Inactive status. Join dates span from 2016 to 2023, giving you a range of membership tenures to work with later.

Check the results panel. You should see number of rows inserted: 10.

Seeing fewer rows inserted?

Make sure you highlighted the entire INSERT INTO statement, including the last row ending with a semicolon. If you only ran a partial selection, Snowflake inserts only the highlighted rows.
My INSERT statement only loaded some of the rows. How do I fix this?

Create the accounts table

Each credit union member can have one or more accounts. The accounts table links back to the members table through the member_id column, creating a relationship between the two tables.

Create the accounts table by running this code:

-- Create accounts table
CREATE OR REPLACE TABLE accounts (
  account_id INTEGER,
  member_id INTEGER,
  account_type VARCHAR(20),
  balance DECIMAL(12, 2),
  opened_date DATE,
  interest_rate DECIMAL(5, 4)
);

What does this code do?

member_id connects each account to its owner in the members table. This is how you'll join the two tables later.
DECIMAL(12, 2) stores the balance with up to 12 digits and 2 decimal places. DECIMAL(5, 4) stores interest rates with 4 decimal places (e.g., 0.0250 for 2.5%).

Check the results panel. You should see Table ACCOUNTS successfully created.

Table not created?

Make sure you ran the USE DATABASE credit_union_db; and USE SCHEMA raw; commands earlier in this step. Without them, Snowflake doesn't know where to create the table.
My CREATE TABLE for accounts is failing. What should I check?

Load 15 sample accounts by running this INSERT INTO statement:

-- Insert sample accounts
INSERT INTO accounts VALUES
  (101, 1, 'Checking', 5420.50, '2019-03-15', 0.0010),
  (102, 1, 'Savings', 15780.00, '2019-03-15', 0.0250),
  (103, 2, 'Checking', 2340.75, '2020-07-22', 0.0010),
  (104, 2, 'Savings', 8900.00, '2020-08-01', 0.0200),
  (105, 3, 'Checking', 12500.00, '2018-01-10', 0.0010),
  (106, 3, 'Money Market', 45000.00, '2018-06-15', 0.0350),
  (107, 4, 'Checking', 1890.25, '2021-11-05', 0.0010),
  (108, 5, 'Savings', 32000.00, '2017-06-30', 0.0250),
  (109, 5, 'Checking', 7650.00, '2017-06-30', 0.0010),
  (110, 5, 'Money Market', 67500.00, '2018-01-15', 0.0350),
  (111, 6, 'Checking', 980.50, '2022-02-14', 0.0005),
  (112, 7, 'Savings', 4200.00, '2019-09-01', 0.0200),
  (113, 8, 'Checking', 1550.00, '2023-04-20', 0.0005),
  (114, 9, 'Savings', 28750.00, '2016-12-01', 0.0250),
  (115, 9, 'Checking', 9800.00, '2016-12-01', 0.0010);

What's in this sample data?

These 15 accounts include three types: Checking, Savings, and Money Market. Notice that some members have multiple accounts. For example, member 5 (Emily Davis) has three accounts. The member_id column is what connects each account back to its owner in the members table.

Check the results panel. You should see number of rows inserted: 15.

Create the transactions table

The transactions table records every deposit, withdrawal, transfer, and payment across all accounts. This is the most detailed table and the foundation for the analytics views you'll build later.

Create the transactions table by running this code:

-- Create transactions table
CREATE OR REPLACE TABLE transactions (
  transaction_id INTEGER,
  account_id INTEGER,
  transaction_date DATE,
  transaction_type VARCHAR(20),
  amount DECIMAL(12, 2),
  description VARCHAR(100)
);

What does this code do?

account_id links each transaction to an account. Combined with the accounts table's member_id, you can trace any transaction all the way back to the member who made it.
transaction_type categorizes activity as Deposit, Withdrawal, Transfer, or Payment. You'll use this column to calculate totals by category in your analytics views.

Check the results panel. You should see Table TRANSACTIONS successfully created.

Table not created?

Check that every column definition has a comma after it except the last one (description). A missing or extra comma is the most common cause of syntax errors in CREATE TABLE statements.
My transactions table isn't being created. What's wrong?

Load 30 sample transactions by running this INSERT INTO statement:

-- Insert sample transactions
INSERT INTO transactions VALUES
  (1001, 101, '2024-01-05', 'Deposit', 3200.00, 'Payroll deposit'),
  (1002, 101, '2024-01-08', 'Withdrawal', 500.00, 'ATM withdrawal'),
  (1003, 101, '2024-01-15', 'Payment', 1200.00, 'Rent payment'),
  (1004, 102, '2024-01-10', 'Deposit', 1000.00, 'Savings transfer'),
  (1005, 103, '2024-01-05', 'Deposit', 2800.00, 'Payroll deposit'),
  (1006, 103, '2024-01-12', 'Withdrawal', 200.00, 'ATM withdrawal'),
  (1007, 103, '2024-01-20', 'Payment', 850.00, 'Utility bills'),
  (1008, 104, '2024-01-15', 'Deposit', 500.00, 'Monthly savings'),
  (1009, 105, '2024-01-03', 'Deposit', 5500.00, 'Payroll deposit'),
  (1010, 105, '2024-01-10', 'Transfer', 2000.00, 'Transfer to money market'),
  (1011, 106, '2024-01-10', 'Deposit', 2000.00, 'Transfer from checking'),
  (1012, 107, '2024-01-05', 'Deposit', 1800.00, 'Payroll deposit'),
  (1013, 107, '2024-01-18', 'Payment', 950.00, 'Car payment'),
  (1014, 108, '2024-01-20', 'Deposit', 2500.00, 'Bonus deposit'),
  (1015, 109, '2024-01-05', 'Deposit', 4200.00, 'Payroll deposit'),
  (1016, 109, '2024-01-22', 'Withdrawal', 300.00, 'ATM withdrawal'),
  (1017, 110, '2024-01-15', 'Deposit', 5000.00, 'Investment transfer'),
  (1018, 111, '2024-01-05', 'Deposit', 1500.00, 'Payroll deposit'),
  (1019, 111, '2024-01-25', 'Withdrawal', 400.00, 'ATM withdrawal'),
  (1020, 113, '2024-01-05', 'Deposit', 1600.00, 'Payroll deposit'),
  (1021, 114, '2024-01-10', 'Deposit', 3000.00, 'Investment return'),
  (1022, 115, '2024-01-05', 'Deposit', 4800.00, 'Payroll deposit'),
  (1023, 115, '2024-01-15', 'Payment', 1500.00, 'Mortgage payment'),
  (1024, 101, '2024-02-05', 'Deposit', 3200.00, 'Payroll deposit'),
  (1025, 103, '2024-02-05', 'Deposit', 2800.00, 'Payroll deposit'),
  (1026, 105, '2024-02-03', 'Deposit', 5500.00, 'Payroll deposit'),
  (1027, 107, '2024-02-05', 'Deposit', 1800.00, 'Payroll deposit'),
  (1028, 109, '2024-02-05', 'Deposit', 4200.00, 'Payroll deposit'),
  (1029, 111, '2024-02-05', 'Deposit', 1500.00, 'Payroll deposit'),
  (1030, 115, '2024-02-05', 'Deposit', 4800.00, 'Payroll deposit');

What's in this sample data?

These 30 transactions span January and February 2024. They include payroll deposits, ATM withdrawals, bill payments, and transfers between accounts. The February transactions are mostly payroll deposits, giving you two months of data to compare in your analytics views.

Check the results panel. You should see number of rows inserted: 30.

Don't see 30 rows inserted?

Highlight the entire INSERT INTO transactions VALUES statement from the first line to the final semicolon. Partial selections cause fewer rows to be inserted.
If you see a data type error, check that all dates are in 'YYYY-MM-DD' format with single quotes around them.
My transactions INSERT statement is failing. What should I check?

Why INSERT INTO instead of loading from files?

In production, data engineers typically load data from files using Snowflake's COPY INTO command, which pulls data from cloud storage like S3 or Azure Blob Storage. INSERT INTO keeps this project focused on SQL and Snowflake concepts without requiring cloud storage setup.

Awesome, I've got everything!

Double check you've run all the statements in your worksheet. You should have three tables (members, accounts, transactions) in the raw schema.

I'd like to double check the full code

Here is the complete code for this step. Compare it against what you have in your worksheet.

-- ============================================
-- STEP 3: Create Raw Data Tables and Load Data
-- ============================================

USE DATABASE credit_union_db;
USE SCHEMA raw;

-- Create members table
CREATE OR REPLACE TABLE members (
  member_id INTEGER,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  phone VARCHAR(20),
  join_date DATE,
  membership_type VARCHAR(20),
  status VARCHAR(10)
);

-- Insert sample members
INSERT INTO members VALUES
  (1, 'Maria', 'Garcia', 'maria.garcia@email.com', '555-0101', '2019-03-15', 'Premium', 'Active'),
  (2, 'James', 'Wilson', 'james.wilson@email.com', '555-0102', '2020-07-22', 'Standard', 'Active'),
  (3, 'Sarah', 'Chen', 'sarah.chen@email.com', '555-0103', '2018-01-10', 'Premium', 'Active'),
  (4, 'Robert', 'Johnson', 'robert.j@email.com', '555-0104', '2021-11-05', 'Standard', 'Active'),
  (5, 'Emily', 'Davis', 'emily.davis@email.com', '555-0105', '2017-06-30', 'Premium', 'Active'),
  (6, 'Michael', 'Brown', 'michael.b@email.com', '555-0106', '2022-02-14', 'Basic', 'Active'),
  (7, 'Lisa', 'Martinez', 'lisa.m@email.com', '555-0107', '2019-09-01', 'Standard', 'Inactive'),
  (8, 'David', 'Taylor', 'david.t@email.com', '555-0108', '2023-04-20', 'Basic', 'Active'),
  (9, 'Jennifer', 'Anderson', 'jen.a@email.com', '555-0109', '2016-12-01', 'Premium', 'Active'),
  (10, 'Kevin', 'Thomas', 'kevin.t@email.com', '555-0110', '2020-08-15', 'Standard', 'Inactive');

-- Create accounts table
CREATE OR REPLACE TABLE accounts (
  account_id INTEGER,
  member_id INTEGER,
  account_type VARCHAR(20),
  balance DECIMAL(12, 2),
  opened_date DATE,
  interest_rate DECIMAL(5, 4)
);

-- Insert sample accounts
INSERT INTO accounts VALUES
  (101, 1, 'Checking', 5420.50, '2019-03-15', 0.0010),
  (102, 1, 'Savings', 15780.00, '2019-03-15', 0.0250),
  (103, 2, 'Checking', 2340.75, '2020-07-22', 0.0010),
  (104, 2, 'Savings', 8900.00, '2020-08-01', 0.0200),
  (105, 3, 'Checking', 12500.00, '2018-01-10', 0.0010),
  (106, 3, 'Money Market', 45000.00, '2018-06-15', 0.0350),
  (107, 4, 'Checking', 1890.25, '2021-11-05', 0.0010),
  (108, 5, 'Savings', 32000.00, '2017-06-30', 0.0250),
  (109, 5, 'Checking', 7650.00, '2017-06-30', 0.0010),
  (110, 5, 'Money Market', 67500.00, '2018-01-15', 0.0350),
  (111, 6, 'Checking', 980.50, '2022-02-14', 0.0005),
  (112, 7, 'Savings', 4200.00, '2019-09-01', 0.0200),
  (113, 8, 'Checking', 1550.00, '2023-04-20', 0.0005),
  (114, 9, 'Savings', 28750.00, '2016-12-01', 0.0250),
  (115, 9, 'Checking', 9800.00, '2016-12-01', 0.0010);

-- Create transactions table
CREATE OR REPLACE TABLE transactions (
  transaction_id INTEGER,
  account_id INTEGER,
  transaction_date DATE,
  transaction_type VARCHAR(20),
  amount DECIMAL(12, 2),
  description VARCHAR(100)
);

-- Insert sample transactions
INSERT INTO transactions VALUES
  (1001, 101, '2024-01-05', 'Deposit', 3200.00, 'Payroll deposit'),
  (1002, 101, '2024-01-08', 'Withdrawal', 500.00, 'ATM withdrawal'),
  (1003, 101, '2024-01-15', 'Payment', 1200.00, 'Rent payment'),
  (1004, 102, '2024-01-10', 'Deposit', 1000.00, 'Savings transfer'),
  (1005, 103, '2024-01-05', 'Deposit', 2800.00, 'Payroll deposit'),
  (1006, 103, '2024-01-12', 'Withdrawal', 200.00, 'ATM withdrawal'),
  (1007, 103, '2024-01-20', 'Payment', 850.00, 'Utility bills'),
  (1008, 104, '2024-01-15', 'Deposit', 500.00, 'Monthly savings'),
  (1009, 105, '2024-01-03', 'Deposit', 5500.00, 'Payroll deposit'),
  (1010, 105, '2024-01-10', 'Transfer', 2000.00, 'Transfer to money market'),
  (1011, 106, '2024-01-10', 'Deposit', 2000.00, 'Transfer from checking'),
  (1012, 107, '2024-01-05', 'Deposit', 1800.00, 'Payroll deposit'),
  (1013, 107, '2024-01-18', 'Payment', 950.00, 'Car payment'),
  (1014, 108, '2024-01-20', 'Deposit', 2500.00, 'Bonus deposit'),
  (1015, 109, '2024-01-05', 'Deposit', 4200.00, 'Payroll deposit'),
  (1016, 109, '2024-01-22', 'Withdrawal', 300.00, 'ATM withdrawal'),
  (1017, 110, '2024-01-15', 'Deposit', 5000.00, 'Investment transfer'),
  (1018, 111, '2024-01-05', 'Deposit', 1500.00, 'Payroll deposit'),
  (1019, 111, '2024-01-25', 'Withdrawal', 400.00, 'ATM withdrawal'),
  (1020, 113, '2024-01-05', 'Deposit', 1600.00, 'Payroll deposit'),
  (1021, 114, '2024-01-10', 'Deposit', 3000.00, 'Investment return'),
  (1022, 115, '2024-01-05', 'Deposit', 4800.00, 'Payroll deposit'),
  (1023, 115, '2024-01-15', 'Payment', 1500.00, 'Mortgage payment'),
  (1024, 101, '2024-02-05', 'Deposit', 3200.00, 'Payroll deposit'),
  (1025, 103, '2024-02-05', 'Deposit', 2800.00, 'Payroll deposit'),
  (1026, 105, '2024-02-03', 'Deposit', 5500.00, 'Payroll deposit'),
  (1027, 107, '2024-02-05', 'Deposit', 1800.00, 'Payroll deposit'),
  (1028, 109, '2024-02-05', 'Deposit', 4200.00, 'Payroll deposit'),
  (1029, 111, '2024-02-05', 'Deposit', 1500.00, 'Payroll deposit'),
  (1030, 115, '2024-02-05', 'Deposit', 4800.00, 'Payroll deposit');

Your raw data layer is complete. You have 10 members, 15 accounts, and 30 transactions loaded and ready to explore. Next up, you'll write SQL queries to dig into this data using JOINs and aggregate functions.

Explore and Query Your Raw Data

Your raw data tables are loaded with sample credit union data. Before transforming anything, you need to understand what the data actually looks like.

In this step, you will write SQL queries that explore, filter, join, and aggregate your raw data. These are the exact query patterns a Data Engineer uses every day to investigate datasets and prepare them for transformation.

In this step, get ready to:

Run basic SELECT queries to explore your raw tables and filter rows with WHERE.
Write JOIN queries to connect members, accounts, and transactions together.
Use aggregate functions like SUM, AVG, and COUNT with GROUP BY to summarize data.

Run basic queries to explore your tables

The first thing to do with any new dataset is look at what is inside it. Basic SELECT queries let you inspect rows, count records, and discover the unique values in each column.

In your Credit Union Pipeline worksheet, set your context to the raw schema and explore the members table by running these queries one at a time.
Place your cursor on a statement and press Cmd+Return (macOS) or Ctrl+Enter (Windows) to run it.

-- Set the context to the raw schema
USE DATABASE credit_union_db;
USE SCHEMA raw;

-- Basic exploration
SELECT * FROM members;
SELECT COUNT(*) AS total_members FROM members;
SELECT DISTINCT membership_type FROM members;
SELECT DISTINCT status FROM members;

What do these queries do?

SELECT * returns every column and every row in the table so you can see the full picture.
COUNT(*) tells you how many rows the table contains.
DISTINCT removes duplicates so you can see only the unique values in a column.

You should see 10 rows in the members table, three distinct membership types (Basic, Premium, Standard), and two distinct statuses (Active, Inactive).

Seeing "Object does not exist" errors?

Make sure you ran USE DATABASE credit_union_db; and USE SCHEMA raw; first. Without these, Snowflake does not know where to find your tables.
Check that your warehouse is set. Click the dropdown at the top of the worksheet and select CREDIT_UNION_WH if it is not already selected.
Why are my queries returning "Object does not exist" even though I set the context?

Now that you know what values exist in the data, you can filter for specific rows. The WHERE clause lets you narrow results down to only the rows that match a condition.

Filter your data by running these two queries one at a time:

-- Filter with WHERE
SELECT first_name, last_name, membership_type
FROM members
WHERE status = 'Active';

SELECT account_type, balance
FROM accounts
WHERE balance > 10000;

What do these queries do?

The first query returns only members whose status is Active. It also selects only three columns instead of all columns, which keeps the results focused.
The second query finds high-balance accounts. The > 10000 condition works the same way as a number comparison in math.

The first query should return 8 active members. The second should return 6 accounts with balances above 10,000.

Write JOIN queries to connect your tables

Right now your data is split across three separate tables. Members, accounts, and transactions each live in their own table. To answer questions like "What transactions did Maria Garcia make?" you need to connect these tables together using a JOIN.

Join the members and accounts tables by running this query:

-- JOIN members and accounts
SELECT
  m.first_name,
  m.last_name,
  m.membership_type,
  a.account_type,
  a.balance
FROM members m
INNER JOIN accounts a ON m.member_id = a.member_id
ORDER BY m.last_name, a.account_type;

What does this query do?

INNER JOIN connects rows from two tables where the ON condition matches. Here, it pairs each member with their accounts using member_id.
The m and a are aliases. They are short nicknames for the table names so you do not have to type members.first_name every time.
Members who have no accounts will NOT appear in the results. That is how INNER JOIN works.

You should see 15 rows. Some members appear multiple times because they have more than one account.

Now extend the query to include all three tables by running this:

-- JOIN all three tables
SELECT
  m.first_name,
  m.last_name,
  a.account_type,
  t.transaction_date,
  t.transaction_type,
  t.amount,
  t.description
FROM members m
INNER JOIN accounts a ON m.member_id = a.member_id
INNER JOIN transactions t ON a.account_id = t.account_id
ORDER BY m.last_name, t.transaction_date;

What does this query do?

This chains two JOINs together. The first JOIN connects members to their accounts. The second JOIN connects those accounts to their transactions. The result is one row per transaction, with the member name and account type attached.

You should see 30 rows. This is the complete picture of who made what transactions and in which accounts.

There is one problem with INNER JOIN. It only returns rows where a match exists in both tables. If a member has no accounts or no transactions, they disappear from the results entirely. A LEFT JOIN fixes this.

Run this LEFT JOIN query to include all members, even those with no transactions:

-- LEFT JOIN to include members with no transactions
SELECT
  m.first_name,
  m.last_name,
  COUNT(t.transaction_id) AS transaction_count
FROM members m
LEFT JOIN accounts a ON m.member_id = a.member_id
LEFT JOIN transactions t ON a.account_id = t.account_id
GROUP BY m.first_name, m.last_name
ORDER BY transaction_count DESC;

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows where both tables have a matching value. Members without accounts are excluded.
LEFT JOIN returns every row from the left table (members) and fills in NULL for columns from the right table when no match exists.
In this query, Kevin Thomas appears with a transaction count of 0 because he has no accounts. An INNER JOIN would have hidden him entirely.

You should see 10 rows. Every member appears, and the members with no transactions show a transaction_count of 0.

Getting a syntax error on your JOIN?

Check that every JOIN has an ON clause specifying how the tables connect. Missing the ON clause is the most common JOIN error.
Make sure you are using the correct column names. The members table uses member_id and the accounts table uses account_id as their primary keys.
Why is my JOIN query returning a syntax error in Snowflake?

Use aggregate functions to summarize your data

Individual rows are useful for investigation, but data teams usually need summaries. Aggregate functions like SUM, AVG, and COUNT collapse many rows into a single number. Combined with GROUP BY, they let you calculate totals per member, per account type, or per month.

Calculate each member's total balance and number of accounts by running this query:

-- Aggregate: total balance per member
SELECT
  m.first_name,
  m.last_name,
  SUM(a.balance) AS total_balance,
  COUNT(a.account_id) AS num_accounts
FROM members m
INNER JOIN accounts a ON m.member_id = a.member_id
GROUP BY m.first_name, m.last_name
ORDER BY total_balance DESC;

What does this query do?

GROUP BY m.first_name, m.last_name groups all the account rows for each member together into one result row.
SUM(a.balance) adds up all account balances within each group. COUNT(a.account_id) counts how many accounts each member has.
ORDER BY total_balance DESC sorts the results from highest balance to lowest.

Emily Davis should appear at the top with the highest total balance across her three accounts.

Break down transaction statistics by type by running this query:

-- Aggregate: transaction statistics by type
SELECT
  transaction_type,
  COUNT(*) AS num_transactions,
  SUM(amount) AS total_amount,
  AVG(amount) AS avg_amount,
  MIN(amount) AS min_amount,
  MAX(amount) AS max_amount
FROM transactions
GROUP BY transaction_type
ORDER BY total_amount DESC;

What does this query do?

This query groups all 30 transactions by their type (Deposit, Withdrawal, Transfer, Payment) and calculates five different statistics for each group. The MIN and MAX functions show the range of transaction sizes within each type.

You should see four rows, one for each transaction type. Deposits should have the highest total amount since they are the most common transaction in the sample data.

Calculate the number of accounts and average balance per membership type by running this query:

-- Aggregate: accounts per membership type
SELECT
  m.membership_type,
  COUNT(DISTINCT m.member_id) AS num_members,
  COUNT(a.account_id) AS num_accounts,
  AVG(a.balance) AS avg_balance
FROM members m
INNER JOIN accounts a ON m.member_id = a.member_id
GROUP BY m.membership_type
ORDER BY avg_balance DESC;

Why use COUNT(DISTINCT) here?

Because the JOIN creates multiple rows per member (one per account), a plain COUNT(m.member_id) would overcount. COUNT(DISTINCT m.member_id) counts each member only once, giving the true number of members in each membership type.

You should see three rows: Premium, Standard, and Basic. Premium members should have the highest average balance.

Getting a "not in GROUP BY" error?

Every column in your SELECT that is not inside an aggregate function (like SUM or COUNT) must also appear in the GROUP BY clause. Check that your GROUP BY lists the same columns as your SELECT.
Why is my GROUP BY query returning an error in Snowflake?

Awesome, I've got everything!

Great. Double check that your worksheet is saved in Snowsight. Your queries should be preserved automatically.

I'd like to double check the full code

Here is the complete set of exploration queries you should have in your worksheet for this step.

-- ============================================
-- STEP 4: Explore and Query Raw Data
-- ============================================

USE DATABASE credit_union_db;
USE SCHEMA raw;

-- Basic exploration
SELECT * FROM members;
SELECT COUNT(*) AS total_members FROM members;
SELECT DISTINCT membership_type FROM members;
SELECT DISTINCT status FROM members;

-- Filter with WHERE
SELECT first_name, last_name, membership_type
FROM members
WHERE status = 'Active';

SELECT account_type, balance
FROM accounts
WHERE balance > 10000;

-- JOIN members and accounts
SELECT
  m.first_name,
  m.last_name,
  m.membership_type,
  a.account_type,
  a.balance
FROM members m
INNER JOIN accounts a ON m.member_id = a.member_id
ORDER BY m.last_name, a.account_type;

-- JOIN all three tables
SELECT
  m.first_name,
  m.last_name,
  a.account_type,
  t.transaction_date,
  t.transaction_type,
  t.amount,
  t.description
FROM members m
INNER JOIN accounts a ON m.member_id = a.member_id
INNER JOIN transactions t ON a.account_id = t.account_id
ORDER BY m.last_name, t.transaction_date;

-- LEFT JOIN to include members with no transactions
SELECT
  m.first_name,
  m.last_name,
  COUNT(t.transaction_id) AS transaction_count
FROM members m
LEFT JOIN accounts a ON m.member_id = a.member_id
LEFT JOIN transactions t ON a.account_id = t.account_id
GROUP BY m.first_name, m.last_name
ORDER BY transaction_count DESC;

-- Aggregate functions
SELECT
  m.first_name,
  m.last_name,
  SUM(a.balance) AS total_balance,
  COUNT(a.account_id) AS num_accounts
FROM members m
INNER JOIN accounts a ON m.member_id = a.member_id
GROUP BY m.first_name, m.last_name
ORDER BY total_balance DESC;

SELECT
  transaction_type,
  COUNT(*) AS num_transactions,
  SUM(amount) AS total_amount,
  AVG(amount) AS avg_amount,
  MIN(amount) AS min_amount,
  MAX(amount) AS max_amount
FROM transactions
GROUP BY transaction_type
ORDER BY total_amount DESC;

SELECT
  m.membership_type,
  COUNT(DISTINCT m.member_id) AS num_members,
  COUNT(a.account_id) AS num_accounts,
  AVG(a.balance) AS avg_balance
FROM members m
INNER JOIN accounts a ON m.member_id = a.member_id
GROUP BY m.membership_type
ORDER BY avg_balance DESC;

You have explored your raw data with filtering, JOINs, and aggregations. These are the same query patterns you will use in the next step to build analytics views. Next up, you will transform this raw data into clean, analytics-ready views that a data team can use for reporting.

Transform Data and Create Analytics Views

In Step 4, you explored your raw data with JOINs and aggregations. Those queries answered questions on the fly, but they disappeared the moment you moved on. Now it is time to make that work permanent.

In this step, you will create views in the analytics schema. A view is a saved SQL query that the analytics team can query like a regular table, without needing to understand the complex JOINs behind it. This is the "Transform" stage of your ELT pipeline.

In this step, get ready to:

Create a member summary view that joins all three raw tables into one analytics-ready dataset.
Create a monthly transaction report view that aggregates activity over time.
Verify your views produce accurate results by comparing them against the raw data.

Create a member summary view

Your first view will combine data from all three raw tables into a single row per member. It will include each member's total balance, account count, transaction count, and a breakdown of deposits, withdrawals, and payments.

Switch to the analytics schema by running these statements in your worksheet:

USE DATABASE credit_union_db;
USE SCHEMA analytics;

Why switch to the analytics schema?

You are moving from the raw schema (where your source data lives) to the analytics schema (where transformed, business-ready data belongs). This two-schema architecture is a common pattern in real data pipelines.

Create the member summary view by running this SQL:

-- Member Summary View
CREATE OR REPLACE VIEW member_summary AS
SELECT
  m.member_id,
  m.first_name,
  m.last_name,
  m.email,
  m.membership_type,
  m.status,
  m.join_date,
  -- Calculate how long each member has been with the credit union
  DATEDIFF('month', m.join_date, CURRENT_DATE()) AS membership_months,
  -- Account-level aggregations
  COUNT(DISTINCT a.account_id) AS num_accounts,
  SUM(a.balance) AS total_balance,
  AVG(a.balance) AS avg_account_balance,
  -- Transaction-level aggregations
  COUNT(t.transaction_id) AS total_transactions,
  SUM(CASE WHEN t.transaction_type = 'Deposit' THEN t.amount ELSE 0 END) AS total_deposits,
  SUM(CASE WHEN t.transaction_type = 'Withdrawal' THEN t.amount ELSE 0 END) AS total_withdrawals,
  SUM(CASE WHEN t.transaction_type = 'Payment' THEN t.amount ELSE 0 END) AS total_payments
FROM credit_union_db.raw.members m
LEFT JOIN credit_union_db.raw.accounts a ON m.member_id = a.member_id
LEFT JOIN credit_union_db.raw.transactions t ON a.account_id = t.account_id
GROUP BY
  m.member_id, m.first_name, m.last_name, m.email,
  m.membership_type, m.status, m.join_date;

What does this view do?

LEFT JOIN connects members to accounts and transactions, so even members with no activity still appear in the results.
DATEDIFF('month', m.join_date, CURRENT_DATE()) calculates how many months each member has been with the credit union.
COUNT(DISTINCT a.account_id) counts each member's accounts without double-counting from the transactions JOIN.
The three SUM(CASE WHEN ...) lines break down transaction totals by type (deposits, withdrawals, payments) into separate columns.
Fully qualified table names like credit_union_db.raw.members ensure the view always reads from the raw schema, even though the view itself lives in the analytics schema.

Place your cursor on the CREATE OR REPLACE VIEW statement and press Cmd+Return (macOS) or Ctrl+Enter (Windows) to run it.
You should see a success message at the bottom of the worksheet saying View MEMBER_SUMMARY successfully created.

Seeing an error about the schema or table?

Make sure you ran both USE DATABASE credit_union_db and USE SCHEMA analytics before creating the view.
Check that your raw tables (members, accounts, transactions) exist in the raw schema from Step 3.
Confirm your warehouse is running. Run USE WAREHOUSE credit_union_wh; if you see a warehouse error.
Still stuck? Help me fix the error I'm seeing when creating the member_summary view.

Create a monthly transaction report view

Your second view gives the analytics team a time-series perspective on credit union activity. It groups transactions by month and type, showing totals, counts, and averages.

Create the monthly transaction report view by running this SQL:

-- Monthly Transaction Report View
CREATE OR REPLACE VIEW monthly_transaction_report AS
SELECT
  -- Truncate each date to the first day of its month
  DATE_TRUNC('month', t.transaction_date) AS report_month,
  t.transaction_type,
  -- Aggregate metrics for each month/type combination
  COUNT(*) AS transaction_count,
  SUM(t.amount) AS total_amount,
  AVG(t.amount) AS avg_transaction_amount,
  MIN(t.amount) AS min_transaction,
  MAX(t.amount) AS max_transaction
FROM credit_union_db.raw.transactions t
GROUP BY
  DATE_TRUNC('month', t.transaction_date),
  t.transaction_type
ORDER BY report_month, transaction_type;

What does this view do?

DATE_TRUNC('month', t.transaction_date) rounds every transaction date down to the first day of its month, so January 5 and January 22 both become January 1.
The GROUP BY groups rows by month and transaction type, then the aggregate functions (COUNT, SUM, AVG, MIN, MAX) calculate metrics for each group.
This view only reads from the transactions table, so it does not need any JOINs.

Run the statement the same way: place your cursor on it and press Cmd+Return (macOS) or Ctrl+Enter (Windows).
You should see View MONTHLY_TRANSACTION_REPORT successfully created in the results panel.

View not creating?

Make sure your cursor is on the CREATE OR REPLACE VIEW line, not on a different statement in the worksheet. Snowflake only runs the statement under your cursor when you use the keyboard shortcut.
If you see a syntax error, double-check that you copied the entire statement including the closing line with ORDER BY report_month, transaction_type;.
Need help? Help me debug the error I'm seeing when creating the monthly_transaction_report view.

Query your analytics views

Your views are created, but you need to confirm they produce the right numbers. A data engineer's job is not done until the output is verified.

Query the member summary view by running this statement:

-- Query the member summary view, sorted by highest balance
SELECT * FROM member_summary ORDER BY total_balance DESC;

You should see 10 rows in the results, one for each member. The member with the highest total_balance should appear at the top.

Query the monthly transaction report by running:

-- Query the monthly transaction report
SELECT * FROM monthly_transaction_report ORDER BY report_month, transaction_type;

You should see rows grouped by month and transaction type. Each row shows the count, total, average, min, and max for that combination.

Now verify your view's accuracy by comparing its totals against the raw data.

Run these two queries one at a time to compare total deposits:

-- Verify accuracy: total deposits from the view
SELECT SUM(total_deposits) AS view_total_deposits FROM member_summary;

-- Total deposits from the raw data
SELECT SUM(amount) AS raw_total_deposits FROM credit_union_db.raw.transactions WHERE transaction_type = 'Deposit';

What should I see?

Both queries should return the same number. If the view's total deposits match the raw table's total deposits, your view is correctly aggregating the data. This kind of cross-check is a standard practice in data engineering to catch errors early.

Numbers not matching?

Make sure you are still in the analytics schema when querying member_summary. Run USE SCHEMA analytics; if needed.
If the raw query returns a different number, check that your member_summary view uses LEFT JOIN (not INNER JOIN), and that the CASE WHEN filters on 'Deposit' exactly as it appears in the raw data.
Still stuck? Help me figure out why my member_summary view totals don't match the raw data.

Awesome, I've got everything!

Great. Double check you have both views created in the analytics schema.

I'd like to double check the full code

Here is the full SQL for this step. Compare it against what you have in your worksheet.

-- ============================================
-- STEP 5: Transform Data and Create Analytics Views
-- ============================================

USE DATABASE credit_union_db;
USE SCHEMA analytics;

-- Member Summary View
CREATE OR REPLACE VIEW member_summary AS
SELECT
  m.member_id,
  m.first_name,
  m.last_name,
  m.email,
  m.membership_type,
  m.status,
  m.join_date,
  DATEDIFF('month', m.join_date, CURRENT_DATE()) AS membership_months,
  COUNT(DISTINCT a.account_id) AS num_accounts,
  SUM(a.balance) AS total_balance,
  AVG(a.balance) AS avg_account_balance,
  COUNT(t.transaction_id) AS total_transactions,
  SUM(CASE WHEN t.transaction_type = 'Deposit' THEN t.amount ELSE 0 END) AS total_deposits,
  SUM(CASE WHEN t.transaction_type = 'Withdrawal' THEN t.amount ELSE 0 END) AS total_withdrawals,
  SUM(CASE WHEN t.transaction_type = 'Payment' THEN t.amount ELSE 0 END) AS total_payments
FROM credit_union_db.raw.members m
LEFT JOIN credit_union_db.raw.accounts a ON m.member_id = a.member_id
LEFT JOIN credit_union_db.raw.transactions t ON a.account_id = t.account_id
GROUP BY
  m.member_id, m.first_name, m.last_name, m.email,
  m.membership_type, m.status, m.join_date;

-- Monthly Transaction Report View
CREATE OR REPLACE VIEW monthly_transaction_report AS
SELECT
  DATE_TRUNC('month', t.transaction_date) AS report_month,
  t.transaction_type,
  COUNT(*) AS transaction_count,
  SUM(t.amount) AS total_amount,
  AVG(t.amount) AS avg_transaction_amount,
  MIN(t.amount) AS min_transaction,
  MAX(t.amount) AS max_transaction
FROM credit_union_db.raw.transactions t
GROUP BY
  DATE_TRUNC('month', t.transaction_date),
  t.transaction_type
ORDER BY report_month, transaction_type;

-- Query the views
SELECT * FROM member_summary ORDER BY total_balance DESC;

SELECT * FROM monthly_transaction_report ORDER BY report_month, transaction_type;

-- Verify accuracy: compare total deposits from the view vs raw data
SELECT SUM(total_deposits) AS view_total_deposits FROM member_summary;
SELECT SUM(amount) AS raw_total_deposits FROM credit_union_db.raw.transactions WHERE transaction_type = 'Deposit';

Your ELT pipeline is complete. Raw data flows into the raw schema, gets transformed by SQL, and lands in the analytics schema as clean, queryable views. That is exactly how production data pipelines work.

Secret mission

Build a Loan Risk Classification View

Ready for a real-world analytics challenge? Create a loan risk classification view that uses CASE statements and window functions to categorize credit union members by risk level. This is the kind of advanced SQL that interviewers love to see.

Before you go

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project uses a Snowflake free trial with $400 in credits and no credit card required, so there is no risk of unexpected charges.

Resources you used:

Snowflake trial account.
Database: credit_union_db (contains all schemas, tables, and views).
Schemas: raw and analytics.
Virtual warehouse: credit_union_wh.
Tables: members, accounts, transactions.
Views: member_summary, monthly_transaction_report.
SQL worksheet: "Credit Union Pipeline".

Keep everything running

No action needed. Choose this if you are still actively building on your credit union pipeline or want to experiment with more queries.

Your Snowflake trial lasts 30 days with $400 in credits.
The credit_union_wh warehouse auto-suspends after 60 seconds of inactivity. It only consumes credits while actively running queries.
Your data stays in the database at minimal storage cost.

Pause - I'll come back to this later

You do not need to do anything. The warehouse auto-suspends on its own after 60 seconds of idle time, so there are no wasted credits while you are away.

Your database, tables, and views will remain exactly as you left them.
When you return, open your "Credit Union Pipeline" worksheet and pick up where you left off.
Keep in mind that the free trial expires after 30 days, so come back before then.

Delete - I don't want to use this again

Remove all project resources and start fresh. Run these commands in your Snowflake worksheet to delete everything you created.

Run the following SQL to drop the database and warehouse:

DROP DATABASE credit_union_db;
DROP WAREHOUSE credit_union_wh;

Dropping the database removes all schemas, tables, and views inside it.
To cancel the Snowflake trial entirely, contact Snowflake Support. Trial accounts cannot be cancelled through the web interface.

Mission accomplished

That's a Wrap!

Nice work! You just built a complete ELT data pipeline in Snowflake for a credit union. That is a huge step toward the Data Engineer role.

Here's what you accomplished:

Set up a Snowflake environment from scratch with a database, two schemas, a virtual warehouse, and three raw data tables loaded with sample credit union member data.
Wrote SQL queries using JOINs, aggregations, and filtering to explore raw data and validate results across multiple tables.
Built an ELT pipeline that transforms raw data into analytics-ready views, including a member summary dashboard and a monthly transaction report for the analytics team.
Created a loan risk classification view using CASE statements and window functions to categorize members by risk level.

Ready to quiz yourself?