Grade Breakdown

• The grade breakdown in the class is fairly straightforward

Homework

• Homework due the night before class at midnight
• Will be submitted through GitHub Classroom
• Problems will commonly require a mix of written and coded pieces:
  • Both will have either template files that you can add to and re-upload, or clear instructions as to what filename you should save your code or resulting data as.
• 72 cumulative, unpenalized hours allotted over the semester for late submissions
  • Late work past that point will drop by 20% credit each day
  • Talk to me if something major comes up, and we can make a plan

Project

• I continue to grow the project in this course, and that trend will continue this semester
• Plan to incorporate:
  • Data acquisition
  • Data pipelines (ETL)
  • Database modeling
  • Data Serving
• Projects are linked to partners, though certain parts will be more or less independent
• At the end of the semester, you and your partner will take the culmination of your efforts and then make a presentation on it

Midterm Video

• I have done away with exams in this course
  • Oral exams were most effective and great practice for technical interviews, but I can’t do them with 45 students
• Instead you will prepare a midterm video
  • There will be a list of skills and techniques to showcase
  • You choose a data set that will allow you to demonstrate those skills, and then prepare a video showcasing those skills and explaining what you are doing
  • Videos should be 10-15 minutes.
  • Happens in lieu of homework, so you have a week to work on it. Can practice as much as you want, split it across multiple takes, etc.

Expectations

• Most of you will not end up being data engineers
• Trying to teach you “enough to be dangerous”
  • If you end up in a data scientist position, you will almost assuredly need to interface and work with your engineer
  • If you end up in charge of data at a smaller enterprise, your responsibilities may well entail both the work of a data scientist and the work of a data engineer
  • It is always useful in the modern era to understand how to store information

Varied opinions!

The Data Engineer’s Role

The Five V’s

• Data Engineers need to contend with multiple facets of data

  Volume:

  the quantity of data points

  Variety:

  the type and nature of the data (text, images, video, audio)

  Velocity:

  the speed at which new data is generated and processed

  Veracity:

  how trustworthy the sources are

  Value:

  how actionable the data is

The Data Software Landscape 2020

The Data Software Landscape 2024

Sampling the Landscape

• Clearly, I neither know all of those nor could teach them in a semester
• My goal for you in this class is three-fold:
  • Understand the big picture
    • Where do many of these pieces of software fit in a pipeline or day-to-day work?
  • Understand what some options are in common use-case categories
    • What is out there for databases?
    • What is out there for data ingestion?
  • Gain experience using a few representative pieces of software

Alternative Storage Structures

Languages to Know?

SQL

The most common interface for databases and data lakes

Python

The bridge language between data engineering and data science

JVM languages (Java or Scala)

Prevalent for common open source Apache projects like Spark and Hive

Bash

The default text interface for Linux operating systems

The Data Engineering Lifecycle

• We are going to be largely concerned with the area between generation and final usage
• This three-step process is commonly given the name ETL, standing for Extract, Transform, Load

When to Organize

• There tend to be two different paradigms for when to run ETL operations
• Batch processing
  • ETL operations are run on a particular interval, and all the most recent data is processed at once as a single “batch”
  • Can take a while to run, depending on the size of the batch
• Stream processing
  • ETL operations are run on the data as it is generated, in real-time
  • Speed depends on how fast new data is being generated

Group Activity

• Use LinkedIn and perform a job search on “Data Engineer” jobs in a US metro area of your choice. Identify 3 returned job listings per group member to investigate in detail (so a group of 3 should look at 9 total postings). Across your 3 listings, identify the following:
  • Goals/objectives/purpose/responsibilities of the position
  • Qualifications required / preferred
  • Skills desired
• Spend 15 minutes on independent investigation and then 15 minutes on group synthesis
  • Produce a concise group-wide bullet-point list summarizing a “competitive applicant” in the field of Data Engineering based on the above 3 dimensions
  • We’ll summarize at the end what people found

Dinner Break!

• Take 30 minutes to relax, grab a bite, and stretch your legs!

How to store data?

• There are many different ways information can be stored, with varying trade-offs!
• Suppose you wanted to keep track of your friend’s birthdays

Options!

First Name,Last Name,Birthday
Frank,Stein,4/2/2000
Tessa,Loch,8/23/2003
Bobby,Wolf,12/14/2005

[
  {"First Name": "Frank",
   "Last Name": "Stein",
   "Birthday": "4/2/2000"},
  {"First Name": "Tessa",
   "Last Name": "Loch",
   "Birthday": "8/23/2003"},
  {"First Name": "Bobby",
   "Last Name": "Wolf",
   "Birthday": "12/14/2005"}
]

The Plot Thickens…

• Suppose now you’d also like to keep track of what courses they are currently taking, and what times those courses are held
• Suppose each is taking 2-3 classes, some of which overlap
• This significantly complicates both example storage methods!

Example Table Storage

• We can’t store tables inside of tables, so we usually need to duplicate information over multiple rows:

• Duplication is generally bad!

Example JSON Storage

• We still have duplication issues with JSON as well.

[
  {"First Name": "Frank", "Last Name": "Stein", "Birthday": "4/2/2000",
   "Classes": [
     {"class": "CS151", "day": "MWF", "time":"1:00pm"},
     {"class": "MATH256", "day": "MWF", "time":"9:00am"}
    ]},
  {"First Name": "Tessa", "Last Name": "Loch", "Birthday": "8/23/2003",
   "Classes": [
     {"class": "CS151", "day": "MWF", "time":"1:00pm"},
     {"class": "IDS236", "day": "TTh", "time":"1:00pm"},
     {"class": "HIST123", "day": "MWF", "time":"12:00pm"}
    ]},
  {"First Name": "Bobby", "Last Name": "Wolf", "Birthday": "12/14/2005",
   "Classes": [
     {"class": "IDS236", "day": "TTh", "time":"1:00pm"},
     {"class": "MATH256", "day": "MWF", "time":"9:00am"}
    ]},
]

Relational Tables

• One solution to this issue is realizing that we are trying to actually keep track of two things: friends and classes.
• So we break things up into two tables, and then create relationships between them
• This is the core of what occurs in a relational database!

Viewing Relational Databases

• In general, you’d probably use a third table to represent all the linkages
• Unique id keys are used to connect the different tables

Relational Databases and SQL

• SQL is a language that allows you to define and query relational databases
• These days it is pronounced “ESS-CUE-ELL”, though you may still hear it occasionally referred to in an older form of “SEQUEL”
• Does not technically stand for “Structured Query Language”!
  • Is not structured in a programming sense
  • Does far more than just query
  • Is not Turing complete in a language sense
• Comes in several variants, though the core standards are governed by ANSI and ISO, so none stray too far from the standards
  • We’ll be focusing on a variant called PostgreSQL this semester

SQL Servers

• PostgreSQL (Postgres for short) operates on a server model, where clients contact the server and ask it to manipulate or query a particular database
  • Multiple databases can exist on the server at a time
• Works well for large distributions, but you can also just run a local server on your computer, where you are the only client that can connect
• Several ways you can interact with the server once it is running
  • From a terminal prompt (Ch 18)
  • Using pgAdmin as detailed in the text
  • Using an alternative database client like Beekeeper Studio (Community Edition)
    • This is my absolute favorite SQL client at the moment, and I’d highly recommend it. It is what I’ll showcase much of the semester

Adding a New Database

• SQL has commands to help with the administration of the database, as well as creating, manipulating, and querying specific tables within the database

• New installs will come with a database already existing (called postgres), but it is a good idea to create a new one and leave the default untouched.

• Can run SQL commands either by opening the terminal or running the command in a query.

  • The commands look the same regardless

• To create a new database, the syntax is:

  CREATE DATABASE |||name of database|||;

Adding a new Table

• Creating tables is one of the more fundamental actions you may need to take with a database
• Need to specify several things upon creation
  • The name of the table
  • The names of the columns and associated data types

CREATE TABLE |||table name||| (
    |||column_name₁||| |||type₁|||,
    |||column_name₂||| |||type₂|||,
    etc...
);

A Note on Syntax

• SQL in general requires no special formatting in the form of capitalization or tabbing
• Following some conventions can help make your commands easier to read though:
  • Use uppercase for SQL keywords
  • Use lowercase and underscores for table or column names
  • Indent clauses and blocks of code for ease of readability
• A semicolon does indicate the end of a command though
• Text and dates will need single quotes, numbers do not

Adding Values to a Table

• Your tables are initially empty!
• You add data to the table by inserting new values into the columns
• New rows are concatenated to the end of the table
• Need to include an entry for each column (rows can’t be unequal in length)

INSERT INTO |||table name||| (|||column₁|||, |||column₂|||)
VALUES ('row1a', 'row2a'),
       ('row1b', 'row2b'),
       ('row1c', 'row2c');

Query Time

• The prime use of SQL for individuals not in charge of managing a database is using it to query the database for information

• Queries return only a subset of information from the database, and don’t alter the database in any way

• The keyword that begins any query is SELECT, followed by:

  • The column name(s) you would like information from, and
  • The table you want to get the information from

• Basic syntax then could look something like:

  SELECT |||column₁|||, |||column₂||| FROM |||table name|||;

• An asterisk *, can stand in for the column names to stand for “all the columns”

  SELECT * FROM |||table name|||;

You are Distinct

• In many instances, a particular column may have duplicate entries
  • Each row should be unique, but if looking at just a single column, there very well could be duplicates
• The DISTINCT keyword allows you to only display the unique values that appear in a column
  • Appears before the names of the desired columns

  SELECT DISTINCT |||column||| FROM |||table name|||;

• Can be applied to multiple columns at a time
  • Then interpreted as “For each X in the table, what are all the Y values?”

  SELECT DISTINCT |||column₁|||, |||column₂|||
  FROM |||table name|||;

Reversing Entropy

• Sometimes patterns can be more evident if some ordering is applied to the query results
  • The ordering only applies to the query, the original data is untouched
• If you do not specify an ordering, you have no guarantee about what order results will come out in!
• SQL provides the ORDER BY keyword to indicate a column to use for ordering
• Can use ASC (the default) or DESC to specify the direction of ordering
• Can also order by multiple columns (separate by comma)

SELECT |||column₁|||, |||column₂|||
FROM |||table name|||
ORDER BY |||column₂||| DESC;

Ordering Basics

Filtration

• Especially in tables with thousands to millions of rows, you likely don’t want all of the information pertaining to a column

• Instead it makes sense to filter or only return rows that meet certain criteria

• SQL’s WHERE keyword provides exactly this functionality:

  SELECT |||column||| FROM |||table name|||
  WHERE |||some condition|||;

• Each condition is some sort of comparison check, which could be summarized as a true / false question

Comparison Operations

Pattern Matching

• LIKE and ILIKE differ only in whether capitalization matters in the match
  • LIKE is capitalization sensitive, ILIKE is not
• Each can use several special characters in the desired pattern
  • % is a wildcard matching one or more of any character
  • _ is a wildcard matching just a single character
  • If you want to actually match off one of these characters, you need to “escape” it with a backslash (\_ or \%)

Conditional Combinations

• Often, you might need to filter on several or more complicated conditions
• SQL also has the boolean AND and OR operators that you can use to stitch together multiple conditions
• Order of operations is left to right, so surround terms in parentheses if you need them evaluated earlier

SELECT *
FROM |||table name|||
WHERE |||column₁||| LIKE 'F%' AND
      (|||column₂||| > 50 OR |||column₃||| <= 10);

Reaching Your Limit

• Often times, especially if sorting, you may not even be interested in all the results
• Maybe you just want the first few, or even just the first result
• You can control the maximum number of rows returned using LIMIT
• Comes last, after all the other keywords

SELECT * FROM |||table_name|||
WHERE |||some_condition|||
ORDER BY |||some_column|||
LIMIT 5;

Example

Introduce yourself to a neighbor and then work together to construct a query to solve the following situation:

• Suppose you are particularly short and thus can only access cereals on the first two shelves. You’d like to grab the cereal with “Cheerios” somewhere in its name that has the highest amount of sugar per serving. Construct a query that could help you figure out what cereal you should be grabbing!