AcmeCard Fraud Analytics

Load synthetic fraud and payments data (RAW → STAGING → INTERMEDIATE → GOLD) into your own Snowflake. Generate data locally, then load. The repo contains only env.example (no credentials, no table data).

New here? → GETTING_STARTED.md (generate then load in 4 steps.)

---

Table of contents

1. Overview
2. Prerequisites
3. Quick start
4. Setup (step-by-step)
5. Sample queries
6. Project structure
7. Reference

---

Overview

What	Description
Goal	Get AcmeCard-style fraud and payments data into your Snowflake so you can run investigations, trend queries, and analytics.
Flow	1) Generate synthetic data → data/raw/*.csv. 2) Load into your Snowflake: RAW (from CSV), STAGING (9 tables, DDL from schema/staging/, populated from RAW), INTERMEDIATE (7 views, DDL from schema/intermediate/), GOLD (5 views, DDL from schema/gold/). Optionally deploy REPORTING tasks.
Auth	Use a programmatic access token (PAT) in .env—not your account password.
Credentials	The repo contains only env.example. Copy it to .env locally and fill in your values; .env is not in the repo.
Scope	Data model (15 RAW tables, 9 STAGING, 7 INTERMEDIATE views, 5 GOLD views), 65+ queries, tasks, and docs. No BI dashboards or Semantic Model config.

In one sentence: Clone → copy env.example to .env → generate data → load into your Snowflake → run queries.

---

Prerequisites

• Python 3.8+
• Your own Snowflake account (trial or paid)
• A programmatic access token (PAT) for your user → put it in SNOWFLAKE_PASSWORD in your local .env (copy from env.example).
  (Create in Snowsight: Governance & security → Users & roles → your user → Programmatic access tokens → Generate new token.)

---

Quick start

Step 1 — Generate data (writes data/raw/*.csv):

git clone <repo-url>
cd acmecard
pip install -r requirements.txt
cp env.example .env
# Edit .env: your Snowflake account, user, PAT, warehouse, database (acmecard), role
python scripts/generate_data.py

Step 2 — Load (into your Snowflake):

python scripts/load_data_to_snowflake.py

Then run queries in Snowflake Worksheets. See Sample queries or open any file in queries/.

Optional: Test the connection first: python scripts/connect_snowflake.py

---

Setup (step-by-step)

1. Clone and install

   git clone <repo-url>
   cd acmecard
   python3 -m venv .venv
   source .venv/bin/activate   # Windows: .venv\Scripts\activate
   pip install -r requirements.txt

2. Configure Snowflake

   cp env.example .env

   Copy env.example to .env, then edit .env with your values:

   • SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER, SNOWFLAKE_PASSWORD (your PAT)
   • SNOWFLAKE_WAREHOUSE, SNOWFLAKE_DATABASE (default acmecard), SNOWFLAKE_ROLE

3. Generate data

   • Run: python scripts/generate_data.py
     This writes data/raw/*.csv (default: ~120K auth events, fixed seed 42 and end date so everyone gets the same reproducible dataset).

4. Load (into your Snowflake)

   • Run: python scripts/load_data_to_snowflake.py
     Creates database (if needed) and all four layers in your Snowflake: RAW (15 tables from data/raw/*.csv), STAGING (9 tables from schema/staging/, then populated from RAW), INTERMEDIATE (7 views from schema/intermediate/), GOLD (5 views from schema/gold/).

5. Verify
   Run queries from Sample queries or any queries/NN_*.sql in Snowflake Worksheets. Set context: USE DATABASE ACMECARD; and select a warehouse.

6. Optional — Deploy tasks
   Run: python scripts/deploy_tasks_to_snowflake.py to create the REPORTING schema and 15 scheduled tasks. See tasks/README.md.

---

Sample queries

All of these live in queries/. Run in Snowflake Worksheets after setting USE DATABASE ACMECARD; and selecting a warehouse.

#	File	Purpose
1	01_merchants_abnormal_fraud_spikes.sql	Merchants with abnormal fraud spikes (baseline, sigma, risk tier)
2	21_daily_fraud_kpis_gold.sql	Daily fraud KPIs (GOLD) — approval rate, chargeback rate, loss
3	22_monthly_loss_trend_gold.sql	Monthly fraud loss trend (GOLD)
4	23_investigation_drill_down.sql	Investigation drill-down — open cases, entity/merchant risk, velocity
5	47_int_fraud_by_region_channel_trend.sql	Fraud by region and channel over time (INTERMEDIATE)

Full list: queries/README.md (65+ queries for RAW, STAGING, INTERMEDIATE, and GOLD).

---

Project structure

acmecard/
├── README.md                 ← You are here
├── GETTING_STARTED.md        ← 4-step generate + load guide
├── env.example               ← Copy to .env and fill in
├── requirements.txt
├── schema/                   ← DDL: raw (15), staging (9), intermediate (7), gold (5)
│   ├── raw/
│   ├── staging/
│   ├── intermediate/
│   └── gold/
├── data/
│   └── raw/                  ← Populated by generate_data.py (*.csv)
├── scripts/
│   ├── generate_data.py      ← Generate synthetic data → data/raw/
│   ├── load_data_to_snowflake.py  ← Load data/ into your Snowflake
│   ├── deploy_tasks_to_snowflake.py  ← Create REPORTING schema + 15 tasks
│   ├── list_snowflake_tasks.py
│   ├── verify_snowflake_load.py     ← Check row counts after load
│   ├── check_snowflake_data.py
│   ├── list_snowflake_databases.py
│   ├── connect_snowflake.py  ← Test connection
│   ├── run_queries.py        ← Run queries from repo against Snowflake
│   ├── smoke_test_queries.py
│   ├── grant_gold_schema_privileges.sql  ← Grant GOLD to BI role (run in Snowflake)
│   └── populate_staging_intermediate.sql  ← Used by load script
├── config/
│   ├── README.md
│   └── connections.example.toml
├── tasks/                    ← Task DDL + definitions (see tasks/README.md)
├── queries/                  ← 65+ SQL files (see queries/README.md)
├── glossary/
│   └── business_glossary.csv
└── docs/
    ├── GLOSSARY.md
    ├── SCHEMAS_REFERENCE.md
    ├── TABLE_RELATIONSHIPS.md
    ├── STANDARDS_BASIS.md
    ├── FRAUD_PLAYBOOK.md
    ├── ANALYST_WORKFLOWS.md
    └── DATASETS.md

Category	File	Purpose
Setup	env.example	Only env file in the repo. Copy to .env locally.
Generate	scripts/generate_data.py	Generate synthetic fraud/payments data into data/raw/. Default: seed 42, fixed end date (reproducible).
Load	scripts/load_data_to_snowflake.py	Create ACMECARD DB; load RAW from CSV; run STAGING DDL (9 tables) and populate from RAW; run INTERMEDIATE DDL (7 views) and GOLD DDL (5 views). All four layers are created in your Snowflake.
Tasks	scripts/deploy_tasks_to_snowflake.py	Create REPORTING schema and 15 scheduled tasks.
Verify	scripts/connect_snowflake.py	Test that your local .env and PAT work.
Verify	scripts/verify_snowflake_load.py	Check key table row counts after load.
Queries	queries/*.sql	65+ investigation-ready SQL files. Run in Snowflake after load.
Reference	docs/SCHEMAS_REFERENCE.md	Full schema and table reference (RAW → GOLD).

---

Reference

• Credentials: The repo has only env.example. Copy it to .env locally; .env is gitignored.
• PAT: Snowsight → Governance & security → Users & roles → your user → Programmatic access tokens → Generate new token. Put the token in SNOWFLAKE_PASSWORD.
• Database: Single database acmecard (Snowflake: ACMECARD). Schemas: RAW, STAGING, INTERMEDIATE, GOLD, REPORTING (after task deploy). Table/view names: UPPERCASE (unquoted).
• Data model: 15 RAW tables, 9 STAGING, 7 INTERMEDIATE views, 5 GOLD views. See docs/SCHEMAS_REFERENCE.md.
• Tasks: Deploy with python scripts/deploy_tasks_to_snowflake.py. Tasks live in ACMECARD → REPORTING → Tasks. See tasks/README.md.
• BI / GOLD access: If your BI tool cannot read GOLD, run scripts/grant_gold_schema_privileges.sql in Snowflake (edit grant_database and grant_to_role first).