Mixpanel to Redshift. Incremental stuff is not wor...
# replication-troubleshooting
s
Mixpanel to Redshift. Incremental stuff is not working for me. I ran the same job again with same parameters. Records doubled. In incremental, it should not insert the data that's already there right. Anyone knows how to change the cursor field which is reference column during incremental load?
p
I was literally just playing with this today, and it looks like
Incremental | Deduped + history
(with primary key defined as
distinct_id, event, time
- unfortunately
insert_id
is missing for some reason) created an
export
table which doesn't have duplicates. I'll have to run it again in the following days to confirm that the data is indeed loaded incrementally, and not fully refreshed on each run. Also, I noted that there's an intermediate table,
_airbyte_raw_export
which seems to have twice more records, and therefore worries me about the long-term impact of this strategy. (I'm working with BigQuery)
s
Whatever the setting in your screenshot, is that working well for incremental scenario?
p
As I mentioned, I'll have to run it again in the following days to confirm that the data is indeed loaded incrementally, and not fully refreshed on each run. If that's the case, then, yes it could work. I'm not entirely sure how robust the primary key is (distinct_id, event, time), I would have preferred to rely on
insert_id
, which is the Mixpanel defined unique id for events.
s
I just made a few attempts based on your suggestion. With the above setting as per your screenshot, we are getting two tables
export
and
export_scd
. export is doing incremental load correctly. When you too try, please let me know if it's the same to you as well.
p
I think the final output,
export
is indeed loaded incrementally My second run today shows that some records emitted in yesterday's run were not overwritten in today's run (341 on the screenshot attached) But I also get a lot of artefacts which I'm not sure I can get rid of. And they're a bit concerning in terms of storage costs: For instance,
_airbyte_raw_export
has now 4.7 times more rows than
export
, and there is no table expiry date set. So I imagine it is just going to keep on building up. In a few days, I will try to delete some of those tables and see how it affects the incremental load.
Also, I hope that building a unique key with
distinct_id, event, time
is robust enough. What do you think @Sharath Chandra?
s
Yes, it worked. Those temp tables can be deleted I guess. For now this worked for me. Thanks for your inputs and I will surely post if there is anything I see or I need for.
p
Just to confirm that after running it again today, I can see that some records extracted yesterday were again left alone, which, I believe, confirms that the incremental load is running as expected. I'm just not sure what exactly the incremental time window is.
u
Hello Sharath Chandra, it's been a while without an update from us. Are you still having problems or did you find a solution?