Data Science and Machine Learning 101: Programming and Data Engineering Data Pipelines and ETLELT Concepts

What This Is

A data pipeline is a repeatable, automated workflow that moves raw data from source systems through ETL (Extract → Transform → Load) or ELT (Extract → Load → Transform) steps, delivering clean, feature‑ready tables for modeling. In a data‑science project the pipeline guarantees that the same preprocessing logic is applied every time you train, validate, or serve a model, eliminating “it works on my laptop” bugs.

Real‑world example: An e‑commerce platform extracts daily click‑stream logs, enriches them with user‑profile data, aggregates to daily sessions, and loads the result into a feature store. A churn‑prediction model then reads the latest features each night to score customers for targeted retention campaigns.

Key Terms & Formulas

• ETL (Extract‑Transform‑Load) – Classic pattern where data is transformed before loading into the target warehouse (good for on‑premise, heavy‑cleaning workloads).
• ELT (Extract‑Load‑Transform) – Modern pattern (especially in cloud) that loads raw data into a scalable data lake first, then transforms using SQL/BigQuery/Databricks.
• Extract – Pull data from APIs, RDBMS, flat files, or streaming sources. Example: pd.read_sql(query, conn) or spark.read.format("json").load(path).
• Transform – Apply cleaning, type‑casting, feature engineering, and business logic. Typical ops: df['price'] = df['price'].astype(float), df = df.dropna(subset=['user_id']).
• Load – Persist the processed dataframe to a destination (data warehouse, feature store, or model‑training bucket). Example: df.to_parquet('s3://ml‑features/daily.parquet').
• Batch vs. Streaming – Batch processes fixed windows of data (e.g., nightly jobs); Streaming processes events in near‑real‑time (e.g., Kafka → Flink).
• Idempotent Pipeline – A pipeline that can be re‑run without side‑effects; achieved by using deterministic transforms and upserts (MERGE INTO target USING source ON key).
• Schema Evolution – Handling new columns or type changes without breaking downstream jobs; often managed with Avro/Delta Lake schema enforcement.
• Data Validation (Great Expectations / Deequ) – Assertions like expect_column_values_to_be_between(df, "age", 0, 120).
• Feature Store – Centralized repository that serves both offline (training) and online (inference) feature vectors, ensuring consistency.
• Airflow DAG – Directed Acyclic Graph that schedules tasks; each node is a PythonOperator, BashOperator, or SparkSubmitOperator.
• CI/CD for Pipelines – Version‑control pipeline code (Git), test with pytest + pytest‑mock, and deploy via Docker/Kubernetes.

Step‑by‑Step / Process Flow

1. Extract – Pull raw data.
   python
# Example: pull daily logs from S3 and user table from Postgres
logs = spark.read.json("s3://raw/logs/2024-05-28/*.json")
users = pd.read_sql("SELECT * FROM users", pg_conn)
2. Validate & Profile – Run data‑quality checks; generate a profiling report (pandas‑profiling, Great Expectations).
3. Transform – Clean, join, and engineer features.
   python
# Join, fill missing, create session length
df = logs.join(users, "user_id", "inner")
df = df.fillna({"country": "UNKNOWN"})
df = df.withColumn("session_len", F.unix_timestamp("end") - F.unix_timestamp("start"))
4. Persist (Load) – Write to the feature store in a format that downstream jobs can read efficiently.
   python
df.write.format("delta").mode("overwrite").partitionBy("date").save("/mnt/feature_store/daily")
5. Orchestrate & Schedule – Define an Airflow DAG that runs the above steps nightly, with retries and alerts.
6. Consume – Training script reads the latest feature snapshot; inference service reads the online layer (e.g., Redis).

Common Mistakes

• Mistake: Hard‑coding file paths or dates.
  Correction: Parameterize paths (e.g., {{ ds }} in Airflow) and use relative timestamps so the pipeline works for any run date.

• Mistake: Running transformations on the source system (e.g., heavy joins in MySQL).
  Correction: Offload heavy compute to Spark/Databricks or a cloud warehouse; keep source DB for simple extracts only.

• Mistake: Skipping data‑validation and assuming “clean” data.
  Correction: Add automated expectations; a single bad row can crash downstream training or cause model drift.

• Mistake: Loading transformed data into a different schema than the training code expects.
  Correction: Enforce a contract (e.g., JSON schema or protobuf) and version it; update both training and serving code together.

• Mistake: Treating the pipeline as “set‑and‑forget” and never monitoring latency or failures.
  Correction: Hook Airflow alerts to Slack/PagerDuty and log metrics (run time, row counts) to a monitoring dashboard.

Data Science Interview / Practical Insights

1. ETL vs. ELT – When to pick which?
   
   Interviewers expect you to say: ETL when the target system cannot handle raw data or when you need row‑level security before loading; ELT when you have a cloud data lake/warehouse that can scale compute and you want to keep the raw data for future experiments.

2. Batch vs. Streaming – Trade‑offs
   
   Be ready to discuss latency (streaming < seconds), complexity (streaming needs stateful processing), and consistency guarantees (exactly‑once vs at‑least‑once).

3. Feature Store Benefits
   
   Explain how a feature store eliminates “training‑serving skew” by guaranteeing the same code path for offline and online feature generation.

4. Idempotency & Re‑runnability
   
   Interviewers love hearing about upserts (MERGE) and checkpointing in Spark Structured Streaming to guarantee exactly‑once semantics.

Quick Check Questions

1. Scenario: Your nightly pipeline fails because a new column promo_code appears in the raw logs.
   Answer: Add schema evolution handling (e.g., enable spark.sql.autoMergeSchema=true or update the Delta table schema).
   Why: Without schema evolution the write will abort, breaking downstream jobs.

2. Scenario: You need to serve churn predictions in real time, but your feature engineering currently runs in a nightly batch job.
   Answer: Move the critical features to an online feature store (e.g., Redis or DynamoDB) and compute them incrementally as events arrive.
   Why: Real‑time inference requires low‑latency feature retrieval, not batch‑only data.

3. Scenario: After adding a new join, the pipeline runtime jumps from 5 min to 45 min.
   Answer: Push the heavy join to the ELT stage using a distributed engine (Spark/BigQuery) or pre‑aggregate the join keys.
   Why: Scaling compute on the target system is faster than doing the join on a single‑node source DB.

Last‑Minute Cram Sheet (10 one‑liners)

1. ETL = Extract → Transform → Load; ELT = Extract → Load → Transform.
2. Idempotent pipelines use deterministic transforms + upserts (MERGE).
3. Batch latency = time between data arrival and pipeline completion; streaming latency = time from event to output.
4. Feature store = single source of truth for offline & online features → prevents training‑serving skew.
5. Great Expectations syntax: expect_column_values_to_be_unique(df, "order_id").
6. Airflow DAG runs tasks in topological order; use depends_on_past=False for independent runs.
7. Delta Lake ACID provides atomic writes and schema enforcement for ELT workloads.
8. ⚠️ StandardScaler assumes Gaussian distribution; use MinMaxScaler for bounded, non‑normal features.
9. Schema evolution flag in Spark: spark.sql.autoMergeSchema = true.
10. CI/CD for pipelines → lint (flake8), unit‑test (pytest), integration test on a staging data lake, then Docker‑push.