Skip to content

DBT: S3 Inventory Diff

DBT model name: s3_inventory_diff

Explore dependencies/lineage: link

Description

Added / deleted / modified objects between the two most recent inventory snapshots.

Details

Column Type Description

No data tests defined 🧐

Not referenced by any model or exposure.

Models

No called script or script source not found.

{{ config(materialized='table', tags=['s3_inventory']) }}

-- Object-level changes between the two most recent inventory snapshots:
-- added / deleted / modified (ETag changed). Empty when fewer than two
-- snapshots exist (history accrues from the first run forward).

with snaps as (
    select distinct snapshot_ts
    from {{ ref('s3_inventory_snapshots') }}
    order by snapshot_ts desc
    limit 2
),

latest_ts as (select max(snapshot_ts) as ts from snaps),
prev_ts as (select min(snapshot_ts) as ts from snaps),

cur as (
    select key, etag, size_bytes, last_modified
    from {{ ref('s3_inventory_snapshots') }}
    where snapshot_ts = (select ts from latest_ts)
),

prev as (
    select key, etag
    from {{ ref('s3_inventory_snapshots') }}
    where snapshot_ts = (select ts from prev_ts)
)

select
    coalesce(cur.key, prev.key) as key,
    (select ts from prev_ts) as previous_snapshot_ts,
    (select ts from latest_ts) as latest_snapshot_ts,
    case
        when prev.key is null then 'added'
        when cur.key is null then 'deleted'
        when cur.etag is distinct from prev.etag then 'modified'
        else 'unchanged'
    end as change_type,
    cur.size_bytes,
    round(cur.size_bytes / (1024 * 1024), 3) as size_mb,
    cur.last_modified
from cur
full outer join prev on cur.key = prev.key
where
    (select ts from latest_ts) <> (select ts from prev_ts)
    and (prev.key is null or cur.key is null or cur.etag is distinct from prev.etag)