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 |