Snowflake’s Cloud Data Warehouse — What I Learned and Why I’m Rethinking the Data Warehouse

Achieving Performance and Simplicity in the Cloud for ORC Data Loads and Tableau Visualization

Hashmap
Hashmap
Jun 28, 2018 · 14 min read
Image for post
Image for post

Why the Need to Rethink?

You Have to Consider the Cloud

Selecting Snowflake Cloud Data Warehouse

Snowflake Architecture

Image for post
Image for post

Connecting to Snowflake

Setting Up A Virtual Warehouse

CREATE WAREHOUSE ETL_WH
WITH WAREHOUSE_SIZE = 'SMALL'
WAREHOUSE_TYPE = 'STANDARD'
AUTO_SUSPEND = 600
AUTO_RESUME = TRUE;
CREATE WAREHOUSE QUERY_WH
WITH WAREHOUSE_SIZE = 'MEDIUM'
WAREHOUSE_TYPE = 'STANDARD'
AUTO_SUSPEND = 600
AUTO_RESUME = TRUE;

Databases, Tables and Views within Snowflake

Snowflake Databases

CREATE DATABASE MULTI_COLUMN_DB;
CREATE DATABASE SINGLE_VARIANT_DB;
CREATE SCHEMA "MULTI_COLUMN_DB".POC;
CREATE SCHEMA "SINGLE_VARIANT_DB".POC;

Multiple Column Tables

CREATE TABLE "MULTI_COLUMN_DB"."POC".DEVICEINFO (
id string,
manufacturer string,
serialNumber string,
etimestamp timestamp,
model string,
accuracy int,
batch_id bigint,
event_date date)
cluster by (event_date);

Single Variant Column Tables

Views

CREATE "SINGLE_VARIANT_DB"."POC”.VIEW DEVICEINFO as
SELECT
$1:_col0::string as id,
$1:_col1::string as manufacturer,
$1:_col2::string as serialNumber ,
$1:_col3::timestamp as etimestamp ,
$1:_col4::string as model ,
$1:_col5::number as accuraccy,
$1:_col6::number as batch_id ,
$1:_col4::date as event_date,
FROM DEVICEINFO_VAR;

Loading Data

Create Stage Object

CREATE STAGE MULTI_COLUMN_DB"."POC".ORC_SNOWFLAKE
URL = 's3://ntgr-snowflake'
CREDENTIALS = (AWS_KEY_ID = 'xxxxxx' AWS_SECRET_KEY = '******');

Create File Format Objects

CREATE FILE FORMAT "MULTI_COLUMN_DB"."POC".ORC_AUTO
TYPE = 'ORC'
COMPRESSION = 'AUTO';

Copy Data Into the Target Table

Multiple Column Table

copy into "MULTI_COLUMN_DB"."POC".DEVICEINFO FROM
(SELECT
$1:_col0::string, --id string
$1:_col1::string, --manufacturer string
$1:_col2::string, --serialnumber string
$1:_col3:: timestamp, --etimestamp string
$1:_col4:: string, --model string
$1:_col5:: number, --accuraccy string
$1:_col6::number, --batch_id bigint
date_trunc('DAY',$1:_col3::timestamp) --event_date date
from @ORC_SNOWFLAKE/tblorc_deviceinfo
(FILE_FORMAT=>ORC_AUTO) ON_ERROR = 'continue');

Single Variant Column Table

COPY INTO "SINGLE_VARIANT_DB"."POC”.DEVICEINFO_VAR
from @ORC_SNOWFLAKE/tblorc_deviceinfo
FILE_FORMAT = ORC_AUTO
ON_ERROR = 'continue';

Verify the Loaded Data

select * from "MULTI_COLUMN_DB"."POC".DEVICEINFO;
select * from "SINGLE_VARIANT_DB"."POC".DEVICEINFO;

Tableau Workbook Results

Image for post
Image for post

Establishing a Baseline

Concurrency with Snowflake

Snowflake’s Unique Advantages

Final Thoughts

Need Snowflake Cloud Data Warehousing and Migration Assistance?

Other Tools and Content You Might Like

HashmapInc

Innovative technologists and domain experts helping…

Hashmap

Written by

Hashmap

Innovative technologists and domain experts accelerating the value of Data, Cloud, IIoT/IoT, and AI/ML for the community and our customers http://hashmapinc.com

HashmapInc

Innovative technologists and domain experts helping accelerate the value of Data, Cloud, IIoT/IoT, and AI/ML for the community and our clients by creating smart, flexible and high-value solutions and service offerings that work across industries. http://hashmapinc.com

Hashmap

Written by

Hashmap

Innovative technologists and domain experts accelerating the value of Data, Cloud, IIoT/IoT, and AI/ML for the community and our customers http://hashmapinc.com

HashmapInc

Innovative technologists and domain experts helping accelerate the value of Data, Cloud, IIoT/IoT, and AI/ML for the community and our clients by creating smart, flexible and high-value solutions and service offerings that work across industries. http://hashmapinc.com

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store