ETL Pipeline Architecture
A Spark ETL pipeline follows the classic Extract-Transform-Load pattern: extract raw data from sources like JDBC databases, S3 object storage, or Kafka topics into a DataFrame, apply transformations such as filtering, joining, and aggregating using the DataFrame API, then load the cleaned result into a sink like Parquet files, a Delta Lake table, or a data warehouse. Structuring each stage as a discrete, testable function makes the pipeline easier to debug when a specific stage produces unexpected output.
Cricket analogy: A cricket academy's development pipeline scouts raw talent (extract), coaches technique and fitness (transform), then places graduates into franchise squads (load), mirroring a Spark ETL job's extract-transform-load stages as distinct, testable steps.
Extracting and Validating Source Data
Extraction should enforce a schema up front rather than relying on schema inference, since inferSchema requires an extra full data pass and can silently misinterpret types on messy source files; specifying a StructType schema and setting mode to DROPMALFORMED or PERMISSIVE with a _corrupt_record column lets bad rows be quarantined instead of crashing the job. When pulling from a JDBC source, setting partitionColumn, lowerBound, upperBound, and numPartitions lets Spark split the extract into parallel range queries instead of pulling the whole table through a single connection.
Cricket analogy: A tournament committee that pre-defines strict eligibility criteria (a schema) before registration processes cleaner squads faster than one that lets any player register and sorts out disqualifications afterward, like enforcing a StructType schema up front.
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, StringType, DoubleType, TimestampType
spark = SparkSession.builder.appName("SalesETL").getOrCreate()
schema = StructType([
StructField("order_id", StringType(), False),
StructField("customer_id", StringType(), False),
StructField("amount", DoubleType(), True),
StructField("order_ts", TimestampType(), True),
])
# Extract: enforce schema, quarantine malformed rows
raw = (spark.read
.option("mode", "PERMISSIVE")
.option("columnNameOfCorruptRecord", "_corrupt_record")
.schema(schema)
.json("s3://raw/orders/"))
good = raw.filter(raw.order_id.isNotNull())
bad = raw.filter(raw.order_id.isNull())
bad.write.mode("append").json("s3://quarantine/orders/")
# Extract from JDBC in parallel
customers = (spark.read.format("jdbc")
.option("url", "jdbc:postgresql://db/prod")
.option("dbtable", "customers")
.option("partitionColumn", "customer_id")
.option("lowerBound", "1")
.option("upperBound", "1000000")
.option("numPartitions", "20")
.load())
Transformations and Data Quality Checks
Transformation logic should chain withColumn, filter, dropDuplicates, and join operations in a way that keeps each intermediate DataFrame testable, and it should include explicit data quality assertions - for example, checking that a null-count on a required column is zero, or that a join didn't unexpectedly fan out row counts - before the data proceeds to load. Running df.count() before and after a join is a cheap sanity check that catches an accidental many-to-many join that would otherwise silently duplicate records downstream.
Cricket analogy: A team's video analyst checks a player's dismissal count against expected patterns before finalizing a scouting report, catching an anomaly like an unusually high number of run-outs, just as a row-count check catches an unexpected join fan-out.
A cheap habit that catches most accidental fan-out bugs: before = df.count(); joined = df.join(other, 'key'); after = joined.count(); assert after <= before * expected_max_multiplier before writing anything downstream.
Loading and Orchestration
Loading into a Delta Lake table instead of plain Parquet adds ACID transactions, so a failed write is automatically rolled back instead of leaving a half-written table that downstream readers might query mid-write; it also enables schema evolution and time-travel queries for auditing past states. Partitioning the output by a low-cardinality column like date or region speeds up downstream filtered queries, and orchestrating the whole pipeline with a scheduler like Airflow ensures the extract, transform, and load steps run in the correct dependency order with retries on failure.
Cricket analogy: A stadium's official scoreboard only updates to the final confirmed score after the third umpire's review completes (an atomic commit), never showing a half-updated score mid-review, just as Delta Lake never exposes a half-written table to readers.
Writing directly to a production table with .mode('overwrite') without a staging table or atomic swap can leave downstream readers querying an empty or partial table for the duration of the write on plain Parquet - Delta Lake's transaction log avoids this, but plain Parquet overwrites do not.
- A Spark ETL job follows Extract-Transform-Load, ideally as discrete, independently testable stages.
- Enforce an explicit schema at extraction instead of relying on inferSchema, and quarantine malformed rows with PERMISSIVE/DROPMALFORMED.
- Use partitionColumn/lowerBound/upperBound/numPartitions to parallelize JDBC extraction across a connection pool.
- Add explicit data quality assertions (null counts, row counts before/after joins) to catch fan-out bugs before loading.
- Delta Lake adds ACID transactions, schema evolution, and time travel on top of Parquet.
- Partition output by a low-cardinality column like date to speed up downstream filtered queries.
- Orchestrate multi-stage pipelines with a scheduler like Airflow for dependency ordering and retries.
Practice what you learned
1. Why is specifying an explicit schema preferred over inferSchema for production ETL extraction?
2. What does setting partitionColumn, lowerBound, upperBound, and numPartitions accomplish for a JDBC read?
3. What is a cheap way to catch an accidental many-to-many join fan-out?
4. What does Delta Lake add on top of plain Parquet?
5. Why partition ETL output by a column like date?
Was this page helpful?
You May Also Like
Spark Best Practices
Practical guidelines for writing efficient, reliable, and maintainable Apache Spark jobs in production.
Spark Quick Reference
A condensed cheat sheet of Spark's core APIs, common transformations and actions, I/O options, and key configuration settings.
Spark vs Hadoop MapReduce
How Spark's in-memory DAG execution model compares to classic Hadoop MapReduce in performance, fault tolerance, and use cases.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics