i'm wondering what how I could incrementally extract an

order_items

table from snowflake. the

order_items

has a relationship to the

orders

table, but does not have an

updated_at

column (which exists on the

orders

table). I'm not sure if there is any existing meltano tooling to handle this, something that would use a query such as below to extract data

select oi.*, o.updated_at
from order_items oi
join orders o on o.order_id = oi.order_id
-- filter for incremental ingestion
where 
o.updated_at > {last-updated-at}

and in the target, I would have to implement some more machinery to handle cases where an order_item is deleted something like an incremental delete+insert approach to merge from stg:

delete from order_items where order_id in (select distinct order_id from stg_order_items);
insert into order_items select * from stg_order_items;

a view would work; does your

order_items

table have an autoincrementing integer field? If so you can use that instead of relying on timestamps to bookmark state.

no, unfortunately no other monotonic field

yeah you have to use a view, I have to do the same thing for fields where my timestamps are occasionally outside of Python’s date datatype range (which is… ridiculous)