Hi. I'm using tap-postgres and target-postgres. I'm unable to use log-based replication, so I use a mix of incremental and full_table. I use incremental when I have a good last_modified column and no hard deletes. I use full table when I have a small table with hard deletes. I've set the settings so that I can correctly detect hard deletes. I've now discovered a table that's big and has hard deletes. That doesn't fit into either of my criteria. Now, I'm doing hourly meltano+dbt runs so that my data is fresh for my consumers. Is it possible to do a daily job that runs the problematic table as full table, so that it finds the hard deletes?

Hi @dylan_just - this one probably slipped through the cracks as our whole team was in London together. I think your question is fairly simply a - Yes. I've seen many customer setups where there's one daily, weekly, or monthly pipeline that's sole purpose it to catchup on the hard deletes. The other think I've seen work pretty well is a "look back window" where you use the created date to continuously load a number of days from a created date column. Logically this is making the table smaller if you see what I mean.

Oh hey Aaron, thanks for following up. What I ended up doing was my daily job first does a

state merge

to set the state back to epoch, then a

truncate table

, then runs the job as normal. The affected table then runs an incremental as normal, but from scratch. Works well. I'm "orchestrating" just with a shell script and running all taps in one go. I think the

--full-refresh

flag doesn't quite do what I need - I think it'd refresh all streams, which would be too slow for my data size. I can only handle the full refresh on certain tables. I'm wondering how we could make Meltano better handle my scenario. Perhaps if

--full-refresh

took a parameter that selects what taps and streams to refresh? For reference, here's the relevant parts of my orchestration script:

if [ "${FULL_TABLE_SWEEP:-}" = "true" ]; then
  STATE_ID="$MELTANO_ENVIRONMENT:tap-hamilton-v1-to-$LOADER"

  echo "Rewinding public-invitation and public-user, then emptying their raw tables" >&2
  uv run meltano "$ENV_ARG" state merge "$STATE_ID" '{"singer_state": {"bookmarks": {
    "public-invitation": {"replication_key_value": 0},
    "public-user": {"replication_key_value": "1970-01-01T00:00:00+00:00"}}}}'

  REWOUND_STATE="$(uv run meltano "$ENV_ARG" state get "$STATE_ID")"
  if ! grep -qF '"replication_key_value":0' <<<"$REWOUND_STATE" ||
     ! grep -qF '"replication_key_value":"1970-01-01T00:00:00+00:00"' <<<"$REWOUND_STATE"; then
    echo "Error: bookmarks did not read back as rewound; refusing to truncate." >&2
    exit 1
  fi

  PGPASSWORD="$WAREHOUSE_MELTANO_PASSWORD" psql \
    --host="$WAREHOUSE_HOST" --port="$WAREHOUSE_PORT" \
    --username=meltano_user --dbname="$WAREHOUSE_DBNAME" \
    --set=ON_ERROR_STOP=1 \
    --command='truncate table raw_hamilton.invitation, raw_hamilton."user"'
fi

echo "Running ${TAPS[*]} -> $LOADER then dbt-postgres:build ($ENV_ARG)" >&2
uv run meltano "$ENV_ARG" run "${BLOCKS[@]}" dbt-postgres:build