Data Engineer Career FAQs Everything You Need to Know

import csv, sqlite3, urllib.request, io

URL = "https://people.sc.fsu.edu/~jburkardt/data/csv/airtravel.csv"
raw = urllib.request.urlopen(URL, timeout=30).read().decode("utf-8")

rows = list(csv.reader(io.StringIO(raw)))
header, data = rows[0], rows[1:]

conn = sqlite3.connect("mini.db")
conn.execute("DROP TABLE IF EXISTS air_travel")
conn.execute("CREATE TABLE air_travel (month TEXT, y1958 INT, y1959 INT, y1960 INT)")
conn.executemany("INSERT INTO air_travel VALUES (?, ?, ?, ?)",
                 [[c.strip().strip('"') for c in r] for r in data if r])
conn.commit()
print(conn.execute("SELECT COUNT(*) FROM air_travel").fetchone())

A typical day (varies by team)
09:30  Check overnight pipeline runs and alerts
10:00  Fix a failed job: source schema changed
11:30  Stand-up with data and product teams
12:00  Build a new incremental load for a source
14:00  Review a teammate's pull request
15:00  Add data quality tests to a model
16:30  Document a table for analysts
17:30  Plan tomorrow; hand over any open issue

Stage 1  SQL (joins, GROUP BY, window functions, CTEs)
Stage 2  Python (files, APIs, pandas, error handling)
Stage 3  Data modelling (star schema, SCD basics)
Stage 4  Orchestration (Airflow DAGs, retries)
Stage 5  Cloud basics (object storage, IAM, a warehouse)
Stage 6  Git, Docker, testing, CI
Stage 7  Spark and streaming basics
Build one project at the end of stages 2, 4 and 6.

-- Can you write this without help?
-- Second highest order amount per customer
SELECT customer_id, amount
FROM (
    SELECT customer_id, amount,
           DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnk
    FROM orders
) t
WHERE rnk = 2;

Level 1  Fix and maintain existing jobs
Level 2  Build new pipelines from a clear spec
Level 3  Design pipelines and data models yourself
Level 4  Own a domain's data (quality, SLAs, docs)
Level 5  Shape platform standards across teams
Ask each quarter: which level am I at, and what
would prove I am ready for the next one?

Design note — New orders pipeline
Problem: Analysts need hourly orders data
Options considered:
  A. Hourly batch from replica       (simple)
  B. CDC stream into warehouse       (fresher, complex)
Decision: A for now; revisit if freshness < 1 hr needed
Risks: replica lag; schema changes
Monitoring: freshness check, row-count check

Rate 1-5:
SQL                    __
Python                 __
Data modelling         __
Orchestration          __
Cloud basics           __
Git and testing        __
Communication          __
Lowest two scores = focus for next 6 weeks

30 min  SQL problem practice
30 min  Current project work
15 min  Read docs of one tool you use
15 min  Write notes: what I learned, what broke
Weekend: one longer project session + review

Company | Role | Date | Source | Stage | Next step
--------|------|------|--------|-------|----------
        |      |      |        |       |
Track which projects interviewers asked about most.