Lecture 4 Notes — Data Quality & Pre-processing II
Learning outcomes: LO3 (analytics on structured and semi-structured data), LO4 (multi-node clusters)
Companion files: slides L04 (29 slides), the end-to-end pipeline guide, and the pipeline project code
1. Why semi-structured data?
Most data that arrives from outside your organisation is not a clean table: REST APIs return JSON, applications write logs where each event has different optional fields, and enterprise or feed systems use XML. Semi-structured data has structure, but each record carries its own rather than conforming to one fixed table definition.
| Structured | Semi-structured | Unstructured | |
|---|---|---|---|
| Examples | SQL tables, CSV | JSON, XML, logs | Free text, images, audio |
| Schema | Fixed, enforced | Self-describing keys/tags | None |
| Flexibility | Low | High | Total |
2. JSON and XML in practice
A single travel-review record in JSON:
{"hotel_id": "H-1042", "name": "Lakeview Inn",
"location": {"city": "Kandy", "lat": 7.29},
"reviews": [
{"user": "u77", "rating": 4.5, "text": "Great view!", "tags": ["family", "scenic"]},
{"user": "u91", "rating": null, "text": "Noisy at night"}
]}
Four things to notice: objects {} (key/value pairs), arrays [] (one hotel has many reviews), nested objects (location), and null / missing values (the second review has a null rating and no tags key at all).
XML carries the same data with opening and closing tags. It is more verbose, has attributes, namespaces and schema languages (XSD), and treats everything as text. JSON is more compact, has real number/boolean/null types, and is native to web APIs and Spark.
3. Schema-on-write vs schema-on-read
- Schema-on-write (traditional warehouse): define the schema first, validate during ETL, load only conforming rows. Queries are fast and consistent, but ingestion is slow, schema changes are costly, and fields you did not plan for are lost.
- Schema-on-read (data lake): store raw data as it arrives, apply a schema when you read it. Ingestion is fast and the raw copy is always available, but quality problems surface at query time, so the cleaning work of Lecture 3 becomes mandatory.
Modern practice combines both with zones: raw/bronze (as received) → cleaned/silver (validated, typed, de-duplicated) → curated/gold (aggregates and features ready for analysis).
4. Flattening nested data
To analyse reviews as a table:
- nested objects become columns via dot notation (location.city);
- arrays become rows via explode (one hotel with 60 reviews becomes 60 rows).
Always compare row counts before and after. Explode multiplies rows, and joins can silently duplicate them.
5. Pitfalls checklist
- Missing keys: code must not assume a field exists.
- Type drift:
ratingis4.5in one record and"4.5"or"N/A"in another. Read it as a string, then cast. - Schema drift: an API adds or renames fields between versions.
- Encoding and escapes: unicode, emoji, embedded quotes.
- Corrupt lines: one bad JSON line must not kill a 100 GB job. Route it to a quarantine location and count it.
- Explode blow-up: duplicates creep in (Lecture 3 again).
6. Why distribute? Spark architecture (LO4)
pandas needs all data in one machine's RAM. When the data or the compute outgrows one machine, Spark splits it into partitions spread over a cluster:
- the driver runs your program and builds the execution plan;
- the cluster manager (Standalone, YARN or Kubernetes) allocates resources;
- executors on worker nodes run tasks, one per partition.
Rule of thumb: if it fits comfortably in memory, pandas is simpler. Go distributed when data or compute time outgrows one machine; the price is shuffles, serialisation and cluster overhead.
7. Spark execution model
- Transformations (
select,filter,withColumn,explode,join,groupBy) are lazy: they build a plan. - Actions (
count,show,collect,write) trigger execution. Spark then optimises the whole plan, for example pushing filters early and reading only needed columns. - Narrow transformations (filter, select) work partition by partition. Wide transformations (groupBy, join, distinct, orderBy) shuffle data across the network and are the main cost.
- Avoid
collect()on big data; it pulls everything to the driver.
8. Week 3 pipeline in PySpark
| Step | pandas | PySpark |
|---|---|---|
| Missing values | fillna(v) |
fillna({...}) / F.when |
| Duplicates | drop_duplicates() |
dropDuplicates([keys]) |
| Invalid values | df.loc[mask] = nan |
withColumn + F.when(cond, None) |
| Outliers (IQR) | quantile |
approxQuantile (approximate, no full sort) |
| Encoding | get_dummies |
StringIndexer + OneHotEncoder |
| Scaling | sklearn StandardScaler |
pyspark.ml.feature.StandardScaler |
9. Storage formats
CSV and JSON lines are row-oriented text with no stored schema. Parquet is columnar binary with an embedded schema, strong compression, and column pruning (reads only the columns a query needs). Habit: land raw JSON, write cleaned data as Parquet, partitioned by a commonly filtered column such as city.
10. Feature engineering at scale
Date parts (month, weekday), text-derived features (length, word count, later TF-IDF), aggregates per hotel or user (groupBy / window functions), encoding, scaling, and binning. In Spark ML, chain steps in a Pipeline, fit on training data only to avoid leakage, and save the fitted model for reuse.
11. In-class activity: word count
Take the 137-word paragraph on the slides, split it into four sub-paragraphs (four "workers"), and count words on each partition in parallel:
- Map: each worker emits local counts, e.g. (data, 3).
- Shuffle: group the pairs by word.
- Reduce: add them up: data = 3+3+1+1 = 8, and the = 0+2+2+4 = 8.
Only small (word, count) pairs travel over the network, not the text. That is why it scales. Totals: 137 words, 86 distinct.
12. Putting it together
The pipeline guide implements this lecture end to end: raw nested JSON → quarantine of corrupt lines → flatten and clean → Parquet partitioned by city → features → analytics → charts, with a Docker Compose cluster for the multi-node run.
Next lecture: Descriptive analytics.