Add `upsert` and `overwrite` behaviors for `write_to_source` about dbt-fal HOT 3 CLOSED

I like the naming, and I like option 3 for the arg names.

from dbt-fal.

As discussed in a meeting, let's do:

• append: the default behaviour that writes data to source with no deduplication measures
• upsert: based on a given column, do a typical UPSERT operation

  -- Postgres example
  INSERT INTO distributors (id_col, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (id_col) DO UPDATE SET dname = EXCLUDED.dname;
  -- Note that the special excluded table is used to reference values originally proposed for insertion

• overwrite: Delete the contents of the table on the source and rewrite it

Then, this would be passed in a mode argument

def write_to_source(
        self,
        data: pd.DataFrame,
        target_source_name: str,
        target_table_name: str,
        mode: dict[str, str],
        dtype: Any = None,
):

and mode can have the values like

mode={'type': 'append'}
mode={'type': 'upsert', 'column': 'id_col'}
mode={'type': 'overwrite'}

or... we could do:

mode='append'
mode={'type': 'upsert', 'column': 'id_col'}
mode='overwrite'

or... we could add a new optional argument which we read only for upsert mode:

mode='append'
mode='upsert', upsert_column='id_col'
mode='overwrite'

from dbt-fal.

We did overwrite in #224, but have not done upsert yet. For now upsert is for the user (download the dataframe, update it locally, overwrite it) to do, since doing it on our side is not trivial (many adapters to support) and want to maybe consider how to do it better.

If anyone needs upsert, open a ticket and we will consider re-prioritizing it.

from dbt-fal.