Learn the One Skill That Will Save You Hours, and Even Your Job
Many data science pros will tell you: learning SQL was one of the best investments in their career, even though the language is basic and obtuse. See why so many developers become data pros in this 2-hour video tutorial featuring data from the Cassini mission.
Programmers love to abuse databases with their data access tools and then blame SQL for the mess
It's something I've never understood: why do programmers hate SQL so much? Is it aesthetics? If so, I can understand that - SQL isn't terribly pretty to look at or to decipher... but the things that it can do are much more important.
Maybe it's the fact that learning SQL can be a chore - online course typically have you creating DVD stores and learning dry syntax rules that are boring and have absolutely no real-world applicability.
Or maybe it's just what they've heard or read, constantly, from other developers who are just perpetuating one of the most ridiculous ideas in computer science.
You can solve just about every data problem you have by spending a weekend learning SQL.
I won't try to convince you that SQL is awesome - it's not. It's an old, clunky language that's difficult to read and even harder to write given the strange syntax rules. But if you take just a weekend and learn it, your career will change forever.
That's not hyperbole - it's happened to everyone who become the "database person" on their project. Programmer's fear of SQL pushes them to do absolutely anything to avoid dealing with SQL, which includes asking you to solve the problem they're having.
Let them. It makes you extremely valuable.
SQL Skills are Always In Demand
It might be hard to believe but SQL consistently ranks in the top 10 programming languages, even though it's not a programming language! It's ahead of languages like Go, Swift and Ruby - consistently! That's because SQL people are always needed!
People do horrible things with data and spreadsheets, introducing errors, bias and often data that's simply trash. Being able to "sense" these issues and then correct them can save your company millions of dollars, making you look like the badass you are.
The Videos
There are 28 videos in all, clocking in at just over 2 hours. We'll start with the basics, getting to know our data, and then you'll be awash in glorious SQL in no time.
Introduction
Welcome!
You're about to dive into the glorious world of databases and the language of
data - SQL. Lucky you! Our working data set will be the actual data from the Cassini mission,
specifically that of Enceladus, an icy moon orbiting Saturn that is confounding
scientists.
05:17
Installing PostgreSQL
We'll be using PostgreSQL for our SQL and analysis tasks. You can use other
systems and they work just fine - but for this one we'll be using PostgreSQL. We have a bunch of
data to load up and poke through, so let's get rolling quickly OK?
04:30
Extraction
Creating Our Workspace
When working with data you often hear the terms "extraction, transformation and
loading" or "ETL". This is something that data analysis people think about more than say
Application Developers - which I think is a bummer. It's important to know how the data in any
application is going to be used so you can make sure you collect the right stuff!
03:35
Importing the Master Plan
Our first task is to find the exact dates and times when Cassini flew by
Enceladus and made its measurements. We need to create a time window so we can narrow down the
results from the INMS - Cassini's on board "Ion Neutral Mass Spectrometer" - that's the thing that
sniffed space for the chemicals we're looking for. Off we go!
04:12
Inspecting the Master Plan
Now that the data is in the database, let's poke around and see what we have,
using simple select statements and getting to know the Postgres client tool psql.
06:13
Tangent: Working with Dates
Dates and timestamps are core to working with data as you will often find that
if you don't know precisely WHEN something happened, it will become meaningless. Dates mark
changes over time - those changes will often drive business decisions, so you better be
correct!
02:29
Validating the Master Plan
The data in our mission plan looks straightforward but since it's a plan that
is based on dates, we need to jump right into validating those dates. Thankfully for us, Postgres
is outstanding at date and time functionality!
05:56
Creating a Proper Import Script
We don't like errors when running our imports and, unfortunately since we're
human, we're going to have a lot of them. Instead of fixing things piecemeal, it's always better
to just rerun everything.
02:04
Extraction: Summary
We did a lot in this section! We dipped our toes into the lovely world of SQL
and we also learned that we should NEVER trust a spreadsheet!
01:13
Find the Flybys
Flybys: Introduction
We have our tools and our initial extraction ready to go, now let's get to work
finding the flybys of Enceladus! In this section we'll focus on transforming this data into
something we can query with some degree of confidence and then, hopefully, we'll find the exact
time windows for the flybys.
02:11
Concept: Normalization
Structuring a relational database is (typically) all about following the rules
of normalization, called "normal forms". Sounds theoretical, but it's pretty
straightforward.
05:01
Narrowing Our Search
Before we can normalize the mission plan data, we need to understand what's in
the table and how it's related.
04:41
Isolating the Enceladus Data
Our inspection showed us how we can isolate the Enceladus data - now let's do
it!
05:49
Creating the Working Set
It's time to nail down the flybys! We have a plan table full of Enceladus
mission plan goodness - now we just need to figure out how to sift the data correctly.
06:11
Summary of Flyby Work
We learned a few wonderful things in this section - one of the biggest, to me,
is that our job is more than running queries - it's also digging in to the data we have and
ensuring that it's usable.
01:39
INMS Data Import
Introducing the INMS
The fun begins! We have the data we need isolated and we feel good about its
integrity - now we're ready to let it tell us a story. But what story is that? This is where
things get interesting.
04:00
Extracting and Loading the INMS Data
We've done this all before so let's get rolling! The first thing we're going to
do is to load up the INMS and chemical data into Postgres.
03:21
Transformation, Part 1
The INMS CSV is loaded, now we need to create our analysis table using the full
power of PostgreSQL - specifically strong data types with appropriate constraints.
02:00
Concept: Constraints
We've been moving along at a pretty fast clip and it's crucial that we don't go
too fast, skimming over super important concepts like constraints!
06:56
Transformation, Part 2
Now that we understand constraints a little bit more, let's carry on and buff
this table out completely. We'll add a timestamp with time zone so we know when the import
happened and then we'll get into some weird stuff with generated columns!
07:44
Troubleshooting
Wouldn't be any fun if we did everything right the first time, would it?
Imposing rules during transformation is always problematic - which is great! We get to learn about
our assumptions and also dig deeper into the data.
11:03
Analysis
Understanding What We're Looking For
The fun begins! We have the data we need isolated and we feel good about its
integrity - now we're ready to let it tell us a story. But what story is that? This is where
things get interesting.
05:18
Concept: Joins
Bias can creep in anywhere during the analysis process - even in the way you
structure your query and the joins you use.
07:27
Spreadsheet Export
Excel is EVERYWHERE and for good reason - it's simple to use, you can visualize
data and you can even write simple functions and macros. Excel is the powerhouse of the analytical
world and we need to prepare our data to work with it.
05:17
Ethical Considerations
It's important to take a step back from time to time and consider just what it
is you're doing and why. In our case, we're working with one of the most important scientific data
sets of the last century - at least with respect to planetary studies.
04:43
Ship It!
We feel good about the data and we're ready to ship it off! But how? Well that,
friends, is the best part! There are multiple ways to do this but by far the simplest way is with
a simple shell command using PSQL and \copy. You can drop the file anywhere you like - on your
desktop so you can email it, or, what's easiest, is to use a secure file sharing system like
Dropbox, Google Drive, OneDrive - whatever. Nice and fast.
03:44
Summary, and Farewell!
Parting... is such sweet sorry! I hope you were able to follow along with me in
this section - because if you did your SQL and data skills just shot up - yay for you! Thanks for
watching...
01:09
You Will Learn SQL the Way We All Did: Under the Gun
You'll be given the raw data that Cassini gathered during its time orbiting Saturn and passing by Enceladus. You're going to load this data, transform it into a solid relational design using PostgreSQL, and then export it for analysis by the analytical team. When you're done, you'll be able to create tables and views, run analytical queries and tweak data as needed. You'll be on your way to your new life as a data pro