Where We're Headed

A well-designed data collection system is really a chain of contracts: each stage promises the next that data will arrive in a known shape, at a known frequency, with failures handled gracefully. Let's walk through every layer of that chain, from the moment data is produced to the moment it's ready for analysis, hitting the decisions that actually matter in practice.

Start by Clarifying Requirements

Before writing a single line of pipeline code, the first question is about data volumes and freshness requirements. These two dimensions drive almost every downstream decision:

Volume: Are we talking about thousands of events per day (a nightly batch is probably fine) or millions per second (we need a streaming architecture)?
Freshness: Does a dashboard need to update within seconds, or is overnight acceptable? Real-time requirements are significantly more expensive to build and operate.
Source diversity: Are we pulling from REST APIs, databases, event streams, flat files, or all of the above? Each source has its own failure modes and rate limits.
Downstream consumers: A data science team needs different guarantees than a billing system. Billing cannot tolerate duplicates; a recommendation model probably can.

Getting crisp answers here prevents over-engineering. A common mistake is reaching for Kafka and Spark on day one when a well-structured Airflow DAG pulling from a REST API would serve the business for years.

The Ingestion Layer

Ingestion is where data first touches your system, and it's where most production incidents originate. The core principle is: land raw data first, transform later. Never discard or modify data at the point of collection. This gives you the ability to replay and reprocess when your transformation logic changes.

For batch ingestion, Airflow is the natural orchestrator. A DAG pulling from a third-party API might look like this:

from airflow.decorators import dag, task
from pendulum import datetime

@dag(schedule="@hourly", start_date=datetime(2024, 1, 1), catchup=True)
def ingest_api_events():

    @task(retries=3, retry_delay_seconds=60)
    def extract(logical_date=None):
        # Use logical_date for idempotent, window-based pulls
        start = logical_date
        end = logical_date.add(hours=1)
        records = fetch_api(start_iso=start.isoformat(), end_iso=end.isoformat())
        return records

    @task
    def land_raw(records, logical_date=None):
        # Write to a date-partitioned raw zone in S3 or GCS
        path = f"s3://raw-zone/events/date={logical_date.date()}/hour={logical_date.hour}/"
        write_parquet(records, path)

    land_raw(extract())

ingest_api_events()

Notice catchup=True. This is important. If the pipeline goes down for six hours, Airflow will backfill those windows automatically when it recovers. That only works if your extraction logic is idempotent, meaning running it twice for the same window produces the same result. Design for this from the start.

For streaming ingestion, Kafka is the standard choice. Producers write events to topics; consumers read at their own pace. The key architectural benefit is decoupling: if your processing layer falls behind, Kafka holds the backlog. Compare this to a direct database write approach where a slow consumer creates backpressure all the way to the source.

Transformation and Aggregation

The tradeoff we face in transformation is between batch and streaming processing. Batch jobs are simpler to reason about, test, and reprocess. Streaming jobs give fresher data but require handling late arrivals and out-of-order events explicitly.

For batch transformations, dbt has become the practical standard for SQL-based teams. It handles dependency ordering, incremental models, and testing in a single tool:

-- models/aggregated/daily_user_events.sql
{{ config(materialized='incremental', unique_key='user_id || date') }}

SELECT
    user_id,
    DATE(event_ts) AS date,
    COUNT(*) AS event_count,
    COUNT(DISTINCT session_id) AS session_count
FROM {{ ref('stg_events') }}
{% if is_incremental() %}
    WHERE DATE(event_ts) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
{% endif %}
GROUP BY 1, 2

The three-day lookback window on incremental runs accounts for late-arriving data and reprocesses recent partitions on every run. This is a small compute cost that prevents silent data quality gaps.

For large-scale transformations where SQL isn't sufficient, Spark is the right tool. PySpark's DataFrame API handles complex aggregations, sessionization, and custom UDFs efficiently. The instinct here is to keep Spark jobs narrow and composable rather than writing one giant job that does everything, because narrow jobs are easier to retry, monitor, and test independently.

Storage Strategy

A layered storage architecture (often called medallion or bronze/silver/gold) maps cleanly to the pipeline stages:

Raw (Bronze): Exact bytes as received from the source. Immutable. Partitioned by ingestion date. This is your source of truth for reprocessing.
Cleaned (Silver): Validated, typed, deduplicated. Schema enforced. Partition by business-meaningful dates (event date, not ingestion date).
Aggregated (Gold): Business-level aggregates, prejoined dimensions, ready for BI tools or API serving.

Parquet on object storage (S3, GCS) is the cost-effective choice for the raw and cleaned layers. For the gold layer, a columnar warehouse like BigQuery, Snowflake, or Redshift gives analysts familiar SQL access with good query performance.

Handling Failures Gracefully

Every part of this system will fail. The question is whether failures are noisy and recoverable, or silent and corrupting. Let's be explicit about each failure mode:

Source API unavailability: Retries with exponential backoff handle transient failures. For persistent failures, alert and let humans decide whether to backfill or skip. Never silently skip.
Schema changes at the source: A source adding a new field is fine. A source removing or renaming a field will break downstream consumers. More on this in the data quality section.
Processing failures mid-stream: Kafka consumers should only commit offsets after successful processing, not before. This gives you at-least-once delivery. Combined with idempotent writes, this prevents both data loss and corruption.
Dead letter queues (DLQ): Any event that fails processing after a configurable number of retries should be routed to a DLQ, not dropped. A DLQ is just a separate Kafka topic or S3 prefix. Operators can inspect failed records, fix the bug, and replay them. Without a DLQ, every processing bug means permanent data loss.

Data Quality and Schema Evolution

Data quality problems found in production are ten times more expensive than problems caught at the pipeline boundary. The practical approach is defense in depth:

Schema validation at ingestion: Use a schema registry (Confluent Schema Registry for Kafka, or JSON Schema validation for REST sources) to reject malformed events before they land in your raw zone. Log rejections with the full original payload for debugging.
dbt tests on transformed data: dbt's built-in tests cover the most common issues. A minimal test suite should include not-null on primary keys, uniqueness on natural keys, accepted values on categorical fields, and referential integrity between tables.
Statistical anomaly checks: Beyond schema tests, row count and aggregate checks catch silent issues. If yesterday's event count is 70% of the 30-day average, something is wrong even if every row is technically valid. Tools like Great Expectations or elementary (the dbt-native option) make this straightforward to configure.
Schema evolution strategy: Additive changes (new nullable columns) are almost always safe. Breaking changes (renames, type changes, removals) require a versioned migration plan. The practical approach is to keep the old field populated in parallel for a transition period, giving consumers time to migrate before the old field is removed.

Orchestration and Monitoring

Airflow handles orchestration well for batch pipelines. The key practices are keeping DAGs declarative rather than procedural (DAG files should describe what runs, not contain business logic), using Airflow's built-in SLA miss alerts, and tagging tasks with owners so alert routing is automatic.

For monitoring, the metrics that matter most are:

Lag: For streaming pipelines, consumer lag in Kafka is the primary health indicator. A growing lag means the consumer is falling behind and freshness guarantees will be violated.
Data freshness: The timestamp of the most recently processed record for each source. If this stops advancing, the pipeline is stalled even if no tasks are failing.
Row counts and null rates: Track these as time series. Sudden drops or spikes are strong signals of upstream changes or processing bugs.

Plugging these metrics into a dashboard (Grafana or a warehouse-native BI tool) and setting threshold alerts gives the team confidence to move fast without breaking data consumers.

Practical Lessons from Real Systems

A few things that consistently come up in production that are easy to miss in design:

Timezones are a persistent source of off-by-one errors. Standardize on UTC at the ingestion boundary and convert to local time only at presentation. Mixing timezones in aggregations produces subtle bugs that are hard to diagnose.
Rate limits will bite you during backfills. A pipeline that runs fine at normal cadence will hit API rate limits when trying to backfill three months of historical data. Build rate limiting and backoff into the extraction layer from day one.
Documentation decays fastest at the source boundary. Maintain a data dictionary that documents what each source field actually means, not just its name and type. This pays dividends when debugging at 2am or onboarding a new team member.
Start with more monitoring than you think you need. The cost of extra metrics is low. The cost of debugging a silent data quality issue with no observability is high.

2344. Data Collection Experience

Similar Questions