[SQLD 18화] DDL 테이블 생성(CREATE TABLE)과 분석 생산성을 높이는 Volatile 임시 테이블 활용법 (SQLD, 직장인 SQL)

작성자:

1. 나만의 데이터 분석 그릇 만들기: DDL의 시작

17화에서 우리는 별도의 복잡한 서브쿼리나 무거운 셀프 조인 구조를 완전히 걷어내고, 현재 행 옆으로 전월 데이터(LAG)나 미래 데이터(LEAD)를 즉시 소환하여 전월 대비 증감률(MoM) 및 트렌드 분석 지표를 계산해 내는 전후 행 참조 함수의 필수 문법을 살펴보았습니다.

지금까지 1화부터 17화에서는 기존 테이블의 데이터를 조회하고 가공하는 조회(SELECT) 중심의 SQL(DQL)을 살펴보았습니다.

그러나 현업 분석가가 수억 건에 달하는 원천 데이터를 다루다 보면, 단순히 남이 만들어 놓은 테이블을 조회하는 것만으로는 분석의 한계에 봉착하기 쉽습니다. “중간 가공 단계의 결과물을 어딘가에 임시로 깔끔하게 보관해 두고 반복해서 조인하고 싶다”거나 “팀 단위 분석 프로모션을 위해 새로운 규격의 분석용 데이터 마트를 직접 설계하고 싶다”는 요구사항이 필연적으로 발생하기 때문입니다.

이처럼 데이터를 담아둘 그릇인 테이블(Table)이나 뷰(View) 등의 데이터베이스 구조 자체를 새롭게 정의하고, 변경하고, 삭제하는 명령어를 DDL (Data Definition Language, 데이터 정의어)이라고 부르며, 그 대표적인 시작점이 바로 CREATE와 ALTER입니다. 본 연작 시리즈는 대용량 처리에 널리 활용되는 테라데이터 SQL 환경을 기준으로 DDL 문법 규칙과 실무 활용 전략을 설명하겠습니다.

2. CREATE TABLE과 ALTER TABLE의 기본 문법 구조

테이블을 생성하는 CREATE TABLE 문법은 저장하고자 하는 칼럼의 이름과 해당 칼럼에 유입될 데이터의 유형(Data Type)을 명확히 선언해 주는 구조를 취합니다.

-- 테이블 생성을 위한 가장 표준적인 CREATE TABLE 문법 구조
CREATE TABLE DW_MART.MY_ANALYSIS_MART (
    CUST_NO            VARCHAR(20)  NOT NULL  -- 회원번호 (문자형, 빈값 비허용)
  , BASE_YM            CHAR(6)      NOT NULL  -- 분석기준년월 (고정문자형)
  , TOTAL_PURCHASE_AMT DECIMAL(18,2)          -- 총구매금액 (소수점 포함 숫자형)
  , REG_DT             DATE                   -- 등록일자 (날짜형)
)
PRIMARY INDEX (CUST_NO); -- 데이터 분산 기준이 되는 Primary Index

이미 생성된 테이블의 구조를 사후에 변경하고 싶을 때는 데이터 정의어인 ALTER TABLE을 사용합니다. 실무에서는 분석용 데이터 마트에 새로운 분류 칼럼을 추가해야 할 때 요긴하게 쓰이는 대안입니다.

-- 기존 생성된 테이블에 새로운 칼럼을 추가하는 ALTER 문법 예시
ALTER TABLE DW_MART.MY_ANALYSIS_MART 
ADD CUST_GRADE_CD CHAR(2); -- 고객 등급 코드 칼럼(2글자형) 추가 선언

⚠️ DDL 구문 작성 시 절대로 누락하면 안 되는 2가지 문법 체크포인트

  • 데이터 타입(Data Type) 선언의 정확성: 칼럼 이름 바로 옆에 배치되는 데이터 형식(예: VARCHAR, DECIMAL, DATE 등)이 올바르지 않으면 문법 에러가 발생합니다. 정밀한 사칙연산이 필요한 금액형 칼럼에는 소수점 자릿수를 유연하게 제어하는 DECIMAL을, 날짜 필터링이 잦은 칼럼에는 DATE 형식을 매칭하는 습관이 중요합니다.
  • 스키마 소유권 및 쓰기 권한 확보: 아무리 완벽한 문법의 CREATE TABLE 구문을 실행하더라도, 접속한 데이터베이스 세션에 해당 스키마 공간(예: DW_MART 등)의 테이블 생성 권한(Create Privilege)이 부여되어 있지 않다면 데이터베이스 시스템은 즉시 권한 부족 에러(Authorization Error)를 발생시키며 테이블 생성을 차단합니다.

3. 현업 비즈니스 사례로 보는 권한의 장벽과 Volatile Table의 등장

현업 실무에서 기업의 데이터베이스 서버는 대단히 엄격하게 통제 및 보안 관리됩니다. 일반적인 비즈니스 분석가나 마케팅 담당자들은 원천 데이터베이스에 대한 읽기 권한(SELECT Privilege)만 보유할 뿐, 데이터 구조를 변경할 수 있는 쓰기 및 생성 권한(DDL 권한)은 IT 시스템 정보보안 부서에 의해 원천적으로 차단되어 있는 경우가 다반사입니다.

이러한 상황 속에서 복잡한 중간 집계 연산을 단 한 번에 끝마치고 임시로 데이터를 적재해 두고 싶을 때, 현직 분석가들이 IT 부서에 정식 권한 신청을 하는 오버헤드 없이 독자적으로 분석 그릇을 만들어내는 최고의 치트키가 바로 Volatile Table (휘발성 임시 테이블)입니다. 이해를 돕기 위해 실무 분석 환경에서 자주 발생하는 가상의 시나리오를 통해 짚어보겠습니다.

📊 [실무 사례 예시: 마케팅 기획 팀 최 대리의 1회성 타겟 세그먼트 결합 연산]

카드사 마케팅 담당자인 최 대리는 이번 주말 전사 VIP 타겟 프로모션을 앞두고 복잡한 연산을 설계 중입니다.

  • 요청 내용: “최근 3개월간 우수 고객 조건에 부합하는 대상자 5,000명의 중간 요약 정보 집합을 먼저 추출한 뒤, 이 집합을 다른 대용량 마스터 테이블 3개와 지속적으로 교차 조인(Join)하여 시뮬레이션을 수행하고 최종 리포트를 산출해 주세요.”

이 작업을 한 번에 묶어서 복잡한 서브쿼리로만 수행하려고 하자, 쿼리가 너무 길어지고 속도 또한 눈에 띄게 느려졌습니다. 그렇다고 영구적인 분석 테이블(CREATE TABLE)을 만드려고 하니 보안 정책상 스키마 생성 권한이 막혀 있는 난감한 상황이었습니다.

최 대리는 이 제한적인 업무 인프라 장벽을 우회하고 연산의 효율성과 가독성을 동시에 높이기 위해, 테라데이터 환경에서 가장 강력한 임시 가공 도구로 평가받는 CREATE VOLATILE TABLE 문법을 구사하여 개인 가상 분석판을 생성했습니다.

-- [올바른 예시] Volatile Table을 활용해 즉시 사용 가능한 가상 작업판을 구성한 쿼리
CREATE VOLATILE TABLE TEMP_VIP_SUMMARY AS (
    -- [1] 복잡한 중간 집계 쿼리를 인라인데이터 형태로 선제 가공하여 적재
    SELECT 
        CUST_NO
      , SUM(PAY_AMT) AS TOTAL_PAY_AMT
      , COUNT(ORDER_NO) AS TOTAL_ORD_CNT
    FROM 
        DW_MART.DAILY_ORDER_LOG
    WHERE 
        ORDER_DT BETWEEN '2026-04-01' AND '2026-06-30'
    GROUP BY 
        CUST_NO
    HAVING 
        SUM(PAY_AMT) >= 5000000 -- 최근 3개월 실적 500만 원 이상 우수 고객 필터
) WITH DATA -- 실제 데이터의 결과 집합의 데이터까지 함께 생성하는 옵션
ON COMMIT PRESERVE ROWS; -- 데이터베이스 세션이 살아있는 동안에는 임시 데이터를 유지하라는 설정

💡 생성된 Volatile Table의 실무 활용 및 비즈니스적 혜택

위 쿼리를 실행하면 일반적인 Teradata 분석 환경에서는 권한 문제 없이 TEMP_VIP_SUMMARY라는 개인 작업 테이블을 생성할 수 있습니다. 최 대리는 이제 일반 테이블을 쓰듯이 다음과 같이 가볍고 쾌적하게 다중 조인을 수행할 수 있습니다.

-- 가상 임시 테이블을 마스터 테이블과 가볍게 교차 조인하여 최종 시뮬레이션 리포트 도출
SELECT 
    T.CUST_NO
  , T.TOTAL_PAY_AMT
  , M.CUST_NM
  , M.EMAIL_ADDR
FROM 
    TEMP_VIP_SUMMARY T -- 내가 생성한 가상 임시 테이블
INNER JOIN 
    DW_MART.CUSTOMER_MSTR M -- 대용량 회원 마스터 테이블
ON 
    T.CUST_NO = M.CUST_NO;
  • 메모리 및 자원 효율 극대화: 무거운 서브쿼리를 반복 실행하며 대용량 원천 마트를 매번 다중 풀 스캔하던 비효율적인 방식과 달리, 단 한 번 추출된 우수 고객 5,000명의 핵심 정제 데이터셋만 사용자의 임시 작업 공간에 저장한 뒤 가벼운 조인만 수행하므로 시스템의 스풀(Spool) 사용량과 반복 연산 부담을 줄일 수 있습니다.
  • 자동 소멸 휘발성 속성: 이름에 쓰인 ‘Volatile'(휘발성) 단어 그대로, 이 테이블은 사용자가 데이터베이스 조회 툴(DBeaver, SQL Assistant 등)의 접속을 종료하는 순간 테이블과 데이터가 자동으로 삭제됩니다. 따라서 지저분하게 데이터베이스 공간 내에 쓰레기 데이터 파편을 남기지 않아 데이터베이스 공간을 불필요하게 점유하지 않습니다.

4. 팩트 체크 및 데이터 분석가의 관점 점검 (WITH DATA와 WITH NO DATA)

기술 콘텐츠의 정밀성과 신뢰성을 확보하기 위해, 실무에서 DDL 임시 테이블을 생성할 때 분석가들이 가장 많이 오해하거나 실수하는 문법 관계들을 명확히 점검해 보겠습니다.

  • CREATE TABLE AS 구문 끝에 WITH DATA를 쓰지 않으면 어떻게 되는가?
    • 팩트 체크: 결과 집합 내부의 실제 알맹이 데이터는 다 버리고, 오직 ‘테이블의 껍데기(칼럼 구조와 이름 및 데이터 타입)’만 복사한 채 텅 빈 테이블이 생성되는 문법적 특성이 있습니다. 만약 데이터를 그대로 적재하여 즉시 분석판으로 활용하고 싶다면 반드시 구문 마지막에 WITH DATA를 기재해 주어야 합니다. 반면 기존의 구조만 본떠서 빈 임시 마트를 만들고 추후 데이터를 따로 INSERT하고 싶다면 WITH NO DATA 옵션을 선택하는 것이 현명한 대안으로 권장됩니다.
  • ON COMMIT PRESERVE ROWS 옵션을 누락하면 어떻게 되는가?
    • 팩트 체크: 테라데이터 문법 규칙상 이 옵션을 누락한 채 테이블을 생성하면, COMMIT 이후 데이터가 삭제될 수 있습니다. 테이블의 구조만 남아있고 알 수 없는 이유로 데이터가 계속 증발하여 분석을 방해하는 원인이 바로 이 구문 누락에서 발생하곤 합니다. 따라서 세션이 종료되기 전까지 나만의 개인 데이터를 깨끗하게 지켜내고 싶다면 반드시 테이블 선언문 끝에 ON COMMIT PRESERVE ROWS 옵션을 명시적으로 결합해야 함을 명심해야 합니다.

5. 18화 요약 및 실무 DDL 임시 테이블 생성 자가 진단 체크리스트

쿼리를 최종 빌드하여 임시 데이터 파이프라인을 구축하기 전, 데이터 소실의 결점이 없는지 스스로 진단할 수 있는 요약 가이드를 제공합니다.

체크포인트발생 가능한 실무 리스크현직 분석가의 권장 대안 및 조치
AS 구문 끝에 WITH DATA 명시 여부알맹이 데이터가 전부 증발한 채 텅 빈 껍데기 테이블만 생성되는 문법 오류 발생데이터까지 함께 생성하려면 WITH DATA를 사용
ON COMMIT PRESERVE ROWS 누락 여부트랜잭션 실행 완료(Commit)와 동시에 내부 행 데이터가 원천 증발함세션이 유지되는 동안 데이터를 방어하기 위해 테이블 선언 끝에 필수 명시
세션 종료 시의 데이터 보존 여부휘발성 테이블의 특성을 잊고 DB 창을 닫아 공들인 가공 테이블 소실접속(Session)을 끊으면 테이블 자체가 영구 소멸되므로 1회성 마트 위주로 활용

6. 결론 및 다음 화 예고

이번 18화에서는 원천 데이터베이스에 대한 DDL 쓰기 권한이 보안상 철저하게 통제되는 업무 인프라 상황 속에서, 아무런 추가 권한 승인 절차 없이도 단 몇 줄의 쿼리만으로 나만의 독립된 1회성 가상 분석 마트를 생성하고 대용량 조인의 효율성을 비약적으로 끌어올리는 Volatile Table (휘발성 임시 테이블) 생성 DDL의 필수 문법과 실무 적용 사례를 다각도로 점검해 보았습니다. 이러한 나만의 가상 작업판을 영리하게 설계해 내는 역량은 복잡하게 얽힌 분석 단계를 체계적으로 통제하고 쿼리 생산성을 극적으로 개선해 주는 강력한 밑거름이 됩니다.

자유자재로 임시 작업 그릇을 만들고 다듬을 줄 아는 분석가가 다음 단계로 확장하게 되는 과제는, 한 번 만든 임시 그릇을 더 이상 쓸모없어졌을 때 시스템 자원을 남기지 않고 가장 효율적으로 처분하여 저장 공간을 효율적으로 관리하는 일입니다.

이어지는 19화에서는 DDL의 종착점이자 데이터 저장 공간 회수의 사령관으로 불리는 DROP과 TRUNCATE 명령어의 기능적 차이점을 집중 분석합니다. 테이블 자체를 완전히 파쇄해 버리는 DROP과 구조는 유지하되 데이터 용량만 깔끔하게 비워 저장 공간을 효율적으로 확보해 주는 TRUNCATE의 작동 메커니즘을 살펴보고, 대용량 데이터를 정제하는 실무 과정에서 자원 낭비를 방지하는 현직 분석가들의 데이터 처분 치트키를 상세히 파악해 보겠습니다.

댓글 남기기