REFERENTIAL_INTEGRITY_COUNT

REFERENTIAL_INTEGRITY_COUNT (시스템 데이터 메트릭 함수)

REFERENTIAL_INTEGRITY_COUNT()는 소스 테이블에서 열 값이 참조 테이블에 대응하는 값이 없는 행의 개수를 반환해요. 이러한 일치하지 않는 행을 고아 행(orphaned rows) 이라고 하며 참조 무결성 위반을 나타내요.

열 인자를 두 개 이상 지정하면 지정한 소스 열들의 조합이 해당 참조 열들에 기반해 참조 테이블의 어떤 행과도 일치하지 않는 행의 개수를 반환해요.

이 함수는 Enterprise Edition 기능이에요. 데이터 품질 모니터링(Data Quality Monitoring)에는 Enterprise Edition이 필요해요. 업그레이드에 대해 문의하려면 Snowflake 지원에 연락하세요.

출처: Snowflake SQL Reference

본문

문법

SNOWFLAKE.CORE.REFERENTIAL_INTEGRITY_COUNT ON ( <column>, TABLE(<ref_table>(<ref_column>)) )

복합(다중 열) 키의 경우:

SNOWFLAKE.CORE.REFERENTIAL_INTEGRITY_COUNT ON (
  <column1>, <column2>, ...,
  TABLE(<ref_table>(<ref_column1>, <ref_column2>, ...))
)

인자

column

참조 테이블에 대해 참조 무결성이 검사되는 소스 테이블의 하나 이상의 열을 지정해요. 여러 소스 열을 지정하면 복합 키를 형성해요.

TABLE(ref_table(ref_column))

TABLE(...) 문법을 사용해 검증할 참조(부모) 테이블과 열을 지정해요. ref_table — 참조 테이블의 완전한 이름(fully qualified name)(예: my_db.my_schema.my_table). ref_column — 소스 열에 대응하는 참조 테이블의 하나 이상의 열. 소스 열의 개수와 순서는 참조 열의 개수와 순서와 일치해야 해요.

허용 데이터 타입

*column*과 *ref_column* 인자에 지정된 열은 다음 데이터 타입 중 아무거나 포함할 수 있어요.

DATE
FLOAT
NUMBER
TIMESTAMP_LTZ
TIMESTAMP_NTZ
TIMESTAMP_TZ
VARCHAR

반환

이 함수는 NUMBER 값을 반환해요.

반환 값 0은 소스 테이블의 모든 행이 참조 테이블에 대응하는 값이 있음을 의미해요. 참조 무결성이 완전히 충족된 상태예요.
반환 값 N > 0은 소스 테이블에 참조 테이블의 일치하는 행이 없는 행이 N개 있음을 의미해요. 이 N개의 행은 고아 행으로 간주돼요.

사용 지침

이 함수는 직접 호출할 수 없어요. 함수를 테이블이나 뷰와 연결해 주기적으로 실행하는 방법은 DMF 연결(Associate a DMF)을 참고하세요. SYSTEM$DATA_METRIC_SCAN 함수를 사용해 연결 없이 테이블에 대해 REFERENTIAL_INTEGRITY_COUNT 함수를 실행할 수 있어요.
소스 열의 NULL 값은 위반으로 계산되지 않아요. 소스 열 값이 NULL인 행은 참조 무결성 검사에서 제외돼요. 이는 NULL 외래 키가 유효한 것으로 간주되는 표준 외래 키(FK) 제약 조건 의미론을 따르는 거예요. 소스 열의 NULL 값을 모니터링해야 하면 이 함수와 함께 NULL_COUNT DMF를 사용하세요.
소스 열의 개수와 순서는 참조 열과 일치해야 해요. 이는 함수를 연결할 때 검증돼요.
REFERENTIAL_INTEGRITY_COUNT 함수에 지정된 열 이름을 바꾸면 함수와 해당 열의 테이블 또는 뷰 사이의 연결이 깨져요. 열 이름을 바꾸면 테이블이나 뷰에 함수를 다시 연결해야 해요.
같은 열과 참조 테이블 조합으로 이 함수를 두 번 이상 연결할 수 없어요.

예제

단일 열 참조 무결성 검사

sp_id가 salespeople 테이블의 sp_id 열에 존재하지 않는 행 수를 반환하도록 함수를 salesorders 테이블에 연결해요.

ALTER TABLE salesorders
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.REFERENTIAL_INTEGRITY_COUNT
    ON (sp_id, TABLE(my_db.sch1.salespeople(sp_id)));

복합 키 참조 무결성 검사

order_id와 product_id의 조합이 order_products 테이블에 일치하는 행이 없는 행 수를 반환하도록 함수를 order_items 테이블에 연결해요.

ALTER TABLE order_items
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.REFERENTIAL_INTEGRITY_COUNT
    ON (order_id, product_id, TABLE(my_db.sch1.order_products(order_id, product_id)));

연결 제거

salesorders 테이블에서 참조 무결성 검사를 제거해요.

ALTER TABLE salesorders
  DROP DATA METRIC FUNCTION SNOWFLAKE.CORE.REFERENTIAL_INTEGRITY_COUNT
    ON (sp_id, TABLE(my_db.sch1.salespeople(sp_id)));

기대값(expectation)과 함께 연결

참조 무결성이 완전히 충족되어야 한다는 기대값(고아 행 0개)을 정의하면서 함수를 연결해요.

ALTER TABLE salesorders
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.REFERENTIAL_INTEGRITY_COUNT
    ON (sp_id, TABLE(my_db.sch1.salespeople(sp_id)))
    EXPECTATION no_orphans (VALUE = 0);

기존 연결에 기대값 추가

DMF가 이미 연결되어 있다면 MODIFY를 사용해 기대값을 추가해요.

ALTER TABLE salesorders
  MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.REFERENTIAL_INTEGRITY_COUNT
    ON (sp_id, TABLE(my_db.sch1.salespeople(sp_id)))
    ADD EXPECTATION no_orphans (VALUE = 0);

REFERENTIAL_INTEGRITY_COUNT