Relational Database Sources & CDC
Relevant source files
The following files were used as context for generating this wiki page:
- docusaurus/platform_versioned_docs/version-1.6/connector-development/ux-handbook.md
- airbyte-integrations/developer-docs/destinations/preflight-checklist.md
- airbyte-integrations/developer-docs/destinations/step-by-step/0-introduction.md
- airbyte-integrations/developer-docs/destinations/implementation-reference.md
- docusaurus/platform_versioned_docs/version-1.8/contributing-to-airbyte/writing-docs.md
Relational Database Sources and Change Data Capture (CDC) represent the infrastructure used to replicate data from structured systems like Postgres, MySQL, and SQL Server into various destinations. The primary goal is to ensure high-trust, reliable data movement while maintaining data integrity, such as preserving leading zeros in zip codes and correctly handling database timezones. Sources leverage specialized implementation details like Debezium for CDC, though these are treated as implementation details hidden from the end-user's primary workflow to ensure a better user experience.
The architecture emphasizes Extract-Load-Transform (ELT) principles over ETL, ensuring that data is loaded into the destination in a format as close to the source as possible. This approach removes Airbyte as a development bottleneck, maintains backwards compatibility with source APIs, and provides a future-proof structure that accommodates evolving user needs without requiring constant re-extraction of data.
Sources: docusaurus/platform_versioned_docs/version-1.6/connector-development/ux-handbook.md:65-95, docusaurus/platform_versioned_docs/version-1.6/connector-development/ux-handbook.md:200-240
Change Data Capture (CDC) Architecture
Change Data Capture (CDC) allows Airbyte to track and replicate deleted records from a source database. This is achieved by detecting specific metadata in the stream, such as the _ab_cdc_deleted_at column. The system supports two primary modes for handling these changes: Hard Delete and Soft Delete.
CDC Deletion Modes
| Mode | Behavior | Implementation Detail |
|---|---|---|
| Hard Delete | Actually removes records from the destination. | Uses a DELETE clause in the MERGE statement. |
| Soft Delete | Keeps records in the destination but populates a tombstone flag. | Standard UPSERT where the deletion timestamp is stored. |
Sources: airbyte-integrations/developer-docs/destinations/implementation-reference.md:380-420
CDC Logic Flow
The diagram below illustrates how the connector determines whether to delete, update, or insert a record during a CDC-enabled sync.
The logic ensures that deletions are only processed if the incoming CDC event is newer than the existing record in the destination based on the cursor.
Sources: airbyte-integrations/developer-docs/destinations/implementation-reference.md:400-415
Core Components for Database Connectors
Implementing a relational database connector requires four core custom components that handle SQL generation, execution, batching, and type mapping.
1. SQL Generator
Responsible for producing dialect-specific SQL (e.g., handling backticks in MySQL vs. double quotes in Postgres). It generates statements for namespace creation, table management, and atomic operations like SWAP or MERGE.
Sources: airbyte-integrations/developer-docs/destinations/implementation-reference.md:10-50
2. Database Client
Executes the SQL produced by the Generator. It implements interfaces for TableOperationsClient and TableSchemaEvolutionClient, handling the actual connection to the data source and returning count or existence checks.
Sources: airbyte-integrations/developer-docs/destinations/implementation-reference.md:55-100
3. Insert Buffer
Optimizes throughput by batching records before writing to the database. Different databases use different strategies:
- MySQL: Multi-row
INSERTstatements. - Postgres: Using the
COPYcommand with temporary files. - Snowflake: GZIP CSV staging via cloud storage.
Sources: airbyte-integrations/developer-docs/destinations/implementation-reference.md:105-135
4. Column Utilities
Maps Airbyte's internal types to the specific data types of the destination database.
| Airbyte Type | Postgres Target | ClickHouse Target |
|---|---|---|
StringType | TEXT | String |
IntegerType | BIGINT | Int64 |
ObjectType | JSONB | JSON |
Timestamp+TZ | TIMESTAMPTZ | DateTime64(3) |
Sources: airbyte-integrations/developer-docs/destinations/implementation-reference.md:140-185
Schema Management & Evolution
For relational sources, Airbyte emphasizes providing strongly typed schemas to enable downstream optimizations. Schema evolution is a four-step process triggered when a sync starts and a table already exists.
Evolution Process Sequence
The framework automatically generates a ColumnChangeset identifying columns to add, drop, or modify. For unsafe changes (e.g., narrowing a type from BIGINT to INT), connectors typically use a "temp column, cast, and rename" pattern to prevent data loss.
Sources: airbyte-integrations/developer-docs/destinations/implementation-reference.md:315-375, airbyte-integrations/developer-docs/destinations/preflight-checklist.md:200-240
User Configuration & Personas
Relational database connectors serve a spectrum of users, ranging from Data Analysts to Data Engineers. To maintain ease of use, connectors follow specific UI/UX guidelines:
- Avoid Implementation Details: Users should not need to know about Debezium or replication slots unless it impacts their prerequisite configuration.
- Advanced Section: Technical parameters (e.g., TLS certificates) are hidden under "Advanced" sections.
- Fast Failure: The
spec.jsonshould includepatternkeywords to validate inputs (e.g., hostnames or ports) before a sync is attempted. - Documentation: Clear instructions must be provided for setting up prerequisites like logical replication for CDC.
Sources: docusaurus/platform_versioned_docs/version-1.6/connector-development/ux-handbook.md:120-195, docusaurus/platform_versioned_docs/version-1.6/connector-development/ux-handbook.md:270-290
Summary of Sync Modes
Connectors select a StreamLoader variant based on the minimumGenerationId and the sync mode (Append, Overwrite, or Dedupe).
| Sync Mode | Temp Table Used? | Finalization Logic |
|---|---|---|
| Append | No | Direct write to final table. |
| Dedupe | Yes | MERGE from temp to final. |
| Overwrite | Yes | Atomic SWAP of temp and final. |
Sources: airbyte-integrations/developer-docs/destinations/implementation-reference.md:210-235