Skip to content

DBT: S3 Usage By Prefix

DBT model name: s3_usage_by_prefix

Explore dependencies/lineage: link

Description

Download (REST.GET.OBJECT) and total request counts + bytes sent, by level-1 and level-2 key prefix, over the access-log window.

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_access_logs']) }}

-- Request counts and bytes-sent aggregated by level-1 and level-2 key prefix,
-- over the access-log retention window. `prefix_level` distinguishes the grain.

with events as (
    select
        operation,
        coalesce(bytes_sent, 0) as bytes_sent,
        split_part(key, '/', 1) as prefix_l1,
        case
            when position('/' in key) > 0
                then split_part(key, '/', 1) || '/' || split_part(key, '/', 2)
            else null
        end as prefix_l2
    from {{ ref('s3_access_log_events') }}
    where key is not null
),

l1 as (
    select 1 as prefix_level, prefix_l1 as prefix,
           count(*) as request_count,
           count(*) filter (where operation = 'REST.GET.OBJECT') as download_requests,
           sum(bytes_sent) as bytes_sent
    from events
    group by prefix_l1
),

l2 as (
    select 2 as prefix_level, prefix_l2 as prefix,
           count(*) as request_count,
           count(*) filter (where operation = 'REST.GET.OBJECT') as download_requests,
           sum(bytes_sent) as bytes_sent
    from events
    where prefix_l2 is not null
    group by prefix_l2
)

select
    *,
    round(bytes_sent / (1024 * 1024), 3) as mb_sent
from (
    select * from l1
    union all
    select * from l2
)
order by prefix_level, download_requests desc