Your First 30 Days as a Data Engineer

01

When you finish, you can

You can query an unfamiliar schema, put it on a schedule, and model it.

Day 30 A number you would defend

02

The plan, 4 weeks

Ordered by what each one buys you, not by topic. Every step says why it is here.

1. Week 01 · ~2h

   Read a table and count it correctly

   You can say what one row means, and group it without losing any.

   Skip this if you already write conditional sums and safe ratios from memory.

   1. SectionReading a table: rows, columns & grain5 minWhat does one row mean? Ask it of every table.→
   2. SectionThe shape of a query4 minThe clauses run in a different order than you write them.→
   3. Worked exampleFirst look at the orders table10 minin the studioYour dataset for two weeks. Read the reference answer first.
   4. ExerciseRead the status distribution10 minin the studioWhat an engineer does to a new table before trusting it.
   5. ExerciseRevenue and orders by status10 minin the studioOne table, one GROUP BY. Every report is this shape.
   6. ExerciseOrders and revenue by month12 minin the studioGroup by a date. Which month does the 31st belong to?
   7. SectionNULL & three-valued logic6 minNULL is not zero, and comparing to it is never true.→
   8. ExerciseCustomers per region, including unassigned12 minin the studioNotice which customers a careless GROUP BY drops.
   9. ExercisePaid vs refunded per buyer15 minin the studioTwo metrics in one pass. The most reusable trick there is.
   10. ExercisePaid rate by country15 minin the studioInteger division makes a rate look reasonable and be wrong.
   11. ExerciseBest selling products, paid only15 minin the studioWHERE filters rows, HAVING filters groups. Get it wrong here.

2. Week 02 · ~2h

   Join two tables without inflating the number

   You can predict a join’s row count before you run it.

   Skip this if you already know when a join will multiply your rows.

   1. ExerciseBuyer country on each shipped order12 minin the studioOne order, one buyer. Nothing multiplies yet.
   2. ExerciseLine items per order12 minin the studioOne order, many items. Watch the row count.
   3. ExerciseRevenue per customer without double counting15 minin the studioYour total will come out too big. That is the lesson.
   4. SectionFan-out: when a join doubles your metric6 minThe name for what just happened to your total.→
   5. SectionGrain & cardinality: predicting row counts6 minHow to see it coming next time.→
   6. ExerciseFind duplicated returns12 minin the studioThe check that catches it first: is the key unique?
   7. ExerciseReturns per order, including none15 minin the studioLEFT JOIN keeps the empties. Watch what COUNT does with NULL.
   8. ExerciseCustomers who never ordered12 minin the studioFinding what is not there. Half of every interview.
   9. SectionSemi & anti joins: existence without multiplying5 minExistence questions do not need the other table’s columns.→
   10. ExerciseOrders that were never returned15 minin the studioSame question, NOT EXISTS. One spelling survives NULLs.
   11. ExerciseJoin three tables into an order detail15 minin the studioThree tables. The row count stops being guessable.
   12. ExerciseEach customer's largest order15 minin the studioOne row per customer out of many. The first hard shape.

3. Week 03 · ~3h

   Put the query on a schedule and make it safe to re-run

   You can schedule a load and run it twice without changing the answer.

   Skip this if you can already say what makes a write safe to repeat.

   1. SectionWhat a data pipeline actually is5 minThe job, now that you can write the middle of one.→
   2. SectionFollow one row: a click becomes a number6 minOne row, end to end. Keep this picture.→
   3. Worked exampleDaily orders ETL30 minin the studioAlready wired. Say what each box does, then reveal.
   4. ExerciseYour first pipeline30 minin the studioBlank canvas, simplest job. Get it running end to end.
   5. ExerciseCounting it once30 minin the studioYou re-ran it and the numbers doubled.
   6. SectionIdempotency: safe to repeat5 minThe name for what just went wrong.→
   7. SectionDeduplication & idempotency keys5 minHow to make a write safe to repeat.→
   8. ExerciseRun it for last Tuesday30 minin the studioOne day, six weeks ago, without disturbing its neighbours.
   9. ExerciseWhere did the rows go?30 minin the studioCounts do not match upstream, and someone is waiting.

4. Week 04 · ~2h

   Design the tables instead of querying someone else’s

   You can turn a paragraph of English into tables, and defend the grain.

   Skip this if you can already draw an ERD and state every grain in it.

   1. SectionWhat a data model actually is4 minThree weeks using other people’s models. Now make one.→
   2. SectionWhat a model decides5 minWhat gets expensive to change once data has landed.→
   3. Worked exampleMarketplace core entities25 minin the studioWeek one’s marketplace, drawn as a model. Read it first.
   4. SectionFrom sentences to an ERD6 minEnglish into entities, relationships, cardinality. The mechanical part.→
   5. ExerciseFood delivery orders30 minin the studioBlank canvas, on a domain you already understand as a customer.
   6. ExerciseRide hailing trips30 minin the studioDriver and rider are both users. Argue for your answer.
   7. SectionWhat grain is5 minThe word for what you nearly got wrong all week two.→
   8. SectionGrain decides what a count means5 minThat fan-out was a grain problem, not a join problem.→
   9. ExerciseCount at the right grain10 minin the studioBack to SQL, counting at the grain you can now name.
   10. QuestionsSQL · Query formulation15 minEditor closed. Twenty questions, out loud.→

03

Start it now

Reading is free and needs no account. The plan itself tracks on your dashboard.