Data types and data preparation

Kwame Asante exported three years of BookVet and VetLedger appointment history into a single file for Noor: about 410,000 appointments across the 11 clinics. Jonah expected the next step to be training a model. Noor spent the next two AI hours on the data instead. "Most of the time in a real project goes here," she said. "The model can only be as good as what we feed it."

This lesson covers the kinds of data the exam expects you to recognize and the preparation steps that turn raw records into something a model can learn from. It also covers the privacy questions that come with using customer records, which Hannah Weiss, the privacy and security lead, raised before anyone opened the file.

Structured and unstructured data

Structured data is organized into a fixed schema of rows and columns, like a spreadsheet or a database table. Every appointment record has the same fields: date, time, clinic, visit type, days booked ahead. Most traditional machine learning works on structured data.

Unstructured data has no predefined schema: free-text call notes, emails, radiograph images, recordings of phone calls. It makes up most of the information in an organization, and it is where deep learning and foundation models are strongest. Semi-structured data, such as JSON records or tagged documents, sits between the two: it has some organization, such as named fields, but not a rigid table.

The data types in the exam guide

• Tabular data: rows and columns, each column a feature. Appointment records, invoices, inventory counts.
• Time-series data: measurements in time order, where the order matters. Daily appointment counts, hourly temperature in the vaccine fridge, monthly drug spend. Forecasting models must respect time: you train on the past and test on the future, never the other way round.
• Text: words in documents, messages, notes and reviews.
• Image data: photographs, scans and radiographs, and video, which is a sequence of images.
• Audio: recorded speech and other sound.

Across all of these runs one more distinction: labelled data comes with the correct answers needed for supervised learning, and unlabelled data does not. Labels can already exist, like Jonah's Missed? column, or they can be created by people. Creating labels is often the most expensive part of a project: someone qualified must look at each radiograph and mark whether it is acceptable, for example. AWS offers Amazon SageMaker Ground Truth to manage labelling work by internal teams or a workforce, and lesson 9 returns to it.

From raw data to training data

Noor's preparation steps for the appointment file followed the order most projects use.

• Collect and combine. The appointment records came from two systems. Joining them needed a common key, the booking number, and a check that nothing was lost or duplicated in the join.
• Clean. Some rows had impossible values: appointments booked 400 days ahead, visit lengths of zero minutes, a clinic code that did not exist. Each needed a decision: correct, remove or flag.
• Handle missing values. About 1 in 20 records had no reminder-confirmation status, because the reminder feature had been added partway through the period. Options include removing those rows, filling in a sensible value, or adding a separate "unknown" category. Noor chose the last, because the missingness itself carried information about the date.
• Deal with outliers. A few clients had dozens of bookings in a month, all for a breeding kennel. They were real, not errors, but they could dominate a model, so Noor capped the "past bookings" feature.
• Transform features. Models need numbers. A categorical feature such as clinic name becomes several 0-or-1 columns through one-hot encoding. Numbers on very different scales are often scaled to similar ranges so that no feature dominates because of its units.
• Engineer features. New features can be built from existing ones: day of week from the date, "days since last visit", "number of past misses". Good features often matter more than the choice of algorithm.
• Split. Finally the data is divided into training, validation and test sets, which lesson 4 explains.

Data leakage

One mistake deserves its own heading because it produces models that look excellent and fail in use. Data leakage happens when training data contains information that would not be available at the moment of prediction. Kwame's export included a field called checkout_time. It was blank for every missed appointment, for the obvious reason that nobody checked out. A model would learn that a blank checkout time means a miss and score almost perfectly on historical data, yet it would be useless in the evening, when tomorrow's appointments have no checkout times at all. Noor removed every field that is only filled in after the appointment. The test question to ask about each feature is simple: would I know this value at the moment I need the prediction?

Privacy and appropriate use

Appointment records contain personal information about clients: names, phone numbers, addresses, payment details and patterns of behaviour. Before using them, Hannah set three rules that apply far beyond this project.

• Minimize. Use only the fields the model needs. The no-show model does not need client names, phone numbers or street addresses, so they were removed before the file left the practice system. Booking numbers were replaced with random identifiers.
• Know the legal basis. In Canada, private-sector organizations collecting personal information in the course of commercial activity are governed by PIPEDA, and Alberta and British Columbia have their own private-sector privacy laws (PIPA) that apply to organizations in those provinces. Using client data for a new purpose may require that clients were told about that purpose or that it is consistent with what they were told. Hannah checked the privacy notice before work started.
• Protect it. The prepared data set was stored encrypted in a dedicated Amazon S3 bucket with access limited to Noor and Kwame, as covered in AWS-102.

Hannah also asked a fairness question about features. Postal codes, for example, can act as a stand-in for income or ethnicity. A model that learns "clients from these neighbourhoods miss more often" and is then used to treat those clients differently can be unfair even if no protected attribute appears in the data. Noor kept postal code out of the first model and wrote the decision down. AWS-114 covers bias in data in depth.

# Small cleaning steps on a few raw appointment rows.
raw = [
    {"days_ahead": 14, "clinic": "North", "confirmed": "yes", "missed": 0},
    {"days_ahead": 400, "clinic": "North", "confirmed": "no", "missed": 1},   # impossible value
    {"days_ahead": 3, "clinic": "South", "confirmed": None, "missed": 0},     # missing status
    {"days_ahead": 21, "clinic": "East", "confirmed": "no", "missed": 1},
]

clean = []
for r in raw:
    if not 0 <= r["days_ahead"] <= 365:
        continue                                   # drop rows with impossible values
    row = dict(r)
    row["confirmed"] = row["confirmed"] or "unknown"  # keep missingness as its own category
    clean.append(row)

clinics = sorted({r["clinic"] for r in clean})
for r in clean:
    one_hot = [1 if r["clinic"] == c else 0 for c in clinics]   # one-hot encoding
    print(r["days_ahead"], dict(zip(clinics, one_hot)), r["confirmed"], "label:", r["missed"])

14 {'East': 0, 'North': 1, 'South': 0} yes label: 0
3 {'East': 0, 'North': 0, 'South': 1} unknown label: 0
21 {'East': 1, 'North': 0, 'South': 0} no label: 1

Three of the four rows survive. The impossible 400-day booking is gone, the missing confirmation status has become an explicit "unknown", and the clinic name has become three 0-or-1 columns a model can use. Real preparation is the same idea repeated across hundreds of thousands of rows and dozens of fields, which is why AWS offers tools such as SageMaker Data Wrangler and AWS Glue for it.

How much data is enough?

Jonah asked how many rows a model needs. There is no single number, but Noor gave three rules of thumb. First, the rarer the outcome, the more data you need: with only about 7 missed appointments in every 100, 410,000 rows give roughly 29,000 misses to learn from, which is plenty, but a model for a rare surgical complication might have only dozens of examples. Second, the more features and the more flexible the model, the more examples it needs to avoid memorizing noise. Third, the data must cover the situations the model will meet: three years of history from all 11 clinics includes holidays, winter storms and a clinic move, so the model has seen them. Data from one clinic in one summer would not prepare it for a January snowstorm in Lethbridge.

Quality also beats quantity. A smaller set with accurate labels and consistent definitions usually produces a better model than a huge set full of errors. Noor spent a full afternoon with Andre reviewing 200 randomly chosen records by hand, checking each label against the clinic's paper day sheets. She found that one clinic had marked every walk-in as a booking that was missed, which would have taught the model nonsense about that clinic. Fixing one recording habit improved the data more than adding another year of rows would have.

Where the data lives on AWS

For the assessment, Kwame placed the prepared appointment file in a dedicated Amazon S3 bucket in the ML account, which is where SageMaker AI and most AWS ML services expect to find training data. Larger pipelines often use AWS Glue to catalogue and transform data from several sources, and Amazon Athena to query files in S3 with SQL during exploration. Image and audio data is also stored in S3, usually as one object per file with a separate table describing labels. Keeping raw exports, cleaned data and training splits in clearly separated prefixes, with versioning turned on, means anyone can see later exactly which data trained which model, which lesson 8 shows is essential.

Imbalanced data

The appointment data has a property that shapes everything that follows: only about 7 in every 100 rows are misses. Data where one class is much rarer than the other is called imbalanced. Models trained on it can learn to predict the common class almost every time, and the usual accuracy score hides the problem, as lesson 5 shows. Preparation can help. Oversampling repeats or synthesizes examples of the rare class, undersampling uses fewer examples of the common class, and many algorithms accept class weights that make errors on the rare class count more during training. Noor used class weights, because they keep every real row in the data and are easy to explain. Whatever method is used, the validation and test sets must keep the real-world balance, so that measured performance reflects what the model will face.