What's the best way to implement Daily Snapshot using Sling?

[karhendana]

In Functional Data Engineering concept, daily snapshot is useful for providing a historical record for analysis and reporting.
In BigQuery, it's implemented using daily partition, enabling independent processing of data for each day.
Sling has snapshot mode with _sling_loaded_at column with int64 type.

How can Sling's snapshot mode be optimally utilized to create daily snapshots that align with BigQuery's daily partitioning for efficient historical data analysis and reporting?

[flarco]

Hey, while testing the following replication example below, I found lacking logic, which has been fixed here.
Below should work well using this branch:

source: postgres
target: bigquery

streams:
  public.table:
    object: public.test_snapshot
    mode: snapshot
    target_options:
      table_keys:
        partition: [date(_sling_loaded_at)]

env:
  SLING_LOADED_AT_COLUMN: timestamp

[flarco]

You could also do this (should work with current version):

source: postgres
target: bigquery

streams:
  public.table:
    sql: "select *, current_date as snapshot_date from {stream_name}"
    object: public.test_snapshot
    mode: snapshot
    target_options:
      table_keys:
        partition: [snapshot_date]

[karhendana]

Thank you for your suggestions.
To guarantee the operation is idempotent, I think could be done with a delete from partition command in the pre_sql step.