Nabiya Maredia — Learning Roadmap

Active Build the AE foundation · Ship first portfolio project ~160 hrs total · 10 weeks

M01

Module 01 — SQL Fundamentals

4 lessons · checkpoint passed 8/8 · all files committed

✓ complete ▼

📁

module-01-sql/

lessons/ · practice/ · checkpoint/ · assets/ · README.md

view on GitHub ↗

L01

Lesson 01 — SQL Basics

SELECT · WHERE · aggregations · ORDER BY · LIMIT · NULL

✓ done▼

⚡ Key mental model

Execution order: WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. This sequence prevents 80% of SQL mistakes.

✓

SELECT · FROM · WHERE · AND · OR · !=

Column names are structural — no quotes. Values like 'Open' need quotes because they're data not structure.

SELECT ticket_id, status FROM tickets WHERE status = 'Open' AND priority = 'High'

✓

COUNT · AVG · SUM · GROUP BY · HAVING

WHERE runs before groups exist. HAVING filters after grouping. Aggregate functions cannot live in WHERE.

GROUP BY priority HAVING AVG(resolution_hours) > 5

✓

ORDER BY · LIMIT · NULL sort behaviour

NULLs float to top on DESC in SQLite — silent bug. Always add IS NOT NULL for top-N queries. NULL ≠ zero.

WHERE resolution_hours IS NOT NULL ORDER BY resolution_hours DESC LIMIT 3

✓

Text-sort flaw (independently identified)

Alphabetical ordering on 'High/Medium/Low' breaks silently when new labels like 'Urgent' are added. Fix: CASE numeric mapping before ORDER BY.

L02

Lesson 02 — JOINs

INNER · LEFT · RIGHT · FULL OUTER · SVG Venn diagrams

✓ done▼

✓ Conditional pass upgraded to full pass on recap

LEFT vs INNER distinction confirmed under recall conditions after a multi-month gap.

✓

INNER JOIN — only matched pairs survive

FROM jobs INNER JOIN tickets ON jobs.job_id = tickets.job_id

✓

LEFT JOIN — all left rows, NULLs where no right match

FROM jobs LEFT JOIN tickets ON jobs.job_id = tickets.job_id

✓

RIGHT JOIN · FULL OUTER JOIN · SVG diagrams committed

L03

Lesson 03 — Subqueries and CASE

Subqueries in WHERE/FROM · CASE statements · COALESCE

✓ done▼

⚡ Key mental model

Aggregate functions can't live in WHERE because WHERE runs before aggregation. A subquery runs the aggregation first, produces a value, then WHERE compares against it.

✓

Subqueries in WHERE

WHERE resolution_hours > (SELECT AVG(resolution_hours) FROM tickets)

✓

Subqueries in FROM (virtual tables)

SELECT * FROM (SELECT priority, COUNT(*) AS cnt FROM tickets GROUP BY priority) AS sub

✓

CASE statements · COALESCE

CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 ELSE 3 END AS priority_rank

L04

Lesson 04 — SQL Toolkit

LIKE · BETWEEN · DISTINCT · IN · Arithmetic · Dates · Strings · UNION · EXCEPT

✓ done▼

✓

LIKE · BETWEEN · DISTINCT · IN

% = any chars, _ = one char. BETWEEN is inclusive. DISTINCT on multiple columns = unique combinations. IN replaces chained OR.

✓

Arithmetic · ROUND · Date functions · DATE_TRUNC

Use 12.0 not 12 for decimal division. DATE_TRUNC collapses dates to period starts — essential for monthly trend reports.

✓

String functions · UNION · UNION ALL · EXCEPT

UNION deduplicates (slower). UNION ALL keeps all rows (faster — use when duplicates don't matter). EXCEPT removes overlap.

✓

Module 01 Checkpoint — 8 Questions

Timed · unsupported · syntax cheat sheet only

8/8 passed▼

✓ Module 01 officially closed

Passed under fully unsupported conditions. One self-correction: forgot GROUP BY column in SELECT, caught and fixed independently. Dataset: orders + customers.

✓

Q1 LIKE · Q2 BETWEEN · Q3 DISTINCT · Q4 GROUP BY+HAVING

✓

Q5 LEFT JOIN · Q6 CASE · Q7 Subquery · Q8 EXCEPT

M02

Module 02 — Advanced SQL + Data Modeling

Window functions · CTEs · Star schemas · The jump from analyst to engineer SQL

▶ next up ▼

// planned lessons

○

Window functions — ROW_NUMBER · RANK · DENSE_RANK

Rank rows within groups without collapsing them. How you identify the first event per user in a dataset — critical for AI training data deduplication.

○

LAG · LEAD · running totals · moving averages

Compare a row to the one before or after it. How you detect model performance degradation over time — did accuracy drop vs last week?

○

CTEs (Common Table Expressions)

Named subqueries that make complex queries readable and modular. The preferred pattern inside dbt models — you'll use CTEs daily as an AE.

○

Star schemas · fact tables · dimension tables

The data modeling pattern that underpins every data warehouse. Facts are events. Dimensions describe context. This is what you'll design as an AE.

○

Normalization vs denormalization — when and why

Normalized = less redundancy, harder to query. Denormalized = easier to query, more storage. AEs decide which to use based on query patterns.

○

Module 02 Checkpoint — unsupported

Mode Analytics SQL Tutorial (free)DuckDB local env🤖 AI relevance: high

M03

Module 03 — Python for Data

pandas foundations + basic pipeline scripting · not just analysis Python

locked▼

// planned lessons — weeks 5–7

○

pandas — DataFrames · filtering · groupby · merging

Same mental model as SQL — different syntax. Merging DataFrames = JOIN. groupby = GROUP BY. The goal is fluency, not just familiarity.

○

Reading and writing files — CSV · JSON · Parquet

Parquet is the standard format in modern data stacks. You need to know why it's better than CSV for analytics workloads.

○

Data cleaning — handling NaN · type casting · deduplication

Real data is always dirty. This is where 60% of an AE's actual work happens in early-stage pipelines.

○

Basic pipeline scripting — reading from API · writing to file · scheduling concepts

The bridge between data analyst Python and data engineer Python. Writing a script that could run unattended is the key skill shift.

○

Module 03 Checkpoint — unsupported

Kaggle Learn (free)Google Colab (free)🤖 AI relevance: high

M04

Module 04 — Tableau Public

Bridge tool · gets you hired · not your day-to-day as an AE

bridge tool▼

⚠ Bridge tool — understand what this isTableau is what the analysts downstream of you will use to read your data. As an AE you build the pipelines that feed Tableau — you don't live in it. This module gets you hired faster. It is not the career you're building. One module, done right, then we move on.

// planned lessons — weeks 8–9

○

Connecting data sources · CSV · SQL output from DuckDB

○

Building charts · bar · line · scatter · filters · calculated fields

○

Dashboard layout · publishing to Tableau Public profile

○

3 published dashboards live on Tableau Public

Hiring managers can evaluate these without reading a line of code. That's the entire point of this module.

Tableau Public (free)Official Tableau training (free)

M05

Module 05 — Phase 1 Capstone

First end-to-end portfolio piece · closes Phase 1

locked▼

// planned deliverables — week 10

○

Choose a real public dataset from Kaggle

○

Clean and model with Python + pandas

○

Advanced SQL queries including window functions

○

Tableau dashboard published to Tableau Public

○

Full GitHub commit history with structured README

Kaggle datasetsDuckDBTableau PublicGitHub

Upcoming dbt · Cloud · Pipelines · AI data concepts = full AE stack 2 hrs/day · ~160 hrs total

M07

Module 07 — dbt Core

The defining AE tool · free certification · version-controlled data models

locked▼

// planned lessons — months 5–6

○

dbt project structure · models · refs · sources

How dbt organizes SQL transformations into a version-controlled, documented data pipeline.

○

Staging → intermediate → mart layer architecture

The canonical AE data model structure. Raw data flows in, clean business-ready data flows out. You design the layers in between.

○

dbt tests — schema tests · custom data quality tests

Automated tests that run every time data transforms. How AI teams catch corrupt training data before it reaches a model.

○

dbt documentation + lineage graph

Auto-generated docs showing every table, column, and how data flows between them. This is what you show in interviews.

○

dbt Fundamentals Certification (free · recognized by employers)

dbt Learn (free)dbt Fundamentals cert (free)🤖 AI relevance: critical

M08

Module 08 — BigQuery

Cloud data warehouse · where AEs actually work · free sandbox

locked▼

// planned lessons — month 7

○

BigQuery sandbox setup · public datasets · UI navigation

○

Partitioning + clustering — query cost and performance

In BigQuery you pay per byte scanned. Partitioning and clustering are how you make queries cheaper and faster at scale.

○

Connect dbt to BigQuery · run full transformation pipeline

The moment your local dbt models run against a real cloud warehouse. This is the AE stack working end-to-end.

○

BigQuery ML basics — what it can do and when to use it

BigQuery sandbox (free)🤖 AI relevance: high

M09

Module 09 — Python for Pipelines

ETL scripts · API ingestion · orchestration concepts · engineer-level Python

locked▼

// planned lessons — month 7

○

Reading from REST APIs · pagination · error handling

Most real data pipelines ingest from APIs. Pagination and error handling are what separate a script that works once from one that runs unattended.

○

Writing data to BigQuery from Python

The ingestion layer of your data stack. Raw API data → Python cleaning → BigQuery → dbt transformation.

○

Orchestration concepts — Prefect or Airflow basics

How you schedule and monitor pipelines in production. You don't need to master these — you need to understand what they do and why they exist.

○

Logging · alerting · pipeline observability basics

Prefect free tierGoogle Colab🤖 AI relevance: high

M10

Module 10 — Data Quality + Modern Data Stack

What makes data trustworthy · ELT vs ETL · the full picture

locked▼

// planned lessons — month 8

○

Data contracts — what they are and why they matter

A formal agreement between data producers and consumers about what the data will look like. Breaking a data contract breaks downstream models and dashboards.

○

ELT vs ETL — why the modern stack uses ELT

ETL transforms before loading. ELT loads first, transforms inside the warehouse using dbt. Understanding this distinction is fundamental to AE work.

○

Data lineage — tracing where data comes from and where it goes

○

The modern data stack end-to-end — Fivetran → BigQuery → dbt → Tableau

You may not use all these tools at every job, but understanding how the full stack connects makes you readable in every interview.

dbt Blog (free)Locally Optimistic (free)🤖 AI relevance: critical

M11

Module 11 — AI Data Concepts

The layer that separates AE from AI AE in every interview

locked▼

// planned lessons — month 8

○

Training data quality — what makes data good vs bad for AI

Bias, label quality, class imbalance, distribution shift. The data problems that cause AI models to fail in production.

○

Feature stores — what they are and why they exist

Centralized repositories for ML features. Prevents the same feature being calculated differently by different teams.

○

LLM evaluation datasets — how they're built and validated

○

Model monitoring — data drift · concept drift · detection methods

How you know when a model's performance is degrading — and how to trace it back to a data problem.

○

Data versioning with DVC — reproducible ML pipelines

Eugene Yan blog (free)Hugging Face docs (free)🤖 AI relevance: critical

M12

Module 12 — Full AI Data Pipeline Capstone

The project that gets interviews · end-to-end · AI angle included

locked▼

// planned deliverables — month 9

○

Raw public dataset → Python ingestion + cleaning pipeline

○

dbt models with staging → mart layers + full test suite

○

BigQuery as the warehouse · dbt lineage graph documented

○

Tableau dashboard published to Tableau Public

○

AI training data analysis section in README

What would need to change to make this dataset suitable as AI training data. This one section makes your capstone different from every other junior AE's.

Python + dbt + BigQuery + Tableau + GitHub🤖 AI relevance: critical