1:1 Data Engineering Mentorship

From the first SELECT to the modeling, tuning and interview patterns that data engineering rounds test. Doubt lectures DL1 (after S08) and DL2 (after S16), then mock MI01. Complete plan only.

1. S01 · SQL foundations: SELECT, WHERE, ORDER BY

   Covers: How a query actually runs (FROM › WHERE › GROUP BY › HAVING › SELECT › ORDER BY), SELECT and aliases, WHERE with AND / OR / IN / BETWEEN / LIKE, how NULL behaves (IS NULL, COALESCE), ORDER BY and LIMIT.

   Assignment: Set up PostgreSQL (or MySQL) and load the course e-commerce dataset: customers, orders, order_items, products. Write 12 queries covering filters, pattern matches, NULL checks and the top 10 orders by amount.

2. S02 · Aggregations and GROUP BY

   Covers: COUNT, SUM, AVG, MIN, MAX. COUNT(*) vs COUNT(col) vs COUNT(DISTINCT). Grouping on several columns, HAVING vs WHERE, conditional aggregation with CASE.

   Assignment: Answer 10 business questions: revenue by month, orders per city, customers with more than 3 orders, average basket size by category, and the share of cancelled orders.

3. S03 · Joins, and the duplicate-row trap

   Covers: INNER, LEFT, RIGHT, FULL, SELF and CROSS joins. Choosing join keys, fan-out duplicates that inflate totals, anti-joins (customers with no orders), joining 3+ tables.

   Assignment: Solve 12 join questions, including one anti-join and one self-join (employee and manager). Then write one query where a bad join inflates revenue and explain the bug in 3 lines.

4. S04 · Subqueries

   Covers: Scalar subqueries, IN and EXISTS, correlated subqueries, derived tables, and when EXISTS, IN or a JOIN is the better choice.

   Assignment: Solve 8 questions twice: once with a subquery and once with a join. For each pair, write which version you would ship and why.

5. S05 · CTEs and recursive CTEs

   Covers: The WITH clause for readable multi-step logic, chaining CTEs, recursive CTEs for hierarchies and for generating date series.

   Assignment: Rewrite 3 long queries from S04 using CTEs. Build an org chart with levels using a recursive CTE, and generate a calendar table for 2026.

6. S06 · Window functions I: ranking

   Covers: OVER, PARTITION BY and ORDER BY. ROW_NUMBER vs RANK vs DENSE_RANK, NTILE, and top-N per group.

   Assignment: Find the top 3 products per category by revenue and the second-highest salary per department. Deduplicate a table so only the latest row per key survives.

7. S07 · Window functions II: analytics

   Covers: LAG and LEAD, running totals, moving averages, frame clauses (ROWS vs RANGE), FIRST_VALUE and LAST_VALUE.

   Assignment: Month-over-month revenue growth %, a 7-day moving average of orders, days between consecutive orders per customer, and cumulative revenue share.

8. S08 · CASE, strings and dates

   Covers: CASE for buckets and flags. String functions (SUBSTRING, CONCAT, TRIM, REPLACE, basic regex). Date functions (DATE_TRUNC, EXTRACT, intervals, date differences), type casting, time zones.

   Assignment: Clean a messy customers table (phone formats, name casing, invalid emails) and add a segment column (new / active / dormant) using date logic.

9. S09 · Deduplication, pivots and set operations

   Covers: Finding and removing duplicates, UNION vs UNION ALL, INTERSECT and EXCEPT, pivoting with conditional aggregation, unpivoting.

   Assignment: Deduplicate a raw events table, build a month x category revenue pivot, and reconcile two tables with EXCEPT, reporting every mismatch.

10. S10 · OLTP vs OLAP, and normalization

    Covers: Transactional vs analytical systems, primary and foreign keys, constraints, 1NF / 2NF / 3NF with examples, and when to denormalize on purpose.

    Assignment: Normalize a flat 20-column sales sheet to 3NF, draw the ER diagram, and write the CREATE TABLE statements with keys and constraints.

11. S11 · Dimensional modeling

    Covers: Facts vs dimensions, declaring the grain, star vs snowflake schema, surrogate keys, fact table types (transaction, periodic snapshot, accumulating snapshot), conformed dimensions.

    Assignment: Design a star schema for a food-delivery app: state the grain, list the facts and dimensions, then write 5 analytical queries against it.

12. S12 · Slowly changing dimensions and MERGE

    Covers: SCD Type 1 vs Type 2 (and where Type 3 shows up), effective dates and current flags, MERGE and upsert patterns, idempotent loads.

    Assignment: Implement SCD Type 2 on a customer dimension using 3 daily change files and MERGE (or INSERT ... ON CONFLICT). Prove the history is correct with queries.

13. S13 · Indexes and query tuning

    Covers: How B-tree indexes work, column order in composite indexes, reading EXPLAIN and EXPLAIN ANALYZE, sargable filters, the usual causes of slow queries.

    Assignment: Take 5 slow queries on a 1M-row table, capture EXPLAIN before and after indexing or rewriting, and write a one-page tuning note.

14. S14 · Partitioning and clustering in warehouses

    Covers: Table partitioning and partition pruning, clustering keys, Snowflake micro-partitions, the cost of full scans, materialized views.

    Assignment: Partition the orders table by month, compare how filtered queries scan it, and write down when you would and would not cluster a table.

15. S15 · Interview query patterns

    Covers: Gaps and islands, consecutive-day streaks, top-N per group, retention and cohort queries, median and percentiles, sessionization.

    Assignment: Solve 15 questions taken from product-company interview loops, spread across the six patterns, with a short explanation for each.

16. S16 · SQL inside pipelines

    Covers: Transactions and ACID, isolation levels in plain terms, views vs materialized views, incremental loads using a watermark column, data checks written in SQL (row counts, null %, duplicates, reconciliation).

    Assignment: Write an incremental load from a staging table to a target using a last-updated watermark, plus 6 SQL data checks that fail loudly when something is off.

Python the way data engineers use it at work: files, APIs, pandas, tests and clean repos. Doubt lectures DL3 (after P06) and DL4 (after P12), then mock MI02. Complete plan only.

1. P01 · Python basics for data work

   Covers: Setup (Python 3.11+, VS Code, Jupyter), variables and types, strings and f-strings, lists and tuples, slicing, if / for / while.

   Assignment: 15 short exercises on string cleanup, list slicing and looping over order records. Push them to a new GitHub repo.

2. P02 · Dictionaries, sets and comprehensions

   Covers: Dictionaries for lookups and counting, sets for dedup and membership checks, list / dict / set comprehensions, sorting with key functions.

   Assignment: Parse 1,000 order records (a list of dicts) and compute revenue per city, unique customers and the top 5 products without using pandas.

3. P03 · Functions

   Covers: Parameters and defaults, *args and **kwargs, return values, scope, lambda, map / filter / sorted, type hints and docstrings.

   Assignment: Build a utility module of 8 reusable cleaning functions (trim, normalize phone, parse date, safe cast and so on) with type hints and docstrings.

4. P04 · Files, errors and logging

   Covers: Reading and writing CSV and JSON, context managers, try / except / finally, custom exceptions, using logging instead of print.

   Assignment: Write a script that reads a folder of CSVs, logs and skips bad rows, and writes one clean JSON file plus a separate reject file.

5. P05 · OOP for pipelines

   Covers: Classes and objects, __init__ and methods, inheritance vs composition, dataclasses, and when a class is better than a function.

   Assignment: Model an Extractor › Transformer › Loader pipeline as classes. Swap the CSV extractor for a JSON one without touching the loader.

6. P06 · Project structure and environments

   Covers: Modules and packages, virtual environments, pip and requirements.txt, config files and environment variables, .gitignore, a clean repo layout.

   Assignment: Restructure your P04 and P05 code into a proper package with a config file, requirements file, README and a single run command.

7. P07 · Iterators, generators and decorators

   Covers: Iterators, generators and yield, processing files bigger than memory, chunking, decorators for timing and retries.

   Assignment: Stream a large log file line by line, count errors per hour, and add a retry decorator to a function that fails randomly.

8. P08 · pandas I

   Covers: DataFrame and Series, reading CSV / Parquet / JSON, selecting and filtering, groupby and aggregation, merge and concat, sorting.

   Assignment: Answer 10 retail sales questions in pandas and check that the results match your SQL answers from S02 and S03.

9. P09 · pandas II

   Covers: Dates and time series, strategies for missing data, apply vs vectorised operations, pivot_table and melt, dtypes and memory, where pandas stops scaling.

   Assignment: Clean a messy 500k-row dataset end to end (types, nulls, duplicates, outliers) and write a before-and-after data profile.

10. P10 · APIs and databases

    Covers: REST basics, requests, auth headers, pagination, rate limits, flattening nested JSON, loading into Postgres with SQLAlchemy or psycopg.

    Assignment: Pull paginated data from a public API, flatten it, and load it into Postgres with an upsert so a rerun never creates duplicates.

11. P11 · Mini ETL project with tests

    Covers: The extract › transform › load flow, writing Parquet, config-driven runs, pytest basics, unit testing transform functions.

    Assignment: Ship a mini ETL repo: API › clean › Parquet › Postgres, with 5 unit tests, logging, and a README that explains how to run it.

12. P12 · Python coding round for data engineers

    Covers: Patterns DE interviews actually ask: hashmaps, two pointers, sliding window, sorting, string and log parsing, time complexity basics.

    Assignment: Solve 15 easy-to-medium Python interview questions and note the time complexity of each solution.

The modern stack in the order a real platform gets built: Spark and the lakehouse, then the warehouse and transformation layer, then streaming and cloud, then quality and design. Doubt lectures DL5 to DL8 sit at the end of each block. Both plans.

1. D01 · How a modern data platform fits together

   Covers: Batch vs streaming, ETL vs ELT, data lake vs warehouse vs lakehouse, file formats (CSV, JSON, Parquet, Avro), table formats (Delta, Iceberg), one reference architecture end to end.

   Assignment: Draw the data architecture of an app you use daily (sources › ingestion › storage › transform › serving) and justify each choice in a short paragraph.

2. D02 · Spark architecture

   Covers: Why distributed processing, driver / executors / cluster manager, jobs, stages and tasks, lazy evaluation and the DAG, narrow vs wide transformations, the shuffle.

   Assignment: Run a job on Databricks Free Edition, open the Spark UI, and explain its stages, tasks and the shuffle it triggered.

3. D03 · PySpark DataFrames

   Covers: Reading data with an explicit schema, select / filter / withColumn, joins, groupBy, window functions, UDFs vs built-in functions, writing Parquet.

   Assignment: Port 8 of your SQL track answers to PySpark and confirm the outputs match.

4. D04 · Spark optimization

   Covers: Partitions, repartition vs coalesce, broadcast joins, data skew and salting, caching, Adaptive Query Execution, the small-files problem.

   Assignment: Take a slow join job, diagnose it in the Spark UI, fix it with two techniques, and record the runtime before and after.

5. D05 · Databricks and Delta Lake

   Covers: Workspace, compute and jobs, the Delta transaction log, ACID writes, MERGE, time travel, OPTIMIZE / Z-ORDER / VACUUM, Unity Catalog basics.

   Assignment: Create a Delta table, apply MERGE upserts for 3 batches, query an older version with time travel, then run OPTIMIZE on it.

6. D06 · Medallion architecture and incremental loads

   Covers: Bronze / silver / gold layers, full vs incremental loads, CDC basics, watermarks, Auto Loader, idempotency, schema evolution.

   Assignment: Design the medallion layers for Project 1: every table, its keys and its load type. Bring it to the M02 session.

7. D07 · Snowflake

   Covers: Storage / compute / services layers, virtual warehouses, stages and COPY INTO, Snowpipe, streams and tasks, time travel, keeping credits under control.

   Assignment: Load files into Snowflake with COPY INTO, set up a stream and task for incremental processing, and note the credits you used.

8. D08 · dbt

   Covers: Project structure, sources and refs, staging › marts, materializations, incremental models, tests, snapshots, generated docs.

   Assignment: Build staging and mart models on your D07 data with at least 6 tests and one incremental model.

9. D09 · Apache Airflow

   Covers: DAGs, tasks and operators, scheduling and catchup, retries and SLAs, sensors, XComs, backfills, designing tasks that are safe to rerun.

   Assignment: Write a daily DAG that runs extract › dbt run › dbt test, with retries and a failure alert.

10. D10 · Apache Kafka

    Covers: Event streaming basics, topics and partitions, producers and consumers, consumer groups and offsets, delivery guarantees, message keys and ordering.

    Assignment: Run Kafka in Docker, write a Python producer for order events and a consumer group of two, and show how partitions get assigned.

11. D11 · Spark Structured Streaming

    Covers: The micro-batch model, reading from Kafka, triggers, watermarks and late data, stateful aggregations, checkpoints, writing to Delta.

    Assignment: Stream your D10 order events into a Delta table with 5-minute windowed revenue and a 10-minute watermark.

12. D12 · AWS for data engineering

    Covers: S3 layout and storage classes, IAM roles and policies, the Glue catalog and Glue jobs, Athena, Redshift basics, Lambda triggers, watching the bill.

    Assignment: Land raw files in S3, catalog them with a Glue crawler, query them in Athena, and trigger a Lambda whenever a new file arrives.

13. D13 · Data quality and observability

    Covers: How pipelines fail silently, tests at each layer, data contracts, freshness and volume checks, alerting, lineage, handling bad records.

    Assignment: Add row count, null, uniqueness and freshness checks to Project 1 that fail the job, and write a short runbook for each alert.

14. D14 · Git, CI/CD and Docker for data

    Covers: Branching and pull requests, code review, CI that runs tests on every PR, dev and prod environments, Docker images for jobs, handling secrets.

    Assignment: Add a GitHub Actions workflow that lints and tests one project on every PR, and containerize your P11 ETL.

15. D15 · Data pipeline system design

    Covers: A framework for design rounds: requirements › volume › architecture › storage › processing › quality › cost › failure modes. Batch vs streaming trade-offs, three worked designs.

    Assignment: Write full designs for two prompts, a ride-hailing trips pipeline and a clickstream analytics platform, using the framework.

These run alongside Track 03. Each project starts only after its tools have been taught, every session reviews your work, and each one ends with a deliverable due before the next. Both plans.

1. M01 · Kickoff: skill audit and roadmap

   Agenda: Where you are vs the role you want, target companies and salary band, weekly hours you can commit, your personal 6-month plan, tools setup.

   Deliverable: Fill the skill audit sheet and share your resume, LinkedIn URL and target company list.

2. M02 · Project 1 kickoff: batch lakehouse

   Agenda: Problem statement and dataset, architecture (S3 › PySpark on Databricks › Delta bronze / silver / gold), repo structure, what 'done' means.

   Deliverable: Repo created and bronze ingestion running on sample data.

3. M03 · Project 1 review: bronze and silver

   Agenda: Code review of ingestion and cleaning, schema handling, dedup logic and incremental loads.

   Deliverable: Silver layer complete, with incremental loads tested on 3 batches.

4. M04 · Project 1 review: gold and scheduling

   Agenda: Gold tables for business metrics, a Databricks Workflows job, quality checks, README review.

   Deliverable: Project 1 finished and pushed with an architecture diagram.

5. M05 · Project 2 kickoff: Snowflake + dbt

   Agenda: Analytics engineering use case, raw › staging › marts design, naming conventions, test plan.

   Deliverable: Raw data loaded in Snowflake and staging models built.

6. M06 · Project 2 review: marts, tests and Airflow

   Agenda: Review of dbt marts, tests and snapshots, and the Airflow DAG that runs the whole flow.

   Deliverable: Project 2 finished with dbt docs and a working DAG.

7. M07 · Project 3 kickoff: real-time streaming

   Agenda: Event design, Kafka topics and keys, producer code, plan for the streaming job.

   Deliverable: Producer publishing events and a consumer reading them.

8. M08 · Project 3 review: streaming into Delta

   Agenda: Structured Streaming job review, watermarks, checkpoints, a failure-and-restart test, the live metrics table.

   Deliverable: Project 3 finished with the restart test documented.

9. M09 · Portfolio polish

   Agenda: Code review across all 3 projects, README structure, architecture diagrams, putting real numbers on results, pinned repos.

   Deliverable: All 3 repos ready to be opened in an interview.

10. M10 · Resume rewrite

    Agenda: ATS-friendly structure, project and work bullets with scale and impact, matching keywords to your target JDs.

    Deliverable: Final resume plus 2 versions tailored to specific JDs.

11. M11 · LinkedIn and Naukri profiles

    Agenda: Headline, About section, featured projects, skills, recruiter visibility settings, a simple posting plan.

    Deliverable: Both profiles updated and your first project post published.

12. M12 · Behavioral story bank

    Agenda: STAR stories from your projects and work: ownership, a failure, a conflict, a tight deadline, measurable impact. The 'walk me through your project' answer.

    Deliverable: 8 written STAR stories and a recorded 2-minute project pitch.

13. M13 · Company targeting and round patterns

    Agenda: How rounds differ at service companies, product companies and GCCs, which companies fit your profile, an application tracker, weekly targets.

    Deliverable: A tracker with 40 target companies and roles.

14. M14 · Referrals and outreach

    Agenda: Referral request messages, recruiter outreach, follow-ups, recruiter screens and CTC questions.

    Deliverable: 20 referral and outreach messages sent and logged.

15. M15 · Offers and your first 90 days

    Agenda: Comparing offers, negotiation, notice period and buyouts, background verification, a 30-60-90 day plan for the new role.

    Deliverable: A negotiation script for your offers and a written 90-day plan.