SMS Dimension Tables | |||
SMSNumberDimension | |||
| Column Name | SQL Type | Snowflake Type | Description |
|---|---|---|---|
| snd_id | int | NUMBER(38,0) | Primary key |
| snd_number | varchar(32) | VARCHAR(32) | The SMS number |
| snd_active | bit (1=true/0=false) | BOOLEAN | Indicates whether the number is active or not |
| snd_deliveryType | varchar(10) | VARCHAR(10) | SMS event delivery type. Possible values:
|
| snd_description | varchar(128) | VARCHAR(128) | The SMS number description |
| snd_eipId | varchar(16) | VARCHAR(16) | The account identifier |
| snd_maxMMSImageSize | int | NUMBER(38,0) | The SMS max MMS image size in bytes |
| snd_maxMMSMessageSize | int | NUMBER(38,0) | The SMS max MMS message size in bytes |
| snd_maxSMSMessageSize | int | NUMBER(38,0) | The SMS max SMS message size in bytes |
| snd_parentEipId | varchar(16) | VARCHAR(16) | The parent account identifier |
| snd_payloadFormat | varchar(1024) | VARCHAR(1024) | The formatting macro to format webhook payload. For example: { |
| snd_product | varchar(16) | VARCHAR(16) | The product billing code |
| snd_provider | varchar(64) | VARCHAR(64) | The SMS provider |
| snd_redactAllowedDigits | int | NUMBER(38,0) | The number of digits permitted if redaction is enforced |
| snd_redactionStyle | varchar(8) | VARCHAR(8) | The redact style if applied. Possible value:
|
| snd_url | varchar(2048) | VARCHAR(2048) | URL for webhook delivery |
| snd_userId | varchar(64) | VARCHAR(64) | The SMS user id associated with this SMS number |
SMSUserDimension | |||
| Column Name | SQL Type | Snowflake Type | Description |
| sud_id | int | NUMBER(38,0) | Primary key |
| sud_firstName | varchar(64) | VARCHAR(64) | The SMS user first name |
| sud_lastName | varchar(64) | VARCHAR(64) | The SMS user last name |
| sud_email | varchar(64) | VARCHAR(64) | The SMS user email |
| sud_userId | varchar(64) | VARCHAR(64) | The user unique identifier |
| sud_enterpriseId | varchar(32) | VARCHAR(32) | The VoIP platform identifier |
| sud_eipId | varchar(16) | VARCHAR(16) | The account identifier |
| sud_parentEipId | varchar(16) | VARCHAR(16) | The parent account identifier |
| sud_timezone | varchar(32) | VARCHAR(32) | The SMS user time zone |
SMSProviderDimension | |||
| Column Name | SQL Type | Snowflake Type | Description |
| spd_id | int | NUMBER(38,0) | Primary key |
| spd_name | varchar(64) | VARCHAR(64) | The SMS provider name |
| spd_abbreviation | varchar(16) | VARCHAR(16) | The SMS provider name's abbreviation |
| spd_apiClassName | varchar(256) | VARCHAR(256) | The SMS provider's API class name |
| spd_messageParserClassName | varchar(256) | VARCHAR(256) | The SMS provider's message parser class name |
SMS Fact Tables | ||||||
SMSMessageFact | ||||||
| Column Name | SQL Type | Snowflake Type | Description | References | Comments | Indexed |
|---|---|---|---|---|---|---|
| smf_id | int | NUMBER(38,0) | Primary key | |||
| smf_messageId | varchar(128) | VARCHAR(128) | The SMS message unique identifier | |||
| smf_messageTag | varchar(32) | VARCHAR(32) | Provider specific message tag | |||
| smf_messageAddress | varchar(32) | VARCHAR(32) | Provider specific message address | |||
| smf_messageStatus | varchar(16) | VARCHAR(16) | SMS message status. Possible values:
| |||
| smf_messageType | varchar(4) | VARCHAR(4) | SMS message type. Possible values:
| |||
| smf_messageState | varchar(16) | VARCHAR(16) | SMS message state. Possible values:
| |||
| smf_messageDirection | varchar(16) | VARCHAR(16) | SMS message direction. Possible values:
| |||
| smf_fingerprint | varchar(64) | VARCHAR(64) | Provider specific message finger print | |||
| smf_flowType | varchar(16) | VARCHAR(16) | SMS message flow type. Possible values:
| |||
| smf_clientId | varchar(16) | VARCHAR(16) | Client identifier | Deprecated | ||
| smf_providerId | varchar(16) | VARCHAR(16) | Abbreviation of the SMS message provider | |||
| smf_providerIdFk | int | NUMBER(38,0) | Foreign key to SMS message provider | SMSProviderDimension | Yes | |
| smf_providerMessageId | varchar(64) | VARCHAR(64) | Provider specific message Id | |||
| smf_fromContactFirstName | varchar(64) | VARCHAR(64) | The first name associated to the contact this message received from | |||
| smf_fromContactLastName | varchar(64) | VARCHAR(64) | The last name associated to the contact this message received from. | |||
| smf_fromContactCompany | varchar(64) | VARCHAR(64) | The company name associated to the contact this message received from | |||
| smf_fromContactNumber | varchar(32) | VARCHAR(32) | The number associated to the contact this message received from | |||
| smf_fromContactType | varchar(16) | VARCHAR(16) | The contact type associated to the contact this message received from | |||
| smf_toContactFirstName | varchar(64) | VARCHAR(64) | The first name associated to the contact this message sent from | |||
| smf_toContactLastName | varchar(64) | VARCHAR(64) | The last name associated to the contact this message sent from | |||
| smf_toContactCompany | varchar(64) | VARCHAR(64) | The company name associated to the contact this message sent from | |||
| smf_toContactNumber | varchar(32) | VARCHAR(32) | The number associated to the contact this message sent from | |||
| smf_toContactType | varchar(16) | VARCHAR(16) | The contact type associated to the contact this message sent from | |||
| smf_attachmentContentType | varchar(16) | VARCHAR(16) | The content MIME type of the attachment if exists | |||
| smf_attachmentContent | text | VARCHAR(16777216) | Base64 encoded content of the attachment if exists | |||
| smf_toList | varchar(1024) | VARCHAR(1024) | To distribution list | |||
| smf_ccList | varchar(1024) | VARCHAR(1024) | CC distribution list | |||
| smf_bccList | varchar(max) | VARCHAR(16777216) | BCC distribution list | |||
| smf_sendingUserId | varchar(64) | VARCHAR(64) | Identifier of the SMS sending user | |||
| smf_receivingUserId | varchar(64) | VARCHAR(64) | Identifier of the SMS receiving user | Yes | ||
| smf_sendingUserIdFk | int | NUMBER(38,0) | Foreign key of the SMS user sending this message | SMSUserDimension | Yes | |
| smf_receivingUserIdFk | int | NUMBER(38,0) | Foreign key of the SMS user receiving this message | SMSUserDimension | ||
| smf_fromNumberFk | int | NUMBER(38,0) | Foreign key of the SMS from number | SMSNumberDimension | Yes | |
| smf_toNumberFk | int | NUMBER(38,0) | Foreign key of the SMS to number | SMSNumberDimension | Yes | |
| smf_hashA | bigint | NUMBER(38,0) | Hash code used to identify related messages | |||
| smf_hashB | bigint | NUMBER(38,0) | Hash code used to identify related messages | |||
| smf_accountId | int | NUMBER(38,0) | Foreign key of the account associated with this SMS message | AccountDimension | Yes | |
| smf_eipId | varchar(16) | VARCHAR(16) | The account identifier | |||
| smf_parentEipId | varchar(16) | VARCHAR(16) | Parent account identifier | |||
| smf_body | varchar(max) | VARCHAR(16777216) | The message body | |||
| smf_originationTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Time at which the SMS was originated. The origination time is shown using the UTC/GMT time zone. | Yes | ||
| smf_deliveredTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Time at which the SMS was delivered. The delivered time is shown using the UTC/GMT time zone. | |||
| smf_deletedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Time at which the SMS was deleted. The deletion time is shown using the UTC/GMT time zone. | |||
| smf_readTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Time at which the SMS has been read. The read time is shown using the UTC/GMT time zone. | |||
| smf_scheduledTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Time at which the SMS was scheduled. The scheduled time is shown using the UTC/GMT time zone. | Yes | ||
| smf_originationDateLocal | int | NUMBER(38,0) | Foreign key of the the SMS message origination date in Local time | DateDimension | Yes | |
| smf_originationDateUTC | int | NUMBER(38,0) | Foreign key of the the SMS message origination date in UTC | DateDimension | Yes | |
| smf_originationTimeLocal | int | NUMBER(38,0) | Foreign key of the the SMS message origination time of day in Local time | TimeOfDayDimension | Yes | |
| smf_originationTimeUTC | int | NUMBER(38,0) | Foreign key of the SMS message origination time time of day in UTC | TimeOfDayDimension | Yes | |
| smf_scheduledDateLocal | int | NUMBER(38,0) | Foreign key of the SMS message scheduled date in Local time | DateDimension | Yes | |
| smf_scheduledDateUTC | int | NUMBER(38,0) | Foreign key of the SMS message scheduled date in UTC | DateDimension | Yes | |
| smf_scheduledTimeLocal | int | NUMBER(38,0) | Foreign key of the SMS message scheduled time time of day in Local time | TimeOfDayDimension | Yes | |
| smf_scheduledTimeUTC | int | NUMBER(38,0) | Foreign key of the SMS message scheduled time time of day in UTC | TimeOfDayDimension | Yes | |
| smf_hasAttachments | bit (1=true/0=false) | BOOLEAN | Indicates whether the SMS has attachments or not | |||
| smf_deleted | bit (1=true/0=false) | BOOLEAN | Indicates whether the SMS has been deleted or not | |||
| smf_read | bit (1=true/0=false) | BOOLEAN | Indicates whether the SMS has been read or not | |||
| smf_visible | bit (1=true/0=false) | BOOLEAN | Indicates whether the SMS is visible or not | |||
| smf_toNumber | varchar(max) | VARCHAR(16777216) | SMS to number | |||
| smf_fromNumber | varchar(16) | VARCHAR(16) | SMS from number | |||
| smf_lastUpdatedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The SMS message last updated timestamp | |||
| smf_updated | bit (1=true/0=false) | BOOLEAN | ||||
SMSStopRequestFact | ||||||
| Column Name | Type | Snowflake Type | Description | References | Comments | |
| ssrf_id | int | NUMBER(38,0) | Primary key | |||
| ssrf_fromNumber | varchar(32) | VARCHAR(32) | The external number requesting the stop or unstop action | |||
| ssrf_smsNumber | varchar(32) | VARCHAR(32) | The SMS number receiving this stop/unstop request | |||
| ssrf_smsNumberFk | int | NUMBER(38,0) | Foreign key to the SMS number receiving this stop/unstop request | SMSNumberDimension | Yes | |
| ssrf_userId | varchar(64) | VARCHAR(64) | User Id associated with the SMS number receiving this stop/unstop request | |||
| ssrf_userIdFk | int | NUMBER(38,0) | Foreign key of the SMS user receiving this stop/unstop request | SMSUserDimension | Yes | |
| ssrf_restartTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Time in which the SMS Stop request has restarted (the user has sent unstop request). The restart time is shown using the UTC/GMT time zone | Yes | ||
| ssrf_restartDateLocal | int | NUMBER(38,0) | Foreign key to the restart date in Local time (date portion only). | DateDimension | Add a foreign key | Yes |
| ssrf_restartDateUTC | int | NUMBER(38,0) | Foreign key to the restart date in UTC (date portion only). | DateDimension | Add a foreign key | Yes |
| ssrf_restartTimeLocal | int | NUMBER(38,0) | Foreign key to the restart time in Local time (time portion only). | TimeOfDayDimension | Add a foreign key | Yes |
| ssrf_restartTimeUTC | int | NUMBER(38,0) | Foreign key to the restart time in UTC (time portion only). | TimeOfDayDimension | Add a foreign key | Yes |
| ssrf_stopTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Time in which the SMS Stop request has been sent to stop messaging. The stop time is shown using the UTC/GMT time zone. | Yes | ||
| ssrf_stopDateLocal | int | NUMBER(38,0) | Foreign key to the stop request date in Local time (date portion only). | DateDimension | Yes | |
| ssrf_stopDateUTC | int | NUMBER(38,0) | Foreign key to the stop request date in UTC (date portion only). | DateDimension | Yes | |
| ssrf_stopTimeLocal | int | NUMBER(38,0) | Foreign key to the stop request time in Local time (time portion only). | TimeOfDayDimension | Yes | |
| ssrf_stopTimeUTC | int | NUMBER(38,0) | Foreign key to the stop request time in UTC (time portion only). | TimeOfDayDimension | Yes | |
| ssrf_accountId | int | NUMBER(38,0) | Foreign key of the account associated with this SMS stop request | AccountDimension | Yes | |
| ssrf_eipId | varchar(16) | VARCHAR(16) | The account identifier | |||
| ssrf_parentEipId | varchar(16) | VARCHAR(16) | Parent account identifier | |||
| ssrf_active | bit (1=true/0=false) | BOOLEAN | Indicates whether the the user has sent stop request (1) or restarted messaging by sending unstop (0). | |||
| ssrf_lastUpdatedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The SMS stop request last updated timestamp | |||
| ssrf_updated | bit (1=true/0=false) | BOOLEAN | ||||
