More info on connector loaded vs destination flush process

[bgadrian]

Hello!

I am looking for more information on the data flow of a postgresql as a connector, or the entire connector data flow, not sure where to dig down.

When I trigger a connector with Incremental | Append + Deduped destination postgresql, the data seems to be flowing but the data is not yet available in the respective tables.

Given the scenario (postgresql in aws rds 16.3)

• start a sync for 5 schemas
• each schema takes 5hours
• no errors are in the worker output
• in the telemetry otel I see the following metrics going up ( stats_commit_attempt_successful_total, state_processed_from_destination_total, state_processed_from_source_total, sync_record_checksum_total) for this job
• in the postgresql database I see the FreeScape going down
• no postgresql metrics/resource is hitting capacity
• I see no transactions ongoing in postgresql

Even after 15h when the first 3 tables are done(according to airbyte connector), when I select * count in the destination tables I see no data.

The data only surface after few hours after the airbyte job is done.

Can someone point me to which area to dig up more, I presume this is not the intended behaviour.

[bgadrian]

Digging more into it I think my question is generic about the flow of the connector loading records, and how does the destination is picking them up.

I see in the connector retry "414,751 records extracted|414,706 records loaded"
But in the destination the tables are empty. I checked with SELECT queries, and also double checked in pg_stat_all_tables.

I presume the data is written at the end?
Looked in https://docs.airbyte.com/understanding-airbyte/airbyte-protocol and https://docs.airbyte.com/connector-development/connector-builder-ui/record-processing but I could'nt find more information.

Are there any docs? Or can I monitor in otel metrics how much data is loaded but not yet written?

[bgadrian]

Seems that it is using several temporary tables between these two steps https://airbyte.com/tutorials/incremental-data-synchronization#step-7