Customer Churn Analysis in Banking (Snowflake)

 Project 1: Customer Churn Analysis in Banking

Tech stack: SQL (Snowflake), Python, Power BI
Highlights:

• Use Snowflake to store customer transaction and demographic data

• Create a data pipeline using Snowpipe + Tasks

• Calculate churn metrics with SQL (e.g., last activity date, inactivity duration)

• Use Power BI to visualize churn trends, segments, and predictions

Project 2: Automated Sales Analytics Pipeline

Tech stack: Snowflake + Python (pandas/airflow) + Power BI
Highlights:

• Load raw data into Snowflake via COPY INTO from external stage

• Clean and transform using SQL scripts scheduled with Tasks

• Store snapshots and perform time-series analysis

• Visualize KPIs and drilldowns (by region, product, time)

✅ Hands-on SQL Queries for Snowflake Practice

1. Create a Table

CREATE TABLE customers (
    customer_id INT,
    name STRING,
    email STRING,
    signup_date DATE
);

2. Insert Sample Data

INSERT INTO customers (customer_id, name, email, signup_date) VALUES 
(1, 'John Doe', 'john@example.com', '2024-01-15'),
(2, 'Jane Smith', 'jane@example.com', '2024-02-10');

3. Select with Filter

SELECT * FROM customers 
WHERE signup_date > '2024-01-31';

4. Create an External Stage

CREATE OR REPLACE STAGE my_stage 
URL='s3://mybucket/data/' 
STORAGE_INTEGRATION = my_s3_integration;

5. Load Data using COPY INTO

COPY INTO customers 
FROM @my_stage/customers.csv 
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"');

6. Create a View

CREATE OR REPLACE VIEW active_customers AS 
SELECT * FROM customers 
WHERE signup_date > CURRENT_DATE - INTERVAL '90' DAY;

7. CTE Example – Monthly Signup Count

WITH monthly_signups AS (
    SELECT DATE_TRUNC('month', signup_date) AS signup_month, COUNT(*) AS total
    FROM customers
    GROUP BY 1
)
SELECT * FROM monthly_signups ORDER BY signup_month;

8. Time Travel Query

SELECT * FROM customers AT (OFFSET => -60*5);  -- 5 minutes ago

9. Stream & Task for Incremental Processing

CREATE OR REPLACE STREAM customer_stream ON TABLE customers;

CREATE OR REPLACE TASK process_new_customers
WAREHOUSE = my_wh
SCHEDULE = '5 MINUTE'
AS
INSERT INTO new_customer_log
SELECT * FROM customer_stream WHERE METADATA$ACTION = 'INSERT';

10. Grant Access to Role

GRANT SELECT ON TABLE customers TO ROLE analyst_role;




✅ Project 1: Customer Churn Analysis (Banking Domain)
🔸 Q1: How would you implement a Customer Churn model using Snowflake?
Data Ingestion:
Ingest customer profile, transaction, and service usage data using Snowpipe or COPY INTO.
Data Transformation:
Use SQL transformations or Streams + Tasks to calculate churn signals:
Last transaction date
Account balance trend
Missed logins or interactions
Feature Engineering:
Create features like:
SELECT customer_id,
       DATEDIFF(day, MAX(transaction_date), CURRENT_DATE) AS days_since_last_txn,
       AVG(balance) AS avg_balance
FROM transactions
GROUP BY customer_id;
Churn Labeling:
If a customer hasn’t transacted in 90+ days → mark as churned.
Visualization:
Use Power BI or Tableau connected to Snowflake via live connection.
Build churn dashboards: churn by age group, tenure, region.

🔸 Q2: How would you use Snowflake features to build a scalable churn pipeline?
Time Travel: Analyze past snapshots for churned customers.
Streams & Tasks: Automate incremental updates.
Zero-Copy Cloning: Test models on a copy of production data without duplicating storage.


✅ Project 2: Sales Analytics Dashboard (Retail or E-commerce)
🔸 Q1: Describe your data pipeline for a Snowflake-based sales dashboard.
Raw Data Load:
Use external stage (e.g., S3/Azure) + COPY INTO.
Data Modeling:
Create dimensional model:
sales_fact, product_dim, store_dim, calendar_dim.
Transformations:
Use dbt, Tasks, or SQL scripts for ETL.
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales_fact
GROUP BY product_id;
KPIs Calculated in Snowflake:
Daily/Monthly Sales
Conversion Rate
Product Profitability
Power BI:
Connect directly via Snowflake connector
Build drilldowns: category → product → SKU

🔸 Q2: How do you optimize Snowflake queries for sales analytics?
Use clustering on large tables (e.g., by region, date).
Enable automatic scaling on warehouses.
Cache frequent dashboard queries using materialized views.
Monitor with QUERY_HISTORY and optimize slow-running queries.