Collecting and Sourcing Data

One table, one grain, one moment in time

Collecting data for machine learning is not the same as collecting data for analysis. An analyst can join whatever is useful and interpret the result. You are building a training table where every row must represent exactly one prediction moment, with only the information that existed at that moment.

Two requirements carry nearly all the difficulty. The table must have a single consistent grain, meaning every row describes the same kind of thing. And it must be point-in-time correct, meaning no value in a row was recorded after the moment the prediction would have been made.

Where data comes from, which formats exist and how sources differ is covered in data types, sources and collection. The concern here is narrower: turning those sources into a table a model can legitimately be trained on.

Grain is the first thing to fix

The grain is the answer to “what does one row mean”, and it follows directly from the unit chosen during problem framing. One row per customer. One row per customer per month. One row per transaction.

Mixing grains is the most common structural bug in a training table, and it usually arrives through a join. Join a customer table to an orders table and you no longer have one row per customer; you have one row per order, with customer attributes repeated. Fit a model on that and customers with many orders carry proportionally more weight, silently.

import pandas as pd

orders = pd.DataFrame({
    "customer_id": [1, 1, 2, 2, 3],
    "order_ts": pd.to_datetime(["2026-01-10", "2026-03-04", "2026-02-01",
                                "2026-04-18", "2026-03-22"]),
    "order_value": [120.0, 340.0, 80.0, 95.0, 410.0],
})

print(orders.groupby("customer_id").size().to_dict())
# {1: 2, 2: 2, 3: 1}

print("duplicate grain rows:", int(
    orders.duplicated(subset=["customer_id", "order_ts"]).sum()))
# duplicate grain rows: 0

Make that duplicated check a habit. After every join, assert that the intended key is unique. One line, and it catches a class of bug that otherwise shows up as an unexplained accuracy improvement.

When you need customer-level rows from transaction data, aggregate deliberately: count, sum, mean, days since last, over a defined period. Aggregation is a choice about what the model sees, not a formatting step.

Point-in-time correctness

Here is the part that separates a training table from a report. Suppose you want to predict order value using the customer’s credit score, and credit scores are refreshed irregularly.

A plain join on customer_id attaches every score to every order, including scores recorded months after the order:

credit = pd.DataFrame({
    "customer_id": [1, 1, 2, 2, 3],
    "scored_ts": pd.to_datetime(["2025-12-01", "2026-02-15", "2026-01-05",
                                 "2026-03-30", "2026-04-01"]),
    "credit_score": [640, 710, 580, 530, 690],
})

naive = orders.merge(credit, on="customer_id")
print(len(naive))   # 9

Five orders became nine rows, and several of those rows pair an order with a score that did not exist yet. The model would learn from the future.

merge_asof fixes both problems at once, matching each order to the most recent score recorded strictly before it:

pit = pd.merge_asof(
    orders.sort_values("order_ts"),
    credit.sort_values("scored_ts"),
    left_on="order_ts", right_on="scored_ts",
    by="customer_id", direction="backward",
)
print(pit[["customer_id", "order_ts", "scored_ts", "credit_score"]]
      .to_string(index=False))

#  customer_id   order_ts  scored_ts  credit_score
#            1 2026-01-10 2025-12-01         640.0
#            2 2026-02-01 2026-01-05         580.0
#            1 2026-03-04 2026-02-15         710.0
#            3 2026-03-22        NaT           NaN
#            2 2026-04-18 2026-03-30         530.0

Five rows, as it should be, and customer 3 has no credit score because theirs was recorded on 1 April, after their 22 March order. That NaN is correct. It is what you genuinely knew at the time, and the temptation to fill it with the April score is exactly the mistake the whole technique exists to prevent.

Missing values created by point-in-time logic are informative rather than annoying. They tell you how often a feature is actually available at prediction time, which is a question worth answering before you build a model that depends on it.

Volume and sources when collecting data for machine learning

Three practical judgements.

Volume. Scale with pattern complexity rather than ambition. Keep enough positive examples in particular: a few hundred thousand rows with forty positives is a small dataset wearing a large one’s clothes.

Time span. Cover at least one full cycle of whatever seasonality your domain has. Retail needs a complete year. Skip this and the model treats December as normal.

Source priority. Internal transactional systems first, since they are the most reliable and the most likely to still exist next year. Then internal logs and event streams. Then third-party or purchased data, which brings licensing terms, coverage gaps and a dependency you do not control.

Record where it came from

Write down, per source: the query or extract used, the date range, the extraction date, the row count, the owning team, and any filter applied. Five minutes of work that saves a day six months later, when the model degrades and you need to know whether the source changed or the world did.

Two things belong in that record for reasons beyond convenience. Any personal data in your training table carries legal obligations, and “we were not sure which rows were personal” is not a position worth being in. And every filter you applied is a selection decision: dropping rows with missing postcodes may quietly remove an entire customer segment, which is the non-representative data problem from the main challenges of machine learning. The temporal join above is documented under pandas.merge_asof.

Frequently Asked Questions

What is point-in-time correctness in machine learning?

Every value in a training row must have been knowable at the moment the prediction would have been made. Joining a credit score recorded after an order means training on the future. Use pandas.merge_asof with direction="backward" to attach only the most recent prior value.

How much data do you need to train a machine learning model?

It depends on pattern complexity and the number of positive examples rather than total rows. A dataset of 500,000 rows containing 40 positives is small. Cover at least one full seasonal cycle, and retrain with different random seeds: widely varying scores mean you need more.

What is data grain and why does it matter?

The grain is what a single row represents: one customer, one customer-month, one transaction. Joins silently change it, so a customer table joined to orders now weights frequent buyers more heavily. Assert key uniqueness after every join to catch this.

Key Takeaways

• Fix the grain before joining anything, and assert key uniqueness after every merge so a silent row explosion cannot reach your model.
• Use pandas.merge_asof with a backward direction for any feature whose value changes over time, rather than a plain join on an identifier.
• Treat missing values produced by point-in-time logic as correct information about availability, not as gaps to be filled with later data.
• Count your positive examples rather than your rows when judging whether a dataset is large enough.
• Record the query, date range, extraction date and row count for every source, because you will need them when the model degrades months later.