Blog

Engineering

How Airbyte 1.0 Detects Dropped Records: Ensuring Data Integrity in ETL Pipelines

Learn how Airbyte 1.0 tracks record counts at the source, platform, and destination to detect dropped records.

ENGINEERING

September 19, 2024

7 min read

Subodh Chaturvedi

Summarize with AI:

How Airbyte 1.0 Detects Dropped Records: Ensuring Data Integrity in ETL Pipelines

In the world of data integration, ensuring the accuracy and completeness of data is paramount. Dropped records—data that fails to make it from the source to the destination—can lead to significant business issues, from incorrect analytics to faulty decision-making. At Airbyte, we've encountered and addressed the challenge of detecting dropped records head-on. This blog post delves into how we've enhanced Airbyte to detect dropped records, ensuring data integrity throughout your ETL processes.

The Problem: Unnoticed Dropped Records

While working on various data integration projects, we noticed an unsettling pattern: records were being dropped silently during the synchronization process. This issue wasn't just a minor glitch; it had the potential to undermine the trustworthiness of entire datasets.

Why Records Get Dropped

Records can be dropped for several reasons, often stemming from bugs in connectors built using the Airbyte Connector Development Kit (CDK). Some common issues we've identified include:

Improper Data Flushing: Certain source connectors weren't correctly flushing data, leading to incomplete data transfer.
JSON Parsing Errors: Complex or malformed JSON data sometimes caused parsing failures, resulting in dropped records.
Large Records Handling: Extremely large records could exceed buffer sizes or timeouts, causing them to be skipped.

For instance, connectors dealing with streaming data or those interfacing with APIs that return nested JSON structures were particularly prone to these issues.

The Scope of the Problem

Although the percentage of dropped records was relatively small, at Airbyte, we believe that even a single lost record is unacceptable. In industries where every data point matters—like finance or healthcare—a dropped record can have serious implications. Therefore, detecting and preventing even minimal data loss became a priority.

Understanding Airbyte's Architecture

To appreciate how we tackled the issue, it's essential to understand the roles of the source, platform, and destination within Airbyte's architecture.

Responsibilities Breakdown

Source: Extracts data from the original data source and streams it to the platform.
Platform: Orchestrates the data flow, manages state, and ensures data integrity.
Destination: Receives data from the platform and loads it into the target system.

Each component must function correctly to guarantee end-to-end data integrity.

State and Statistics Handling

Airbyte uses state messages to track the progress of data synchronization. These messages are crucial for checkpointing and resuming failed syncs without starting from scratch.

Introducing Enhanced State Messages

To detect dropped records, we extended the Airbyte protocol by adding statistics to the state messages.

How StateStats Improves Detection

We incorporated StateStats into the state messages, enabling the source to include metadata about the number of records read up to a certain point. Here's how it works:

Periodic State Messages: The source emits state messages every 15 minutes (configurable), each containing a counter of the total records read so far.
Platform-Level Counting: The Airbyte platform maintains its own count of records as they pass through.
Destination Acknowledgment: The destination also keeps a record count and, upon loading data, sends a state message back to the platform, including both source and destination statistics.

By comparing the counts from the source, platform, and destination, we can determine if any records were dropped at any stage.

Communication Through Pipes

The source and destination communicate with the platform via pipes, allowing for efficient data streaming and message passing without persistent storage. This mechanism ensures real-time monitoring of record counts.

Example of an AirbyteStateMessage


{
    "type": "STATE",
    "state": {
        "type": "STREAM",
        "stream": {
            "stream_descriptor": {
                "name": "foo",
                "namespace": "bar"
            },
            "stream_state": {
                "updatedAt": "2024-01-01"
            }
        },
        "sourceStats": {
            "recordCount": 10.0
        },
        "destinationStats": {
            "recordCount": 10.0
        }
    }
}

In this example, the counts match across all components, indicating no records were dropped.

Implementation and Support

Supported Sources and Destinations

The enhanced state message feature is supported in the latest Airbyte versions. Key sources and destinations that have implemented this feature include:

Sources: PostgreSQL, MySQL, Salesforce, and more.
Destinations: Snowflake, S3, BigQuery, etc.

How to Use the Feature

For end-users, leveraging this feature is seamless:

Upgrade Airbyte: Ensure you're using a compatible Airbyte version.
Configure Connectors: Use supported source and destination connectors.
Monitor Logs: During syncs, check the logs for state messages and record counts.

Behind the Scenes: Quality Assurance at Airbyte

Internal Monitoring

At Airbyte, we continuously analyze these statistics for quality assurance. Automated systems check for discrepancies in record counts and alert our engineering team if any are detected.

Sentry Notifications

We've integrated Sentry for error tracking and monitoring. If dropped records are detected, Sentry sends notifications, allowing us to investigate promptly.

Future Enhancements

Automated Sync Failures

We're working on features that will automatically fail a sync if dropped records are detected. This proactive approach will prevent incomplete data from reaching your destination, ensuring data integrity.

User Notifications

Future versions will include in-app notifications and alerts if discrepancies in record counts are found, keeping users informed in real-time.

Conclusion

Data integrity is non-negotiable in today's data-driven landscape. By enhancing Airbyte's ability to detect dropped records through improved state messages and record counting, we've taken significant steps to ensure your data arrives accurately and completely at its destination.

Whether you're an engineer maintaining data pipelines or a business analyst relying on accurate data, these improvements provide peace of mind and trust in your ETL processes.

We invite the community to contribute to this ongoing effort. If you have ideas, encounter issues, or want to help improve data integrity features, reach out to us on GitHub or Slack.

By proactively addressing the issue of dropped records, Airbyte continues to enhance its platform, providing robust, reliable data integration solutions for all users.

What other great readings

This article is part of a series of articles we wrote around all the reliability features we released for Airbyte to reach the 1.0 status. Here are some other articles about those reliability features: