Get new records from dataview

Hello,

To get the recent data fetched from the Vertica source, you can use a filter in the source query based on the etl_created_at or etl_updated_at columns.
The filter criteria depends on the frequency of the workflow execution. For example, if the workflow executes daily you can use the filter etl_created_at >= sysdate -1

You can modify the sysdate -1 criteria by getting it in different formats such as YYYY-MM-DD or DD-MM-YYYY

Thanks
Shahzeb

Lightweight partition copy increases performance by initially sharing the same storage between two tables.
If you call COPY_PARTITIONS_TO_TABLE and the target table does not exist, the function creates the table automatically.
See: https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/PartitionManagement/COPY_PARTITIONS_TO_TABLE.htm

create table fact01(reason_descr varchar(50), is_deleted_flg boolean, etl_created_at timestamp not null, etl_updated timestamp)
order by etl_created_at
segmented by hash(etl_created_at) all nodes
partition by date(etl_created_at)
;
CREATE TABLE
copy fact01 from stdin delimiter ',' abort on error;
select * from fact01 order by 3,4;
    reason_descr    | is_deleted_flg |       etl_created_at       |        etl_updated
--------------------+----------------+----------------------------+----------------------------
 november_bot_farm  | f              | 2022-08-05 08:05:00.679696 | 2022-08-05 08:05:00.679696
 november_bot_farm  | f              | 2022-08-05 08:05:00.679696 | 2022-08-05 08:05:00.679698
 november_bot_farm  | f              | 2022-08-05 08:05:00.697696 | 2022-08-05 08:05:00.679696
 may_bot_farm       | f              | 2022-08-05 08:05:14.011098 | 2022-08-05 08:05:14.011098
 may_bot_farm       | f              | 2022-08-05 08:05:14.011098 | 2022-08-05 08:05:14.011098
 may_bot_farm       | f              | 2022-08-05 08:05:15.211978 | 2022-08-05 08:05:15.211978
 may_bot_farm       | f              | 2022-08-05 08:05:18.486118 | 2022-08-05 08:05:18.486118
 may_bot_farm       | f              | 2022-08-05 08:05:18.486118 | 2022-08-05 08:05:18.486118
 may_bot_farm       | f              | 2022-08-08 19:27:52.824    | 2022-08-08 19:27:52.824
 september_bot_farm | f              | 2022-09-01 08:05:00.679696 | 2022-09-01 08:05:00.679696
 bot_farm           | f              | 2022-09-01 14:05:26        | 2022-09-01 14:05:26
(11 rows)

select current_date() -1;
  ?column?
------------
 2022-09-01
(1 row)

select COPY_PARTITIONS_TO_TABLE (
          'fact01',
          current_date() -1,
          current_date() -1,
          'last_date_records',
          true);
                              COPY_PARTITIONS_TO_TABLE
------------------------------------------------------------------------------------
 Created table last_date_records.
1 distinct partition values copied at epoch 525.

(1 row)

select * from last_date_records;
    reason_descr    | is_deleted_flg |       etl_created_at       |        etl_updated
--------------------+----------------+----------------------------+----------------------------
 september_bot_farm | f              | 2022-09-01 08:05:00.679696 | 2022-09-01 08:05:00.679696
 bot_farm           | f              | 2022-09-01 14:05:26        | 2022-09-01 14:05:26
(2 rows)

Hi ramik_allakhverdiev,

Well that would be a basic filter:

select *
  from MyTable
 where greatest(etl_created_at, etl_updated_at)::date >= current_date - 1
   and greatest(etl_created_at, etl_updated_at)::date <  current_date;