Cost Governance & Performance Optimization in Snowflake

Snowflake Summit 2023에서 들은 Hands-On Lab: for Cost Optimization in Snowflake 세션에 대해 정리한 글입니다.

Agenda

• Cost Governance & Performance Optimization in Snowflake
• David Spezia, Principal Sales Engineer, Snowflake
• Optimizing cost and performance inside Snowflake — including visibility, control, and optimization of Snowflake spend.

강의 모습

HOW SNOWFLAKE METERING WORKS

데이터는 어디에나 존재하며, 스마트 기기와 연결된 장치로 인해 시간이 흐를수록 더 많은 데이터를 생성합니다. 데이터를 분석하고 유용한 정보로 만들기 위해서는 비지니스가 준실시간으로 무엇을 하고 있는지 이해하고, 합리적인 비용을 사용하기 위해 워크로드를 최적화하는 것이 중요합니다.

이번 세션에서는 일반적으로 Snowflake내에서 사용량이 측정이 어떻게 동작 하는지, Snowflake의 리소스와 비용 거버넌스에 대해 알아보았습니다.

아래 그림은 기존 데이터베이스와 Snowflake에서의 사용량 변화를 설명하는 그림입니다.

Flexible Architecture of Snowflake

Snowflake에서는 클라우드의 탄력성을 활용하여 사용량에 따라 비용이 부과됩니다. 이는 기존 데이터베이스의 고정 비용과는 차이점이 있습니다. Snowflake에서는 사용량에 따라 CPU가 변동하며, 시간에 따라 탄력적으로 확장하고 축소합니다. 클라우드를 활용하여 리소스가 사용되는 시점과 그렇지 않은 시점을 구분하여 워크로드에 적합한 크기를 가진 리소스를 사용해야 합니다. 즉, 컴퓨팅 파워를 유연하게 확장하거나 축소하고 필요한 만큼 스토리지를 탄력적으로 확장하는 방식으로 비용을 지불하게 됩니다.

과거에는 사용량을 예측해서 일정 수준을 기준으로 장비를 구매하였다면 Snowflake는 서버리스 형태로 솔루션을 이용할 수 있습니다. 필요한 만큼만 컴퓨터 엔진을 기동하여서 사용할 수 있고, 컴퓨터 엔진을 사용하는 안에서도 서로 다른 워크로드들이 리소스 엔진을 침범하지 않도록 리소스 엔진을 나눠서 구성할 수도 있습니다.

Snowflake에서의 비용은 몇 가지 다른 영역으로 나눌 수 있습니다.

Snowflake Layers

• Warehouse Compute
• Data Storage
• Data Transfer & Egress
• Cloud Services & Serverless Features

Snowflake는 컴퓨팅과 스토리지가 분리되어 더 이상 초기 서버 비용, 서버 유지 관리 및 라이선스 비용을 기준으로 비용을 계산하지 않아도 됩니다. 각 레이어의 특징에 대해 조금 더 상세히 살펴보겠습니다.

Warehouse Compute

Warehouse Compute 비용은 탄력성 패턴인 자동 중단, 확장, 축소, 초당 요금 청구를 기준으로 컴퓨팅 티어 또는 웨어하우스 티어에 대해 부과되는 금액입니다.

사용자는 이 부분에서 큰 제어권을 갖습니다. Warehouse Compute 비용은 Credit 소비의 가장 큰 부분을 차지하는 부분입니다. 해당 리소스들이 올바르게 활성화되고, 적절한 크기로 설정되며, 작업에 필요한 적절한 크기의 리소스로 라우팅되도록 보장해야 합니다.

Data Storage

다음은 Data Storage 영역입니다. 기본적으로 매월 테라바이트 당 가격이 책정되며 지역에 따라 약간씩 다르게 부과됩니다. FDN 형식을 사용함으로써 최대 7배 압축률을 얻을 수 있으며, JSON, Avro, Parquet 등 데이터 저장 형식에 대한 네이티브 지원이 내장되어 있습니다.

또한 Zero Copy Cloning, Time Travel, Transient Tables, Regular Tables, External Tables과 같은 기능들을 사용하는 것이 스토리지 비용에 어떤 영향을 미치는지를 이해하는 것이 중요합니다. 사용하지 않은 데이터, 일정 기간 이상 쿼리하지 않은 객체들을 파악하여 제거하는 것도 중요한 부분입니다. 실제 지출에서는 크게 영향을 미치지 않지만 데이터 스토리지를 관찰하는 것이 중요합니다.

Data Transfer & Egress

Snowflake 밖 또는 리전이나 클라우드 공급자 간에 데이터를 이동할때 드는 데이터 전송 비용이 있습니다.

다른 클라우드에서 실행되는 워크로드가 있을 경우, 이동 비용이나 데이터 전송 비용이 발생할 수 있으며, 같은 클라우드나 리전 내에서 결합 가능한 워크로드를 찾아보는 것이 좋습니다.

Cloud Services & Serverless Features

다음은 클라우드 서비스 부분 또는 Snowflake의 서버리스 기능입니다. 서버리스인 만큼 각각 자체적인 리소스를 사용하게 됩니다. 이에 따라 과금 체계도 독특한데, 일부 구성 요소들은 사용 요율에 따라 기준점을 갖고 그 기준점 백분율에 따라서 과금 되는 구조가 있습니다. 기본적으로 사용량을 기반으로 과금된다고 생각하시면 됩니다.

Resource & Cost Governance Frameworks in Snowflake

Snowflake의 비용 거버넌스에 대해 살펴보겠습니다.

Snowflake 비용 거버넌스는 Snowflake 클라우드 데이터 플랫폼 사용과 관련된 비용을 관리하고 최적화하는 프로세스를 의미합니다. 여기에는 사용 메트릭을 모니터링 하고 분석하여 비용을 절감할 수 있는 영역을 식별하여 지출을 제어하고, 예기치 않은 비용을 방지하기 위한 전략을 구현하는 작업이 포함됩니다. Snowflake는 Resource Groups, Budgets 및 Usage View를 포함하여 비용 거버넌스를 위한 다양한 도구와 기능을 제공합니다.

Cost Governance in Snowflake

비용 최적화를 위해 다음 세가지 측면에서의 관리가필요합니다

• Visibility
• Control
• Optimization

모니터링한 값들을 기반으로 제어가 필요하고, 최적화가 되지 않은 부분이 있다면 자원들을 찾아서 최적화까지 수행을 해주어야 합니다.

Snowflake는 위의 세가지 측면에서 비용을 절감할 수 있도록 여러가지 기능을 지원하고 있습니다.

• 사용량 대시보드나 사용 데이터 등을 통해 사용 현황을 파악할 때 Snowsite나 Snowflake의 기본 기능을 활용할 수 있습니다. 또한 객체 태그를 이용하여 지출을 다른 그룹이나 사업 부서로 구분하여 Snowflake 환경 내의 개별적인 문제를 처리하고 지출을 효율적으로 관리할 수 있습니다.
• 제어(Control)를 도입함으로써 웨어하우스 크기, 타임아웃 제한, 자동 중단, 자동 재개 등을 설정할 수 있습니다. 또한 Resource Monitor를 통해 필요 없는 리소스를 중지시킬 수도 있습니다.
• 또한, 최적화 측면에서는 지속적으로 플랫폼을 개선하며 모두에게 더 빠른 쿼리 실행을 제공하기 위해 노력하고 있습니다. 최적화를 위해 쿼리 프로파일과 히스토리, 쿼리 계획을 분석하고, 웨어하우스 로드 및 사용 기록을 확인하여 리소스의 크기와 활용을 더욱 최적화할 수 있습니다.

Best Practice

1. Obtain visibility into cost & implement chargeback

• 특정 워크로드, 웨어하우스, 작업 등과 같은 세분화된 사용률을 추적 합니다.
• chargeback/showback에 대한 특정 팀, 고객 또는 비용 센터(Tagging, cost attribution features)에 대한 지출 속성 및 집계를 합니다.

2. Proactive budgeting & alerting

• 계정 이나 맞춤형 리소스 그룹 수준으로 예산 책정이 가능합니. 리소스 모니터를 통해 지출 제한 및 초과 지출을 방지합니다.

3. ROI-based workload segmentation

• 최대한의 비용 및 ROI를 파악하여 재아키텍처, 사용량 감소/증가 또는 특정 워크로드 삭제에 대한 정보에 입각한 의사 결정을 내릴 수 있습니다.

4. Throttle resource allocation & scaling permissions

• 관리 팀은 웨어하우스 생성, 크기 조정 및 Scaling 제한과 같은 Credit 소비 운영 관련 정책을 관리하여 비용을 제어하고 불확실성을 줄입니다.

BUILT IN VISIBILITY

Snowsight 내의 Usage 탭에서 웨어하우스 별 Credit 사용량을 확인할 수 있으며 Tag 지정 및 드릴다운 기능을 통해 상세 내역을 확인할 수 있습니다.

UI-based cost exploration

snowflake-usage-app을 통해 Warehouse usage heatmaps, scatter plots (# of queries vs execution time) 등을 Streamlit 기반으로 확인할 수 있습니다.

Streamlit Snowflake usage app (github.com/streamlit/snowflake-usage-app)

ACCOUNT USAGE SCHEMA

Snowflake 내에서 사용하는 일부 제어 방식에 대해 이해하고, 어드민으로서 Snowflake 내의 제어 관련 데이터들과 모니터링할 수 있는 항목들을 살펴보겠습니다.

WHAT IS ACCOUNT USAGE?

• Does Snowflake have Logs?
• 64+ amazing views with usage and rich metadata about your Snowflake accounts
• Account Usage
• Object Billing
• Object Details
• Logins
• Session Details
• 1 Year of Data Retention

ACCOUNT USAGE 스키마는 Snowflake에서 가장 인기 있는 기능 중 하나입니다. 간단히 말해, Snowflake는 내부적으로 사용되는 로그를 가지고 있으며 이러한 로그를 가공하여 유용한 테이블로 만들고 모든 Snowflake 계정과 조직에 대해 이러한 테이블을 제공합니다. ACCOUNT USAGE 스키마에는 47개 이상의 유용한 테이블들이 있으며, Snowflake 계정에 대한 상세한 메타데이터를 제공합니다.

ACCOUNT USAGE 스키마는 계정 사용, 객체, 로그인, 세션 관련 데이터 등이 포함되며, 이들은 Snowflake의 기본 기능으로 제공되며 1년간의 전체 데이터 보관 기능이 내장되어 있습니다. 이는 Snowflake의 가장 유용한 기능 중 하나로, 모든 Snowflake 계정에서 동일한 계정 사용 스키마를 사용하기 때문에 사용자가 작성한 쿼리나 시각화를 커뮤니티와 공유할 수 있으며 모두가 이를 활용할 수 있습니다.

USEFUL OBJECTS

ACCOUNT USAGE 스키마에는 여러가지 유용한 객체들이 있습니다.

• Access History : Table Access Logs with Columns Access by QID
• Login History : Each initial session Login
• Query History : QID Level Details, very detailed
• Sessions : Session and HTTPS header information
• Users : Current and Deleted Users
• Warehouse Metering History : Hourly warehouse billing metrics

예를 들면, 접근 기록(access history)과 로그인 기록(login history) 등이 있습니다. 또한 파일 포맷에 대한 접근 기록(access history)과 같이 유용한 데이터들이 기록되어 있습니다. 특히, 테이블 수준의 접근 로그와 각 쿼리마다 접근한 컬럼들까지 기록된 정보들이 있어서 필요하지 않은 컬럼들을 찾아서 제거하는 등 최적화에 활용할 수 있습니다.

로그인 기록을 통해 어떤 인증 방법을 사용하여 로그인을 시도했는지, 실패한 로그인 시도가 있는지 등을 파악할 수 있습니다. 또한 쿼리 기록을 통해 각 쿼리에 대한 다양한 세부 정보들을 확인할 수 있습니다. 쿼리 텍스트, 데이터베이스, 스키마, 쿼리 유형, 세션 정보, 사용자 이름, 롤 이름, 사용된 웨어하우스 크기 등 다양한 정보들이 포함되어 있습니다.

이러한 정보들은 쿼리를 분석하는데 유용하게 사용할 수 있습니다. 다음 쿼리를 이용하여 각 정보들을 확인할 수 있습니다.

-- Context
use database snowflake;
use schema account_usage;

-- Show Views+
show views;

-- Access History
desc view access_history;
select * from access_history limit 10;

-- Login History
desc view login_history;
select * from login_history limit 10; 

-- Query History
desc view query_history;
select * from query_history limit 10;

-- Sessions
desc view sessions;
select * from sessions limit 10;

-- Users
desc view users;
select * from users limit 10;

-- Warehouse Metering History
desc view warehouse_metering_history;
select * from warehouse_metering_history limit 10;

Account Usage Harbor Tour

다음으로 Snowflake 내에서 사용할 수 있는 최적화 방법 중에서 가장 쉬운 쿼리들을 살펴보겠습니다.

Auto Suspend / Auto Resume

웨어하우스는 실행 시간에 대해 요금이 청구되므로 웨어하우스가 연중 무휴로 실행되도록 두지 말고 필요할 때만 사용해야 합니다.

먼저, 웨어하우스에 자동 중단(auto suspend) 정책이 설정되어 있는지 확인하는 것이 좋습니다. 또한, 자동 중단을 설정하면 자동으로 재개(auto resume)되도록 설정하는 것이 좋습니다. 이렇게 하면 웨어하우스에 다음 쿼리가 들어오면 몇 밀리초 내에 자동으로 재개됩니다.

자동 중단 정책은 비즈니스 인텔리전스(BI)와 같이 캐시 사용이 많은 경우에는 적당한 시간으로 설정하고, ETL 작업과 같이 데이터 변환이 끝난 후에는 웨어하우스를 꺼두는 것이 좋습니다.

Enable Auto Suspend

Auto Suspend Policies : 가상 웨어하우스가 유휴 상태가 된 후 신속하게 전원을 끄면 고객의 비용이 절약됩니다.

• 쿼리 캐싱의 이점을 활용하는 사용 사례는 더 오래 사용해야 합니다.
• 쿼리 캐싱을 사용하지 않는 ELT 및 데이터 로드는 즉시 해제해야 합니다

Enable Auto Resume

Auto Resume Policies : auto-suspended된 웨어하우스 의 경우 사용자 또는 시스템이 웨어하우스에 대해 쿼리를 실행할 때 auto resume기능이 다시 켜집니다.

• 여기서 잠재적인 지출을 주도하는 웨어하우스 확인
• 계정 수준에서 청구되므로 청구가 발생하기 전에 대부분의 계정에 상당한 여유 공간이 있음

Warehouses Approaching Cloud Services Billing

또 다른 간단한 모니터링 대상은 워크로드나 웨어하우스가 클라우드 서비스 요금에 접근하고 있는지입니다. 이는 계정 전체에 대한 하루 단위의 요금을 기준으로 합니다. 워크로드나 웨어하우스가 클라우드 서비스 요금을 사용하고 있는지를 파악하는 것이 중요합니다.

Warehouses Approaching Cloud Services Billing

Warehouse

다음 쿼리는 클라우드 서비스 비용이 워크로드의 높은 비율을 차지하는 웨어하우스를 살펴볼 수 있는 쿼리 입니다.

with
cloudservices as (
    select
        warehouse_name
        ,month(start_time) as month
        ,sum(credits_used_cloud_services) as cloud_services_credits, 
        count(*) as no_querys 
    from
        snowflake.account_usage.query_history
    group by
        warehouse_name
        ,month
    order by
        warehouse_name
        ,no_querys desc
),
warehousemetering as (
    select
        warehouse_name
        ,month(start_time) as month
        ,sum(credits_used) as credits_for_month
    from
        snowflake.account_usage.warehouse_metering_history
    group by
        warehouse_name
        ,month
    order by
        warehouse_name
        ,credits_for_month desc
)
select
    *
    ,to_numeric(cloud_services_credits/nullif(credits_for_month,0)*100,10,2) as perct_cloud 
from
    cloudservices
join
    warehousemetering using(warehouse_name,month)
order by
    perct_cloud desc;

Account

with
cloudservices as (
    select
        warehouse_name
        ,month(start_time) as month
        ,sum(credits_used_cloud_services) as cloud_services_credits, 
        count(*) as no_querys 
    from
        snowflake.account_usage.query_history
    group by
        warehouse_name
        ,month
    order by
        warehouse_name
        ,no_querys desc
),
warehousemetering as (
    select
        warehouse_name
        ,month(start_time) as month
        ,sum(credits_used) as credits_for_month
    from
        snowflake.account_usage.warehouse_metering_history
    group by
        warehouse_name
        ,month
    order by
        warehouse_name
        ,credits_for_month desc
)
select
    month
    ,sum(cloud_services_credits) as sum_cloud_services_credits
    ,sum(credits_for_month) as sum_credits_for_month
    ,to_numeric(sum_cloud_services_credits/nullif(sum_credits_for_month,0)*100,10,2) as perct_cloud 
from
    cloudservices
join
    warehousemetering using(warehouse_name,month)
group by
    month
order by
    perct_cloud desc;

Set Account & Warehouse Timeouts

또 다른 최적화 방법으로는 계정 수준과 웨어하우스 수준의 타임아웃 설정을 하는 것입니다. 비즈니스 인텔리전스(BI)와 분석용 웨어하우스에는 15분과 같이 상대적으로 짧은 타임아웃을 설정하고, 웨어하우스를 사용하지 않는 사용자들의 타임아웃을 설정하여 리소스를 효율적으로 관리할 수 있습니다.

Set Account & Warehouse Timeouts

Account Timeouts : 계정의 시간 초과는 ETL 작업과 비교하여 웨어하우스의 임계값 또는 범위에 도달하는 모든 장기 실행 쿼리를 중지합니다

• 최적화와 함께 사용자 경험을 고려합니다.
• 시간 초과된 쿼리를 보고하고 사용자에게 알림을 보내는 것이 좋습니다.

다음 쿼리로 Timeout 값을 설정할 수 있습니다.

alter warehouse load_wh set STATEMENT_TIMEOUT_IN_SECONDS = 3600;
show parameters in warehouse load_wh;

• Timeout in seconds for statements: statements are automatically canceled if they run for longer; if set to zero, max value (604800) is enforced.

PURGE DORMANT USERS

또한 잠재적으로 사용하지 않는 사용자들을 삭제하거나, 지난 30, 60, 90일 동안 로그인하지 않은 사용자들을 삭제하는 것도 좋은 방법입니다. 불필요하게 계정에 접근할 수 있는 위험을 최소화할 수 있습니다.

PURGE DORMANT USERS

Dormant Users : 시스템에 로그인하지 않는 사용자는 위험을 초래합니다.

• 오래된 사용자
• 로그인하지 않은 사용자

Never logged in users

show users;
select * from table(result_scan(last_query_id()))
where "last_success_login" is null
and datediff('day',"created_on",current_date) > 30;

• 30일 이상 로그인 하지 않은 사용자

stale users

show users;
select * from table(result_scan(last_query_id()))
where datediff('day',"last_success_login",current_date) > 90;

• 마지막 로그인 이후 90일이 지난 사용자

DROP UNUSED TABLES

마지막으로, 사용되지 않는 테이블들을 파악하여 삭제하는 것도 리소스 최적화에 도움이 됩니다.

DROP UNUSED TABLES

dml from the information schema to identify table sizes and last updated timestamps

select table_catalog || '.' || table_schema || '.' || table_name as table_path, 
    table_name, table_schema as schema,
    table_catalog as database, bytes,
    to_number(bytes / power(1024,3),10,2) as gb, 
    last_altered as last_use,
    datediff('day',last_use,current_date) as days_since_last_use
from information_schema.tables
where days_since_last_use > 14 --use your days threshold
order by bytes desc;

• Information Schema에서 테이블 크기와 마지막으로 업데이트된 timestamp를 식별

last dml on object

select (system$last_change_commit_time(
'database.schema.table_name')/1000)::timestamp_ntz;

queries on object in last nn days

select count(*) from snowflake.account_usage.query_history
where contains(upper(query_text),'table_name')
and datediff('day',start_time,current_date) < 90;

Useful Objects in Snowflake

Snowflake내에서 내 계정에 대한 데이터를 로컬로 복사하는 것이 좋습니다.

Copy Objects Locally

1. Does Snowflake have Logs?

2. Year of Data Retention

• This is the one that means the most to me
• SOX Compliance
• YoY Performance and Growth
• Account Migrations Keep History

3. Performance

• Cluster By Control
• Micro Partition Scope of Queries
• Query Compiler
• BI and Reporting

데이터 보존 기간이 1년 이상인 접근 기록, 세션 및 쿼리와 같은 데이터가 필요할 수 있으며, SOCKS 규정에 따라 최대 7년까지 데이터를 보존해야 할 수도 있습니다.

또한, 연간 성능 및 시스템 성장을 살펴보며 워크로드의 변화와 성능을 분석할 수 있습니다. 로컬로 복사한 데이터는 직접 클러스터링하고 HR(하우스 룰)에서 메타데이터를 추가하여 클러스터링을 더욱 최적화할 수 있습니다.

이렇게 성능과 1년 이상의 데이터 보존을 고려할 때, 데이터를 로컬로 복사하는 것은 매우 합리적인 선택입니다.

Copy the Most Useful Objects Locally

유용한 객체들을 로에 저장한 후, 시각화하여 활용할 수 있습니다.

Snowflake 내의 데이터베이스에 저장하기 위해 다음과 같이 데이터베이스를 생성한 후 선택합니다.

-- Context
use database sandbox;
use schema public;
use warehouse demo_wh;

select datediff('day',current_date,'1/1/2022'::date);
set date_delta = -220;
select $date_delta;
select dateadd('day',$date_delta,current_date);
select datediff('day',dateadd('day',$date_delta,current_date), current_date);

-- warehouse_metering_history
create or replace table sandbox.public.warehouse_metering_history as (  
    select * from snowflake.account_usage.warehouse_metering_history
    where start_time::date >= dateadd('day',$date_delta,current_date)
);

alter table sandbox.public.warehouse_metering_history cluster by (start_time::date);

select count(*) from sandbox.public.warehouse_metering_history;

select * from sandbox.public.warehouse_metering_history limit 10;

-- query_history
create or replace table sandbox.public.query_history as (  
    select *, hash(upper(query_text)) as query_text_hash from snowflake.account_usage.query_history
    where start_time::date >= dateadd('day',$date_delta,current_date)
);

alter table sandbox.public.query_history cluster by (query_text_hash, start_time::date);

select count(*) from sandbox.public.query_history;

select * from sandbox.public.query_history limit 10;

-- Estimated Query Credits
-- view est_job_credits
create or replace view sandbox.public.est_job_credits as (  
select distinct
 q.warehouse_name
 ,q.database_name
 ,q.user_name
 ,q.query_id
 ,q.start_time
 ,q.query_text
 ,hash(upper(q.query_text)) as sql_text_hash
 ,c.meter_day
 ,q.query_type
 ,q.role_name
 ,q.warehouse_size
 ,q.cluster_number
 ,q.execution_status
 ,q.credits_used_cloud_services
 ,c.credits as total_warehouse_credits
 ,zeroifnull(q.bytes_scanned) as bytes_scanned
 ,q.bytes_spilled_to_local_storage as bytes_spilled_to_disk
 ,q.bytes_spilled_to_remote_storage as bytes_spilled_to_s3
 ,q.rows_produced + q.rows_inserted + q.rows_updated + q.rows_deleted + q.rows_unloaded as rows_addressed_by_query
 ,q.percentage_scanned_from_cache
 ,q.partitions_scanned
 ,q.partitions_total
 ,q.compilation_time
 ,q.execution_time
 ,q.queued_provisioning_time
 ,q.queued_repair_time
 ,q.queued_overload_time
 ,q.transaction_blocked_time
 ,iff(q.partitions_total=0,0,q.partitions_scanned/q.partitions_total) as percentage_table_scanned
 ,sum(q.execution_time) over (partition by q.warehouse_name, q.database_name, c.meter_day, q.user_name, q.query_id) as total_query_time
 ,sum(q.execution_time) over (partition by q.warehouse_name, q.database_name, c.meter_day) as total_warehouse_time
 ,zeroifnull(((sum(q.execution_time) over (partition by q.warehouse_name, c.meter_day, q.user_name, q.query_id))/nullif((sum(q.execution_time) over (partition by q.warehouse_name, c.meter_day)),0))::number(38,5)) as query_ratio
 ,zeroifnull((c.credits*(sum(q.execution_time) over (partition by q.warehouse_name, q.database_name, c.meter_day, q.user_name, q.query_id))/nullif((sum(q.execution_time) over (partition by q.warehouse_name, c.meter_day)),0))::number(38,5)) as est_credits
from sandbox.public.query_history q
left join
(
   select 
    warehouse_name
    ,to_char(start_time, 'yyyy-mm-dd') as meter_day
    ,sum(credits_used) as credits
   from sandbox.public.warehouse_metering_history
   group by 1,2
) as c
on q.warehouse_name=c.warehouse_name 
and to_char(q.start_time, 'yyyy-mm-dd')=c.meter_day
where 
q.execution_time>0              
and c.credits>0
-- and to_char(start_time, 'yyyy-mm-dd')='2018-08-20'
--   if you want to look at certain time windows
-- and c.warehouse_name='ds_wh'
--   or a certain warehouse
order by est_credits desc
);

select count(*) from sandbox.public.est_job_credits;

select * from sandbox.public.est_job_credits limit 10;

-- est_job_credits as a table
create or replace table sandbox.public.tbl_est_job_credits as (  
 select * from sandbox.public.est_job_credits
);

alter table sandbox.public.tbl_est_job_credits cluster by (sql_text_hash, meter_day);

select * from sandbox.public.tbl_est_job_credits limit 10;

-- Quality Control Query
select sum(est_credits) as sum_est_credits,
    sum(credits_used_cloud_services) as sum_credits_used_cloud_services,
    sum(est_credits)+sum(credits_used_cloud_services) as total_est_credits,
    (select sum(credits_used) as credits from snowflake.account_usage.warehouse_metering_history
     where start_time::date >= dateadd('day',$date_delta,current_date)) as wmh_credits
from sandbox.public.est_job_credits;

다음은 Tableau를 이용하여 위에서 생성한 테이블을 시각화한 예시입니다.

Tableau Visualization Example

Scatterplots

Drilling into Details

Drilling into All Queries Ran

Optimizations & Serverless Monitoring

다음으로, 시각화에서 확인되지 않은 다른 최적화 가능한 리소스에 대해 알아보고자 합니다.

특히 중요한 ‘이상적인 성능 쿼리’라고 할 수 있는 쿼리가 있습니다. 이 쿼리는 7일 기간의 데이터 차이를 보여주며 웨어하우스에서 얼마나 Credit을 소비하는지 확인할 수 있습니다. 이상적인 성능 쿼리를 분석하고 리소스 사용을 최적화함으로써 웨어하우스 사용에 대한 효율성을 높일 수 있습니다. 이렇게 최적화 가능한 리소스를 파악하고 개선해 나가면 더욱 효율적인 데이터 처리와 비용 관리가 가능해질 것입니다.

Snowflake 성능 지수는 실제 고객들이 실제 프로덕션 워크로드에서 경험한 Snowflake 성능의 양적 변화를 지속적으로 측정하는 일련의 지표입니다.

이는 쿼리를 컴파일하는 데 걸리는 시간(구문 분석, 의미 분석, 쿼리 재작성, 메타데이터 기반 가지치기, 비용 기반 최적화)과 실행하는 데 걸리는 시간의 추세를 추적합니다. 이러한 지표를 통해 고객들이 실제 운영 환경에서 겪는 성능 변화를 지속적으로 모니터링하고 분석할 수 있습니다.

Identify Longest Running Queries

가장 오래 실행 중인 쿼리들을 확인해볼 수 있습니다. 이 쿼리들을 최적화하거나 개선하여 성능을 향상시킬 수 있습니다

Top 25 Longest Queries

-- Longest Running Queries
select
    query_id,
    user_name,
    query_text,
    (execution_time / 60000) as exec_time
from
    snowflake.account_usage.query_history
where
    execution_status = 'SUCCESS'
order by
    execution_time desc
limit
    25;

Dive into specific queries with Query Profile

Dashboard from Monitoring Snowflake with Snowsight by Ashish Patel

Queries that Spill to Disk or BLOB

“Spilling”이란 쿼리 실행 중에 메모리에 들어갈 수 없는 큰 데이터 집합을 처리하기 위해 디스크 공간을 사용하는 개념입니다. 이는 Warehouse의 메모리 부족 문제를 해결하기 위한 방법으로, 실행 중인 쿼리가 메모리 용량을 초과하는 경우 디스크로 데이터를 Spill하여 임시로 저장하고 필요할 때 다시 메모리로 로드하여 쿼리를 계속 실행할 수 있게 해줍니다.

“BLOB spilling”은 큰 BLOB데이터를 디스크로 Spill하는 방식을 말하며, “Disk spilling”은 일반적인 테이블 데이터를 디스크로 Spill하는 방식을 의미합니다.

이는 추가 IO 작업을 의미하므로 작업을 메모리 용량에 최적화 된 쿼리보다는 처리 시간이 오래 걸립니다. 따라서 Spilling 되는 쿼리를 확인하여 Warehouse 크기 조정, 데이터 배치 사이즈 조정 등을 통해 쿼리를 최적화 할 수 있습니다.

다음과 같은 쿼리를 통해 90일 동안 Spilling 된 쿼리는 확인 해 볼 수 있습니다.

Top Disk Spillers

-- Spillers
set date_delta = -90;

-- Disk Spillers
select top 25
    hash(query_text) as query_hash
    ,min(query_text) as query_text
    ,count(query_id) as query_count
    ,avg(total_elapsed_time) as avg_total_elapsed_time
    ,sum(bytes_spilled_to_local_storage) / 1024 / 1024 / 1024 as sum_gb_spilled_to_disk
    ,sum(bytes_spilled_to_remote_storage) / 1024 / 1024 / 1024 as sum_gb_spilled_to_blob
from
    snowflake.account_usage.query_history
where
    start_time::date >= dateadd('day',$date_delta,current_date)
group by
    query_hash
having
    query_count > 5
order by
    sum_gb_spilled_to_disk;

Top BLOB Spillers

-- BLOB Spillers
select top 25
    hash(query_text) as query_hash
    ,min(query_text) as query_text
    ,count(query_id) as query_count
    ,avg(total_elapsed_time) as avg_total_elapsed_time
    ,sum(bytes_spilled_to_local_storage) / 1024 / 1024 / 1024 as sum_gb_spilled_to_disk
    ,sum(bytes_spilled_to_remote_storage) / 1024 / 1024 / 1024 as sum_gb_spilled_to_blob
from
    snowflake.account_usage.query_history
where
    start_time::date >= dateadd('day',$date_delta,current_date)
group by
    query_hash
having
    query_count > 5
order by
    sum_gb_spilled_to_blob;

Access Logs by Application & Query Count

또한 테이블 액세스 로그를 확인하고 어떤 애플리케이션에서 어떤 쿼리를 얼마나 자주 실행하는지 확인할 수 있습니다.

페이로드에 있는 클라이언트 애플리케이션 ID를 통해 애플리케이션별 액세스를 그룹화할 수 있습니다. 최근 45일 동안 액세스 기록을 살펴보고, 모든 쿼리에 대해 액세스된 실제 테이블과 컬럼 ID를 추출하여 테이블 액세스를 파악할 수 있습니다. 이 정보를 종합하여 사용자별, 애플리케이션별, 테이블별로 어떤 것들이 가장 많이 액세스되는지 확인할 수 있습니다.

-- How much data to pull in days -90 = last 90 days including today
set date_delta = -45;

-- Query pulling this all together and aggregating
with queries as
    (
      select
        query_id
        ,user_name
        ,role_name
        ,warehouse_name
        ,session_id
      from snowflake.account_usage.query_history
      where start_time::date >= dateadd('day',$date_delta,current_date)
      order by session_id
    ),
accessLogs as
    (
      select
        query_id as query_id1
        ,direct_objects_accessed
        ,doa.value:"objectName"::string as object_name
        ,doa.value:"objectDomain"::string as object_type
        ,split_part(object_name,'.',1) as database_name
        ,split_part(object_name,'.',2) as schema_name
        ,split_part(object_name,'.',3) as table_name
      from snowflake.account_usage.access_history,
      lateral flatten(direct_objects_accessed) doa
      where query_start_time::date >= dateadd('day',$date_delta,current_date)
    ),
sessions as
    (
      select
        session_id
        ,parse_json(client_environment):APPLICATION::string as application
      from snowflake.account_usage.sessions
      where created_on::date >= dateadd('day',$date_delta,current_date)
    )
select
    user_name
    ,role_name
    ,application
    ,object_name
    ,object_type
    ,database_name
    ,schema_name
    ,table_name
    ,count(distinct query_id) as query_count
from
    (select *
    from queries
    join accessLogs on queries.query_id = accessLogs.query_id1
    join sessions on queries.session_id = sessions.session_id)
where
    user_name <> 'WORKSHEETS_APP_USER'
group by
    user_name
    ,role_name
    ,application
    ,object_name
    ,object_type
    ,database_name
    ,schema_name
    ,table_name
order by
    query_count desc
;

Cache Usage and Full Table Scans

Cache Usage

-- 04.05 Warehouse Cache Usage
select
    warehouse_name
    ,count(*) as query_count
    ,sum(bytes_scanned) as bytes_scanned
    ,sum(bytes_scanned*percentage_scanned_from_cache) as bytes_scanned_from_cache
    ,sum(bytes_scanned*percentage_scanned_from_cache) / sum(bytes_scanned) as percent_scanned_from_cache
from
    snowflake.account_usage.query_history
where
    start_time >= dateadd(month,-1,current_timestamp())
    and bytes_scanned > 0
group by 1
order by 5 desc
;

Full Table Scans

-- 04.06 Full Table Scans
select
    user_name
    ,query_id
    ,partitions_scanned
    ,partitions_total
    ,query_text
from
    snowflake.account_usage.query_history
where
    start_time >= dateadd(month,-1,current_timestamp())
    and partitions_scanned > (partitions_total*0.95)
    and query_type not like 'create%'
    and user_name not in ('SYSTEM', 'MLU_METRICS')
order by 3 desc
limit 100;

Serverless Cost Visibility

서버리스 비용 접근에 대해서도 자동 클러스터링, 구체화된 뷰 갱신, 검색 최적화, 스노우 파이프, 복제 비용 등에 대한 시각화를 제공합니다.

이렇게 최적화와 비용 확인을 빠르게 수행할 수 있는 방법을 소개하였습니다. 다양한 최적화 방법과 서버리스 비용 접근 방법을 활용하여 최적화 및 개선을 진행할 수 있습니다.

Resource Monitors

Snowflake에는 Resource Monitor라는 객체 클래스가 있습니다. 이 Resource Monitor를 사용하면 다양한 예산을 설정하여 서로 다른 Snowflake 계정 및 웨어하우스에 대해 경고 및 중단 조치를 설정할 수 있습니다.

예를들어 100 Credit을 보유한 상황에서 크레딧의 사용 제한을 설정하자 할 때, 50% 썼을 때 알람, 70% 썼을 때 멈춤, 80% 썼을 때 즉시 정지할 수 있는 등의 정책을 설정할 수 있습니다. 또한 사용자별로 Scale up등의 수정에 대한 제어를 하고자 할때, 웨어하우스 사용은 할 수 있으나 수정은 할 수 없도록 권한을 제어할 수 있습니다.

이를 통해 사용자는 웨어하우스 사용에 대한 통제력을 강화하고 비용을 효율적으로 관리할 수 있습니다. 예산을 설정하여 웨어하우스가 비정상적으로 많은 Credit을 소비하는 것을 방지하고, 경고 시스템을 통해 빠르게 대응하여 예산 초과를 방지할 수 있습니다.

Apply Resource Monitors with UI

Snowsight UI에서 Resource Monitor를 생성할 수 있습니다.

Apply Resource Monitors with SQL

웹 UI 뿐만 아니라 SQL을 통해서도 Resource Monitor를 관리할 수 있습니다.

-- Warehouses Without RM
show warehouses;

select * from table(result_scan(last_query_id()))
where "resource_monitor" = 'null';

-- Create RM
create or replace resource monitor "curtlovescubes_rm" with credit_quota = 150 
 triggers 
 on 75 percent do notify 
 on 90 percent do notify;
alter warehouse curt_wh set resource_monitor = "curtlovescubes_rm";

show resource monitors;

SQL로 웨어하우스에 적용된 Resource Monitor를 확인 하거나 적용 여부도 파악 할 수 있습니다. SQL을 사용하면 웹UI와 마찬가지로 여러 대시보드와 웨어하우스, 리소스에 Resource Monitor를 쉽게 적용할 수 있습니다.

또한 새로운 기능으로 Resource Group을 설정하여 다른 테이블, 웨어하우스, 서버리스 기능 등에 예산을 할당할 수 있으며, 이를 통해 계정이나 웨어하우스보다 높은 수준에서 예산을 모니터링할 수 있습니다.

Visibility

Control

Resource Monitor를 적절하게 활용함으로써 Snowflake 계정의 사용량을 최적화하고 비용을 절감하는데 도움이 됩니다.

Snowsight Dashboard

Snowflake에서는 어떤 유저가 어떤 쿼리를 실행했는지, 어떤 데이터에 접근했는지, 쿼리가 얼마나 수행되었는지, 네트워크 사용량은 얼마나 되는지 등을 기록하고 있습니다. 이러한 정보들은 테이블로 제공되며, 대시보드라는 기능을 통해 해당 테이블을 시각적으로 확인할 수 있습니다.

예를 들어 최근 한 달 동안의 데이터를 모니터링하는데 사용할 수 있습니다.

Snowsight Dashboard

대시보드를 구성하면 최근 한 달 동안의 데이터 접근과 쿼리 실행 정보를 한눈에 확인할 수 있습니다. 이를 통해 어떤 유저가 어떤 데이터에 자주 접근하거나 어떤 쿼리가 자주 수행되는지 등의 패턴을 파악할 수 있수 있으며 네트워크 사용량도 모니터링할 수 있습니다.

이러한 대시보드를 통해 데이터 사용량 및 성능 관련 정보를 쉽고 효율적으로 파악하고 워크로드를 최적화할 수 있습니다.

마치며

지금까지Snowflake Summit 2023에서 들은 Hands-On Lab:for Cost Optimization in Snowflake 세션에 대해 정리 해 보았습니다. Snowflake에서비용을 합리적으로 사용 할 수 있는 기능들을 소개해드렸는데 많은 도움이 되셨길 바랍니다.