Most of the errors that end up inside a model walk in through the front door, at the moment data is read. Not in the algorithm, not in the tuning, in the twenty lines that pull a file, call an endpoint or run a query.

Those twenty lines get almost no attention. They are usually written once, in a hurry, at the start of a project, and then never revisited even as the model built on top of them gets six months of careful work. I have lost more accuracy to a badly written ingestion step than to any hyperparameter I ever tuned, and the loss is worse because ingestion bugs rarely announce themselves. A wrong learning rate gives you a bad score. A wrong data pull gives you a good score on the wrong data.

Churn project status going into this part

Last part we had the Telco Customer Churn table loaded into a dataframe, 7,043 rows and 21 columns, and we spent the whole post on what pandas does to it once it is in memory: vectorisation, merges, reshaping, and a memory footprint of 7.79 MB that had no business being that large. We took the file for granted. This part goes back one step and treats the read itself as the thing that can go wrong.

By the end of this part the project has three inputs rather than one. A CSV of customer accounts, which is what we already had. A paginated API pull of support ticket counts per customer, which will become one of the strongest churn features in the lifecycle sense of the word. And a SQL pull of contract history from a relational database. Real churn projects always look like this. The account table is never enough on its own, and the interesting signal lives in systems that were built for billing or support rather than for modelling.

Everything below was run on Python 3.10.12 with pandas 2.3.3, NumPy 2.2.6, requests 2.34.2 and SQLAlchemy 2.0.51. Where numbers appear they were measured on a 7,043 row copy of the churn table, so you should expect the same shape of result rather than the same millisecond.

Four ways data arrives, and their failure modes

Almost every dataset you will ever model arrives by one of four routes, and each route has a signature failure that is worth memorising. Knowing which route you are on tells you which check to write first, which matters because you cannot check everything and still ship.

Notice a pattern in that last column. Every first check is an assertion about a count or a type, not an inspection of values. Value level checks come later, and they are what exploratory analysis is for, as I covered in the analyst post on exploratory data analysis. Structural checks come first because a structural failure invalidates every value check you run afterwards. If half your rows are missing, a clean distribution plot tells you nothing except that the half you kept looks reasonable.

My rule is that an ingestion function is not finished until it raises on its own output. Not logs a warning, raises. Warnings scroll past in a scheduler log and nobody reads them. An exception stops the pipeline and someone gets paged, which is exactly the outcome you want when the alternative is a model trained on 20 percent of the customer base.

flowchart TD
  A[Source system] --> B{Bounded and small}
  B -->|Yes| C[Single read with dtype schema]
  B -->|No| D{Paginated or chunked}
  D -->|API| E[Session with retry and page loop]
  D -->|SQL| F[read_sql with chunksize]
  C --> G[Assert row count and dtypes]
  E --> G
  F --> G
  G --> H[Write interim copy that keeps dtypes]
  H --> I[Feature engineering]

Pulling from a paginated API without losing rows

Our churn project needs support ticket counts per customer, and that data sits behind an internal endpoint that returns 500 records at a time with a cursor. Here is the version almost everyone writes first, and I include it because it is what I wrote first too.

import requests, pandas as pd

BASE = 'https://internal.example/support-tickets'

r = requests.get(BASE, params={'limit': 500}, timeout=10)
data = r.json()
df = pd.DataFrame(data['results'])
print('rows pulled:', len(df))

Tested with pandas 2.3.3, requests 2.34.2 and SQLAlchemy 2.0.51 on Python 3.10.12.

Two things are wrong with it. First, it reads one page and stops, so it will happily return 500 rows out of 2,500 forever. Second, it never checks the status code. When the endpoint rate limits you, the body is still valid JSON, it just does not contain what you asked for. Run it against an endpoint that answers with a 429 and you get this.

Traceback (most recent call last):
  File '/tmp/naive.py', line 5, in <module>
    df = pd.DataFrame(data['results'])
KeyError: 'results'

A KeyError on a key you know exists is nearly always an error body you did not check for.

That KeyError is the friendly version of the bug, because at least it stops. Now imagine the endpoint returns a valid but empty page instead of an error. You get a dataframe with zero rows, no exception at all, and a feature column full of zeros downstream.

Here is the version I use now. It mounts a retry policy onto a session, loops over pages until the cursor comes back empty, and refuses to exit quietly if it hits the page limit. That last detail is the eleven lines that would have saved me nine days.

import requests, pandas as pd
from requests.adapters import HTTPAdapter
from urllib3.util import Retry

BASE = 'https://internal.example/support-tickets'

def make_session():
    retry = Retry(
        total=5,
        backoff_factor=0.5,
        status_forcelist=[429, 500, 502, 503, 504],
        allowed_methods=frozenset(['GET']),
        respect_retry_after_header=True,
    )
    s = requests.Session()
    s.mount('http://', HTTPAdapter(max_retries=retry))
    s.mount('https://', HTTPAdapter(max_retries=retry))
    return s

def fetch_all(session, limit=500, max_pages=100):
    cursor, pages, frames, seen = 0, 0, [], 0
    while pages < max_pages:
        r = session.get(BASE, params={'cursor': cursor, 'limit': limit}, timeout=30)
        r.raise_for_status()
        payload = r.json()
        batch = payload['results']
        frames.append(pd.DataFrame(batch))
        seen += len(batch)
        pages += 1
        print(f'page {pages}: {len(batch)} rows, running total {seen}')
        cursor = payload.get('next_cursor')
        if cursor is None:
            break
    else:
        raise RuntimeError(f'stopped at max_pages={max_pages}, cursor still {cursor}')
    return pd.concat(frames, ignore_index=True)

tickets = fetch_all(make_session())
print('final shape:', tickets.shape)
print('duplicate customerIDs:', tickets['customerID'].duplicated().sum())

Run against an endpoint that returns a 429 on the first request, then paginates 2,500 records.

page 1: 500 rows, running total 500
page 2: 500 rows, running total 1000
page 3: 500 rows, running total 1500
page 4: 500 rows, running total 2000
page 5: 500 rows, running total 2500
final shape: (2500, 3)
duplicate customerIDs: 0

The 429 never appears in the output because the retry policy absorbed it and re-sent the request.

Three details in that code do most of the work. respect_retry_after_header means urllib3 honours the wait the server asks for rather than guessing, which keeps you from being blocked entirely. backoff_factor of 0.5 gives increasing gaps between attempts instead of hammering a struggling service. And the while and else construction, which is one of the few genuinely useful uses of that Python feature, means the RuntimeError fires only when the loop hits its bound without breaking, so an unbounded dataset cannot silently become a truncated one.

One choice I would push back on if I saw it in review is a bare except around the whole pull. People add it because the job kept failing overnight and they wanted it to finish. What it actually does is convert a loud, fixable failure into a quiet, permanent one. If a pull cannot complete, I want the pipeline dead and the previous day of data still in place, not a fresh table with a hole in it.

SQL pulls into pandas, done safely

Contract history for our churn customers lives in a relational database, and pandas will read it directly. The temptation is to build the query with an f-string because it is quick and it reads well. Do not. Here is what happens the moment a value contains anything the SQL parser cares about, and hyphens in a contract label are enough.

contract = 'Month-to-month'
with engine.connect() as conn:
    df = pd.read_sql(f'SELECT * FROM churn WHERE Contract = {contract}', conn)

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near 'to': syntax error
[SQL: SELECT * FROM churn WHERE Contract = Month-to-month]

Read the echoed SQL line, not the exception name. It shows you exactly what the database received.

Everybody knows string formatting into SQL is a security problem. Fewer people notice it is a correctness problem first. This one at least crashed. A value with an apostrophe in a customer name will sometimes produce valid SQL that returns the wrong rows, and that version does not crash at all. Parameterised queries fix both problems in the same line of code.

import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine('sqlite:///t.db')

q = text('SELECT customerID, tenure, MonthlyCharges, Churn '
         'FROM churn WHERE Contract = :contract')

with engine.connect() as conn:
    df = pd.read_sql(q, conn, params={'contract': 'Month-to-month'})
print('month-to-month rows:', len(df))

total = 0
with engine.connect() as conn:
    for chunk in pd.read_sql('SELECT * FROM churn', conn, chunksize=2000):
        total += len(chunk)
print('rows via chunksize=2000:', total)

SQLAlchemy 2.0.51 | pandas 2.3.3
month-to-month rows: 3848
rows via chunksize=2000: 7043

Named bind parameters with text, and a chunked read that never holds the full result set in memory.

chunksize is worth understanding properly because it changes what read_sql returns. Without it you get a dataframe. With it you get an iterator, and pandas pulls rows from the cursor in batches, so peak memory is roughly one chunk rather than the whole table. On 7,043 rows this is pointless. On 40 million rows of billing events it is the difference between a job that runs and a job that is killed by the kernel out of memory handler with no traceback whatsoever.

One more habit worth forming. Push aggregation into SQL rather than pulling raw rows and grouping in pandas. Databases are extremely good at GROUP BY, and moving 40 million rows across a network so that pandas can turn them into 7,043 numbers is a waste of both. My analyst post on GROUP BY and window functions covers the SQL side of this in detail, and everything there applies unchanged when the consumer is a model rather than a dashboard.

File formats measured on the churn table

Once data is pulled you have to put it somewhere before feature engineering. Format choice looks like a preference and is actually a performance and correctness decision. I wrote the same 7,043 row churn table out in five formats and read each one back five times, taking the fastest read to remove scheduler noise.

My verdict on this table is blunt. Use CSV at the boundary, because that is what other systems produce and consume and you rarely get a choice. Use Parquet for every interim copy inside your project. Avoid JSON as a storage format entirely, and avoid pickle for anything that outlives a single session.

pickle looks like the winner on those numbers, at 2.3 milliseconds, and it is a trap. A pickle file is executable Python, so loading one you did not create is a code execution risk, and it is tied to the library versions that wrote it. Upgrade pandas six months from now and your archived pickles may not load. I use pickle for a cache that lives inside one script run and gets deleted afterwards, and never for anything a colleague might open.

Parquet is the format I did not measure here and would still recommend above all of these for interim storage. It is columnar, it compresses well, and unlike CSV it stores the dtype alongside the data, so a float32 column comes back as float32 without you passing a schema. Two practical notes from the pandas documentation. read_parquet needs the pyarrow library installed, and in pandas 3.1 the fastparquet engine is deprecated because that project has been retired, so pass engine as pyarrow or leave the argument off entirely.

Encoding, dates and numeric columns that lie

Back to our churn CSV, which contains a small trap that catches nearly everyone who meets this dataset. TotalCharges looks numeric. It reads as object.

import pandas as pd

df = pd.read_csv('telco.csv')
print('shape:', df.shape)
print('TotalCharges dtype:', df['TotalCharges'].dtype)
blanks = pd.to_numeric(df['TotalCharges'], errors='coerce').isna().sum()
print('rows where TotalCharges will not parse:', blanks)
df['TotalCharges'].astype(float)

shape: (7043, 21)
TotalCharges dtype: object
rows where TotalCharges will not parse: 11
ValueError: could not convert string to float: ' '

Eleven rows out of 7,043 contain a single space instead of a number, and one space is enough to make the whole column a string.

Eleven rows in 7,043 is 0.16 percent of the data, and it costs you the dtype of an entire column. Those eleven customers have a tenure of zero, meaning they signed up and have not been billed yet, so the blank is meaningful rather than corrupt. That distinction matters later, because dropping them removes exactly the newest customers, and new customers churn differently from everyone else.

Fixing this properly means declaring what you expect rather than letting pandas guess. Pass a dtype schema, pass na_values so blanks become nulls, and convert the awkward column explicitly with errors set to raise so a new kind of bad value in next month’s file stops the pipeline instead of silently becoming a null.

SCHEMA = {
    'customerID': 'string', 'gender': 'category', 'SeniorCitizen': 'int8',
    'Partner': 'category', 'Dependents': 'category', 'tenure': 'int16',
    'Contract': 'category', 'PaymentMethod': 'category',
    'MonthlyCharges': 'float32', 'Churn': 'category',
}

df = pd.read_csv(
    'telco.csv',
    dtype=SCHEMA,
    na_values=[' ', '', 'NA', 'N/A', 'null'],
    keep_default_na=True,
)
df['TotalCharges'] = pd.to_numeric(df['TotalCharges'], errors='raise').astype('float32')

print('TotalCharges dtype:', df['TotalCharges'].dtype)
print('TotalCharges nulls:', df['TotalCharges'].isna().sum())
print('memory:', round(df.memory_usage(deep=True).sum()/1024/1024, 2), 'MB')

TotalCharges dtype: float32
TotalCharges nulls: 11
memory: 4.74 MB

Same file, same 7,043 rows, 39 percent less memory and a correctly typed numeric column.

Dates deserve the same treatment. Never let pandas infer date formats across a whole column, because a file containing both 01/02/2026 and 2026-02-01 will parse without complaint and give you dates a month apart. Pass a format string to pd.to_datetime and let it raise on anything that does not match. Ambiguous day and month ordering is the single most common date bug I see in production data, and it is invisible for the first twelve days of any month.

Encoding is the last of the quiet ones. A UnicodeDecodeError on read is the good outcome, because it stops. The bad outcome is a file that was written as Windows-1252 and read as UTF-8 with errors ignored, which leaves you with mangled customer names that no longer join to anything. If a file comes from a spreadsheet export, check the encoding explicitly rather than assuming, and never pass errors set to ignore just to make a read succeed.

Ingestion setup I would use for this project

For the churn project as it stands, with three sources and a model that will be retrained monthly, here is what I would actually build. One module called ingest, with one function per source, each returning a typed dataframe and each ending in assertions about row count, key uniqueness and dtypes. Each function writes a Parquet copy to a dated folder before returning, so a bad run can be compared against a good one without re-pulling anything.

Schemas live in a separate file as plain dictionaries, not scattered through the read calls, because when a source adds a column you want to change one place. API pulls get a retry policy and a page bound, as above. SQL pulls get parameterised text queries and aggregation pushed into the database. The CSV read gets a dtype schema and an na_values list. None of this is sophisticated, and it takes maybe an afternoon to write, which is a fraction of the time it saves the first time a source changes underneath you.

What I would avoid is the pattern where ingestion lives inside the notebook that also does modelling. It always starts that way and it should not stay that way. Once a read is buried in cell four of a notebook, nobody tests it, nobody reviews it, and it gets copy-pasted into three other notebooks that then drift apart. We will pull all of this into a proper package structure in Part 21, but the discipline starts now, at the point where you have three sources instead of one.

Next, in Part 6, we take these three typed and asserted tables and start building features from them, which is where most of the accuracy in a churn model actually comes from. Ticket counts become ratios, tenure becomes buckets, and contract type becomes something a model can learn from. Get your ingestion assertions in place first, because feature engineering on quietly broken data is the most expensive kind of wasted week there is.

References

• pandas.read_csv API reference, pandas documentation
• pandas.read_parquet API reference, pandas documentation
• Advanced usage, including transport adapters and retries, Requests documentation
• urllib3.util.Retry reference, urllib3 documentation
• Working with engines and connections, SQLAlchemy 2.0 documentation
• Telco Customer Churn dataset, IBM on GitHub

About The Author

Dr. Pranay Jha

Dr. Pranay Jha is a Cloud and AI Consultant with 18+ years of experience in hybrid cloud, virtualization, and enterprise infrastructure transformation. He specializes in VMware technologies, multi-cloud strategy, and Generative AI solutions. He holds a PhD in Computer Applications with research focused on Cloud and AI, has published multiple research papers, and has been a VMware vExpert since 2016 and a VMUG Community Leader.

See author's posts