Thanks a lot @mlauber71 - that was exactly what I was thinking (and should have done in the first place, sry!!! 
)
I have attached a workflow to this post (disregard the csv reader, it was just to bring my example data into KNIME originally):
So what I have is this:
- A (SQLite) DB with 1,000 rows of transactional data and a date column
- A table with the same structure than the table in the DB (I basically got this from looking at the workflow that @izaychik63 shared above). This only has one value (2023-05-31) in the date column
What I want to achieve is that I can filter the DB on the database (so BEFORE reading it into KNIME) to only keep rows that are of that date (May 31st, 2023) or newer → this is my condition I have referred to above.
My business case:
I have several workflows that import data from tables from a company database. these range from a few 100,000 datapoints (like 30,000 rows and 20 columns) up to several 100,000,000 datapoints (3 million rows, 150 columns etc).
I have now made an initial load and transformation for the past two years, resulting in csv files (don’t ask me why it has to be csv) up to 2GB big.
This data needs to be updated daily so I want to avoid to always have that full load (last 2 years) and then delete duplicate rows in KNIME. I thought it might make more sense to do this on the database itself (somehow, if possible) because I was assuming this will speed up the workflow. I then read into KNIME only the subset, so basically the rows that have been added since I last read from the database. This of course will be a different time stamp for every execution of the workflow (I planned to store this in a temp .table).
Once this is set up all of these workflows should be scheduled on the KNIME Business Hub (but I want to try it out locally first).