Flexibility: schema-on-read and the data lake

One keyword: flexibility

The deck "Towards NoSQL" opens its first section, "Towards Big Data Architectures", with a three-row comparison (p. 165). Read the rows as answers to three questions: which data, on what hardware, for what purpose.

Data. The traditional side admits data only after it has been modelled into tables — a "highly modeled schema" designed before the first row arrives. The big-data cell has no words, only icons — read them as web pages, video, touch screens, tweets, searches: whatever form the data happens to have. "Schema agility" is the name for being able to take it anyway.

Cost. One expensive machine against many cheap, ordinary ones, for storage and for computing alike. Why the cheap machines win beyond a certain size — the cost curves and their grey area — is the subject of lesson 3.

Culture. A report looks backwards, like a rear-view mirror: what happened last month, computed with relational algebra. "Intelligent action" acts on the data, with machine-learning models, graph algorithms, streams and image processing. Notice the capital AND: relational algebra stays in the toolbox; the new tools join it.

The next slide is a single line: "One keyword: FLEXIBILITY" (p. 166). Every row is flexibility of a kind — in the data you accept, in the way you grow, in what you do with the data. This lesson follows the first kind, and its price.

Big data trades the guarantees of a structure designed up front for flexibility: in the data it accepts, the hardware it grows on and the analyses it runs.

Schema-less or implicit schema?

Page 168 is titled "The schema-less idea", and its first line is a question: "Schema-less or implicit schema?" Look at two documents from one collection of the hospital's heating systems — an HVAC system and a gas heating system, the two children of the (T, E) hierarchy in the exam's ER diagram:

{ "Heater_Sys_ID": "HS_1",
  "Heating_Power": 150,
  "Hourly_KWh_Cons": 42.0,
  "Cost_Per_KWh": 0.25 }

{ "Heater_Sys_ID": "HS_2",
  "Heating_Power": 120,
  "Hourly_SQM_Cons": 3.5,
  "Cost_Per_SQM": 0.9 }

No table declared these fields, and the store accepted both. Yet nothing about the data is shapeless: a program that computes the cost of an hour of heating must know that an HVAC system carries Hourly_KWh_Cons and Cost_Per_KWh and a gas system Hourly_SQM_Cons and Cost_Per_SQM.

# hourly cost of a heating system,
# whatever its kind
def hourly_cost(d):
  if "Hourly_KWh_Cons" in d:
    # an HVAC system
    return (d["Hourly_KWh_Cons"]
            * d["Cost_Per_KWh"])
  if "Hourly_SQM_Cons" in d:
    # a gas heating system
    return (d["Hourly_SQM_Cons"]
            * d["Cost_Per_SQM"])
  raise ValueError("unknown kind")

For the two documents it returns 10.5 and 3.15. The if lines are a schema: they say which fields exist and what they mean. The schema has moved out of the database — where a relational DBMS would declare it and refuse every row that breaks it — into the code that reads the data. That is the answer to the slide's question.

The slide then sorts the non-relational models by what they treat as the unit of data (Fig. 1):

• Aggregate-based — "Key-value and document-based". The unit is an aggregate: a cluster of related data that is read, written and moved together, like a hospital with its buildings and departments. A key-value store keeps the aggregate as an opaque value under its key; a document store keeps it as a document whose fields the database can see and query.
• Relationship-based — graph databases. The unit is the connection: nodes and the relationships between them are stored as data, so a question that walks from a heating system to the floors it heats and on to the departments placed there follows stored links instead of matching keys.

The reference at the bottom of the slide, "Domain-Driven Design, by Eric Evans", is the book that named the aggregate.

This split is the first thing that the one-point question of five past papers, "Which DB technology would be the best to store the entities/rel. of the ER diagram?", asks of you: which parts of a diagram are aggregates, read as a unit, and which are webs of relationships. Lesson 5 completes the list of database types; the last exam question below is a first try.

The schema moves after the write

The hospital of the hook ran the traditional pipeline. Pages 169–170 set it next to the new one under the title "Paradigmatic shift introduce by Big Data", and redrawn side by side the two stacks make their point by position alone.

Schema on write (p. 169): collect, apply a complex schema, write, analyse. The schema comes first, so it has to be designed before anybody knows all the questions — the slide's "Long lasting discussion about the schema that can accommodate all needs". Whatever the finished schema has no place for is dropped or refused on the way in, and the second bullet follows from the order of the steps: "Some analysis can no longer be performed because the data were lost at writing time". That is where the room temperatures went.

Schema on read (p. 170): collect, write, and only then — separately for every analysis — apply a simple schema and analyse. The slide says it in three lines: "Load data first, ask question later"; "All data are kept, the minimal schema need for an analysis is applied when needed"; "New analyses can be introduced in any point in time". The data-wrangling deck puts it in one sentence: "In schema on read, data is applied to a plan or schema as it is pulled out of a stored location, rather than as it goes in", right after "Today data modeling at design time is a luxury" (p. 1142, lesson 37).

You have met this move before, at the level of method: one of the four paradigmatic shifts of lesson 2 sets "Carefully cleanse information Before any analysis" against "Analyze information as is, Cleanse as needed" (p. 27). Schema on read is the same shift applied to the data model.

Both ideas have a twin you already know. In statistics, keeping only the sample means you planned is schema on write; keeping the raw observations is schema on read, the only way to compute a statistic nobody thought of at collection time. And Python checks a type when the line runs, not before — Kleppmann compares schema on read to exactly this dynamic type checking (background, not slides).

What is gained, what is paid

Schema on write decides at design time what will survive; schema on read keeps everything and lets each analysis bring its own schema.

The logical architecture of big data

Page 171, "Logical Architecture of Big Data", turns the principle into a platform. Follow one thermostat reading of the hospital through it, from the bottom up.

1. Data Ingestion. The reading arrives with thousands of others and is imported, transferred and loaded — "The process of importing, transferring and loading data for storage and later use" (p. 159, lesson 3).

2. Raw Big Data Landing Zone — "Any data, any form, many schemata (if any) (e.g., csv, json, txt, …)". The JSON is written as it came, room_temp and all. This is the write of schema on read: nothing is dropped.

3. Data Wrangling — "understanding, cleansing, augmenting and shaping data" (p. 160). Here the "n/a" values are found and a format is chosen; the method itself fills lessons 35–37.

4. Big Data Long Term Storage — "Data format optimized for querying, but without an integrated schema (e.g., ORC, parquet)". The readings are rewritten in a columnar file format: fast to scan, still not bound to the schema of any one analysis.

5. ETLs #1 … #n. For the facility manager's question an ETL extracts floor, time and room temperature, transforms them into the schema that fits the analysis and loads them. This is the point where a schema is applied — one per purpose.

6. Data View #1 … #n, and on top the consumers: BI dashboards ("BI #1.a", "BI #1.b" …) or "<<anything>> e.g., algorithm for process optimization" — say, a model that schedules the heaters.

Two things in the picture deserve a second look. Nothing below the ETLs carries an integrated schema: the landing zone takes "any data, any form", and even the long-term storage keeps the data free of the schema of any particular use. And "ORC, parquet" are columnar file formats: a file stores the values of each column together, block by block, so a job that needs three columns reads only those three — the "best format (e.g., columnar)" that the wrangling slide asks for (p. 160). Lesson 40 opens such a file.

ETL, and virtual ETL

The arrows "ETLs #i" have a slide of their own, "Extract Transform Load (in the Big Data era)" (p. 175): "The process that extracts data from heterogeneous data sources, transforms it in the schema that better fits the analysis to perform and loads it in the system that will perform the analysis." The slide then draws it twice.

The slide shows only the pictures; the standard reading of them is a trade-off. The classic ETL, run every night say, gives fast queries on a materialised copy — but the data is only as fresh as the last run, and the copy has to be stored. The virtual ETL keeps no copy and is always fresh, but every query pays for the extraction and loads the sources. The slides do not say which kind feeds the data views of p. 171: a view that is stored and refreshed needs a classic ETL, a view computed when it is queried a virtual one. The target of a classic ETL is typically a data warehouse — lesson 23.

The data lake and its zones

The next slide names the store in which all this raw data collects: "The Concept of Data Lake" (p. 172), an infographic built as a water cycle: "The concept can be compared to a water body, a lake, where water flows in, filling up a reservoir and flows out." Its four numbered circles:

1. "The incoming flow represents multiple raw data archives ranging from emails, spreadsheets, social media content, etc."
2. "The reservoir of water is a dataset, where you run analytics on all the data."
3. "The outflow of water is the analyzed data."
4. "Through this process, you are able to "sift" through all the data quickly to gain key business insights."

Around the lake the slide lists both inflows: STRUCTURED DATA, "Information in rows and columns", and UNSTRUCTURED DATA — "Raw, unorganized data", emails, PDF files, images, video and audio, social media. The course notes (§3.1.3) add why one lake is better than many: the organisation gets "only one centralized data source for all its analytical needs, as opposed to silos based approaches" — not a database per department, each with its own schema and its own copy.

A lake of raw files has an obvious way to fail, and the notes name it: "it's important to organize and index well all the incoming data, or we could end up with a huge Data Swamp where it's impossible for everyone to find what they're looking for". They give the remedy in the next sentence: the incoming raw data "gets incrementally refined and enriched in order to improve accessibility, by adding metadata and indexing tags and by performing quality checks". The next slide, "Data Lakes in Process" (p. 173), draws that refinement as four zones.

• Transient zone — "Ingest, Tag, & Catalog Data": every incoming file is labelled and entered in a catalogue, so that it can be found.
• Raw zone — "Apply Metadata, Protect Sensitive Attributes": who produced it, when, in which format; personal data masked or locked.
• Trusted zone — "Data quality & Validation": the values are checked against what they should be.
• Refined zone — "Enrich Data & Automate Workflows": joined with other data, prepared for use and refreshed without manual work.

On its left the slide shows where data comes from — STREAMING, FILE DATA, RELATIONAL — and on its right who uses it, the CONSUMER SYSTEMS: "Data Catalog, Data Prep Tools, Data Visualization, External Connectors". Try the zones on three of the hospital's files.

Put p. 171 and p. 173 side by side and they tell one story: the transient and raw zones do their work where the landing zone of p. 171 sits, wrangling produces trusted data, and the ETLs and data views draw on refined data. The slides do not draw this link; it is a reading that ties an answer together.

Objects against tables — and the hospital's answer

The deck adds one more motivation, from the programmer's side: "Another issue: ORM" (p. 174), with three bullets — "Impedance Mismatch", "Object-Relational Mapping problem (and solution)", "Object Orientation (OODB)" — and under the last one, "Commercial failure!".

Concretely: in a program, a department of the hospital is one object, with its specialty, the list of floors it occupies and the list of people who work for it, each with a role. In a relational database the same department is spread over three tables — Department, Placed_Into and Works_For — rebuilt with joins at every read and taken apart into rows at every write.

That translation is the impedance mismatch: the program thinks in nested objects with references, the database in flat rows linked by keys. Object-relational mapping (ORM) frameworks are the "solution" in brackets: they generate most of the translation code — but the two shapes stay different. Object-oriented databases tried to remove the mismatch by storing the objects themselves; the slide's verdict is the sub-bullet, "Commercial failure!".

Document stores attack the same problem from the data side: a document has the shape of the object. The MongoDB deck lists "Solves Impedance Mismatch problem" among the reasons for going document-based, next to "Handles Schema Changes Well (easy development)" (p. 291, lesson 13). Flexibility again — this time for the programmer.

The hospital's question, answered

Now the hook. The room temperatures were not deleted: they were never written. The heater table was schema on write — its schema stood between the collection and the write, and every field outside heater, time and kWh was dropped by design; "the data were lost at writing time" (p. 169). In the widget, the reading whose kwh was "n/a" even took a good room temperature with it when the typing check refused it.

A schema-on-read platform would have written the thermostat JSON into the landing zone as it came, and today's question would bring its own small schema — floor, time, room temperature — to two-year-old data: "Load data first, ask question later" (p. 170). The price would have been paid elsewhere. Someone had to tag and catalogue those files in the transient zone, or the lake would be a swamp in which nobody could find them today; and the analysis itself must cope with values like "n/a" that no DBMS refused at the write. Flexibility is not free: it moves the work from the writer to the reader.