3.4: Challenges and Best Practices in Data Ingestion

Challenges and Best Practices in Data Ingestion

Building data ingestion pipelines is not without difficulties. As data volume, variety, and velocity grow, several challenges commonly arise. In this section, we will discuss key challenges – schema evolution, scalability, latency, and data quality – and provide best practices to address each. These challenges are interrelated (for instance, scaling up might introduce data quality issues if not managed, etc.), but we will tackle them one by one for clarity.

Figure \(\PageIndex{1}\): This infographic outlines the four main problems in data ingestion — changing data formats, handling growth, reducing delays, and keeping data accurate — along with the best ways to solve each one. (CC BY 4.0; Felix Amoruwa) Access to detailed description of Figure \(\PageIndex{1}\)

3.4.1 Schema Evolution and Schema Drift

The Challenge: Schema evolution refers to changes in the structure or format of data over time. This can happen when source systems are updated – e.g., a new column is added to a database table, a field’s data type changes, or an API returns an additional JSON attribute in the response. Schema drift is a related term, often used when these changes are unplanned or not synchronized between source and target, causing the schema to “drift” apart. For ingestion pipelines, schema evolution is challenging because pipelines are usually built with an expectation of certain schemas. If the source starts sending data in a new shape that the ingestion process or the target cannot handle, pipelines can break or (worse) silently drop or corrupt data.

For example, consider a simple ETL that copies a database table to a warehouse. If a new column is added to the source table, the ETL might fail (column mismatch) or ignore the new column (thus not capturing data). Conversely, if a column is removed or renamed, ingestion might try to load data into a non-existent column on the target, causing errors.

Schema changes are very common in agile development environments. A survey might add questions, logs might have new message formats after a software update, etc. Without robust handling, schema evolution can “poison” a pipeline..

Best Practices for Schema Evolution:

• Schema Flexibility: Use data formats and stores that can handle evolving schemas. For instance, storing data in a semi-structured format like JSON or Parquet in a data lake can allow new fields to appear without breaking. NoSQL databases or schema-on-read approaches (like parsing JSON at query time) provide flexibility. If using a columnar warehouse, consider adding generic key-value stores or wide tables that can accommodate extra attributes.

• Schema Registry and Versioning: In streaming pipelines, a schema registry (such as Confluent Schema Registry for Kafka) can enforce versioned schemas. Producers register schemas and consumers validate against them. This ensures backward/forward compatibility strategies are followed (e.g., only add optional fields). Each schema change becomes a new version, and consumers can choose when to upgrade to it.

• Backward Compatibility: Design data changes to be backward compatible whenever possible. That means new fields are optional or have defaults, removing fields is done only when not in use or after deprecation period, and data types are widened rather than fundamentally changed (e.g., going from int to long is easier than int to string). If ingestion can assume backward compatibility, it can continue working with older schema until it's updated for the new one, rather than failing.

• Automated Pipeline Adjustments: Use modern ETL/ELT tools that can auto-detect source schema changes and adjust. Some ELT tools (e.g., Fivetran, Stitch) automatically add new columns to target if they appear in source, logging the change for awareness. In custom pipelines, implementing a check step that compares source vs target schema and either auto-adds columns or alerts engineers can help.

• Schema Enforcement vs. Permissiveness: There’s a trade-off. Enforcing a strict schema (say the pipeline rejects unknown fields) will cause immediate failures on drift, which is bad for availability but good for catching issues. A permissive approach (ignore unknown fields) keeps the pipeline running but might lose data or mask an issue. A good practice is to log and alert on any schema discrepancy – so maybe the pipeline drops a new field for now to keep running but sends an alert so that engineers can properly incorporate the change.

• Testing and Staging: For planned schema changes, coordinate between application developers and data engineers. Use a staging environment – test the pipeline with the new schema (for example, point it to a staging database that has the new column). This proactive approach catches issues early. Data contracts between teams can formalize this: producers agree to provide notice or versioning for changes, and consumers adapt accordingly.

• Store Raw Data for Reprocessing: If a pipeline did miss something due to schema change (e.g., it didn't capture a new field for a week), having raw data logs or backups can allow reprocessing. For example, if an upstream system keeps an audit log of all changes including the new field, you can backfill that into your analytics store once the pipeline is updated. This is where ELT’s approach of keeping raw data can be beneficial – if the pipeline was just not pulling that field, maybe the raw extract had it and you can re-transform it.

In summary, managing schema evolution is about anticipating change as the norm. Building pipelines with adaptability, monitoring for changes, and communicating across teams are key. By treating schema drift as an expected event, one can design ingestion processes that evolve gracefully rather than break.

3.4.2 Scalability

The Challenge: Scalability in data ingestion means the ability to handle increasing data volumes, higher data rates, and a growing number of data sources, without performance degradation or failure. Many pipelines are built with current requirements in mind, but data tends to grow – more customers, more sensors, higher frequency, and new data sources over time. If an ingestion pipeline can’t scale, it will become a bottleneck or could collapse under the load (losing data or causing huge delays).

Scalability issues manifest in various ways: a batch job that once took 1 hour now takes 5 hours because data volume doubled (pushing it into business hours or missing SLA); a message queue that becomes overwhelmed with events faster than consumers can process; network bandwidth becoming a limiter for data transfer; or simply storage space and I/O throughput constraints on a destination database as more and more data is ingested.

Best Practices for Scalability:

• Distributed Systems and Parallelism: Leverage distributed computing frameworks and storage that can scale horizontally. Instead of one ETL server, use a cluster (e.g., Spark for ETL, which can add nodes as data grows). Use cloud storage/data lakes that decouple compute and storage, so you can scale compute power on demand. A message queue like Kafka scales by partitioning topics across multiple brokers – design your topics with partition keys that distribute load evenly, and run multiple consumer instances in parallel. Essentially, avoid single-threaded or single-machine bottlenecks in the ingestion process.

• Microservices and Modular Pipelines: Break the ingestion pipeline into components that can be scaled independently. For example, one service handles ingesting logs from devices, another service processes and stores them. Each can be replicated behind a load balancer. Cloud-managed services (like AWS Kinesis Firehose, Google Dataflow, Azure Event Hubs) provide auto-scaling ingestion components so you don’t have to manage it manually.

• Backpressure and Buffering: Design for smoothing out spikes. Use buffering (via message queues as discussed, or even local buffers) to handle bursts. Implement backpressure mechanisms where sources are informed to slow down if the system is at capacity (this can be hard with external sources, but within an internal pipeline you can use flow control). For example, if a consumer falls behind, maybe the system can drop non-critical data or sampling. However, dropping data is last resort in most analytics scenarios; more typically, you ensure the pipeline has enough throughput or at least degrades gracefully (perhaps latency increases but system doesn’t crash).

• Sharding Data Sources: If pulling from databases, consider splitting the load – e.g., instead of one process reading a huge table, break by data partitions (like by date or by key range) with multiple processes. Many modern connectors can do parallel extracts (e.g., Oracle’s connector can split by row id ranges). Similarly, if one API becomes a bottleneck (rate limiting), see if multiple keys or partitions are possible, or if data can be split across time windows in parallel calls.

• Monitoring and Auto-scaling: Employ robust monitoring on throughput, lag, and resource utilization. If you see ingestion queues growing (meaning pipeline can’t keep up), that’s a sign to scale resources. In cloud environments, set up auto-scaling rules – e.g., if CPU usage on the stream processing cluster goes beyond 70%, add more nodes. Monitor data delay: how far behind is the pipeline from real-time? Many systems track lag (Kafka has consumer lag metrics, for instance). Use those metrics to plan capacity.

• Optimize Data Formats and Transfer: Efficiency at scale matters. Compress data in transit and at rest to reduce I/O. Use binary formats (Avro, Parquet, ORC) instead of verbose ones (JSON, CSV) for large-scale ingestion, as they reduce size and often are quicker to parse. For batch loads, prefer bulk loading interfaces (e.g., copy a file into a database rather than row-by-row inserts). Partition data appropriately in storage so that writing and reading can be distributed and pruned (for a data lake, partition by date for instance). This reduces stress on any single node and keeps ingestion speed up.

• Cloud Services and Serverless Options: Consider using cloud-native services that automatically scale. For example, AWS Glue (serverless ETL) or Google Cloud Dataflow (autoscaling data processing) can handle varying loads by scaling out workers. Serverless architectures (like Lambda functions triggered by events) can also be leveraged, although concurrency limits need to be understood. Using managed services (like BigQuery for ingestion directly via streaming API) offloads a lot of scalability concerns to the provider.

• Scalability Testing: Don’t assume it will scale – test it. Perform load tests by simulating higher data input rates to see where the pipeline breaks or slows. This could mean generating synthetic data or replaying historical data at an accelerated pace. Use those tests to identify bottlenecks (maybe CPU-bound at transformation, maybe network-bound, etc.) and address them (e.g., if CPU-bound, see if code can be optimized or parallelized; if network-bound, consider compression or more bandwidth or filtering unnecessary data early).

• Architectural Patterns: Embrace data architecture patterns known to scale. For instance, the data lake + ELT pattern can scale storage easily and use cluster computing for transforms, rather than pushing everything into a single enterprise warehouse that might become strained. A monolithic pipeline on a single server is likely to hit a ceiling; a distributed one using robust frameworks is much more scalable.

To illustrate, consider a real-world case: Netflix deals with billions of event messages per day. Their ingestion uses Kafka to handle on the order of 8 million events per second, with a data pipeline that scales across many nodes. They partition data cleverly and use stream processing to filter/route data to various sinks. By partitioning (by customer ID, by event type, etc.), they ensure no single node handles all events, thus scaling horizontally. They also built tooling to automatically deploy and manage hundreds of Kafka brokers and stream processors to meet demand.

In summary, design for scale from day one. Even if your data is small now, assume it will grow and choose technologies and designs that have headroom. It is easier to throttle a powerful system on small data than to retrofit a fragile pipeline to handle large data.

3.4.3 Latency

The Challenge: Latency in data ingestion is the delay from the time data is generated or available at the source to the time it is ready for use in the target system. High latency means your analytics or systems are operating on stale data. In some contexts, data that is minutes old might be too stale (e.g., fraud detection), whereas in others, a day old might be acceptable (some reports). The challenge is that as data freshness requirements tighten, ingestion pipelines must be optimized to reduce any unnecessary delays.

There can be many sources of latency: waiting for batch schedules (intrinsic in batch vs streaming discussion), network transfer times, processing times for transformation or enrichment, queuing delays (data waiting in a buffer behind other data), or slow writes to the target system. Reducing latency often means converting batch steps to streaming, eliminating bottlenecks, and possibly sacrificing some cost or complexity to achieve speed.

Best Practices to Reduce Latency:

• Adopt Streaming where Appropriate: As discussed earlier, streaming ingestion provides much lower latency than batch. If your current pipeline waits for nightly batches, evaluate if you can shift to more frequent micro-batches or a streaming approach using tools like Kafka, Spark Streaming, or cloud streaming services. For example, instead of collecting logs and uploading daily, send them in real-time to a processing pipeline. 

• Optimize Pipeline Stages: Examine each step in the pipeline and its throughput vs. delay. Maybe the transform step is running on an outdated single-threaded script – parallelize or upgrade it to reduce processing time. Perhaps data spends time in storage between steps (like staged to disk then read again to load) – consider pipelining steps in memory (one process’s output directly streaming into next). Each read/write or wait adds latency; stream processing frameworks can pipeline transformations without intermediate storage.

• Incremental Processing and Small Batches: If true streaming is too much of a leap, shorten batch intervals. If you run hourly now, try 5 or 10 minutes. Use incremental loads (load only new data frequently rather than full loads infrequently). Microbatch frameworks (like Spark Structured Streaming) might internally process in mini-batches of, say, a few seconds – you could tune that interval to trade-off overhead vs latency. The key is, don’t collect more data than needed before processing; more frequent smaller loads keep data fresher.

• Near-Real-Time Tools: Utilize specialized tools for low-latency data store updates. For example, if you need data queryable with low latency, consider technologies like Apache Druid or ClickHouse which are built for rapid ingestion and querying of streaming data (they can ingest millions of records per second and make them queryable within seconds). Even in traditional warehouses, some have streaming ingestion APIs (BigQuery’s streaming insert, Snowflake’s Snowpipe) that allow continuously loading data with seconds latency into tables.

• Reduce Network Latency: If data sources are geographically separate from the ingestion system, network chatter can add latency. Solutions: deploy ingestion closer to source (edge processing for IoT – do some processing on edge devices or local gateways so faraway cloud doesn’t have to handle raw data; or use region-specific ingestion endpoints). Also use efficient protocols (binary, compressed) to send data. Avoid chatty interactions – e.g., an API that requires multiple round trips to get a dataset will be slower than one bulk call. If using cloud, take advantage of high-bandwidth links or dedicated links between systems (like AWS Direct Connect) for heavy data flow.

• Prioritize and Filter: Perhaps not all data needs ultra-low latency. Identify critical streams that need to be fast and handle them separately. For instance, you might send critical events through a fast path (direct to a real-time analytics DB), while less critical data goes through a slightly slower path. Filtering out noise early can reduce load and latency for the important data. For example, in security logs, you might immediately flag and forward high-risk events to an alert system, whereas normal events can be batch-processed.

• Monitor Latency: You can’t manage what you don’t measure. So set up metrics for end-to-end latency. For example, stamp each record with an event time and then measure when it lands in target, and compute the difference. Many pipelines have a concept of “event time vs processing time” – use that to see delays. If delays creep up, alert on it and investigate. Netflix’s data pipeline, for example, monitors how far behind the consumer lags the producer timeline to ensure their real-time analytics stay within a few seconds.

• Simplify Where Possible: Each component, transformation, or system hopped through adds latency. While decoupling is good, be mindful of over-complicating. If ultra-low latency is needed, maybe write directly to a fast store instead of flowing through multiple intermediate topics and processes. Sometimes a direct pipeline with fewer steps can drastically cut latency (with trade-offs in flexibility).

As an example of latency-sensitive design: in financial trading, ingestion and analysis often must occur in milliseconds. They use in-memory streams and event processing engines, collocate servers physically near exchange data feeds, etc. That’s an extreme case. For most business analytics, sub-second latency is not needed, but going from hours to minutes or seconds can enable things like dynamic dashboards and timely alerts.

Ultimately, appropriate latency depends on the use case. The best practice is to meet the requirements but don’t over-engineer for speed that isn’t needed (since ultra-low latency often costs more in complexity and resources). Use the smallest batch or fastest stream that accomplishes the business need. If daily was fine but hourly would give a nice improvement, no need to go to full streaming – stepwise improvements are fine. But if you need real-time, design for it from the ground up.

3.4.4 Data Quality

The Challenge: Data quality issues can severely undermine the value of ingested data. If the data coming into your analytics systems is incomplete, incorrect, duplicated, or inconsistent, any analysis or machine learning on it will be flawed. During ingestion, data can have quality issues from the source (dirty data) or can be introduced by the ingestion process (e.g., truncating strings, mismatched encodings). Common data quality problems include: missing values, outliers or erroneous values, duplicates (the same record ingested multiple times), inconsistent formatting (dates, units, categorical values), and data corruption (from conversion errors, etc.).

Ensuring quality is tricky because ingestion often deals with raw, diverse data. However, it’s vital to tackle quality as early as possible – the famous ETL maxim, “garbage in, garbage out,” holds true.

Best Practices for Data Quality in Ingestion:

• Validation and Cleansing Steps: Incorporate data validation checks into the ingestion pipeline. For instance, after extraction, validate that required fields are present and within expected ranges. If data fails validation, decide whether to reject it, fix it, or route it for manual review. Some typical validations: numeric fields within plausible ranges (e.g., ages 0-120), string patterns (e.g., email contains "@"), referential integrity (if referencing IDs, do those IDs exist?). This can catch issues early. Also, cleanse data by trimming whitespace, standardizing formats (e.g., all dates to ISO8601), and handling common anomalies (like converting “N/A” strings to actual nulls, etc.).

• Deduplication: Ensure the pipeline has a strategy to avoid or eliminate duplicate records. This is especially needed if ingesting from multiple sources or if using at-least-once delivery from queues (where duplicates can occur). Strategies include: using primary keys or unique constraints in the target to naturally deduplicate, or maintaining a hash or identifier log of ingested records to skip repeats. Some pipelines employ exactly-once processing frameworks to avoid duplicates, but if not possible, have a post-processing job to collapse duplicates.

• Enrichment with Reference Data: Data quality can improve by adding context. For example, if ingesting product codes, join with a reference table to ensure the codes are valid and maybe replace them with full names. If a code isn’t found, flag that record. This ensures consistency (only allowed values pass through). It’s best to integrate such reference checks in the ingestion so bad data isn’t stored (or is stored but marked).

• Missing Data Handling: Decide how to treat missing values during ingestion. Options: drop records that are missing critical fields (if those records aren’t usable); fill with default or estimated values; or tag them and handle later. For example, if a sensor sometimes doesn’t send a reading, you might fill forward the last known value or mark it as missing so analysis can ignore or interpolate later. 

• Outlier Detection: The ingestion process can include detecting outliers – e.g., a temperature sensor reading 1000°C out of range. Such outliers might indicate a sensor fault or data error. One can either remove them, or quarantine them for investigation, or cap them at certain limits depending on use case. It might not always be right to drop them automatically (they could be real extreme events), but identification is key.

• Auditing and Logging: Keep detailed logs of the ingestion process, including any errors or anomalies detected. For instance, log counts of records processed vs. loaded, number of errors, etc. If a record was dropped or altered, consider logging an audit trail. This helps later to debug data issues. Some pipelines create an "error handling" path – e.g., records that fail validation are written to a separate error log or table with the reason, so data stewards can review and correct them. This prevents silently losing data.

• Consistency and Idempotency: Ensure that repeated runs or parallel processes don’t create inconsistencies. For example, if ingestion is incremental, make sure an update isn't applied twice differently. Idempotent processing (where reprocessing yields the same result) is a good design principle. E.g., if you re-run yesterday’s batch, it should end up the same in target (not double the data). Designing with idempotency (like using upsert operations instead of blind inserts) improves quality.

• Data Profiling and Quality Monitoring: Regularly profile the ingested data to check quality metrics. Profiling involves computing stats like min, max, mean, distinct count, null count for each field, and seeing if they match expectations. Set up data quality dashboards or alerts – e.g., if suddenly 20% of a numeric field are null when it’s usually <1%, something’s wrong either at source or ingestion. Monte Carlo Data (a data observability company) often talks about such data observability, which monitors data health proactively. Catching a data quality drop early can save a lot of downstream pain.

• Governance and Standards: Establish data definitions and standards for each data element. If all systems agree on units, formats, and definitions, it reduces quality issues. For example, define that all currency fields are in USD and two decimal places; then ensure ingestion converts or checks any source that deviates. Use schema and constraints in the target (if the target is a database, use data types and constraints as a quality gate – e.g., a NOT NULL constraint ensures missing data doesn’t slip in for critical fields, foreign key constraints ensure referential integrity if applicable, etc.). Note though that using too strict constraints can cause ingestion failures – there’s a balance to strike. Some prefer to allow the data in a raw area then fix it, others stop it at the gate.

• Cleaning at Source if Possible: Sometimes the best fix is upstream. If you notice a recurring quality issue (e.g., a particular sensor sends bad data due to calibration), work with the source system owners to correct it. Similarly, training those doing manual data entry or improving forms can reduce bad data creation. In other words, push quality checks as far upstream as feasible to reduce the burden on ingestion.

A short case: A healthcare data pipeline ingests patient records from multiple clinics. They noticed sometimes birthdates were clearly wrong (e.g., 1900, or future dates) because of data entry errors. By adding a validation rule in ingestion (reject or flag birthdates that make patient age > 120 or < 0), they improved the quality of the centralized data. The rejected records are sent back to clinics for correction. This ensures analysis (like age group outcomes) isn’t skewed by impossible ages. It’s a simple example, but replicates across countless scenarios.

In conclusion, embedding data quality checks and cleansing into ingestion prevents “garbage” from polluting your analytics environment. It’s more efficient to handle at entry point than to fix later when it may have cascaded through reports and models. Balance is key: overzealous cleaning might drop useful data, too lax and the data lake becomes a data swamp. Continuous monitoring and improvement of data quality processes is part of a healthy data ingestion practice.