Data warehousing

The reading track

1. 01

   Lesson 1 · A data warehouse is not a bigger database; it is a database built for a different question.

   Building the Data Warehouse · W. H. Inmon · 1992

   Inmon's four adjectives are really four design decisions. Subject-oriented: data is organised by customer, product, order — not by the applications that produced it. Integrated: the same country codes, the same units, the same keys, however many sources sit behind them; integration is the most expensive part of the work and the reason the warehouse exists at all. Time-variant: every row is a dated snapshot, and history is kept rather than overwritten. Nonvolatile: data is loaded and read; it is not corrected in place the way a transactional system corrects it. The production system answers "what is this customer's address now?". The warehouse answers "how has order value moved across regions over the last five years?". The first question wants one row, fast and consistent. The second wants millions of rows that can be compared with each other. They are two different jobs, and the lessons that follow show why the same engine does not do both well.

   A data warehouse is a subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of management's decision making process.Building the Data Warehouse (1992), cap. 2 — definiția depozitului de date

   Why it matters Without a warehouse, every report does its own integration — and each does it slightly differently. Decisions need comparable history, and comparable history is exactly what production systems do not keep.

   ▶What is a Data Warehouse?Video lesson · IBM Technology

2. 02

   Lesson 2 · Data independence: the user describes what they want, not where it lives.

   A Relational Model of Data for Large Shared Data Banks · E. F. Codd · 1970

   In 1970 Codd proposed something that now looks obvious: tables (relations) with rows and columns, primary keys that identify a row and foreign keys that link tables, plus a declarative language in which you state the result you want rather than which files the machine should open. Normalisation comes from the same idea: if a customer's address appears in a thousand orders, you correct it in a thousand places or in none. Third normal form fits in one sentence: every attribute depends on the key, the whole key, and nothing but the key. For a data warehouse, normalisation is a choice, not a law. Sources are normalised so they can write without anomalies; the warehouse denormalises on purpose so it can read without ten joins. What stays untouched is Codd's principle: the logical model separated from the physical one. That is why you can change indexes, partitions and storage formats without rewriting queries — and why the later lessons on indexes and memory never touch the SQL at all.

   Future users of large data banks must be protected from having to know how the data is organized in the machine (the internal representation).A Relational Model of Data for Large Shared Data Banks, Communications of the ACM 13(6), 1970 — prima propoziție a rezumatului

   Why it matters Every optimisation lesson in this course rests on the logical–physical split. Lose it and you end up rewriting reports every time a table moves.

   ▶Learn Database Normalization — 1NF, 2NF, 3NF, 4NF, 5NFVideo lesson · Decomplexify

3. 03

   Lesson 3 · An engine good at transactions is weak at analysis, and vice versa — not a defect, physics.

   “One Size Fits All”: An Idea Whose Time Has Come and Gone · Michael Stonebraker, Uğur Çetintemel · 2005

   OLTP (transaction processing): thousands of small operations per second, each touching a few whole rows — an order, a customer — and each has to be correct while thousands of others happen at the same time. Hence row storage, B-tree indexes and transaction isolation. OLAP (analytical processing): a handful of large queries, each touching two or three columns across hundreds of millions of rows and summing them. Hence column storage, compression and batch scanning. Stonebraker said it in 2005: the era of one engine for everything was over, and since then columnar, streaming, time-series and document engines have arrived. HTAP systems try to do both in one; the price is complexity and, usually, a columnar copy kept up to date in the background. The practical rule: do not run reports on the production database. An analytical scan on a transactional engine blocks or slows writes and misuses the indexes. A read replica is first aid; the warehouse is the treatment.

   The last 25 years of commercial DBMS development can be summed up in a single phrase: “One size fits all”. This phrase refers to the fact that the traditional DBMS architecture (originally designed and optimized for business data processing) has been used to support many data-centric applications with widely varying characteristics and requirements.“One Size Fits All”: An Idea Whose Time Has Come and Gone, ICDE 2005 — rezumat

   Why it matters The most common cause of a slow production system is not transaction volume but the morning report that scans the entire orders table.

   ▶OLTP vs OLAP and the row / column storage tradeoffVideo lesson · Ben Dicken

4. 04

   Lesson 4 · Declare the grain before anything else; the rest of the model follows from it.

   The Data Warehouse Toolkit · Ralph Kimball, Margy Ross · 2013

   Kimball's four steps come in that order for a reason. The business process is something that happens — a sale, a payment, a shipment — not a department. The grain is the sentence that says what one row of the fact table stands for: "one receipt line, in one store, on one day". Only then come the dimensions — who, what, where, when, the descriptive context — and the facts, the numeric measures that can be added up at the chosen grain. The result is the star schema: a narrow, long fact table with foreign keys to wide, denormalised dimensions, each carrying a surrogate key (an integer minted by the warehouse, not the code from the source). The classic mistake is to mix grains — order lines and order totals in the same table — and get doubled sums in the first report. The second mistake is three slightly different "Customer" dimensions; conformed dimensions, shared by every process, are what make two reports comparable.

   Select the business process. Declare the grain. Identify the dimensions. Identify the facts.Kimball Group, Dimensional Modeling Techniques — Four-Step Dimensional Design Process (aceiași pași ca în The Data Warehouse Toolkit, ed. a 3-a, cap. 3)

   Why it matters A fact table with an undeclared grain produces figures that contradict each other across reports, and nobody can say which one is right.

   ▶Data Modeling Tutorial: Star Schema (aka Kimball Approach)Video lesson · Kahan Data Solutions

5. 05

   Lesson 5 · Data Vault separates what is stable (keys), what links (relations) and what changes (attributes).

   Data Vault Series 1 — Data Vault Overview · Dan Linstedt · 2002

   The star schema is fast to read and easy to understand, but rigid: a new source or a new column means rewriting the dimension. The snowflake normalises the dimensions — fewer duplicates, more joins — and is usually not worth it. Data Vault answers a different problem: how to integrate twenty sources that change often, without rewriting anything and with a full audit trail. Three kinds of tables: hubs (business keys — a customer, a product), links (relations between keys — the customer bought the product) and satellites (descriptive attributes, with load date and source, hence full history). The cost is that a vault is not read directly: it has too many tables. The typical architecture is staging, then vault (raw and business), then star-shaped marts on top for reporting. The choice is not ideological. Few, stable sources: star directly. Many, changing sources with audit requirements: vault underneath, star on top.

   The Data Vault Model is a detail oriented, historical tracking and uniquely linked set of normalized tables that support one or more functional areas of business.Dan Linstedt, Data Vault Series 1 — Data Vault Overview (2002), definiția modelului

   Why it matters A warehouse that gets rewritten for every new source never reaches the tenth source. The choice of model decides how cheap change is.

   ▶What is a Data Vault? 3NF vs Dimensional model vs Data VaultVideo lesson · IT k Funde

6. 06

   Lesson 6 · Lakehouse: open files on object storage, with transactions and a schema on top.

   Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics · Michael Armbrust, Ali Ghodsi, Reynold Xin, Matei Zaharia · 2021

   The data lake promised cheap storage for anything: Parquet files on object storage, no enforced schema, no transactions. It often delivered a swamp: nobody knows what is clean, two jobs write over the same directory, a read catches half of a write. The classic warehouse has transactions, schema and speed, but in a closed format, as a copy of the data. The lakehouse puts an open table format between them (Delta, Iceberg, Hudi): a metadata log over the Parquet files that brings ACID transactions, "time travel" to an earlier version, schema evolution and, above all, several engines — SQL, Spark, an ML framework — reading the same files without copies. The medallion architecture organises the layers: bronze (data exactly as it arrived, never modified), silver (cleaned, deduplicated, correctly typed), gold (aggregated, modelled for consumption — usually as a star). The golden rule is that bronze is the evidence: anything in silver and gold must be rebuildable from it.

   This paper argues that the data warehouse architecture as we know it today will wither in the coming years and be replaced by a new architectural pattern, the Lakehouse, which will (i) be based on open direct-access data formats, such as Apache Parquet, (ii) have first-class support for machine learning and data science, and (iii) offer state-of-the-art performance.Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics, CIDR 2021 — rezumat

   Why it matters When a transformation turns out to be wrong six months later, the only question that matters is whether you still have the raw data. Bronze answers yes.

   ▶The Medallion Data Architecture (Pros & Cons)Video lesson · Kahan Data Solutions

7. 07

   Lesson 7 · The data mesh moves ownership of data to the domain that produces it; the platform and the rules stay shared.

   Data Mesh Principles and Logical Architecture · Zhamak Dehghani · 2020

   The problem the data mesh solves is organisational: the central data team becomes a bottleneck and does not know the semantics of twenty domains; when an order "was cancelled" means something different in logistics than in billing, the central team finds out last. The first principle moves ownership to the domain. The second requires what the domain publishes to be a product: a dataset with a schema, an owner, an SLA, documentation, an access address — discoverable, trustworthy, interoperable. The third, the self-serve platform, is what makes the first two possible: teams publish without each building their own infrastructure. The fourth, federated computational governance, says the shared rules — what counts as PII, which formats, which identifiers — are code checked automatically, not a committee. The trap: a mesh without a platform is twelve incompatible warehouses with a new name. And scale matters: for a company with a single data team, a well-modelled central warehouse is the right answer, not a scaled-down mesh.

   Domain-oriented decentralized data ownership and architecture. Data as a product. Self-serve data infrastructure as a platform. Federated computational governance.Data Mesh Principles and Logical Architecture, martinfowler.com (2020) — cele patru principii, în ordinea din articol

   Why it matters Most "mesh" failures are platform failures: ownership was decentralised before there was anything shared to publish on.

   ▶Introduction to Data Mesh — Zhamak DehghaniVideo lesson · Thoughtworks

8. 08

   Lesson 8 · Column storage, compression and vectorised execution are why a query over a billion rows takes seconds.

   C-Store: A Column-oriented DBMS · Michael Stonebraker et al. · 2005

   Three ideas explain almost everything about modern analytical databases. First: column storage. A query touching three columns out of fifty reads 6% of the bytes, not 100%. Second: compression. Within a column, neighbours look alike — the same country a thousand times, increasing dates, amounts in the same range — so dictionary, run-length and delta encodings compress tenfold, and the engine can often work directly on compressed data. Third: vectorised execution, in batches of thousands of values rather than tuple by tuple, so the processor is not waiting on memory. On top of them sit two architectural decisions. MPP (massively parallel processing): data is partitioned across nodes, each scans its share, and joins and aggregations require redistribution (the shuffle) — the expensive part. Separation of storage and compute: data lives on cheap, durable object storage and compute is started on demand and scaled separately; the price is cold latency and local caching. Micro-partitions carry min/max per block, so a filter on a date skips the blocks that cannot contain it. What columnar does not do well: small, frequent writes — load in batches.

   This paper presents the design of a read-optimized relational DBMS that contrasts sharply with most current systems, which are write-optimized.C-Store: A Column-oriented DBMS, VLDB 2005 — prima propoziție a rezumatului

   Why it matters Whoever understands why columnar is fast writes queries that filter on the partitioned columns and avoids inserting row by row into an analytical engine.

   ▶Column vs Row Oriented Databases ExplainedVideo lesson · Hussein Nasser

9. 09

   Lesson 9 · The log is the source of truth; the database is a cache of the latest value in the log.

   The Log: What every software engineer should know about real-time data's unifying abstraction · Jay Kreps · 2013

   A log is a list you only append to, in order. It sounds trivial, but it is the abstraction that ties together replication, streaming and the data warehouse. Every relational database has one inside — the transaction log — and every insert, update or delete passes through it before it reaches the tables. Change data capture (CDC) reads exactly that log and emits every change as an event: no periodic queries loading the source, no window lost between two runs and, above all, deletions included — a nightly SELECT never sees the row that vanished at noon. Events land in a distributed log; consumers read with an offset, so they can resume where they stopped or, if needed, replay from the beginning. In the warehouse, bronze receives the raw events and silver applies "latest value per key" (merge) or keeps the full history. The realistic semantics is at-least-once plus idempotency: every event carries a key and a version, and applying it twice yields the same result. Order matters per key, not across keys.

   A log is perhaps the simplest possible storage abstraction. It is an append-only, totally-ordered sequence of records ordered by time.The Log: What every software engineer should know about real-time data's unifying abstraction, LinkedIn Engineering (2013) — Part One: What Is a Log?

   Why it matters Periodic extraction with SELECT misses deletions and loads the source; a warehouse fed that way slowly loses accuracy without raising a single error.

   ▶Change Data Capture (CDC) Explained (with examples)Video lesson · Irtiza Hafiz

10. 10

    Lesson 10 · Accountants don't use erasers: record facts, derive states, recompute when you were wrong.

    Immutability Changes Everything · Pat Helland · 2015

    An accountant does not erase a wrong entry; they add a correcting one. The ledger stays complete, anyone can redo the arithmetic, and "the balance" is a derived value, not a fact. Helland shows that modern computing is moving the same way: transaction logs, event logs, Parquet files written once, even SSDs that do not overwrite in place. When data is immutable, whole classes of problems vanish — lost updates, inconsistent reads, "who changed this?" — and two precious things appear: audit is free and any state can be recomputed. In a data warehouse, facts are naturally immutable: a sale happened. Corrections become new rows with the opposite sign or with a validity period, and late-arriving facts are appended, not "patched" into the past. Dimensions keep their history through versions — the next lesson. The only real conflict is with the GDPR right to erasure, for which there is an answer compatible with immutability, in lesson 19.

    There is an inexorable trend towards storing and sending immutable data. We need immutability to coordinate at a distance and we can afford immutability, as storage gets cheaper.Immutability Changes Everything, CIDR 2015 — rezumat (secțiunea 2 se numește „Accountants Don't Use Erasers”)

    Why it matters A warehouse corrected in place cannot answer "what did the report show last month, and why". An append-only one always can.

    ▶Master Event Sourcing in Just 10 MinutesVideo lesson · ByteMonk

11. 11

    Lesson 11 · SCD type 2: when an attribute changes, you do not overwrite — you add a row and close the old one's period.

    The Data Warehouse Toolkit · Ralph Kimball, Margy Ross · 2013

    A customer moves from one city to another. What do you do with their row in the Customer dimension? Type 1: overwrite. Simple, no history — and the 2023 sales suddenly show up in the new city, so old reports change. Good for fixing mistakes and for attributes with no historical value. Type 2: add a new row, with a new surrogate key, valid_from and valid_to, and a flag for the current one. Old facts stay tied to the version of the time, new facts to the new version; the 2023 report stays exactly as it was. Type 3: keep a "previous value" column — a single step of history, useful when sales territories are realigned and you want both cuts at once. The choice is per attribute, not per table: the same dimension can have type 1 and type 2 columns. Two traps. First: joining facts on the natural key instead of the surrogate, which multiplies rows by the number of versions the customer has. Second: a type 1 "update" run by mistake on a type 2 dimension, which rewrites every version at once and erases the history without a single error.

    Slowly changing dimension type 2 changes add a new row in the dimension with the updated attribute values. This requires generalizing the primary key of the dimension beyond the natural or durable key because there will potentially be multiple rows describing each member.Kimball Group, Dimensional Modeling Techniques — Type 2: Add New Row (The Data Warehouse Toolkit, ed. a 3-a, cap. 5)

    Why it matters Without type 2, "sales by region in 2023" gives a different answer every year as customers move. History has to live in the dimension, not be guessed from the facts.

    ▶How to Track History in a Data Warehouse (Slowly Changing Dimensions)Video lesson · Kahan Data Solutions

12. 12

    Lesson 12 · Data outlives code: every schema change has to be readable by the old code and the new.

    Designing Data-Intensive Applications · Martin Kleppmann · 2017

    An application is replaced in five minutes; its data sits for five years. Hence two compatibilities: backward (new code reads old data) and forward (old code reads new data — because nothing is ever updated all at once). The rules that keep both are few and strict: add optional columns, never reuse a name with a different meaning, never change a type in place. A type change takes four steps: add the new column, fill it (backfill), move the readers, only then drop the old one. Formats with a schema (Avro, Protobuf, Parquet) make the rules checkable; free-form JSON leaves them to luck. The data contract is the same idea, raised to the level of teams: the producer and the warehouse agree on schema, semantics, SLA, owner and change policy, and the agreement is checked automatically on every publish. A change that breaks the contract is published as a new version, alongside the old. The reason is that the worst defects are silent: a column renamed at the source arrives as NULL, the report drops by 30% and nobody gets an error. The contract catches it at the boundary, where it is cheap.

    Thus, five-year-old data will still be there in the original encoding, unless you have explicitly rewritten it since then. This observation is sometimes summed up as data outlives code.Designing Data-Intensive Applications (2017), cap. 4 Encoding and Evolution — Dataflow Through Databases

    Why it matters An unannounced schema change does not raise an error; it produces a wrong report, discovered a month later. The contract moves the discovery to the boundary and to the minute.

    ▶What is a Data Contract?Video lesson · IBM Technology

13. 13

    Lesson 13 · ELT moves the transformation into the warehouse; what is left to design is orchestration and idempotency.

    Fundamentals of Data Engineering · Joe Reis, Matt Housley · 2022

    ETL transforms data in a separate tool before loading it; ELT loads it raw and transforms it with SQL inside the warehouse, where compute is cheap, the SQL lives in git and lineage can be read from the queries. The new order makes extraction thin (copy faithfully) and transformation rich (model, test, document). Orchestration is the graph of tasks: dependencies, schedule, retries, alerts, a view of what ran and what did not. The parameter of a run is the time window, never "now" — otherwise yesterday's run can no longer be repeated. Idempotency is the property that makes everything repairable: rerunning for the same window produces exactly the same rows. You get it by deleting and reloading the partition, or by merging on a key — never by blind appends, which double the data on the second run. With it, a backfill is just a loop over old windows, and a 3 a.m. failure is fixed with a rerun, not an investigation. Incremental loads carry a watermark and a window for late-arriving data; small files get compacted; everything is tested in CI, like any code.

    Data engineering is the development, implementation, and maintenance of systems and processes that take in raw data and produce high-quality, consistent information that supports downstream use cases, such as analysis and machine learning.Fundamentals of Data Engineering (2022), cap. 1 — definiția ingineriei datelor

    Why it matters A pipeline that cannot be rerun without doubling the data turns every incident into a manual clean-up operation.

    ▶ETL vs ELT: Powering Data Pipelines for AI & AnalyticsVideo lesson · IBM Technology

14. 14

    Lesson 14 · Quality is built into the pipeline, not inspected in the report.

    Out of the Crisis · W. Edwards Deming · 1986

    Data quality has dimensions that can be measured: completeness (are rows or values missing?), uniqueness (duplicate keys?), validity (values from the allowed set, correct types), consistency (do the lines sum to the order total?), timeliness (did it arrive on time?) and accuracy (does it match reality?). Deming's point applied here: tests are code and sit next to the transformations, not in a quarterly audit report. At intake, contracts (lesson 12). On every model: not null, unique, accepted values, relationships between tables, freshness, expected volume. At the output, anomaly monitors on the figures that matter — rows per day, distributions, sums. What you do when a test fails depends on what is at stake: for critical figures, the load stops (fail-closed) and someone looks; for the rest, suspect rows go to quarantine and the remainder passes. Every dataset has an owner and an SLA, and the cause is fixed upstream, not in the report. The report is where you find out; it is not where you fix.

    Cease dependence on inspection to achieve quality. Eliminate the need for massive inspection by building quality into the product in the first place.Out of the Crisis (1986), cap. 2 — cele 14 puncte pentru management, punctul 3

    Why it matters The same error costs one line of test at intake and a week of reconciliation when found in the report. The difference is the place, not the effort.

    ▶How to test your Data Pipelines with Great ExpectationsVideo lesson · BI Insights Inc

15. 15

    Lesson 15 · Garbage in, garbage out — and provenance is the only way to find where the garbage got in.

    Passages from the Life of a Philosopher · Charles Babbage · 1864

    The question that exasperated Babbage is still asked today, just differently: "the model is good, so the figures are good?". No. A warehouse does not fix what it receives; at best it makes visible where it came from. That is provenance (lineage): for every figure in a report, the path back through the transformations to the source rows and file, at column level, derived from the SQL that produced them. On every row, small metadata: source system, load identifier, extraction time. What it is for: impact analysis (if source X breaks, which reports suffer?), debugging (this odd figure — from which rows? which file? which day?), compliance (where personal data flows, which the GDPR explicitly requires) and reproducibility (same code, same data version, same result). The typical case: an amount column upstream switches from one currency to another without notice; the totals "look plausible" for two weeks. No type validation catches that. The only defence is being able to trace every figure back and see the day the distribution moved.

    On two occasions I have been asked, 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' … I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.Passages from the Life of a Philosopher (1864), cap. V — Difference Engine No. 1

    Why it matters The wrong figure that "looks fine" is the most expensive one in the warehouse. Without provenance you hunt it for weeks; with it, you find it in one query.

    ▶What is Data Observability?Video lesson · IBM Technology

16. 16

    Lesson 16 · Preparing data for ML: the same warehouse, but with correct time, no leakage and reproducible features.

    The Unreasonable Effectiveness of Data · Alon Halevy, Peter Norvig, Fernando Pereira · 2009

    The 2009 argument became doctrine: more data beats a cleverer model. The unspoken condition is that the data is correct in three ways a reporting warehouse does not demand. First, point-in-time correctness: a feature is computed "as it was at the time of the event", not "as it is now" — a customer who is premium today may have been on the free tier when they placed the 2023 order; type 2 dimensions are exactly the tool here. Then, leakage: a column that contains the answer, directly or indirectly — the cancellation date when predicting cancellation; the model looks excellent in training and fails in production. Finally, training–serving skew: the features computed in batch for training and those computed live for prediction must come from the same code; the feature store exists to guarantee that. The rest is warehouse discipline: split sets by time, not at random; document the label definition; version the training set so you can reproduce it a year later; keep class balance in mind. Only then does "a lot of data" mean more than "a lot of rows".

    But invariably, simple models and a lot of data trump more elaborate models based on less data.The Unreasonable Effectiveness of Data, IEEE Intelligent Systems 24(2), 2009

    Why it matters A model trained on "today's" features for "back then" events learns the future and forgets it in production. The warehouse must be able to answer "how it was at that moment".

    ▶A Chat with Andrew on MLOps: From Model-centric to Data-centric AIVideo lesson · DeepLearningAI

17. 17

    Lesson 17 · Embeddings: meaning becomes a vector, and the warehouse gets a column it cannot compare with "=".

    A synopsis of linguistic theory 1930–1955 · John Rupert Firth · 1957

    Firth's idea, formalised: a model reads a text and turns it into a vector of a few hundred or thousand numbers, so that texts with close meaning end up as close vectors. Closeness is measured — usually cosine — not checked by equality. Hence a new structure in the warehouse: the vector index (HNSW, IVF), which finds approximate nearest neighbours without comparing against every row. On top of it sits RAG: retrieve the relevant fragments by meaning, then let a generative model answer using them. What changes for the data engineer: the embedding model's version is part of the schema — change the model, recompute the column, otherwise you compare vectors from different spaces. Stored next to the vector: the text, a hash of it, the model, the dimension. Chunking is a modelling decision, not a detail. Metadata filters (language, date, customer) are applied before or together with the vector search, not after. And vectors do not replace keys and joins: they are one more column in a table that stays relational.

    You shall know a word by the company it keeps.A synopsis of linguistic theory 1930–1955, în Studies in Linguistic Analysis (1957)

    Why it matters A warehouse that does not know which model produced a vector cannot tell whether two vectors are comparable. A column without a version is a column without meaning.

    ▶What is a Vector Database? Powering Semantic Search & AI ApplicationsVideo lesson · IBM Technology

18. 18

    Lesson 18 · Separate identity from facts: an identity vault, a token in the warehouse and no way back without the key.

    Regulamentul general privind protecția datelor (GDPR) · Parlamentul European și Consiliul Uniunii Europene · 2016

    The regulation's definition, read as architecture, is a three-part schema. The identity vault: name, e-mail, national identifier, address — encrypted, access-restricted, in its own system. The token map: the link between a person and a pseudonym, either a random surrogate or an HMAC with a secret key; a plain hash of the e-mail is not enough, it is reversed with a dictionary. The warehouse: the facts, keyed by token. Analysts see tokens and behaviour, never identities; the join back is possible only for whoever holds the key, and is audited. What you gain: erasing a person becomes deleting the row from the vault — the facts remain, but lead to no one; access to identity is granted separately from access to analysis; "where is this person's data" is answered from provenance (lesson 15). What you do not gain: pseudonymised data is still personal data, because the additional information exists. All obligations remain; the risk drops. Real anonymisation is a different threshold, with traps of its own — lesson 20.

    'pseudonymisation' means the processing of personal data in such a manner that the personal data can no longer be attributed to a specific data subject without the use of additional information, provided that such additional information is kept separately and is subject to technical and organisational measures to ensure that the personal data are not attributed to an identified or identifiable natural personRegulamentul (UE) 2016/679, art. 4 pct. 5

    Why it matters A warehouse with the name on every row cannot erase a person without rewriting history. One with tokens erases them from a single row, in another room.

    ▶The Difference Between Pseudonymization & AnonymizationVideo lesson · comforte

19. 19

    Lesson 19 · Personal data is a liability: inventory it, collect the minimum, retain with a deadline and delete provably.

    Data Is a Toxic Asset, So Why Not Throw It Out? · Bruce Schneier · 2016

    Schneier flips the intuition that "data is the new oil": what you do not have cannot be stolen from you, cannot be subpoenaed and does not need an explanation for why you kept it. For the warehouse this becomes a four-stage cycle. Inventory: where the personal-data columns are, marked by classification (direct identifiers, quasi-identifiers, sensitive), discovered automatically where possible. Minimisation: collect only what serves a declared purpose; a full date of birth when you need an age band is a spare column. Retention: every dataset has a deadline and a reason, and deletion is a scheduled job, not a promise. Deletion: provable, including from backups and derived data. This is also where immutability (lesson 10) makes peace with the right to erasure: crypto-shredding. Each person's data is encrypted with their own key; deleting the person means destroying the key. The rows physically remain in the immutable files and in backups, but can never be read by anyone again. A log that cannot be rewritten and a deletion that cannot be undone, at once.

    Data is a toxic asset. We need to start thinking about it as such, and treat it as we would any other source of toxicity.Data Is a Toxic Asset, So Why Not Throw It Out? — eseu, CNN / schneier.com, martie 2016

    Why it matters Every personal-data column you do not use is risk without benefit. Its cost does not show on the storage bill, but on the day of the incident.

    ▶GDPR Compliant using Crypto ShreddingVideo lesson · PRISMA European Capacity Platform

20. 20

    Lesson 20 · No name does not mean anonymous: postcode, date of birth and sex identify most people.

    k-anonymity: a model for protecting privacy · Latanya Sweeney · 2002

    Sweeney showed, with real "anonymised" hospital data and a purchased voter list, that three mundane columns — postcode, date of birth and sex — are unique for most of the population. These columns are quasi-identifiers: each is harmless alone, together they are a name. k-anonymity is her answer: a release is safe only if every combination of quasi-identifiers appears for at least k people. It is achieved through generalisation (date of birth becomes a year or an age band, postcode becomes a county) and suppression (rows too rare are removed). That is not the end of the story. If all k people in a group share the same diagnosis, you still learned something about each — hence l-diversity and t-closeness, which demand variety in the sensitive attribute. And any "anonymous" set can be linked to another set published tomorrow. For the warehouse, the practical rule: an extract without names is not anonymous until you have counted its quasi-identifiers and checked k on every combination; and aggregation is your friend — a report by age band and county has no person rows at all.

    A release provides k-anonymity protection if the information for each person contained in the release cannot be distinguished from at least k-1 individuals whose information also appears in the release.k-anonymity: a model for protecting privacy, International Journal of Uncertainty, Fuzziness and Knowledge-Based Systems 10(5), 2002 — rezumat

    Why it matters The "no names" extract sent to a partner is the most common personal-data leak and the one least often recognised as such.

    ▶k-anonymity explainedVideo lesson · Security and Privacy Academy

21. 21

    Lesson 21 · Differential privacy: you add calibrated noise to the answer, not the data, and the promise survives any other released set.

    The Algorithmic Foundations of Differential Privacy · Cynthia Dwork, Aaron Roth · 2014

    The weakness of k-anonymity is that it depends on what else exists in the world. Differential privacy changes the question: not "can the set be re-identified?", but "does the answer to a query change if a single person enters or leaves the set?". If the answer is almost the same with or without you, nobody learns anything about you in particular, whatever else they know. The mechanism: to every aggregate answer (a sum, an average, a count) random noise is added, calibrated to how much a single individual can move the result. The parameter epsilon is the budget: smaller means more protection and less precision, and every query spends some of it — once the budget is spent, you stop answering. For the warehouse it is a technique for output, not storage: raw data stays whole and protected as in lessons 18–19; what is protected is what leaves — public statistics, reports to partners, training sets. Modern censuses use it exactly this way. The cost is real and must be told to users: on small groups, the noise is comparable to the signal.

    'Differential privacy' describes a promise, made by a data holder, or curator, to a data subject: 'You will not be affected, adversely or otherwise, by allowing your data to be used in any study or analysis, no matter what other studies, data sets, or information sources, are available.'The Algorithmic Foundations of Differential Privacy (2014), cap. 1 — The Promise of Differential Privacy

    Why it matters It is the only definition of privacy that does not collapse when another dataset appears tomorrow. The price is precision, and the price must be stated.

    ▶Differential Privacy — Simply ExplainedVideo lesson · Simply Explained

22. 22

    Lesson 22 · The warehouse is the biggest target in the company because it gathers everything; the defence is least privilege, applied to rows and columns, with audit.

    The Protection of Information in Computer Systems · Jerome Saltzer, Michael Schroeder · 1975

    A source system exposes one customer; the warehouse exposes all of them, with history. That is why the 1975 principle is more concrete here than anywhere. Least privilege applies on three axes: tables (a role sees only the schemas it needs), columns (dynamic masking: the analyst sees the last four digits, accounting sees everything) and rows (row-level security: a sales agent sees only their region, through the same view, without copies of the table). Access to the identity vault from lesson 18 is a separate role, granted separately. Underneath, two layers that are not negotiable: encryption at rest, with keys managed outside the warehouse and rotated, and encryption in transit. On top, audit: every query on sensitive tables is recorded — who, when, what was read, how many rows — retained and, above all, read by someone. The usual traps are mundane, which is why they are common: a service account for the BI tool with rights over the whole warehouse, used by everyone; secrets pasted into SQL; shared accounts that make the audit useless. Saltzer and Schroeder's rule, read backwards: any privilege you cannot justify by a task is one to remove.

    Every program and every user of the system should operate using the least set of privileges necessary to complete the job.The Protection of Information in Computer Systems, Proceedings of the IEEE 63(9), 1975 — §I.A.3, principiul (f), least privilege

    Why it matters A single over-privileged service account turns a breach in a reporting tool into a leak of the company's entire history.

    ▶Dynamic Data Masking and Row-Level SecurityVideo lesson · Microsoft Mechanics

23. 23

    Lesson 23 · Cloud or on-prem is not a question about place, but about who operates what and what it costs idle versus at peak.

    All Things Distributed · Werner Vogels · 2008

    In the cloud, compute is separated from storage and elastic: you pay per second or per query, buy no hardware, upgrades belong to the vendor, regions are a click away. The reverse: data egress costs, unbounded queries that produce surprise bills, vendor dependence and data residency, which for regulated data demands a European region and keys you manage. On-prem: predictable cost at steady load, full control, low latency to internal systems, sensitive data that never leaves. The reverse: capacity planning (you pay for the peak all year), upgrades, people, a single site. The decision rests on a few variables, not fashion: how variable the load is, what regulation demands, what the team knows, what licences already exist, how much goes out as egress. Hybrid is legitimate: the identity vault at home, token-keyed facts in the cloud. And Vogels' sentence holds in both places: the disk fails in your data centre too, the vendor has outages too. So backups tested by restoring, not by existing; recovery drills; two zones. "Where" is the small question; "what happens when it fails" is the big one.

    Everything fails, all the time.Werner Vogels — formulare reluată în prezentări și pe blogul All Things Distributed, din 2008

    Why it matters The cloud bill grows with every careless query; the on-prem bill is fixed and includes the peak you hit one day a year. Both are lost if you never tested a restore.

    ▶The True Cost of Cloud vs. On-Premises: A Comprehensive GuideVideo lesson · Cloud Computing Insider

24. 24

    Lesson 24 · A join executes in three ways, and Knuth's critical 3% in a warehouse is almost always a join.

    Structured Programming with go to Statements · Donald Knuth · 1974

    Three algorithms. Nested loop: for every row on one side, look it up on the other — unbeatable when one side is tiny or the other is indexed on the key, disastrous between two large tables. Hash join: build a hash table from the smaller side, then pass once through the larger side and match — the warehouse workhorse, provided the small side fits in memory; if not, it spills to disk and becomes ten times slower. Merge join: both sides sorted on the key, a single parallel pass — perfect when the data is already ordered. The planner chooses among them from statistics; stale statistics mean a wrong plan, so refreshing them is maintenance, not an option. In distributed systems one more question appears: where do the rows meet? A small dimension is broadcast to every node; two large tables are redistributed (shuffled) by key, and a hot key — often NULL — sends half the data to a single node. And the classic trap: a join on a non-unique key multiplies rows, silently; the type 2 dimension from lesson 11 is the typical example. Before optimising, read the plan. The 3% are there, with a name.

    We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.Structured Programming with go to Statements, ACM Computing Surveys 6(4), 1974

    Why it matters Nine queries out of ten do not matter; the tenth holds up the whole morning report, and inside it is a join that spills to disk or multiplies rows.

    ▶How do nested loop, hash, and merge joins work?Video lesson · The Magic of SQL

25. 25

    Lesson 25 · In a columnar warehouse, the data structure is the layout on disk: partition and order decide how many blocks need not be read.

    Mesaj pe lista de discuții git · Linus Torvalds · 2006

    The B-tree index is the king of transactional databases: it finds a row in a few steps. Columnar warehouses work on a different principle: they do not look for a row, they skip blocks. Every column block carries a min and a max (zone map); if the filter asks for "March" and the block covers "June–July", the block is not read at all. Hence two levers. Partitioning — usually by date — eliminates whole directories before the query starts. Clustering (or the sort key) groups nearby values into the same blocks, so the zone maps have something to skip; without it, every block contains a bit of everything and none can be skipped. The design rules follow the filters, not the arrival of data: partition on what queries filter by, cluster on the second column in the WHERE. Too many partitions mean small files and metadata more expensive than the data; compact them. For equality on high-cardinality columns, Bloom filters; for repeated aggregates, materialised views; and fresh statistics remain the condition for any of this to be used. Torvalds was talking about code, but the rule holds: do not optimise the query; lay the data out so the query has nothing to read.

    Bad programmers worry about the code. Good programmers worry about data structures and their relationships.Mesaj pe lista de discuții git, 27 iunie 2006

    Why it matters The difference between a three-second query and a three-minute one on the same data is usually just the order in which it was written to disk.

    ▶Understanding B-Trees: The Data Structure Behind Modern DatabasesVideo lesson · Spanning Tree

26. 26

    Lesson 26 · Every rung of the memory hierarchy is a hundred times slower than the one above; a query is fast exactly as long as it stays on the top rung.

    Tape is Dead, Disk is Tape, Flash is Disk, RAM Locality is King · Jim Gray · 2006

    Gray's figures still hold as orders of magnitude: RAM in nanoseconds, SSD in tens of microseconds, spinning disk in milliseconds, object storage in tens of milliseconds plus a per-request cost. Between rungs are factors of a hundred. "Disk is tape" means disk is only worth it for sequential reads — hence the columnar format, compression and sorted layout from lesson 25: they turn random access into a scan. "RAM locality is king" means the fast query is the one whose working set fits in memory: the join's hash table, the sort, the aggregation. The moment it no longer fits, it spills to the next rung and slows down ten to a hundred times, without raising any error. Cloud warehouses make the hierarchy explicit: object storage is the base, cheap and unlimited; compute nodes keep a cache on local SSD; recent results sit in a result cache. Sizing is not "how much memory the server has" but "how much memory each query slot gets" — ten parallel queries share the same RAM. Columnar compression is, in Gray's terms, the cheapest way to climb a rung: the same data, five times smaller, fits where it did not fit before.

    Tape is Dead, Disk is Tape, Flash is Disk, RAM Locality is King.Prezentare la Storage Guru Gong Show, Redmond, 10 decembrie 2006 (reluată la CIDR 2007)

    Why it matters A query that "worked yesterday" and today takes ten times longer did not get harder; its working set dropped a rung in the hierarchy.

    ▶Latency Numbers Programmer Should KnowVideo lesson · ByteByteGo

27. 27

    Lesson 27 · What to watch for: warehouses do not die of technology, they die of the lack of a model, an owner and a user.

    The Mythical Man-Month · Frederick P. Brooks Jr. · 1975

    Brooks said the tables explain the system; in a warehouse, the tables are the system. The first cause of failure is the missing model: sources copied side by side and called a "warehouse", with no facts and dimensions, no shared definition of "customer" or "revenue" — hence three reports with three figures. The second is the missing owner: datasets nobody answers for, without tests, where a column renamed at the source goes unnoticed for a month. The third is the missing user: a warehouse built "for when we will need it", instead of for a concrete decision on Monday morning. The rest is a checklist, briefly. Surrogate and natural keys confused, joins that multiply rows. Time zones and currencies mixed in the same column. Event time confused with load time. The NULL that disappears from aggregates and joins without notice. Unbounded queries in a system billed per read. Personal data on every row, with no vault and no deadline. Streaming where a daily load was enough. And missing documentation — although, as Brooks says, a well-named, well-keyed table is the best documentation you will ever write.

    Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowcharts; they'll be obvious.The Mythical Man-Month (1975), cap. 9 — Ten Pounds in a Five-Pound Sack

    Why it matters No tool makes up for a missing model, an ownerless dataset or a warehouse without a user. All three show in the tables, if you look.

    ▶Top 5 Data Engineering Pipeline Mistakes ExplainedVideo lesson · The Data and AI Guy

← All topics