What Is Data Engineering? A Complete Beginner's Guide
A beginner-friendly guide to data engineering: what data engineers do, how pipelines work, the core concepts and tools, and a first ETL pipeline you can run.
Every company now collects data: orders, clicks, payments, sensor readings, support tickets. But raw data is rarely ready to use. It sits in different systems, in different formats, with missing values, duplicates and mistakes. Before anyone can build a dashboard or train a model, someone has to collect that data, clean it, and deliver it somewhere reliable. That work is data engineering.
This guide explains data engineering from the ground up. You'll learn what data engineers do, how a data pipeline works, the core concepts (ETL, ELT, batch, streaming, warehouses and lakes), the tools used in the field, and you'll build a small but complete data pipeline in Python that you can run on your own computer.
Data engineering is the practice of designing, building and maintaining the systems that collect, store, transform and deliver data so that it is reliable and ready to use.
A useful way to picture it: if data is water, data engineers build and run the plumbing. They connect the sources (the "wells"), move the water through pipes, filter it, store it in tanks, and make sure clean water comes out of the tap when someone opens it. Analysts, data scientists and applications are the people opening the taps.
The main product of data engineering is the data pipeline: an automated process that takes data from one or more sources, applies a series of steps to it, and delivers the result to a destination such as a database, a data warehouse or a file store.
Why data engineering matters
Reports, dashboards and machine learning models are only as good as the data behind them. Without data engineering, teams run into the same problems again and again:
Data is scattered. Sales are in one database, website events in another tool, and budgets in spreadsheets. Nobody can see the full picture.
Data is inconsistent. One system writes country codes as "IN", another as "India", a third as "in ".
Numbers don't match. Two dashboards show different revenue because each team cleaned the data differently.
Work is manual. Someone downloads files and fixes them by hand every week, which is slow and error-prone.
Data engineering solves these problems by making data centralised, consistent, automated and trustworthy. It is the foundation that analytics, business intelligence and machine learning are built on.
What does a data engineer do?
A data engineer's day-to-day work usually includes:
Building pipelines that pull data from databases, APIs, files and event streams.
Writing transformations (often in SQL or Python) that clean, join and reshape data.
Designing tables and data models so data is easy and fast to query.
Scheduling and monitoring pipelines, and fixing them when they fail.
Checking data quality: no missing keys, no duplicates, values within expected ranges.
Managing storage and compute costs, access permissions and security.
Data engineering is often confused with two related roles. They work closely together, but the focus is different:
Role
Main question
Typical output
Data engineer
How do we get reliable data to the people and systems that need it?
Pipelines, clean tables, data platforms
Data analyst
What happened, and why?
Reports, dashboards, analysis
Data scientist
What is likely to happen, and what should we do?
Statistical models, machine learning
In small companies one person may do all three. In larger teams, data engineers provide the clean data that analysts and scientists depend on.
How data engineering works: the data pipeline
Most pipelines follow the same lifecycle, even when the tools differ.
Ingestion is moving data from its source into your platform. It can happen in batches (for example, copying yesterday's orders every night) or as a stream (each event is sent the moment it happens). Sources include application databases, REST APIs, CSV or JSON files, and event systems.
Storage
Ingested data needs a home. Raw data is commonly kept in a data lake (cheap file storage such as Amazon S3, Azure Data Lake Storage or Google Cloud Storage), while cleaned, structured data often lives in a data warehouse (such as Snowflake, Google BigQuery or Amazon Redshift) that is optimised for analytical SQL queries.
Transformation
Transformation turns raw data into useful data. Typical steps are:
Validating: rejecting or flagging rows that break rules, such as negative prices or impossible dates.
De-duplicating: keeping one copy of each record.
Joining: combining data from several sources, for example orders with customers.
Aggregating: summarising, for example daily revenue per country.
Serving
The final, trusted data is made available to its users: BI tools such as Power BI or Tableau, analysts writing SQL, machine learning models, or other applications.
Orchestration and monitoring
Pipelines have many steps that must run in the right order, on a schedule, and recover from failures. An orchestrator such as Apache Airflow describes these steps as a workflow (in Airflow, a DAG: a directed acyclic graph of tasks) and runs them. Monitoring and alerting tell you when a pipeline fails or produces unexpected data.
Core concepts and terminology
ETL vs ELT
Both describe the order of the three main pipeline steps:
ETL (Extract, Transform, Load): data is extracted, transformed on a separate processing system, then loaded into the destination. This is the traditional approach.
ELT (Extract, Load, Transform): raw data is loaded into the destination first (usually a cloud warehouse or lakehouse) and transformed there, often with SQL. Modern cloud warehouses have the compute power to make this practical, and keeping the raw data makes it easy to re-run transformations later.
ETL
ELT
Where transformation happens
Before loading, on a separate engine
Inside the warehouse or lakehouse
Raw data kept in the destination
Usually not
Yes
Common today in
Legacy systems, sensitive data that must be filtered before loading
Cloud data platforms
Neither is "better" in general. Many real platforms mix both.
Batch vs stream processing
Batch processing handles data in chunks at intervals: every hour, every night. It is simpler and cheaper, and it fits most reporting needs.
Stream processing handles each event continuously, seconds or less after it happens. It suits use cases such as fraud detection, live tracking or real-time recommendations. Apache Kafka is a widely used platform for moving event streams, and engines such as Apache Spark (Structured Streaming) or Apache Flink process them.
A good rule for beginners: start with batch. Use streaming only when the business genuinely needs results within seconds or minutes.
Data warehouse, data lake and lakehouse
Data warehouse
Data lake
Lakehouse
Stores
Structured, cleaned tables
Any data in its raw form (files)
Files in open formats, with table features on top
Best for
Fast SQL analytics and BI
Cheap storage of large or varied data
Both analytics and data science on one copy of data
Examples
Snowflake, BigQuery, Redshift
Amazon S3, Azure Data Lake Storage
Databricks with Delta Lake
Many lakehouse platforms organise data in layers often called the medallion architecture: bronze (raw data as ingested), silver (cleaned and validated) and gold (business-ready tables and aggregates). It is a simple and useful mental model even outside those platforms.
File formats
CSV and JSON are easy to read and share, but they are slow to query at scale and carry no reliable data types.
Parquet is a columnar file format: values from the same column are stored together and compressed. Analytical queries that read only a few columns become much faster and cheaper, which is why Parquet is a common choice in data lakes.
Data quality and idempotency
Two ideas separate reliable pipelines from fragile ones:
Data quality checks test data against expectations: required fields are present, IDs are unique, amounts are not negative, dates parse correctly.
Idempotency means running a pipeline twice gives the same result as running it once. If a job fails halfway and is re-run, it must not create duplicate rows. Techniques include upserts (insert or update by key) and overwriting whole partitions.
You'll see both ideas in the hands-on example below.
The data engineering toolkit
You don't need to learn every tool. This table shows the main categories and some widely used options in each.
Apache Airflow, Dagster, Prefect, Azure Data Factory
Storage
Keeping raw and processed data
Amazon S3, Azure Data Lake Storage, Google Cloud Storage
Warehouses and lakehouses
Querying data with SQL at scale
Snowflake, Google BigQuery, Amazon Redshift, Databricks
Infrastructure
Running and deploying it all
Docker, Kubernetes, Terraform, Git
Tip: SQL and Python are the two skills almost every data engineering job asks for. If you are just starting, learn them well before anything else.
Build your first data pipeline in Python
Let's build a small ETL pipeline that shows the core ideas: extract raw data, validate and clean it, and load it into a database in a way that is safe to re-run. It uses only Python's standard library, so there is nothing to install.
Prerequisites
Python 3.9 or newer (check with python3 --version).
SQLite comes built into Python. The upsert syntax used below needs SQLite 3.24 or newer, which current Python installers include. You can check with python3 -c "import sqlite3; print(sqlite3.sqlite_version)".
Optional: the sqlite3 command-line tool, to query the result. It's preinstalled on macOS and most Linux distributions.
Step 1: Create the raw data
Create a folder for the project and save this as orders.csv. It deliberately contains common real-world problems: inconsistent country codes, extra spaces, a duplicate order, a date in the wrong format and a negative amount.
order_id,order_date,country,amount
1001,2026-01-05,in,250.00
1002,2026-01-05, IN ,120.50
1003,2026-01-06,uk,89.99
1004,2026/01/06,UK,45.00
1005,2026-01-06,us,-10.00
1002,2026-01-05,IN,125.50
1006,2026-01-07,us,300.00
Step 2: Write the pipeline
Save this as pipeline.py in the same folder.
"""A minimal ETL pipeline using only Python's standard library.
Extract: read raw orders from a CSV file.
Transform: validate, clean and de-duplicate the rows.
Load: upsert the clean rows into a SQLite table.
"""
import csv
import sqlite3
from datetime import datetime
from pathlib import Path
RAW_FILE = Path("orders.csv")
DATABASE = Path("warehouse.db")
def extract(path: Path) -> list[dict]:
"""Read every row of the CSV file as a dictionary."""
with path.open(newline="", encoding="utf-8") as f:
return list(csv.DictReader(f))
def transform(rows: list[dict]) -> tuple[list[tuple], list[tuple]]:
"""Return (clean rows, rejected rows with the reason)."""
clean, rejected = {}, []
for row in rows:
try:
order_id = int(row["order_id"])
order_date = datetime.strptime(row["order_date"].strip(), "%Y-%m-%d").date().isoformat()
country = row["country"].strip().upper()
amount = round(float(row["amount"]), 2)
if amount < 0:
raise ValueError("amount is negative")
except (KeyError, ValueError) as error:
rejected.append((row["order_id"], str(error)))
continue
# A later row with the same order_id replaces the earlier one.
clean[order_id] = (order_id, order_date, country, amount)
return list(clean.values()), rejected
def load(rows: list[tuple], database: Path) -> None:
"""Create the table if needed and upsert the rows (safe to re-run)."""
conn = sqlite3.connect(database)
try:
with conn: # commits on success, rolls back on error
conn.execute(
"""
CREATE TABLE IF NOT EXISTS orders (
order_id INTEGER PRIMARY KEY,
order_date TEXT NOT NULL,
country TEXT NOT NULL,
amount REAL NOT NULL
)
"""
)
conn.executemany(
"""
INSERT INTO orders (order_id, order_date, country, amount)
VALUES (?, ?, ?, ?)
ON CONFLICT(order_id) DO UPDATE SET
order_date = excluded.order_date,
country = excluded.country,
amount = excluded.amount
""",
rows,
)
finally:
conn.close()
if __name__ == "__main__":
raw = extract(RAW_FILE)
clean, rejected = transform(raw)
load(clean, DATABASE)
print(f"Extracted {len(raw)} rows, loaded {len(clean)}, rejected {len(rejected)}")
for order_id, reason in rejected:
print(f" rejected order {order_id}: {reason}")
How the code works
extract uses csv.DictReader, which turns each line into a dictionary keyed by the header (row["amount"]). Opening the file with newline="" is what the Python documentation recommends for the csv module.
transform applies the rules. strip() removes stray spaces and upper() standardises country codes. strptime enforces one date format, so 2026/01/06 is rejected instead of silently guessed. A negative amount raises an error on purpose. Instead of crashing, each bad row is recorded with its reason: in a real pipeline you'd send these to a "quarantine" table and alert someone. Storing clean rows in a dictionary keyed by order_id removes duplicates, keeping the last version of each order.
load creates the table only if it doesn't exist, then uses an upsert: INSERT … ON CONFLICT(order_id) DO UPDATE. A new order is inserted, and an existing one is updated, so running the pipeline again never creates duplicates. That is idempotency in practice. The ? placeholders pass values safely instead of building SQL strings by hand, which prevents SQL injection. The connection's with block commits the transaction, or rolls it back if anything fails. It doesn't close the connection, so finally: conn.close() does that.
Step 3: Run it
python3 pipeline.py
Output:
Extracted 7 rows, loaded 4, rejected 2
rejected order 1004: time data '2026/01/06' does not match format '%Y-%m-%d'
rejected order 1005: amount is negative
Seven raw rows became four clean orders: one was a duplicate (order 1002) and two broke the rules.
Step 4: Query the result
Save this report query as report.sql:
SELECT order_date,
country,
COUNT(*) AS orders,
ROUND(SUM(amount), 2) AS revenue
FROM orders
GROUP BY order_date, country
ORDER BY order_date, country;
Run it with the sqlite3 command-line tool:
sqlite3 -header -column warehouse.db < report.sql
Output:
order_date country orders revenue
---------- ------- ------ -------
2026-01-05 IN 2 375.5
2026-01-06 UK 1 89.99
2026-01-07 US 1 300.0
Notice that "in" and " IN " are now counted together as IN, and order 1002 is counted once, with its latest amount (125.50). That's the value of a transformation step: everyone querying this table gets the same, consistent answer.
Step 5: Verify that it's idempotent
Run the pipeline a second time, then count the rows:
python3 pipeline.py
sqlite3 warehouse.db "SELECT COUNT(*) FROM orders;"
The count is still 4. Because of the upsert, re-running the job is safe.
Troubleshooting
Problem
Likely cause and fix
FileNotFoundError: orders.csv
Run the command from the folder that contains orders.csv, or use the full path.
sqlite3.OperationalError: near "ON": syntax error
Your SQLite is older than 3.24 and doesn't support upserts. Update Python, or check the version with the command in the prerequisites.
command not found: sqlite3
Install the SQLite command-line tool, or run the query from Python with sqlite3.connect("warehouse.db").execute(...).
A valid-looking row is rejected
Print the rejection reason: it's usually an unexpected format, such as a different date layout or a comma as a decimal separator.
What a production version adds
This example has the same shape as a real pipeline, just smaller. In production you would typically read from an API or database instead of a file, and load into a warehouse such as BigQuery or Snowflake. You'd also run the job on a schedule with an orchestrator such as Airflow, write rejected rows to a quarantine table, add automated data quality tests, and send an alert when a run fails.
Scaling up: the same pipeline in PySpark
When data grows to many gigabytes or more, a single Python process is no longer enough. Apache Spark spreads the work across many machines, and PySpark is its Python API. The same cleaning logic looks like this.
Note: This example was not run for this article. It's written for recent PySpark versions; check the official PySpark documentation for your version before using it. PySpark also needs Java installed.
from pyspark.sql import SparkSession, functions as F
spark = SparkSession.builder.appName("orders-etl").getOrCreate()
raw = spark.read.option("header", True).csv("orders.csv")
clean = (
raw
# Keep only rows whose date matches YYYY-MM-DD before parsing it.
.filter(F.trim(F.col("order_date")).rlike(r"^\d{4}-\d{2}-\d{2}$"))
.withColumn("order_id", F.col("order_id").cast("int"))
.withColumn("order_date", F.to_date(F.trim(F.col("order_date")), "yyyy-MM-dd"))
.withColumn("country", F.upper(F.trim(F.col("country"))))
.withColumn("amount", F.col("amount").cast("double"))
.filter(F.col("amount") >= 0)
.dropDuplicates(["order_id"])
)
clean.write.mode("overwrite").parquet("output/orders")
The ideas are identical: read, validate, standardise, de-duplicate, write. Two differences are worth knowing:
Spark writes Parquet files, the columnar format discussed earlier, instead of loading a database table.
dropDuplicates keeps one row per order_id, but not necessarily the latest one. If you need the latest version, add an update timestamp to the data and keep the newest row with a window function.
Writing with mode("overwrite") replaces the output on each run, which is another simple way to stay idempotent.
Real-world data engineering use cases
E-commerce reporting: combine orders, payments, returns and marketing data into a daily sales table that every team uses for the same revenue figures.
Customer 360: join support tickets, purchases and website activity into one view of each customer.
Fraud detection: stream card transactions through Kafka and score them within seconds, so suspicious payments can be stopped before they complete.
IoT and sensors: collect readings from thousands of devices, store the raw history cheaply in a data lake, and aggregate it for maintenance dashboards.
Machine learning features: produce clean, regularly updated feature tables that models are trained and served from.
Regulatory reporting: build auditable pipelines whose outputs can be traced back to the source data.
Advantages and limitations
Advantages
A single, trusted source of truth instead of conflicting spreadsheets.
Automation replaces repetitive manual data work.
Faster, better decisions, because data is fresh and ready to query.
It makes analytics and machine learning possible at scale.
Limitations and challenges
Pipelines need ongoing maintenance: source systems change, APIs break and data volumes grow.
Cloud storage and compute cost money, and inefficient queries or forgotten resources can make bills grow quickly.
Real-time systems are considerably more complex to build and operate than batch ones.
Handling personal data brings privacy, security and compliance responsibilities.
Common mistakes beginners make
Ignoring data quality. Loading data without validation simply moves bad data faster. Validate early and keep rejected rows visible.
Pipelines that aren't idempotent. A plain INSERT re-run after a failure creates duplicates. Use upserts or overwrite whole partitions.
Choosing streaming too early. Real-time pipelines are harder to build and run. Most reporting needs are met by hourly or daily batches.
Hard-coding secrets. Passwords and API keys in code end up in Git history. Use environment variables or a secrets manager.
No monitoring. A pipeline that fails silently is worse than no pipeline, because people keep trusting stale data.
Over-engineering. Starting with a cluster, five tools and microservices for a few thousand rows a day. Begin simple and scale when you need to.
Best practices
Keep the raw data. Store data as ingested (the "bronze" layer) so you can re-run transformations if logic changes.
Make every job idempotent and safe to retry.
Test your data, not just your code: check uniqueness, nulls, ranges and row counts on every run.
Use version control (Git) for pipeline code and SQL, and review changes.
Document your tables: what each column means, where it comes from and how often it updates.
Design for change: expect new columns, renamed fields and late-arriving data.
Watch costs: partition large tables, query only the columns you need, and switch off idle resources.
How to start learning data engineering
A practical learning path, in order:
SQL: joins, aggregations and window functions. You will use it every day.
Python: files, APIs, data structures, and libraries such as pandas.
Data modelling: tables, keys, normalisation, and star schemas (fact and dimension tables).
One cloud platform: AWS, Azure or Google Cloud, focusing on its storage and data services.
A warehouse and transformations: BigQuery or Snowflake, plus dbt for SQL-based transformations.
Orchestration: Apache Airflow.
Big data and streaming: Apache Spark (PySpark), then Apache Kafka.
Projects: build two or three end-to-end pipelines on public datasets, publish the code on GitHub and write up what you learned.
Tip: employers value a working project that you can explain far more than a long list of tools. Extend the pipeline from this article: read from a public API, schedule it, and load the results into a cloud warehouse.
Frequently asked questions
What is data engineering in simple terms?
It is the work of building the systems that collect data from different places, clean and organise it, and deliver it reliably to the people and tools that use it.
Is data engineering hard to learn?
It has a lot of topics, but each one is learnable step by step. If you are comfortable with basic SQL and Python, you can build your first real pipeline within weeks. Depth comes with practice on real projects.
Do data engineers need to know coding?
Yes. SQL and Python are the core skills. Some tools offer drag-and-drop pipelines, but coding is needed for anything non-trivial, and for debugging.
What is the difference between a data engineer and a data scientist?
Data engineers build and run the pipelines and platforms that provide clean data. Data scientists use that data to build statistical and machine learning models. Each relies on the other.
Is ETL the same as data engineering?
No. ETL is one important pattern within data engineering. The field also covers ELT, streaming, data modelling, storage design, orchestration, data quality, security and cost management.
Which tools should a beginner learn first?
SQL and Python first. Then one cloud platform, a data warehouse, Apache Airflow, and Apache Spark. Learn tools through projects rather than in isolation.
Can I learn data engineering without a computer science degree?
Yes. Many data engineers come from analytics, software development or other backgrounds. A portfolio of working projects demonstrates your skills directly.
Conclusion
Data engineering is the discipline that makes data usable. It moves data from scattered sources through ingestion, storage and transformation, and delivers clean, consistent, trusted data to the people and systems that need it. You've seen the core concepts: pipelines, ETL and ELT, batch and streaming, warehouses, lakes and lakehouses. You've also seen the habits that make pipelines reliable: validation and idempotency. And you've built a working ETL pipeline in Python.
The best next step is to keep building. Change the pipeline to read from a public API, schedule it to run daily, and load the results into a cloud data warehouse. Then learn Apache Airflow to orchestrate it and Apache Spark to scale it. Each project you finish teaches more data engineering than any list of tools.