Skip to content

DBT: S3 Recent Object Changes

DBT model name: s3_recent_object_changes

Explore dependencies/lineage: link

Description

Object creates/deletes seen in access logs since the latest inventory snapshot.

Details

Column Type Description

No data tests defined 🧐

Models

Models

No called script or script source not found.

-- Also tagged s3_inventory: the cutoff is the latest inventory snapshot, so it
-- must be rebuilt whenever a new snapshot lands (s3_files_latest overlays it).
{{ config(materialized='table', tags=['s3_access_logs', 's3_inventory']) }}

-- Object creates / deletes observed in the access logs since the latest inventory
-- snapshot. Bridges the gap between weekly inventories so recent changes are
-- visible before the next inventory lands.

with latest_snapshot as (
    select max(snapshot_ts) as ts_str
    from {{ ref('s3_inventory_snapshots') }}
),

cutoff as (
    -- snapshot_ts looks like 2026-05-31T01-00Z (UTC).
    select strptime((select ts_str from latest_snapshot), '%Y-%m-%dT%H-%MZ')
               at time zone 'UTC' as ts
)

select
    request_time,
    operation,
    case when operation like '%.DELETE.OBJECT' then 'deleted' else 'created' end
        as change_type,
    bucket,
    key,
    object_size,
    requester,
    remote_ip,
    http_status,
    source_log_key
from {{ ref('s3_access_log_events') }}
where
    operation in (
        'REST.PUT.OBJECT',
        'REST.POST.OBJECT',
        'REST.COPY.OBJECT',
        'REST.POST.UPLOAD',
        'REST.DELETE.OBJECT',
        'BATCH.DELETE.OBJECT'
    )
    and http_status >= 200 and http_status < 300
    and request_time > coalesce(
        (select ts from cutoff), '1970-01-01 00:00:00+00'::timestamptz
    )
order by request_time desc