SQL로 기대값 사용

Enterprise Edition 기능 — 데이터 품질 모니터링(Data Quality Monitoring)은 Enterprise Edition이 필요해요. 업그레이드 문의는 Snowflake 지원에 연락해 주세요.

데이터 메트릭 함수(DMF)에서 값을 반환하면 유용한 정보를 제공하지만, 데이터에 대해 무엇이 허용 가능한지 알지 못하면 그 값이 데이터 품질 문제를 나타내는지 알기 어려울 수 있어요. 예를 들어 특정 컬럼에 NULL 값이 10개 미만인 테이블을 데이터 품질 검사를 통과한 것으로 간주할 수 있어요. 이 경우 값이 10 미만일 것으로 기대하고, 그 값을 초과할 때만 알림을 받고 싶을 거예요.

*기대값(expectation)*은 데이터가 DMF가 수행한 데이터 품질 검사를 통과하는지에 대한 기준을 정의할 수 있게 해줘요. DMF가 값을 반환하면 그 값을 이 기준과 비교해 데이터가 검사를 통과했는지 실패했는지 판단해요. 실패한 반환 값은 기대값 위반으로 보고되므로 데이터에 적절한 조치를 취할 수 있어요.

참고 — 이 주제는 SQL을 사용해 기대값을 설정하고 모니터링하는 방법을 설명해요. DMF와 기대값으로 구성된 데이터 품질 검사를 사용자 인터페이스로 설정하려면 Snowsight로 데이터 품질 검사 설정하기를 참고해요.

다음은 컬럼 C1에 NULL 값이 10개 미만이라는 기대값을 만드는 예시예요.

ALTER VIEW v1
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (C1)
  EXPECTATION my_exp ( VALUE < 10);

시스템 DMF와 사용자 지정 DMF 모두에 대해 기대값을 정의할 수 있어요.

출처: Documentation

본문

기대값을 충족하는 것의 정의

기대값은 기대값이 충족되었는지 여부를 결정하는 부울 표현식을 포함해요. 이 표현식이 TRUE로 평가되면 DMF 결과가 기대값과 일치했다는 뜻이에요.

표현식 안에서 VALUE 키워드는 DMF가 반환한 값을 나타내요. 예를 들어 다음과 같은 기대값 정의가 있다고 가정해요.

EXPECTATION my_exp (VALUE < 5)

Snowflake는 기대값을 평가할 때 VALUE를 DMF가 반환한 값으로 대체해요. DMF가 3을 반환했다면 표현식이 TRUE로 평가되므로 기대값이 충족돼요.

표현식이 FALSE로 평가되면 Snowflake는 그것을 기대값 위반으로 보고해요. 이러한 위반을 추적하는 방법은 기대값 위반 식별을 참고해요.

표현식은 다음 유형의 연산자를 포함할 수 있어요.

표현식은 다른 테이블이나 뷰, 또는 UDF(사용자 정의 함수)를 참조할 수 없어요.

기대값 만들기

DMF와 객체 사이의 각 연결은 기대값을 하나 이상 가질 수 있어요.

DMF를 테이블 또는 뷰와 연결할 때 기대값을 추가하거나, 나중에 연결에 추가할 수 있어요. 또한 기존 기대값을 수정할 수 있어요.

기대값을 추가한 후에는 DMF가 일정에 따라 실행될 때까지 기다리지 않고 수동으로 테스트할 수 있어요.

DMF를 연결할 때 기대값 추가

ALTER TABLE 또는 ALTER VIEW 명령을 사용해 DMF를 테이블 또는 뷰와 연결해요. 연결을 만드는 것과 같은 SQL 문에서 연결에 기대값을 추가할 수 있어요.

예를 들어 DMF를 테이블과 연결할 때 기대값을 추가하는 문법은 다음과 같아요. 뷰는 유사한 문법을 사용해요.

ALTER TABLE <table>
  ADD DATA METRIC FUNCTION <dmf>
    ON (<col_name> [ , ... ] [ , TABLE<table_name>( <col_name> [ , ... ] ) )
    [ EXPECTATION <expectation_name> ( <expression> )
      [, <expectation_name> ( <expression> ) [ , ... ] ] ]

여기서:

*expectation_name*은 기대값을 식별하는 데 사용되는 문자열이에요. 서로 다른 연결에 속하는 한 같은 이름으로 기대값을 만들 수 있어요.
*expression*은 DMF가 기대 값을 반환했는지 여부를 결정하는 부울 표현식이에요. 기대값을 충족하는 것의 정의를 참고해요.

예제: 단일 기대값 추가

MAX 시스템 DMF를 뷰 v1과 연결해 컬럼 c1의 최대값을 확인한다고 가정해요. 최대값이 25에서 50 사이일 것으로 기대해요.

ALTER VIEW v1
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.MAX ON (C1)
    EXPECTATION my_exp ( 25 < VALUE AND VALUE < 50);

MAX DMF가 이 기대 값 범위 밖의 값을 반환하면 Snowflake는 그것을 기대값 위반으로 기록해요.

예제: 여러 기대값 추가

테이블이 5분 내에 업데이트되지 않았을 때, 그리고 다시 30분 동안 업데이트되지 않았을 때 알림을 받고 싶다고 가정해요. 다음 기대값을 추가한 다음 기대값이 위반된 때를 확인할 수 있어요.

ALTER TABLE emp
ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.FRESHNESS ON (last_updated)
  EXPECTATION lessThan5Mins (VALUE < 300), lessThan30Mins (VALUE < 1800);

기존 연결에 기대값 추가

ALTER TABLE 또는 ALTER VIEW 명령을 사용해 DMF와 테이블 또는 뷰 사이의 기존 연결에 기대값을 추가해요.

예를 들어 테이블과 DMF 사이의 연결에 기대값을 추가하는 문법은 다음과 같아요. 뷰는 유사한 문법을 사용해요.

ALTER TABLE <table>
  MODIFY DATA METRIC FUNCTION <dmf>
    ON (<col_name> [ , ... ] [ , TABLE<table_name>( <col_name> [ , ... ] ) )
    [ ADD EXPECTATION <expectation_name> ( <expression> )
      [, <expectation_name> ( <expression> ) [ , ... ] ] ]

여기서:

*expectation_name*은 기대값을 식별하는 데 사용되는 문자열이에요. 서로 다른 연결에 속하는 한 같은 이름으로 기대값을 만들 수 있어요.
*expression*은 DMF가 기대 값을 반환했는지 여부를 결정하는 부울 표현식이에요. 기대값을 충족하는 것의 정의를 참고해요.

예제

이전에 my_table 테이블의 c1 컬럼과 NULL_COUNT 시스템 DMF를 연결했다고 가정해요. 컬럼 c1에 NULL 값이 10개 이상일 때 알림을 받을 수 있도록 기대값을 추가하려면 다음 문을 실행해요.

ALTER TABLE my_table
  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (c1)
    ADD EXPECTATION my_exp (VALUE < 10);

NULL_COUNT의 결과가 15이면 기대값 위반으로 보고돼요.

기존 기대값 수정

MODIFY EXPECTATION 절을 사용해 이전에 연결에 추가한 기대값의 표현식을 변경해요.

예를 들어 이전에 테이블 t1과 NULL_COUNT DMF 사이의 연결에 기대값 my_exp를 추가했다고 가정해요. 컬럼 c1에 NULL 값이 15개 이상일 때 위반되도록 기대값을 수정하려면 다음 문을 실행해요.

ALTER TABLE t1
  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (c1)
    MODIFY EXPECTATION my_exp (VALUE < 15);

기대값의 이전 표현식은 VALUE < 15로 교체돼요.

기대값 테스트

기대값을 추가한 후 SYSTEM$EVALUATE_DATA_QUALITY_EXPECTATIONS 시스템 함수를 호출해 올바르게 추가되었는지 확인하고 이러한 기대값이 현재 위반되었는지 판단할 수 있어요.

예를 들어 DMF와 테이블 t1 사이의 연결에 기대값을 하나 이상 추가했다고 가정해요. 이러한 기대값이 현재 위반되었는지 보려면 다음 문을 실행해요.

SELECT *
  FROM TABLE(SYSTEM$EVALUATE_DATA_QUALITY_EXPECTATIONS(
      REF_ENTITY_NAME => 'my_db.sch.t1'));

기대값 삭제

DROP EXPECTATION 절을 사용해 연결에서 기대값을 제거하고 시스템에서 제거해요.

예를 들어 이전에 테이블 t1의 c1 컬럼과 NULL_COUNT DMF 사이의 연결에 기대값 my_exp를 추가했다고 가정해요. 연결과 DMF에서 my_exp를 제거하려면 다음 코드를 실행해요.

ALTER TABLE t1
  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT on (c1)
    DROP EXPECTATION my_exp;

기대값 위반 식별

다음을 사용해 기대값 위반을 식별할 수 있어요.

SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS_RAW — 원시 데이터 품질 결과를 기록하는 전용 이벤트 테이블.
DATA_QUALITY_MONITORING_EXPECTATION_STATUS 뷰 — SNOWFLAKE.LOCAL 스키마의 평면화된 결과를 포함하는 뷰.
DATA_QUALITY_MONITORING_EXPECTATION_STATUS 함수 — 기대값 결과를 반환하는 테이블 함수.

SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS_RAW

데이터 품질 결과는 전용 이벤트 테이블 SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS_RAW에 기록돼요.

객체와 DMF 사이의 연결에 기대값이 있으면 Snowflake가 DMF의 결과를 계산할 때마다 테이블에 두 행이 추가돼요. 첫 번째 행은 DMF가 연결된 객체, DMF 자체, 데이터 품질 검사 결과에 대한 정보를 기록해요. 두 번째 행은 DMF 연결에 설정된 기대값과 관련된 정보(기대값이 충족되었는지 위반되었는지 포함)를 기록해요. 기대값이 여러 개 있으면 각 기대값에 대한 행이 있어요.

resource_attribute 열의 snow.data_metric.record_type 필드는 행이 기대값에 해당하는지 여부를 나타내요. 이 필드에는 두 가지 가능한 값이 있어요.

EXPECTATION_VIOLATION_STATUS — 행이 기대값에 해당함을 나타내요.
EVALUATION_RESULT — 행이 DMF의 평가에 해당함을 나타내요.

행이 기대값에 해당하면 resource_attribute 열에도 기대값과 관련된 다음 필드가 포함돼요.

snow.data_metric.expectation_id — 시스템 생성 식별자.
snow.data_metric.expectation_name — 기대값이 연결에 추가된 때의 이름.
snow.data_metric.expectation_expression — 기대값의 표현식.

행이 기대값의 평가에 해당한다고 확인한 후 value 열을 확인해 기대값이 위반되었는지 판단할 수 있어요. TRUE이면 기대값이 위반된 거예요.

DATA_QUALITY_MONITORING_EXPECTATION_STATUS 뷰

SNOWFLAKE.LOCAL 스키마에 있는 DATA_QUALITY_MONITORING_EXPECTATION_STATUS 뷰는 이벤트 테이블의 정보를 평면화해 DMF 결과에 더 쉽게 접근하게 해줘요.

DATA_QUALITY_MONITORING_EXPECTATION_STATUS 함수

DATA_QUALITY_MONITORING_EXPECTATION_STATUS 테이블 함수는 DATA_QUALITY_MONITORING_EXPECTATION_STATUS 뷰에서 사용할 수 있는 것과 같은 정보를 제공하는 행을 반환해요. 이 함수는 뷰와 다른 액세스 제어 모델을 사용해요.

기대값 사용 추적

Snowflake는 계정의 모든 기대값을 추적해요. 함수를 실행하거나 ACCOUNT_USAGE 뷰를 조회해 기대값 사용을 모니터링할 수 있어요. 여기에는 다음 작업이 포함돼요.

DMF와의 연결에 기대값이 정의된 객체가 무엇인지 모니터링.
객체와의 연결에 기대값이 정의된 DMF가 무엇인지 모니터링.
객체와 DMF 사이의 특정 연결에 기대값이 정의되어 있는지 발견.
데이터 품질 검사를 더 잘 이해하기 위해 기대값의 부울 표현식을 결정.

함수를 실행해 기대값 추적

DATA_METRIC_FUNCTION_EXPECTATIONS 함수를 실행해 특정 객체, 특정 DMF 또는 객체와 DMF 사이의 연결에 대해 정의된 기대값을 출력할 수 있어요.

예제: 특정 객체에 존재하는 기대값

SELECT *
  FROM TABLE(
    INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_EXPECTATIONS(
      REF_ENTITY_NAME => 'my_table',
      REF_ENTITY_DOMAIN => 'table'));

예제: 특정 DMF에 존재하는 기대값

SELECT *
  FROM TABLE(
    INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_EXPECTATIONS(
      METRIC_NAME => 'SNOWFLAKE.CORE.NULL_COUNT'));

예제: 객체와 DMF 사이의 특정 연결에 존재하는 기대값

SELECT *
  FROM TABLE(
    INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_EXPECTATIONS(
      METRIC_NAME => 'SNOWFLAKE.CORE.NULL_COUNT',
      REF_ENTITY_NAME => 'my_table',
      REF_ENTITY_DOMAIN => 'table'));

뷰를 조회해 기대값 추적

ACCOUNT_USAGE 스키마의 DATA_METRIC_FUNCTION_EXPECTATIONS 뷰는 계정의 모든 기대값을 포함해요. 뷰를 조회해 계정 내 기대값 사용을 추적하고 각 기대값의 부울 표현식을 결정할 수 있어요.

예제: Snowflake 계정의 모든 기대값 반환

SELECT * FROM snowflake.account_usage.data_metric_function_expectations
  ORDER BY expectation_name;

예제: 특정 데이터 메트릭 함수에 대한 기대값 식별

SELECT expectation_name,
    ref_database_name as object_database,
    ref_schema_name as object_schema,
    ref_entity_name as object_name
  FROM snowflake.account_usage.data_metric_function_expectations
  WHERE
    metric_database_name = 'SNOWFLAKE' AND
    metric_schema_name = 'CORE' AND
    metric_name = 'ROW_COUNT'
  ORDER BY expectation_name;

SQL로 기대값 사용