Automating ETL - Complete 5 ETL Projects

From absolute beginner to advanced after a few projects. Also free seminar access for lifetime students.
4.4 (4 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
49 students enrolled
Instructed by Tim Smith Development / Databases
$75
Take This Course
  • Lectures 43
  • Length 10.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 4/2014 English

Course Description

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.

To provide maximum learning for students wanting to master ETL skills, whether for business opportunity or employment, the course provides the following types of 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.
  • Mp3s recorded independently from the video covering similar and also new content because students are unable to "see what's happening" - additional learning material.
  • 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.

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.

What are the requirements?

  • Mandatory: Computer with Microsoft SQL Server Express (or above) 2008R2 (or above)
  • Mandatory: PowerShell ISE 3.0 (or above)
  • Optional: Visual Studio 2010 (or above)
  • Optional: MongoDB

What am I going to get from this course?

  • 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

What is the target audience?

  • Businesses requiring ETL developers
  • Students interested in ETL positions
  • Employees working in ETL positions
  • Employees interested in ETL positions
  • Individual business owners who want to perform ETL on their own
  • Employers needing ETL developers
  • Recruiters wanting to understand ETL positions

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction
The Power of ETL
Preview
10:50
15:53

We cover the introduction to ETL for business owners and employees seeking ETL positions, including what it involves and what we'll be discussing throughout this course.

03:18

There are many new limitations, so see the above video for how to get what you need.  Thanks!

Section 2: Automating ETL Processes - Extract
04:19

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.

Extract (Part One)
18:05
Extract (Part Two)
37:27
Extract (Part Three)
13:34
08:08

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

02:57

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

15:14

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

Extract (Closing Thoughts)
03:21
Our Course Project (Extract Homework)
09:36
Section 3: Automating ETL Processes - Load
Load (Part One)
20:34
Load (Part Two)
14:31
Load (Part Three)
26:54
Our Course Project (Load Homework)
28:05
Section 4: Automating ETL Processes - Transform
Transform (Part One)
21:53
Transform (Part Two)
16:40
Transform (Part Three)
30:38
Transform: (Part Four)
10:05
Transform (Part Five)
20:03
Transform (Part Six)
08:52
Our Course Project (Transform Homework)
16:58
11:58

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.

Section 5: The Master Class Series
Master Class Series Introduction
02:52
The Master Logic of Extract
08:47
Data Recon
09:13
12:58

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.

16:35

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.

Handling Wrapped Exceptions
20:00
Handling Useless Characters
06:16
Excel Non-Data Sheets
17:28
Automate Reporting
09:46
Automating Email Loading
18:12
ETL Architecture
20:03
Your ETL Briefcase
10:07
Thinking About ETL In Big Data Environments
19:43
Section 6: ETL Case Studies
ETL Case Studies: Make Money With ETL
05:00
ETL Freelance Case Study
05:55
ETL Case Study: Beating the S&P 500 Part I
09:54
ETL Case Study: Beating the S&P 500 Part II
28:32
ETL Case Study: Beating the S&P 500 Part III
13:26
Section 7: Live Practice
27:30

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.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Tim Smith, Senior Developer and Administrator

I work as a database and smart contract architect for FinTek Development.  I am also a published tech writer and video instructor; one of which is my YouTube channel SQL In Six Minutes.

In the more than seven combined years I've worked in tech, the largest opportunity I've seen - whether for a freelancer or potential business owner interested in data - is the opportunity to automate data and build a structure which acts on these data. From hospitals to colleges, few, if any, organization does this well and students who take this course will have obtained the experience that many people learn over the course of a decade or more.

Finally, I am a proponent of the learn-by-doing methodology. I've worked with and interviewed many individuals who had too much theory, often from higher educational institutions, and too little practice. Since tech changes rapidly, people interested in tech must show a willingness to practice often and learn quickly from their practice.

Ready to start learning?
Take This Course