User data is complex, and includes a lot of information about the users themselves.

<aside> <img src="/icons/drafts_blue.svg" alt="/icons/drafts_blue.svg" width="40px" />

The users snapshot contains encrypted information. See our separate chapter on encryption for more information about this topic.

</aside>

User information includes:

• User ID
• Dates
  • Registration
  • Last profile change
  • Last authorized
  • Deleted at date — set for users who are GDPR deleted
• Encrypted:
  • E-mail address
  • MSISDN (phone number)
  • First name
  • Last name
• Flags for:
  • Newsletter subscriber
  • Test user
  • If the user has an e-mail address
  • If the user has a phone number (MSISDN)
• Profile type (standard, child)
• Region
• Avatar ID
• Parent user (indicating which users are profiles).

Base table

To create the table, we recommend the following structure:

CREATE TABLE IF NOT EXISTS USERS (
	USER_ID VARCHAR,
	REGISTRATION_DATE TIMESTAMP_TZ(9),
	PROFILE_UPDATED_DATE TIMESTAMP_TZ(9),
	LAST_AUTHORIZED_DATE TIMESTAMP_TZ(9),
	DELETED_AT_DATE TIMESTAMP_TZ(9),
	HAS_EMAIL BOOLEAN,
	HAS_MSISDN BOOLEAN,
	ENCRYPTED_MAIL BINARY(8388608),
	ENCRYPTED_MSISDN BINARY(8388608),
	ENCRYPTED_FIRST_NAME BINARY(8388608),
	ENCRYPTED_LAST_NAME BINARY(8388608),
	IS_NEWSLETTER_SUBSCRIBER BOOLEAN,
	IS_TEST_USER BOOLEAN,
	PROFILE_TYPE VARCHAR(16777216),
	REGION VARCHAR(16777216),
	AVATAR_ID VARCHAR,
	PARENT_USER_ID VARCHAR,
	IV BINARY(8388608),
	CONSTRAINT PKEY PRIMARY KEY (USER_ID),
	CONSTRAINT FKEY_PARENT_USER_ID FOREIGN KEY (PARENT_USER_ID) REFERENCES USERS (USER_ID) NOT ENFORCED
);

Importing data

This creates a stored procedure that atomically imports the data from the simplified releases report.

CREATE OR REPLACE PROCEDURE import_users()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    -- Start a transaction
    BEGIN TRANSACTION;
    
    -- (Optional) Truncate or clean the table first, if desired:
    TRUNCATE TABLE USERS;
    
    COPY INTO USERS
    FROM @BEAT_STAGE/snapshots/users.csv.zst
    FILE_FORMAT = (FORMAT_NAME = 'BEAT_CSV_FORMAT')
    MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
    ON_ERROR = ABORT_STATEMENT;
    
    -- Commit the transaction
    COMMIT;
    
    RETURN 'Users imported successfully.';
END;
$$;

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

CALL import_users();

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