Data+ Certification Prep

The exam you are preparing for

On 14 October 2025 CompTIA launched DA0-002, the second version of its Data+ exam, and in November 2026 it is the only version CompTIA's Data+ page describes. Every lesson in this course is built against that exam's published objectives. Before you write a single query, it pays to know exactly what the test looks like, because its format should shape how you prepare.

Here are the facts as CompTIA publishes them on its Data+ page and in the official objectives document, read on 2 November 2026. Check the page again before you book, because CompTIA can change any of them.

Two of those numbers deserve a second look. Ninety questions in ninety minutes sounds like a minute each, but performance-based questions take several minutes, so the multiple-choice items have to go faster than that to bank time. And 675 is not 67.5 percent, nor 75 percent of 900. It is a scaled score. CompTIA reports results on a scale from 100 to 900 and does not publish how many correct answers produce a given score, so no percentage target is reliable.

Once earned, the certification expires three years from the date you certify, and Data+ is one of the certifications in CompTIA's continuing education program, the route by which certified people renew before that date. The recommended experience is advice, not an entry requirement: nobody checks your work history at the test centre. This course is written for people with less than eighteen months in an analyst's chair, so each lesson carries the hands-on practice that the job would otherwise have given you.

Key idea: DA0-002 gives you at most 90 questions in 90 minutes, mixes multiple choice with performance-based tasks, and reports a scaled score on which 675 passes, so practise for speed and for doing, not only for recall.

Five domains, unevenly weighted

CompTIA divides the exam into five domains and tells you what share of the exam each one represents. The weights are your first study-planning tool.

Data Analysis is the heaviest single domain, but read the second and third rows together. Getting data ready and analyzing it make up 46 percent of the exam, and every objective in those two domains asks you to act on a scenario. Domain 1 has five objectives and only a fifth of the marks, and all five ask you to explain or identify. It rewards precise vocabulary: knowing that a varchar column stores text of varying length, or that Compass is a client for MongoDB, is the whole of what some of its questions ask.

Key idea: the weights tell you where the marks are; nearly half sit in preparing and analyzing data, where the exam expects you to choose and apply, not merely define.

Reading the objectives the way the exam does

The official objectives document for DA0-002 is labelled Exam Objectives Version 1.1, carries a 2024 copyright, and runs to eleven pages. It lists eighteen numbered objectives under the five domains, each followed by bulleted examples, and it ends with an acronym list of forty-one entries and a sample list of hardware and software for a practice lab. CompTIA's Data+ page shows only a short summary of the objectives; the Sources below link the full document on CompTIA's own file server. Keep it open while you study. This course paraphrases the objectives rather than copying them, and every lesson names the objective numbers it covers, so you can tick them off as you go.

Read the verb at the start of each objective, because it tells you how deeply the topic is tested.

• Explain (1.1 and 5.1) asks what something is and why it exists: why a table has a primary key, what data lineage records.
• Identify (1.2 to 1.5) asks you to recognize and name: given a description, pick the data source, the storage type, the tool or the AI technique.
• Summarize (5.2) asks for the gist of a body of rules, such as what GDPR or PCI DSS requires of the data you hold.
• Compare and contrast (5.3 and 5.4) asks you to choose between neighbours: masking or anonymization, a unit test or a user acceptance test.
• Given a scenario and Given a set of requirements ask you to apply the idea to a situation. Nine of the eighteen objectives start this way: all three in Domain 2, all three in Domain 3 and all three in Domain 4.

Two warnings sit in the document itself. First, CompTIA says the bulleted examples are not exhaustive, so a technology or task that fits an objective can appear even if it is not named. The acronym list proves the point: OLAP, OLTP, MDM and SDLC appear there but in none of the objectives' bullets, and this course teaches each of them where it belongs. Second, the document states that people who prepare with unauthorized materials, the leaked question collections known as brain dumps, will have their certifications revoked and be suspended from future testing. Every practice question in this course is original and written from the objectives, not from anyone's memory of the real exam.

One more caution applies to any objectives document: it is frozen on the day it was written, and software is not. The objectives name Azure Data Studio among the database tools in objective 1.4, but Microsoft retired that product on 28 February 2026. Learn the names the objectives use, and know what replaced them; Lesson 4 covers both.

Key idea: treat the objectives document as your syllabus, read each objective's verb as a depth gauge, and expect anything that reasonably belongs under an objective, named or not.

Performance-based questions and the clock

CompTIA lists Data+ among its exams with simulation performance-based questions, or PBQs. Its explanation of PBQs gives four facts that matter for strategy. You can skip a simulation PBQ and return to it later. Simulations have a reset button. Partial credit may be given. And there can be more than one way to solve the problem a PBQ poses.

CompTIA does not publish its PBQs, so treat what follows as a reasonable inference rather than a fact about the exam: the "Given a scenario" objectives are where hands-on tasks fit most naturally, such as putting cleaning steps in order, choosing the join that returns the rows a manager asked for, matching a chart to a question, or matching a control to a privacy requirement. The best preparation is the one this course builds into every lesson: running real queries, cleaning real columns and reading real output yourself, so a simulated screen feels familiar.

Those facts suggest a plan. When a PBQ appears early, read it, flag it, and move on to the multiple-choice items, then return to the PBQs with the time you have banked. A partly completed PBQ can still earn marks, so never leave one blank.

If an attempt goes badly, CompTIA's retake policy imposes no waiting period between the first and second attempts but requires at least fourteen calendar days before a third or later attempt, and every attempt is paid for in full; CompTIA offers no free retakes. If you are seventeen or younger, CompTIA's ID policy says that since 23 June 2026 a parent or guardian must give consent through CompTIA Central before you register.

Which version you will sit

CompTIA usually retires an exam about three years after launch and gives 2028 as its estimate for DA0-002. Its page of exam objectives under development, read on 2 November 2026, held drafts for several other certifications, DataSys+ among them, but nothing for Data+, so no new version is close. Check that page and the Data+ page again before you book.

If you own books or videos written for the earlier exam, DA0-001, know what changed. The table compares the two objectives documents.

DA0-002 adds objectives that the old exam did not have: infrastructure such as cloud storage and containers (1.3), artificial intelligence concepts (1.5), troubleshooting an analysis (3.3) and a report (4.3), and quality assurance testing such as unit, stress and user acceptance tests (5.4). It also condenses the statistics. DA0-001 named individual tools such as t-tests, chi-squared tests and simple linear regression; DA0-002 lists four broad methods, descriptive, inferential, predictive and prescriptive, plus measures of center and spread. Lessons 16 to 19 still teach the core tests, because "inferential" means little without them and the examples are not exhaustive.

One plain statement belongs in this lesson. CompTIA and Data+ are trademarks of CompTIA. CompTIA does not endorse or sponsor this course, and the course has no connection to CompTIA beyond following its published objectives. No course can promise a pass, and this one does not; what it can do is make sure nothing on the objectives list is new to you on exam day.

How to study so it lasts

In 2013 John Dunlosky and four colleagues reviewed the research on ten common study techniques. Two earned their highest rating, "high utility": practice testing and distributed practice, which means spreading study across many sessions instead of cramming. Five techniques were rated low utility, including rereading, highlighting and summarizing. Those are exactly the habits most learners rely on.

For Data+ the translation is direct. Use the ten questions at the end of each lesson as a test, not as a check after rereading. Explain each answer aloud, including why the wrong options are wrong, because the distractors are built from real mistakes analysts make. Come back to a lesson's quiz a few days later and again a week after that. For material that must become automatic, like the order of a SQL query's clauses or the difference between variance and standard deviation, do a short daily drill rather than one long session.

Plan on six to eight hours a week. Keep a lab notebook, paper or digital, where you record each query or command you run, what it returned and what you think it means. That notebook becomes your revision guide, and writing the meaning down is itself a form of retrieval.

Key idea: test yourself and space your sessions; rereading and highlighting feel productive but, in the research, do little for what you can recall weeks later.

The analyst's toolkit the exam names

Objective 1.4 asks you to identify common data analysis tools, and it sorts them into categories. The exam asks which category a named product belongs to and which tool fits a task, so learn the categories first and the names second. The last column shows the free choice this course uses for each.

One name in that table needs a warning before you install it at work. Anaconda's terms of service, effective 15 July 2025, allow free use for individuals working on their own non-commercial projects, for eligible academic and non-profit institutions, and for for-profit organizations with 200 or fewer employees and contractors; larger for-profit organizations need a paid plan. The pandas documentation, for its part, notes that the pandas packages in Anaconda are not managed by the pandas development team and recommends pip or the community-run conda-forge channel. This course therefore installs Python from python.org and its packages with pip, which needs no account and no license decision.

Building your lab

You need no paid software for this course and no account anywhere. Every lab uses small files and runs on an ordinary laptop; CompTIA's sample hardware list suggests 16 GB of memory, which matters for large data sets, not for these. Work through these steps once, in order.

1. Install LibreOffice from libreoffice.org. It is free and open source, runs on Windows, macOS and Linux, and its spreadsheet, Calc, is your stand-in for the Microsoft Office Suite on CompTIA's list.
2. Install Python 3 from python.org (on Linux, use your distribution's package manager). On Windows, tick the option to add Python to your path.
3. Create a virtual environment, a private folder of packages for this course, so nothing you install disturbs the rest of your system. Make a folder called dataplus-lab, open a terminal in it, and run python -m venv .venv (on Windows, py -m venv .venv). Activate it with source .venv/bin/activate on macOS or Linux, or .venv/Scripts/Activate.ps1 in Windows PowerShell, which accepts forward slashes.
4. Install the analysis packages: python -m pip install pandas matplotlib jupyterlab. Then start a notebook with jupyter lab; it opens in your browser but runs entirely on your computer.
5. SQL needs nothing extra. Python's standard library includes the sqlite3 module, a complete SQLite database engine. For a point-and-click view of your databases, install DB Browser for SQLite from sqlitebrowser.org, a free, open-source tool for Windows, macOS and Linux that can import CSV files into tables. If you want a server database as well, PostgreSQL is free from postgresql.org; it is optional throughout.

Your first procedure: one total, three tools

Analysts are paid to produce numbers other people trust, so the first procedure checks that your three tools agree. Create a text file called sales.csv in your dataplus-lab folder with exactly these seven lines:

order_id,region,amount
1001,North,120.50
1002,South,89.99
1003,North,240.00
1004,East,15.25
1005,South,310.10
1006,East,64.16

1. Calc. Open sales.csv in LibreOffice Calc, accept the comma as separator, and type =SUM(C2:C7) in an empty cell.
2. SQLite. In DB Browser for SQLite, create a database called lab.db, import sales.csv as a table named sales, and run SELECT SUM(amount) FROM sales; on the Execute SQL tab. Or, in a notebook, run import sqlite3, pandas as pd, then con = sqlite3.connect("lab.db"), then pd.read_csv("sales.csv").to_sql("sales", con, if_exists="replace", index=False) and con.execute("SELECT SUM(amount) FROM sales").fetchone().
3. pandas. In the notebook, run df = pd.read_csv("sales.csv") and then df["amount"].sum().

All three report 840: Calc shows 840, and SQLite and pandas show 840.0. Now change one input and watch the results come apart. In sales.csv, retype order 1003's amount as 24O.00, with a capital letter O where the zero should be, the kind of slip a hurried keyboard makes every day. Save the file and run all three again. These are the results this lesson's author got with LibreOffice, SQLite 3.50 and pandas 3.1:

One typo, three tools, three different wrong answers, and not one error message. The true total, with the value the clerk meant, is still 840. Calc's answer is off by the whole order; SQLite's is off by 216; pandas did not produce a number at all, which at least is hard to miss.

The fix is to make the tool tell you which values failed instead of quietly guessing. In pandas, run pd.to_numeric(df["amount"], errors="coerce"): every value that cannot be read as a number becomes NaN, pandas' marker for a missing value. The sum of that converted column is 600.0, and asking for the rows where it is missing, df[pd.to_numeric(df["amount"], errors="coerce").isna()], returns exactly one row, order 1003. Now you know what to fix, and you can fix it at the source rather than in your total.

Key idea: a total is only as trustworthy as the type of every value in the column, and spreadsheets, databases and pandas each handle a bad value differently, often without warning. Check types before you add anything up; Lesson 2 shows how.

Common misconceptions

• "675 means I need 67.5 percent." The score is scaled from 100 to 900, and CompTIA does not publish the conversion. Aim to know the material, not to hit a guessed percentage.
• "If a term is not in the objectives, it will not be tested." The objectives document says its bulleted examples are not exhaustive, and its own acronym list includes terms, such as OLAP and MDM, that no bullet names.
• "I need Tableau or Power BI to prepare." Objective 1.4 asks you to identify tools, and the visualization objectives ask you to choose and validate visuals. Those skills transfer between products, and the free tools in this course teach them.
• "No error message means the number is right." The procedure above produced three wrong totals without a single error. Silence from a tool is not a check.
• "Rereading my notes is the safest way to prepare." Rereading was rated low utility in Dunlosky and colleagues' review. Practice testing and spaced sessions were rated high.

What to carry forward

• DA0-002: at most 90 questions, multiple choice plus performance-based, 90 minutes, a scaled passing score of 675 on 100 to 900, launched 14 October 2025, retirement estimated in 2028. No successor draft was listed on 2 November 2026.
• Domain weights: Concepts 20%, Acquisition and Preparation 22%, Analysis 24%, Visualization and Reporting 20%, Governance 14%.
• Objective verbs set the depth. Nine of the eighteen objectives start "Given a scenario" or "Given a set of requirements", every one in Domains 2, 3 and 4.
• Simulation PBQs can be skipped and revisited, have a reset button, and may earn partial credit.
• Objective 1.4's categories: coding environments (IDE, text editor, notebook), packages and libraries, languages, BI software and database tools.
• Your lab: LibreOffice Calc, Python with pandas and JupyterLab in a virtual environment, SQLite through Python or DB Browser for SQLite. No account, no fee.
• One bad value gave three different totals in three tools. Check every column's type before you trust a number.

Sources

1. CompTIA. (n.d.). Data+ (V2) certification: Exam details and objectives summary. Retrieved 2 November 2026. comptia.org
2. CompTIA. (2024). CompTIA Data+ DA0-002 certification exam: Exam objectives (Version 1.1). CompTIA (PDF)
3. CompTIA. (2020). CompTIA Data+ certification exam objectives, exam number DA0-001 (Version 2.0). CompTIA (PDF)
4. CompTIA. (n.d.). CompTIA exam objectives under development. Retrieved 2 November 2026. comptia.org
5. CompTIA. (n.d.). Performance-based questions explained. comptia.org
6. CompTIA. (n.d.). CompTIA certification retake policy; Certification renewal policy; Candidate ID policy. Retrieved 2 November 2026. retakes; renewal; ID and minors
7. Dunlosky, J., Rawson, K. A., Marsh, E. J., Nathan, M. J., and Willingham, D. T. (2013). Improving students' learning with effective learning techniques. Psychological Science in the Public Interest, 14(1), 4-58. doi.org
8. Microsoft. (2026). What's happening with Azure Data Studio. Microsoft Learn. learn.microsoft.com
9. Anaconda, Inc. (2025). Terms of service (effective 15 July 2025). anaconda.com
10. pandas development team. (2026). Installation; Migration guides (the pandas 3.0 string dtype); pandas.to_numeric. pandas 3.1.0 documentation. installation; migration; to_numeric
11. Python Software Foundation. (n.d.). Virtual environments and packages (Python tutorial, chapter 12) and sqlite3: DB-API 2.0 interface for SQLite databases. docs.python.org; sqlite3
12. SQLite. (n.d.). SQL language expressions, section on CAST expressions. sqlite.org
13. The Document Foundation. (n.d.). Converting text to numbers. LibreOffice Calc Help. help.libreoffice.org
14. Project Jupyter. (n.d.). Installation. JupyterLab documentation. jupyterlab.readthedocs.io
15. Microsoft. (2026). Download Power BI Desktop. Microsoft Learn. learn.microsoft.com

A fifth of the gene lists were wrong

In August 2016 three researchers, Mark Ziemann, Yotam Eren and Assam El-Osta, published the results of a scan of 35,175 supplementary Excel files from 18 genomics journals, covering 2005 to 2015. Of the 3,597 papers that attached lists of genes, 704, or 19.6 percent, contained gene names the spreadsheet had quietly changed. SEPT2, the gene for septin 2, had become the date 2-Sep. MARCH1 had become 1-Mar. Identifiers from the RIKEN institute had turned into floating-point numbers, which is unsurprising once you notice that an identifier such as 2310009E13 is, to a spreadsheet, the number 2,310,009 times ten to the thirteenth.

The tempting diagnosis is that the software had a bug. It did not. It did exactly what it was built to do: look at a piece of text, guess what kind of value it represents, and store it as that type. A data type is the declaration that tells a system how to store a value, how to compare and sort it, and what arithmetic it allows. If you do not declare it, something else guesses, and the guess is made once, silently, at import.

Objective 1.1 lists the types you must know: strings (char, varchar, nvarchar), null values, spatial, Boolean, numeric (integer, decimal, float), datetime with timestamp, large objects (BLOB and CLOB), and GUIDs or UUIDs. This lesson works the other way round from a glossary. It starts from five wrong conclusions in one ordinary report and traces each back to the type decision that caused it.

The point: a data type is a decision about meaning, and every wrong type produces wrong answers later, usually without an error message.

Five wrong conclusions from one customer file

Suppose a colleague loads a loyalty-program export, customers.csv, into a spreadsheet and pandas with default settings, and drafts a report. Here is a small piece of the file, exactly as it arrived:

member_id,name,postal_code,signup,rating,discount
4111111111111111111,Ana Lima,02134,03/04/2025,4,0.10
4111111111111111112,Zoë Martin,10001,12/01/2024,,0.20
,Đặng Văn An,60614,01/15/2025,8,0.00

Every conclusion in the draft below is wrong, and none of them came with an error message. The right-hand column names the type problem underneath; the rest of the lesson explains each one. The figures in the table are what pandas 3.1 actually produced from these three rows.

Notice what the five have in common. Each value was stored, displayed and used without complaint. The type was wrong at the moment of import, and every later step inherited the mistake. That is why the exam treats types as a data concept, not a programming detail: the analyst who picks the types decides what the numbers will mean.

Text: char, varchar and nvarchar

Start with the first two symptoms, because both have the same fix. A postal code, a phone number, an account number and a product code look like numbers, but you will never add, average or multiply them. They are labels made of digits, and labels belong in a string type. The rule of thumb is short: if arithmetic on it would be meaningless, store it as text. Read the postal code and member ID columns as strings and both symptoms disappear: 02134 keeps its zero, and the two IDs stay different.

SQL offers several string types, and the exam names three.

The n in nvarchar is a Microsoft name for a Unicode type. Microsoft's documentation describes nchar and nvarchar as storing the full range of Unicode characters in UTF-16 when a supplementary-character collation is used, and it warns that writing a string literal without the N prefix converts it to the database's default code page, which may not recognize some characters. That is how a customer called Đặng becomes a row of question marks in a varchar column on an older server. PostgreSQL and SQLite simply store text in the database's encoding, normally UTF-8, so they need no separate national type.

The point: identifiers and codes are text, even when they are made of digits; choose char for fixed-length codes, varchar for most text, and nvarchar on SQL Server when names may use any script.

Numbers: integer, decimal and float

Now the discount total. Type 0.1 + 0.2 into Python, or SELECT 0.1 + 0.2; into SQLite, and both answer 0.30000000000000004. Ask SQLite whether 0.1 + 0.2 = 0.3 and it answers 0, false. Nothing is broken. A floating-point number is stored in binary, and just as one third has no exact decimal form, one tenth has no exact binary form. Microsoft's explanation for Excel puts the stored error for 0.1 at about 2.8 times ten to the minus seventeenth, and notes that Excel, like other spreadsheet programs, keeps about 15 significant digits. The Python tutorial gives the same arithmetic from the language side.

Numeric types fall into three families, and the exam expects you to match each to its job.

PostgreSQL's manual is blunt about the choice: decimal and numeric are the same type, it is "especially recommended for storing monetary amounts", and if you need exact storage and calculation you should use numeric rather than the inexact real and double precision types. Python offers the same exactness through its decimal module: Decimal("0.1") + Decimal("0.2") is exactly Decimal("0.3").

Floating point also explains the member IDs. A 64-bit float has 53 bits of precision, so it can represent every whole number only up to 2 to the 53rd, 9,007,199,254,740,992. Beyond that, neighbouring floats are spaced more than one apart. Around 4.1 quintillion they are 512 apart, so 4111111111111111111 and 4111111111111111112 both round to 4111111111111111168. In the file above, pandas would have kept the IDs as exact 64-bit integers if every row had one; the single blank forced the whole column to float, because the integer type has no way to mark a missing value. In a full export of 50,000 members, one empty cell would change the type of every ID in the file.

The point: integers count, decimals carry money, and floats measure; a float is an approximation by design, and it cannot hold long identifiers exactly.

Dates and times: datetime and timestamp

The fifth symptom is a sorting problem. As text, '12/01/2024' is greater than '03/04/2025', because text is compared character by character and '1' comes after '0'. SQLite confirms it: SELECT '03/04/2025' < '12/01/2024'; returns 1, true. Written the ISO 8601 way, year first, the same comparison '2025-03-04' < '2024-12-01' returns 0, which is correct. ISO 8601 text sorts in time order; month-first and day-first text do not. And 03/04/2025 is 4 March in the United States but 3 April in most of Europe, so text dates are ambiguous as well as unsortable.

The cure is a real date or date-time type, which stores a point in time and sorts, compares and subtracts as time. Systems differ in what they offer, and the differences are examined on the exam.

• date holds a calendar day; datetime or timestamp holds a day plus a time. In PostgreSQL, plain timestamp means timestamp without time zone. Its partner, timestamp with time zone (timestamptz), stores the instant in UTC and displays it in the session's local zone. Both resolve to one microsecond.
• Unix time counts seconds since 1970-01-01 00:00:00 UTC. Many web APIs send milliseconds instead: 1730505600000 is 2 November 2024 at midnight UTC.
• SQLite has no date type at all. Its documentation says dates are stored as ISO 8601 text, as Julian day numbers in a REAL, or as Unix time in an INTEGER, and its date functions convert between them.
• SQL Server's timestamp is not a time. Microsoft documents timestamp as a deprecated synonym for rowversion, an automatically incrementing binary number used to tell whether a row has changed; it "does not preserve a date or a time". To record one, Microsoft says to use datetime2.

In pandas, the fix for the report is one line: pd.to_datetime(df["signup"], format="%m/%d/%Y"). Stating the format matters, because it settles the month-or-day question instead of letting the library guess. After conversion, the latest signup is 4 March 2025.

The point: store dates as dates, write them as year-month-day when they must be text, and know whether a timestamp carries a time zone.

Null: the value that is not there

The rating symptom is the subtlest, because the wrong answer came from someone trying to be helpful. The second customer did not answer the survey. Her rating is null: not zero, not an empty string, but no value at all. Filling it with 0 asserted that she gave the lowest possible score.

SQL treats null as unknown, and that one idea explains all its behaviour. These results come from SQLite with three ratings, 4, null and 8:

pandas follows the same logic with its own marker. Missing numbers are NaN, and in pandas 3 missing text in the default str type is NaN too; df["rating"].mean() skips it and returns 6.0, while df["rating"].fillna(0).mean() returns 4.0. Whether to fill a gap, and with what, is a decision about meaning that Lessons 13 and 15 take up as imputation. The type lesson is simpler: keep null as null until you have decided.

The point: null means unknown; aggregates skip it, comparisons with it are never true, and turning it into zero changes the answer.

Booleans, places, large objects and identifiers

The remaining types on the objective list are less common in a sales file but come up constantly in exam scenarios.

• Boolean holds true or false, and in SQL a nullable Boolean adds unknown. Use it for flags such as is_member or opted_in, instead of a text column holding Y, yes, 1 and TRUE in the same field. SQLite has no separate Boolean storage class; it stores 0 and 1, and since version 3.23.0 the keywords TRUE and FALSE are simply other spellings of 1 and 0.
• Spatial types hold locations and shapes: points, lines and polygons. SQL Server offers two. Its geometry type works on a flat, Euclidean plane, suitable for a floor plan or a small local grid; its geography type works on a round earth with latitude and longitude, which is what distances between stores in different cities need. Spatial values are often written as well-known text, such as POINT(-122.349 47.651) for a spot in Seattle, longitude first. Microsoft's documentation warns that its own geography Point method takes latitude first, reversed compared with well-known text, and swapped coordinates are a classic source of maps with every store in the ocean.
• Large objects hold values too big for ordinary columns. A BLOB, binary large object, stores bytes: scanned contracts, product photos, audio. A CLOB, character large object, stores long text: call transcripts, policy documents. Oracle allows each to reach terabytes. Analysts rarely calculate on them directly; often the database keeps a path or address and the file itself sits in object storage, a choice Lesson 7 returns to.
• GUID or UUID identifiers are 128-bit values written as 36 characters, 32 hexadecimal digits in groups of 8-4-4-4-12, such as f81d4fae-7dec-11d0-a765-00a0c91e6bf6. RFC 9562, published in May 2024, defines them and notes that GUID is another name for the same thing. Version 4 UUIDs are random; version 7 UUIDs begin with a Unix-time timestamp in milliseconds, so new ones sort roughly in creation order. Their selling point is that two systems can generate identifiers independently, with practically no risk of collision, which matters the day two companies merge their customer tables and both numbered their customers from 1. PostgreSQL has a native uuid type, and Python 3.14 can generate both versions with uuid.uuid4() and uuid.uuid7().

Choosing types for a real table

Here is the customer file again, with a type chosen for every column and the reason. Writing this table before you import anything is the cheapest data-quality step there is.

In pandas, the same decisions go into the import: pd.read_csv("customers.csv", dtype={"member_id": "str", "postal_code": "str"}) keeps both identifiers as text, and the date conversion above handles signup.

Common misconceptions

• "If it is made of digits, it is a number." Postal codes, phone numbers and account numbers are labels. Treating them as numbers deletes leading zeros and, for long IDs, digits.
• "Null is the same as zero, or as an empty string." Null is unknown. Aggregates skip it, comparisons with it are never true, and filling it with zero changes averages.
• "Float is fine for money if I show two decimal places." Formatting changes the display, not the stored value. Sums of floats drift; decimal types do not.
• "A timestamp column always holds a date and time." In SQL Server, timestamp is a deprecated synonym for rowversion, an incrementing number that records no time at all.
• "nvarchar(50) holds 50 characters." Microsoft calls this a common misconception: n counts byte-pairs, and characters outside the basic plane take two.

Recap

• A data type decides storage, sorting, comparison and arithmetic. If you do not choose it, an import tool guesses.
• Strings: char(n) fixed and padded, varchar(n) variable, nvarchar(n) Unicode on SQL Server. Identifiers made of digits belong here.
• Numbers: integer for counts, decimal or numeric for exact values such as money, float for approximate measurements. 0.1 + 0.2 is not exactly 0.3 in floating point.
• Dates: use date and timestamp types; ISO 8601 text sorts correctly, month-first text does not. Know whether a timestamp has a time zone, and that SQL Server's timestamp is not a time.
• Null is unknown: AVG skips it, COUNT(column) ignores it, = NULL never matches; use IS NULL.
• Boolean for flags; spatial geometry for flat planes and geography for the round earth; BLOB for binary and CLOB for long text; GUID/UUID for identifiers that must be unique across systems.

Sources

1. Ziemann, M., Eren, Y., and El-Osta, A. (2016). Gene name errors are widespread in the scientific literature. Genome Biology, 17, 177. PubMed Central
2. Python Software Foundation. (n.d.). Floating-point arithmetic: Issues and limitations (Python tutorial, chapter 15) and decimal: Decimal fixed-point and floating-point arithmetic. tutorial; decimal
3. Microsoft. (2026). Floating-point arithmetic may give inaccurate result in Excel. Microsoft Learn. learn.microsoft.com
4. PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation, sections 8.1 Numeric types, 8.3 Character types, 8.5 Date/time types and 8.12 UUID type. numeric; character; date/time; uuid
5. Microsoft. (n.d.). nchar and nvarchar (Transact-SQL); rowversion (Transact-SQL); Spatial data (SQL Server); Point (geography data type). Microsoft Learn. nvarchar; rowversion; spatial; Point
6. SQLite. (n.d.). Datatypes in SQLite (Boolean and date/time storage) and NULL handling in SQLite. datatypes; nulls
7. Oracle. (n.d.). Data types, in Oracle Database SQL language reference 19c (CHAR, VARCHAR2, BLOB, CLOB). docs.oracle.com
8. Davis, K., Peabody, B., and Leach, P. (2024). Universally unique IDentifiers (UUIDs) (RFC 9562). RFC Editor. rfc-editor.org
9. pandas development team. (2026). Migration guides (missing values in the pandas 3 string dtype). pandas 3.1.0 documentation. pandas.pydata.org

Fifteen thousand results that did not fit

On 4 October 2020 Public Health England announced that 15,841 positive Covid-19 test results, from 25 September to 2 October, had been left out of England's daily figures. Its statement gave a technical cause: some files containing positive results had "exceeded the maximum file size" of the system that loaded them into central databases, and the quick fix was to split large files into smaller ones. Contact tracers spent the following weekend catching up on people who should have been called days earlier.

No one had typed a wrong number. The data was correct and the file holding it was the wrong shape for the job. Every file format is a container with rules: what it can hold, what it preserves, how big it may grow, and what the program reading it must assume. Objective 1.1 asks you to recognize six file extensions, .csv, .xlsx, .json, .txt, .jpg and .dat, and three kinds of structure: structured, semi-structured with its nested JSON, and unstructured. This lesson sets them side by side and then reads the comparison, because the exam's questions are nearly all of the form "which one fits this situation".

Six extensions, side by side

Read the table by columns rather than rows and three lessons appear.

First, only two of the six carry types. A CSV or TXT file is a stream of characters; the number 02134 and the text 02134 are written identically, which is why Lesson 2's import problems happen at all. An .xlsx cell has a type, and a JSON value is either a string, a number, a Boolean or null, so both formats can say "this is text" where CSV cannot.

Second, the formats differ in what they can nest. CSV, TXT and a simple worksheet are flat: rows and columns, nothing inside a cell. JSON can put an array of order items inside an order, and a customer object inside that. Flat formats are easy to load into a table; nested formats describe real things more naturally but must be flattened before most analysis.

Third, the extension is a promise, not a guarantee. A .dat file may be fixed-width text, delimited text or a program's private binary format. A .csv file from a European system may use semicolons. A .txt file may be a log or a novel. You learn the true format by opening the file in a text editor and reading its documentation, not by reading its name.

Why this matters: choose a format for what it must carry, types, nesting, size and layout, and never trust an extension to tell you how a file is built.

CSV: simple, everywhere, and loosely defined

CSV is the lingua franca of data exchange precisely because it is so simple, but its simplicity hides gaps. The closest thing to a specification is RFC 4180, published in October 2005, and it describes itself as an Informational document that "does not specify an Internet standard of any kind". The Library of Congress, which keeps format descriptions for preservation, calls CSV a format "for which no single, official specification exists". RFC 4180 records the common rules:

• each record sits on its own line, and an optional first line names the fields;
• fields that contain commas, double quotes or line breaks are wrapped in double quotes;
• a double quote inside a quoted field is written twice.

So a customer called Lima, Ana must appear as "Lima, Ana", or every reader will split her into two columns, and a product called 6" stake must appear as "6"" stake". Real files bend the rules in predictable ways. The Library of Congress notes that where the comma is the decimal separator, as in much of Europe, the field separator is often a semicolon; some files use tabs; and the character encoding is rarely declared. A file of names written in one encoding and read in another produces gibberish such as ë where ë belongs.

The defence is to state your assumptions instead of accepting defaults. In LibreOffice Calc, the Text Import dialog lets you set the character set, choose the separator (tab, semicolon, comma or another character), and set a column's type to Text so identifiers keep their leading zeros; switching off "Detect special numbers" stops Calc from turning date-like text into dates. In pandas, the same choices are arguments: pd.read_csv("orders.csv", sep=";", decimal=",", encoding="utf-8", dtype={"sku": "str"}).

Spreadsheets: a package, not a table

An .xlsx file looks like a table on screen but is something else on disk. The Library of Congress describes it as Office Open XML, standardized as ECMA-376 and ISO/IEC 29500, packaged so that you can rename it to .zip, open it with ordinary ZIP software, and find the workbook's sheets, shared strings and styles as separate XML files. That design is why a spreadsheet can carry several sheets, formulas, formatting and charts, and also why it is a poor exchange format for data alone.

For analysts the trouble is less the format than how people use it. A worksheet made for human eyes often has a title in row 1, two header rows, merged cells, subtotal rows between groups and a notes column. Each of those breaks the assumption that every row is one record with the same columns. The other limit is size: Microsoft lists a current Excel worksheet's maximum as 1,048,576 rows by 16,384 columns. A transaction log larger than that belongs in a CSV file or a database, not in a sheet.

JSON and XML: data that describes itself

JSON, JavaScript Object Notation, is defined by RFC 8259 (December 2017). It can represent four primitive types, strings, numbers, Booleans and null, and two structured types, objects and arrays. Because every value is labelled by its key, a JSON record describes itself, and two records in the same file may carry different keys. That flexibility is what "semi-structured" means: there is structure, but it is carried inside the data rather than fixed in advance by a table's columns.

Here is one online order in two shapes. As CSV, an order with two items needs two rows, and the order-level facts repeat on each:

order_id,order_date,customer,sku,qty,unit_price
5001,2026-10-30,"Lima, Ana",TENT-2P,1,189.00
5001,2026-10-30,"Lima, Ana",STOVE-1,2,34.50

As JSON, the items nest inside the order and the customer is an object of its own:

{"order_id": 5001,
 "order_date": "2026-10-30",
 "customer": {"name": "Ana Lima", "loyalty_id": "4111111111111111111"},
 "items": [{"sku": "TENT-2P", "qty": 1, "unit_price": 189.00},
           {"sku": "STOVE-1", "qty": 2, "unit_price": 34.50}]}

Two details in that JSON are deliberate. The loyalty ID is a string, in quotation marks, because RFC 8259 warns that only integers between minus and plus 2 to the 53rd, less one, are interoperable across implementations; a 19-digit number would be rounded by any reader that stores numbers as 64-bit floats, the same trap as in Lesson 2. And the RFC requires JSON exchanged between independent systems to be encoded in UTF-8, so JSON at least settles the encoding question that CSV leaves open.

XML, Extensible Markup Language, is JSON's older cousin and appears in the exam's acronym list. It wraps each value in named tags, such as <sku>TENT-2P</sku>, can nest elements to any depth, and can add attributes. You meet it in older web services, in many government data feeds, in RSS news feeds and, as you have just seen, inside every .xlsx file. It is semi-structured for the same reason JSON is: the labels travel with the values.

Structured, semi-structured, unstructured

The three structure categories in objective 1.1 are about how much of a data set's organization is fixed before the data arrives.

Two refinements keep this honest. Unstructured does not mean useless: a folder of support emails holds the best evidence a company has about why customers leave, and Lesson 8's natural language processing exists to read it. And one object can mix categories. A .jpg is unstructured pixels, but its embedded metadata, the camera, the time, sometimes the GPS position, is a small set of labelled fields, semi-structured data riding inside unstructured content.

Why this matters: structure decides your first step. Structured data you can query at once, semi-structured data you navigate or flatten, and unstructured data you must first turn into features.

Flattening nested JSON: a worked example

The United States Geological Survey publishes a live feed of earthquakes as GeoJSON, a JSON dialect for geographic data, with no account or key required. Its documentation lays out the shape: a FeatureCollection with a metadata object, a bounding box, and a features array; each feature has a properties object (magnitude, place, time, a link and more), a geometry object of type Point whose coordinates run longitude, latitude, depth, and an id. The feeds are regenerated every minute.

Load the past day's quakes of magnitude 2.5 and above into pandas:

import json, urllib.request
import pandas as pd

url = "https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/2.5_day.geojson"
with urllib.request.urlopen(url) as r:
    data = json.load(r)

quakes = pd.json_normalize(data["features"])
quakes["when"] = pd.to_datetime(quakes["properties.time"], unit="ms", utc=True)
coords = pd.DataFrame(quakes["geometry.coordinates"].tolist(),
                      columns=["longitude", "latitude", "depth"])
quakes = quakes.join(coords)

Each line does one job. json_normalize turns every feature into a row and every nested key into a column with a dotted name, such as properties.mag. The time arrives as a long integer of milliseconds since 1 January 1970: when this lesson was written, the first quake in the feed had the time 1793638157895, which pandas converted to 2 November 2026 at 16:49:17.895 UTC, the same instant the USGS's own CSV service reported for that event. The coordinates arrive as a list inside one cell, so the next line splits them into three named columns. Notice the order. The GeoJSON lists longitude first, while the same service's CSV output puts latitude first; read the documentation for each format rather than assuming.

For records that contain arrays, such as the order above, json_normalize can produce one row per item and carry order-level fields alongside: pd.json_normalize(order, record_path="items", meta=["order_id", ["customer", "name"]]) returns two rows, one for the tent and one for the stoves, each labelled with order 5001 and Ana Lima. That operation, one row per element of a list, is what objective 2.3 calls exploding, and Lesson 15 practises it.

Text that is not CSV: fixed-width files and .dat

Statistical agencies and older business systems often publish fixed-width text: no separators at all, just columns at agreed character positions, with a record layout document saying, for example, that characters 1 to 9 are the household ID and 10 to 11 the state code. The extension may be .txt or .dat. pandas reads such files with pd.read_fwf, given the column positions or widths from the layout document. Without the layout, a fixed-width file is a wall of digits.

The general rule for any unfamiliar file is a three-step inspection before analysis: open it in a plain text editor and look at the first lines; find the producer's documentation, data dictionary or record layout; then import with every assumption stated. If a text editor shows unreadable symbols, the file is binary, and only the producing program, or a reader written for its format, can open it.

Common misconceptions

• "CSV has one official set of rules." RFC 4180 is Informational and says it specifies no standard; separators, quoting and encodings vary between producers.
• "An .xlsx file is just a table." It is a ZIP package of XML parts that can hold many sheets, formulas and formatting, and the human layout of a sheet often breaks the one-row-one-record rule.
• "JSON numbers can safely carry any ID." RFC 8259 guarantees interoperability only for integers within 2 to the 53rd minus 1. Long identifiers should travel as strings.
• "Unstructured data cannot be analyzed." It cannot be queried directly, but text, audio and images can be turned into structured features by natural language processing, speech-to-text and image recognition.
• "The extension tells me how to read the file." A .dat file can be almost anything, and a .csv file may use semicolons or tabs. Inspect, then read the documentation.

What you now know

• .csv is flat, typeless text with loose rules; .txt is any plain text layout; .xlsx is a zipped package of XML with sheets, types and formulas and a row limit; .json is self-describing and nestable; .jpg is a compressed image; .dat is whatever its producer made it.
• Structured data has a fixed schema; semi-structured data carries its labels with it and may nest; unstructured data has no data model and needs extraction first.
• Quote CSV fields that contain commas, quotes or line breaks; declare separators, encodings and text columns when importing.
• JSON has four primitive types and two structured ones, must be UTF-8 between independent systems, and is only safe for integers up to 2 to the 53rd minus 1.
• pd.json_normalize flattens nested JSON; record_path produces one row per array element.

Sources

1. Public Health England. (2020, October 4; updated October 5). PHE statement on delayed reporting of COVID-19 cases. GOV.UK. gov.uk
2. Shafranovich, Y. (2005). Common format and MIME type for comma-separated values (CSV) files (RFC 4180). RFC Editor. rfc-editor.org
3. Bray, T. (Ed.). (2017). The JavaScript Object Notation (JSON) data interchange format (RFC 8259). RFC Editor. rfc-editor.org
4. Library of Congress. (n.d.). Sustainability of digital formats: CSV (RFC 4180); XLSX Transitional (Office Open XML); JSON; JFIF (JPEG). CSV; XLSX; JSON; JFIF
5. Microsoft. (n.d.). Excel specifications and limits. Microsoft Support. support.microsoft.com
6. World Wide Web Consortium. (2008). Extensible Markup Language (XML) 1.0 (5th ed.). W3C Recommendation. w3.org
7. U.S. Geological Survey. (n.d.). GeoJSON summary format, Earthquake Hazards Program real-time feeds. earthquake.usgs.gov
8. pandas development team. (2026). pandas.json_normalize; pandas.read_fwf. pandas 3.1.0 documentation. json_normalize; read_fwf
9. The Document Foundation. (n.d.). Text import. LibreOffice Help. help.libreoffice.org

A paper about not needing to know

In June 1970 the journal Communications of the ACM printed an eleven-page paper by Edgar F. Codd, a mathematician at IBM's research laboratory in San Jose, California. Its title was "A Relational Model of Data for Large Shared Data Banks", and its opening sentence set out a problem that every analyst still benefits from his solving: future users of large data banks, Codd wrote, must be protected from having to know how the data is organized inside the machine. In the systems of his day, a program that read data had to follow the physical paths by which records were stored, and when the storage changed, the program broke.

Codd's answer was to present all data as relations, which the rest of us call tables, and to let users ask for data by its values rather than by its location. That idea became the relational database, the system that holds most business data you will query in your career, and it is the first of the two database types objective 1.1 names. The second, non-relational databases, arrived when the web created problems that tables handled badly. This lesson follows the story from one to the other, picking up the vocabulary the exam expects along the way, and ends with the tools you use to open each kind.

Tables, keys and the schema

A relational database stores data in tables. Each row is one thing, a customer or an order, and each column is one attribute of it, with a declared type, which Lesson 2 covered. Two kinds of column hold the design together.

• A primary key is a column, or set of columns, whose value is unique and never null, so every row can be told apart. A customer_id of 1 identifies exactly one customer.
• A foreign key is a column whose values must match a primary key in another table. An order's customer_id points to the customer who placed it, so the order does not need to repeat her name and email.

The schema is the declared structure that the database enforces: its tables, their columns and types, their keys and their constraints. Here is a two-table schema in SQL:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  email       TEXT UNIQUE
);
CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  order_date  TEXT NOT NULL,
  total       NUMERIC NOT NULL
);

Splitting customers from orders is the heart of relational design, called normalization: each fact is stored once, in one place, and tables are linked by keys. When Ana Lima changes her email, one row changes, not every order she has placed. The cost is that answering "which customer placed order 5001?" means joining the tables back together, which is what SQL's JOIN does and what Lesson 9 practises. Databases & SQL (cs-db, CS 340) teaches normal forms in depth if you want them; for Data+, the idea of one fact in one place, linked by keys, is enough.

A schema is only useful if the database enforces it. Try inserting an order for customer 99, who does not exist, into the tables above. PostgreSQL refuses at once. SQLite, by default, accepts it: its documentation explains that foreign key enforcement is disabled by default for backward compatibility and must be switched on for each connection with PRAGMA foreign_keys = ON;, issued before any transaction begins. With it on, the same insert fails with "FOREIGN KEY constraint failed". The lesson generalizes: know what your database actually checks, because orphaned rows in a source system become missing values in your report.

Relational databases also process changes as transactions with four guarantees, abbreviated ACID. A transfer that debits one account and credits another happens entirely or not at all (atomicity); it never leaves the data breaking its rules (consistency); concurrent transactions do not see each other's half-finished work (isolation); and once committed it survives a crash (durability). SQLite's documentation states that all its changes and queries appear to be atomic, consistent, isolated and durable. The acronym RDBMS, relational database management system, on the exam's list, names the software that does all this: PostgreSQL, MySQL, SQL Server, Oracle and SQLite are all RDBMSs.

What matters here: a relational database stores each fact once in typed tables, links them with primary and foreign keys, enforces the declared schema, and wraps changes in ACID transactions.

2007: a shopping cart that must never refuse

By the 2000s the largest web companies were running into the limits of that design. In October 2007, nine Amazon engineers, Giuseppe DeCandia and colleagues including Werner Vogels, presented a paper at the ACM Symposium on Operating Systems Principles describing Dynamo, the storage system behind Amazon's shopping cart. Their requirement was blunt: customers should be able to view and add items to their carts even if disks are failing, network routes are flapping, or data centers are being destroyed by tornadoes.

The paper explains why a relational database was the wrong tool for that job. Many of Amazon's services, among them best-seller lists, shopping carts, customer preferences, session management, sales rank and the product catalog, only ever stored and fetched data by its primary key. They did not need joins or complex queries, and the relational machinery that provided them cost money and limited how far and how reliably the system could scale. Dynamo offered just two operations, get a value by its key and put a value under a key, and spread the data across many servers.

The authors were open about the price. To stay available for writes during failures, Dynamo "sacrifices consistency under certain failure scenarios": it is eventually consistent, meaning every update reaches every copy of the data eventually, but not necessarily at once. When copies of a cart disagreed, Amazon's application merged them, so an "add to cart" was never lost; the paper concedes that, as a result, deleted items can resurface. For a shopping cart that trade was acceptable. For a bank balance it would not be, which is why banks kept their relational databases. The paper reported that the cart service handled tens of millions of requests, resulting in well over three million checkouts, in a single day.

What matters here: non-relational databases trade some of the relational model's guarantees, often strict consistency, joins and a fixed schema, for scale, availability or flexibility that a particular workload needs.

Four families of non-relational database

"Non-relational", often called NoSQL, is a family of designs rather than one design. The exam expects you to recognize four.

Notice that each family answers a different question well and others poorly. A key-value store answers "what is stored under this key?" in a millisecond but cannot easily tell you how many carts contain a tent. A graph database finds the fraud ring that would take a relational database a chain of self-joins, but it is a clumsy place to sum a year of sales. That is why large organizations run several database types side by side, and why an analyst often pulls data from more than one of them into a single analysis.

Schema on write, schema on read

The deepest difference for an analyst is when the structure is enforced. A relational database applies schema on write: the table's columns and types are declared first, and a row that does not fit is rejected at the door. Most document stores apply schema on read: they accept documents of varying shape, and whoever reads the data must decide what structure to impose.

Schema on read moves work onto you. Suppose some product documents have a discount field and others do not, and some store price as a number while a few, written by an older application, store it as text. A query for average discount must decide whether a missing field means zero or unknown, and a price filter must cope with two types. Neither problem would exist in a relational table with a typed, nullable discount column; both are routine in a document collection. When you meet a document database on the exam or at work, expect to profile the fields before trusting any total, the exploration work Lesson 13 teaches.

The line between the two families is blurring. PostgreSQL stores JSON in its json and jsonb types and can index and query inside it; SQLite has built-in JSON functions, so SELECT json_extract(doc, '$.specs.weight_kg') FROM products; reaches into a JSON document stored in an ordinary text column. Many document databases, in turn, now offer optional schema validation. Learn the two models as ends of a spectrum rather than rival camps.

What matters here: with schema on write the database guarantees structure before data arrives; with schema on read, you impose structure when you query, and the checking becomes your job.

The tools that open them

Objective 1.4 lists six database management tools. The exam asks which tool fits which database, so learn them as pairs.

Two patterns help on the exam. A product-specific tool usually carries its database's name or vendor: SSMS for SQL Server, Workbench for MySQL, Compass for MongoDB. A general tool such as DBeaver connects to many databases through drivers. For this course's labs, DB Browser for SQLite plays the same role for SQLite, and DBeaver Community is a good free choice if you also install PostgreSQL.

Common misconceptions

• "NoSQL means no SQL and no structure." Many non-relational databases offer SQL-like query languages, and all of them have structure; the difference is whether a fixed schema is enforced when data is written.
• "Relational databases are legacy technology." Dynamo's own authors kept relational databases for work that needs joins and strict consistency. Most business reporting still runs on relational tables.
• "Declaring a foreign key guarantees referential integrity." Only if the database enforces it. SQLite leaves enforcement off until each connection turns it on.
• "Eventual consistency means the data is wrong." It means copies may briefly disagree after a write and then converge. Whether that is acceptable depends on the business question: fine for a shopping cart, not for a bank balance.
• "Azure Data Studio is the current SQL tool from Microsoft." It retired on 28 February 2026. The objectives still name it, so know it, and know that VS Code with the MSSQL extension replaced it.

The short version

• Relational databases (Codd, 1970) hold typed tables linked by primary and foreign keys, enforce a schema on write, and process changes as ACID transactions; RDBMS names the software.
• Non-relational databases trade some of those guarantees for scale, availability or flexibility. Amazon's Dynamo (2007) accepted eventual consistency so its shopping cart could always accept writes.
• Four families: key-value for lookups by key, document for flexible JSON-like records, wide-column for huge sparse write-heavy tables, graph for connections.
• Schema on read moves structure-checking to the person querying; profile fields before trusting totals.
• Tools: SSMS for SQL Server, MySQL Workbench for MySQL, Compass for MongoDB, DBeaver for many databases, Toad from Quest, and Azure Data Studio, retired in 2026 in favour of VS Code with the MSSQL extension.

Sources

1. Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. doi.org
2. DeCandia, G., Hastorun, D., Jampani, M., Kakulapati, G., Lakshman, A., Pilchin, A., Sivasubramanian, S., Vosshall, P., and Vogels, W. (2007). Dynamo: Amazon's highly available key-value store. In Proceedings of the 21st ACM Symposium on Operating Systems Principles (SOSP '07), 205-220. allthingsdistributed.com (PDF)
3. SQLite. (n.d.). SQLite foreign key support; SQLite is transactional; JSON functions and operators. foreign keys; transactions; JSON
4. PostgreSQL Global Development Group. (2026). JSON types, PostgreSQL 18 documentation, section 8.14. postgresql.org
5. MongoDB, Inc. (n.d.). Documents (MongoDB manual) and MongoDB Compass. documents; Compass
6. Neo4j, Inc. (n.d.). What is a graph database? Neo4j Getting Started guide. neo4j.com
7. Chang, F., Dean, J., Ghemawat, S., Hsieh, W. C., Wallach, D. A., Burrows, M., Chandra, T., Fikes, A., and Gruber, R. E. (2006). Bigtable: A distributed storage system for structured data. In Proceedings of the 7th USENIX Symposium on Operating Systems Design and Implementation (OSDI '06). research.google
8. Microsoft. (2026). SQL Server Management Studio (SSMS); What's happening with Azure Data Studio. Microsoft Learn. SSMS; Azure Data Studio
9. Oracle. (n.d.). MySQL Workbench manual. dev.mysql.com
10. DBeaver Corp. (n.d.). DBeaver Community; Quest Software. (n.d.). Toad. dbeaver.io; quest.com

A book from 1996 in a guide from 2024

Microsoft's guidance for building Power BI models, revised in December 2024, tells its readers to classify every table in a model as one of two kinds, a dimension or a fact. For the details, it sends them to a book first published in 1996: Ralph Kimball's The Data Warehouse Toolkit, whose third edition, written with Margy Ross, appeared in 2013. Three decades on, the tables behind most business dashboards are still built the way Kimball described, as a central table of measurements surrounded by tables of descriptions, a layout called a star schema.

Objective 1.1 names the parts: fact tables, dimensional tables, slowly changing dimensions, bridge tables and the schema that ties them together. The acronym list adds OLTP and OLAP, which explain why such tables exist at all. This lesson builds one small star schema step by step in SQLite, then changes a single input, a product moving to a new category, and watches a historical report change or stay put depending on one design choice.

OLTP and OLAP: two jobs, two shapes

The normalized design of Lesson 4, each fact stored once and linked by keys, is ideal for running a business. It is awkward for analyzing one. The two jobs have names.

Kimball distinguishes two ways to implement the analytical side: star schemas in a relational database, queried with SQL, and OLAP cubes, which he calls multidimensional databases, often built from a star schema and queried with more analytic languages. For Data+ you need the star schema in detail and the cube by name.

Step 1: declare the grain

Kimball's guidance calls declaring the grain "the pivotal step in a dimensional design": it says exactly what a single row of the fact table represents. Decide it before you name a single column, because everything else follows from it. For an outdoor retailer with a Portland store and a web shop, choose this grain:

One row per product, per store, per day, holding the quantity sold and the sales amount.

A finer grain, one row per order line, would let you analyze baskets; a coarser one, one row per product per month, would be smaller but could never answer a question about Saturdays. You cannot recover detail that the grain threw away, so when in doubt, analysts prefer the finest grain the source provides.

Step 2: choose the dimensions, Step 3: list the facts

Dimensions are the "by" in every business question: sales by date, by product, by store. Microsoft's guidance puts it in one line: dimension tables enable filtering and grouping, and fact tables enable summarization. Each dimension table holds one row per thing, with a key and descriptive attributes.

• dim_date: one row per calendar day, with year, month, quarter, weekday and holiday flags, so nobody computes "fiscal quarter" in a formula twice.
• dim_product: SKU, name, category, brand.
• dim_store: store name, channel (store or online), city, region.

Facts are the numbers measured at the declared grain, and Kimball sorts them by how they can be added up.

Microsoft's guidance makes the same point about unit price: it may be averaged or given a minimum or maximum, but it should never be summed.

Step 4: build and query the star

Here is the schema in SQLite. Notice that the fact table holds only keys and numbers, and that the product dimension has extra columns whose purpose will become clear in a moment.

CREATE TABLE dim_date    (date_key INTEGER PRIMARY KEY, full_date TEXT, year INTEGER, month INTEGER);
CREATE TABLE dim_store   (store_key INTEGER PRIMARY KEY, store_name TEXT, channel TEXT);
CREATE TABLE dim_product (product_key INTEGER PRIMARY KEY, sku TEXT, name TEXT, category TEXT,
                          valid_from TEXT, valid_to TEXT, is_current INTEGER);
CREATE TABLE fact_sales  (date_key INTEGER, product_key INTEGER, store_key INTEGER,
                          quantity INTEGER, sales_amount NUMERIC);

Load eight days of sales across 2025 and 2026 for a tent, a stove, a rain jacket and a rechargeable headlamp with SKU LAMP-3 (the activity gives every row), then ask the classic question, sales by category by year:

SELECT d.year, p.category, ROUND(SUM(f.sales_amount), 2) AS sales
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_date d    ON d.date_key    = f.date_key
GROUP BY d.year, p.category
ORDER BY d.year, p.category;

The query has the shape of every star-schema query: start at the fact table, join out to the dimensions you want to group or filter by, and aggregate the facts. Picture the fact table in the middle and the dimensions around it, and you have the star the schema is named after.

Remember: declare the grain first, put descriptive attributes in dimensions and measurements in the fact table, and sum only the facts that are additive across the dimension you are grouping by.

Star or snowflake

In a star schema each dimension is one wide, denormalized table: dim_product carries the category name on every product row. A snowflake schema normalizes the dimensions instead. Microsoft's example is Adventure Works, whose product dimension is stored as three related tables, product, subcategory and category, branching outward from the fact table like the arms of a snowflake.

Microsoft's guidance concludes that for Power BI models the benefits of a single denormalized dimension table generally outweigh those of several; snowflakes save a little storage at the cost of more joins and a more confusing field list.

Change one input: a product moves category

On 1 January 2026 the merchandising team moves the LAMP-3 headlamp from Camping to a new Electronics category. The headlamp sold 448.50 worth in 2025, all while it was a camping product. What should next year's report say about 2025?

That decision belongs to the dimension's design. A dimension whose attributes change occasionally is a slowly changing dimension, and Kimball catalogues several ways to handle the change. Two matter most.

• Type 1, overwrite. Update the category on the headlamp's single row. Kimball's description is blunt: the technique "destroys history". Every past sale now appears under Electronics.
• Type 2, add a new row. Close the old row (valid_to 2025-12-31, is_current 0) and insert a new row for the same SKU with a new surrogate key, category Electronics, valid_from 2026-01-01 and is_current 1. Sales before the change keep pointing at the old key; sales after it use the new one. Kimball's definition requires exactly these pieces: a surrogate key, because the same product now has several rows, plus an effective date, an expiration date and a current-row indicator.

Run the same query against both designs and the 2025 rows differ:

Neither answer is wrong in itself; they answer different questions. Type 2 reports 2025 as the business was organized in 2025, which is what an auditor or anyone comparing year on year needs. Type 1 reports all history as if today's categories had always applied, which suits attributes where the old value has no meaning, such as a corrected misspelling or a customer's new phone number, Microsoft's example. Type 2 still lets you see the product as a whole: grouping by the SKU instead of the surrogate key gives the headlamp's total across both versions, 867.10.

The danger is not choosing either type but not knowing which one a dimension uses. An analyst who compares a 2025 report saved in January with one rerun in March may find Camping has lost 448.50, with nobody having touched a sales record.

Remember: Type 1 overwrites and rewrites history; Type 2 adds a versioned row with a surrogate key, dates and a current flag, and keeps history as it was.

Bridge tables for many-to-many relationships

A star schema assumes each fact row points to one member of each dimension. Some relationships refuse to cooperate. Kimball's examples are a patient with several simultaneous diagnoses and a bank account held by several customers. The solution is a bridge table, with one row for each pairing, sitting between the fact and the dimension.

Suppose account A-100 holds 10,000 and is jointly owned by Ana and Ben, while A-200 holds 4,000 and belongs to Ana alone. The bridge has three rows: A-100 to Ana, A-100 to Ben, A-200 to Ana. Join balances through the bridge and sum by customer, and SQLite reports Ana 14,000 and Ben 10,000, which is right for each person. But add those up and you get 24,000, though the bank holds only 14,000. The joint account was counted once for each holder.

Two remedies are common. Report per customer but never total across customers through the bridge, or add an allocation column to the bridge, here 0.5 for each joint holder and 1.0 for a sole holder, and multiply by it: Ana 9,000 plus Ben 5,000 makes 14,000. Kimball and Ross describe such weighting factors in The Data Warehouse Toolkit. Microsoft's guidance uses a bridging table, which it calls a kind of factless fact table, for salespeople assigned to more than one sales region.

Common misconceptions

• "A data warehouse is just a copy of the operational database." It is reshaped for analysis: dimensional, denormalized and deliberately historical, where the OLTP source is normalized and current.
• "Every number in a fact table can be summed." Balances are semi-additive across time, and ratios and unit prices are not additive at all.
• "Snowflake schemas are always better because they are normalized." They save storage but add joins and complexity; Microsoft recommends single denormalized dimension tables for most Power BI models.
• "Overwriting a changed attribute is harmless because the sales did not change." Under Type 1, every historical report grouped by that attribute changes after the overwrite.
• "The SKU can be the primary key of a Type 2 product dimension." Each version of the product is a separate row, so the SKU repeats; a surrogate key identifies each version.

Putting it together

• OLTP systems record the business in normalized, current tables; OLAP systems analyze it in dimensional, historical ones, as star schemas or cubes.
• Design a star by declaring the grain, choosing dimensions for filtering and grouping, and listing facts as additive, semi-additive or non-additive.
• A snowflake normalizes dimensions into hierarchies of tables; a star keeps each dimension in one table.
• Slowly changing dimensions: Type 1 overwrites and destroys history; Type 2 adds a row with a surrogate key, effective and expiration dates and a current flag.
• Bridge tables resolve many-to-many relationships; totals through a bridge double count unless you allocate.

Sources

1. Kimball Group. (n.d.). Dimensional modeling techniques: Grain; Star schemas and OLAP cubes; Additive, semi-additive, non-additive facts; Type 1: Overwrite; Type 2: Add new row; Multivalued dimensions and bridge tables. index; grain; type 2; bridge tables
2. Microsoft. (2024). Understand star schema and the importance for Power BI. Microsoft Learn. learn.microsoft.com
3. Kimball, R., and Ross, M. (2013). The data warehouse toolkit: The definitive guide to dimensional modeling (3rd ed.). Wiley.
4. Kimball Group. (n.d.). Additive, semi-additive, non-additive facts. kimballgroup.com

A question at five o'clock

At about 16:00 Central European Time on Monday 2 November 2026, the European Central Bank published that day's euro reference rates for about thirty currencies. One euro bought 1.1216 US dollars. An hour later, picture an analyst at an American outdoor retailer that buys its tents from a German maker, facing three questions from her manager. What will this month's euro invoice cost in dollars? How much did Saturday's two-hour website outage cost in lost orders? And are our tent prices still in line with our two biggest competitors?

No single system holds the answers. She will need the company's sales database, an exchange-rate feed, the supplier's price file, the web servers' logs and, possibly, the competitors' public web pages. Those are five of the data sources objective 1.2 lists, databases, APIs, files, logs and web scraping, and the sixth, data repositories, is Lesson 7's subject. Work through her day and each source shows its character: how you reach it, how fresh it is, how much it can be trusted, and what can go wrong.

Source 1: the operational database

Orders live in the company's transactional database, the OLTP system of Lesson 5. Querying it is the most direct route to "how many orders did we take on Saturday?", and in Python it takes two lines: open a connection, then pd.read_sql_query(sql, con), which runs a SQL query and returns the result as a DataFrame.

Direct does not mean free. The same database is taking customers' orders while she queries it, and a heavy analytical query that scans months of history can slow the checkout. Organizations protect production systems in three common ways: analysts get read-only accounts, heavy queries run against a replica (a copy kept in step with production) or against the data warehouse, and large extracts run outside peak hours. A database source is usually the most authoritative and the most current, but it is the one where a careless query has customers as its victims.

Source 2: an API

An application programming interface, or API, is a defined way for one program to request data or services from another. A web API answers HTTP requests sent to a URL, usually with JSON, sometimes with CSV or XML. The ECB's Data Portal offers one that needs no account or key; this request asks for the three most recent daily dollar rates as CSV:

import pandas as pd
url = "https://data-api.ecb.europa.eu/service/data/EXR/D.USD.EUR.SP00.A?lastNObservations=3&format=csvdata"
rates = pd.read_csv(url)
rates[["TIME_PERIOD", "OBS_VALUE"]]

On 2 November 2026 it returned 1.1130 for 29 October, 1.1172 for 30 October and 1.1216 for 2 November. Notice what the response did not contain: Saturday and Sunday, because reference rates are published on business days only. A careful analyst decides in advance which rate applies to a weekend invoice. The ECB also states that its reference rates are published for information purposes only and that using them for transactions is strongly discouraged, which matters if the invoice is ever settled at a bank's rate instead.

Most commercial APIs are less open than the ECB's, and they share a vocabulary the exam expects you to know.

HTTP status codes tell you how a request went. HTTP's core specification, RFC 9110, defines the common ones, and RFC 6585 added 429 for rate limiting.

In short: an API gives you structured, current data on the provider's terms: learn its endpoints, guard its keys, respect its rate limits and collect every page.

Source 3: files

The supplier sends its price list as a spreadsheet attached to an email every Monday. Files are the oldest and still the most common data source: exports from systems that offer no API, spreadsheets maintained by hand, CSVs on a shared drive. The ECB publishes its daily rates as a small XML file too, a file served over the web rather than an API you query.

A file is a snapshot. It was correct at the moment it was exported and begins to age at once, and nothing inside it says so unless someone wrote the date in. Files also multiply: price_list.xlsx, price_list_final.xlsx and price_list_final_v2.xlsx can all sit in the same folder. Good habits are simple and save hours: record where each file came from and when you received it, keep the original untouched in a raw folder, and work on a copy. Lesson 27 turns those habits into data lineage.

Source 4: logs

Logs are records that systems write about their own activity: one line per event, appended as it happens, each with a timestamp. Web servers, applications, databases, firewalls and sensors all produce them, in volumes that dwarf the business tables. For the outage question, the web server's access log is the best evidence there is, because it records every request, including the ones that failed.

The Apache HTTP Server documentation defines the widely used Common Log Format, seven fields in a fixed order, and the Combined Log Format, which appends two more. Here is one line, field by field:

203.0.113.7 - - [02/Nov/2026:14:03:22 +0000] "GET /products/TENT-2P HTTP/1.1" 200 5120 "-" "Mozilla/5.0"

Count the 5xx status codes minute by minute and the outage's start and end appear to the second. Count successful checkout requests in the same two hours on the previous four Saturdays and you have a baseline for the orders that never happened. Logs are semi-structured: every line has the same pattern, but the pattern must be parsed, a job for the regular expressions of Lesson 14. They also hold personal data such as IP addresses, which is why their retention is a governance question in Lesson 28.

Source 5: web scraping

The competitors publish their prices on their websites and offer no API. Web scraping is extracting data from web pages by downloading their HTML, the markup language of the web, and pulling out the parts you need. pandas can read the tables on an HTML page with pd.read_html, which needs the lxml package installed; dedicated scraping libraries handle everything else.

Scraping is the source with the most strings attached, and the exam treats it that way.

• Permission comes first. A site's terms of use may forbid automated collection. Its robots.txt file, defined in RFC 9309 (September 2022), tells crawlers which paths the owner does not want visited. The RFC itself warns that these rules "are not a form of access authorization": robots.txt is not a lock, and its silence is not permission. Respect it, read the terms, and ask the owner if in doubt.
• Personal data stays personal. Scraping names, emails or profiles brings data protection law, such as the GDPR of Lesson 28, into play, whatever the page's visibility.
• Be a polite client. Pause between requests, collect only what you need, and identify your program honestly in its user agent.
• Expect breakage. A scraper depends on the page's layout. When the site is redesigned, the scraper fails or, worse, silently extracts the wrong column.

When an organization offers an API or a downloadable data set, use it instead. Scraping is a last resort for public, non-personal data, collected within the owner's rules.

Comparing the five sources

Common misconceptions

• "An API call returns all the data." Many APIs paginate. A month of 4,000 orders that arrives as exactly 100 records is a first page, not a quiet month.
• "If robots.txt does not forbid it, scraping is allowed." RFC 9309 says its rules are not access authorization in either direction; terms of use and privacy law still apply.
• "A quick query on the production database cannot hurt." A full scan competes with live transactions. Use a replica or the warehouse.
• "Logs are only for IT." Access logs are often the only record of what customers tried to do and could not, which makes them prime evidence for analysts.
• "The file we were sent is the source of truth." It is a snapshot of a source. Record when it was taken, and go back to the system it came from when it matters.

Where this leaves us

• Objective 1.2's sources: databases (live and authoritative, but protect production), APIs (structured and current, on the provider's terms), files (snapshots that age), logs (timestamped, semi-structured event records) and web scraping (a last resort with legal and ethical limits).
• For APIs know endpoints, parameters, keys, rate limits (429), pagination and status codes: 200, 401, 403, 404, 429, 500.
• The Common Log Format records host, identity, user, time, request, status and size; the Combined format adds referer and user agent.
• robots.txt states the owner's wishes but is not access authorization; prefer an API or an official download whenever one exists.

Sources

1. European Central Bank. (n.d.). Euro foreign exchange reference rates and ECB Data Portal API. Retrieved 2 November 2026. reference rates; API overview
2. Fielding, R., Nottingham, M., and Reschke, J. (Eds.). (2022). HTTP semantics (RFC 9110). RFC Editor. rfc-editor.org
3. Nottingham, M., and Fielding, R. (2012). Additional HTTP status codes (RFC 6585), section 4: 429 Too Many Requests. RFC Editor. rfc-editor.org
4. Koster, M., Illyes, G., Zeller, H., and Sassman, L. (2022). Robots exclusion protocol (RFC 9309). RFC Editor. rfc-editor.org
5. Apache Software Foundation. (n.d.). Log files, Apache HTTP Server version 2.4 documentation. httpd.apache.org
6. pandas development team. (2026). pandas.read_sql_query; pandas.read_html. pandas 3.1.0 documentation. read_sql_query; read_html

The warehouse that was supposed to wither

In January 2021, at the Conference on Innovative Data Systems Research, four authors from Databricks, the University of California at Berkeley and Stanford presented a paper. Michael Armbrust, Ali Ghodsi, Reynold Xin and Matei Zaharia opened it with a prediction: the data warehouse architecture as we know it "will wither in the coming years". In its place they proposed the lakehouse, built on open file formats such as Apache Parquet. It would offer first-class support for machine learning and data science, and performance to match a warehouse.

Read that claim as an argument, not a verdict: Databricks sells a lakehouse platform. Five years on, Microsoft's Azure Architecture Center, revised in 2026, describes data lakes coexisting with warehouses in a lakehouse approach rather than replacing them. Most organizations now run several kinds of repository at once, on infrastructure that may sit in their own building, in a public cloud or in both. Objective 1.2 asks you to identify the repositories, data lakes, lakehouses, marts and silos, and objective 1.3 the infrastructure underneath: cloud providers, deployment models, storage types and containers. This lesson lays them side by side.

Four repositories and one problem

Lesson 5's data warehouse is the reference point: integrated, cleaned, structured data from across the organization, modelled for analysis, with the schema enforced as data is written. The others are defined against it.

Read the table by its schema column and the logic appears. A warehouse and its marts do the hard work of cleaning and modelling before anyone queries, so queries are fast and trustworthy but new kinds of data wait for a project. A lake accepts anything at once and defers the work to query time. Microsoft's definition says a data lake holds large volumes of data "in its native, raw format", including structured tables, semi-structured JSON and logs, and unstructured images and audio, and applies transformations only when data is needed. Microsoft also names the lake's typical failure: without cataloguing, a lake can "devolve into data swamps", where valuable data is present but nobody can find or understand it.

The lakehouse tries to keep the lake's cheap, open storage while restoring the warehouse's reliability. Data stays in files such as Apache Parquet, an open, column-oriented format. A table layer records which files make up each version of each table, so updates are transactional and analysts can query the files with SQL as if they were warehouse tables. Microsoft's guidance describes the common layout in three layers, which it calls the medallion architecture: raw (bronze) data as ingested, cleansed (silver) data, and curated (gold) data ready for reports.

A data silo is different in kind: not an architecture anyone chooses but a condition that grows when each department keeps its own data. Its symptom is familiar. Sales reports one revenue figure, finance another, and both are "right" from their own systems, because the two silos define customers, dates and refunds differently. Repositories that integrate data, and the master data management of Lesson 27, are the cure.

The upshot: warehouses and marts give clean, modelled data for known questions, and lakes keep everything raw for questions not yet asked. Lakehouses aim for both on one copy. Silos are what happens when nobody integrates.

Clouds and deployment models

Repositories run on infrastructure, and the first decision is whose. The objectives name the three largest public cloud providers: Amazon Web Services, Microsoft Azure and Google Cloud. The vocabulary for how cloud is deployed comes from NIST Special Publication 800-145, written by Peter Mell and Timothy Grance in September 2011. It defines cloud computing by five essential characteristics: on-demand self-service, broad network access, resource pooling, rapid elasticity and measured service.

NIST also defines a community cloud, shared by organizations with common concerns, though the Data+ objectives do not list it. For an analyst, the deployment model shows up as practical questions: where the data may legally be stored (Lesson 28's jurisdictional requirements), how fast it can be moved between systems, and who pays for every query.

Storage types: object, file, block, local and shared

Objective 1.3 lists five storage types, which describe how data is organized and reached rather than where it sits.

AWS's own comparison sums up the first three. Object storage keeps data as objects with metadata and suits large volumes of unstructured data. Block storage allows direct access to individual blocks and suits low-latency workloads such as databases. File storage provides shared access through a hierarchy of directories. The last two rows add the acronyms on the exam's list. Network-attached storage, NAS, is a file server on the network: users see shared folders. A storage area network, SAN, is a dedicated network that presents block storage to servers, which treat it as their own disks. When a scenario says many analysts need the same folder of CSVs, think file storage on a NAS. When it says a database server needs fast disks, think block storage, perhaps from a SAN.

Containers: the same environment everywhere

A notebook that runs on your laptop and fails on a colleague's is usually not a mystery: a different version of Python, pandas or some library underneath. Containerization solves that by packaging an application together with everything it needs, its libraries, settings and runtime, into an image that runs the same way on any machine with a container engine. Docker is the best-known tool, and Kubernetes the common system for running many containers across a cluster.

NIST's Application Container Security Guide, Special Publication 800-190 (2017), draws the same contrast and adds a caution worth carrying into governance. Because containers share the host's kernel, isolation between them is weaker than between virtual machines, and the images themselves must be kept patched and scanned. For Data+, remember the benefit in one word, reproducibility: an analysis packaged in a container gives the same result next year on someone else's machine.

The upshot: match storage to access, objects for lakes, files for shared folders, blocks for databases, and package environments in containers so your work runs the same everywhere.

Common misconceptions

• "A data lake is a bigger data warehouse." A lake stores raw data with schema on read; a warehouse stores modelled data with schema on write. They answer different needs, and many organizations run both.
• "Buying a lake ends our data silos." A silo is an integration and ownership problem. Copying silos into one lake without shared definitions produces a swamp, not a single truth.
• "Object storage is a network drive." Object storage is reached through an API by key, with no real folder hierarchy; a network drive is file storage.
• "Containers are small virtual machines with their own operating system." Containers share the host's kernel; that is why they are light and why their isolation is weaker.
• "Hybrid cloud just means using the cloud a little." NIST defines hybrid as distinct infrastructures, such as a private and a public cloud, kept separate but bound together so data and applications can move between them.

Pulling it together

• Warehouse: integrated, modelled, schema on write. Mart: one subject or department's slice. Lake: raw data of every structure, schema on read, at risk of becoming a swamp. Lakehouse: lake files in open formats plus a transactional table layer. Silo: isolated, unintegrated data.
• Cloud providers on the exam: AWS, Azure, Google. Deployment: on-premises, private, public and hybrid clouds, defined in NIST SP 800-145.
• Storage: object (S3, Blob Storage, Cloud Storage) for lakes; file (NAS) for shared folders; block (SAN, EBS) for databases; local disks; shared storage reached over a network.
• Containers package an environment with its dependencies and share the host kernel; they make analysis reproducible.

Sources

1. Armbrust, M., Ghodsi, A., Xin, R., and Zaharia, M. (2021). Lakehouse: A new generation of open platforms that unify data warehousing and advanced analytics. Conference on Innovative Data Systems Research (CIDR 2021). cidrdb.org (PDF)
2. Microsoft. (2026). What is a data lake? Azure Architecture Center. learn.microsoft.com
3. Amazon Web Services. (n.d.). What is a data mart? and What's the difference between block, object, and file storage? data mart; storage types
4. Google Cloud. (n.d.). Compare AWS and Azure services to Google Cloud. cloud.google.com
5. Mell, P., and Grance, T. (2011). The NIST definition of cloud computing (NIST Special Publication 800-145). National Institute of Standards and Technology. csrc.nist.gov
6. Souppaya, M., Morello, J., and Scarfone, K. (2017). Application container security guide (NIST Special Publication 800-190). National Institute of Standards and Technology. csrc.nist.gov
7. Apache Software Foundation. (n.d.). Parquet overview. parquet.apache.org
8. Docker, Inc. (n.d.). What is a container? Docker documentation. docs.docker.com

Forty percent faster, forty percent right

In July 2023 the journal Science published an experiment by Shakked Noy and Whitney Zhang. They gave 453 college-educated professionals writing tasks from their own occupations and let half of them use ChatGPT. Among those with access, the average time taken fell by 40 percent and the quality of the output, as graded by other professionals, rose by 18 percent.

That same year, a team led by Jinyang Li published a benchmark of a task closer to an analyst's desk. It turns questions in plain English into SQL against 95 real databases totalling 33.4 GB. The most effective model they tested, ChatGPT, produced queries that returned the correct result 40.08 percent of the time. Human experts managed 92.96 percent.

Both findings are credible, and both describe the tools now built into spreadsheets, BI products and coding environments. Objective 1.5 asks you to identify the AI concepts behind them: generative AI and large language models, foundational models, deep learning, natural language processing, and robotic process automation with its automated reporting. This lesson defines them first, then takes the dispute seriously: when should an analyst trust a model's output?

The vocabulary, from the bottom up

The terms nest inside one another, and the exam rewards knowing which is the wider idea.

Two relationships are worth fixing in memory. An LLM is a kind of foundation model, and most foundation models are built with deep learning. So the chain runs machine learning, deep learning, foundation model, LLM, from wider to narrower. And RPA sits beside that chain rather than inside it: a classic RPA bot follows rules and contains no learned model at all. Microsoft's Power Automate describes its desktop flows as robotic process automation that can drive both modern and legacy applications through their on-screen elements. Vendors increasingly pair RPA with AI, for example a bot that uses a language model to read an invoice before typing its fields into a finance system. Even then, the automation and the intelligence are separate parts.

Worth holding on to: deep learning is a kind of machine learning, and foundation models are large, adaptable deep learning models. LLMs are text-trained foundation models that generate. NLP is the field of language tasks, and RPA automates clicks and keystrokes by rule.

The case for: measured gains

The evidence for productivity gains is not anecdote. Besides Noy and Zhang's experiment, Erik Brynjolfsson, Danielle Li and Lindsey Raymond studied 5,179 customer-support agents at a company that introduced a generative AI assistant gradually. The gradual rollout let them compare agents with and without it. Their study, published in the Quarterly Journal of Economics in 2025, found that access raised issues resolved per hour by 14 percent on average. The gain was 34 percent for novice and low-skilled workers, with minimal impact on the most experienced and skilled.

Two patterns in that evidence matter for analysts. The gains were largest for people learning a job, which is a reason for a new analyst to use these tools to see how an unfamiliar task is done. And the tasks were ones where a person could judge the output: a support agent sees whether the customer's problem was solved, a professional writer can read the draft. Analysts can put AI to work on similar tasks. It can draft a query or a script that they then test, explain a colleague's unfamiliar code, summarize open-text comments they can sample and check, or produce realistic fake data for a test.

The case against: confident errors

NIST's Generative AI Profile, NIST AI 600-1 (July 2024), lists the risks that generative AI introduces or makes worse. Its second is confabulation, which it defines as "the production of confidently stated but erroneous or false content (known colloquially as 'hallucinations' or 'fabrications') by which users may be misled or deceived." A language model produces text that is statistically likely, not text that has been checked. It can cite a regulation section that does not exist with the same fluency as one that does.

For analysts, the dangerous output is not the query that fails but the query that runs. Take the star schema from Lesson 5 and suppose an assistant, asked for 2025 sales by category, writes this:

SELECT p.category, SUM(f.sales_amount) AS sales
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_date d    ON d.date_key    = f.date_key
WHERE d.year = 2025 AND p.is_current = 1
GROUP BY p.category;

It is tidy, it runs, and it returns Camping 516.00 and Clothing 267.00. The true 2025 Camping figure is 964.50. The innocent-looking filter p.is_current = 1 silently dropped every 2025 sale of the headlamp, whose 2025 version is no longer current. Nothing in the result looks wrong unless you know what it should say, which is exactly the situation in which the benchmark's 40 percent and the experiment's 40 percent stop being contradictory. The tools are fast at producing plausible work and unreliable at guaranteeing it is right.

Two further risks are specific to working with organizational data. Pasting customer records, salaries or health information into a public chatbot sends that data to a third party, which may breach your organization's policies or data protection law. Lessons 28 and 29 cover the rules. And the same prompt can produce different outputs on different days, which makes an analysis harder to reproduce unless the generated code is saved and tested like any other code.

What would settle it for your work

The two positions disagree less than they seem. The productivity studies measured help on tasks where people could judge the output; the error studies measured output nobody checked. What settles the question for a given task is evidence from that task, gathered the way NIST's AI Risk Management Framework suggests organizations govern, map, measure and manage AI risk.

• Measure on known answers. Before trusting an assistant with a kind of query, give it ten questions whose answers you already know and count how many it gets right.
• Verify every result that matters. Reconcile totals with a trusted report, check row counts before and after each join, and test generated code on a small data set you can check by hand.
• Keep humans on the decision. Use generated text and code as drafts; the person who signs the report owns every number in it.
• Protect the data. Use only tools your organization has approved for the data's classification, and prefer synthetic or anonymized samples when asking for help.
• Keep a record. Save prompts, outputs and the version of the code you actually ran, so the analysis can be reproduced and audited.

Worth holding on to: generative AI speeds up work you can check and confidently produces errors you might not catch; the test is your own measured accuracy on your own task, plus verification of every result that matters.

Common misconceptions

• "AI and generative AI mean the same thing." Generative AI is one family; models that classify, predict or detect without generating content are AI too.
• "An LLM looks facts up in a database." It generates likely text from patterns learned in training. Unless a system feeds it verified sources, nothing guarantees its statements are true, which is why NIST names confabulation as a risk.
• "RPA is a kind of artificial intelligence." Classic RPA replays rule-based steps in user interfaces. It is often combined with AI, but the bot itself learns nothing.
• "If generated SQL runs without an error, it is right." The example above runs cleanly and omits 448.50 of sales. Only a check against known answers reveals it.
• "Deep learning is something other than machine learning." It is a subset: machine learning with many-layered neural networks.

Summing up

• Machine learning contains deep learning, which underlies foundation models, of which LLMs are the text-trained, generative kind. Generative AI creates synthetic content; NLP is the field of language tasks; RPA automates user-interface steps by rule, including automated reporting.
• Evidence for: 40 percent less time and 18 percent higher quality on professional writing (Noy and Zhang, 2023); 14 percent more support issues resolved per hour, 34 percent for novices (Brynjolfsson, Li and Raymond, 2025).
• Evidence against: 40.08 percent execution accuracy for the best text-to-SQL model tested against 92.96 percent for humans (Li and colleagues, 2023); confabulation as a named risk in NIST AI 600-1.
• Use AI on tasks you can verify, measure its accuracy on your own work, keep sensitive data in approved tools, and own every number you publish.

Sources

1. Noy, S., and Zhang, W. (2023). Experimental evidence on the productivity effects of generative artificial intelligence. Science, 381(6654), 187-192. doi.org
2. Brynjolfsson, E., Li, D., and Raymond, L. R. (2025). Generative AI at work. The Quarterly Journal of Economics, 140(2), 889-942. Working paper version: NBER Working Paper 31161
3. Li, J., Hui, B., Qu, G., and others. (2023). Can LLM already serve as a database interface? A big bench for large-scale database grounded text-to-SQLs. Advances in Neural Information Processing Systems 36 (NeurIPS 2023). arXiv:2305.03111
4. National Institute of Standards and Technology. (2024). Artificial intelligence risk management framework: Generative artificial intelligence profile (NIST AI 600-1). nvlpubs.nist.gov (PDF)
5. National Institute of Standards and Technology. (2023). AI risk management framework (AI RMF 1.0, NIST AI 100-1). nist.gov
6. Bommasani, R., and others. (2021). On the opportunities and risks of foundation models. Stanford Center for Research on Foundation Models. arXiv:2108.07258
7. LeCun, Y., Bengio, Y., and Hinton, G. (2015). Deep learning. Nature, 521(7553), 436-444. doi.org
8. Vaswani, A., Shazeer, N., Parmar, N., Uszkoreit, J., Jones, L., Gomez, A. N., Kaiser, L., and Polosukhin, I. (2017). Attention is all you need. Advances in Neural Information Processing Systems 30. arXiv:1706.03762
9. Jurafsky, D., and Martin, J. H. (n.d.). Speech and language processing (3rd ed., draft). Stanford University. web.stanford.edu
10. Microsoft. (2026). Introduction to desktop flows (Power Automate). Microsoft Learn. learn.microsoft.com

Order 5007 and the customer who does not exist

Order 5007 was placed in the Portland store on 3 March 2026 by customer 99, and the customers table has no customer 99. Whether that order appears in a report depends on one word in a SQL query, and an analyst who does not know which word will report a total that is quietly wrong in one direction or the other.

Objective 2.1 asks you to use data acquisition methods in a scenario, and querying is the first of them: filter, join, union and concatenate in this lesson; grouping, aggregates and nested queries in Lesson 10. The procedure here runs one realistic request end to end against a small practice database, then changes one input at a time and shows how each change alters the rows you get. If SQL is new to you, Databases & SQL (cs-db, CS 340) teaches it from the beginning; this lesson concentrates on the decisions the exam tests.

Create the practice database now. In DB Browser for SQLite, make a new database called shop.db and run this script on the Execute SQL tab; or run it in Python with sqlite3.connect("shop.db").executescript(script). Lessons 10 and 11 use the same database.

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, signup_date TEXT);
CREATE TABLE products  (sku TEXT PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, list_price NUMERIC);
CREATE TABLE orders    (order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, channel TEXT);
CREATE TABLE order_items (order_id INTEGER, sku TEXT, qty INTEGER, unit_price NUMERIC);
CREATE TABLE newsletter_store (email TEXT);
CREATE TABLE newsletter_web   (email TEXT);
INSERT INTO customers VALUES
 (1, 'Ana Lima', 'Portland', '2024-03-02'), (2, 'Ben Osei', 'Seattle', '2024-07-19'),
 (3, 'Chloe Wu', 'Portland', '2025-01-08'), (4, 'Dev Patel', NULL, '2025-05-30'),
 (5, 'Eva Rossi', 'Boise', '2025-09-14'),   (6, 'Femi Ade', 'Seattle', '2026-02-01');
INSERT INTO products VALUES
 ('TENT-2P', 'Two-person tent', 'Camping', 189.00), ('STOVE-1', 'Camp stove', 'Camping', 34.50),
 ('LAMP-3', 'Rechargeable headlamp', 'Camping', 29.90), ('JACKET-R', 'Rain jacket', 'Clothing', 89.00),
 ('SOCK-W', 'Wool socks', 'Clothing', 14.00);
INSERT INTO orders VALUES
 (5001, 1, '2025-10-30', 'web'), (5002, 2, '2025-11-02', 'store'), (5003, 1, '2025-11-15', 'store'),
 (5004, 3, '2025-12-01', 'web'), (5005, 5, '2026-01-12', 'web'),   (5006, 2, '2026-02-20', 'web'),
 (5007, 99, '2026-03-03', 'store');
INSERT INTO order_items VALUES
 (5001, 'TENT-2P', 1, 189.00), (5001, 'STOVE-1', 2, 34.50), (5002, 'JACKET-R', 1, 89.00),
 (5003, 'LAMP-3', 3, 29.90), (5003, 'STOVE-1', 1, 34.50), (5004, 'TENT-2P', 1, 179.00),
 (5005, 'JACKET-R', 2, 89.00), (5005, 'LAMP-3', 1, 29.90), (5006, 'STOVE-1', 4, 34.50),
 (5007, 'TENT-2P', 1, 189.00);
INSERT INTO newsletter_store VALUES ('ana@example.com'), ('ben@example.com'), ('dev@example.com');
INSERT INTO newsletter_web VALUES ('ana@example.com'), ('chloe@example.com'), ('eva@example.com'), ('ben@example.com');

Every result quoted in this lesson was produced by running these queries against this data in SQLite.

Filter: WHERE, and the NULL trap

A filter keeps the rows that satisfy a condition. The WHERE clause accepts a small set of operators that cover nearly every request.

The date range works on this text column only because the dates are ISO 8601, which sorts in time order, as Lesson 2 showed. Running SELECT order_id, customer_id, order_date FROM orders WHERE channel = 'web' AND order_date BETWEEN '2025-01-01' AND '2025-12-31'; returns orders 5001 and 5004.

Now the trap. A manager asks how many customers live outside Portland. Six customers, two in Portland, so the answer should be four. SELECT COUNT(*) FROM customers WHERE city <> 'Portland'; returns 3. Dev Patel's city is NULL, and comparing NULL with anything gives unknown, never true, so his row is filtered out. Adding OR city IS NULL returns 4. Whether Dev belongs in the answer is a business question, but the query must make that choice deliberately rather than by accident.

So what?: a filter on a column that contains nulls silently drops those rows unless you name them with IS NULL; decide where they belong, every time.

Join: four ways to match rows

A join combines columns from two tables by matching rows on a condition, usually a key. The join types differ only in what they do with rows that find no match, and that is exactly what exam scenarios ask about.

Two patterns built on the LEFT JOIN answer questions every analyst is asked. To find customers who have never ordered, keep the unmatched rows: SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL; returns Dev Patel and Femi Ade. To find orphaned orders, turn it round: orders LEFT JOIN customers, keeping rows where the customer is NULL, returns order 5007. That second query is a data quality check you should run whenever you inherit a database whose foreign keys are not enforced, as SQLite's are not by default.

The second hazard is fan-out. A join returns one row for every matching pair, so joining the seven orders to their order lines returns ten rows, because three orders have two lines each. Any order-level number carried into that result, a shipping fee or an order count, now appears once per line, and summing it double counts. Count rows before and after every join: if the count rises when you expected it to stay the same, find out why before you aggregate.

So what?: pick the join by the unmatched rows you need to keep, check for orphans in both directions, and count rows before and after a join to catch fan-out.

Union: stacking rows

A join adds columns; a union adds rows. UNION stacks the results of two queries with the same number of columns in compatible types, and the column names come from the first query. Its two forms differ in one way.

• SELECT email FROM newsletter_store UNION SELECT email FROM newsletter_web; returns 5 rows: UNION removes duplicate rows, so Ana and Ben, who signed up in both places, appear once.
• The same query with UNION ALL returns all 7 rows, duplicates included.

PostgreSQL's documentation states the rule plainly: UNION eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used. Choose by meaning. Stacking January's transactions on February's needs UNION ALL, because two identical-looking transactions are still two sales; a duplicate-free mailing list needs UNION. Removing duplicates also costs work, so UNION ALL is faster when duplicates are impossible or wanted.

Concatenate: two meanings

"Concatenate" means joining end to end, and analysts use it for strings and for data sets.

Strings. SQLite and PostgreSQL join text with the || operator; SQL Server and MySQL use a CONCAT function, which PostgreSQL and recent SQLite also offer. The two behave differently with NULL:

Data sets. In pandas, pd.concat([jan, feb]) stacks DataFrames row on row, like UNION ALL: duplicates survive unless you remove them with drop_duplicates(). Exam scenarios that say "concatenate two monthly extracts into one data set" mean this kind.

The full request, end to end

Here is the kind of request that arrives on a Monday: every web order, with the customer's name, the product's name and the line total. It combines three joins, a filter and a calculated column:

SELECT o.order_id, c.name AS customer, p.name AS product,
       i.qty, i.unit_price, i.qty * i.unit_price AS line_total
FROM order_items i
JOIN orders o    ON o.order_id = i.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p  ON p.sku = i.sku
WHERE o.channel = 'web'
ORDER BY o.order_id;

Change one input and the answer changes. Ask for store orders instead and order 5007 vanishes, because the inner join to customers finds no customer 99; switch that join to a LEFT JOIN and it returns with a NULL customer name. Neither is wrong in itself. The analyst's job is to know which one the manager asked for and to say, in the report, what was excluded. In pandas, the same query can be sent unchanged with pd.read_sql_query(sql, con), or rebuilt from DataFrames with merge, whose how argument takes "inner", "left", "right" or "outer".

Common misconceptions

• "WHERE city <> 'Portland' returns everyone not in Portland." It also drops every row whose city is NULL.
• "An INNER JOIN keeps all my orders." It keeps only orders that match a customer. Orphans disappear silently.
• "UNION and UNION ALL give the same rows." UNION removes duplicate rows; UNION ALL keeps them. Two identical transactions are still two sales.
• "Joining never changes the number of rows I sum." One-to-many joins fan out; order-level values repeat once per matching line.
• "Concatenating with NULL just skips the NULL." With || the whole result becomes NULL; only functions such as concat, or COALESCE, avoid it.

Looking back

• Filter with WHERE using comparisons, BETWEEN, IN, LIKE and IS NULL; a comparison with NULL is never true.
• INNER keeps matches only; LEFT keeps all left rows; RIGHT mirrors it; FULL OUTER keeps everything. Use LEFT JOIN with IS NULL to find non-matches and orphans.
• Count rows before and after each join to catch fan-out.
• UNION removes duplicate rows; UNION ALL keeps them; both need matching columns.
• || returns NULL if any part is NULL; concat skips NULLs; pd.concat stacks DataFrames like UNION ALL.

Sources

1. SQLite. (n.d.). SELECT and Built-in scalar SQL functions (concat, coalesce). SELECT; core functions
2. PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation: 7.2 Table expressions (joined tables); 7.4 Combining queries (UNION, INTERSECT, EXCEPT); 9.4 String functions and operators. joins; union; strings
3. pandas development team. (2026). Merge, join, concatenate and compare. pandas 3.1.0 user guide. pandas.pydata.org
4. Microsoft. (n.d.). CONCAT (Transact-SQL). Microsoft Learn. learn.microsoft.com

Forty-four percent and thirty percent

In autumn 1973 the six largest graduate departments at the University of California, Berkeley, received 4,526 applications. They admitted 44.5 percent of the men who applied and 30.4 percent of the women. Two years later the statistician Peter Bickel and two colleagues, Eugene Hammel and J. William O'Connell, published an analysis in Science asking whether the gap showed bias against women. The data, which R distributes as its UCBAdmissions data set, are small enough to type into SQLite in five minutes, and they make the case for this lesson better than any invented example: the same twelve rows answer the question in opposite ways depending on how you group them.

Objective 2.1 lists grouping, aggregates and nested queries among the querying skills you must apply. Grouping and aggregating turn rows into summaries; nested queries let one query use the result of another. Both are mechanical to write and easy to get subtly wrong, so this lesson works the Berkeley problem first and then the questions a manager would ask of the shop database from Lesson 9.

Grouping the problem two ways

Load the data as one row per department and gender:

CREATE TABLE admissions (dept TEXT, gender TEXT, admitted INTEGER, rejected INTEGER);
INSERT INTO admissions VALUES
 ('A','Male',512,313), ('A','Female',89,19),  ('B','Male',353,207), ('B','Female',17,8),
 ('C','Male',120,205), ('C','Female',202,391), ('D','Male',138,279), ('D','Female',131,244),
 ('E','Male',53,138),  ('E','Female',94,299),  ('F','Male',22,351),  ('F','Female',24,317);

Group by gender alone and the gap appears:

SELECT gender, SUM(admitted) AS admitted, SUM(admitted + rejected) AS applied,
       ROUND(100.0 * SUM(admitted) / SUM(admitted + rejected), 1) AS pct
FROM admissions
GROUP BY gender;

Women: 557 admitted of 1,835, 30.4 percent. Men: 1,198 of 2,691, 44.5 percent. Now group by department as well, which is where admission decisions were actually made, and the picture turns over. Women were admitted at a higher rate than men in four of the six departments: 82.4 against 62.1 percent in department A, 68.0 against 63.0 in B, 34.9 against 33.1 in D, and 7.0 against 5.9 in F. Men were ahead in C and E by less than four points.

A third grouping explains the reversal. Summarize each department's applicants and admission rate:

Women applied mostly to the departments that admitted few applicants of either sex, and men mostly to the two that admitted most. The pooled rate mixes those different departments in different proportions, so it measures where people applied as much as how they were treated. A trend that appears in every group but reverses when the groups are combined is called Simpson's paradox. Bickel and colleagues found few departments with statistically significant departures from what equal treatment would predict, and noted that the department-level data showed, if anything, a small bias in favour of women. Their deeper point was about where to look: aggregate at the level where the decision is made.

The core of it: GROUP BY is a claim about which differences matter. Before trusting a pooled figure, regroup by the variables that drive the outcome and see whether the conclusion survives.

Aggregates and what they skip

An aggregate function turns many rows into one value per group. Five cover most analysis, and each has a rule about NULL that Lesson 2 introduced.

WHERE filters rows, HAVING filters groups

A query is written in one order and evaluated in another, and the evaluation order explains most grouping errors.

1. FROM and JOIN assemble the rows.
2. WHERE removes rows, before any grouping.
3. GROUP BY forms the groups.
4. HAVING removes groups, using aggregates.
5. SELECT computes the output columns and aliases.
6. ORDER BY sorts, and LIMIT trims.

Because WHERE runs before grouping, it cannot test an aggregate: "departments that admitted fewer than 30 percent" needs HAVING.

SELECT dept, ROUND(100.0 * SUM(admitted) / SUM(admitted + rejected), 1) AS pct_admitted
FROM admissions
GROUP BY dept
HAVING 1.0 * SUM(admitted) / SUM(admitted + rejected) < 0.30;

It returns E at 25.2 percent and F at 6.4 percent. The 1.0 multiplier forces decimal division; in SQLite and PostgreSQL, dividing one integer by another discards the fraction, so 557 / 1835 is 0. In the shop database, SELECT order_id, COUNT(*) AS lines FROM order_items GROUP BY order_id HAVING COUNT(*) >= 2; returns orders 5001, 5003 and 5005.

One more rule protects you from a quiet error. Every column in the SELECT list of a grouped query should either appear in GROUP BY or sit inside an aggregate. PostgreSQL enforces this and refuses the query. SQLite does not: SELECT customer_id, order_date, COUNT(*) FROM orders GROUP BY customer_id; runs and returns an order_date for each customer, but for a customer with two orders that date is simply one of them, chosen by the engine. If you want the latest, say so with MAX(order_date).

Nested queries: one query inside another

A nested query, or subquery, is a SELECT inside another statement. The outer query uses its result as a value, a list or a table.

The last question is a classic exam scenario because it needs two levels of aggregation: first a total per customer, then the average of those totals. A common table expression names the first step so the second can use it:

WITH spend AS (
  SELECT o.customer_id, SUM(i.qty * i.unit_price) AS total
  FROM orders o JOIN order_items i ON i.order_id = o.order_id
  GROUP BY o.customer_id
)
SELECT c.name, ROUND(s.total, 2) AS total
FROM spend s JOIN customers c ON c.customer_id = s.customer_id
WHERE s.total > (SELECT AVG(total) FROM spend);

The average customer total is 237.02, and only Ana Lima, at 382.20, exceeds it. Two details are worth noticing. Averaging the order lines instead of the customer totals answers a different question, the average line value, which is the commonest mistake in this kind of query. And the average includes 189.00 from order 5007's unknown customer 99; excluding it raises the average to 249.03. Ana still leads either way, but the orphan from Lesson 9 is now quietly inside a number a manager will quote.

A scalar subquery gives each category its share of revenue: Camping brought 918.10, or 77.5 percent of the 1,185.10 total, and Clothing 267.00, or 22.5 percent. A correlated subquery, (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id), finds each customer's latest order, and returns NULL for Dev and Femi, who have none.

The NOT IN trap deserves its own warning. PostgreSQL's documentation explains that if the subquery's list contains a NULL and no value matches, NOT IN yields null, not true. In shop.db, asking for customers whose city is NOT IN the cities of customers 3 and 4, Portland and NULL, returns no rows at all, because Dev's NULL city poisons every comparison. Use NOT EXISTS, or filter NULLs out of the subquery, whenever its column can be NULL.

The core of it: nest when one answer depends on another: aggregate at the inner level first, name the step with WITH for readability, and prefer NOT EXISTS to NOT IN when NULLs are possible.

The same work in pandas

pandas expresses the same ideas with groupby. df.groupby("gender")[["admitted", "applied"]].sum() mirrors GROUP BY with SUM; filtering the result afterwards, for example rates[rates["pct"] < 30], does what HAVING does; and a nested query becomes two steps, a grouped DataFrame of customer totals and then a comparison with its mean. The pandas user guide calls this pattern split-apply-combine: split the rows into groups, apply a function to each, and combine the results.

Common misconceptions

• "The overall rate tells the whole story." Pooled rates can reverse within every group, as the Berkeley data show. Group by the variables that drive the outcome.
• "WHERE and HAVING are interchangeable." WHERE filters rows before grouping and cannot use aggregates; HAVING filters groups after.
• "COUNT(column) counts rows." It counts non-null values; COUNT(*) counts rows.
• "NOT IN is the opposite of IN." Not when the list contains NULL: then NOT IN returns no rows.
• "If the database accepted the query, the grouping is right." SQLite accepts bare columns outside GROUP BY and returns an arbitrary value from each group.

What to remember

• Berkeley 1973: 44.5 percent of men and 30.4 percent of women admitted overall, yet women led in four of six departments. Simpson's paradox: choose groupings that match where decisions are made.
• COUNT(*) counts rows; COUNT(col) and the other aggregates skip NULL; AVG divides by the non-null count.
• Evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. HAVING tests aggregates; WHERE cannot.
• Subqueries can return a value, a list or a table; CTEs name intermediate results; NOT EXISTS is safe where NOT IN fails on NULL.
• Two-level questions, such as above-average customers, aggregate the inner level first.

Sources

1. Bickel, P. J., Hammel, E. A., and O'Connell, J. W. (1975). Sex bias in graduate admissions: Data from Berkeley. Science, 187(4175), 398-404. doi.org
2. R Core Team. (n.d.). UCBAdmissions: Student admissions at UC Berkeley, R datasets package documentation. stat.ethz.ch
3. PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation: 7.2 Table expressions (GROUP BY and HAVING); 7.8 WITH queries; 9.23 Subquery expressions. GROUP BY; WITH; subqueries
4. SQLite. (n.d.). SELECT (aggregate queries and bare columns) and The WITH clause. SELECT; WITH
5. pandas development team. (2026). Group by: split-apply-combine. pandas 3.1.0 user guide. pandas.pydata.org

A lookup that was slow, broken and dangerous

On 17 August 2009 the US Attorney's Office in Newark announced the indictment of three men for breaches at five companies, among them Heartland Payment Systems, 7-Eleven and Hannaford, through which more than 130 million credit and debit card numbers were allegedly stolen. The press release named the technique: a SQL-injection attack, which, in its own words, exploits security vulnerabilities in elements of a computer that receives user input. The same flaw turns up in analysts' scripts today, usually alongside two quieter problems: queries that read far more data than they need, and indexes that exist but go unused.

Here is a helper an analyst wrote to look up customers by name, with the orders table behind it holding two million rows:

def find(name):
    sql = "SELECT customer_id, email FROM customers WHERE name = '" + name + "'"
    return con.execute(sql).fetchall()

Three complaints arrive in one week. The monthly report, which calls a similar order lookup 50,000 times, has become unbearably slow. The script crashed on a customer called Sean O'Brien. And a colleague who typed x' OR '1'='1 into the name box to test it received the whole customer list. The tempting diagnosis is that the server is too small and O'Brien's record is malformed. Neither is true. This lesson traces each failure to its cause, which is objective 2.1's basic query optimization: indexing, parameterization, subsets and temporary tables.

Failure 1: the query is assembled from text

The helper pastes the name inside quotation marks. For Ana Lima that produces valid SQL. For Sean O'Brien, the apostrophe in his name closes the string early, and SQLite stops with "syntax error near Brien". For the test input, the query becomes ... WHERE name = 'x' OR '1'='1', and since '1'='1' is true for every row, every customer comes back. The input has stopped being data and become part of the program. That is SQL injection.

The fix is a parameterized query: write the SQL once with a placeholder and pass the value separately, so the database always treats it as a value and never as code.

def find(name):
    return con.execute("SELECT customer_id, email FROM customers WHERE name = ?", (name,)).fetchall()

Now Sean O'Brien's row is found, and the test input returns nothing, because no customer is literally named x' OR '1'='1. Python's sqlite3 documentation warns against assembling queries with Python's string operations for exactly this reason and documents the ? and named :name placeholders; PostgreSQL drivers use their own placeholder styles, and OWASP's cheat sheet calls parameterized queries the first defence against injection. Parameters help speed too: PostgreSQL's documentation for PREPARE notes that a prepared statement is planned once and can then be executed many times, which pays off when one session runs many similar statements.

Bottom line: never build SQL by pasting values into strings; pass them as parameters, which fixes injection, apostrophes and repeated planning at once.

Failure 2: every lookup reads every row

The order lookup filters on customer_id. Ask SQLite how it will run the query by putting EXPLAIN QUERY PLAN in front of it:

EXPLAIN QUERY PLAN
SELECT order_id, order_date, total FROM orders WHERE customer_id = ?;

The answer is SCAN orders: SQLite reads all two million rows to find the six that belong to one customer. On the laptop used for this lesson, each lookup took about 160 milliseconds; 50,000 lookups take over two hours. Now create an index:

CREATE INDEX idx_orders_customer ON orders(customer_id);

The plan becomes SEARCH orders USING INDEX idx_orders_customer (customer_id=?), and the same lookup took about 0.2 milliseconds, roughly eight hundred times faster. Your times will differ with your hardware; the change from SCAN to SEARCH will not.

An index is a separate structure, kept sorted by the indexed column and pointing back to the rows, which lets the database jump to matching values instead of reading everything, the way a book's index saves you from reading every page. It is not free. Each index takes storage, and every INSERT, UPDATE and DELETE must update every index on the table, so indexes slow writes. Index the columns that queries filter, join and sort on, especially when a filter selects a small fraction of rows; do not index every column out of hope. On a transactional database owned by another team, propose indexes rather than creating them yourself.

Failure 3: the index exists and is ignored

The monthly report also counts 2025 orders, and someone has already indexed order_date. Compare two ways to write the filter:

Both return the same count. The first wraps the indexed column in a function, so the database must compute substr for every row before it can compare, and the index's sort order is useless. The second compares the raw column with a range, which the index can seek directly. The same rule explains why LIKE '%son', with a wildcard at the start, cannot use an ordinary index, while LIKE 'Son%' sometimes can. Write filters on the bare column wherever you can, and check the plan rather than assuming.

Subsets: ask for less

The cheapest row is the one never read or sent. The objectives call this working with subsets, and it has several forms.

• Name the columns. SELECT * moves every column across the network into memory, including wide text and large objects you will never use, and breaks when someone adds a column. List what you need.
• Filter in the database. Pulling two million rows into pandas to keep the 2025 ones wastes the database's indexes and your memory. Push WHERE conditions and aggregates into SQL and bring back the result.
• Limit while exploring. LIMIT 100 shows the shape of a table quickly. Without ORDER BY, though, which 100 rows you get is up to the database, so never treat them as representative or reuse them in a report.
• Restrict by partition. Large tables are often partitioned by date; a filter on the partition column lets the database skip whole months of data.

Temporary tables: compute once, reuse

The monthly report runs six queries against "orders from the last 90 days with their line totals". Recomputing that join six times repeats the expensive part. A temporary table stores it once:

CREATE TEMP TABLE recent_lines AS
SELECT o.order_id, o.customer_id, o.order_date, i.sku, i.qty * i.unit_price AS line_total
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_date >= date('now', '-90 days');

The six queries then read recent_lines. A temporary table belongs to the session that created it: SQLite keeps it in a separate temp database that is deleted when the connection closes, and PostgreSQL drops temporary tables automatically at the end of the session. Nobody else sees it and nothing needs cleaning up, which also makes it a safe place to stage intermediate results while debugging. If the temporary table is large and queried repeatedly by one column, it can be indexed like any other table. A CTE, from Lesson 10, names an intermediate result for one statement; a temporary table keeps it for the whole session.

A checklist for a slow or suspicious query

Common misconceptions

• "Escaping quotes by hand is as good as parameters." Hand-escaping is easy to get wrong and differs between databases. Placeholders let the driver keep data and code apart every time.
• "More indexes always mean faster queries." Each index slows every write and takes space, and an index on a column nobody filters by does nothing.
• "If an index exists on the column, the query uses it." Wrapping the column in a function or starting a pattern with a wildcard defeats it. Read the plan.
• "LIMIT 100 gives a fair sample." Without ORDER BY the rows are whatever the database finds first, often the oldest or the most recently inserted.
• "A temporary table is shared with my team until I drop it." It is private to the session that created it and disappears when the connection closes.

The takeaway

• Parameterize every query that takes outside values: it prevents SQL injection, handles apostrophes and lets plans be reused.
• Use EXPLAIN QUERY PLAN (EXPLAIN in PostgreSQL): SCAN reads everything; SEARCH USING INDEX seeks.
• Index columns used to filter, join and sort, mindful that indexes slow writes; keep filters on bare columns so indexes apply.
• Ask for subsets: named columns, filters and aggregates in SQL, LIMIT only for a first look.
• Temporary tables hold intermediate results for one session and vanish when it ends.

Sources

1. U.S. Attorney's Office, District of New Jersey. (2009, August 17). Three men indicted for hacking into five corporate entities, including Heartland, 7-Eleven, and Hannaford, with over 130 million credit and debit card numbers stolen [Press release]. U.S. Department of Justice. justice.gov (PDF)
2. Python Software Foundation. (n.d.). sqlite3: DB-API 2.0 interface for SQLite databases, section "How to use placeholders to bind values in SQL queries". docs.python.org
3. OWASP Foundation. (n.d.). Query parameterization cheat sheet and SQL injection prevention cheat sheet. parameterization; injection prevention
4. SQLite. (n.d.). EXPLAIN QUERY PLAN; Query planning; CREATE INDEX; CREATE TABLE (temporary tables). EXPLAIN QUERY PLAN; query planning; CREATE TABLE
5. PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation: chapter 11 Indexes; 14.1 Using EXPLAIN; PREPARE; CREATE TABLE (TEMPORARY). indexes; EXPLAIN; PREPARE

Sixty thousand households

Every month the United States learns its unemployment rate from a survey of about 60,000 eligible households, roughly 110,000 people. The Current Population Survey began in 1940 as a Work Projects Administration program, and the Census Bureau has run it for the Bureau of Labor Statistics since 1942. Its sample is drawn from about 800 geographic areas and designed, in the bureau's words, to be "representative of the entire population of the United States". Each month a quarter of the households are replaced, so no household stays in the sample for more than four consecutive months.

A nation of more than 300 million described by 110,000 people is the most consequential sampling in American statistics, and it works because of how the households are chosen, not how many there are. This lesson follows data from that kind of collection into an analyst's hands. Objective 2.1 ends with two acquisition topics: collecting new data by surveying and sampling, and integrating data from several sources through pipelines described as ETL or ELT. Both are about the same worry: the data you analyze must faithfully stand for something larger than itself.

Surveying: who is asked, and how

A survey collects data by asking people questions. Organizations run them constantly: customer satisfaction surveys, employee engagement surveys, market research panels. Whether the answers can be trusted depends on four design decisions.

• Population and frame. The population is everyone the results should describe, for example all customers who bought in the last year. The sampling frame is the list from which the sample is actually drawn, for example the customer email list. People missing from the frame, such as customers who bought in cash and gave no email, can never be selected, and that gap is coverage error.
• Selection. How individuals are chosen from the frame, the subject of the next section.
• Questions and mode. Wording and order shape answers, and so does the mode: an interviewer, a phone call or a web form. Leading questions ("How much did you enjoy our improved service?") push answers in one direction.
• Nonresponse. Selected people who do not answer differ, often systematically, from those who do. A customer survey answered mainly by the delighted and the furious describes neither the typical customer nor the average one.

For an analyst handed survey data, the first questions are therefore not statistical but practical: who was in the frame, how were they selected, how many responded, and what exactly were they asked.

Sampling: probability and the rest

Statistics Canada's teaching guide divides sampling methods into two families. In probability sampling, each unit's chance of selection is known and can be calculated, which is what makes reliable estimates and margins of error possible. In non-probability sampling there is, in the guide's words, "no way to estimate the probability of any one element being included in the sample", so any conclusion about the population rests on assuming the sample is representative.

Two points from the table matter most on the exam. Stratification protects small groups: a simple random sample of 500 from five regions might include only a handful of customers from the smallest region, while a stratified sample guarantees each region its share or more. And size does not cure bias: a volunteer poll of 100,000 website visitors tells you about people who answer website polls, while 500 customers drawn at random from the full list can describe all of them, within a margin of error that Lesson 18 computes.

In pandas, a simple random sample is one line, members.sample(n=500, random_state=42), where random_state fixes the random seed so the same sample can be drawn again. A stratified sample takes a fraction from each group: members.groupby("region").sample(frac=0.01, random_state=42).

Key idea: a sample can stand for a population only if every unit's chance of selection is known; stratify to protect small groups, and never trust size to fix self-selection.

Integration: many sources, one view

Now put the collected data to work. A retailer wants to know whether unemployment in a store's region predicts its sales. That means bringing together the stores' sales from the transactional database, the regional unemployment series published by the BLS, and perhaps weather from a public API. Data integration is combining data from different sources into one consistent view, and nearly all of its difficulty comes from differences the sources do not announce.

Every row of that table is a place where two sources can be joined successfully and still produce nonsense. The join runs; the result is wrong. That is why integration is a design task, not just a coding one, and why its decisions belong in the documentation of Lesson 27.

ETL and ELT

Integration is usually automated as a pipeline, and two patterns dominate. Microsoft's architecture guidance defines extract, transform, load (ETL) as an integration process that consolidates data from diverse sources into a unified store, transforming it according to business rules in a specialized engine, often through staging tables, before loading it into its destination. Extract, load, transform (ELT) "differs from ETL solely in where the transformation takes place": the raw data is loaded first and transformed inside the target system, using that system's own processing power.

Three further distinctions describe any pipeline. A full load copies everything each time; an incremental load, sometimes called a delta load, copies only rows that are new or changed since the last run, which is faster but needs a reliable way to detect change, such as a last-modified timestamp or change data capture from the database's log. A batch pipeline runs on a schedule, nightly or hourly; a streaming pipeline processes events continuously as they arrive, for fraud alerts or live operations dashboards. And reverse ETL, a newer term in the same guidance, sends modelled data from the warehouse back into operational tools such as a CRM.

Key idea: ETL cleans before loading, ELT loads raw and cleans inside the target; choose by where the computing power, the raw-data needs and the compliance rules sit.

A pipeline in miniature

You can see both patterns in a dozen lines of Python. An ELT version loads the ECB exchange-rate CSV from Lesson 6 untouched into a staging table, then transforms it with SQL inside SQLite:

import sqlite3, pandas as pd
con = sqlite3.connect("pipeline.db")
url = "https://data-api.ecb.europa.eu/service/data/EXR/D.USD.EUR.SP00.A?lastNObservations=30&format=csvdata"
pd.read_csv(url).to_sql("stg_rates", con, if_exists="replace", index=False)   # extract, load
con.executescript("""
  DROP TABLE IF EXISTS usd_rates;
  CREATE TABLE usd_rates AS
  SELECT TIME_PERIOD AS rate_date, CAST(OBS_VALUE AS REAL) AS usd_per_eur
  FROM stg_rates WHERE OBS_VALUE IS NOT NULL;                                 -- transform
""")

An ETL version would do the renaming, type conversion and filtering in pandas first and load only the finished table. Same result; the difference is where the work happened and whether the raw rows were kept. In ELT the staging table preserves exactly what the source sent, which is what an auditor, or you next month, will want when a number looks odd.

Common misconceptions

• "A bigger sample is always a better sample." Size reduces random error, not bias. A huge self-selected sample can be worse than a small random one.
• "Stratified and cluster sampling are the same thing." Stratified sampling draws from every group; cluster sampling picks some groups and takes everyone in them.
• "If the join runs, the integration worked." Mismatched keys, calendars, definitions and units produce joins that succeed and results that are wrong.
• "ELT means skipping transformation." ELT still transforms; it does so after loading, inside the target system.
• "An incremental load is just a faster full load." It depends on correctly detecting changed rows; without a reliable change marker, updates and deletions are missed.

Recap

• The CPS reaches about 60,000 households a month with a probability sample designed to represent the US population; how units are chosen matters more than how many.
• Probability methods: simple random, systematic, stratified, cluster, multi-stage. Non-probability: convenience, volunteer, quota, judgement, with no calculable selection chances.
• Survey quality depends on the frame, selection, wording and mode, and nonresponse.
• Integration must reconcile keys, calendars, definitions, units and timing before joining.
• ETL transforms before loading in a separate engine; ELT loads raw data and transforms inside the target. Loads are full or incremental, batch or streaming.

Sources

1. U.S. Bureau of Labor Statistics. (n.d.). How the government measures unemployment. Retrieved 2 November 2026. bls.gov
2. Statistics Canada. (n.d.). Statistics: Power from data!, Probability sampling and Non-probability sampling. probability sampling; non-probability sampling
3. Statistics Canada. (2010). Survey methods and practices (Catalogue no. 12-587-X). statcan.gc.ca
4. Diez, D. M., Cetinkaya-Rundel, M., and Barr, C. D. (2019). OpenIntro statistics (4th ed.), section 1.3 Sampling principles and strategies. OpenIntro. openintro.org
5. Microsoft. (2025). Extract, transform, load (ETL). Azure Architecture Center. learn.microsoft.com
6. pandas development team. (2026). pandas.DataFrame.sample; Group by: split-apply-combine. pandas 3.1.0 documentation. sample; groupby

What is the average order?

A manager emails a twelve-row export, orders_export.csv, at 9:00 and asks one question: what was our average order value in early September? pandas answers in a second: 753.35. The median order was 69.00. Something in the file has pulled the mean to more than ten times the typical order, and the honest answer at 9:01 is not a number but "let me look at the data first".

That look has a name. Objective 2.2 asks you, given a scenario, to explore a data set to identify possible inconsistencies, and lists six things to find: missing values, duplication, redundancy, outliers, completeness and validation. The UK government's Data Quality Hub published a framework in December 2020 that names six dimensions of quality covering the same ground: completeness, uniqueness, consistency, timeliness, validity and accuracy. This lesson works through the manager's file problem by problem, then turns the steps into a routine you can run on any new data set.

order_id,order_date,customer,email,state,qty,unit_price,amount
7001,2026-09-01,Ana Lima,ana@example.com,OR,1,189.00,189.00
7002,2026-09-01,Ben Osei,ben@example.com,WA,2,34.50,69.00
7003,2026-09-02,Chloe Wu,,OR,1,29.90,29.90
7004,2026-09-02,Dev Patel,dev@example,ID,3,14.00,42.00
7005,2026-09-03,Eva Rossi,eva@example.com,OR,1,89.00,89.00
7005,2026-09-03,Eva Rossi,eva@example.com,OR,1,89.00,89.00
7006,2026-09-04,Femi Ade,femi@example.com,WA,-1,34.50,-34.50
7007,2026-09-04,Gus Ruiz,gus@example.com,XX,1,189.00,189.00
7008,2026-09-05,Hana Sato,hana@example.com,OR,2,14.00,
7009,2026-09-31,Ivan Petrov,ivan@example.com,WA,1,29.90,29.90
7010,2026-09-06,Jo Kim,jo@example.com,OR,40,189.00,7560.00
7011,2026-09-06,ana lima,ANA@EXAMPLE.COM,OR,1,34.50,34.50

Every figure below came from loading this file with pd.read_csv in pandas 3.1.

First pass: shape, types and summary

Start with what the file is before asking what it says. df.shape gives 12 rows and 8 columns. df.dtypes shows order_id, qty as integers, unit_price and amount as floats, and the rest as text, including order_date, which pandas did not parse as a date. df.describe() summarizes the numeric columns, and three lines of it already point at problems: qty has a minimum of -1 and a maximum of 40; amount has a count of 11, not 12; and amount's mean of 753.35 sits far above its 75th percentile of 139.00.

A summary is a map of where to dig, not a finding. Each oddity it shows belongs to one of the six problems the objective lists.

Missing values and completeness

df.isna().sum() counts missing values per column: one email (order 7003) and one amount (order 7008). Completeness is the same fact expressed as a rate: email and amount are each 91.7 percent complete, every other column 100 percent. In SQL the count is COUNT(*) - COUNT(email), since COUNT(column) skips nulls.

Two questions decide what a gap means. First, is the value unknown or not applicable? A missing email may mean the customer declined to give one; a missing amount on a completed order is an error. Second, can it be recovered? Order 7008's amount is derivable from its other columns, 2 times 14.00, so it can be filled from the data itself, a decision Lesson 15 calls imputation. Completeness also applies to records, not just fields: if the export should contain every order from 1 to 6 September, check that no order numbers are missing from the sequence and that the date range is what you were promised.

The point: count missing values per column, decide whether each gap means unknown, not applicable or error, and check that whole records are not missing as well.

Duplication and redundancy

df.duplicated().sum() finds one exact duplicate row: order 7005 appears twice, identical in every column, most likely an export or copy-paste fault. Exact duplicates are the easy case, and df.drop_duplicates() removes them. Two harder cases need judgement.

• Duplicate keys with different values. df["order_id"].duplicated() checks the column that should be unique. If two rows shared an order_id but differed in amount, dropping one blindly would discard information; you would need the source system to say which is right.
• Near duplicates of an entity. Orders 7001 and 7011 are different orders, correctly, but their customers are "Ana Lima" with ana@example.com and "ana lima" with ANA@EXAMPLE.COM. Counted as written, the file has eleven customers; standardized to lower case it has ten. This is the uniqueness dimension: each real-world thing should appear once.

Redundancy is different from duplication: it is the same information stored twice in different columns. Here amount always equals qty times unit_price, which a quick check confirms for every row where amount is present. Redundant columns are not always wrong, since a stored total can be faster to query, but each is a chance for the copies to disagree, so verify them against each other and know which one is authoritative.

Outliers: investigate, then decide

The NIST/SEMATECH e-Handbook of Statistical Methods defines an outlier as an observation that lies an abnormal distance from other values, and gives the standard screening rule from the box plot. Compute the first and third quartiles, Q1 and Q3, and their difference, the interquartile range (IQR). Values beyond the inner fences, Q1 minus 1.5 IQR or Q3 plus 1.5 IQR, are mild outliers; values beyond the outer fences at 3 IQR are extreme.

One value lies beyond the fences: order 7010 at 7,560.00, forty tents at 189.00 each. It is also beyond the outer fence, so it counts as extreme, and it alone explains why the mean is 753.35 while the median is 69.00. The handbook's advice is that outliers "should be investigated carefully": they are often bad data, but they may carry valuable information about the process. Is order 7010 a typing error for 4 tents, or a scout troop's bulk order? The arithmetic is internally consistent, so the next step is a phone call to the store, not a delete key. If it is real, report it, and consider whether the median or a mean without bulk orders better answers the manager's question; if it is an error, fix it at the source.

The point: fences find candidates, not culprits; an outlier is removed only when you know it is an error, and otherwise reported and explained.

Validation: rules the data must obey

Validity is about whether values fall within allowed ranges and formats. You check it by writing rules and counting the rows that break them:

Each failure needs a decision, and the decision is a business one. A negative quantity may be a legitimate return that belongs in a separate table, not a deletion. A placeholder state may be recoverable from the customer's address. An impossible date needs the source system. The value of written rules is that they run again on next month's file automatically, which is where Lesson 30's automated quality monitoring begins.

A routine for every new data set

1. Shape and types: rows, columns, and whether each column's type matches its meaning (Lesson 2).
2. Completeness: missing values per column, and missing records against what was promised.
3. Uniqueness: exact duplicate rows, duplicate keys, near-duplicate entities after standardizing case and spacing.
4. Redundancy and consistency: columns that should agree with each other or with another source.
5. Distributions and outliers: describe(), value counts for categories, IQR fences for numbers.
6. Validity: written rules for dates, ranges, codes and formats; count the failures.
7. Record and ask: write down every issue with its row count, and ask the data's owner before changing anything you cannot verify.

Only after this does the manager get an answer, and a better one: "Excluding one duplicate and one bulk order we are confirming, the typical order was about 69 dollars; three rows have invalid data that the store is checking."

Common misconceptions

• "Outliers are errors and should be removed." NIST's handbook says to investigate them first; a genuine bulk order is information, not noise.
• "drop_duplicates() solves duplication." It removes only identical rows. Duplicate keys with conflicting values, and the same customer spelled two ways, need investigation and standardizing.
• "A complete column is a correct column." Order 7007's state is present and invalid. Completeness and validity are separate dimensions.
• "Exploration is optional when the source is a trusted system." The file above came from a point-of-sale system and still held seven kinds of problem.
• "A missing value should be filled with zero." Zero asserts a value. Decide whether a gap means unknown, not applicable or error before filling it.

What to carry forward

• Profile before analyzing: shape, types, describe(), missing values, duplicates.
• Missing values: count them, measure completeness, and decide what each gap means before filling it.
• Duplication: exact rows, duplicate keys, near-duplicate entities. Redundancy: the same fact in two columns, which must agree.
• Outliers: IQR fences at 1.5 and 3 times the IQR flag candidates; investigate before removing.
• Validation: write rules for dates, ranges, codes and formats; the UK framework's six dimensions are completeness, uniqueness, consistency, timeliness, validity and accuracy.

Sources

1. Government Data Quality Hub. (2020). The Government Data Quality Framework. GOV.UK. gov.uk
2. NIST/SEMATECH. (2012). e-Handbook of statistical methods, section 7.1.6 What are outliers in the data? and section 1.3.3.7 Box plot. National Institute of Standards and Technology. outliers; box plot
3. pandas development team. (2026). Working with missing data; pandas.DataFrame.duplicated; pandas.DataFrame.describe. pandas 3.1.0 documentation. missing data; duplicated; describe
4. Diez, D. M., Cetinkaya-Rundel, M., and Barr, C. D. (2019). OpenIntro statistics (4th ed.), section 2.1 Examining numerical data (box plots, quartiles and outliers). OpenIntro. openintro.org

Six rows, five problems each

A loyalty-program contact file arrives from three stores and a website, and the six rows below contain most of the text problems an analyst meets in a year. Names have stray spaces and random capitals. Phone numbers come in five formats. City, state and postal code share one field. Dates arrive in two formats, one of them impossible. Spending carries dollar signs and thousands separators. And one row is a test record nobody deleted.

id,name,phone,location,joined,spend
1,"  ana LIMA ",(503) 555-0142,"Portland, OR 97201",2025-03-04,"$1,204.50"
2,Ben Osei,503.555.0199,"Seattle, WA 98101",03/15/2025,$89.00
3,chloe wu,5035550123,"Boise, ID 83702",2025-04-01,$0.00
4,Dev Patel,+1 503 555 0177,"Portland, OR 97209",2025-13-01,"$2,000"
5,TEST USER,000-000-0000,"Nowhere, XX 00000",2025-01-01,$0.00
6,sean o'brien,503-555-0110,"Vancouver, BC V6B 1A1",2025-05-20,$45.10

Objective 2.3 asks you, given a scenario, to apply transformation and cleansing techniques, and this lesson takes four of them: string manipulation with regular expressions, parsing, conversion and deletion. Lesson 15 takes the rest. The procedure below cleans the file one column at a time in pandas 3.1, and every output shown is what that code produced. Then one input changes, and a rule that worked perfectly starts failing.

Step 1: standardize text

Start with the cheapest fixes. df["name"].str.strip() removes leading and trailing spaces, and .str.title() capitalizes each word, turning " ana LIMA " into "Ana Lima" and "sean o'brien" into "Sean O'Brien". Standardizing means bringing every value to one agreed form, and it is what lets Lesson 13's "Ana Lima" and "ana lima" be recognized as one customer.

Title case is a convenience, not a truth. Python's own title() turns "mcdonald" into "Mcdonald" and "van der berg" into "Van Der Berg", both wrong. For names, a safer rule is often to standardize a separate matching key, lower case with spaces trimmed, and leave the displayed name as the customer typed it. Codes deserve the same care: when a state column holds "OR", "or ", "Oregon" and "Ore.", build an explicit mapping table to the two-letter code rather than trusting a case change to fix it.

A note on vocabulary. The objectives list "standardization" among transformation techniques, and analysts use the word in two senses: bringing formats to one convention, as here, and rescaling numbers to a common scale with z-scores, which Lesson 17 covers. An exam scenario will make clear which one it means.

Step 2: regular expressions

A regular expression, or regex, is a small pattern language for matching text. Python's re module, pandas' string methods, PostgreSQL's regexp functions and LibreOffice Calc's REGEX function, added in version 6.2, all use close relatives of the same syntax. Six pieces cover most cleaning work:

Phone numbers show the most common use: replace, then validate. df["phone"].str.replace(r"[^0-9]", "", regex=True) deletes every character that is not a digit, so (503) 555-0142, 503.555.0199 and 503-555-0110 all become ten digits. The +1 number becomes eleven digits beginning with 1, so a second rule drops that leading country code. Then .str.fullmatch(r"[0-9]{10}") confirms that every value is now exactly ten digits. All six pass, including the test record's 0000000000, which shows that a format check is not a reality check.

Email validation from Lesson 13 uses the same tools: [^@ ]+@[^@ ]+[.][A-Za-z]{2,} requires something, an @, something, a full stop and a domain ending of at least two letters. It rejects dev@example, which has no ending, and accepts ana@example.com. Real email syntax is far looser than any short pattern, so treat such a regex as a screen for obvious errors, not proof that an address exists.

One trap catches everyone once. Quantifiers are greedy: they match as much as they can. On the text "Tent (green) and stove (red)", the pattern [(](.*)[)] captures "green) and stove (red", running from the first opening bracket to the last closing one. Adding ? makes the quantifier lazy, [(](.*?)[)], and the capture is just "green".

Why this matters: regular expressions turn vague rules ("phone numbers should be digits") into exact, repeatable tests and fixes; anchor them, watch greediness, and remember that matching a format is not proof the value is real.

Step 3: parse combined fields

Parsing means splitting a value into its meaningful parts. The location field packs city, state and postal code into one string. A regex with three groups extracts them in one step:

parts = df["location"].str.extract(r"^(.+), ([A-Z]{2}) ([0-9]{5})$")
parts.columns = ["city", "state", "zip"]

Rows 1 to 5 split cleanly: Portland, OR, 97201 and so on, and the test record yields Nowhere, XX, 00000. Parsing also covers dates and numbers stored as text. Dates first: pd.to_datetime(df["joined"], format="%Y-%m-%d", errors="coerce") parses the ISO dates and turns the others into missing values, and a second pass with format="%m/%d/%Y" fills in row 2's 03/15/2025. Row 4's 2025-13-01 fits neither format, because there is no thirteenth month, so it stays missing and goes on the list of values to query with the store.

Step 4: convert types

Conversion changes a value's type so it can be calculated with, and it is where Lesson 2's type decisions are carried out. The spend column is text because of its symbols. Remove them, then convert:

spend = pd.to_numeric(df["spend"].str.replace(r"[$,]", "", regex=True), errors="coerce")

The result is 1204.5, 89.0, 0.0, 2000.0, 0.0 and 45.1, a total of 3,338.60. The argument errors="coerce" turns anything still unconvertible into a missing value instead of stopping the program, which is what you want during cleaning, provided you then count the missing values it created. In SQL, CAST does the same job: CAST(REPLACE(REPLACE(spend, '$', ''), ',', '') AS REAL) in SQLite. PostgreSQL also offers regexp_replace for pattern-based replacements; SQLite has no built-in regular expression function, so its REGEXP operator works only when an application supplies one.

Step 5: delete by rule, and keep the receipts

Row 5 is a test record: its name says so and its phone number is all zeros. Deletion is the right treatment for records that should never have been in the data, such as tests, exact duplicates and rows outside the scope of the analysis. It is the wrong treatment for records that are merely inconvenient, such as outliers you have not investigated or rows with one missing field.

Delete by a written rule, not by eye: test = df["name"].str.contains("test", case=False) | (digits == "0000000000") flags exactly row 5. Before removing the flagged rows, save them to a separate file and record how many were removed and why. Work on a copy, never on the source. Those three habits, a rule, a log and a preserved original, are what let a colleague, or you in six months, explain why the row count fell from 6 to 5.

Change one input: a customer from Canada

Row 6 has not been mentioned yet, because every step handled it except one. The location parser returned nothing at all for "Vancouver, BC V6B 1A1": no city, no province, no postal code. The pattern demanded five digits at the end, and Canadian postal codes mix letters and digits. The regex worked perfectly on the data its author imagined and silently failed on the first record from outside it.

The lesson generalizes to every cleaning rule. Each one encodes an assumption, here that every customer is American, and the moment the data's scope changes, the rule starts producing missing values without any error. Two defences help. Count the values each rule fails to parse, every time it runs, and investigate any that appear. And write rules for the scope you have actually checked, adding new branches, such as a Canadian postal-code pattern, when the data grows.

Common misconceptions

• "Title case fixes names." It mangles McDonald, van der Berg and many others. Standardize a matching key and keep the display value.
• "If a value matches the regex, it is valid." 0000000000 is ten digits and still not a phone number. A pattern checks format, not reality.
• ".* matches up to the next closing bracket." Quantifiers are greedy and run to the last possible match; add ? for the shortest.
• "errors='coerce' cleans the column." It hides problems as missing values. Count the values it converted to missing and investigate them.
• "Deleting bad rows is the cleanest fix." Delete only by a documented rule, keep a copy of what you removed, and never delete outliers or incomplete rows by reflex.

What you now know

• Standardize: strip spaces, unify case, map variant codes with an explicit table; "standardization" can also mean z-score scaling, covered in Lesson 17.
• Regex pieces: character classes, shorthand classes, quantifiers, anchors, groups and alternation; quantifiers are greedy unless followed by ?.
• Parse combined fields with capture groups; parse dates with explicit formats, one format at a time.
• Convert with to_numeric, to_datetime or CAST after removing symbols; count what coercion turned into missing values.
• Delete only by written rules, with a log and a saved copy; every cleaning rule encodes an assumption about the data's scope.

Sources

1. Python Software Foundation. (n.d.). Regular expression HOWTO and re: Regular expression operations. HOWTO; re
2. pandas development team. (2026). Working with text data; pandas.to_datetime; pandas.to_numeric. pandas 3.1.0 documentation. text; to_datetime; to_numeric
3. The Document Foundation. (n.d.). REGEX function. LibreOffice Calc Help. help.libreoffice.org
4. PostgreSQL Global Development Group. (2026). Pattern matching, PostgreSQL 18 documentation, section 9.7. postgresql.org
5. SQLite. (n.d.). SQL language expressions (the REGEXP operator and CAST). sqlite.org

Order 5001 held a tent and two stoves

Order 5001 held a two-person tent and two camp stoves, but the export that recorded it squeezed both products into one field, "TENT-2P;STOVE-1". The same export left two shipping times blank, kept customers' regions in another table, and arrived as two quarterly files. Before anyone can ask which products sell together in which region, the data has to change shape: tables combined, lists unpacked, gaps filled, new columns calculated and amounts grouped into bands.

Objective 2.3 lists these transformations by name: merging, appending, exploding, augmentation, imputation, derived variables and calculated fields, and clustering by binning. They are easy to perform and easy to perform wrongly, and the exam's scenarios usually turn on choosing the right one. So this lesson first sets them side by side in one table, then reads the table technique by technique. Every result comes from pandas 3.1 and two small quarterly files: four orders from the fourth quarter and two from the first.

Seven transformations, side by side

Read the second column and the table sorts itself into three groups. Merging and augmentation add columns; appending and exploding add rows; imputation, derivation and binning change values inside the existing shape. Whenever a scenario describes the table you need, count its rows and columns against the one you have, and the technique usually names itself.

Appending and merging

Appending the two quarterly files is the simplest step: orders = pd.concat([q4, q1], ignore_index=True) stacks four rows on two, making six. It is the pandas form of Lesson 9's UNION ALL, and it has the same requirement: the columns must mean the same thing. If one quarter's file calls a column ship_days and the other days_to_ship, concat keeps both columns and fills each with missing values where the other file had data. It is a silent failure, and you catch it by checking the column list after every append.

Merging brings in each customer's region from a separate table: orders.merge(customers, on="customer_id", how="left", validate="many_to_one", indicator=True). Two arguments make the merge safe. validate="many_to_one" asserts that each customer appears once in the customer table; when a second row for customer 1 was added as a test, pandas refused the merge with a MergeError instead of silently fanning six orders out to eight rows. And indicator=True adds a column saying whether each row matched in both tables or only one, so unmatched orders, Lesson 9's orphans, are counted rather than lost.

Exploding

The skus field holds a list in disguise. Split the text into a real list, then explode it:

lines = orders.assign(sku=orders["skus"].str.split(";")).explode("sku")

Six orders become nine rows, one per product: order 5001 appears twice, once with TENT-2P and once with STOVE-1. Exploding is exactly the "one row per array element" step that Lesson 3 met with nested JSON. Its risk is the fan-out of Lesson 9: every order-level column, including the order's amount of 258.00, is now repeated on each product row. Summing amount over the exploded table counts order 5001 twice. Either sum amounts before exploding, or explode first and sum only line-level values.

What matters here: merging and augmenting add columns, appending and exploding add rows, and every operation that adds rows can multiply values you later sum; count rows before and after each step.

Augmentation and derived variables

Augmentation enriches your data with attributes from outside it: each store's regional unemployment rate from the BLS, public holidays from a calendar table, weather from a public API, population from a census. Technically it is a merge with a reference table, and all of Lesson 12's integration hazards apply: the keys, the definitions and the dates must line up. In machine learning, the same word also means creating extra training examples by altering existing ones, such as flipped or cropped images, but Data+ scenarios about preparing analytical data mean enrichment.

A derived variable, called a calculated field in BI tools, is a new column computed from existing ones. Examples from the order data: line_total as qty times unit_price; days_to_ship as the difference between ship date and order date; is_bulk as qty of 10 or more; first_order as a flag on each customer's earliest order. Derived columns put business logic in one place, so every report computes margin or days to ship the same way. They also inherit every flaw of their inputs: if unit_price is text, the multiplication fails or, worse, as Lesson 1 showed, does something surprising.

Imputation: filling gaps without inventing certainty

Two of the six orders have no ship_days. Imputation fills missing values with estimates so that rows are not lost. The methods range from crude to careful:

• A single statistic: the mean, the median or, for categories, the mode.
• A group statistic: the median ship time for orders in the same region or shipping method, which respects known differences.
• Carry forward or interpolate: for time series such as daily stock levels, take the last known value or a value between neighbours.
• Model-based: predict the missing value from other columns, a step beyond Data+ depth.

Every method has a cost that is easy to miss. The four known ship times are 2, 5, 3 and 4 days, with a mean of 3.5 and a standard deviation of 1.29. Fill the two gaps with the mean and the mean stays 3.5, but the standard deviation falls to 1.00, because two values now sit exactly at the centre. Simple imputation makes data look less variable and more certain than it is, which flatters any later test or margin of error. Roderick Little and Donald Rubin's textbook on missing data treats this distortion at length; for analysts, three habits contain it. Record which values were imputed, for example in a ship_days_missing flag column; prefer group statistics to one global value; and never impute identifiers or the outcome you are trying to explain.

What matters here: imputation keeps rows but invents values; flag what you filled, fill by group where you can, and remember that filled data looks more certain than it is.

Binning

Binning groups a continuous value into ranges, and the objectives list it under clustering, in the everyday sense of grouping similar values together. (Clustering algorithms such as k-means, which group whole records by similarity across many variables, are a modelling topic beyond Data+.) There are two common kinds of bin.

• Fixed edges chosen for meaning: pd.cut(orders["amount"], bins=[0, 100, 200, float("inf")], labels=["under 100", "100 to 200", "over 200"], right=False) puts 89.00 under 100; 124.20, 138.00 and 179.00 in 100 to 200; and 207.90 and 258.00 over 200.
• Quantile bins with equal numbers of rows: pd.qcut(orders["amount"], q=4) splits the six orders into quartiles, which suits ranking, such as "top quarter of customers by spend", but produces edges with no business meaning.

Edges are where binning goes wrong. By default pd.cut builds intervals closed on the right, so a value of exactly 100 lands in the 0-to-100 bin; with right=False it lands in 100-to-200. A label that says "under 100" while holding 100 is a small lie that someone will eventually quote. Decide the edge rule, write it into the labels, and test the boundary values. In SQL the same bins are a CASE expression, CASE WHEN amount < 100 THEN 'under 100' WHEN amount < 200 THEN '100 to 200' ELSE 'over 200' END, where the order of conditions makes the edges explicit. The NIST/SEMATECH handbook's discussion of histograms is a reminder that the number and placement of bins can change what a distribution appears to show.

Common misconceptions

• "Appending and merging are the same thing." Appending stacks rows with the same columns; merging adds columns matched on a key.
• "After exploding, I can sum any column." Order-level values are repeated on every exploded row and will be counted more than once.
• "Mean imputation is harmless because the mean does not change." The mean stays put, but the spread shrinks, making the data look more certain than it is.
• "Bins are just labels." Edges decide where boundary values land; an unstated edge rule misplaces values exactly on the boundary.
• "Augmentation always means synthetic data." In analytics it usually means enriching records with outside attributes; synthetic variation is the machine-learning sense.

The short version

• Add columns by merging (on keys, with validate and indicator) or augmenting (from outside sources); add rows by appending (matching columns) or exploding (one row per list element).
• Derived variables and calculated fields put business formulas in one place and inherit their inputs' flaws.
• Imputation fills gaps with a statistic, a group statistic, a carried value or a model; it shrinks spread, so flag imputed values.
• Binning groups values with fixed or quantile edges; decide and label the edge rule and test boundary values.
• Count rows before and after every reshaping step.

Sources

1. pandas development team. (2026). pandas.DataFrame.merge; pandas.concat; pandas.DataFrame.explode; pandas.cut; pandas.qcut. pandas 3.1.0 documentation. merge; concat; explode; cut; qcut
2. pandas development team. (2026). Working with missing data. pandas 3.1.0 user guide. pandas.pydata.org
3. Little, R. J. A., and Rubin, D. B. (2019). Statistical analysis with missing data (3rd ed.). Wiley.
4. NIST/SEMATECH. (2012). e-Handbook of statistical methods, section 1.3.3.14 Histogram. National Institute of Standards and Technology. itl.nist.gov

The number the Census Bureau leads with

On 15 September 2026 the US Census Bureau published its report on income in 2025, and the figure in its first finding was a median: median household income was $87,460. Not the mean, not a total, but the income of the household in the middle, with half the country's households earning more and half earning less. The choice is deliberate, and understanding it is the first skill in objective 3.2, which asks you, given a scenario, to select the appropriate statistical method or function.

The objective lists four basic statistical methods, descriptive, inferential, predictive and prescriptive, and then the functions and measures that carry them out, starting with measures of central tendency. This lesson follows one retail analyst's week through all four kinds of question, then settles the question the Census Bureau settled: when someone asks for "the average", which center do you report?

Four questions, four kinds of analysis

On Monday the analyst's manager asks how September went. On Tuesday, whether the new loyalty email actually raised spending. On Wednesday, how many tents the stores will sell in November. On Thursday, how many tents to order. Four questions, four kinds of analysis.

The four build on one another. A forecast is only as good as the description of the past it learned from, and a recommendation is only as good as the forecast it optimizes. The exam's scenarios test whether you can tell them apart from the question's wording: "summarize", "what was" and "how many" signal descriptive; "sample", "significant" and "confidence" signal inferential; "forecast", "likely" and "predict" signal predictive; "optimize", "recommend" and "best course of action" signal prescriptive. Some frameworks add diagnostic analysis, asking why something happened; the Data+ objectives fold that work into the others.

Remember: descriptive says what happened, inferential says what a sample implies about a population, predictive says what is likely, and prescriptive says what to do about it.

The mean, the median and the mode

Descriptive statistics start with the center of a distribution, and the NIST/SEMATECH e-Handbook of Statistical Methods defines the three standard measures.

• The mean is the sum of the values divided by their number.
• The median is the middle value once the data are sorted; with an even number of values, it is the average of the two middle ones.
• The mode is the most frequent value.

Watch them disagree. A small outdoor store pays ten staff between 38,000 and 52,000 dollars a year, and its owner takes 310,000. The eleven salaries have a mean of 68,636.36 and a median of 45,000. Remove the owner and the mean falls by more than 24,000 to 44,500, while the median barely moves, to 44,250. One extreme value dragged the mean far from every actual employee; the median ignored it. The order values from Lesson 13 did the same thing: one bulk order of 7,560 made the mean 753.35 while the median order was 69.00.

That property has a name. The median is robust: it resists outliers, because it depends only on which value sits in the middle, not on how far the extreme values reach. The mean uses every value's size, which is its strength when the data are well behaved and its weakness when they are not.

The mode is the only one of the three that works for categories. The most common jacket size sold last month, M, is a mode; there is no mean of S, M and L. For numbers the mode is less useful: in the salary list every value is different, so there is no mode at all, and for continuous measurements a mode depends on how finely values are rounded.

Choosing the center

The gap between mean and median is also a quick reading of skew. In a right-skewed distribution, with a long tail of large values, the mean sits above the median: incomes, order values and claim amounts usually look like this, which is why the Census Bureau leads with the median. In a left-skewed distribution, with a tail of small values, such as scores on an easy test where most people score near the maximum, the mean sits below the median. NIST's handbook illustrates how the three measures coincide for a symmetric distribution and separate for skewed ones.

Remember: report the median when a few extreme values could mislead, the mean when you need totals or the data are symmetric, and the mode for categories; when mean and median differ a lot, say so, because the gap itself describes the data.

Averages of averages

The manager's next request sounds harmless: what is the average order value across the three stores? Store A averaged 50.00 over 1,000 orders, Store B 80.00 over 100 orders, and the pop-up Store C 120.00 over 20 orders. Averaging the three averages gives 83.33. But the chain's actual average order, total revenue divided by total orders, is 60,400 divided by 1,120, or 53.93.

The simple average of averages gave the pop-up store's twenty orders the same say as Store A's thousand. The fix is a weighted mean: multiply each value by its weight, here the number of orders, add the products, and divide by the total weight. Whenever you combine rates or averages from groups of different sizes, weight them by size, or better, go back to the totals. It is the same lesson Simpson's paradox taught in Lesson 10: group sizes change what pooled numbers mean.

Computing center in four tools

Two practical notes. SQLite has no median function in its standard build; its documentation describes a percentile extension that adds median() but is disabled by default and must be switched on when SQLite is compiled, and Python's built-in sqlite3 module lacks it. When you need a median from SQLite data, pull the column into pandas or use PostgreSQL. And pandas' mode() returns a Series rather than a single value, because a column can have several equally common values; Python's statistics module offers mode and multimode for the same reason.

Common misconceptions

• "Average means mean." In everyday speech, "average" can mean any center. Ask which one a figure is, and say which one you report.
• "The mean is more accurate than the median because it uses every value." Using every value is exactly why one extreme value can drag it away from every real case.
• "You can average the averages." Only if every group is the same size. Otherwise weight by size or return to the totals.
• "Prescriptive analysis is just a more confident prediction." Prediction estimates what will happen; prescription recommends what to do, usually by optimizing over predictions and constraints.
• "Every numeric column has a meaningful mode." In continuous data, most values occur once; the mode depends on rounding and often does not exist.

Putting it together

• Four methods: descriptive (what happened), inferential (what a sample says about a population, with uncertainty), predictive (what is likely), prescriptive (what to do).
• Mean: sum over count; median: the middle value; mode: the most frequent value. The median is robust to outliers; the mode is the only center for categories.
• Right-skewed data have mean above median, as with incomes, which is why the Census Bureau reports a median of $87,460 for 2025.
• Weight averages by group size, or recompute from totals.
• Calc: AVERAGE, MEDIAN, MODE.SNGL, SUMPRODUCT; SQL: AVG and, in PostgreSQL, percentile_cont; pandas: mean, median, mode.

Sources

1. U.S. Census Bureau. (2026, September 15). Income in the United States: 2025 (Current Population Reports P60-289). census.gov
2. NIST/SEMATECH. (2012). e-Handbook of statistical methods, section 1.3.5.1 Measures of location. National Institute of Standards and Technology. itl.nist.gov
3. Diez, D. M., Cetinkaya-Rundel, M., and Barr, C. D. (2019). OpenIntro statistics (4th ed.), sections 1.1 (data and statistics) and 2.1 (mean, median and skew). OpenIntro. openintro.org
4. The Document Foundation. (n.d.). Statistical functions part four (MEDIAN, MODE.SNGL). LibreOffice Calc Help. help.libreoffice.org
5. SQLite. (n.d.). The percentile extension; PostgreSQL Global Development Group. (2026). Aggregate functions, section 9.21. sqlite.org; postgresql.org
6. Python Software Foundation. (n.d.). statistics: Mathematical statistics functions. docs.python.org

Two stores with the same average

Two stores each sold an average of 400 dollars a day over the same week. Store A's seven days were 410, 395, 405, 400, 390, 415 and 385. Store B's were 250, 550, 300, 500, 350, 450 and 400. Same mean, same median, and yet a manager staffing the tills, or an analyst forecasting cash needs, would treat them completely differently. Store A is predictable; Store B swings by 300 dollars from one day to the next.

The center of a distribution, Lesson 16's subject, says where the values sit. Spread says how far they scatter, and objective 3.2 lists measures of dispersion beside measures of central tendency for exactly this reason. Objective 2.3 adds two transformations built on the same ideas, scaling and standardization, which put variables measured in different units on a common footing. This lesson computes every measure for the two stores, step by step, and then adds one unusual day to Store B to see which measures survive it.

Step 1: the range and the interquartile range

The simplest measure of spread is the range, the maximum minus the minimum. Store A's range is 415 minus 385, or 30; Store B's is 550 minus 250, or 300.

The interquartile range (IQR) covers the middle half of the data: the third quartile minus the first, Q3 minus Q1. pandas' quantile([0.25, 0.75]) gives Store A quartiles of 392.5 and 407.5, an IQR of 15, and Store B quartiles of 325 and 475, an IQR of 150. (Quartiles of small data sets depend on the interpolation rule a tool uses; pandas and LibreOffice's QUARTILE.INC both interpolate linearly by default, so they agree here, but expect small differences between tools on small samples.) Because the IQR ignores the top and bottom quarters, it shares the median's robustness, which is why Lesson 13 built outlier fences from it.

Step 2: variance and standard deviation

The range and IQR use only two values each. The variance and standard deviation use every value. The procedure, for Store B:

1. Find the mean: 400.
2. Subtract it from each value to get deviations: -150, 150, -100, 100, -50, 50 and 0.
3. Square each deviation, so negatives do not cancel positives: 22,500, 22,500, 10,000, 10,000, 2,500, 2,500 and 0. Their sum is 70,000.
4. Divide by n minus 1 for a sample: 70,000 / 6 = 11,666.67. This is the sample variance, in squared dollars.
5. Take the square root to return to dollars: about 108.01. This is the sample standard deviation.

The same steps give Store A a variance of 116.67 and a standard deviation of 10.80. Store B's daily sales typically sit about 108 dollars from the mean, Store A's about 11. The standard deviation is the measure most analyses use, because it is in the data's own units and because, as Lesson 18 shows, it governs how precisely a sample estimates a population.

Step 4 hides an exam favourite. Dividing by n gives the population variance, correct when the data are the whole population, such as every day the store has ever been open. Dividing by n minus 1 gives the sample variance, which corrects for the tendency of a sample's spread to understate its population's, and is what you want when the week is a sample of the store's typical behaviour. Tools disagree about the default:

pandas and NumPy default to opposite choices, so the same data can produce two different "standard deviations" in one notebook. State which you used.

In short: the variance averages squared deviations from the mean, the standard deviation is its square root in the data's own units, and sample versions divide by n minus 1.

Step 3: comparing spreads with the coefficient of variation

A standard deviation of 108 dollars is large for a store selling 400 a day and small for one selling 40,000. The coefficient of variation (CV) divides the standard deviation by the mean so spreads can be compared across scales: Store A's CV is 10.80 / 400, or 2.7 percent; Store B's is 108.01 / 400, or 27.0 percent. The CV only makes sense for data measured from a true zero, such as sales or weights, not for temperatures in Celsius, where zero is arbitrary.

Step 4: position with percentiles and z-scores

Measures of position say where one value stands within the distribution. A percentile is the value below which a given percentage of the data falls: Store B's 90th percentile is 520, so about nine days in ten fall below it. Quartiles are the 25th, 50th and 75th percentiles. Percentiles are how growth charts, test scores and service levels are reported ("95 percent of calls answered within 20 seconds").

A z-score measures position in standard deviations from the mean: z = (x minus mean) / standard deviation. Store B's 550-dollar day has z = (550 - 400) / 108.01, about 1.39: it is 1.39 standard deviations above its own average. A 415-dollar day at Store A has z = (415 - 400) / 10.80, also about 1.39. Two very different amounts are, relative to their stores' usual behaviour, equally unusual. That comparability is the whole point.

Step 5: scaling and standardization

Objective 2.3's scaling and standardization apply these ideas to entire columns, usually so that variables in different units, dollars and visits and days, can be compared or fed to a model without the largest-unit variable dominating.

In pandas each is one line: (s - s.min()) / (s.max() - s.min()) and (s - s.mean()) / s.std(). Neither changes the shape of the distribution; both change only its location and scale. As Lesson 14 noted, "standardization" also names format clean-up, so read each scenario for which sense it means.

In short: percentiles and z-scores locate a value within its distribution; min-max scaling maps a column onto 0 to 1, and z-score standardization gives it mean 0 and standard deviation 1.

Change one input: a holiday sale

Now Store B has an eighth day, a holiday sale, with sales of 2,400. Recompute everything and the measures split into two camps.

One day multiplied the standard deviation by more than six and the range by seven, while the IQR rose by a sixth and the median by 25 dollars. The scaled values change even more visibly. Min-max scaling now maps the seven normal days into the narrow band from 0 to 0.14, because the holiday sets the maximum; the differences that mattered before are squeezed almost flat. Z-scores are less dramatic: the holiday becomes 2.45, and the ordinary days bunch between -0.56 and -0.14, because the inflated mean and standard deviation now describe neither kind of day well.

The lesson is a choice of tools. When outliers are possible and genuine, describe spread with the IQR as well as, or instead of, the standard deviation; scale with methods that the outlier does not dominate, or treat the unusual day separately; and always investigate the extreme value first, as Lesson 13 insisted.

Common misconceptions

• "The same mean means the same performance." Stores A and B share a mean of 400 and differ tenfold in spread.
• "Standard deviation and variance are interchangeable." Variance is in squared units; the standard deviation, its square root, is in the data's units and is the one to report.
• "Every tool computes the same standard deviation." pandas defaults to the sample formula, NumPy to the population formula.
• "Min-max scaling removes outliers." It keeps them and lets them set the scale, compressing every other value.
• "A z-score of 2 means twice the mean." It means two standard deviations above the mean.

Where this leaves us

• Range: maximum minus minimum. IQR: Q3 minus Q1, robust to outliers.
• Variance: the average squared deviation, dividing by n minus 1 for a sample; standard deviation: its square root, in the data's units.
• Calc STDEV.S and STDEV.P, pandas std() with ddof=1 by default, NumPy std() with ddof=0 by default.
• Coefficient of variation: standard deviation over mean, for comparing spreads across scales.
• Percentiles and z-scores give position; min-max scaling maps to 0 to 1; z-score standardization gives mean 0 and standard deviation 1. Outliers inflate the standard deviation and range and distort both scalings; the IQR and median resist them.

Sources

1. NIST/SEMATECH. (2012). e-Handbook of statistical methods, section 1.3.5.6 Measures of scale. National Institute of Standards and Technology. itl.nist.gov
2. Diez, D. M., Cetinkaya-Rundel, M., and Barr, C. D. (2019). OpenIntro statistics (4th ed.), section 2.1 (variance, standard deviation, box plots, quartiles and robust statistics) and section 4.1 (z-scores). OpenIntro. openintro.org
3. pandas development team. (2026). pandas.Series.std and pandas.Series.quantile. pandas 3.1.0 documentation. std; quantile
4. NumPy developers. (n.d.). numpy.std. NumPy documentation. numpy.org
5. The Document Foundation. (n.d.). Statistical functions part five (STDEV.S, STDEV.P, VAR.S, VAR.P). LibreOffice Calc Help. help.libreoffice.org

Four dollars and thirty cents

A retailer randomly split 800 loyalty members into two groups. Four hundred received a new promotional email; four hundred did not. Over the next month the email group spent an average of 62.40 dollars each, with a standard deviation of 30.00; the control group spent 58.10, with a standard deviation of 29.00. The difference is 4.30 dollars, and a t-test returns a p-value of 0.0396.

Two analysts read the same output and disagree. Maya says the result is statistically significant, the experiment was randomized, and the email should go to all 48,000 members. Tom says the effect might be almost nothing, that the team quietly tested nineteen other emails this quarter, and that the conversion rate, the metric they originally said they cared about, showed no significant difference at all. Both are doing statistics correctly. Objective 3.2 includes inferential methods, the tools that let a sample speak about a population, and this lesson builds them up until you can judge the dispute yourself.

From a sample to a population: the standard error

Any sample gives a slightly different answer from the next sample would. Inference is about how much. The key quantity is the standard error of a mean, the standard deviation divided by the square root of the sample size: SE = s / √n. It measures how much sample means would vary from sample to sample.

Suppose a random survey of 500 of the 48,000 loyalty members finds a mean monthly spend of 61.80 with a standard deviation of 28.50. The standard error is 28.50 / √500 = 1.27. A 95 percent confidence interval is the sample mean plus or minus a critical value times the standard error. For a sample this size the t critical value is about 1.965, so the interval is 61.80 plus or minus 2.50, or 59.30 to 64.30.

Interpret it carefully. The method that produced this interval captures the true population mean in 95 percent of the samples it is applied to. It is not a 95 percent probability that the true mean lies in this particular interval; the true mean is fixed, and this interval either contains it or does not. In practice: "we estimate average monthly spend at 61.80 dollars, with a 95 percent confidence interval from 59.30 to 64.30."

Sample size enters through the square root. Survey 2,000 members with the same mean and spread and the interval narrows to 60.55 to 63.05: four times the sample buys half the width. That square-root law is why surveys of 1,000 to 2,000 people are common and surveys of a million are not.

The upshot: the standard error, s / √n, measures sampling variability; a 95 percent confidence interval is the estimate plus or minus about two standard errors, and it narrows only with the square root of the sample size.

Hypothesis tests in five steps

A hypothesis test asks whether data are consistent with a specific claim. The NIST/SEMATECH handbook describes the logic, and it runs in five steps.

1. State the hypotheses. The null hypothesis, H0, is the claim of no effect: the email does not change mean spend. The alternative, H1, is that it does.
2. Choose a significance level, alpha, before looking: commonly 0.05.
3. Compute a test statistic that measures how far the data sit from what H0 predicts, in standard errors.
4. Find the p-value: the probability, assuming H0 is true, of a result at least as extreme as the one observed.
5. Decide: if p is below alpha, reject H0; otherwise, fail to reject it, which is not the same as proving it.

Two kinds of mistake are possible, and the exam names them.

The test must fit the outcome. A numeric outcome compared between two groups, such as spend, calls for a two-sample t-test; Welch's version, which does not assume equal variances, is the safe default. A categorical outcome, such as converted or not, calls for a chi-square test of independence on the table of counts, or the equivalent test of two proportions. Before-and-after measurements on the same units call for a paired t-test.

Maya's case for rolling out

Maya's evidence is solid as far as it goes. Welch's t-test on the two groups gives t = 2.06 with a p-value of 0.0396, below the 0.05 the team chose. The 95 percent confidence interval for the difference, 4.30 plus or minus 1.963 times a standard error of 2.09, runs from 0.20 to 8.40 dollars, and it excludes zero. And because members were assigned at random, the groups differ only by the email and by chance, so a real difference is a causal effect of the email, something no observational comparison could claim.

Tom's case for caution

Tom's evidence is just as solid. First, the interval: an effect as small as 20 cents a month is consistent with the data. If the email costs 50 cents per member to produce and send, much of the interval represents a loss. The American Statistical Association's 2016 statement on p-values makes the general point in two of its six principles: business or policy decisions "should not be based only on whether a p-value passes a specific threshold", and a p-value, or statistical significance, "does not measure the size of an effect or the importance of a result".

Second, the other emails. If the team tests twenty emails that truly do nothing, each at alpha 0.05, the chance that at least one shows "significance" by luck is 1 minus 0.95 to the twentieth power, about 64 percent. A single winner among twenty is roughly what chance alone would produce. Third, the metric: the team planned to judge emails by conversion, and 12 percent of the email group bought something against 8 percent of the control group, a difference a chi-square test puts at p = 0.059, not significant at 0.05. Switching to whichever metric crossed the line after seeing the results inflates the false-positive rate in the same way as testing many emails.

What would settle it

Neither analyst can settle the dispute from this data alone, but the steps that would are standard practice.

• Decide the decision rule first. Name the primary metric and the smallest effect worth acting on, for example the break-even spend that covers the email's cost, before the test runs.
• Report the interval, not just the verdict. "Between 0.20 and 8.40 dollars" says far more than "significant".
• Account for multiple tests. The simplest correction, Bonferroni's, divides alpha by the number of tests: 0.05 / 20 = 0.0025, a bar this result does not clear.
• Replicate with enough power. A second randomized test, sized so it would reliably detect the break-even effect, either confirms the email or exposes the first result as luck.

The upshot: a p-value below 0.05 says the data would be surprising if there were no effect. It does not say the effect is large, important or replicable, and it means less when many tests or metrics were tried.

Doing it in your tools

In Python, install SciPy alongside pandas with python -m pip install scipy. Then stats.ttest_ind(email, control, equal_var=False) runs Welch's t-test on two arrays of spend, and stats.chi2_contingency([[48, 352], [32, 368]], correction=False) runs the chi-square test on converted and not-converted counts; by default SciPy applies Yates' continuity correction to 2 by 2 tables, which raises the p-value further, to about 0.077. In LibreOffice Calc, T.TEST returns a t-test's p-value and CONFIDENCE.T the half-width of a confidence interval.

Common misconceptions

• "p = 0.04 means a 4 percent chance the null hypothesis is true." The ASA statement says p-values do not measure the probability that the studied hypothesis is true. A p-value assumes H0 and asks how surprising the data are.
• "A 95 percent confidence interval has a 95 percent chance of containing the true value." The 95 percent describes the method across repeated samples, not this one interval.
• "Not significant means no effect." It means the data could not rule out no effect; a small or underpowered test often misses real effects (a Type II error).
• "Significant means important." With a large enough sample, trivial differences become significant. Look at the effect size and its interval.
• "Testing more ideas costs nothing." Each extra test is another chance for a false positive unless the analysis accounts for it.

Pulling it together

• Standard error = s / √n; a 95 percent confidence interval is about two standard errors either side of the estimate, and quadrupling n halves its width.
• Test in five steps: hypotheses, alpha, test statistic, p-value, decision. Type I is a false positive (rate alpha); Type II a false negative; power is 1 minus beta.
• t-tests compare means of numeric outcomes; chi-square tests compare categorical counts; paired tests compare before and after on the same units.
• Judge practical significance with the effect size and its interval; pre-register the metric; correct for multiple comparisons.

Sources

1. American Statistical Association. (2016, March 7). American Statistical Association releases statement on statistical significance and p-values (press release with the six principles). Published as Wasserstein, R. L., and Lazar, N. A. (2016), The ASA statement on p-values: Context, process, and purpose, The American Statistician, 70(2), 129-133. amstat.org (PDF)
2. NIST/SEMATECH. (2012). e-Handbook of statistical methods, sections 7.1.3 What are statistical tests? and 7.1.4 What are confidence intervals? National Institute of Standards and Technology. tests; intervals
3. Diez, D. M., Cetinkaya-Rundel, M., and Barr, C. D. (2019). OpenIntro statistics (4th ed.), chapters 5 (foundations for inference), 6 (categorical data, including chi-square tests) and 7 (numerical data, including t-tests). OpenIntro. openintro.org
4. SciPy developers. (n.d.). scipy.stats.ttest_ind and scipy.stats.chi2_contingency. SciPy documentation. ttest_ind; chi2_contingency

Four data sets, one wrong conclusion

In February 1973 the statistician Francis Anscombe published four small data sets in The American Statistician, eleven points each, which R still ships as its anscombe data set. Compute their summaries and they are indistinguishable: in every one, x has mean 9 and variance 11, y has mean 7.50 and variance of about 4.12, the correlation is 0.816, and the least-squares line is y = 3.00 + 0.500x. The tempting conclusion is that the four describe the same relationship.

They do not. Plotted, the first is a straight-line cloud, the second a smooth curve, the third a perfect line spoiled by one outlier, and the fourth a vertical stack of ten points with one far-off point that creates the entire correlation. Anscombe's point was that numerical summaries cannot see shape. This lesson starts from that wrong conclusion and from two more that analysts reach every week, and traces where each goes wrong. Along the way it covers objective 3.2's two remaining statistical methods: predictive analysis, with correlation, regression and forecasting, and prescriptive analysis, which turns predictions into decisions.

Correlation: strength and direction, nothing more

The Pearson correlation coefficient, r, measures how closely two numeric variables follow a straight line. It runs from -1, a perfect downward line, through 0, no linear relationship, to +1, a perfect upward line. Its square, r squared, is the share of the variation in one variable that a straight line on the other accounts for. A rank correlation, Spearman's, applies the same idea to the ranks of the values and so measures any steadily increasing or decreasing relationship, curved or not.

Anscombe's quartet already shows the first limit: r measures linear association only, so a strong curve, a single outlier or a vertical stack can produce the same r as a clean line. The second limit is the one the exam tests most often. A correlation between two variables can arise because a third variable drives both. Ice-cream sales and drownings both rise in summer; neither causes the other. In business data, season, store size and customer income are the usual hidden third variables, called confounders.

Wrong conclusion 2: "double the ads, gain 28,000 in sales"

A marketing analyst has ten months of data, advertising spend from 2,000 to 11,000 dollars and monthly sales from 30,000 to 55,000 (both recorded in thousands). Python's statistics module fits the least-squares line in one call, statistics.linear_regression(ads, sales): slope 2.8, intercept 26.8, so predicted sales = 26.8 + 2.8 x ads. The correlation is 0.98 and r squared 0.96. The analyst writes: each extra 1,000 dollars of advertising brings 2,800 dollars of sales, so raising spend from 11,000 to 22,000 will lift monthly sales to 88,400. Trace the reasoning and it fails in three places.

• Causation. The data are observational. If the company advertises most in its busy season, season drives both ads and sales, and the slope mixes the effect of advertising with the effect of the calendar. Only a randomized experiment, Lesson 18's A/B test, or a careful design that holds season constant, can separate them.
• Extrapolation. 22,000 dollars is double the largest spend ever observed. The line describes the data between 2 and 11; nothing in the data says what happens at 22. Predictions outside the observed range rest on the assumption that the straight line continues, which the data cannot check.
• Fit. The residuals, actual minus predicted sales, run -2.4, -1.2, 1.0, 0.2, 2.4, 1.6, 1.8, 0.0, -0.8 and -2.6 from the smallest spend to the largest: negative at both ends and positive in the middle. That arch is the signature of a curve, here diminishing returns, being forced through a straight line. A high r squared did not reveal it; the residual pattern did. The rank correlation is a perfect 1.0, confirming the relationship is consistently increasing but not that it is straight.

The NIST/SEMATECH handbook's guidance on least-squares regression makes the same demand in general form: fit the model, then check it with residual plots before using it. A regression slope is a description of the data you have; turning it into a prediction, let alone a cause, needs more.

Worth holding on to: a regression line summarizes a straight-line pattern in the observed range; check residuals for curves, never extrapolate far beyond the data, and never read a slope from observational data as a causal effect.

Forecasting: predicting over time

Forecasting is predictive analysis on time series, and its simplest methods are surprisingly hard to beat. Rob Hyndman and George Athanasopoulos's free textbook, Forecasting: Principles and Practice, opens its toolbox with four.

These benchmarks matter because any sophisticated model must beat them to justify itself. And all forecasts are judged the same way: hold back the most recent periods, forecast them from the earlier data, and measure the errors, for example the mean absolute error, on the held-back periods only. A model judged on the same data it was fitted to will always look better than it is, the overfitting that Introduction to Data Science (ds-intro) examines with train and test splits.

Wrong conclusion 3: "order exactly the forecast"

Prescriptive analysis asks what to do, and it can disagree with the forecast it starts from. The tent buyer's forecast for November is uncertain: demand of 80, 100, 120 or 140 tents, with probabilities 0.2, 0.3, 0.3 and 0.2, an expected demand of 110. Each tent costs 110 dollars and sells for 189; tents left over at the end of the season are cleared at 60. The tempting answer is to order 110.

Work out the expected profit of each order size instead, averaging over the four demand scenarios:

Of these, 120 is best, more than expected demand, because the two mistakes cost different amounts: a tent short loses 79 dollars of margin, while a tent too many loses only 50 after clearance. The best action depends on the forecast's whole range of outcomes and on the costs of being wrong in each direction, not on the single most likely number. That is the core of prescriptive analysis: decision variables (how many to order), an objective (expected profit), constraints (budget, shelf space) and an optimization or simulation that searches the options. Real versions use linear programming, scheduling and simulation tools, but the structure is the one in this table.

Worth holding on to: predictive analysis estimates what will happen with its uncertainty; prescriptive analysis chooses the action that does best across that uncertainty, given costs and constraints, and the best action need not equal the forecast.

Common misconceptions

• "Identical summary statistics mean identical data." Anscombe's quartet has identical means, variances, correlations and regression lines and four different shapes. Plot first.
• "A correlation of 0.98 proves one variable drives the other." Confounders such as season can produce strong correlations between variables that do not affect each other.
• "A high r squared means the straight line is right." The ads data have r squared 0.96 and a clearly curved residual pattern.
• "A good model can predict well beyond the data it saw." Extrapolation assumes the pattern continues where there is no data to check it.
• "The best order equals the forecast." When shortages and surpluses cost different amounts, the best order shifts toward the cheaper mistake.

Summing up

• Correlation (r) measures linear association from -1 to 1; r squared is the share of variation a line explains; rank correlation measures monotonic association. Correlation does not establish causation.
• Simple linear regression fits y = a + bx by least squares; check residuals for patterns, report the slope as a description of the observed range, and avoid extrapolation.
• Forecasts start from mean, naive, seasonal naive and drift benchmarks and are judged on held-back periods.
• Prescriptive analysis combines predictions, costs and constraints to choose actions; the optimal action can differ from the most likely outcome.

Sources

1. Anscombe, F. J. (1973). Graphs in statistical analysis. The American Statistician, 27(1), 17-21. doi.org
2. R Core Team. (n.d.). anscombe: Anscombe's quartet of 'identical' simple linear regressions, R datasets package documentation. stat.ethz.ch
3. NIST/SEMATECH. (2012). e-Handbook of statistical methods, section 4.1.4.1 Linear least squares regression. National Institute of Standards and Technology. itl.nist.gov
4. Diez, D. M., Cetinkaya-Rundel, M., and Barr, C. D. (2019). OpenIntro statistics (4th ed.), chapter 8 Introduction to linear regression. OpenIntro. openintro.org
5. Hyndman, R. J., and Athanasopoulos, G. (2021). Forecasting: Principles and practice (3rd ed.), section 5.2 Some simple forecasting methods. OTexts. otexts.com
6. Python Software Foundation. (n.d.). statistics: Mathematical statistics functions (correlation and linear_regression). docs.python.org

Thirty-first of January, plus one month

Ask three tools for the date one month after 31 January 2026 and you get two different answers. LibreOffice Calc's EDATE and pandas' DateOffset(months=1) both return 28 February 2026. SQLite's date('2026-01-31', '+1 month') returns 3 March 2026: it moves to the nonexistent 31 February and then rolls the three extra days forward. A subscription renewal report built on one tool and checked on another would disagree on every customer who joined on the 29th, 30th or 31st of a month.

Objective 3.2 ends with functions and measures: mathematical (with the measures of center and spread from Lessons 16 and 17), logical, date and string. These are the workhorses of every analysis, and their pitfalls are less about syntax than about the small differences in how each tool defines them. This lesson builds one customer summary step by step in Calc, SQL and pandas, then changes inputs, a tie in rounding, a missing value, a month-end date, and watches the tools part company. Every SQL and pandas result below was checked in SQLite 3.50 and pandas 3.1.

Step 1: mathematical functions

The everyday mathematical functions are the same everywhere in name: ROUND, ABS for absolute value, MOD for the remainder, POWER and SQRT, and the aggregates SUM, AVERAGE, MIN and MAX. Two behaviours differ.

Rounding ties. Calc's ROUND(2.5; 0) and SQLite's round(2.5) give 3, rounding halves away from zero, as most people learned at school. Python's built-in round(2.5) gives 2, and round(3.5) gives 4: Python rounds halves to the nearest even number, a convention called banker's rounding that avoids a systematic upward drift when many values are rounded. pandas' Series.round() does the same, turning 2.5 and -2.5 into 2.0 and -2.0. The difference is a cent here and there, which is exactly the size of discrepancy that makes two finance reports disagree.

Integer division. In SQLite, and in PostgreSQL for integer columns, 7 / 2 is 3, because dividing two integers discards the fraction; 7 / 2.0 is 3.5. This is why Lesson 10's admission rates multiplied by 1.0 before dividing. 7 % 3, the modulo or remainder, is 1, which is handy for tasks such as putting every third customer in a test group.

Step 2: logical functions

Logical functions return different values depending on conditions. The building blocks are comparisons, AND, OR and NOT, and a conditional:

Order matters in multi-condition logic: CASE, IFS and np.select all stop at the first true condition, so test the most specific case first. A CASE that checks amount >= 100 before amount >= 500 labels every bulk order "large".

Now change one input: an order with no amount. CASE WHEN amount > 100 THEN 'big' ELSE 'small' END labels it "small", because NULL > 100 is unknown, not true, and unknown falls through to ELSE. Your report now counts an order of unknown size as a small one. Write the missing case explicitly, WHEN amount IS NULL THEN 'unknown', or leave out ELSE, in which case the result is NULL and stays visible as missing.

So what?: logical functions evaluate conditions in order and treat NULL as unknown; put specific conditions first and give missing values their own branch, or they will be silently lumped with the ELSE.

Step 3: date functions

Dates are numbers underneath, days since an epoch in a spreadsheet and in SQLite's Julian day numbers, so differences are subtraction. From 2 November 2026 to 25 December 2026 is 53 days: =DATE(2026;12;25)-DATE(2026;11;2) in Calc, julianday('2026-12-25') - julianday('2026-11-02') in SQLite, and (pd.Timestamp("2026-12-25") - pd.Timestamp("2026-11-02")).days in pandas. The common date tasks map like this:

The weekday row deserves a second look: one Monday, three numbers, 2, 1 and 0, because each tool counts from a different day and a different start. A "weekend sales" filter written for one tool and pasted into another silently picks the wrong days. And the month-arithmetic row is this lesson's opening example. For month-end-sensitive work in SQLite, compute the last day of the month explicitly: date('2026-01-31', 'start of month', '+2 months', '-1 day') returns 28 February.

Time zones complete the list of date hazards: a timestamp stored in UTC and displayed in local time can move an order to the previous day in a daily report. Decide which zone defines "the day" for each report, as Lesson 2 advised.

Step 4: string functions

One difference catches everyone moving between tools: where counting starts. SQL and spreadsheets number characters from 1, so substr('TENT-2P', 1, 4) is TENT and instr('TENT-2P', '-') is 5. Python numbers from 0, so 'TENT-2P'[:4] is TENT and 'TENT-2P'.find('-') is 4. Code that extracts "everything after the hyphen" by position must add or subtract one when it is translated. Also note that pandas' str.replace treats the pattern as plain text unless you pass regex=True, the opposite of a regular-expression tool, which matters when the pattern contains a full stop.

Common misconceptions

• "Rounding is rounding." Calc and SQLite round 2.5 to 3; Python and pandas round it to 2. Decide which convention a report uses.
• "7 / 2 is 3.5 in any database." Integer division in SQLite and PostgreSQL gives 3. Make one operand decimal.
• "ELSE catches the leftovers I meant." It also catches NULLs, labelling unknown values as whatever ELSE says.
• "Adding a month always lands in the next month." SQLite rolls 31 January forward to 3 March; Calc's EDATE and pandas' DateOffset stop at 28 February.
• "Weekday numbers are standard." The same Monday is 2 in Calc, 1 in SQLite and 0 in pandas.

Looking back

• Mathematical: ROUND, ABS, MOD, POWER, SQRT and the aggregates; watch rounding ties (half away from zero versus half to even) and integer division.
• Logical: IF, IFS, CASE and np.where or np.select; first true condition wins; NULL is unknown and falls to ELSE.
• Date: subtract for differences; YEAR, MONTH and strftime for parts; EDATE, DateOffset and SQLite modifiers for month arithmetic, which differ at month ends; weekday numbering differs by tool.
• String: length, case, trim, extract, find, replace, join; SQL and spreadsheets count from 1, Python from 0.

Sources

1. SQLite. (n.d.). Date and time functions and Built-in scalar SQL functions. dates; scalar functions
2. The Document Foundation. (n.d.). Date and time functions; Text functions; Logical functions. LibreOffice Calc Help. dates; text; logical
3. pandas development team. (2026). Time series / date functionality and Working with text data. pandas 3.1.0 user guide. time series; text
4. Python Software Foundation. (n.d.). Built-in functions (round). docs.python.org
5. PostgreSQL Global Development Group. (2026). Date/time functions and operators, PostgreSQL 18 documentation, section 9.9. postgresql.org

Three people ask for the same analysis

On a Monday morning in November, three people ask an outdoor retailer's analyst about September's product returns. The chief executive wants to know whether returns are hurting profit. The manager of the Boise store wants to know which products came back and why, so she can brief her staff before the weekend. And the brand that makes the retailer's tents wants evidence for its claim that a new zip is failing. The data is the same 4,000 return records. The right answer is three different products.

Objective 3.1 asks you, given a set of requirements, to determine the appropriate communication approach, and lists what to weigh: mock-ups, accessibility, technical and nontechnical audiences, level of detail, internal and external audiences, user personas such as the C-suite and individual contributors, sensitive and non-sensitive data, and key performance indicators. This lesson works the returns problem through each of them in turn.

Start from requirements, not from the data

The tempting first move is to open the returns table and start charting. The better first move is ten minutes of questions, because the same table supports very different products. Ask each requester:

The answers decide everything that follows. Notice that none of them is about charts yet.

Key performance indicators: the few numbers that matter

A key performance indicator (KPI) is a measure tied directly to a goal, tracked over time against a target. The UK Government Digital Service gives a concrete example: its Service Manual requires government services to publish four mandatory KPIs, cost per transaction, user satisfaction, completion rate and digital take-up. Each one answers a question the organization cares about, and each is defined precisely enough that two analysts would compute the same number.

Precision is where KPIs usually fail. "Return rate" sounds obvious until you ask: returns divided by orders or by items? Counted in the month of the sale or the month of the return? Including exchanges? A well-defined KPI states its numerator, its denominator, its period, its target, its owner and how often it refreshes. Defined that way, the chief executive's KPI might be "items returned within 30 days of purchase, divided by items sold, by month of sale; target under 6 percent; owner, head of customer service; refreshed monthly".

KPIs belong to the audience's decisions. The chief executive thinks in the profit and loss statement, often shortened to P&L, the income statement that, in the US Securities and Exchange Commission's description, deducts costs from revenue to reach the bottom line; so his KPI set pairs the return rate with its cost. The store manager needs operational measures she can act on this week, such as returns per hundred sales by product. A measure that looks impressive but changes no decision, such as total page views of the returns page, is a vanity metric, and it crowds out the ones that matter.

The core of it: a KPI is a precisely defined measure tied to a goal, with a target and an owner; choose a handful that drive the audience's decisions.

Personas and level of detail

The objectives contrast two user personas, the C-suite and the individual contributor, and their needs pull in opposite directions.

Technical and nontechnical audiences differ in the same way. Engineers at the tent maker want the defect counts by production batch, the definitions behind them and the raw data to test their own hypotheses; a statistically worded caveat is welcome. The chief executive wants the conclusion and the confidence in it, in one sentence: "Returns rose to 7.2 percent, costing about 41,000 dollars, mostly the TENT-2P zip; we are fairly sure of the cause." Same evidence, different translation. Lesson 25 turns these choices into executive summaries and dashboards.

Internal and external, sensitive and non-sensitive

Before anything leaves the analytics team, decide what each audience may see. Internal audiences can usually see more, but not everything: the store manager needs her store's returns, not every store's staff discounts. External audiences, such as the tent maker, see only what an agreement allows.

• Remove what is not needed. The tent maker needs defect counts and failure descriptions, not customers' names, emails or addresses, which are personal data (Lesson 29).
• Aggregate. Totals by product and week answer the question without exposing individual transactions.
• Suppress small numbers. A count of one return from a small town can identify a customer; statistical offices routinely hide or combine very small cells for this reason.
• Check classification and agreements. Commercially sensitive data, such as other suppliers' defect rates or the retailer's margins, stays internal unless a contract says otherwise. Lesson 28 covers classification.

Mock-ups: agree the design before you build it

A mock-up is a sketch of the finished report or dashboard, built quickly so the audience can react before the real work starts: where each KPI sits, which filters exist, what a drill-down shows. It can be pencil on paper, a slide, or a wireframe in a diagramming tool; CompTIA's own sample software list for Data+ names Lucid, PowerPoint and Visio as mock-up tools. The point is cheapness. Moving a box on a sketch takes seconds; rebuilding a dashboard after it ships takes days, and an executive who first sees a design on launch day will often ask for exactly the changes a ten-minute mock-up review would have caught.

Good mock-up reviews ask the requester to use the sketch: "Show me where you would look to decide whether to change the returns policy." If they cannot find it, the design is wrong, however good the data.

Accessibility from the start

An analysis that some of its audience cannot read has failed. In the UK, the Government Analysis Function's guidance reminds public-sector analysts that the Public Sector Bodies Accessibility Regulations 2018 require their web content to meet the A and AA success criteria of the Web Content Accessibility Guidelines (WCAG) 2.2, and its advice applies to any organization:

• give every chart a text alternative or an accessible data table that states its message;
• never rely on colour alone to carry meaning, and check the contrast of text and lines against their background;
• use plain language and short sentences, and put titles and sources in text, not inside images;
• publish tables as real tables, not pictures of tables.

Accessibility is cheaper as a requirement than as a repair, which is why it belongs in the first conversation. Lesson 24 applies these rules to chart design.

The core of it: settle audience, decision, detail, sensitivity and accessibility before building; mock up the design and test it with the people who will use it.

Common misconceptions

• "More detail is always more helpful." Executives need the answer and its size; line-level detail belongs in drill-downs for the people who act on it.
• "Any important number is a KPI." A KPI is tied to a goal, precisely defined, has a target and an owner, and drives decisions. Most numbers are not KPIs.
• "Internal reports can include everything." Internal audiences still see only what their role requires, and personal data stays protected.
• "Mock-ups waste time when the data is ready." They are the cheapest point at which to discover that the design answers the wrong question.
• "Accessibility can be added at the end." Retrofitting colour schemes, text alternatives and structure costs more than designing them in.

What to remember

• Gather requirements first: decision, level of detail, frequency, audience type, sensitivity and access needs.
• KPIs are precisely defined measures tied to goals, with targets and owners; GOV.UK's four mandatory KPIs are an example of a small, decision-focused set.
• C-suite personas need summaries, KPIs and plain language; individual contributors need detail, filters and technical precision.
• External and sensitive outputs need minimization, aggregation, small-number suppression and agreements.
• Mock up before building, and design for accessibility (WCAG 2.2 A and AA) from the first conversation.

Sources

1. Government Digital Service. (2021). Data you must publish (Service Manual: Measuring success). GOV.UK. gov.uk
2. Government Analysis Function. (2020). Making analytical publications accessible. Office for National Statistics. analysisfunction.civilservice.gov.uk
3. World Wide Web Consortium. (2023). Web Content Accessibility Guidelines (WCAG) 2.2. W3C Recommendation. w3.org
4. U.S. Securities and Exchange Commission. (n.d.). Beginners' guide to financial statements (income statements). sec.gov
5. CompTIA. (2024). CompTIA Data+ DA0-002 certification exam: Exam objectives (Version 1.1), objective 3.1 and the hardware and software list. CompTIA (PDF)

Tuesday's sales were zero

At 8:05 on a Wednesday the sales dashboard shows zero revenue for Tuesday across all forty stores. Within ten minutes the regional director has emailed the chief executive: "Tuesday was a disaster." It was not. The stores sold as usual. At 2:00 that morning, the overnight job that copies sales into the warehouse failed to connect, because the service account's database password had expired the day before. The dashboard drew the missing day as a zero. The plausible wrong answer, a collapse in sales, took ten minutes to spread; the real cause, a credential, took an analyst forty minutes to find.

Objective 3.3 asks you, given a scenario, to troubleshoot basic issues with the appropriate tool or method. It names four kinds of issue, connectivity-related, user-reported, basic SQL code and corrupted data, and three tools and methods: enabling logging, validating the data source, and consulting vendor communities and online resources. This lesson starts from wrong answers like Tuesday's and traces each to its cause.

A method before a fix

CompTIA teaches the same troubleshooting method across its certifications, and Network+ objective 5.1 spells it out in seven steps. Identify the problem, establish a theory of probable cause, test the theory, and establish a plan of action. Then implement the solution or escalate, verify full system functionality, and document what you found. Network+ Certification Prep (cs-netplus) teaches it in full; for an analyst, three habits from it matter most.

• Identify before theorizing. What exactly is wrong, since when, for whom? "Tuesday shows zero for every store, and only Tuesday" points straight at a load failure, not at the stores.
• Question the obvious first. Was anything changed: a password, a schema, a server, a file format? Most breakages follow a change.
• Verify and document. After reloading Tuesday, check its total against the stores' own till reports, then write down cause and fix so the next analyst finds it in minutes.

Connectivity-related issues

A connection from a notebook, a BI tool or a pipeline to a database passes through several layers, and each fails with its own symptom.

Work from the bottom up: if the host is unreachable, no password will help. And read the error message literally. "Authentication failed for user" and "connection refused" describe different layers; the first means the server answered and rejected you, the second that nothing answered on that port.

User-reported issues

"The numbers look wrong" is the most common report and the least specific. Treat it as the start of the identify step, not a conclusion. Ask which report, which figure, what the user expected and why, and when it last looked right. Then reproduce it yourself with the same filters. A surprising share of reports dissolve at this stage: a filter left on another region, a cached copy of last week's page, a different definition of "sales" in the user's head. The rest become precise problems you can test, such as "the September return rate on the Boise page is 7.2 percent but the store's own count gives 5.9 percent", which is a definitional or data question you can now trace.

Basic SQL code: the loud errors and the silent ones

Some SQL errors stop the query with a message, and the messages are good. These come from SQLite on Lesson 9's shop database:

The dangerous errors produce no message. SELECT order_id customer_id FROM orders, with the comma missing, runs happily and returns seven rows of order IDs under a column named customer_id, because SQL reads the second word as an alias. WHERE c.city = NULL returns no rows rather than an error. An INNER JOIN silently drops orphans, an integer division silently returns 0, and a bare column in a grouped query silently returns an arbitrary value, the traps of Lessons 9, 10 and 20. The defence is to test results, not just code: check row counts against expectations, total a known period and compare it with a trusted figure, and spot-check a few rows by hand.

Corrupted data

Data can be damaged in transit or storage, and the damage often looks like data. Three checks catch most of it.

• Encoding. A file written in UTF-8 and read as Windows-1252 turns "Zoë Martin" into "Zoë Martin". The characters are not corrupted on disk; they are misread. Re-import with the correct character set, as Lesson 3 described.
• Completeness of the transfer. A truncated download or export has fewer rows than the source and often a broken last line. Compare row counts with the source system, and compare file hashes: a SHA-256 checksum computed by the sender and the receiver, with certutil -hashfile file SHA256 on Windows, sha256sum on Linux or Python's hashlib, must match exactly.
• Database integrity. SQLite's PRAGMA integrity_check scans a database file for low-level corruption and returns "ok" when it finds none; server databases have their own consistency checks, run by administrators.

The tools: logging, source validation and the community

Enable logging. Tuesday's failure would have been found at 2:01, not 8:45, if the overnight job had logged its connection attempt and alerted on failure. In Python the standard logging module records timestamped messages at levels from DEBUG to CRITICAL with one line of setup. In PostgreSQL, settings such as log_min_duration_statement record every statement slower than a threshold, which is how slow report queries are found. Log what ran, when, with how many rows, and every error.

Validate the data source. Before blaming the report, confirm the source: did the load finish, how many rows arrived compared with yesterday and with the source, did the schema change (a renamed column, a new code value), and does a known total reconcile? Validation turns "the numbers look wrong" into "the load of 2 November contained 0 rows".

Consult vendor communities and online resources. Error messages are searchable, and someone has usually met yours. Official documentation, release notes, knowledge bases, FAQs and forums run by vendors and users are legitimate tools. Two cautions apply. Never paste confidential data, credentials or customer records into a public forum or an external AI tool. And treat any answer as a theory to test against your own system, not a fix to apply blindly.

Bottom line: troubleshoot in order: identify, theorize, test, fix, verify and document. Check connectivity layer by layer, distrust SQL that runs but has not been checked against known results, and use logs, source validation and documentation as your instruments.

Common misconceptions

• "A zero in a report means nothing happened." It often means data did not arrive. Check the load before announcing a collapse.
• "If the query runs, it is correct." A missing comma, a NULL comparison or an inner join can return plausible wrong results with no error.
• "Garbled names mean the database is corrupt." Usually the file was read with the wrong encoding; re-import before restoring backups.
• "Connection errors are all the same." Unreachable host, refused port and failed authentication point to different layers.
• "Posting the error with the data gets faster help." It may expose confidential data. Share the message and a minimal, anonymized example.

The takeaway

• Follow the method: identify, theorize, test, plan, implement or escalate, verify, document.
• Connectivity: network and VPN, host and port (PostgreSQL 5432 by default), server status, credentials and permissions, drivers and settings.
• User reports: clarify, reproduce, then trace.
• SQL: read error messages literally; test results against known totals to catch silent errors.
• Corruption: check encodings, row counts, checksums and database integrity.
• Tools: logging, source validation, documentation and communities, without sharing sensitive data.

Sources

1. CompTIA. (2023). CompTIA Network+ N10-009 certification exam objectives (Document version 6.0), objective 5.1 troubleshooting methodology. CompTIA (PDF)
2. Python Software Foundation. (n.d.). Logging HOWTO and hashlib: Secure hashes and message digests. logging; hashlib
3. PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation: 19.3 Connections and authentication; 19.8 Error reporting and logging. connections; logging
4. SQLite. (n.d.). PRAGMA statements (integrity_check) and Result and error codes. pragmas; error codes
5. Microsoft. (n.d.). certutil (Windows commands reference). Microsoft Learn. learn.microsoft.com

A table that chooses the chart

The UK Government Analysis Function, the professional body for government statisticians and analysts, publishes guidance on charts that begins not with chart types but with questions. Its table pairs each kind of relationship in the data with the charts that show it. A distribution such as population by age suits a bar chart, a box plot or a dot plot. A time series such as price inflation suits a line chart, and a ranking of schools suits a sorted bar chart. A correlation between weight and height suits a scatterplot, and geographical clusters of disease suit a map. Part-to-whole shares suit a bar chart, a pie or a treemap, and flows of trade between countries suit a Sankey diagram.

That order, question first and chart second, is the whole skill objective 4.1 tests when it asks you, given a scenario, to use the appropriate visual elements. Its list of types is short, charts, maps, pivot tables and infographics, and each covers a family of choices. This lesson lays the families side by side, then reads the comparison for the reasons behind each choice. Lesson 24 handles the design elements that make any of them readable.

Charts by question

Read down the third column and one idea repeats: a good chart encodes the important comparison in something the eye judges accurately. William Cleveland and Robert McGill tested that idea experimentally in 1984 and ranked the ways charts encode numbers by how accurately people decode them. Position along a common scale came first, then length, with angle and area well behind and colour shading near the bottom. Bar charts and dot plots use position and length; pie charts use angle and area. That is why a bar chart of five market shares is easier to read precisely than a pie of the same five.

Pie charts are not banned, but the Analysis Function limits them: use one only when there are five categories or fewer and the categories sum to a meaningful whole. Remember too that pies are hard to read when magnified, which matters to readers with low vision. The guidance also advises against dual-axis charts, which are easily misread, and against decoration such as 3D shapes, shadows and heavy gridlines. And it says never to break the numerical axis of a bar chart, because readers judge bars as proportional to each other.

Key idea: pick the chart from the question, comparison, time, distribution, relationship, share, flow or place, and prefer encodings readers judge accurately: position and length before angle and area.

Maps: rates, not raw counts

Maps are the right choice when location is part of the answer: where customers live, which regions are growing, where deliveries are late. The famous early example is John Snow's map of cholera deaths in London's Soho in 1854, which clustered the deaths around one water pump and supported his argument that cholera spread through water. Analysts meet three kinds of map most often.

• Choropleth maps shade areas, such as states or postal districts, by a value. They are the most common and the most misused: shading by a raw count mostly maps where people live, so a choropleth should usually show a rate, such as returns per 1,000 orders or customers per 10,000 residents.
• Point and proportional-symbol maps place a dot or a sized circle at each location, such as each store sized by revenue, which avoids the impression that large, empty areas matter most.
• Heat maps of density shade a continuous surface where events cluster, such as delivery failures across a city.

Maps add their own hazards: large rural areas dominate the eye even when few people live there, and the projection distorts sizes far from the equator. When the geography is not essential to the question, a sorted bar chart of the same regions is often clearer.

Pivot tables: summaries you can rearrange

A pivot table summarizes a flat table by grouping rows and columns and aggregating a value, and lets the reader rearrange the grouping without writing formulas. It is a GROUP BY you can drag. LibreOffice Calc's help describes the steps: put the cursor in a range with headings, choose Insert, then Pivot Table, confirm the current selection as the source, then drag the field buttons into the Row Fields, Column Fields, Data Fields and Filters areas.

From Lesson 9's shop data, order lines summed by product category and sales channel give this pivot table:

In pandas the same table is lines.pivot_table(index="category", columns="channel", values="line_total", aggfunc="sum", margins=True), where margins adds the totals. Pivot tables are ideal for exploration and for readers who want to slice the numbers themselves; for a fixed message to an executive, a chart of the one comparison that matters usually communicates better than a grid of numbers.

Infographics: charts plus story

An infographic combines a few charts, key numbers, icons and short text into a single designed page that tells one story, usually for a general or public audience: a city's annual report, a health campaign, a summary of a survey for customers. Florence Nightingale's diagrams of deaths in the Crimean War army hospitals, published in the late 1850s, are often cited as early examples: designed to persuade officials, not just to inform statisticians.

The strength of an infographic is attention and memory; its risk is decoration that outweighs the data. Every number on it should be sourced, every chart should obey the rules above, and icons should never be sized to show quantities, since readers judge area poorly. For internal analytical audiences, a plain chart and a sentence usually serve better.

Reading the comparison

Put the four visual types side by side and their audiences sort them. Charts answer one question for anyone, quickly. Maps answer "where", and only when place matters. Pivot tables serve readers who will explore and need exact figures. Infographics serve broad audiences who need one memorable story. The exam's scenarios usually describe the audience and the question; the right visual follows from both.

Key idea: charts for a single comparison, maps when location is the point and rates are shown, pivot tables for exploring exact summaries, infographics for one story told to a broad audience.

Common misconceptions

• "Pie charts are the natural choice for shares." Use a pie only for five or fewer parts of a meaningful whole; a sorted bar chart is usually easier to read.
• "A map makes any regional data clearer." Raw-count choropleths mostly show population; map rates, or use a bar chart when geography is incidental.
• "A histogram is a bar chart." A histogram's bars are bins of a continuous variable that touch; a bar chart compares separate categories.
• "Starting a bar axis above zero just zooms in." Bars are read as proportional lengths, so a truncated axis exaggerates differences.
• "Infographics are more rigorous because they are designed." Design attracts attention; it does not replace correct, sourced numbers and honest encodings.

Recap

• Choose by question: bars for comparison, lines for time, histograms and box plots for distribution, scatter and bubble charts for relationships, stacked bars or small pies and treemaps for parts of a whole, waterfalls for steps, heat maps for matrices, maps for place.
• Cleveland and McGill: position and length are read more accurately than angle, area and colour.
• UK Analysis Function guidance: pies only for five or fewer meaningful parts; no dual axes, 3D or broken bar axes.
• Choropleths should show rates; symbol maps avoid the empty-area effect.
• Pivot tables group and aggregate interactively (Calc's Insert, Pivot Table; pandas pivot_table); infographics tell one sourced story to a broad audience.

Sources

1. Government Analysis Function. (n.d.). Data visualisation: charts. Office for National Statistics. analysisfunction.civilservice.gov.uk
2. Cleveland, W. S., and McGill, R. (1984). Graphical perception: Theory, experimentation, and application to the development of graphical methods. Journal of the American Statistical Association, 79(387), 531-554. doi.org
3. The Document Foundation. (n.d.). Creating pivot tables. LibreOffice Calc Help. help.libreoffice.org
4. pandas development team. (2026). pandas.pivot_table. pandas 3.1.0 documentation. pandas.pydata.org
5. NIST/SEMATECH. (2012). e-Handbook of statistical methods, section 1.3.3.26 Scatter plot. National Institute of Standards and Technology. itl.nist.gov
6. Snow, J. (1855). On the mode of communication of cholera (2nd ed.). John Churchill.

One reader in twelve

The US National Eye Institute estimates that about 1 in 12 men has a colour vision deficiency, most often difficulty telling red from green. In a meeting of twenty-four men, two may see the red and green lines of a "target versus actual" chart as two muddy lines of the same colour. Add the colleague who prints the deck in black and white and the director reading it on a phone in sunlight, and the chart's design, not its data, decides whether the message arrives.

Objective 4.1 lists four design elements, labels, legends, branding and colour schemes, and objective 3.1 adds accessibility. This lesson takes one default chart, the kind any spreadsheet produces in two clicks, and redesigns it step by step. Then it changes one input, the reader, and checks whether the design survives.

The starting point

The chart shows monthly returns per 1,000 items sold for four product categories over a year. As first produced it has the title "Chart 1" and a vertical axis labelled only with numbers. A legend at the right lists the categories in alphabetical order. Four lines use the default colours, including a red and a green, over heavy gridlines and a shaded background. Nothing is wrong with the data. Everything is wrong with the reading: the viewer must work out what the chart is about, what the numbers measure, which line is which and why any of it matters.

Step 1: titles and labels that say something

Replace the label title with the message: "Tent returns doubled after the zip change in August", with a subtitle giving the measure and period: "Returns per 1,000 items sold, by month, November 2025 to October 2026". A reader who sees only the title now has the finding; the chart supplies the evidence.

Then label the axes with units, format numbers for reading (thousands separators, percentages with a % sign, no more decimals than the data can support), and keep the value axis of any bar chart starting at zero. Finish with a note line under the chart: the data source, its date and any definition the reader needs, such as "returns within 30 days of purchase". The UK Government Analysis Function's chart guidance asks for titles and sources to sit in text near the chart rather than inside an image, so screen readers and search can find them.

Step 2: legends or direct labels

A legend makes readers look away from the data, find a colour, and look back, for every line. With four lines that is tolerable; with eight it fails. The usual improvement is to label each line directly at its end with the category name, in the line's own colour. When a legend is necessary, for example on a stacked bar chart with many small segments, put it close to the data and order it as the data appear. Drop it entirely when there is only one series.

Step 3: a colour scheme with a job

Colour should encode something, and the right scheme depends on what.

For the returns chart the message is about tents, so a highlight scheme works best: tents in dark blue, the other three categories in light grey, each labelled directly. The UK Analysis Function publishes tested palettes for exactly these cases, categorical, sequential and focus, and recommends a single colour for simple bar charts with nothing to highlight. Two further rules keep colour honest. Use the same colour for the same category across every chart in a report. And prefer palettes designed to vary in lightness as well as hue, such as the ColorBrewer schemes or matplotlib's viridis, which stay distinguishable in greyscale.

Step 4: branding without losing the data

Most organizations have a style guide: fonts, logo placement, corporate colours, sometimes a template for every chart. Branding builds trust and recognition, and the exam treats it as a legitimate design element. Its limit is legibility. A brand palette of three similar blues may suit a logo and fail as a categorical scheme; a red brand colour used for "good" news confuses readers who expect red to mean a problem. Apply branding to the frame, fonts, titles and logo, and choose data colours for distinguishability, using brand colours only where they pass the same tests. Red-amber-green (RAG) status colours, common in corporate reporting, need particular care: add text or symbols such as "On track" or a tick, because red and green are the pair that colour-deficient readers most often confuse.

Step 5: accessibility checks

The Web Content Accessibility Guidelines, WCAG 2.2, turn these concerns into testable criteria, and UK public bodies must meet its A and AA levels.

• Do not rely on colour alone (success criterion 1.4.1). Direct labels, line styles, markers or patterns must carry the distinction too.
• Contrast for text (1.4.3): at least 4.5 to 1 against the background, or 3 to 1 for large text. Light grey labels on white often fail.
• Contrast for graphics (1.4.11): lines, bars and other graphical objects needed to understand the chart need at least 3 to 1 against adjacent colours.
• Text alternatives (1.1.1): give the chart's message in text, and offer the data as a real table for readers who cannot see the chart.

Two practical tests catch most failures before anyone else does: view the chart in greyscale, and run its colours through a colour-vision-deficiency simulator or a contrast checker. If the message survives both, most readers will receive it.

The point: state the message in the title, label directly, and give colour a single job. Apply branding to the frame rather than at the data's expense, and test contrast, greyscale and text alternatives before publishing.

Change the reader

Now hand both versions to a reader with red-green colour vision deficiency, or print them in black and white. In the original, the red tents line and the green clothing line become indistinguishable, the legend cannot be matched to the lines, and the chart says nothing. In the redesign, the tents line is dark against pale grey lines, every line is labelled by name at its end, and the title states the finding in words. The data did not change; only the reader did, and only one design still works.

In matplotlib, the redesign takes a few lines. Plot the other categories with color="lightgrey", and the tents with a dark colour and a thicker line. Place labels with ax.text at each line's last point, and set the title and axis label with the message and units. Finally, remove the top and right borders with ax.spines[["top", "right"]].set_visible(False).

Common misconceptions

• "A title is just a label." A title that states the finding does half the communicating for readers who look at nothing else.
• "More colours make a chart more informative." Each colour asks the reader to decode something. Use colour for one job, and grey for context.
• "Brand colours are always the right data colours." They may be too similar to distinguish, or carry the wrong meaning; test them like any palette.
• "Red and green are the natural pair for bad and good." They are the pair most often confused by colour-deficient readers; add labels or symbols.
• "Accessibility means a separate version." Most fixes, direct labels, contrast, text alternatives, improve the one chart for every reader.

What to carry forward

• Title with the message, subtitle with measure and period, labelled axes with units, a source note; bar axes start at zero.
• Prefer direct labels; when a legend is needed, place it near the data in the data's order.
• Colour schemes: categorical for groups, sequential for ordered values, diverging around a midpoint, highlight for the series that matters; keep colours consistent across charts.
• Branding belongs to the frame; data colours must stay distinguishable. RAG colours need text or symbols too.
• WCAG 2.2: not colour alone (1.4.1), text contrast 4.5:1 (1.4.3), graphics 3:1 (1.4.11), text alternatives (1.1.1). About 1 in 12 men has a colour vision deficiency; test in greyscale.

Sources

1. National Eye Institute. (n.d.). Color blindness. National Institutes of Health. nei.nih.gov
2. Government Analysis Function. (2026). Data visualisation: colours (updated 12 February 2026) and Data visualisation: charts. Office for National Statistics. colours; charts
3. World Wide Web Consortium. (2023). Web Content Accessibility Guidelines (WCAG) 2.2 and Understanding success criterion 1.4.3: Contrast (minimum). WCAG 2.2; contrast
4. Matplotlib development team. (n.d.). Choosing colormaps in Matplotlib. matplotlib.org
5. Harrower, M., and Brewer, C. A. (2003). ColorBrewer.org: An online tool for selecting colour schemes for maps. The Cartographic Journal, 40(1), 27-37. Tool: colorbrewer2.org

One finding, delivered four ways

The returns analysis from Lesson 21 found its answer on a Tuesday: tent returns had doubled since a zip supplier changed in August, costing about 41,000 dollars a quarter. Over the following month that one finding was delivered four times, in four forms, to four audiences. The chief executive got a one-page summary that Thursday, and store managers a weekly dashboard. The finance team got a governed data set in a self-service portal, and the auditors a frozen month-end snapshot. Each form was right for its audience and wrong for the others.

Objective 4.2 asks you, given a scenario, to use the appropriate delivery or consumption method. It names executive summaries; self-service portals; dashboards, static or dynamic, recurring or ad hoc; and data versioning techniques, snapshot or real-time. This lesson follows the returns finding through each.

Thursday: the executive summary

The chief executive gets one page, and its first sentence carries the conclusion: "Tent returns doubled after the August zip change and are costing about 41,000 dollars a quarter; we recommend switching back to the previous supplier." Readers of executive summaries often read only that sentence, so it must contain the finding, its size and the decision. The rest supports it:

1. The conclusion and recommendation, in one or two sentences.
2. Three supporting points, each a fact with a number: the return rate before and after, the cost, the evidence that the zip is the cause.
3. One chart that shows the main point, designed as in Lesson 24.
4. Risks and confidence: what could make the conclusion wrong and how sure the analysis is.
5. Next steps, with owners and dates.
6. A data note: source, period, definitions and who to contact.

The order is the opposite of how analysis is done. Analysts work from data to method to result; executives need result, then decision, then just enough evidence to trust it. An executive summary that walks through the method first loses its reader before the finding arrives.

Weekly: the store managers' dashboard

Store managers need to watch returns every week, so they get a dashboard: a single screen of the few measures they act on, refreshed on a schedule. Stephen Few's often-quoted definition describes a dashboard as a visual display of the most important information needed to achieve one or more objectives, arranged on a single screen so it can be monitored at a glance. The design choices the exam tests are two pairs.

The managers' dashboard is dynamic and recurring: each Monday it refreshes with the previous week's data, and each manager filters to her own store. Some recurring outputs are better static, such as a regulatory report that must look identical each month, and Microsoft's SQL Server Reporting Services (SSRS), on the exam's acronym list, has long produced exactly such paginated, printable reports. Its documentation now notes that from SQL Server 2025, Microsoft is consolidating on-premises reporting under Power BI Report Server and will release no new versions of SSRS, another product name the objectives preserve after the market moved on.

Why this matters: match the delivery form to how the audience will use it. Send a summary for a decision and a dynamic dashboard for exploration, a static report for identical repeats and an ad hoc output for a one-time question.

On demand: the self-service portal

The finance team does not want a finished report; it wants the cleaned returns data so its analysts can build their own views. A self-service portal publishes governed data sets, and often dashboards and reports, in one place where authorized users find and use them without waiting for the analytics team. Examples include a Power BI app or workspace, a Tableau site and an internal data catalog. Government open-data portals such as data.gov do the same for the public.

Self-service scales an analytics team's reach, and it needs governance to stay trustworthy:

• Certified data sets with documented definitions, so everyone's "return rate" means the same thing (Lesson 27).
• Access control, including row-level restrictions, so each user sees only permitted data (Lesson 29).
• Clear refresh information, so users know how current the data is.
• Training and a contact, because users who misunderstand a field will publish wrong numbers with confidence.

Month end: snapshots and real-time data

The auditors' requirement is different again: they need the September figures exactly as they stood when the books closed, even after late returns arrive in October. That is a snapshot, a point-in-time copy of the data, frozen and labelled with its date. The opposite technique is real-time delivery, where every view queries the current data.

Power BI makes the trade-off concrete. A semantic model in Import mode copies data from its sources into the model, a snapshot that is refreshed on a schedule or on demand. Microsoft's documentation limits models on shared capacity to eight scheduled refreshes a day, and models on Premium, Premium Per User or Fabric capacity to up to 48. Models in DirectQuery mode import nothing: they query the underlying source with every user interaction. The data is then as current as the source, at the cost of speed and load on the source system.

The deciding question is what "current" must mean for the decision. An executive reviewing a quarter needs stable numbers that will not shift during the meeting; a warehouse supervisor routing pickers needs the stock level now. Whatever the choice, show it on the output: "Data as of 1 November 2026, 06:00" turns a confusing discrepancy into an obvious one.

Why this matters: snapshots give speed and reproducibility, real-time gives currency at a cost; label every output with the time its data represents.

Common misconceptions

• "An executive summary summarizes the method." It leads with the conclusion and decision; method belongs in an appendix.
• "Dynamic dashboards are always better." Readers who need the same view each time, or who print, are better served by a static report.
• "Real-time data is always more accurate." It is more current, but numbers shift during review and audits need fixed snapshots.
• "Self-service means no analytics team is needed." It moves the team's work to curating, documenting and securing data sets.
• "A scheduled refresh can run as often as I like." Platforms impose limits, such as eight a day on Power BI shared capacity.

What you now know

• Executive summaries lead with the conclusion, size and recommendation, then evidence, one chart, risks, next steps and a data note.
• Dashboards are static or dynamic and recurring or ad hoc; choose by how the audience uses them.
• Self-service portals publish governed data sets and reports for users to explore, with certification, access control and refresh information.
• Snapshot delivery copies data at a point in time and refreshes on a schedule (Power BI Import: eight scheduled refreshes a day on shared capacity, 48 on Premium); real-time delivery queries the source on every interaction (DirectQuery).
• SSRS produced paginated reports; from SQL Server 2025 Microsoft consolidates on-premises reporting under Power BI Report Server.

Sources

1. Microsoft. (n.d.). Data refresh in Power BI and Storage mode in Power BI Desktop. Microsoft Learn. refresh; storage mode
2. Microsoft. (n.d.). Reporting Services consolidation FAQ. Microsoft Learn. learn.microsoft.com
3. Few, S. (2006). Information dashboard design: The effective visual communication of data. O'Reilly Media.
4. U.S. General Services Administration. (n.d.). Plain language guides (the Plain Writing Act of 2010 and writing for your audience). Digital.gov. digital.gov
5. U.S. General Services Administration. (n.d.). Data.gov, the US government's open data portal. data.gov

An average that left out five countries

In 2010 the economists Carmen Reinhart and Kenneth Rogoff published an influential finding: countries whose public debt exceeded 90 percent of GDP had, on average, slightly negative growth. In April 2013 Thomas Herndon, Michael Ash and Robert Pollin, of the Political Economy Research Institute at the University of Massachusetts Amherst, published a replication. They reported that a coding error in the working spreadsheet "entirely excludes five countries, Australia, Austria, Belgium, Canada, and Denmark, from the analysis", and that the spreadsheet error, together with selective exclusion of available data and unconventional weighting, had changed the answer: properly calculated, average growth for the high-debt countries was 2.2 percent, not minus 0.1 percent.

A range in a formula stopped five rows short. Nobody who read the published table could see it; only someone who reopened the calculation could. Objective 4.3 asks you, given a scenario, to troubleshoot issues using report validation techniques. Its issues are excessive load time, slow refresh rate, large data size, a filter not working correctly, stale data and corrupt data; its techniques are data filtering, review of code, calculations and by peers, source validation, attention to data structure changes, and monitoring alerts. This lesson starts from four wrong conclusions about one dashboard and traces each to its cause.

Wrong conclusion 1: "the dashboard is slow, so we need a bigger server"

The returns dashboard takes 45 seconds to open, and someone has asked for more hardware. Trace the load first. The model imports every column of five years of order lines, about forty million rows, including free-text notes nobody displays, and the opening page draws fourteen visuals, each sending its own query.

The cause is large data size and too much work per view, and the first fix is data reduction, not hardware. Microsoft's guidance on reducing Power BI import models lists the main techniques: remove unnecessary columns, remove unnecessary rows by filtering to the periods or entities reports need, and group by and summarize, which it calls perhaps the most effective technique, loading pre-summarized data at a coarser grain and keeping detail for drill-through pages. Fewer visuals on the opening page, and slow queries found with the database's own tools (Lesson 11's query plans), finish the job. Hardware helps only after the model and queries are lean.

A slow refresh rate has the same roots: a refresh that reloads everything each night, when an incremental load of new rows (Lesson 12) would do, or a source query that scans an unindexed table.

Wrong conclusion 2: "returns stopped on Thursday"

On Monday the dashboard shows no returns since Thursday. The data is stale: the scheduled refresh has failed every night since Thursday, and the dashboard kept showing its last successful snapshot. Why did it fail? On Thursday the source team renamed a column, reason_code to return_reason, in the returns table. The refresh's query still asked for reason_code and stopped with an error.

This is the objective's "data structure changes": renamed or removed columns, changed data types, new code values a mapping does not recognize, a split table. Every report depends on the shape of its sources, and shapes change without warning. Two practices contain the damage: agree with source owners that structural changes are announced, and validate structure on every load, checking that expected columns exist with expected types before loading any rows.

The deeper failure was that nobody knew. A dashboard that silently shows old data is worse than one that shows an error. Display the data's as-of time on every page, as Lesson 25 advised, and configure monitoring alerts: a notification when a refresh fails, when the latest data is older than expected, or when a key figure moves outside a plausible range, such as daily returns falling to zero. Power BI and similar platforms can email the owner on refresh failure; a simple scheduled query that checks the maximum date in each table can do the rest.

What matters here: stale data is usually a failed refresh, often caused by a change in a source's structure; validate structure on load, show the as-of time, and alert on failures and implausible values.

Wrong conclusion 3: "Boise has the same returns as the whole chain"

The Boise manager selects her store in the filter and sees the chain's totals. The filter is not working. Typical causes, from most to least common:

• A missing or wrong relationship. The filter is on the store table, but the returns table is not related to it, or is related on the wrong column, so the selection cannot reach the returns.
• Mismatched values. The filter offers "Boise" while the data holds "Boise " with a trailing space or "BOISE", so nothing matches, or the visual ignores the filter. Lesson 14's standardizing fixes it at the source.
• A visual set to ignore filters. A card designed to show the chain total keeps showing it, by design, next to filtered charts, confusing readers.
• Filters in the wrong order or scope. A page filter and a report filter conflict, or a filter applied before a calculation changes what the calculation sees.

The validation technique is data filtering in reverse: apply the filter, then check the result independently. Query the source for Boise's returns directly, with SQL or a pivot table, and compare the total with the filtered dashboard. A filtered figure that equals the unfiltered one is a symptom, not a coincidence.

Wrong conclusion 4: "the numbers are right because the formulas ran"

The September total on the dashboard is 7 percent below the finance team's figure. Two causes are common, and the Reinhart-Rogoff case shows the cost of missing either.

Corrupt data. The nightly file from one region arrived truncated, its last 3,000 rows missing after a transfer interruption, and loaded without complaint. Source validation catches it: compare row counts and control totals with the source system, and compare file checksums, the techniques of Lesson 22.

A calculation error. A measure meant to sum returns over all regions references a range or a filter that excludes one, exactly the class of error Herndon, Ash and Pollin found. The objectives name three kinds of review that catch it:

A calculation review is often the cheapest: total the month in a pivot table from the raw data and see whether it matches the dashboard. When two independent methods agree, a hidden error is unlikely; when they disagree, the reviewer has found where to look.

What matters here: before publishing, reconcile key figures with the source and recompute them by an independent route; reviews of code, calculations and conclusions catch the errors that running formulas cannot.

A validation checklist

Common misconceptions

• "A slow report needs a faster server." Data reduction and leaner queries usually fix it first; Microsoft calls summarizing perhaps the most effective size reduction.
• "No data since Thursday means nothing happened since Thursday." Check the refresh history and the data's as-of time before reporting a business change.
• "If the filter shows a number, the filter works." Compare the filtered figure with an independent query; equal filtered and unfiltered totals reveal a broken filter.
• "Peer review is only for academic papers." Any report that informs decisions benefits from a second person checking code, calculations and conclusions.
• "Source schemas are stable." Renamed columns and new codes are a leading cause of failed refreshes; validate structure on every load.

The short version

• Load time, refresh rate and data size: remove unnecessary columns and rows, summarize to a coarser grain, load incrementally, tune source queries.
• Filters: check relationships, standardize values, verify filtered totals independently.
• Stale data: refresh failures, often from data structure changes; validate structure, show as-of times, alert on failure and staleness.
• Corrupt or wrong figures: validate sources with row counts, control totals and checksums; review code, calculations and conclusions.
• Herndon, Ash and Pollin found a spreadsheet range that excluded five countries; with other errors, it turned 2.2 percent growth into minus 0.1.

Sources

1. Herndon, T., Ash, M., and Pollin, R. (2013). Does high public debt consistently stifle economic growth? A critique of Reinhart and Rogoff (Working Paper 322). Political Economy Research Institute, University of Massachusetts Amherst. Published in 2014 in the Cambridge Journal of Economics, 38(2), 257-279. peri.umass.edu (PDF)
2. Microsoft. (n.d.). Data reduction techniques for Import modeling. Power BI guidance, Microsoft Learn. learn.microsoft.com
3. Microsoft. (n.d.). Data refresh in Power BI. Microsoft Learn. learn.microsoft.com
4. Government Data Quality Hub. (2020). The Government Data Quality Framework (timeliness and consistency). GOV.UK. gov.uk

A file in the wrong units

On 23 September 1999 NASA lost the Mars Climate Orbiter as it arrived at Mars. On 10 November the mishap investigation board named the root cause: "the failure to use metric units in the coding of a ground software file, 'Small Forces', used in trajectory models". Thruster data was produced in English units, while the existing software interface documentation required the output file to be metric, and the navigation team, reasonably, assumed it was. The documentation existed. It said the right thing. Nobody checked that the data obeyed it, and a units mismatch between two teams cost NASA the spacecraft.

Business data fails in the same way, less spectacularly and far more often: two teams use one word for two things, a column's unit is unstated, a figure's origin is unknown. Objective 5.1 asks you to explain data management concepts, and its list is the toolkit for preventing exactly this. It covers integration and documentation: data flow diagrams, data explainability reports, data dictionaries, hierarchy structures and data lineage. It adds a source of truth, data versioning with snapshots and refresh intervals, and metadata. This lesson follows one disputed number through a company until those tools resolve it.

Three answers to one question

The chief executive asks how many active customers the company has. Marketing says 52,300, from the email platform. The loyalty team says 48,000, from the loyalty database. Finance says 41,200, from the billing system. All three ran correct queries. They disagree because "active customer" means three different things: anyone who opened an email this year; anyone with loyalty points who bought in the last twelve months; anyone billed in the last twelve months, excluding employees and test accounts. And the three systems identify customers differently, so even counting the same people is hard.

The disagreement is not a calculation problem. It is a data management problem, and the fix has four parts. Agree and document what the words mean, and trace where each number comes from. Decide which system is authoritative for which data, and record which version of the data each report used.

Documentation: five artefacts

The Mars Climate Orbiter's lesson applies to every row: a data dictionary is only as good as the checks that enforce it. A dictionary that says amounts are in dollars should be matched by a validation rule, Lesson 13's kind, that rejects a file in cents. A data flow diagram can be drawn as boxes and arrows in any diagramming tool, but even a table of source, process, destination and frequency does the job.

Lineage deserves particular attention because questions about numbers are usually questions about lineage. The W3C's PROV standard, published in April 2013, defines provenance as information about the entities, activities and people involved in producing a piece of data; lineage is that record for a figure in a report. Modern data catalogues and pipeline tools capture it automatically by reading the transformations; in small teams, a header comment in each query and a sources note in each report go a long way.

Remember: a data dictionary defines fields, a data flow diagram maps movement, and a hierarchy structure defines levels for roll-ups. Lineage traces one figure to its sources, and an explainability report tells non-specialists how a result was produced.

Metadata: data about the data

Everything in those artefacts is metadata, data that describes other data. It comes in three broad kinds:

• Technical metadata: tables, columns, types, sizes, keys, file formats.
• Business metadata: definitions, KPI formulas, owners, classifications, approved uses.
• Operational metadata: when data was loaded, how many rows, refresh history, lineage, quality scores.

A data catalogue gathers all three in one searchable place, so an analyst can find the certified active-customer table, read its definition and owner, and see when it last refreshed before using it. Metadata is what turns a data lake into something better than Lesson 7's swamp.

One source of truth, and master data management

The executive needs one number, which means deciding which system is authoritative. A single source of truth is the agreed, authoritative source for a given piece of data: billing is the source of truth for who paid, the loyalty database for points, the HR system for who is an employee. Other systems may hold copies, but when they disagree, the source of truth wins, and reports draw from it or from a certified data set built from it.

Customers, products, stores and suppliers, the entities that many systems share, are master data, and master data management (MDM, on the exam's acronym list) is the discipline of keeping one consistent, authoritative record of each. In the dispute, MDM matches the three systems' customer records, recognizing that Ana Lima in billing, ana.lima at an email address and loyalty member 4411 are one person. It then produces a golden record with a single customer ID that every system can reference. This is where objective 5.1's "integration" belongs: integration is not only moving data between systems, as Lesson 12 described, but making integrated systems agree on what each entity is.

Data versioning: snapshots and refresh intervals

One more source of disagreement remains: time. The loyalty team ran its query on Monday; finance used the month-end snapshot from the previous Friday. Lesson 25 introduced snapshots and refresh intervals as delivery choices; here they are management obligations. Every certified data set should state its refresh interval, nightly for example, and its last refresh time. Every report should state the snapshot or as-of time its figures represent. And snapshots used for official reporting, month-end closes or regulatory returns should be kept, labelled and unchanged, so that a figure published in October can be reproduced in December.

The answer

With a dictionary definition, a lineage trace, billing as the source of truth, MDM's golden records and the month-end snapshot, the company reports 43,850 active customers as of 30 September. Marketing's 52,300 remains a true count of something else, email-engaged contacts, now labelled as such. The explainability report says why the old dashboard showed more. The number did not become correct by calculation; it became correct by management.

Common misconceptions

• "If the query is right, the number is right." Three correct queries produced three answers because the definition, the source and the date differed.
• "Documentation is the same as control." The Mars Climate Orbiter's interface documentation required metric units; only a check on the data would have enforced it.
• "A single source of truth means one database for everything." It means one authoritative source for each kind of data, which other systems reference.
• "Lineage is the same as a data flow diagram." A data flow diagram maps how data moves in general; lineage traces a particular figure back to its sources.
• "Metadata is just technical detail." Business and operational metadata, definitions, owners and refresh history, are often what users need most.

Putting it together

• Documentation: data dictionary, data flow diagram, hierarchy structure, data lineage and data explainability report; enforce definitions with validation rules.
• Metadata: technical, business and operational, gathered in a catalogue.
• A single source of truth names the authoritative source for each kind of data; MDM keeps one golden record per shared entity across integrated systems.
• Versioning: state refresh intervals and as-of times, and keep official snapshots unchanged so figures can be reproduced.

Sources

1. Mars Climate Orbiter Mishap Investigation Board. (1999, November 10). Phase I report. National Aeronautics and Space Administration. llis.nasa.gov (PDF)
2. World Wide Web Consortium. (2013). PROV-Overview: An overview of the PROV family of documents. W3C Working Group Note. w3.org
3. DAMA International. (2017). DAMA-DMBOK: Data management body of knowledge (2nd ed.). Technics Publications.
4. National Institute of Standards and Technology. (2023). Artificial intelligence risk management framework (AI RMF 1.0, NIST AI 100-1), section on explainable and interpretable systems. nist.gov
5. Government Data Quality Hub. (2020). The Government Data Quality Framework (metadata and documentation). GOV.UK. gov.uk

Seventy-two hours

Article 33 of the European Union's General Data Protection Regulation gives an organization that suffers a personal data breach a deadline. It must notify the supervisory authority "without undue delay and, where feasible, not later than 72 hours after having become aware of it". No notice is needed if the breach is unlikely to result in a risk to people's rights and freedoms. The deadline is written in hours, not working days. An analyst who finds on Friday evening that a spreadsheet of customer emails and purchase histories went to the wrong supplier should not wait until Monday to say so. The clock runs from when the organization becomes aware, and her report may be how it becomes aware.

Objective 5.2 asks you to summarize concepts related to data compliance. They are retention, GDPR, jurisdictional requirements, replication, storage, data ethics, PCI DSS, audits, classification, and incident reporting for data breaches and security incidents. The verb is summarize, so the exam wants the shape of each rule and the situations it applies to, not legal detail. This lesson lays the main regimes side by side and then reads the comparison for what an analyst must do.

Two regimes side by side

Read the second row first: the two regimes protect different things. A customer's name, email and purchase history are personal data under GDPR; her card number is payment account data under PCI DSS, and personal data as well. A table holding both is subject to both. The PCI Council's own advice to merchants is not to store cardholder data unless it is absolutely necessary. For analysts, that means keeping card numbers out of analytical data, since analysis almost never needs a full card number. The standard forbids keeping sensitive authentication data, such as the security code printed on the card, after a payment is authorized, even if encrypted. It also requires card numbers to be masked when displayed. Both rules are easier to keep when the data never arrives.

In short: GDPR is law protecting personal data, with principles, a 72-hour breach notice and large fines. PCI DSS is an industry standard protecting payment card data, enforced through the card brands' programs. Much data falls under both.

Jurisdiction, storage and replication

Jurisdictional requirements are the rules of the places that govern the data: where the people are, where the organization is, and where the data is stored. GDPR follows the personal data of people in the EU and allows transfers to other countries only under its transfer rules, such as a European Commission decision that the destination protects data adequately. In the United States, HIPAA governs health information held by health plans, health care clearinghouses, health care providers that conduct certain transactions electronically, and their business associates. Lesson 29 returns to it. California's Consumer Privacy Act gives that state's consumers the right to know about, delete and opt out of the sale or sharing of personal information that businesses collect about them. An analyst does not need to master every regime, but must find out which apply to a data set before moving it.

That is why storage and replication are compliance topics. Storage location brings law with it: copying EU customers' records to servers in another country can be a transfer that GDPR's rules must permit. Replication, keeping copies on other servers or in other regions so that one failure loses nothing, multiplies the places data lives, and the obligations follow every copy. Under the CCPA, for example, a consumer can ask a business to delete her personal information and to tell its service providers to do the same. A deletion that reaches the main database but not the replicas, the warehouse and the extracts on analysts' laptops is incomplete. Know where every copy is.

Retention: long enough, and no longer

Retention rules pull in two directions. Some rules set minimums. The US Internal Revenue Service, for example, advises keeping the records that support a tax return for 3 years in most cases. It advises 7 years after claiming a loss from worthless securities or a bad debt deduction, and keeping records indefinitely if no return is filed. GDPR sets a maximum principle, called storage limitation. Personal data may be kept in identifiable form "for no longer than is necessary for the purposes for which the personal data are processed". There are exceptions, such as archiving in the public interest and research.

A retention schedule resolves the pull for each kind of data: what it is, why it is kept, the minimum and maximum periods, who owns it, and how it is destroyed or anonymized at the end. For analysts, two consequences follow. Data past its retention date should not be in your extracts, however useful it looks; and long-term trend analysis often relies on aggregated or anonymized data precisely because the detailed personal records must go.

Classification: label data by the harm its exposure would cause

Data classification assigns each data set a level that determines how it must be handled. Each organization sets its own levels; a four-level scheme might look like this:

The US federal standard FIPS 199, published in 2004 for federal information systems, takes a related approach. It categorizes information by the potential impact of a loss of confidentiality, integrity or availability. The impact is low if the adverse effect would be limited, moderate if serious, and high if severe or catastrophic. Whatever the scheme, treat the classification as travelling with the data. An extract of a confidential table is confidential, and a report that aggregates restricted data should be classified lower only if no individual can be identified from it.

Audits and data ethics

An audit is an independent check that practice matches policy and law. An internal audit team, an external auditor or, for PCI DSS, a Qualified Security Assessor approved by the PCI Council examines access, retention, classification and controls. For analysts, audit-readiness means documented lineage, access that matches roles, and the ability to show who used which data for what. The documentation and lineage of Lesson 27, with the access logs that databases and platforms keep, are an audit's raw material.

Data ethics asks what you should do, not only what you may. The UK government's Data Ethics Framework of 2020 set three overarching principles: transparency, accountability and fairness. In December 2025 it was retitled the Data and AI Ethics Framework and four more principles were added: privacy, environmental sustainability, societal impact and safety. A use can be legal and still unethical: using loyalty data to show higher prices to the customers least likely to compare may break no law and still fail a test of fairness. Ethics also covers the analyst's own conduct: not browsing personal data out of curiosity, and not shaping results to please.

Incident reporting

GDPR defines a personal data breach as a security breach leading to the accidental or unlawful destruction, loss, alteration, unauthorized disclosure of, or access to, personal data. The emailed spreadsheet in this lesson's opening is one; so is a lost unencrypted laptop. Organizations also track wider security incidents, such as malware or an intrusion, that may or may not involve personal data. The analyst's part in the response is consistent:

1. Report internally at once, to the security or data protection team, through the organization's incident process; do not investigate alone or try to hide it.
2. Contain as directed: ask the recipient to delete the file, revoke shared links, disable access.
3. Preserve evidence: what was shared, with whom, when, and which records.
4. Support assessment and notification: the data protection team decides whether to notify the supervisory authority within 72 hours and, where the risk to people is high, the people themselves.
5. Learn: record the cause and the fix, so the same mistake is less likely.

In short: classify data, keep it only as long as the retention schedule allows, and know which jurisdictions and copies it lives in. Report any suspected breach internally at once, because GDPR's 72-hour clock starts when the organization becomes aware.

Common misconceptions

• "GDPR applies only to companies based in Europe." Article 3 also reaches organizations elsewhere that offer goods or services to people in the EU or monitor their behaviour there.
• "PCI DSS is a law." It is an industry standard, enforced through the card brands' own compliance programs rather than by a government.
• "Keeping data forever is the safe choice." GDPR's storage limitation forbids keeping personal data in identifiable form longer than necessary, and every extra year of data is more to lose in a breach.
• "Deleting a record from the main database completes a deletion request." Replicas, warehouse tables and extracts hold copies too.
• "If it is legal, it is ethical." Ethics frameworks ask about fairness, transparency and wider harms that the law does not fully cover.

Where this leaves us

• GDPR: personal data, Article 5 principles including storage limitation, 72-hour breach notification where feasible, and fines in two tiers reaching 20 million euros or 4 percent of worldwide turnover.
• PCI DSS: an industry baseline for protecting payment account data, enforced through the card brands' programs; keep card data out of analytics.
• Retention schedules balance legal minimums (such as the IRS's 3 or 7 years) with storage limitation; storage location and replication carry obligations to every copy.
• Classification (public, internal, confidential, restricted, or FIPS 199's low, moderate and high) decides handling; audits check practice against policy; ethics frameworks such as the UK's add principles like transparency, accountability and fairness.
• Report suspected breaches internally at once; contain, preserve evidence and support notification.

Sources

1. European Parliament and Council of the European Union. (2016). Regulation (EU) 2016/679 (General Data Protection Regulation), Articles 3, 4, 5, 33, 34, 44, 45 and 83. Official Journal of the European Union. eur-lex.europa.eu; article text consulted at gdpr-info.eu
2. PCI Security Standards Council. (n.d.). PCI Data Security Standard (PCI DSS). pcisecuritystandards.org
3. PCI Security Standards Council. (n.d.). For PCI DSS, can sensitive account data be stored before authorization? (FAQ). pcisecuritystandards.org
4. PCI Security Standards Council. (2018). PCI DSS quick reference guide: Understanding the Payment Card Industry Data Security Standard version 3.2.1. listings.pcisecuritystandards.org
5. PCI Security Standards Council. (n.d.). At a glance: PCI data storage do's and don'ts. listings.pcisecuritystandards.org
6. Internal Revenue Service. (n.d.). How long should I keep records? irs.gov
7. Office of the Attorney General, State of California. (2026). California Consumer Privacy Act (CCPA). oag.ca.gov
8. U.S. Department of Health and Human Services. 45 CFR 160.103, Definitions (covered entity, business associate, protected health information). Electronic Code of Federal Regulations. ecfr.gov
9. National Institute of Standards and Technology. (2004). Standards for security categorization of federal information and information systems (FIPS 199). csrc.nist.gov
10. Government Digital Service. (2025). Data and AI Ethics Framework (formerly the Data Ethics Framework, 2020). GOV.UK. gov.uk

The request on Dana's desk

Dana is an analyst for a group of outpatient clinics. A university team studying missed appointments asks for a year of appointment records. Her table looks like this, with twelve fictional patients standing in for thousands of real ones (the first three rows are shown):

Before she sends anything, Dana has five questions to answer. Which columns are personal or health information? Who inside the clinics may see which columns? How should the data travel and be stored? What may the researchers do with it? And how much identity can she remove while keeping the data useful?

Objective 5.3 asks you to compare and contrast the practices that answer those questions: role-based access control, encryption in transit and at rest, data usage, data sharing, NIST guidance, PII, PHI, anonymization and masking. This lesson takes Dana's questions in order.

Which data is personal: PII and PHI

NIST's guide to protecting personally identifiable information, SP 800-122, defines PII broadly. It is information that can be used to distinguish or trace a person's identity, such as a name, Social Security number, date and place of birth or biometric record. It also covers any other information linked or linkable to a person, such as medical, educational, financial and employment information. The exam objectives write "personal identifiable information" and the acronym list writes "personally identifiable information"; both mean the same thing.

It helps to split PII into two kinds. Direct identifiers, such as a name or an email address, point to one person on their own. Quasi-identifiers, such as ZIP code, birth date and sex, do not, but they combine. In the 1990s Massachusetts released insurance records of state employees' hospital stays for research, with names removed but birth date, ZIP code and sex kept. Latanya Sweeney, then doing graduate work at MIT, bought the Cambridge voter list for $20, matched those three fields, and found the records of Governor William Weld. She went on to estimate that up to 87 percent of the US population could be uniquely identified by five-digit ZIP code, birth date and sex, using 1990 census data. A later study using 2000 census data put the figure at 62 percent.

PHI is health information about an identifiable person. The exam's acronym list expands it as "personal health information". The US HIPAA rules call it protected health information: individually identifiable health information held or sent in any form, with exceptions such as the employment records a covered entity keeps in its role as an employer. HIPAA applies to covered entities, meaning health plans, health care clearinghouses and health care providers that conduct certain transactions electronically. It also applies to their business associates, which include contractors that do data analysis for them.

In Dana's table, name and email are direct identifiers, and ZIP, birth date and sex are quasi-identifiers. The clinics are a covered entity, so every row, with its visit date, clinic and missed flag, is PHI.

The upshot: deleting the name column does not make a table anonymous. Quasi-identifiers combine into a fingerprint, and health details attached to an identifiable person make the whole row PHI.

Who may touch it: role-based access control

Giving each person a private list of permissions breaks down quickly. People join, move and leave, and soon nobody can say who can see what. Role-based access control (RBAC) puts roles in the middle: each role gets the permissions its job needs, and each user gets one or more roles. David Ferraiolo and Rick Kuhn formalized the model in 1992, and NIST's version became American National Standard INCITS 359 in 2004. When Dana's colleague moves from scheduling to finance, an administrator changes her role, not fifty separate grants.

This is least privilege in practice, and for PHI it is also the law. HIPAA's minimum necessary standard requires covered entities and business associates to make reasonable efforts to limit PHI to the minimum necessary for the purpose of each use, disclosure or request, with exceptions such as disclosures for treatment.

In PostgreSQL, a free database you can run yourself, roles and grants look like this. The role dana must already exist as a login role, and the appointments table is assumed to have these columns:

CREATE ROLE analyst NOLOGIN;
GRANT SELECT (pid, zip3, birth_year, sex, visit_date, clinic, missed)
    ON appointments TO analyst;
GRANT analyst TO dana;

The column list makes this a column-level grant: the analyst role can read seven columns and nothing else. PostgreSQL can also restrict rows with row security policies, so that each scheduler sees only her own clinic's appointments.

How it travels and rests: encryption in transit and at rest

PostgreSQL's client library shows how much the details matter. Its sslmode setting decides what a connection protects against:

The documentation says the default exists only for backward compatibility and is not recommended in secure deployments, and it recommends verify-full in most security-sensitive environments. A connection to a database holding PHI should therefore say sslmode=verify-full.

The rules reward encryption. GDPR's Article 32 lists pseudonymisation and encryption among the security measures appropriate to the risk. Its Article 34 says a controller need not tell individuals about a breach if the data was unintelligible to anyone not authorized to see it, for example because it was encrypted. HIPAA's Security Rule lists encryption of stored and of transmitted electronic PHI as "addressable" specifications. An organization must implement them where reasonable and appropriate; otherwise it must document why not and use an equivalent measure where that is reasonable and appropriate.

What it may be used for, and with whom: data usage and data sharing

Data usage rules limit purposes. GDPR's purpose limitation principle says personal data is collected for specified, explicit and legitimate purposes and not further processed in incompatible ways. HIPAA's minimum necessary standard limits how much PHI each use gets. NIST SP 800-122 advises organizations to keep the PII they use, collect and retain to what is strictly necessary. In practice, an analyst uses data only for the approved purpose, takes only the columns that purpose needs, and records what was used.

Data sharing moves data to someone else, and the agreement comes first. Under HIPAA, a covered entity may let a business associate handle PHI only with satisfactory assurance, documented in a written contract, that the business associate will safeguard it. For research, HIPAA offers three routes, which differ in what Dana may keep:

The missed-appointment study needs visit dates, because weekday and season matter, so Safe Harbor's year-only dates would destroy the question. A limited data set under a data use agreement fits. In it, the researchers agree to use the data only as permitted, to safeguard it, to report any misuse, and not to identify or contact the patients.

How much identity to remove: masking, pseudonymization and anonymization

Microsoft's SQL Server, for example, offers dynamic data masking, which shows masked values to users without the right permission while the stored data stays unchanged. Microsoft also warns that users who can run their own queries may infer masked values by guessing, so masking complements other controls rather than replacing them.

Anonymization is harder than it looks, and the words are used loosely. NIST notes that some writers treat de-identification and anonymization as the same thing, while others reserve anonymization for processes that cannot be reversed. In 2006 AOL released search logs with each user replaced by a random number, but the searches themselves identified people. In 2008 the researchers Narayanan and Shmatikov showed that the set of films a person had watched and rated in the Netflix Prize data could often act as an identifier.

Try it: four techniques on Dana's table

Save this as privacy.py in the virtual environment from Lesson 1 and run it. It builds the twelve fictional patients, then tests a plain hash, a keyed pseudonym, a display mask and k-anonymity.

import hashlib
import hmac
import secrets
import pandas as pd

rows = [
    ("Ana Ruiz", "ana.ruiz@example.com", "02138", "1984-03-12", "F", "2026-01-05", "Cardiology", 0),
    ("Ben Okafor", "ben.okafor@example.com", "02139", "1986-07-30", "M", "2026-01-06", "Dermatology", 1),
    ("Chloe Martin", "chloe.m@example.org", "02140", "1983-11-02", "F", "2026-01-06", "Cardiology", 1),
    ("Dev Patel", "dev.patel@example.net", "02141", "1991-05-19", "M", "2026-01-07", "Orthopedics", 0),
    ("Elena Rossi", "elena.rossi@example.com", "02142", "1995-09-08", "F", "2026-01-08", "Dermatology", 0),
    ("Femi Adeyemi", "femi.a@example.org", "02138", "1993-02-25", "M", "2026-01-08", "Cardiology", 1),
    ("Grace Kim", "grace.kim@example.com", "02139", "1972-12-14", "F", "2026-01-09", "Orthopedics", 0),
    ("Hugo Laurent", "hugo.l@example.net", "02140", "1975-04-03", "M", "2026-01-12", "Dermatology", 0),
    ("Ines Duarte", "ines.d@example.com", "02141", "1978-08-21", "F", "2026-01-12", "Cardiology", 1),
    ("Jamal Hassan", "jamal.h@example.org", "02142", "1971-06-11", "M", "2026-01-13", "Orthopedics", 1),
    ("Kira Novak", "kira.n@example.com", "02138", "1997-10-30", "F", "2026-01-14", "Cardiology", 0),
    ("Leo Brandt", "leo.b@example.net", "02139", "1999-01-17", "M", "2026-01-15", "Dermatology", 0),
]
df = pd.DataFrame(rows, columns=["name", "email", "zip", "birth_date", "sex",
                                 "visit_date", "clinic", "missed"])

# 1. A plain hash is not anonymous: hash a list of guesses and compare.
def sha(text):
    return hashlib.sha256(text.encode()).hexdigest()

guesses = ["someone@example.com", "ben.okafor@example.com", "kira.n@example.com"]
lookup = {sha(g): g for g in guesses}
print("Plain hash, matched:", df["email"].map(sha).map(lookup).dropna().tolist())

# 2. A keyed pseudonym (HMAC): without the key, the same guesses fail.
key = secrets.token_bytes(32)   # keep the key apart from the data
def pseudonym(text):
    return hmac.new(key, text.encode(), hashlib.sha256).hexdigest()[:12]

df["pid"] = df["email"].map(pseudonym)
print("Keyed pseudonym, matched:", df["pid"].map(lookup).dropna().tolist())

# 3. Masking for display: keep the first letter and the domain.
def mask(email):
    user, domain = email.split("@")
    return user[0] + "***@" + domain

print("Masked:", df["email"].map(mask).head(3).tolist())

# 4. k-anonymity: the size of the smallest group sharing the quasi-identifiers.
print("k before:", df.groupby(["zip", "birth_date", "sex"]).size().min())
df["zip3"] = df["zip"].str[:3]
df["decade"] = df["birth_date"].str[:3] + "0s"
groups = df.groupby(["zip3", "decade", "sex"]).size()
print(groups.to_dict())
print("k after generalizing:", groups.min())

The output:

Plain hash, matched: ['ben.okafor@example.com', 'kira.n@example.com']
Keyed pseudonym, matched: []
Masked: ['a***@example.com', 'b***@example.com', 'c***@example.org']
k before: 1
{('021', '1970s', 'F'): 2, ('021', '1970s', 'M'): 2, ('021', '1980s', 'F'): 2, ('021', '1980s', 'M'): 1, ('021', '1990s', 'F'): 2, ('021', '1990s', 'M'): 3}
k after generalizing: 1

1. A plain hash is a code anyone can rebuild. Hashing is one-way, but someone with a list of likely emails can hash the list and compare: two of the three guesses matched. HIPAA's Safe Harbor also says a re-identification code must not be derived from information about the person, so a hash of an email does not qualify as a study code there; a random study number in a lookup table does.
2. A keyed hash resists the same attack. HMAC mixes in a secret key, made here by Python's secrets module, so the guesses fail. Store the key apart from the data, and keep it: a new key next month gives every patient a new code and breaks the link between extracts.
3. Masking is for eyes, not for files. The masked emails are fine on a screen, but the full addresses are still in the table.
4. k-anonymity measures the smallest crowd. A data set is k-anonymous when every combination of quasi-identifier values is shared by at least k records. Full ZIP, birth date and sex give k = 1: every patient is unique. Three ZIP digits, birth decade and sex still leave one man born in the 1980s alone. Suppressing that one record, or generalizing further, reaches k = 2.

k-anonymity hides who, but not always what. Both women born in the 1980s in this table visited Cardiology. So anyone who knows that a woman of that age from the 021 area is in the extract learns where she was treated. Refinements such as l-diversity add a requirement that each group contain a variety of sensitive values.

What NIST adds

The objectives list NIST, the National Institute of Standards and Technology, an agency of the US Department of Commerce. Its publications are free to download, and analysts meet them as reference points. FIPS 199 was written for federal information systems, and the Privacy Framework says its contents do not have the force and effect of law.

Dana's answer

1. Agree the route first. The purpose is research, so she prepares a limited data set, and nothing moves until the data use agreement is signed.
2. Take the minimum necessary. She drops name and email, adds a random study number from a lookup table the clinics keep, and keeps ZIP, visit date, clinic and missed flag. Birth date becomes birth year, because the study needs age, not birthdays.
3. Check the crowd sizes. She counts k on the quasi-identifiers and generalizes or suppresses tiny groups before release.
4. Encrypt the journey and the destination. The extract sits on an encrypted share and travels through the approved secure transfer service, never as an email attachment.
5. Limit and log access. Only the research extract role can read it, and she records who received what, when, and under which agreement.
6. Document it. The release goes into the catalogue with its lineage (Lesson 27) and the extract's retention date (Lesson 28).

Common misconceptions

• "Removing names makes data anonymous." Quasi-identifiers combine; ZIP code, birth date and sex were enough to find a governor's records.
• "A hashed email is anonymous." Anyone with a list of candidate emails can rebuild the hashes, as two of three guesses showed.
• "Masked data is protected data." Masking changes what is shown; dynamic masking leaves the stored data unchanged, and users who can run their own queries may infer masked values.
• "Pseudonymized data is outside GDPR." Recital 26 says data that can be attributed to a person with additional information, such as a key, is still about an identifiable person.
• "Encrypting the connection protects the file." Encryption in transit protects the journey only; the file at its destination needs encryption at rest.

Pulling it together

• PII covers direct identifiers and quasi-identifiers; PHI is identifiable health information held by HIPAA covered entities and their business associates.
• RBAC grants permissions to roles and roles to people; with column grants and row policies it puts least privilege and HIPAA's minimum necessary into practice.
• Encryption in transit (TLS, sslmode=verify-full) protects the journey; encryption at rest (BitLocker, FileVault, encrypted storage) protects lost devices and copies.
• Data usage rules limit purposes and columns; data sharing needs an agreement first, such as a business associate contract or a data use agreement.
• Masking hides values on screen, pseudonymization replaces them with keyed codes, and anonymization removes identifiability; only the last takes data outside GDPR, and it is the hardest to achieve.

Sources

1. McCallister, E., Grance, T., & Scarfone, K. (2010). Guide to protecting the confidentiality of personally identifiable information (PII) (NIST SP 800-122). National Institute of Standards and Technology. csrc.nist.gov
2. Garfinkel, S. L. (2015). De-identification of personal information (NISTIR 8053). National Institute of Standards and Technology. csrc.nist.gov
3. Garfinkel, S., Guttman, B., Near, J., Dajani, A., & Singer, P. (2023). De-identifying government datasets: Techniques and governance (NIST SP 800-188). National Institute of Standards and Technology. csrc.nist.gov
4. National Institute of Standards and Technology. (2020). NIST Privacy Framework: A tool for improving privacy through enterprise risk management, Version 1.0. nvlpubs.nist.gov; current status at nist.gov
5. National Institute of Standards and Technology. (2024). The NIST Cybersecurity Framework (CSF) 2.0 (NIST CSWP 29). csrc.nist.gov
6. National Institute of Standards and Technology. (n.d.). Role based access control. Computer Security Resource Center. csrc.nist.gov
7. U.S. Department of Health and Human Services. 45 CFR 160.103 (definitions), 164.306 and 164.312 (Security Rule), 164.502 and 164.514 (minimum necessary, business associates, de-identification, limited data sets). Electronic Code of Federal Regulations. 160.103; 164.312; 164.502; 164.514
8. European Parliament and Council of the European Union. (2016). General Data Protection Regulation, Articles 4, 5, 32 and 34 and Recital 26. Text consulted at gdpr-info.eu (Recital 26) and gdpr-info.eu (Article 32); official text at eur-lex.europa.eu
9. PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation: Database roles; GRANT; Row security policies; SSL support. Roles; GRANT; Row security; SSL support
10. Microsoft. (n.d.). Dynamic data masking. Microsoft Learn. learn.microsoft.com
11. Python Software Foundation. (n.d.). hmac: Keyed-hashing for message authentication and secrets: Generate secure random numbers for managing secrets. Python documentation. hmac; secrets
12. Device and file encryption: Microsoft, BitLocker overview, learn.microsoft.com; Apple, Protect data on your Mac with FileVault, support.apple.com; The Document Foundation, Protecting content in LibreOffice, help.libreoffice.org

The Monday the average order jumped

On a Monday morning the shop's dashboard showed the average order value at about 952, against a usual 41.80. Nothing had crashed. A partner's new checkout system had started sending some amounts in cents instead of dollars, so three orders of 38.50, 42.00 and 29.99 arrived as 3850, 4200 and 2999. No query failed, no error appeared, and the number on the screen was wrong.

Objective 5.4 asks you to compare and contrast data quality assurance practices: requirement testing, stress tests, user acceptance testing (UAT), source control, unit tests, data health checks and data drift, automated data quality monitoring, data profiling and quality metrics, and ISO standards. This lesson is a procedure of eight steps, from the first requirement to the daily load, and each step catches a different kind of failure. It ends with a plan for your last week before the exam.

The eight steps on one page

NIST defines a software development life cycle (SDLC) as a formal or informal method for designing, creating and maintaining software. An analyst's queries, cleaning scripts and dashboards are software in that sense, so the same life cycle applies to them.

Step 1: Test the requirements

Requirement testing checks the requirements themselves, before anything is built. A requirement that cannot be tested cannot be accepted or rejected later. Microsoft's guidance for business application projects bases the testing scope on functional and nonfunctional requirements, and asks for clear objectives and entry and exit criteria for each type of test. For an analyst, that means rewriting vague requests as measurable criteria:

Every later test should trace back to one of these lines. A test with no requirement behind it, or a requirement with no test, is a gap you can now see.

Step 2: Unit-test each piece of logic

Microsoft describes unit testing as breaking a program's functionality into discrete testable behaviors and testing each one as an individual unit. Python's built-in unittest framework, originally inspired by JUnit, needs no installation. Save this cleaning rule as clean.py:

KNOWN_TYPOS = {"Nrth": "North", "Sth": "South"}
REGIONS = {"North", "South", "East", "West"}

def clean_region(text):
    value = text.strip().title()
    value = KNOWN_TYPOS.get(value, value)
    if value not in REGIONS:
        raise ValueError("unknown region: " + text)
    return value

Save its tests as test_clean.py in the same folder:

import unittest
from clean import clean_region

class TestCleanRegion(unittest.TestCase):
    def test_trims_and_capitalizes(self):
        self.assertEqual(clean_region("  south "), "South")

    def test_fixes_known_typo(self):
        self.assertEqual(clean_region("nrth"), "North")

    def test_rejects_unknown_value(self):
        with self.assertRaises(ValueError):
            clean_region("Central")

if __name__ == "__main__":
    unittest.main()

Run python -m unittest -v test_clean.py. Each test reports ok, and the run ends with a line such as Ran 3 tests in 0.001s followed by OK (your time will differ). Now break the rule on purpose: change text.strip().title() to text.title() and run again. The run ends FAILED (errors=1), because the padded value " south " no longer matches a region. The mistake is caught in seconds, before it reaches a report, which is why unit tests run on every change.

Step 3: Keep everything in source control

The Pro Git book defines version control as a system that records changes to a file or set of files over time so that you can recall specific versions later. It lets you compare changes, see who changed what, and return a file or a whole project to an earlier state. Git is free and open source. Every query, cleaning script and test belongs in a repository:

git init
git add clean.py test_clean.py
git commit -m "Add region cleaning with tests"
git log --oneline
git diff
git revert HEAD

git log lists the history, git diff shows uncommitted edits line by line, and git revert HEAD records a new commit that undoes the last one, so the history stays honest. Keep data files, passwords and keys out of the repository: Lesson 29's pseudonymization key, sitting in a shared history, would undo the protection it was meant to give.

Step 4: Stress-test it, then let the users accept it

Microsoft's performance guidance puts the difference in one line: stress testing finds the breaking point under peak load. For the shop, a stress test might copy a month of orders, multiply the volume by 10, 50 and 100, rerun the dashboard queries at each level, and record when the 5-second target breaks and what happens then. Microsoft's testing guidance for business applications describes UAT as testing the solution from the user's perspective, and notes that a project might start with unit testing and end with UAT. In the shop's UAT, finance staff reconcile three days against the ledger, and the sales team checks that the region filter shows exactly four regions.

Step 5: Profile the data and measure its quality

Data profiling, met in Lesson 13, summarizes a data set's structure and content: types, missing values, duplicates, ranges and allowed values. Quality metrics turn the profile into numbers you can track over time. The UK Government Data Quality Framework uses six core dimensions defined by DAMA UK:

The framework's own example shows why the dimensions are kept apart. A school with 300 students that has recorded 294 emergency phone numbers has a field that is 98 percent complete, but that says nothing about whether the numbers are right.

Save this as health.py and run it in your lab's virtual environment. The batch is Monday's twelve orders, and the baseline holds what past loads looked like:

import datetime
import sqlite3
import pandas as pd

batch = pd.DataFrame({
    "order_id": [5001, 5002, 5003, 5004, 5005, 5006, 5007, 5008, 5009, 5010, 5010, 5012],
    "email": ["a@example.com", "b@example.com", None, "d@example.com", "e@example.com",
              "f@example.com", None, "h@example.com", "i@example.com", "j@example.com",
              "j@example.com", "l@example.com"],
    "region": ["North", "South", "East", "West", "Nrth", "South",
               "East", "West", "North", "South", "South", "East"],
    "amount": [38.50, 42.00, 29.99, 3850, 45.25, 4200,
               51.10, 36.80, 2999, 40.00, 40.00, 47.60],
})
baseline = {"rows": (10, 14), "mean_amount": 41.80,
            "regions": {"North", "South", "East", "West"}}

# 1. Profile: quality metrics for today's batch.
metrics = {
    "completeness_email": batch["email"].notna().mean(),
    "uniqueness_order_id": batch["order_id"].nunique() / len(batch),
    "validity_region": batch["region"].isin(baseline["regions"]).mean(),
    "validity_amount": batch["amount"].between(0.01, 1000).mean(),
}
for name, value in metrics.items():
    print(f"{name}: {value:.1%}")

# 2. Health check: compare today's batch with the baseline.
problems = []
if not baseline["rows"][0] <= len(batch) <= baseline["rows"][1]:
    problems.append("row count out of range")
ratio = batch["amount"].mean() / baseline["mean_amount"]
if not 0.5 <= ratio <= 1.5:
    problems.append(f"mean amount is {ratio:.1f} times the baseline")
new_values = set(batch["region"]) - baseline["regions"]
if new_values:
    problems.append(f"new region values: {sorted(new_values)}")
print(problems)

# 3. Monitor: log every run so trends and alerts can be built on it.
con = sqlite3.connect("dq_log.db")
con.execute("CREATE TABLE IF NOT EXISTS dq_log (run_at TEXT, metric TEXT, value REAL)")
run_at = datetime.datetime.now().isoformat(timespec="seconds")
con.executemany("INSERT INTO dq_log VALUES (?, ?, ?)",
                [(run_at, name, float(value)) for name, value in metrics.items()])
con.commit()
print(con.execute("SELECT COUNT(*) FROM dq_log").fetchone()[0], "rows logged")
con.close()

The output of the first run:

completeness_email: 83.3%
uniqueness_order_id: 91.7%
validity_region: 91.7%
validity_amount: 75.0%
['mean amount is 22.8 times the baseline', "new region values: ['Nrth']"]
4 rows logged

The profile finds two missing emails (10 of 12 present), one duplicated order (order 5010 twice, so 11 distinct IDs in 12 rows), one invalid region and three impossible amounts.

Step 6: Run health checks and catch drift

A data health check compares each new batch with a baseline built from past loads: its volume, its averages and its allowed values. The second block of health.py is one. The row count passed, but the mean amount came out 22.8 times the baseline, and a region value appeared that had never been seen before.

Microsoft's machine learning documentation describes data drift as a change in a model's input data that degrades the model's performance. It lists causes that apply just as well to a dashboard. Upstream process changes are one, such as a replaced sensor that switches units from inches to centimeters. Data quality issues are another, such as a broken sensor that always reads 0. Natural drift is a third, such as mean temperature changing with the seasons. The cents problem is the first kind. Natural drift is the trap in the other direction: December sales are not an error, so compare each batch with a baseline from a comparable period, and let a person judge every flag before anything is changed.

Step 7: Automate the monitoring

The third block of health.py writes every metric to a dq_log table, so each run leaves a record: 4 rows after the first run and 8 after the second. Automated data quality monitoring means running such checks on every load without anyone needing to remember, keeping the results, and alerting someone when a threshold fails. On Windows, Task Scheduler can run the script each morning; it runs tasks when the triggers you choose are met. A chart of the dq_log table over several weeks turns single checks into trend lines, and that is where slow drift shows up.

Step 8: Anchor on standards (ISO)

ISO, the International Organization for Standardization, publishes the standards that give quality a shared vocabulary. ISO/IEC 25012, published in 2008, defines a general data quality model for data kept in a structured format within a computer system. It sorts quality into fifteen characteristics seen from two points of view, inherent and system dependent, and it can be used to set data quality requirements, define measures and plan evaluations. The ISO 8000 series covers the quality of master data and its exchange between systems, and extends ISO 9001, the quality management standard, to the case where data is the product. The UK framework's six dimensions come from DAMA UK rather than ISO. Because vocabularies differ, name the standard you are using whenever you report a quality score.

Comparing the practices

Worth holding on to: each practice catches a different failure. Tests on code cannot see bad data, and checks on data cannot see bad logic, so a pipeline needs both, run automatically, with the history kept.

Your final week before the exam

Data+ DA0-002 has at most 90 questions, multiple choice and performance-based, in 90 minutes, and the passing score is 675 on a scale of 100 to 900. The domains weigh 20, 22, 24, 20 and 14 percent. CompTIA describes the Data+ simulations as questions you can skip and come back to, with a reset button and the chance of partial credit. Recheck all of this on CompTIA's pages before you book, because exam details can change.

On the day, answer what you know first, flag what you doubt, skip a slow simulation and return to it, and keep an eye on the clock. No course can promise a result; steady practice on the objectives is what moves a score.

Common misconceptions

• "Testing is a phase at the end." Quality assurance runs through the whole life cycle, from requirement testing before the build to monitoring every load in operation.
• "Passing unit tests means the data is right." Unit tests check code against the cases you wrote; health checks and quality metrics check each batch of data.
• "A stress test is just a big load test." A load test confirms the expected peak; a stress test goes past it to find the breaking point and how the system fails.
• "Drift always means an error." Natural drift, such as seasonal change, is real; a health check flags it for a person to judge.
• "Source control is only for software engineers." Analysts' SQL, scripts and dashboards change too, and version history makes those changes reviewable and reversible.

Summing up

• The SDLC frames quality work: requirement testing first, unit tests and source control while building, stress tests and UAT before release, and data checks in operation.
• Unit tests check logic, a stress test finds breaking points, and UAT confirms that users accept the result against agreed criteria.
• Profiling turns data into quality metrics such as completeness, uniqueness and validity; health checks compare each batch with a baseline to catch drift.
• Automated monitoring runs the checks on every load, logs the results and alerts on failures; ISO/IEC 25012 and ISO 8000 give quality a standard vocabulary.
• In the final week, start from a timed baseline, spend time in proportion to the domain weights, and recheck CompTIA's pages before exam day.

Sources

1. CompTIA. (2024). CompTIA Data+ DA0-002 certification exam: Exam objectives (Version 1.1), objective 5.4 and the acronym list. CompTIA (PDF); exam details at comptia.org
2. CompTIA. (n.d.). Performance-based questions explained. comptia.org
3. National Institute of Standards and Technology. (n.d.). Software development life cycle (SDLC). CSRC glossary, citing NIST SP 800-218. csrc.nist.gov
4. Microsoft. (n.d.). Unit test basics; Performance testing (Azure Well-Architected Framework); Testing strategy (Dynamics 365 implementation guide); Detect data drift on datasets (Azure Machine Learning); Task Scheduler. Microsoft Learn. Unit tests; Performance testing; Testing strategy; Data drift; Task Scheduler
5. Python Software Foundation. (n.d.). unittest: Unit testing framework. Python documentation. docs.python.org
6. Chacon, S., & Straub, B. (2014). Pro Git (2nd ed.), section 1.1, About version control. Apress. git-scm.com
7. Government Data Quality Hub. (2020). The Government Data Quality Framework. GOV.UK. gov.uk
8. ISO/IEC. (2008). ISO/IEC 25012:2008, Software engineering: Software product Quality Requirements and Evaluation (SQuaRE): Data quality model. Catalogue entry at webstore.iec.ch
9. European Medicines Agency. (2023). Data quality framework for EU medicines regulation (EMA/326985/2023), annex on ISO 9000, ISO 8000 and ISO 25012. ema.europa.eu