Data Engineering and the Death of Visual ETL

A presentation at Bored in Quarantine Virtual Series by Stewart Bryson

Founder & CEO Red Pill Analytics twitter: @stewartbryson medium: @stewartbryson linkedin: stewartbryson

Data Warehouse Analytics Data Engineering / ETL ANALYTICS Autonomous Data Warehouse @redpilla @stewartbryson

https://medium.com/free-code-camp/the-rise-of-the-data-engineer-91be18f1e603 @redpilla We’re hiring. @stewartbryson

@redpilla @stewartbryson “There’s a multitude of reasons why complex pieces of software are not developed using drag and drop tools: it’s that ultimately code is the best abstraction there is for software.” Maxime Beauchemin, The Rise of the Data Engineer, Jan 20, 2017

What’s so different about ETL? @redpilla @stewartbryson

Code versus Clicks Source control CI/CD Collaboration Code anywhere @redpilla Faster Easier to start Any discipline Business friendly @stewartbryson

Assumption: Code is better, but harder. @redpilla @stewartbryson

Data Warehouses SQL >> GUI (ETL) >> GUI (ELT) Modern Data Platforms SQL >> GUI (ETL) >> GUI (ELT) >> Distributed >> SQL @redpilla @stewartbryson

CREATE OR REPLACE VIEW stage.promotions_vw AS SELECT promo_id, promo_name, promo_subcategory, promo_begin_date, promo_end_date, time_id status_date, status, weekday_flag FROM ( SELECT time_id, CASE WHEN day_name IN (‘Saturday’,’Sunday’) THEN ‘N’ ELSE ‘Y’ END weekday_flag, promo_id, MAX(promo_name) OVER (partition BY promo_id) promo_name, MAX(promo_subcategory) OVER (partition BY promo_id) promo_subcategory, MAX(promo_begin_date) OVER (partition BY promo_id) promo_begin_date, MAX(promo_end_date) OVER (partition BY promo_id) promo_end_date, nvl(status, ‘ongoing’) status FROM stage.promotions_stg partition BY (promo_id) right outer JOIN sh.times ON time_id=status_date) WHERE time_id BETWEEN promo_begin_date AND promo_end_date @redpilla https://www.rittmanmead.com/blog/2009/04/using-a-partition-by-join-to-fill-in-the-gaps

@redpilla @stewartbryson https://www.rittmanmead.com/blog/2009/04/using-a-partition-by-join-to-fill-in-the-gaps

@redpilla @stewartbryson mapping.create(‘MY_PROJECT’, ‘DEMO_FOLDER’, ‘EMPLOYEE_DIM_LOAD’) .datastores([ [name: “HR.EMPLOYEES”], [name: “HR.DEPARTMENTS”], [name: “HR.JOBS”], [name: “PERF.D_EMPLOYEE”, integration_type: “SCD”], ]) .select(“EMPLOYEES”) .filter(‘NAME_FILTER’, [filter_condition: “EMPLOYEES.FIRST_NAME LIKE ‘D%’” ]) .join(‘EMP_DEPT’, [‘DEPARTMENTS’], [join_condition: “EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID” ]) .join(‘DEPT_JOBS’, [‘JOBS’], [join_condition: “EMPLOYEES.JOB_ID = JOBS.JOB_ID” ]) .connect(“D_EMPLOYEE”, [[ attr: “employee_id”, key_indicator: true ], [ attr: “eff_from_date”, expression: “sysdate”, execute_on_hint: “TARGET”], [ attr: “eff_to_date”, expression: “sysdate”, execute_on_hint: “TARGET”], [ attr: “current_flag”, expression: 1, execute_on_hint: “TARGET”], [ attr: “surr_key”, expression: “:RM_PROJECT.D_EMPLOYEE_SEQ_NEXTVAL”, execute_on_hint: “TARGET”],]) .commit() .validate() https://www.rittmanmead.com/blog/2017/03/accelerating-your-odi-implementation-rittmanmead-style/ https://www.rittmanmead.com/blog/2009/04/using-a-partition-by-join-to-fill-in-the-gaps

@redpilla @stewartbryson https://www.rittmanmead.com/blog/2017/03/accelerating-your-odi-implementation-rittmanmead-style/

SELECT first_name, last_name, email, phone_number, hire_date, job_id, manager_id, department_id, salary, commission_pct, eff_from_date, eff_to_date, current_flag FROM EMPLOYEES JOIN DEPARTMENTS using (DEPARTMENT_ID) JOIN JOBS using (JOB_ID) @redpilla @stewartbryson

Code >> GUI >> Code @redpilla @stewartbryson

I would rather write this. @redpilla SELECT first_name, last_name, email, phone_number, hire_date, job_id, manager_id, department_id, salary, commission_pct, eff_from_date, eff_to_date, current_flag FROM EMPLOYEES JOIN DEPARTMENTS using (DEPARTMENT_ID) JOIN JOBS using (JOB_ID) @stewartbryson

@redpilla @stewartbryson We aren’t starting with a blank slate. @stewartbryson

An embarrassment of riches. @redpilla @stewartbryson

Shouldn’t my pipelines just live in my data warehouse? @redpilla @stewartbryson

Data Build Tool (dbt) @redpilla @stewartbryson

Am I feeding other downstream systems? Data warehouses typically aren’t very good at that. @redpilla @stewartbryson

What are some other use cases we might want to consider? @redpilla @stewartbryson

Realtime Events © 2016 RED PILL Analytics

© 2016 RED PILL Analytics Mobile Analytics

@redpilla @stewartbryson Machine Learning

Why Databricks? @redpilla @stewartbryson

Stewart Bryson
@stewartbryson

1 / 44

A funny thing happened on the way to modern, data-driven analytics: we ditched the GUI for ETL development. There are drawbacks to moving data using code instead of clicks, especially for organizations heavily invested in visual tools. But writing code has an upside as well.

In this presentation, we’ll explore the data engineering discipline, and how it compares to conventional ETL. Using Databricks and Snowflake together, we’ll look at classic batch and modern streaming use cases, and consider DataOps requirements around testing and delivery where code really shines. And we’ll do it all inside of an interactive notebook.

Resources

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

Demo Notebook

Data Engineering and the Death of Visual ETL

Link for this presentation:

HTML code for embedding:

Share on social media:

Resources

Demo Notebook