Data collection and ingestion: building a reliable data foundation

Part 2 of the data science lifecycle. With the analytical question, target, and constraints defined, the next task is to acquire the right data and move it reliably into an environment where it can be analyzed. Collection identifies the sources and fields needed for the question. Ingestion is the controlled movement of data from those sources into a platform where it can be validated, tracked, and prepared. These practices apply whether the source is a customer database, a public API, an IoT stream, a financial feed, or a healthcare system.

1. Start with the business question and source inventory

Do not begin by connecting every available database. Start with the decision from part 1: what population, event, and time window does the analysis require? Then make a source inventory that records the system owner, business purpose, data domain, refresh cadence, delivery method, expected volume, sensitivity, retention requirement, and the person who can explain the fields.

Useful sources depend on the business question. A retail analysis may need orders, products, inventory, web events, and customer interactions. A financial-services use case may combine accounts, transactions, payment events, and market data. An industrial use case may use equipment telemetry, maintenance records, production batches, and quality inspections. A healthcare payer example may combine eligibility, claims, provider, authorization, and pharmacy data. Across domains, systems rarely share identical definitions or keys, so identify the authoritative source for each concept before joining anything.

A source inventory should also record what is missing. An order extract without returns may overstate net sales. A financial transaction feed without reversals may distort balances. A sensor feed without calibration or maintenance context may create misleading anomaly signals. In a healthcare example, a claims extract without adjustment history may be unsuitable for a paid-claims trend. Write down these limits before treating an extract as complete.

2. Understand common data sources and formats

Most analytical platforms combine data from relational databases, cloud warehouses, application APIs, event brokers, object storage, public datasets, and partner file exchanges. In a healthcare payer example, common administrative EDI transactions include the 834 for enrollment, 837 for claims, 835 for remittance advice, 270/271 for eligibility, 276/277 for claim status, and 278 for authorization-related services. The same source-inventory method applies to retail orders, banking transactions, web events, manufacturing sensors, and customer-support systems; only the domain-specific formats change.

For healthcare interoperability, FHIR APIs may expose resources such as Patient, Coverage, ExplanationOfBenefit, Claim, Organization, and Practitioner, depending on the implementation and access agreement. This is one domain-specific API example—not a requirement for a general data science pipeline. For any API, understand its schema, authentication, pagination, rate limits, versioning, and permitted use before building the extractor.

Files remain common because employers, vendors, clearinghouses, and legacy platforms often exchange scheduled extracts. CSV is easy to inspect but weakly typed. JSON represents nested API payloads. XML appears in several established exchange formats. Parquet is efficient for analytical storage because it is columnar and preserves a schema. The extension alone does not define the contract: document encoding, delimiters, compression, null conventions, date formats, code sets, and schema version too.

3. Choose the ingestion pattern deliberately

Batch ingestion is appropriate when scheduled freshness is sufficient: nightly orders, daily account transactions, weekly product catalogs, or periodic sensor summaries. Define the schedule, delivery window, expected volume, and what happens when a delivery is late. A batch process should be restartable for a specific business date or source batch rather than requiring an operator to rerun everything.

API-based ingestion is useful when a consumer needs request-and-response access or a source publishes resources through a managed interface. Plan for authentication and token renewal, pagination, rate limits, timeouts, retries with backoff, and source throttling. Store the request window and continuation state so a partial page sequence is not mistaken for a complete pull. Respect the API's permitted purpose and access scope.

An API extraction pattern in Python

The example below illustrates a common REST pattern: follow the server-provided next-page URL, set a timeout, retry transient failures, and keep the access token outside the source code. Real APIs differ: some use cursor parameters, some return a Link header, and some require a refreshed token. Follow the provider's pagination and rate-limit contract rather than assuming every endpoint behaves alike.

import os
import requests
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry

retry = Retry(
    total=5,
    backoff_factor=1,
    status_forcelist=[429, 500, 502, 503, 504],
    allowed_methods=["GET"],
    respect_retry_after_header=True,
)
session = requests.Session()
session.mount("https://", HTTPAdapter(max_retries=retry))

token = os.environ["SOURCE_API_TOKEN"]
url = "https://api.example.com/v1/orders"
headers = {"Authorization": f"Bearer {token}"}
all_records = []

while url:
    response = session.get(url, headers=headers, timeout=(5, 30))
    response.raise_for_status()
    page = response.json()
    all_records.extend(page["results"])
    url = page.get("next")  # Use the API's documented cursor/next link

print(f"Retrieved {len(all_records)} records")

A robust implementation also records the page or cursor reached, request start and end times, response status, and source request ID where available. Do not log bearer tokens or full payloads containing personal information. If the process fails halfway through, resume from a persisted cursor only if the API guarantees that cursor remains valid; otherwise restart the bounded extraction and rely on idempotent landing to prevent duplicates.

Event-driven ingestion is useful when a downstream process needs lower-latency updates. Events might represent an order being placed, a payment being authorized, a device crossing a temperature threshold, or a claim status changing. Design for at-least-once delivery: a consumer may receive the same event more than once. Use a stable event identifier, idempotent processing, an ordering strategy where required, and a dead-letter path for messages that cannot be processed. Do not promise exactly-once business outcomes merely because a broker offers delivery features.

ETL transforms data before loading it into its destination. ELT lands source data first and transforms it inside the analytical platform. Neither is universally better. ETL may be appropriate when sensitive fields must be minimized before they reach a destination. ELT can preserve source history and use scalable warehouse compute, provided raw access is tightly controlled. The architecture should follow security, latency, audit, and operating constraints—not fashion.

4. Define the data contract before building the job

A data contract is an agreement between the producer and consumer about what a delivery means. At minimum, define the business grain, schema and version, field definitions, data types, required and optional fields, code sets, primary or natural keys, effective-date rules, timezone, null semantics, delivery cadence, expected volume, and correction behavior. Name the owner on both sides and the process for communicating a breaking change.

Grain is fundamental to analytical correctness. One row may represent a customer, an order, an order line, a product, an account transaction, or a sensor reading. These are not interchangeable. If an order-level total is joined to several order lines and then summed, the total may multiply. In a healthcare example, a claim header and its service lines also represent different grains. Put the grain in the contract and preserve it in table names and documentation.

Every extract needs an as-of time and an event or business-effective time when those differ. An order may be placed today, shipped tomorrow, and returned next week. A financial transaction may be posted later than its event date. Store event time, source update time, ingestion time, and effective dates separately where available. This makes late-arriving data explainable instead of silently rewriting history.

5. Build a repeatable, restartable pipeline

A production ingestion job needs more than a SELECT statement or a file copy. A useful sequence is: register the expected delivery; authenticate and retrieve it; validate transport and file integrity; land the original payload; record metadata; parse and validate the schema; apply basic technical checks; publish a success or quarantine outcome; and emit operational metrics. Keep extraction separate from downstream business transformations so a failed model does not force a new source pull.

Use watermarks or checkpoints to identify what has already been collected. A watermark might be a source update timestamp, monotonically increasing identifier, change-data-capture position, or a partner's sequence number. The correct choice depends on source behavior. A timestamp-only incremental pull can miss records when timestamps are backdated, have low precision, or are updated out of order. Reconcile incremental loads against periodic full counts or bounded historical re-reads.

Incremental extraction with SQL

A simple timestamp watermark is easy to understand, but production code should use a deliberate overlap window and a stable tie-breaker when the source supports it. The example shows the shape of a bounded incremental query; parameter syntax and merge behavior vary by database. Persist the high watermark only after the batch has been validated and committed successfully.

-- :low_watermark and :high_watermark are bound parameters.
-- The overlap helps catch late updates; deduplicate by order_id
-- and source_updated_at in the staging step.
SELECT
    order_id,
    customer_id,
    order_status,
    order_total,
    source_updated_at
FROM source_orders
WHERE source_updated_at > :low_watermark
  AND source_updated_at <= :high_watermark
ORDER BY source_updated_at, order_id;

For a mutable source table, a timestamp pull captures changed rows only if every relevant update advances the timestamp reliably. Change data capture (CDC) is often preferable when the source exposes a transaction log or change stream, because it can represent inserts, updates, and deletes with an ordered position. CDC still needs retention monitoring: if the consumer falls behind the source log retention window, it may need a controlled reinitialization. A watermark is a recovery checkpoint, not proof that every change was captured.

Make reruns safe. A common pattern is to write each delivery to a unique batch location, validate it, then publish it to the curated landing area only after checks pass. Record a batch ID and source file checksum. Use merge or upsert logic keyed to the business event where appropriate, and retain the original delivery for audit. If a partner resends a file, the pipeline should recognize the duplicate delivery rather than double-count its orders or transactions.

6. Land raw data with lineage and provenance

Keep an immutable or access-controlled raw landing layer when policy and retention rules allow it. It should preserve what the source sent, not a cleaned interpretation of it. Alongside the payload, capture source system, source object or endpoint, partner, file name, batch ID, extraction window, received timestamp, ingestion timestamp, schema version, checksum, record count, job version, and processing status. These fields let an engineer answer which delivery produced a row and which code processed it.

A practical lake or warehouse layout often separates raw, validated, and curated zones. Raw preserves the received representation. Validated data has passed structural and basic quality checks while remaining close to source meaning. Curated data is standardized for analysis and downstream products. These are logical responsibilities; they do not require three separate storage products. Apply access controls at each layer and avoid broad access to raw PHI simply because it is convenient.

Lineage should be traceable in both directions. From a dashboard value, an analyst should be able to identify the curated table, transformation version, source batches, and upstream systems. From a source delivery, an operator should be able to see which tables, features, models, and reports it affected. This is essential when a vendor corrects a file or a source system reprocesses historical records.

7. Protect sensitive data and enforce allowed use

Data collection is a governance decision as much as an engineering task. Confirm the permitted purpose, legal or contractual basis, minimum-necessary fields, retention period, and approved destinations before moving personal, financial, confidential, or regulated data. In a healthcare implementation, HIPAA requirements may also apply depending on the organizations, roles, data, and use. A data pipeline does not create permission to use information for a new purpose. Involve privacy, security, compliance, and data owners when the use or sharing arrangement is unclear.

Use encryption in transit and at rest, managed secrets, least-privilege service identities, network restrictions, and auditable access. Separate production credentials from development. Mask or tokenize sensitive identifiers in lower environments, and use synthetic or appropriately de-identified data for testing where feasible. Avoid personal or confidential data in job logs, exception messages, filenames, and monitoring labels. Define retention and secure deletion for both payloads and temporary copies.

Allowed use belongs in metadata. Record the source, data owner, classification, approved purpose, restrictions, and applicable agreement or policy reference. A dataset that is technically accessible is not automatically approved for every analytic question. Recheck permissions when a new feature, vendor, model, or downstream consumer is introduced.

8. Validate quality at the ingestion boundary

Run checks before data is released to downstream users. Structural checks confirm that the file opens, the schema is recognized, and required columns exist. Completeness checks compare received files and records with expected deliveries. Type and domain checks validate dates, numeric values, code sets, and allowed status values. Key checks identify missing identifiers and duplicate business events. Referential checks test whether claim lines connect to known headers or provider identifiers where the relationship is expected.

Reconciliation is particularly valuable. Compare source and landed record counts, control totals, billed and paid amounts, file-level totals, and counts by status or business date. For financial feeds, preserve the source control totals and explain any accepted difference. A job that exits with code zero is not proof that the data is complete or correct.

Not every failed row should stop the whole batch. Define severity: reject the delivery for a broken schema or missing control total; quarantine invalid records when safe partial processing is allowed; warn on noncritical anomalies; and publish only when the agreed acceptance criteria are met. Keep rejected records protected, report the reason, and provide a controlled remediation and replay path.

9. Make operations observable

Monitor freshness, completeness, volume, schema drift, processing duration, retry count, rejected records, and reconciliation differences. Alert on business impact, not just infrastructure noise. A claims feed that is three hours late before a payment run deserves a different response from a low-priority reference file that is a day late.

Give each pipeline an owner, runbook, service expectation, escalation path, and recovery procedure. The runbook should explain how to identify the affected batch, whether a rerun is safe, how to replay quarantined records, and how to notify downstream consumers. Track recurring incidents and work with the source owner to correct the contract rather than adding another permanent exception.

Orchestration: coordinate work, do not hide logic

An orchestrator such as Apache Airflow can schedule tasks, express dependencies, manage retries, and expose run history. A typical daily workflow might wait for an order file, validate its checksum and schema, load a staging table, run reconciliation, and publish a completion event. Keep business transformations in tested Python or SQL modules and let the DAG coordinate them. Tasks should be small, observable, and safe to retry; avoid putting the entire pipeline into one opaque task or using the scheduler as a substitute for data-quality rules.

Choose storage with the workload in mind. A relational database or warehouse is useful for structured queries and governed access. Object storage is economical for large raw files and historical payloads. A lakehouse combines object storage with table formats and transaction features that support concurrent analytical workloads. Parquet is often a good analytical file format because columnar reads can scan only required fields, but it is not a replacement for a table catalog, access controls, compaction, or schema-evolution policy. The right platform depends on query patterns, scale, latency, team skills, and operating cost.

A practical architecture decision guide

Use scheduled batch when the business can tolerate a known delay and the source provides complete extracts. Use API pulls when the source is managed through a service interface and the required volume fits its limits. Use CDC when you need reliable changes from a mutable database and can operate the log consumer. Use event streaming when multiple consumers need low-latency events and the organization can support broker operations, schema governance, replay, and consumer monitoring. Streaming is not automatically more modern or more accurate; it adds operational complexity and is justified by a real freshness requirement.

10. A worked example: ingesting daily commerce data

Consider an online retailer receiving daily order files from a commerce platform and inventory updates from a warehouse system. The ingestion service registers each delivery, verifies transport and checksum, stores the original payload in a controlled raw zone, and records the source, batch, timestamps, schema version, and record count. A parser validates the structure and required fields before publishing accepted records to a validated zone. The same design could ingest a healthcare 837 claims feed, but the domain-specific parser and governance rules would differ.

The order pipeline preserves order-header and order-line grain separately. It reconciles accepted records to delivery control totals, quarantines malformed rows, and makes batch status visible to operations. Inventory events are linked through stable product and location keys rather than being appended as if they were order lines. Corrections are retained as new events or versioned changes according to the source contract, rather than silently overwriting history.

An analyst can now calculate order volume, fulfillment time, and product availability while tracing each result back to the source delivery and transformation version. That distinction is the difference between a dataset that merely loads and one that can support a defensible business answer.

A practical ingestion readiness checklist

Before calling an ingestion pipeline production-ready, confirm eight things: the source owner and permitted purpose are known; grain, keys, schema, and effective dates are documented; the batch, API, or event pattern meets the freshness need; retries, checkpoints, idempotency, and replay are designed; raw payload and lineage are retained appropriately; sensitive-data access and retention are approved; quality rules and reconciliation are defined; and monitoring, ownership, and a recovery runbook are in place.

The pull is complete only when it is repeatable, explainable, and permitted. The next part of this series takes the landed data and deals with what is wrong inside it: missing values, duplicate keys, outliers, and types that do not match.