Summary

• What would a Single Version of the Truth (SVOT) for Asset Managers on Snowflake look like?

Follow along with the video

Github

• The code is open-sourced on github.

Problem Statement:

• Asset managers (big banks, insurance companies, and hedge funds) have spent hundreds of millions of dollars on systems to quickly and accurately give a SVOT in real-time. These systems are critical especially in times of market stress like the Great Financial Crisis or any unexpected market change.

Why Snowflake

• SnowSight allows you to create and share dashboards for executives, portfolio managers, risk managers, and traders.
• Significantly high performance and less cost of maintaining one SVOT
• Near-Unlimited Compute and Concurrency enable quick data-driven decisions

What we will see

• Use Data Marketplace to instantly get free stock price history from the Zepl data share
• Query trade, cash, positions, and PnL (Profit and Loss) on Snowflake
• Use Window Functions to automate cash, position, and PnL reporting

Prerequisites

• Snowflake Account or Trial Account with accountadmin privileges.

You'll learn how to

• Query free stock market history data instantly with zero learning curve and time
• Run a Python faker function to create 100 synthetic traders
• Size up compute to create 3 billion synthetic trades
• Insert those trades ordered by trader, symbol, and date so any queries on that data are signficantly faster
• Create a cluster key to future proof that trade table from any data that has been inserted without being sorted
• Create a window function to calcluate real time trades, cash, and Profit and Loss (PnL)
• Query 3 billion rows with only small compute and 3 second run-time
• See Snowflake's 3 caches
• Zero Copy Clone for DevOps and instant sandboxes
• Time Travel to see and roll back up to 90 days of data
• Drop and Undrop Tables

We setup

• Role Based Access Control RBAC
• Virtual Warehouses (compute)
• Database
• Objects

Run the finserv demo 10 setup script. Each script is idempotent meaning that you can rerun it without issues.

YouTube video: Running script 10

We:

• "mount" and verify the Knoema economy_data_atlas share
• ensure data quality, ie no duplicates and positive share prices
• Python Faker function (credit to James Weakley's Flaker 2.0 - Fake Snowflake data the easy way)

Knoema Economy Data Atlas

Run the finserv demo 20 Marketplace script.

"Mount" the free Knoema Economy Data Atlas share. Click "Get" then name the database economy_data_atlas and grant access to the public role.

## Verify Data Marketplace Share
    select top 1 *
    from economy_data_atlas.economy.usindssp2020;

YouTube video: Running script 20 and mounting the share

We:

• Size up compute to create 3 billion synthetic trades
• Insert those trades ordered by trader, symbol, and date so any queries on that data are signficantly faster
• Create a cluster key to future proof that trade table from any data that has been inserted without being sorted
• Create a window function to calculate real time trades, cash, and Profit and Loss (PnL)

Run the finserv demo 30 DDL script.

YouTube video: Running script 30

We:

• Query 3 billion rows with only small compute and 3 second run-time
• See Snowflake's 3 caches
• Zero Copy Clone for DevOps and instant sandboxes
• Time Travel to see and roll back up to 90 days of data
• Drop and Undrop Tables

Run the finserv demo 40 queries script.

YouTube video: Running script 40

We setup

• Three Snowsight filters: Trader, TradeDate, and Snowsight

These filters give us reusable drop-downs throughout our Snowsight dashboards. We set them to refresh once a day.

Build the finserv demo 60 filter Snowsight script.

YouTube video: Running script 60 building the three filters in Snowsight

We setup

• a Snowsight dashboard with charts (line, bar, and scorecard)

Build un the finserv demo 70 SnowSight.

YouTube video: Running script 70 to build the dashboard

Optional Script to reset / remove all objects created during this demo

Run the finserv demo 90 reset script.

Recap

YouTube video: A recap of what we covered

What we've covered

• Query free stock market history data instantly with zero learning curve and time
• Run a Python faker function to create 100 synthetic traders
• Size up compute to create 3 billion synthetic trades
• Insert those trades ordered by trader, symbol, and date so any queries on that data are signficantly faster
• Create a cluster key to future proof that trade table from any data that has been inserted without being sorted
• Create a window function to calcluate real time trades, cash, and Profit and Loss (PnL)
• Query 3 billion rows with only small compute and 3 second run-time
• See Snowflake's 3 caches
• Zero Copy Clone for DevOps and instant sandboxes
• Time Travel to see and roll back up to 90 days of data
• Drop and Undrop Tables

More resources

Full Demo on Snowflake Demo Hub

Modernizing Risk Analytics with Sigma on Snowflake.

Allen Wong's Youtube Channel.