Hi, I was exploring the INCREMENTAL replication in tap-mysql and tested and found that for the next job run, it includes the last state also. For ex.: 1. I implemented INCREMENTAL replication on a column ID 2. I populated the data (IDs from 1-20) 3. Next, I added some data (IDs from 21-25) in the source table and ran the ELT 4. Instead of like appending the datas from 21-25, 20-25 is appending. i.e. 20 is getting duplicated. Am I missing something. Or is it a bug to be resolved? Thanks is advance.

This is expected behavior: https://www.stitchdata.com/docs/replication/replication-methods/key-based-incremental#limitation-3--replication-key-inclusivity, but it is more relevant when using a timestamp as a replication key than an ID.

Oh OK.. In target-bigquery, from my knowledge the replication methods available are append and truncate. Is there a way to upsert data?

I don't think it supports that today, no. Is it an option to deduplicate inside the destination using dbt? Alternatively, you can look into modifying tap-mysql to only use

> <replication key>

instead of

>= <replication key>

when the key is an interger rather than a timestamp

I'm running only EL through meltano. Can we configure that in meltano.yml? If yes, Can U guide me how?

It's not as simple as adding "deduplicate on key X" to

meltano.yml

, unfortunately, no. you'd need to setup a dbt project and add your own dbt model to do this. Are you familiar with dbt already? @taylor Can you help out here? I wonder if we could make it as easy as "deduplicate on key X" if we can generate the dbt models etc...

I was thinking about this earlier today. It’d be an interesting iteration on the target sdk to assist with creating a base layer using dbt. We’d have to pass through some info from the tap and make the SQL fairly agnostic, but there’s no reason we couldn’t solve that. To your point @boopathy_raja_thirugnanasigha my comment here is still my recommendation https://meltano.slack.com/archives/CFG3C3C66/p1616509800079700?thread_ts=1616480614.078000&cid=CFG3C3C66 at GitLab even for sources coming from paid tools we still had a cleaning and validation step for all raw data.

Thank U for the support @taylor @douwe_maan

Truncate is the sql keyword used to remove all rows (data) from a table but keep its structure in place. https://docs.snowflake.com/en/sql-reference/sql/truncate-table.html I don’t know many taps that support that since that’s a very destructive operation

Hi, @taylor. Thank U for the response. I know about the truncate in sql keywords. But there is a configuration, 'truncate' in the replication method for target-bigquery. https://meltano.com/plugins/loaders/bigquery.html#replication-method .. Is it like removing all the data for every run(i.e. only new values are loaded, (in case of incremental replication in the tap-mysql side)). If so, Is there a feature to truncate with

unique key

constraint, so that duplicate values are removed ?? I'm eager in deduplicating in the EL part itself, so that the cost of DML query in the target-bigquery side using

dbt

won't be expensive, since deduplicating DML query has to run for every scheduled elt job.

ah my mistake, apologies. I’ve scanned through the code and documentation and I’m not quite sure what the expected behavior is. The naming of truncate makes me nervous as I’m more used to Snowflake’s merge statement… can you try it out with a small dataset and see what happens?

Sure. Thanks @taylor