15 KiB
Google DE Coach Tracker — Dennis
Coach mode: You study; I (Grok) plan, check answers, and advance you. Target: Google Senior Data Engineer (Merchant Data Science) — Zürich / Mountain View
Status: Assessment passed (2026-06-20); waiting for recruiter. Prep so a sudden loop doesn’t catch you cold.
Baseline assumption: Rusty in everything. Start at the beginning; skip when the skip-test is green.
Related files:interview_prep_brief.md(loop map) ·star_stories.md(behavioral)
How we work
- You do a block (below), tick the boxes, note date + confidence (1–5).
- Tell me: “coach: finished 0.1” or paste a stuck problem / SQL answer.
- I verify, correct, and open the next block (or force a repeat set).
- Prefer free resources only (listed with URLs).
Cadence (default while waiting): ~45–75 min/day, 5–6 days/week.
If energy is low: SQL only that day (still wins).
Skip rule: For any skill block, if you pass the Skip test in one sitting, mark SKIPPED (confident) and jump to the next block. Don’t skip whole tracks without a skip-test.
Repeat rule: Anything marked confidence ≤2 goes into § Weekly Repeat Queue and gets re-done within 3–7 days.
Progress dashboard
| Track | Status | Started | Last session | Confidence (1–5) | Notes |
|---|---|---|---|---|---|
| 0 Foundations | ⬜ not started | rusty start | |||
| 1 SQL core | ⬜ | highest leverage with coding | |||
| 2 SQL interview patterns | ⬜ | ||||
| 3 Python coding patterns | ⬜ | brief says HIGH weight | |||
| 4 Data modeling | ⬜ | your day job — refresh | |||
| 5 Pipeline / system design | ⬜ | your strength — structure it | |||
| 6 Behavioral / STAR | ⬜ | stories already drafted | |||
| 7 Mock loop | ⬜ | only after 1–5 green |
Overall stage: Phase 0 — bootstrap
Next action for Dennis: Open 0.1 (Big-O + complexity intuition, 20–30 min).
Phase 0 — Foundations (rusty bootstrap)
Goal: shared language so later SQL/Python blocks don’t thrash.
0.1 Big-O & how interviews think
- Resource (read, free): Big-O Cheat Sheet — scan array/hash/sort rows only
- Resource (video, free, ~10 min): Big O Notation — freeCodeCamp (short intro)
- Write from memory: O(1), O(n), O(n log n), O(n²) with one example each
Skip test: Explain out loud why a hash map lookup is average O(1) and when it degrades.
Done: date ____ confidence _/5
0.2 Python syntax refresh (no algorithms yet)
- Resource (interactive, free): Learn Python — freeCodeCamp interactive (or skimmable) — only sections: types, lists, dicts, loops, functions
- Faster alternative (video, free, ~1h if rusty): Python for Beginners — freeCodeCamp full course — watch at 1.5×, skip UI fluff; stop after functions/dicts
- In a local
.pyfile, write without looking up: list comp, dict count frequencies,sorted(..., key=), set membership
Skip test: Write a function that returns the most common word in a list of strings (use a dict). Time yourself ≤10 min.
Done: date ____ confidence _/5
0.3 SQL mental model (what a query does)
- Resource (free, best first SQL text): Mode SQL Tutorial — bare essentials through aggregations
Start: Basic SQL → WHERE → JOINs intro → aggregations - Hands-on twin (free, browser): SQLBolt — Lessons 1–7
Skip test: Write a query with FROM, WHERE, GROUP BY, HAVING, ORDER BY and explain order of execution (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY).
Done: date ____ confidence _/5
Phase 0 exit: All three confidence ≥3 or skip-tests passed. Then → Phase 1.
Phase 1 — SQL core (daily driver)
Goal: fluent on joins, nulls, aggregations, CTEs — before windows.
1.1 Joins & nulls
- Resource: Mode — SQL JOINs
- Drill: SQLBolt lessons 6–12
- Draw INNER / LEFT / FULL and one business example each (merchants, orders, null country)
Skip test: Given orders and customers, list customers with no orders (anti-join pattern).
Done: date ____ confidence _/5
1.2 GROUP BY, HAVING, CASE
- Resource: Mode — Aggregations
- Resource: Mode — CASE
- Solve 5 problems: LeetCode Database — Easy
Done: date ____ confidence _/5
1.3 CTEs & subqueries
- Resource: Mode — Subqueries & CTEs
- Rewrite one nested subquery as a CTE (any LeetCode SQL you’ve done)
Skip test: Explain when a CTE is clearer than a subquery; write a 2-CTE query.
Done: date ____ confidence _/5
Phase 1 exit: Can write join + aggregate + CTE without syntax panic. → Phase 2.
Phase 2 — SQL interview patterns (Google-relevant)
Goal: windows, ranking, gaps, top-N — what DE screens love.
2.1 Window functions (core)
- Resource (best free deep dive): Mode — Window Functions
- Resource (second explanation): DataLemur — SQL Window Functions Guide
- Master by hand:
ROW_NUMBER,RANK,DENSE_RANK,LAG/LEAD,SUM() OVER,PARTITION BY
Skip test: For each employee, salary rank within department + running total of salary (one query).
Done: date ____ confidence _/5
2.2 Pattern drill set (do in order)
Use free sites; log problem IDs:
| # | Pattern | Resource | ID / link | ✓ | Date | Conf |
|---|---|---|---|---|---|---|
| 1 | Top-N per group | DataLemur free SQL | pick “top” / ranking | ⬜ | ||
| 2 | Dedup / latest row | LeetCode SQL | e.g. search “duplicate emails” / “latest” | ⬜ | ||
| 3 | Gaps & islands / consecutive | DataLemur or LeetCode | consecutive logins / dates | ⬜ | ||
| 4 | Self-join | LeetCode SQL | employees vs manager style | ⬜ | ||
| 5 | Multi-join analytics | StrataScratch free (filter Free) | 1 medium | ⬜ |
Bulk practice hubs (bookmark):
- LeetCode Database study plan / problemset
- DataLemur SQL interview questions — free tier enough
- Select Star SQL — narrative + practice, free
Phase 2 volume target: 20 SQL problems total (Easy+Medium), ≥8 with windows.
Count so far: 0 / 20
Phase 2 exit: 20 logged + window functions confidence ≥3. → Phase 3 (or parallel 3 if SQL is already warm).
Phase 3 — Python coding (interview shape)
From
interview_prep_brief.md: coding is the gap furthest from daily work.
Target: Easy → Medium, narrate + Big-O. Not Hard DP grind.
3.1 Platform setup
- Account: LeetCode (free)
- Language: Python3 only
- Habit: speak approach before typing; state time/space at end
3.2 Pattern ladder (free LeetCode)
Do in this order. Mark when green (solved without solution, or with ≤1 peek then re-solved next day).
| # | Pattern | Starter problems (free) | ✓ |
|---|---|---|---|
| 1 | Arrays / two pointers | Two Sum · Valid Palindrome · Container With Most Water | ⬜ |
| 2 | Sliding window | Best Time to Buy/Sell Stock · Longest Substring Without Repeating | ⬜ |
| 3 | Hash maps | Group Anagrams · Top K Frequent Elements | ⬜ |
| 4 | Stack | Valid Parentheses · Daily Temperatures | ⬜ |
| 5 | Binary search | Binary Search · Search Insert Position | ⬜ |
| 6 | BFS/DFS trees | Maximum Depth of Binary Tree · Invert Binary Tree · Binary Tree Level Order | ⬜ |
| 7 | Heap / top-K | Kth Largest Element in Array | ⬜ |
| 8 | Intervals | Merge Intervals | ⬜ |
Teaching video (free, optional when stuck on a pattern):
NeetCode.io — free problem list + YouTube explanations (search problem name + “NeetCode”).
Roadmap overview: NeetCode roadmap
Volume target: 40 Easy/Medium total (brief said 40–60 Mediums long-run; start with 40 mixed).
Count so far: 0 / 40
Skip test for a pattern: Solve 2 new problems of that pattern in <25 min each with narration. Then skip remaining starters for that pattern.
Phase 3 exit: ≥25 solved + comfortable narrating Two Sum / sliding window / BFS. → keep light maintenance while doing 4–6.
Phase 4 — Data modeling (refresh, not learn from zero)
4.1 Dimensional modeling basics
- Resource (free article, classic): Kimball Group — Dimensional Modeling Techniques (overview PDF/notes)
- Resource (free, readable): Star Schema vs Snowflake (IBM overview)
- Define in your own words: grain, fact, dimension, surrogate key, SCD Type 1 vs 2
Skip test: Model merchant_orders for analytics (facts + ≥3 dims + grain sentence + one SCD2 example).
Done: date ____ confidence _/5
4.2 Tie to YOUR work (no fabrication)
- Map Swisscom Fulfillment (Oracle → Kafka → Teradata) onto a star: what is the fact grain?
- Map Iceberg lakehouse (SW-1): how does partitioning relate to query grain?
- One sentence: data product vs raw table (SW-7) — scoped ownership language
Done: date ____ confidence _/5
Phase 5 — Pipeline / system design
5.1 Structure template (memorize)
Practice every design with this spine (from your brief):
- Requirements / SLAs / consumers
- Ingestion (batch + stream)
- Storage & table format
- Transform / model
- Quality, monitoring, on-call
- Serving (BI / ML)
- Trade-offs (cost, latency, consistency)
- Resource (free video series): Seattle Data Guy — data engineering system design (YouTube search) — watch 1 full design walkthrough
- Resource (free concepts): ByteByteGo YouTube — pick one video on message queues or batch vs stream
- Optional free text: The Data Engineering Cookbook (GitHub PDF) — skim architecture chapters only
5.2 Design drills (talk out loud, 25–35 min each)
| # | Prompt | ✓ | Date | Notes |
|---|---|---|---|---|
| 1 | Merchant clickstream → daily metrics tables for analysts | ⬜ | use Kafka + lakehouse language you own | |
| 2 | Near-real-time fraud features + daily warehouse truth | ⬜ | batch + stream coexistence | |
| 3 | Migrate legacy warehouse domain to cloud tables (your SW-1 shape) | ⬜ | sequencing, dual-run, rollback |
Accuracy: “governed data products within Swisscom’s Data Mesh” — never “I built the Mesh.”
Phase 5 exit: Can run drill #1 cleanly with trade-offs without notes.
Phase 6 — Behavioral (stories already exist)
- Read full:
star_stories.md - For each story: speak out loud once timed (2–3 min)
- Record (phone voice memo) Story 1 (ownership) and self-critique
| Story | Maps to | Spoken ✓ | Date | Conf |
|---|---|---|---|---|
| 1 Ownership / pipelines | autonomy | ⬜ | ||
| 2 Migration | judgment | ⬜ | ||
| (others in star_stories.md) | ⬜ |
Google’s own free guide: How we hire
Googleyness cues: Google interview tips
Phase 7 — Mock loop (later)
Only after Phases 1–3 are ≥3 confidence:
| Mock | Format | ✓ |
|---|---|---|
| SQL timed 30 min | 2 mediums, no AI | ⬜ |
| Python timed 45 min | 1 easy + 1 medium, narrate | ⬜ |
| Design 35 min | Drill #1 with coach | ⬜ |
| Behavioral 30 min | 2 STARs with coach | ⬜ |
Weekly Repeat Queue
Anything confidence ≤2 or failed cold. Re-do within a week.
| Item | Added | Next due | Done |
|---|---|---|---|
| (example) Window functions LAG/LEAD |
Standing weekly minimum (even after advanced):
- 3× SQL mediums
- 3× Python mediums (or 2 medium + 1 review)
- 1× design spine spoken once
- 1× STAR spoken once
Session log
| Date | Block | Minutes | What went well | Stuck / wrong | Next |
|---|---|---|---|---|---|
| 2026-07-19 | — | — | Tracker created; baseline = rusty | — | Start 0.1 |
Coach notes (for Grok)
- Prefer free URLs only; if a site walls free tier, switch to LeetCode/SQLBolt/Mode.
- Enforce scope discipline in design/behavioral answers (big-corp ownership).
- Don’t let Dennis skip to Hard LeetCode to self-punish; SQL + Medium Python + design > ego Hard.
- When he says “coach: …”, update this file’s checkboxes/dashboard if he reports results.
Quick start (today / tomorrow)
- 0.1 Big-O cheat sheet + short video (30 min).
- 0.3 SQLBolt lessons 1–7 (45 min) or Mode basic SQL if you prefer reading.
- Message coach: “finished 0.1 + 0.3, confidence X” → unlock 1.x or force skip-test.
Primary bookmarks bar: