Base table

To create the table, we recommend the following structure:

CREATE OR REPLACE TABLE PRODUCTS (
    PRODUCT_ID                STRING       NOT NULL,
    NAME                      STRING,
    DESCRIPTION               STRING,
    IS_VISIBLE                BOOLEAN,
    CURRENT_PRICE             NUMBER,
    CURRENT_PRICE_CURRENCY    STRING,
    STRIPE_PRICE_ID           NUMBER,
    RENEWAL_INTERVAL_LENGTH   NUMBER,
    RENEWAL_INTERVAL_UNIT     STRING,
    TRIAL_LENGTH              NUMBER,
    TRIAL_UNIT                STRING,
    CONSTRAINT PKEY PRIMARY KEY (PRODUCT_ID)
);

Importing data

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

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

    -- Truncate existing data
    TRUNCATE TABLE PRODUCTS;

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

    -- Commit the transaction
    COMMIT;

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

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

CALL import_products();

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