Lectures

This lecture covers the course mechanics for DATA 503: Fundamentals of Data Engineering.

Jan 12, 2026

Lecture 01: Introduction to Data Engineering

This lecture covers the introduction to data engineering.

Jan 12, 2026

Lecture 03: Data Types, Importing Data, and SQL Math

This lecture covers the data types, importing data, and SQL math.

Jan 19, 2026

Lecture 03-1: SQL Joins

This lecture covers SQL joins using the Blockbuster Bend database. We explore inner joins, outer joins, cross joins, and self joins with real examples from film rental data.

Jan 26, 2026

Lecture 03-2: Storytelling with Data

This lecture covers storytelling with data and introduces the project matchmaking workshop. We explore how data engineering pipelines enable compelling narratives by combining disparate data sources, then participate in a structured activity to form project teams.

Jan 26, 2026

Lecture 04-1: Normalization and Data Modeling

This lecture covers database normalization and data modeling fundamentals. We explore the normalization process from messy flat files to well-structured relational schemas, practice with the Dunder Mifflin employee dataset, explore database design for the Oregon Turtle Conservation project, and learn to create Entity-Relationship diagrams using draw.io.

Feb 2, 2026

Lecture 05-1: Table Design and Constraints

This lecture covers the practical implementation of database table design in PostgreSQL. After learning to normalize data and design relational schemas, we now focus on building those tables with proper constraints. Topics include naming conventions, primary keys (natural vs surrogate), foreign keys and referential integrity, CHECK constraints, UNIQUE constraints, NOT NULL constraints, modifying tables with ALTER TABLE, and speeding up queries with indexes. A music catalog dataset serves as the running example throughout.

Feb 9, 2026

Lecture 05-2: Inspecting and Modifying Data

This lecture covers the practical skills of inspecting, cleaning, and modifying data in PostgreSQL. After building normalized tables with constraints, we now learn to migrate data from a staging table into the final schema. Topics include auditing data quality, ALTER TABLE for schema changes, UPDATE for fixing data, DELETE for removing rows, backup strategies, and transactions for safe modifications. The music catalog dataset from the previous lecture serves as our running example.

Feb 9, 2026

Lecture 05-3: Pet Clinic Assignment

This lecture introduces the Pet Clinic assignment, where students apply the entire data engineering pipeline end-to-end: importing messy CSV data, normalizing it, building tables with constraints, auditing and fixing quality issues, migrating data, and verifying the result. The assignment also requires a Data Design Journal documenting decisions, assumptions, and reflections throughout the process.

Feb 9, 2026

Lecture 06-1: Data Science at the Command Line

This lecture introduces data science at the command line, covering the OSEMN model for data science, setting up your environment with Docker, and essential Unix concepts including the shell, command-line tools, pipes, redirection, and file management.

Feb 16, 2026

Lecture 06-2: Command Line Skills for Data Detectives

This supplemental lecture reviews core command-line navigation (cd, ls, paths) with visual diagrams, then builds hands-on proficiency with the text-processing commands needed for the Command Line Mystery homework: grep, sed, awk, head, tail, cat, wc, sort, uniq, cut, pipes, and redirection. Students follow along inside the lucascordova/dataeng Docker container using a practice dataset downloaded with curl.

Feb 25, 2026

Lecture 07-1: Web Data Pipelines

This lecture covers building data engineering pipelines from web data. We start with web scraping using the Web Scraper Chrome extension, scrape vinyl record data from Discogs, then deploy a full pipeline using Docker on Railway: database setup, data ingestion, transformation, dashboards, API bridge, and developer portal.

Feb 25, 2026

Lecture 08-2: Advanced Statistics in SQL

This lecture covers statistical functions built into SQL, including correlation, regression, ranking, and window functions. We use real U.S. Census and export data to explore how SQL can answer statistical questions without ever leaving the database. Based on Chapter 11 of Practical SQL, 2nd Edition.

Mar 2, 2026

Lecture 08-1: Web Scraping

This lecture covers web scraping using the Web Scraper Chrome extension. We scrape vinyl record data from Discogs, including artist names, album titles, and ratings from detail pages using a two-level scraping approach with Element (scroll) selectors.

Lecture 08-2: Web Data Pipelines

This lecture covers building data engineering pipelines from web data. We deploy a full pipeline using Docker on Railway: database setup, data ingestion with api2db, transformation, dashboards with Grafana, API bridge with PostgREST, and a developer portal with Swagger.

Lecture 08-2: Web Data Pipelines

This lecture walks through building a real-time data ingestion pipeline from scratch. We set up cloud infrastructure on Railway, ingest weather and traffic incident data from public APIs on a 5-minute polling schedule, store raw JSON in PostgreSQL, and transform it into structured tables for analysis.

A True Story by Professor Cordova

The Wednesday Incident

Lecture 09-0: Web API Pipelines with Railway

This lecture introduces cloud-based data pipelines using Railway. We set up a shared PostgreSQL database, explore public web APIs, and deploy automated services that collect weather and flight data over Portland on a schedule. This is the infrastructure that powers your group project and sets up the data we will analyze in the next lecture.

Mar 11, 2026

Lecture 09-1: Advanced Statistics with Flight and Weather Data

This lecture takes the flight and weather data we have been collecting for two weeks and transforms it from raw JSON blobs into a normalized relational schema. We then apply advanced SQL statistics – correlation, regression, ranking, and window functions – to answer the question: does weather affect air traffic over Portland? Based on Chapter 11 of Practical SQL, 2nd Edition.

Mar 11, 2026

Lecture 11-1: Statistical Functions in SQL

This lecture covers statistical functions built into PostgreSQL. We explore correlation, linear regression, variance, standard deviation, ranking with window functions, rate calculations, and rolling averages. Based on Chapter 11 of Practical SQL, 2nd Edition.

Mar 16, 2026

Lecture 10-2: Working with Dates and Times

This lecture covers working with dates, times, and time zones in PostgreSQL. We explore date/time data types, extraction functions, time zone handling, date math, and interval calculations. We apply these concepts to NYC yellow taxi trip data and Amtrak train schedules, then connect them to the pipeline work you have been doing all semester. Based on Chapter 12 of Practical SQL, 2nd Edition.

Mar 16, 2026

Lecture 10-3: Advanced Query Techniques

This lecture covers advanced SQL query techniques: subqueries in WHERE, FROM, and SELECT clauses, correlated subqueries, EXISTS, LATERAL joins, Common Table Expressions, cross tabulations, and CASE expressions. We apply these patterns to Census, survey, and temperature data. Based on Chapter 13 of Practical SQL, 2nd Edition.

Mar 16, 2026