Typing and Deduping
Relevant source files
The following files were used as context for generating this wiki page:
- airbyte-integrations/developer-docs/destinations/preflight-checklist.md
- airbyte-integrations/developer-docs/destinations/step-by-step/0-introduction.md
- docusaurus/platform_versioned_docs/version-2.1/connector-development/ux-handbook.md
- docusaurus/platform_versioned_docs/version-2.0/connector-development/ux-handbook.md
- docusaurus/platform_versioned_docs/version-1.8/connector-development/ux-handbook.md
Typing and Deduping represents a critical phase in the Airbyte destination connector lifecycle, ensuring that data replicated from various sources is correctly formatted for the target database and that duplicate records are handled according to the user's sync configuration. This system transitions data from a raw "Airbyte-internal" state to a "final" state that is usable for downstream analytics.
The process involves mapping Airbyte types (JSON-based) to native database types and applying deduplication logic—typically using primary keys and cursor fields—to maintain data integrity. This ensures that the destination reflects a clean, typed, and unique representation of the source data.
Sources: airbyte-integrations/developer-docs/destinations/preflight-checklist.md, airbyte-integrations/developer-docs/destinations/step-by-step/0-introduction.md
Core Architecture and Data Flow
The Typing and Deduping system operates as part of the "Advanced Features" set in the Destination CDK. It relies on a specific sequence of operations: discovering the current schema, computing the required changes, and applying those changes before or during the data write process.
The following diagram illustrates the data flow from raw message ingestion to the final typed and deduped table:
This diagram shows the transition of data from the raw staging area to the final typed destination table through schema evolution logic.
Sources: airbyte-integrations/developer-docs/destinations/step-by-step/0-introduction.md:143-154, airbyte-integrations/developer-docs/destinations/preflight-checklist.md:120-143
Type Mapping and Column Utilities
A fundamental component of the system is the toDialectType() utility, which maps universal Airbyte types to destination-specific SQL types. This ensures data integrity by preventing precision loss (e.g., US zip codes losing leading zeros if incorrectly typed as integers).
Standard Type Mappings
| Airbyte Type | Recommended Dialect Type | Notes |
|---|---|---|
| String | VARCHAR, TEXT, or STRING | Check length limits |
| Integer | BIGINT | Ensure range covers source |
| Decimal | DECIMAL(p,s) or NUMERIC | Usually DECIMAL(38,9) |
| Boolean | BOOLEAN | Fallback to TINYINT(1) |
| JSON | JSONB, VARIANT, or TEXT | Native types preferred for query performance |
| Timestamp | TIMESTAMPTZ | Must support Timezones |
Sources: airbyte-integrations/developer-docs/destinations/preflight-checklist.md:67-101, docusaurus/platform_versioned_docs/version-2.1/connector-development/ux-handbook.md:104-114
Deduplication Mechanisms
Deduplication is required when a sync mode specifies primary keys. The system uses these keys alongside a cursor field (like updated_at) to identify the most recent version of a record.
Implementation Strategies
If the database does not support native atomic upserts, the system implements a manual deduplication flow:
- Window Functions: Utilizing
ROW_NUMBER() OVER (PARTITION BY pk ORDER BY cursor DESC)to select the latest record. - Temp Tables: Loading new data into a temporary area, performing the deduplication, and then merging into the final table.
- Atomic Swaps: Using
ALTER TABLE ... SWAP WITH ...to replace old data with the newly deduped dataset to minimize downtime.
The sequence above demonstrates the use of a staging area to perform deduplication before merging into the production table.
Sources: airbyte-integrations/developer-docs/destinations/preflight-checklist.md:120-143, airbyte-integrations/developer-docs/destinations/step-by-step/0-introduction.md:162-175
Schema Evolution
Schema Evolution allows connectors to automatically adapt the destination table as the source schema changes. The SQL Generator component manages this through four distinct phases:
- Discover: Introspect the existing table in the destination via system catalogs (e.g.,
information_schema.columns). - Compute: Determine the desired schema based on the incoming Airbyte stream.
- Compare: Identify differences (new columns, type changes, deleted columns).
- Apply: Execute
ALTER TABLEstatements to synchronize the schemas.
Handling Type Changes
- Safe Changes: Widening types (e.g.,
INTtoBIGINT) can often be done directly. - Unsafe Changes: Narrowing types or incompatible changes (e.g.,
VARCHARtoINT) may require table recreation or temporary column patterns to avoid data loss.
Sources: airbyte-integrations/developer-docs/destinations/preflight-checklist.md:146-168, airbyte-integrations/developer-docs/destinations/step-by-step/0-introduction.md:126-135
Key Components and Interfaces
The implementation of Typing and Deduping is distributed across several architectural roles within the CDK.
| Component | Responsibility |
|---|---|
| SqlGenerator | Generates the SQL for MERGE, UPSERT, and ALTER TABLE. Must be pure functions. |
| TableOperationsClient | Executes the generated SQL and handles database-specific error codes. |
| InsertBuffer | Collects records in memory to perform efficient batch inserts before typing logic is applied. |
| StreamLoader | Orchestrates the lifecycle, deciding between direct writes or temp table swaps. |
Sources: airbyte-integrations/developer-docs/destinations/step-by-step/0-introduction.md:143-154, airbyte-integrations/developer-docs/destinations/preflight-checklist.md:10-25
Conclusion
Typing and Deduping is the mechanism that transforms raw data streams into structured, reliable database tables. By automating schema evolution and providing robust deduplication strategies, Airbyte ensures that data is not only replicated but also immediately ready for analytical use cases. This system prioritizes data integrity and reliability, adhering to the principle that infrastructure should act as a "debugging timesink" reduction tool for the user.
Sources: docusaurus/platform_versioned_docs/version-2.1/connector-development/ux-handbook.md:88-100, airbyte-integrations/developer-docs/destinations/step-by-step/0-introduction.md:46-55