17with
18 source as (
30 *,
31 _metadata.file_path as source_file,
32 _metadata.file_modification_time as ingested_at
33 from
34 read_files(
37 '{{ var("landing_path") }}/postings',
38 format => 'json',
39 schemahints
40 => 'slug string, title string, …, created_at bigint'
41 )
43 ),
45 renamed as (
51 slug as posting_id,
63 timestamp_seconds(created_at) as posted_at,
72 from source
74 ),
76 deduplicated as (
91 select *
92 from renamed
93 qualify
94 row_number() over (partition by posting_id order by ingested_at desc) = 1
96 )
98select *
99from deduplicated