Intro to Analytics Engineering

From Analyst to Engineer

Our program will take anyone who is comfortable writing analytical SQL to a full-fledged analytics engineer.

Course curriculum

In addition to a robust curriculum supported by videos, students will receive 6-months access to their own "sandbox" and tooling (GitHub Codespaces, BigQuery, dbt) to complete the course exercises.

1. Welcome to AEC!
2. Course Overview
3. Exercise: Set up your development environment
4. Exercise: Connect to the aec-students BigQuery project
5. Exercise: Write your first query
6. Example solution: Write your first query
1. Chapter Notes
2. Introduction
3. Moving Around
4. Homeward Bound
5. Creating Files and Folders
6. Vim & Text Editing
7. Environment Variables
8. Dot Files
9. Sourcing a File
10. Exercises: Command line basics
11. Chapter Extras
1. Chapter Notes
2. Introduction to Git
3. What are version control, Git, and GitHub?
4. Why use Git?
5. Building our mental model for Git
6. Level 1: Using git for code stored on your computer
7. Level 2: Hosting a repo on GitHub
8. Level 3: Using branches and pull requests
9. Checkpoint
10. Exercise: Add to a Git repo
1. Chapter Notes
2. What is a merge conflict?
3. How/why does this happen?
4. How do I handle a merge conflict?
5. Option 1: Using the GitHub editor
6. Option 2: Using the command line (recommended)
7. How can I avoid merge conflicts? Or at least make my life easier?
8. What about rebase?
9. How to rebase
10. Why we prefer rebasing
11. You got this!
12. Exercise: Handling merge conflicts
13. Example solution: Handling merge conflicts
14. Exercise: SQL style
15. Chapter Extras
1. Chapter Notes
2. Setting the Scene
3. What is dbt?
4. Using dbt: Install dbt
5. Using dbt: Creating a dbt Project
6. Using dbt: Connecting to a data warehouse
7. Using dbt: Telling dbt to run
8. Using dbt: Changing the way dbt builds your models
9. Using dbt: Creating dependencies between models
10. Using dbt: Understanding DAGs
11. Checkpoint
12. Exercise: Build a dbt project
1. Adding tests to a project
2. Generating Documentation
3. Adding Sources
4. Exercise: dbt tests, docs and sources
1. Chapter Notes
2. What are deployment environments?
3. Case study: docs.getdbt.com
4. What is continuous integration?
5. What is continuous deployment?
6. Applying these concepts to analytics
7. A final word to the wise
8. Chapter Extras
1. Chapter Notes
2. Imagine a query
3. Why model the data?
4. Solution 1: One big table
5. Solution 2: A star (schema) is born
6. Solution 3: Kimball's Approach
7. Solution 3: Kimball's Approach - Facts and Dimensions
8. Solution 3: Kimball's Approach - The Kimball Process
9. Solution 3: Kimball's Approach - Where Kimball starts to break down
10. Our recommendations
11. So, data modeling is just about cleaning data and moving columns around, right?
1. What's the point of modeling data?
2. Why are we calling this out in a lecture?
3. Case study: course_days
4. Case study: payments system migration
5. Case study: Slack messages
6. Where does your team sit?
1. Chapter Notes
2. 1. Understand your stakeholder's request
3. Case study: Engineering team metrics
4. 2. Check the availability of the source data
5. Case study: Engineering team metrics
6. 3. Mock the report you're trying to build
7. Case study: Engineering team metrics
8. 4. Mock the query that would produce this table
9. Case study: Engineering team metrics
10. 5. Iterate on your data models
11. Case study: Engineering team metrics
12. 6. Turn it into a dbt DAG
13. 6a. Declare sources
14. Example solution: Engineering team metrics
15. 6b. Build staging models
16. Example solution: Engineering team metrics
17. 6c. Build (draft) stakeholder models
18. Example solution: Engineering team metrics
19. 7. Validate, iterate and optimize
20. 8. Merge the code!
21. Case study: Engineering team metrics
22. Trust the process
23. Exercise: Build some data models
24. Data modeling miscellany
25. Chapter Extras
1. Chapter Notes
2. Exercise 1: User stitching
3. Exercise 2: Sessionization
4. Exercise 3: LTV curves
5. Example solutions: Advanced SQL Exercises
1. Chapter Notes
2. Introduction & Terminology
3. Data Warehouses
4. Materializations
5. Indexing
6. Distributed Computing
7. Exercises
8. Exercise solution: Views vs Tables
1. Chapter Notes
2. What is Jinja?
3. Rendering values with {{ }}
4. Performing other logic with {% %}
5. Data types
6. Using control structures
7. Exercise: Using Jinja and SQL
8. dbt-specific Jinja
1. Chapter Notes
2. What are dbt packages?
3. How to install a package
4. Exercises: Installing dbt packages
5. My opinions of packages
6. Chapter Extras
1. Chapter Notes
2. Example custom test
3. Exercise: Write a custom test
4. The tests directory
5. Additional exercises
6. Chapter Extras
1. What do we know about materializations so far?
2. What is a materialization?
3. What about the ephemeral materialization?
4. How do I choose a materialization for my dbt model?
5. Other things to know about materializations
1. Setting the scene
2. Configuring incremental models
3. Hurdle 1: What if data arrives late?
4. Hurdle 2: What if data is split across two runs?
5. Hurdle 3: Handling huge data
6. Final thoughts
7. Exercises: Incremental models
1. Setting the scene
2. Why did we do all that?
3. Some terminology
4. So how do we handle this?
5. Idea one: Let's change the source data
6. Idea two: "Snapshot" our daily ice cream flavors report (🙅)
7. Idea three: "Snapshot" our source data
8. The mechanics of snapshotting data
9. dbt Snapshots
10. Replicate this demo
11. Limitations of snapshots
12. Bonus exercise: building models on top of snapshots
1. Chapter Notes
2. Introduction
3. Motivation
4. Getting Set Up
5. Virtual Environments
6. Installing Packages
7. Documenting which packages were installed
8. Exercises: Python environments
1. Chapter Notes
2. Variable Assignment
3. Lists
4. Dictionaries
5. Loops
6. Functions
7. Conditional control flow
8. Exercises: Python basics
9. Solution: Python basics
1. Chapter Notes
2. Printing
3. The python debugger
4. Try Except
5. Chapter Extras/Exercise
1. CLI Lesson
2. Exercises: CLI
1. Python Testing Part 1
2. Python Testing Part 2
3. Exercises: Testing
4. Solution: CLI & Testing
1. Chapter Notes
2. Write Code for Others to Read
3. Simplicity
4. Modularity and Composability
5. Style
6. Exercises: Software engineering principles
7. Topics Not Covered
8. Chapter Extras
1. Survey & Certificate of Completion
1. Glossary
2. Reading Guide

About this course

$1,000.00
Lifetime access to 200 lessons
7+ hours of video content
6-months access to a personal "sandbox"

Discover your potential, starting today

Enroll now

Analytics Engineers Club

From Analyst to Engineer

Course curriculum

Overview and Prework

Command Line Basics

Version Control & Git

Merge Conflicts + SQL Style

dbt 101 - Projects & Models

dbt 201 - Tests, Docs, Sources

Deployment Environments

What is data modeling?

Building business-centric data models

How to design (and build) a data model

Advanced SQL

Data Warehouses

Jinja & Macros

dbt Packages

Custom tests

Materializations

Making your models faster to build (Or: Incremental models)

Snapshotting data

Python Environments

Python Basics

Debugging in Python

Making a CLI

Testing in Python

Software Engineering Principles

Course Completion

Course Extras

About this course

Discover your potential, starting today