Hello Best Practices, question for `target-parquet...
# best-practicese
emcp
10/22/2022, 4:58 PMHello Best Practices, question for
target-parquettarget-postgresqlemcp
10/22/2022, 4:59 PMit feels like this is a TAP problem and I need to rectify it there
a
aaronsteers
10/22/2022, 6:20 PMHi, @emcp! This is a fun and important question and it comes up often with file-based targets like CSV, JSONL, and Parquet. I think I might log as a GutHub and/StackOverflow discussion. But to kick off the discussion, I'll put my two cents here:
First, to clarify, is your desired behavior that the target Parquet be loaded with zero duplicate record histories when running INCREMENTAL sync, or just that - when running a FULL_TABLE sync, you don't want multiple copies of the entire dataset?
aaronsteers
10/22/2022, 6:24 PMI should also ask, before getting too deep into the question, have you ruled out using dbt for deduping in your transformation layer? Most of the good solutions to deduping will eventually require some transformation logic - so I'm curious if you are using dbt for subsequent transformations or if you have another requirement.
e
emcp
10/22/2022, 7:09 PMI am pushing to parquet and then utilizing TRINO.. I tried looking for DBT based solutions but.. since I wrote the tap I think it's important to push the data in the best most straight forward manner according to what I would expect.. there is no incremental optional just a full on sync (I use the GUI so.. select options are ignored) .. DBT seems to require more code at the moment to implement with TRINO / Parquet on S3
emcp
10/22/2022, 7:10 PMI will lean to .. writing the tap core over again.. and deduping from there
emcp
10/22/2022, 7:13 PMdesired behavior is.. I query the 3rd party API.. and theres zero value in duplicates.. if a user queries
AAAemcp
10/22/2022, 7:14 PMreference : https://interactivebrokers.github.io/tws-api/introduction.html ,
The tap is a python client which calls a C++ Thrift Server.. I will de-dupe probably at the thrift server level
emcp
10/22/2022, 7:15 PMI'll give a full tour Wednesday in Stockholm .. including the Meltano latest and greatest 2.8.x.. excited to share a bit how much headache this has all saved me as a structured data n00b
a
aaronsteers
10/22/2022, 7:17 PMLots here I want to dive into more... but one more clarification question? Do you care about deleted prior records that no longer exist? And/or prior versions of records that changed? I think 'no' to the latter question but less sure of the first.
e
emcp
10/22/2022, 7:25 PMRecords deleted actually REALLY matter.. but the vendor does not provide that data.. hence it is up to me to keep a constant record of data coming out of the vendor
emcp
10/22/2022, 7:26 PMI query on a weekly basis for the latest fresh data.. and have to sort it out myself (did a company go bankrupt and delist? did it simply just change tickers? ie GOOGL becoming ABC ..etc...
emcp
10/22/2022, 7:27 PMso yes I absolutely care about prior data... the way I fix it is I append the datetime stamp which the data was retrieved
emcp
10/22/2022, 7:27 PMso every row.. I have the timestamp in UTC when it was pulled
emcp
10/22/2022, 7:28 PMthere should never be a duplicate of those.. hence the thought, I have a bug somewhere in my C++ (not surprising and I am thinking to try out RUST thrift server going forward)
a
aaronsteers
10/22/2022, 9:46 PMThanks for confirming. So, because you can only pull as a full table sync and because you do need to observe what was present in prior runs but missing in the current, the "full table append" and "full table upsert" do seem like your best choices. And that leads us back to your initial ask about the Parquet target not supporting upserts/merges based on primary key...
Certainly the Parquet target could have a merge upsert feature added, but in a file based system, it's a little tricky to update the existing files. What the target would likely need to do is to create a new dataset that contains the merge upsert result and then swap the new and old files. The file swap itself may still create a blip of "downtime" but for some scenarios that could be acceptable. (Perhaps there are creative ways of resolving - like uploading the new dataset on top of the old, but it's just a different problem than using a database with table locks and transactions.)
aaronsteers
10/22/2022, 9:48 PMIf you can get incremental data from the source, you'd have a smaller dataset, but you likely may still end up with two types of duplicates:
• Duplicates from old/newer versions of the same record.
• Duplicates from "ties" on the bookmark key, because most taps use a "greater than or equal to" logic to ensure no records are every missed.
aaronsteers
10/22/2022, 9:49 PMIs this helpful at all? I'm curious if any of those routes sounds more/less attractive for your use case.
e
emcp
10/22/2022, 9:55 PMI think the wrinkle here is.. once I have the parquet file.. I utilize a micro service to upload it to an S3 bucket
emcp
10/22/2022, 9:56 PMand the target will have no knowledge or connection to any of that prior data.. it's merely a dumb pipe to dump whatever the latest full table sync gives me from the vendor
emcp
10/22/2022, 9:56 PMand the data is always unique due to the appending of whatever the datetime stamp at time of triggering was
emcp
10/22/2022, 9:58 PMthis source is ... basically Interactive Brokers.. they won't give old data.. there's probably some dataset you buy for that in their catalog.. but I am just sort of learning the ropes and downloading the data as it comes (snapshot) .. and appending it to a downstream RAW S3 bucket.. from there.. I plan to either use DBT, Airflow + Python, etc... to create my analytics tables for subsequent queries on the same vendor. You need to know what the valid ticker symbols are at any given moment.. in order to then go and query the news