Common sources and how to read them robustly: Source Python Watch out for CSV csv.DictReader, pandas.readcsv delimiters, quotes, encodings (utf-8-sig for BOM) JSON / JSON Lines json.load, line-by-line json.loads nested objects → flatten REST API requests / httpx pagination, rate limits, retries Database DB-API cursor, SQLAlchemy fetch in chunks; read from a replica Excel openpyxl, pandas.readexcel merged cells, header rows Extract rules of thumb Stream large files instead of loading them fully — generators keep memory flat. Keep the raw copy (file or table) so you can replay transforms. Capture metadata: source name, extract time, row count, checksum. For APIs, follow next links / cursors until exhausted and back off on HTTP 429.
Key Takeaways
Use generators to stream large inputs instead of reading everything into memory.
Handle encodings and BOMs explicitly — they are the #1 CSV surprise.
Flatten nested JSON into columns before loading into tables.
Paginate APIs until no cursor remains and retry transient failures with backoff.