How to execute the T in ELT

A presentation at Snowflake GROUP BY Data Heroes by Daan Bakboord

How to execute the T in ELT DAAN BAKBOORD, MANAGER DATA & ANALYTICS at PONG BV I JULY 2020 © 2020 Snowflake Inc. All Rights Reserved

DAAN BAKBOORD - DAANALYTICS © 2020 Snowflake Inc. All Rights Reserved

© 2020 Snowflake Inc. All Rights Reserved

GOVERN DATA © 2020 Snowflake Inc. All Rights Reserved

SQLDBM – DATABASE MODELING • Database Modelling • Project Conversion • DB Documentation • Compare Revisions • Forward Engineering • Snowflake Direct Db Connection • Reverse Engineering • Bring your database “Without writing a single line of code” © 2020 Snowflake Inc. All Rights Reserved

SQLDBM – PARTNERCONNECT “Try for yourself!” © 2020 Snowflake Inc. All Rights Reserved

LOADING DATA © 2020 Snowflake Inc. All Rights Reserved

PARTNER ECO SYSTEM © 2020 Snowflake Inc. All Rights Reserved

LOADING DATA • Snowflake • Manual • Snowpipe • PartnerConnect • Fivetran • Matillion • Informatica © 2020 Snowflake Inc. All Rights Reserved “SQL is THE Language to Query Data”

TRANSFORMING DATA © 2020 Snowflake Inc. All Rights Reserved

TRANSFORMING DATA • Cleansing • formatting • validating • Combining • joining • Integrating • Aggregating • summarizing • cross-column calcs • Separating • filtering • column-splitting © 2020 Snowflake Inc. All Rights Reserved

FIVETRAN TRANSFORMATIONS Prepare your Data for Reporting • Easy to use • Data analysts’ language of choice: SQL. • Create Table • Name & Schema • Schedule of choosing © 2020 Snowflake Inc. All Rights Reserved

DBT; THE T IN ELT • Data already in DWH • Logic in SQL • Transformation in batch • Transformations are idempotent DBT is NOT an ETL/EL-T Tool! © 2020 Snowflake Inc. All Rights Reserved

DBT IN ACTION • Take Code • Compile it to SQL • Run against the Database © 2020 Snowflake Inc. All Rights Reserved

FIVETRAN & DBT • Fivetran • (E) – Extract from Data Source • (L) – Load Data into Cloud DWH • DBT • (T) Transform Data in Cloud DWH EL-T using the Best of both Worlds! © 2020 Snowflake Inc. All Rights Reserved

LOOKER’S APPROACH TO DATA MODELLING LookML - Use, Re-Use and Recycle © 2020 Snowflake Inc. All Rights Reserved

LOOKER DERIVED TABLE • Native – LookML • SQL-based • Pesisted • Stored outside Looker • Refreshed on schedule © 2020 Snowflake Inc. All Rights Reserved

FIVETRAN & LOOKER • Looker Blocks® • Library of LookML’s • Components of Business Logic • Reusable & Customizable © 2020 Snowflake Inc. All Rights Reserved

GETTING STARTED © 2020 Snowflake Inc. All Rights Reserved

SNOWFLAKE MODERN DATA ARCHITECTURE RAW ANALYTICS DATABASES LOADING TRANSFORMING ANALYZING LOADER TRANSFORMER ANALYZER LOAD TRANSFORM ANALYZE VIRTUAL WAREHOUSES ROLES USERS © 2020 Snowflake Inc. All Rights Reserved

CONCLUSION • Ease of Use • DwaaS • Flexibility • Snowflake eco-system “SQL is THE Language to Query Data” • • ANSI-SQL Fully ACID compliant © 2020 Snowflake Inc. All Rights Reserved “Simply Load and Query Data”

© 2020 Snowflake Inc. All Rights Reserved

Daan Bakboord
@daanalytics

1 / 22

The founders of Snowflake wanted a Cloud Data Warehouse which enables a user to; “Simply Load and Query Data”.

Loading Data into Snowflake is easy. Sometimes this data needs to be transformed. In this session I will show various possibilities to transform data in Snowflake. These possibilities vary from internal Snowflake functions, to ELT-tools to other 3rd party options.

Resources

The following resources were mentioned during the presentation or are useful additional information.

Snowflake GROUP BY Data Heroes

GROUP BY is an opportunity for data practitioners around the world to come together virtually and learn how some of the most engaged users are leveraging Snowflake. Dive deep into use cases, participate in workshops, and discuss popular Snowflake features with our amazing Data Heroes.