How to fetch only updated data from source database?

Q&A
, ,
1

Hi Airbyte Team,

First of all , thanks for providing such a valuable open source to us.

Could someone help me how to fetch the updated rows from source not all data again? PFB the steps which i performed.

Steps:

  1. Created the source-postgres connector and Destination as Snowflake
  2. Add the 2 tables and started as Full load
  3. Loaded successfully lets say 100 rows from Source to target
  4. Same connection , I have updated the Tables fetch from Full & Overwrite mode to Incremental + Depuded History
  5. Trigger the Sync now.

Description:
I would like to pull only new rows or updated rows from Source database. But , even in incremental model its fetches all rows from source database again like Full overwrite option.

Is there any settings I am missing.

2

Hello @tagnev, this indeed should not happen! Could you tell me which connector versions you are using?

3

its pretty much issue for both Postrgesql and Oracle connector

4

Could you give me the specific connector versions? Sometimes updating connectors or rolling them back to a specific version helps solve the issue.

5

image
image1533×188 16.8 KB

Attached the version details here

6

Thanks! A few things:

  1. Could you please update Airbyte as well as all connectors? For example, the Postgres source connector is really behind and that might be part of the problem.
  2. For some connectors the user needs to define the cursor field so incremental sync works correctly, the Postgres source connector is one of these. Please take a look here:
    https://docs.airbyte.com/understanding-airbyte/connections/incremental-append/#user-defined-cursor

Let me know if that helps. If you are getting any logs, I’d love to see them.

7

Thanks. I have upgraded Airbyte and connectors. While doing the progress its failing with some insufficient privilege.
Its perfectly working for FULL load, but if i changed that Incremental + Depuded hisotry . its failing.
Attached the logs
lognew.txt (5.1 MB)

8

Does the role have MANAGE GRANTS privilages?

9

Yes, it has MANAGE privilages. Not sure what privilages exactly needed here.

Insufficient privileges to operate on database ‘SANDBOX’,externalMessage=Normalization failed during the dbt run. This may indicate a problem with the data itself.,metadata=io.airbyte.config.Metadata@475585c[additionalProperties={attemptNumber=0, jobId=69, from_trace_message=true}],stacktrace=Encountered an error:
Database Error
** 003001 (42501): SQL access control error:**
** Insufficient privileges to operate on database ‘SANDBOX’,retryable=,timestamp=1660936001968], io.airbyte.config.FailureReason@6769c101[failureOrigin=normalization,failureType=system_error,internalMessage=Encountered an error:**
Database Error
** 003001 (42501): SQL access control error:**
** Insufficient privileges to operate on database ‘SANDBOX’,externalMessage=Normalization failed during the dbt run. This may indicate a problem with the data itself.,metadata=io.airbyte.config.Metadata@5a05d4b3[additionalProperties={attemptNumber=0, jobId=69, from_trace_message=true}],stacktrace=Encountered an error:**
Database Error
** 003001 (42501): SQL access control error:**
** Insufficient privileges to operate on database ‘SANDBOX’,retryable=,timestamp=1660936001968]]]**

10

Its throwing PERMISSION issue only for INCREMENTAL LOAD. Its weird

11

Every time incremental SYNC bring all rows from source.

Attached the logs
logs-66.txt (6.5 MB)

12

Do we have any updates on this?.. I guess this is very critical issue for incremental SYNC. Many folks are facing same issue as they mentioned in our SLACK channel.

13

I think I might have an idea of what is going on, but please give me a link to the Slack discussion if you can as well!

I know we had some backwards compatibility issues about a month ago with the Postgres source connector, but they should be resolved now. Could you give me the details on what system/setup you are using?

Is there any chance you’d be able to create a new test user and a new connection todo a trial sync? I’m wondering if the permissions were not setup correctly somehow or the prerequisites aren’t met. What version of Postgres are you using?

Here’s the setup info:
https://docs.airbyte.com/integrations/sources/postgres#prerequisites