How I’d Learn Data Engineering From Scratch (If I Started Today)

Tarun Tiwari

3 hours ago

10 min read

Most beginners fail interviews not because they don't know advanced concepts like Apache Kafka or ETL processing tools, but because they can't explain why a JOIN suddenly tripled their table's row count.

SQL and Python are core for data engineering and can get you through roughly 60% of entry-level Data Engineering interviews. I once came across a resume which had around 25 tools (Hadoop, Flume, Sqoop, Kafka, Kubernetes, Spark, Cassandra) but when I questioned the difference between RANK() and DENSE_RANK() they froze.

Twenty-five tools on a one-page resume. Zero production pipelines. And they couldn't write a window function without Googling the syntax. Instead of trying to learn everything, focus on core concepts, try to understand how basic sql operations work.

Try to master Python basic functions.

When you look at entry-level roles whether that's ₹4 - 8 LPA in India or $90k - $115k in the US (which scales up to $130k–$155k as you reach mid-level) employers are really testing your engineering judgment and practical debugging skills rather than how well you've memorized syntax. Now that AI tools can write routine boilerplate in seconds, interviewers put far less weight on syntax recall. Instead, they look for candidates who genuinely grasp core concepts like table grain, data skew, and pipeline idempotency.

Before We Touch a Single Tool: What a Data Pipeline Actually Is?

Freshers often think they cannot build a real-time data pipeline until they know how to configure a three-node Apache Kafka cluster, write Scala for Spark Streaming, and deploy everything on Kubernetes.

That mindset will paralyze you.

In Designing Data-Intensive Applications (DDIA), Martin Kleppmann strips away the marketing hype of modern data tools and points out a simple truth: at the heart of almost every distributed data engine is an append-only log. Whether you are looking at PostgreSQL's Write-Ahead Log (WAL) or an enterprise Kafka topic, the core mechanism is just writing immutable records to the end of a file on disk, one after another.

You don't need a cloud account to build a fault-tolerant, real-time pipeline. You can build one right now in 35 lines of Bash.

The 35-Line Real-Time Streaming Architecture

Imagine an e-commerce checkout service firing off payment events every second. Some orders succeed, some stay pending, and occasionally, an upstream bug spits out corrupted JSON. Our job is to capture those events in real time, route broken records to a Dead-Letter Queue (DLQ), push completed orders into a clean analytics table, and make sure we don't lose our place if the worker crashes.

None

[Producer Script] ──(Appends JSON)──► [events.log] (The Broker)

│

(Tails from last saved offset: consumer.offset)

▼

[Stream Worker]

├── Valid 'COMPLETED' ──► [realtime_metrics.csv]

└── Corrupted JSON ───► [dlq_events.log]

First, here is our producer (producer.sh). It appends JSON events to events.log and intentionally throws in a corrupted payload 10% of the time:

Shell

#!/usr/bin/env bash

LOG_FILE="events.log"

ORDER_ID=1000

STATUSES=("COMPLETED" "COMPLETED" "FAILED" "PENDING")

while true; do

ORDER_ID=$((ORDER_ID + 1))

AMOUNT=$(( (RANDOM % 500) + 10 ))

STATUS=${STATUSES[$((RANDOM % ${#STATUSES[@]}))]}

EVENT_TS=$(date -u +"%Y-%m-%dT%H:%M:%SZ")

# 10% chance of a broken upstream payload

if (( RANDOM % 10 == 0 )); then

echo "{BROKEN_PAYLOAD_ORDER_${ORDER_ID}" >> "$LOG_FILE"

else

printf '{"order_id":%d,"status":"%s","amount_usd":%d,"event_ts":"%s"}\n' \

"$ORDER_ID" "$STATUS" "$AMOUNT" "$EVENT_TS" >> "$LOG_FILE"

sleep 1

done

Now look at the stream processor (stream_worker.sh). Notice we aren't piping the producer directly into the consumer (producer.sh | worker.sh). In DDIA Chapter 11, Kleppmann explains why direct piping breaks at scale: if the consumer slows down or crashes, the 64KB Linux kernel buffer fills up and either blocks the producer or drops in-flight data. By landing events in events.log first, we decouple the two systems completely.

Shell

#!/usr/bin/env bash

LOG_FILE="events.log"

OFFSET_FILE="consumer.offset"

SINK_FILE="realtime_metrics.csv"

DLQ_FILE="dlq_events.log"

touch "$LOG_FILE" "$DLQ_FILE"

[[ ! -f "$SINK_FILE" ]] && echo "order_id,amount_usd,event_ts,processed_ts" > "$SINK_FILE"

# 1. State Recovery: Read the last committed line offset

CURRENT_OFFSET=$( [[ -f "$OFFSET_FILE" ]] && cat "$OFFSET_FILE" || echo 0 )

START_LINE=$((CURRENT_OFFSET + 1))

# 2. Stream from the exact line where we left off

tail -n +"$START_LINE" -f "$LOG_FILE" | while IFS= read -r raw_event; do

CURRENT_OFFSET=$((CURRENT_OFFSET + 1))

PROCESSED_TS=$(date -u +"%Y-%m-%dT%H:%M:%SZ")

# 3. Schema Check: Route corrupted JSON to the Dead-Letter Queue

if ! echo "$raw_event" | jq -e . >/dev/null 2>&1; then

echo "offset=$CURRENT_OFFSET | ts=$PROCESSED_TS | raw=$raw_event" >> "$DLQ_FILE"

else

# 4. Filter & Transform: Only materialize COMPLETED orders

if [[ "$(echo "$raw_event" | jq -r '.status')" == "COMPLETED" ]]; then

echo "$raw_event" | jq -r --arg pts "$PROCESSED_TS" \

'[.order_id, .amount_usd, .event_ts, $pts] | @csv' >> "$SINK_FILE"

# 5. Atomic Offset Checkpoint

echo "$CURRENT_OFFSET" > "${OFFSET_FILE}.tmp" && mv "${OFFSET_FILE}.tmp" "$OFFSET_FILE"

done

Why This Tiny Script Teaches You More Than 10 Tool Tutorials

Look closely at what those 35 lines actually do. You just built:

Sequential Disk I/O: Appending (>>) to events.log happens at the end of the file. As DDIA Chapter 3 highlights, sequential writes are orders of magnitude faster than random in-place updates.
Dead-Letter Routing: Instead of letting a single malformed JSON record crash the entire consumer loop, jq -e catches the bad schema and shunts it to dlq_events.log for later inspection.
Offset Checkpointing: If you hit Ctrl+C to kill stream_worker.sh while the producer keeps running, and then restart the worker five minutes later, it reads consumer.offset and picks up at the exact line it stopped. Zero missed events.

Even better, this script reveals the exact failure modes that senior engineers get paid to solve. What happens if the server loses power right after writing the CSV row in Step 4, but before updating consumer.offset in Step 5? When the worker restarts, it replays that offset and writes a duplicate row into realtime_metrics.csv. In distributed systems, that is called At-Least-Once delivery—and fixing it requires making your downstream storage idempotent.

What happens when events.log grows to 500GB and fills up your disk? You need log segmentation and compaction. What happens when one CPU core can no longer keep up with jq parsing 50,000 events per second? You split the log across multiple machines by hashing on order_id.

Every single tool on the roadmap below—Spark, Parquet, Iceberg, Airflow, dbt, and Kafka—exists solely to solve what happens when this 35-line script outgrows a single machine. Keep that mental model in your head, and you will never feel overwhelmed by a new tool again.

The Foundations (Weeks 1–6)

Advanced SQL (The Interview Filter)

Always establish the exact grain of your target tables before writing a single JOIN clause.

When evaluating candidates during technical SQL rounds, engineers evaluate how clearly you reason about data structure and fan-out behavior. Every dataset possesses a foundational granularity, such as one record per raw credit event in raw_fintech.card_transactions versus an aggregated daily summary. Joining raw_fintech.card_transactions directly to dim_merchant_kyc without pre-aggregation produces duplicated rows and distorts revenue totals.

Window functions retain row granularity while performing complex analytical aggregations.

Window functions allow you to perform calculations across partition frames without collapsing individual records like a standard GROUP BY. You must comfortably write ROW_NUMBER() to eliminate duplicate records from raw_fintech.upi_settlements during system retries. Mastery of RANK(), DENSE_RANK(), and LAG() lets you trace state transitions across historical records without executing heavy self-joins.

Materialize heavy query steps into temporary tables whenever a CTE is referenced repeatedly.

Common Table Expressions create readable query structures when staging intermediate transformations within a complex pipeline. Certain database engines recompute referenced CTEs multiple times, which rapidly increases overall execution runtime and query compute cost. Materializing intermediate outputs from raw_fintech.upi_settlements into temporary tables reduces repetitive compute overhead on large datasets.

Profile query plans with EXPLAIN ANALYZE to eliminate unindexed scans and disk spills.

Strong data engineers diagnose query bottlenecks by analyzing physical execution plans generated via EXPLAIN ANALYZE. Reading these plans highlights costly full table scans on raw_fintech.card_transactions that require dedicated indexing strategies. Identifying memory spills to disk caused by restrictive work_mem settings ensures high-throughput database performance during peak operational loads.

Code Example (Webhook Deduplication):

SQL

-- Scenario: Payment gateway sends duplicate retry webhooks for the same order_id.

-- Goal: Keep only the latest state per order_id on a specific partition date.

WITH deduplicated_orders AS (

SELECT

order_id,

customer_id,

order_status,

amount_cents,

updated_at,

ROW_NUMBER() OVER (

PARTITION BY order_id

ORDER BY updated_at DESC, ingestion_timestamp DESC

) AS row_num

FROM raw_fintech.webhook_orders

WHERE event_date = '2026-11-01' -- Always filter on partition column first!

)

SELECT

order_id,

customer_id,

order_status,

amount_cents / 100.0 AS amount_usd,

updated_at

FROM deduplicated_orders

WHERE row_num = 1;

-- Note: In Snowflake, BigQuery, or Databricks, replace the outer WHERE with:

-- QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1

Python for Data Movement

Production Python tasks require building robust ingestion pipelines that extract data safely without exhausting server memory. Python generators yield small batches from raw_fintech.upi_settlements log files rather than loading multi-gigabyte payloads directly into RAM.

Robust extraction scripts handle HTTP 429 rate limits using exponential backoff strategies and enforce payload validation with Pydantic schemas.

Stream large datasets using generators to maintain flat memory consumption during API ingestion.

Custom Production Code Example (Memory-Safe Chunked API/File Generator):

Python

import json

from typing import Iterator, Dict, Any

def stream_large_jsonl(file_path: str, batch_size: int = 5000) -> Iterator[list[Dict[str, Any]]]:

"""Reads a multi-gigabyte JSONL file in fixed batches without blowing up RAM."""

batch = []

with open(file_path, "r", encoding="utf-8") as file:

for line_num, line in enumerate(file, start=1):

try:

record = json.loads(line)

batch.append(record)

if len(batch) >= batch_size:

yield batch

batch = []

except json.JSONDecodeError:

print(f"[WARN] Corrupted JSON at line {line_num}, routing to dead-letter log.")

continue

if batch:

yield batch

Linux & Bash CLI Survival Skills

Master text-processing CLI utilities to diagnose production file issues quickly without high memory consumption.

Opening multi-gigabyte log files in GUI applications causes system freezing during live production incidents. Command line utilities like head and tail quickly inspect file headers and trailing records without loading whole files into memory. Utilities such as grep and awk isolate specific failure messages and detect malformed columns directly on remote servers.

Storage & Data Modeling (Weeks 7–10)

Kimball Dimensional Modeling

Separate transactional systems from analytical workflows by building well-structured star schemas.

Querying operational databases directly for analytical reporting slows transaction processing and causes query locks. Dimensional modeling structures data into star schemas, separating core metrics into fact tables like fact_settlements and contextual attributes into dim_merchant_kyc. Slowly Changing Dimensions manage evolving attributes by overwriting existing values in Type 1 or preserving historical versions in Type 2.

File Formats: Row vs. Columnar

Store analytical datasets in Parquet format to optimize read performance and reduce storage footprint.

Row-oriented formats store full records together, making them efficient for writes but slow for analytical queries. Columnar formats like Apache Parquet store data organized by individual columns to reduce storage I/O during aggregations. This structure enables Run-Length Encoding and allows query engines to skip irrelevant file blocks using metadata statistics.

Open Table Formats

Adopt open table formats like Iceberg to bring ACID reliability to object storage data lakes.

Storing standalone Parquet files in cloud object storage lacks transactional guarantees for concurrent read and write workloads. Open table formats such as Apache Iceberg introduce metadata management layers on top of underlying data files. This architecture provides ACID transactions, seamless schema updates, and time-travel querying using atomic metadata commits.

Partitioning & The Small File Problem

Compact small files regularly to keep object storage file sizes optimized for distributed query engines.

Partitioning tables by date attributes allows query execution engines to scan only targeted storage directories. Excessive partitioning on high-cardinality fields creates thousands of tiny files and overwhelms metadata services. Maintaining file sizes between 128MB and 512MB through compaction routines ensures efficient distributed processing runs.

Processing & Orchestration (Weeks 11–16)

Distributed Compute (Apache Spark)

Minimize cluster data shuffling by using broadcast joins for smaller dimensional tables.

Processing massive datasets requires distributed frameworks like Apache Spark to split workloads across cluster nodes. Data shuffles during wide transformations repartition datasets across nodes and create heavy network bottlenecks. Employing broadcast joins for lookup tables like dim_merchant_kyc avoids unnecessary shuffles and maintains high execution performance.

Warehouse Transformation (dbt)

Structure SQL queries into modular layers with dbt to maintain data quality and clear data lineage.

Modern analytical transformations rely on dbt to bring software engineering practices like version control to SQL. Modular SELECT statements compile into native SQL queries that execute directly within data warehouses. Organizing transformations into staging, intermediate, and mart layers ensures clear lineage tracking and enforces schema assertions on tables like fact_settlements.

Orchestration (Airflow & Dagster)

Design idempotent pipeline tasks to ensure safe execution retries during unexpected system failures.

Orchestration frameworks manage pipeline execution timing, task dependencies, and automatic retries across complex data workflows. Platforms like Apache Airflow manage task DAGs, while tools like Dagster track underlying data assets across execution steps. Designing idempotent pipelines guarantees that rerun operations populate tables like raw_fintech.upi_settlements without generating duplicate records.

What NOT to Learn (The Anti-Curriculum)

Prioritize core data processing over advanced infrastructure administration when starting out.

Focusing on complex infrastructure management like Kubernetes administration early in your career distracts from core data skills. Entry-level data engineering tasks require basic containerization knowledge rather than deep cluster setup expertise. Using simple Docker configurations to run local PostgreSQL or Airflow instances provides sufficient development setup.

Skip legacy Hadoop ecosystem tools in favor of modern cloud data warehousing and processing engines.

Legacy Hadoop tools like MapReduce, Sqoop, and Hive have been largely replaced by modern cloud data stacks. Cloud object storage and scalable engines perform storage and execution duties more efficiently than legacy clusters. Learning modern alternatives ensures your technical skills align directly with active industry standards.

Master one primary cloud provider thoroughly before expanding your knowledge to alternative platforms.

Attempting to master multiple cloud platforms simultaneously leads to learning fatigue and superficial topic coverage. Mastering core storage, compute, and warehousing services on a single provider like AWS builds transferable foundation knowledge. Fundamental architecture concepts transfer easily between cloud platforms once key patterns are understood.

Build strong batch processing fundamentals before diving into real-time streaming architectures.

Real-time streaming engines add significant system complexity and should be approached after mastering batch fundamentals. Production systems rely heavily on robust batch processing routines to transform datasets like raw_fintech.upi_settlements reliably. Solid batch modeling skills provide the foundation needed before handling real-time streaming edge cases.

How to Stand Out

Build an automated end-to-end pipeline with live API data instead of relying on static toy datasets.

Instead of building another generic project using static Titanic survival records or COVID-19 tracking numbers, construct an automated end-to-end pipeline using live operational data. Configure your ingestion job to poll a paginated REST API continuously and append incoming payloads directly into object storage. Routing raw payloads from raw_fintech.upi_settlements into a dedicated cloud storage landing zone establishes a resilient Bronze layer for downstream transformations.

Containerize your Airflow environment to ensure reliable, scheduled ingestion without dependency conflicts.

Orchestrate your daily ingestion tasks using Apache Airflow deployed locally within a lightweight Docker container environment. Running Docker containers guarantees that environment dependencies remain isolated while testing failure retries across pipeline stages. Automating raw ingestion runs for raw_fintech.card_transactions ensures consistent execution schedules without requiring manual intervention.

Transform raw Bronze events into Kimball star schemas with dbt to enforce strict data quality.

Process raw Bronze layer records into a modeled Kimball star schema using PySpark and dbt transformations. Transforming raw transaction events populates structured fact tables like fact_settlements alongside normalized dimension entities such as dim_merchant_kyc. Enforcing schema testing across intermediate dbt models ensures strict data quality controls before analytical tables open for querying.

Document architectural trade-offs and system failure scenarios in your README to showcase engineering judgment.

Document your pipeline architecture thoroughly in a comprehensive project README detailing key design decisions. Highlight technical trade-offs made during development, such as selecting specific partition strategies or balancing batch processing intervals against compute costs. Documenting explicit recovery steps for simulated failure scenarios demonstrates real-world debugging capabilities to prospective engineering hiring managers.