SQL로 데이터 메트릭 함수 설정하기

이 주제는 SQL로 데이터 메트릭 함수(DMF, Data Metric Function)를 테이블이나 뷰에 연결해서 정기적으로 실행되게 하는 방법을 설명해요. 또한 테이블이나 뷰에 연결하기 전에 DMF를 테스트하고 싶을 때처럼 DMF를 직접 호출하는 방법도 다뤄요.

출처: Snowflake User Guide - Data metric functions

본문

DMF 연결하기

DMF를 테이블이나 뷰에 연결하면 정기적으로 자동 호출돼요. 연결할 때 DMF에 인자로 전달할 컬럼을 지정해요.

ALTER TABLE 또는 ALTER VIEW 명령으로 DMF를 연결하고 인자로 전달할 컬럼을 지정해요. 예를 들어 다음 명령은 NULL_COUNT 시스템 DMF를 테이블 t에 연결해요. DMF가 실행되면 컬럼 c1의 NULL 값 개수를 반환해요.

ALTER TABLE t
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (c1);

어떤 DMF는 컬럼을 인자로 받지 않아요. 예를 들어 ROW_COUNT 시스템 DMF를 뷰 v2에 연결하려면:

ALTER VIEW v2
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.ROW_COUNT ON ();

ACCEPTED_VALUES DMF는 컬럼 이름과 람다 표현식을 포함해, 기대한 값과 일치하지 않는 레코드가 몇 개인지 확인할 수 있게 해 줘요. 예를 들어 다음 문은 함수를 테이블 t1에 연결해 age 컬럼 값이 5와 같지 않은 레코드 수를 반환하게 해요.

ALTER TABLE t1
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.ACCEPTED_VALUES
    ON (age, age -> age = 5);

결과를 차원별로 나누고 싶다면(예: 지역별 NULL 개수), 연결을 만들 때 WITHIN GROUP 절을 포함해요. 자세한 내용은 그룹별 데이터 품질 체크 적용을 참고해요.

생성 시점에 DMF 붙이기

CREATE 문에서 바로 DMF를 테이블이나 뷰에 붙이면, 별도의 ALTER 단계 없이 객체 생성과 동시에 데이터 품질 모니터링이 시작돼요.

CREATE 문에서 WITH DATA METRIC FUNCTION 절을 사용해요. 테이블과 이벤트 테이블에서는 컬럼 정의 뒤에 오고, 뷰·구체화된 뷰·동적 테이블에서는 AS SELECT 정의 앞에 와요.

-- 테이블
CREATE [ OR REPLACE ] TABLE <name> ( <column_definitions> )
  WITH DATA METRIC FUNCTION (
    <dmf_name> ON ( <col_name> [ , <col_name> ... ] )
    [ , <dmf_name> ON ( <col_name> [ , <col_name> ... ] ) ... ]
  );

-- 뷰
CREATE [ OR REPLACE ] VIEW <name>
  WITH DATA METRIC FUNCTION (
    <dmf_name> ON ( <col_name> [ , <col_name> ... ] )
    [ , <dmf_name> ON ( <col_name> [ , <col_name> ... ] ) ... ]
  )
AS SELECT ...;

참고: Snowflake는 괄호를 감싸지 않은 바인딩 목록 형태도 받아들이지만 이는 더 이상 권장되지 않으며, 향후 동작 변경 릴리스에서 제거될 예정이에요. 새 문에는 괄호 형태를 사용하고 기존 문도 그에 맞게 수정해 주세요.

예를 들어 다음 문은 테이블을 만들고 email 컬럼에 NULL_COUNT 시스템 DMF를 붙이면서 NULL 값이 없어야 한다는 기대(expectation)를 설정해요.

CREATE OR REPLACE TABLE customers (
  customer_id NUMBER,
  email       VARCHAR
)
WITH DATA METRIC FUNCTION (
  SNOWFLAKE.CORE.NULL_COUNT ON (email)
    EXPECTATION no_null_email ( VALUE = 0 )
);

같은 문에서 여러 DMF를 붙이려면 괄호 안에서 바인딩을 쉼표로 구분해요. 각 바인딩은 <dmf_name> ON (...) 형태로 작성하고 WITH DATA METRIC FUNCTION 키워드는 반복하지 않아요.

CREATE OR REPLACE TABLE orders (
  order_id    NUMBER,
  customer_id NUMBER
)
WITH DATA METRIC FUNCTION (
  SNOWFLAKE.CORE.NULL_COUNT ON (customer_id)
    EXPECTATION no_null_customers ( VALUE = 0 ),
  SNOWFLAKE.CORE.DUPLICATE_COUNT ON (order_id)
    EXPECTATION no_duplicate_orders ( VALUE = 0 )
);

이 절은 뷰, 구체화된 뷰, 동적 테이블, 외부 테이블, 이벤트 테이블에서도 지원돼요. AS SELECT 정의가 있는 객체(뷰·구체화된 뷰·동적 테이블)에서는 WITH DATA METRIC FUNCTION 절이 반드시 AS SELECT 앞에 와야 해요.

CREATE OR REPLACE VIEW active_users
WITH DATA METRIC FUNCTION (
  SNOWFLAKE.CORE.BLANK_COUNT ON (email)
    EXPECTATION no_blank_emails ( VALUE = 0 )
)
AS SELECT user_id, email FROM users WHERE active = TRUE;

동작 및 의미:

원자성(Atomicity): 모든 DMF 바인딩이 원자적으로 붙어요. 어떤 바인딩이 유효하지 않으면 전체 CREATE가 실패하고 부분 상태가 남지 않아요.
CREATE OR REPLACE: 이전 객체와 모든 DMF 바인딩을 처음부터 다시 교체해요.
CREATE IF NOT EXISTS: 객체가 이미 있으면 아무 일도 하지 않으며, 기존 DMF 바인딩은 그대로 유지돼요.
CLONE: 복제된 객체가 소스로부터 DMF 바인딩을 상속해요.
LIKE: 새 객체가 소스 테이블로부터 DMF 바인딩을 상속해요.
기대(Expectations): 기대 이름은 단일 DMF 바인딩 안에서 유일해야 해요. 비교의 왼쪽은 반드시 키워드 VALUE여야 해요. 허용되는 연산자는 =, !=, <>, <, >, <=, >=, AND, OR, NOT, EQUAL_NULL이에요. 따옴표로 감싼 문자열('VALUE' = 0), VALUE에 대한 산술, 서브쿼리, 문자열 캐스트는 허용되지 않아요.

각 DMF 바인딩에서 지원되는 속성(EXECUTE AS ROLE, ANOMALY_DETECTION, WITHIN GROUP, GROUP LIMIT, SENSITIVITY, DATA_QUALITY_NOTIFICATION, EXPECTATION)의 전체 레퍼런스는 데이터 메트릭 함수 동작 문서를 참고해요.

객체에서 DMF 제거하기

ALTER TABLE 또는 ALTER VIEW 명령으로 DMF를 제거할 수 있어요. 예:

ALTER TABLE t
  DROP DATA METRIC FUNCTION governance.dmfs.count_positive_numbers ON (c1, c2, c3);

DMF 스케줄 조정하기

테이블, 뷰, 구체화된 뷰의 DATA_METRIC_SCHEDULE 객체 파라미터가 DMF가 실행되는 빈도를 제어해요. 기본적으로 스케줄은 1시간이에요. 테이블이나 뷰의 모든 데이터 메트릭 함수는 같은 스케줄을 따라요.

DMF 실행을 스케줄하는 방법:

지정된 분(minute) 후에 실행하도록 설정
cron 표현식으로 특정 빈도로 실행
트리거 이벤트로 테이블에 DML 변경(예: 새 행 삽입)이 있을 때 실행. 단, 테이블의 리클러스터링(reclustering)은 DMF 실행을 트리거하지 않고, 트리거 방식은 특정 종류의 테이블에서만 사용 가능해요.

예시:

-- 5분마다 실행
ALTER TABLE hr.tables.empl_info SET DATA_METRIC_SCHEDULE = '5 MINUTE';

-- 매일 오전 8시 실행
ALTER TABLE hr.tables.empl_info SET DATA_METRIC_SCHEDULE = 'USING CRON 0 8 * * * UTC';

-- 평일만 오전 8시에 실행
ALTER TABLE hr.tables.empl_info SET DATA_METRIC_SCHEDULE = 'USING CRON 0 8 * * MON,TUE,WED,THU,FRI UTC';

-- 하루 3번 0600/1200/1800 UTC에 실행
ALTER TABLE hr.tables.empl_info SET DATA_METRIC_SCHEDULE = 'USING CRON 0 6,12,18 * * * UTC';

-- 테이블을 수정하는 일반 DML 연산(예: 새 행 삽입)이 있을 때 실행
ALTER TABLE hr.tables.empl_info SET DATA_METRIC_SCHEDULE = 'TRIGGER_ON_CHANGES';

SHOW PARAMETERS 명령으로 지원되는 테이블 객체의 DMF 스케줄을 확인할 수 있어요.

SHOW PARAMETERS LIKE 'DATA_METRIC_SCHEDULE' IN TABLE hr.tables.empl_info;

+----------------------+--------------------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------+
| key                  | value                          | default | level | description                                                                                                                  | type   |
+----------------------+--------------------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------+
| DATA_METRIC_SCHEDULE | USING CRON 0 6,12,18 * * * UTC |         | TABLE | Specify the schedule that data metric functions associated to the table must be executed in order to be used for evaluation. | STRING |
+----------------------+--------------------------------+---------+-------+------------------------------------------------------------------------------------------------------------------------------+--------+

뷰와 구체화된 뷰 객체의 경우 TABLE을 객체 도메인으로 지정하고 스케줄을 확인해요.

SHOW PARAMETERS LIKE 'DATA_METRIC_SCHEDULE' IN TABLE mydb.public.my_view;

참고: 테이블에서 DMF를 수정하면, 이전에 테이블에 지정된 DMF에는 스케줄 변경이 반영되는 데 10분의 지연이 있어요. 다만 테이블에 새로 지정한 DMF에는 10분 지연이 적용되지 않아요. DMF 스케줄링과 DMF 해제 연산을 예상 DMF 비용에 맞춰 계획하세요. 또한 DATA_QUALITY_MONITORING_RESULTS 뷰를 쿼리하는 등 DMF 결과를 평가할 때는 쿼리에서 measurement_time 컬럼을 평가 기준으로 지정해요. DMF 평가를 시작하는 내부 프로세스가 있어서, 스케줄된 시각과 측정 시각 사이에 INSERT 같은 테이블 갱신이 일어날 수 있어요. measurement_time 컬럼을 사용하면 측정 시각이 DMF의 평가 시각을 나타내므로 더 정확한 평가가 가능해요.

DMF 일시 중지하기

테이블과 연결된 상태여도 DMF가 실행되지 않도록 일시 중지할 수 있어요. 또는 한 문으로 테이블과 연결된 모든 DMF를 일시 중지할 수도 있어요.

테이블과 연결된 특정 DMF를 일시 중지하려면 연결을 수정해 SUSPEND 파라미터를 설정해요. 예:
```
ALTER TABLE t1
  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (col1)
  SUSPEND;
```
DMF 실행을 재개하려면 또 다른 MODIFY DATA METRIC FUNCTION 문으로 RESUME 파라미터를 설정해요.
테이블과 연결된 모든 DMF를 일시 중지하려면 테이블의 스케줄을 빈 문자열로 설정해요. 예:
```
ALTER TABLE t1 SET DATA_METRIC_SCHEDULE = '';
```
DMF를 재개하려면 DATA_METRIC_SCHEDULE 파라미터를 유효한 값으로 설정해요.

DMF 수동 호출하기

DMF를 직접 호출하는 것은 테이블이나 뷰에 연결하기 전에 DMF 출력을 테스트할 때 유용해요. DMF를 호출하는 문법은 다음과 같아요.

SELECT <data_metric_function>( <query> )

data_metric_function — 시스템 또는 사용자 정의 DMF를 지정해요.
query — 테이블이나 뷰에 대한 SQL 쿼리를 지정해요. 쿼리가 투영하는 컬럼은 DMF 시그니처의 컬럼 인자와 일치해야 해요.

참고: 다음 시스템 DMF는 인자를 받지 않기 때문에 이 문법을 따르지 않아요.

DATA_METRIC_SCHEDULED_TIME

ROW_COUNT

예를 들어 세 개의 컬럼을 인자로 받는 커스텀 DMF count_positive_numbers를 호출하려면:

SELECT governance.dmfs.count_positive_numbers (
  SELECT c1, c2, c3 FROM t
);

ssn 컬럼의 NULL 값 개수를 확인하기 위해 NULL_COUNT 시스템 DMF를 호출하려면:

SELECT SNOWFLAKE.CORE.NULL_COUNT (
  SELECT ssn FROM hr.tables.empl_info
);

커스텀 DMF가 여러 테이블에서 인자를 받는다면, 컬럼을 투영하는 각 쿼리를 괄호로 감싸야 해요. 예를 들어 REFERENTIAL_CHECK DMF를 수동으로 호출하려면:

SELECT referential_check (
  (SELECT id FROM salesorders),
  (SELECT id FROM salespeople)
);

SQL로 데이터 메트릭 함수 설정하기