4.55 out of 5
4.55
32 reviews on Udemy

Automating ETL – Complete 5 ETL Projects

From beginner to advanced after a few projects. Also free seminar access for lifetime students.
Instructor:
Tim Smith
166 students enrolled
English [Auto-generated]
Introduce ETL effectively
Learn and understand extract
Learn and understand transform
Learn and understand load
Learn the theory behind automation
Learn how to automate the ETL process
Learn advanced techniques in extract, transform and load
See case studies with applied ETL uses for freelancers or business owners

        This course covers a guide to automating ETL through automation techniques, intended for business owners, freelancers or employees seeking to jump ahead of the competition and seeking to minimize data disasters. With the demand for ETL developers increasing (per the Bureau of Labor Statistics and the opportunity to become a data provider (for business owners or freelancers), I highlight methods and architecture to minimize the work that must be done for ETL, so that you can spend more time working on enhancing skills. 

  I offer a ton of free content on my YouTube channel, and suggest students to become familiar with the basics of SQL Server and MongoDB, covered at the below, free playlists: 

  • SQL In Six Minutes (YouTube)

  • The MongoDB 103 (YouTube)

  This course covers: 

  • Understanding the extract process and constructing architecture to automate it.

  • Understanding the load process, when change is necessary with loading, and how to automate it.

  • Understanding different issues we may face in transform, how to minimize re-facing them with re-usable solutions.

  • Understanding advanced techniques in ETL, the theory behind these, and applications with them.

  • Understanding the basic logic, functions, operators and advanced use of PowerShell so beginners and mid-level developers can advance quickly with it.

  • Completing a large assignment that will have you import over 200K of records.

  • Reviewing case studies of applied ETL techniques, especially useful for freelancers adding credibility to their business.

  • Course features extra ETL content with MongoDB and SSIS/SQL Server Data Tools – though neither of these are required for this course.

        To provide maximum learning for students wanting to master ETL skills, whether for business opportunity or employment, the course provides the following material: 

  • Focused video lectures with demonstrations of ETL processes in action, the code/script used to make the process occur, and no distracting facial or background movements.

  • Dynamic syllabus with extra content, such as links to articles and videos to enhance and increase your learning, as well as links to specific recruiters in desperate need for ETL developers (students wanting to become employees) and ways to uncover possible data providing opportunities for business owners.

  • Scripts/code that are used in the video lectures, as well as additional tools that students can learn as they construct their own ETL architecture.

  • Course material that we use in the course, such as files we import during the series.

  • Course projects that you complete as well as your own project to develop.

  • Feedback session for students who meet the requirements where you practice answering questions and presenting your own project.

        Understanding, manipulating and transforming data will only become more important in the future.  Those who can master this through automation will have an advantage over all their competition because when we see the results of our measurement, only then can we make the appropriate changes; this course shows you how to automate those steps. 

Companies seeking ETL talent.  If you are a company seeking junior to senior ETL developers, you can get in touch with me, as I have metrics on students who completed exceptional work in this course and have expressed interest in working with a firm.  In addition, with some of these students, I’ve had feedback sessions where I’ve assessed their skills, helping you identify who may be solid candidates.  You can find my contact information on my YouTube about page, or on my LinkedIn profile.

Introduction

1
Welcome To Automating ETL

Students: this is an about video for people looking at this course and is not required for course completion.

Automating ETL Processes - Extract

1
Extract Introduction

One challenge in the medical world involves extract. How do we automate extracting when the current approach is per process, we receive tons of different formats, and we report (ie: load) in other formats? As we learn how to automate extraction, we can then focus on those "data exceptions" and automate a uniform load process to provide data consistency. See the linked Wikipedia article as an example of HL7 data and consider how, using these extract tools and learning, you would automate this data form.

2
Extract (Part One)
3
Extract (Part Two)
4
Extract (Part Three)
5
Extract (Part Four)

In this brief lecture, we cover how to automate extracting data into MongoDB. You can find the source code in the ETL Extract folder on the Google Drive

6
Extract (Part Five)

How to get extract (or import) help when you use MongoDB.

7
Extract (Part Six)

I've linked the GitHub location to this script below this.

8
Extract (Closing Thoughts)
9
SSIS & SQL Server Data Tools Overview

Overview of tools in SSIS/SQL Server Data tools with a demo importing data and transforming the data.

10
SSIS Project: Aggregate Regional Weather Data

In this project, we import weather data and add a column before importing to a table to find the average temperature in an area.

11
Our Course Project (Extract Homework)

Documents: https://drive.google.com/drive/folders/0B4SM-CYK1EadSWlkZEduaVdmVEE?usp=sharing 

Automating ETL Processes - Load

1
Load (Part One)
2
Load (Part Two)
3
Load (Part Three)
4
Our Course Project (Load Homework)

Documents: https://drive.google.com/drive/folders/0B4SM-CYK1EadSWlkZEduaVdmVEE?usp=sharing 

Automating ETL Processes - Transform

1
Transform (Part One)
2
Transform (Part Two)
3
Transform (Part Three)
4
Transform: (Part Four)
5
Transform (Part Five)
6
Transform (Part Six)
7
Our Course Project (Transform Homework)

Documents: https://drive.google.com/drive/folders/0B4SM-CYK1EadSWlkZEduaVdmVEE?usp=sharing 

8
Our Course Project (Transform Homework Part II)

Note that when running algorithms like the average, standard deviation or two hundred simple moving average, we would prefer to use an application like C# or PowerShell because of the limited speed of row-by-row algorithms in SQL Server.  After having written that, it's important to see the flexibility of what you can do - add columns, algorithms, and transform data.

Documents: https://drive.google.com/drive/folders/0B4SM-CYK1EadSWlkZEduaVdmVEE?usp=sharing 

The Master Class Series

1
Master Class Series Introduction
2
The Master Logic of Extract
3
Data Recon
4
Handling Dirty Excel Delimiters

Note that this addresses Excel data files; for outside Excel files with functions and macros, watch the Master Class video, Excel Non-Data Sheets, because these function may raise errors.

5
Another Excel Extract Method

Note that this addresses Excel data files; for outside Excel files with functions and macros, watch the Master Class video, Excel Non-Data Sheets, because these function may raise errors.

6
Handling Wrapped Exceptions
7
Handling Useless Characters
8
Excel Non-Data Sheets
9
Automate Reporting
10
Automating Email Loading
11
ETL Architecture
12
Your ETL Briefcase
13
Thinking About ETL In Big Data Environments

Optional Exercises and Challenges

1
AI Part 1

The following series is an optional exercise and is not a part of the 5 ETL projects.  Also, this series will be rolled out over time.

2
AI Part 2

The following series is an optional exercise and is not a part of the 5 ETL projects.  Also, this series will be rolled out over time.

3
Coming Parts 3 - 5

The following series is an optional exercise and is not a part of the 5 ETL projects.  Also, this series will be rolled out over time.

4
Python Swapping

The following series is an optional exercise and is not a part of the 5 ETL projects.  Because some environments may require we use other languages, we look at what we can do if we need to swap languages.  In this file, we look at situations where we may be required to swap code or functions into another language.  We also look at a character challenge that's a popular question people ask.

ETL Case Studies

1
ETL Case Studies: Make Money With ETL
2
ETL Freelance Case Study
3
ETL Case Study: Beating the S&P 500 Part I
4
ETL Case Study: Beating the S&P 500 Part II
5
ETL Case Study: Beating the S&P 500 Part III

Live Practice

1
"Lock In Your Bill Price At $12 Now!" Good Deal Or Not?

A live example of importing natural gas data to see if $12 is really cheap for natural gas (it's not).  This is just one of many examples of how easy it is to import a data set quickly, analyze it, and then take an action on it.

Development Practices

1
Development With New Technology
2
The Automation In Automating

Recruitment and Future Recruitment For ETL Positions

1
Recruiting For ETL Positions (Challenge Required)

Email sqlinsix@gmail.com and use your Udemy username, otherwise, I will not add you to the list.  Thanks!

2
2019 Recruitment List
You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.6
4.6 out of 5
32 Ratings

Detailed Rating

Stars 5
14
Stars 4
13
Stars 3
4
Stars 2
1
Stars 1
0
cdd208e7a9ecf632d4e1ca49435880a6
30-Day Money-Back Guarantee

Includes

12 hours on-demand video
5 articles
Full lifetime access
Access on mobile and TV
Certificate of Completion