I am looking more for concepts or pointed in the right direction for best practices for a project I am working on. I am currently playing around with Luigi for a data pipeline. I followed the tutorial here: https://kpatronas.medium.com/python-create-an-etl-with-luigi-pandas-and-sqlalchemy-d3cdc9292bc7 This was easy enough to get up and running. Where I am struggling conceptually is if I ran this pipeline nightly what is the best practice for handling data new or changing data from the source data? Question 1) Should you be gathering a full dataset from Source, and then overwriting the data at the Target? Or is it better to try and only insert new records? Ie. The difference between Source and Target.
Question 2) If you are only inserting new records, what is the best practice for handling update records? Is it better to capture the whole record with a date on it? Or am I updating the record that sits in the Target?