Permanent, transient, temp, external, Iceberg, dynamic

USE ROLE ROLE_CORE_LAB_ADMIN;
USE WAREHOUSE WH_CORE_XS;
USE DATABASE ALPINE_RETAIL;
USE SCHEMA LAB;

CREATE OR REPLACE TABLE LAB.T_PERM (
  ID NUMBER, LABEL STRING
);
CREATE OR REPLACE TRANSIENT TABLE LAB.T_TRANS (
  ID NUMBER, LABEL STRING
);
CREATE OR REPLACE TEMPORARY TABLE LAB.T_TEMP (
  ID NUMBER, LABEL STRING
);

INSERT INTO LAB.T_PERM VALUES (1, 'permanent');
INSERT INTO LAB.T_TRANS VALUES (1, 'transient');
INSERT INTO LAB.T_TEMP VALUES (1, 'temporary');

SELECT 'PERM' AS kind, * FROM LAB.T_PERM
UNION ALL SELECT 'TRANS', * FROM LAB.T_TRANS
UNION ALL SELECT 'TEMP', * FROM LAB.T_TEMP;

-- Needs files on @LAB.STG_INBOUND first:
-- CREATE OR REPLACE EXTERNAL TABLE LAB.EXT_ORDERS (
--   ORDER_ID NUMBER AS (VALUE:c1::NUMBER),
--   AMOUNT NUMBER AS (VALUE:c2::NUMBER)
-- )
-- LOCATION = @LAB.STG_INBOUND/orders/
-- FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1)
-- AUTO_REFRESH = FALSE;

-- Needs EXTERNAL VOLUME + catalog setup:
-- CREATE ICEBERG TABLE LAB.IB_ORDERS (
--   ORDER_ID NUMBER, AMOUNT NUMBER(10,2)
-- )
-- CATALOG = 'SNOWFLAKE'
-- EXTERNAL_VOLUME = 'my_ext_vol'
-- BASE_LOCATION = 'orders/';

CREATE OR REPLACE TABLE LAB.DT_SOURCE (
  EVENT_ID NUMBER,
  EVENT_TS TIMESTAMP_NTZ,
  PAYLOAD STRING
);
INSERT INTO LAB.DT_SOURCE VALUES
  (1, CURRENT_TIMESTAMP(), 'click'),
  (2, CURRENT_TIMESTAMP(), 'purchase');
SELECT * FROM LAB.DT_SOURCE;

-- CREATE OR REPLACE DYNAMIC TABLE LAB.DT_EVENTS
--   TARGET_LAG = '1 minute'
--   WAREHOUSE = WH_CORE_XS
-- AS
--   SELECT EVENT_ID, EVENT_TS, PAYLOAD
--   FROM LAB.DT_SOURCE;