Base table

To create the table, we recommend the following structure:

CREATE TABLE IF NOT EXISTS SUBSCRIPTIONS (
    SUBSCRIPTION_ID      STRING         NOT NULL,
    USER_ID              STRING         NOT NULL,
    PRODUCT_ID           STRING         NOT NULL,
    TRIAL_START_DATE     TIMESTAMP_TZ,
    TRIAL_END_DATE       TIMESTAMP_TZ,
    LOCK_START_DATE      TIMESTAMP_TZ,
    LOCK_END_DATE        TIMESTAMP_TZ,
    START_DATE           TIMESTAMP_TZ,
    STOP_DATE            TIMESTAMP_TZ,
    EXPIRY_DATE          TIMESTAMP_TZ,
    NEXT_BILLING_DATE    TIMESTAMP_TZ,
    STATE                STRING,
    IS_GRANTING_ACCESS   BOOLEAN,
    PARTNER_NAME         STRING,
    CONSTRAINT PKEY PRIMARY KEY (SUBSCRIPTION_ID),
    CONSTRAINT 
    CONSTRAINT FKEY_USER_ID FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID) NOT ENFORCED
);

Importing data

This creates a stored procedure that atomically imports the data from the subscriptions snapshot.

CREATE OR REPLACE PROCEDURE import_subscriptions()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    -- Start a transaction
    BEGIN TRANSACTION;

    -- Clear existing data
    TRUNCATE TABLE SUBSCRIPTIONS;

    -- Import data using column name matching
    COPY INTO SUBSCRIPTIONS
    FROM @BEAT_STAGE/snapshots/subscriptions.csv.zst
    FILE_FORMAT = (FORMAT_NAME = 'BEAT_CSV_FORMAT')
    MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
    ON_ERROR = ABORT_STATEMENT;

    -- Commit if successful
    COMMIT;

    RETURN 'Subscriptions imported successfully.';
END;
$$;

To import data once after you’ve created the stored procedure, you can do this:

CALL import_shops();

See the separate section for information on how to schedule nightly imports.