-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 23:36:33.248319+00:00
WITH 
"twilio_incoming_phone_number_data_projected" AS (
    -- Projection: Selecting 18 out of 19 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "account_id",
        "address_id",
        "address_requirements",
        "beta",
        "capabilities_mms",
        "capabilities_sms",
        "capabilities_voice",
        "created_at",
        "emergency_address_id",
        "emergency_status",
        "friendly_name",
        "origin",
        "phone_number",
        "trunk_id",
        "updated_at",
        "voice_caller_id_lookup",
        "voice_url"
    FROM "memory"."main"."twilio_incoming_phone_number_data"
),

"twilio_incoming_phone_number_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> phone_number_id
    -- beta -> is_beta
    -- capabilities_mms -> supports_mms
    -- capabilities_sms -> supports_sms
    -- capabilities_voice -> supports_voice
    -- created_at -> creation_timestamp
    -- updated_at -> last_updated_timestamp
    -- voice_caller_id_lookup -> caller_id_lookup_enabled
    -- voice_url -> voice_handler_url
    SELECT 
        "id" AS "phone_number_id",
        "account_id",
        "address_id",
        "address_requirements",
        "beta" AS "is_beta",
        "capabilities_mms" AS "supports_mms",
        "capabilities_sms" AS "supports_sms",
        "capabilities_voice" AS "supports_voice",
        "created_at" AS "creation_timestamp",
        "emergency_address_id",
        "emergency_status",
        "friendly_name",
        "origin",
        "phone_number",
        "trunk_id",
        "updated_at" AS "last_updated_timestamp",
        "voice_caller_id_lookup" AS "caller_id_lookup_enabled",
        "voice_url" AS "voice_handler_url"
    FROM "twilio_incoming_phone_number_data_projected"
),

"twilio_incoming_phone_number_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- friendly_name: The problem is that the 'friendly_name' column contains a phone number format '(555) 555-5555' instead of actual friendly names. This appears to be placeholder data rather than genuine friendly names. Typically, a 'friendly_name' field would contain human-readable names or identifiers, not phone numbers. The correct values should be actual names or meaningful identifiers for the entities represented in this dataset. 
    SELECT
        "phone_number_id",
        "account_id",
        "address_id",
        "address_requirements",
        "is_beta",
        "supports_mms",
        "supports_sms",
        "supports_voice",
        "creation_timestamp",
        "emergency_address_id",
        "emergency_status",
        CASE
            WHEN "friendly_name" = '''(555) 555-5555''' THEN ''''
            ELSE "friendly_name"
        END AS "friendly_name",
        "origin",
        "phone_number",
        "trunk_id",
        "last_updated_timestamp",
        "caller_id_lookup_enabled",
        "voice_handler_url"
    FROM "twilio_incoming_phone_number_data_projected_renamed"
),

"twilio_incoming_phone_number_data_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- address_id: from DECIMAL to VARCHAR
    -- creation_timestamp: from VARCHAR to TIMESTAMP
    -- emergency_address_id: from DECIMAL to VARCHAR
    -- last_updated_timestamp: from VARCHAR to TIMESTAMP
    -- phone_number: from INT to VARCHAR
    -- trunk_id: from DECIMAL to VARCHAR
    SELECT
        "phone_number_id",
        "account_id",
        "address_requirements",
        "is_beta",
        "supports_mms",
        "supports_sms",
        "supports_voice",
        "emergency_status",
        "friendly_name",
        "origin",
        "caller_id_lookup_enabled",
        "voice_handler_url",
        CAST("address_id" AS VARCHAR) AS "address_id",
        CAST("creation_timestamp" AS TIMESTAMP) AS "creation_timestamp",
        CAST("emergency_address_id" AS VARCHAR) AS "emergency_address_id",
        CAST("last_updated_timestamp" AS TIMESTAMP) AS "last_updated_timestamp",
        CAST("phone_number" AS VARCHAR) AS "phone_number",
        CAST("trunk_id" AS VARCHAR) AS "trunk_id"
    FROM "twilio_incoming_phone_number_data_projected_renamed_cleaned"
),

"twilio_incoming_phone_number_data_projected_renamed_cleaned_casted_missing_handled" AS (
    -- Handling missing values: There are 2 columns with unacceptable missing values
    -- address_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- emergency_address_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "phone_number_id",
        "account_id",
        "address_requirements",
        "is_beta",
        "supports_mms",
        "supports_sms",
        "supports_voice",
        "emergency_status",
        "friendly_name",
        "origin",
        "caller_id_lookup_enabled",
        "voice_handler_url",
        "creation_timestamp",
        "last_updated_timestamp",
        "phone_number",
        "trunk_id"
    FROM "twilio_incoming_phone_number_data_projected_renamed_cleaned_casted"
)

-- COCOON BLOCK END
SELECT * FROM "twilio_incoming_phone_number_data_projected_renamed_cleaned_casted_missing_handled"

version: 2
models:
- name: stg_twilio_incoming_phone_number_data
  description: The table is about Twilio incoming phone numbers. It contains details
    such as account ID, phone number, capabilities (MMS, SMS, voice), creation date,
    friendly name, and voice URL. The table also includes information on address requirements,
    emergency status, and voice caller ID lookup settings for each phone number.
  columns:
  - name: phone_number_id
    description: Unique identifier for the phone number
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is a unique identifier for each phone number. For this
        table, each row represents a unique phone number, and phone_number_id is unique
        across rows.
  - name: account_id
    description: Unique identifier for the Twilio account
    tests:
    - not_null
  - name: address_requirements
    description: Requirements for address information
    tests:
    - not_null
  - name: is_beta
    description: Indicates if the number is in beta
    tests:
    - not_null
  - name: supports_mms
    description: Indicates if MMS is supported
    tests:
    - not_null
  - name: supports_sms
    description: Indicates if SMS is supported
    tests:
    - not_null
  - name: supports_voice
    description: Indicates if voice calls are supported
    tests:
    - not_null
  - name: emergency_status
    description: Current status for emergency services
    tests:
    - not_null
    - accepted_values:
        values:
        - Active
        - Inactive
        - On Alert
        - Responding
        - Stand By
        - All Clear
        - Code Red
        - Code Yellow
        - Code Green
        - Dispatched
        - En Route
        - On Scene
        - Returning
        - Available
        - Unavailable
  - name: friendly_name
    description: Human-readable name for the phone number
    tests:
    - not_null
  - name: origin
    description: Source or provider of the phone number
    tests:
    - not_null
    - accepted_values:
        values:
        - twilio
        - verizon
        - at&t
        - t-mobile
        - sprint
        - vonage
        - google voice
        - skype
        - whatsapp
        - viber
        - line
        - telegram
        - signal
        - bandwidth
        - nexmo
        - plivo
        - ringcentral
        - grasshopper
        - ooma
        - other
  - name: caller_id_lookup_enabled
    description: Indicates if caller ID lookup is enabled
    tests:
    - not_null
  - name: voice_handler_url
    description: URL for handling incoming voice calls
    tests:
    - not_null
  - name: creation_timestamp
    description: Timestamp of when the number was created
    tests:
    - not_null
  - name: last_updated_timestamp
    description: Timestamp of the last update
    tests:
    - not_null
  - name: phone_number
    description: The actual phone number
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column contains the actual phone number. For this table, each
        row represents a unique Twilio incoming phone number. Phone numbers are inherently
        unique, and Twilio would not allow duplicate phone numbers within an account.
        Therefore, this column should be unique across all rows.
  - name: trunk_id
    description: ID of the associated trunk
    cocoon_meta:
      missing_acceptable: Not applicable for non-trunk phone numbers.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 23:33:05.120257+00:00
WITH 
"twilio_call_data_projected" AS (
    -- Projection: Selecting 24 out of 25 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "account_id",
        "annotation",
        "answered_by",
        "caller_name",
        "created_at",
        "direction",
        "duration",
        "end_time",
        "forwarded_from",
        "from_",
        "from_formatted",
        "group_id",
        "outgoing_caller_id",
        "parent_call_id",
        "price",
        "price_unit",
        "queue_time",
        "start_time",
        "status",
        "to_",
        "to_formatted",
        "trunk_id",
        "updated_at"
    FROM "memory"."main"."twilio_call_data"
),

"twilio_call_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> call_id
    -- annotation -> call_notes
    -- created_at -> record_created_at
    -- direction -> call_direction
    -- duration -> call_duration
    -- end_time -> call_end_time
    -- from_ -> caller_number
    -- from_formatted -> caller_number_formatted
    -- price -> call_price
    -- price_unit -> price_currency
    -- start_time -> call_start_time
    -- status -> call_status
    -- to_ -> recipient_number
    -- to_formatted -> recipient_number_formatted
    -- updated_at -> record_updated_at
    SELECT 
        "id" AS "call_id",
        "account_id",
        "annotation" AS "call_notes",
        "answered_by",
        "caller_name",
        "created_at" AS "record_created_at",
        "direction" AS "call_direction",
        "duration" AS "call_duration",
        "end_time" AS "call_end_time",
        "forwarded_from",
        "from_" AS "caller_number",
        "from_formatted" AS "caller_number_formatted",
        "group_id",
        "outgoing_caller_id",
        "parent_call_id",
        "price" AS "call_price",
        "price_unit" AS "price_currency",
        "queue_time",
        "start_time" AS "call_start_time",
        "status" AS "call_status",
        "to_" AS "recipient_number",
        "to_formatted" AS "recipient_number_formatted",
        "trunk_id",
        "updated_at" AS "record_updated_at"
    FROM "twilio_call_data_projected"
),

"twilio_call_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- record_created_at: The problem is that the date '2022-02-30' is invalid because February never has 30 days. The correct value should be the last day of February in 2022, which is February 28th. All other dates in the list appear to be valid and do not need correction. 
    -- call_end_time: The problem is that '2022-02-30 17:23:13.000000 UTC' contains an invalid date. February 30th does not exist in any year. The most likely explanation is that this is a data entry error, and the intended date was February 28th (the last day of February in non-leap years) or March 2nd (if it was meant to be the last day of February plus 2 days). Since 2022 was not a leap year, the most probable correct date is February 28th. 
    -- call_start_time: The problem is that '2022-02-30 17:23:13.000000 UTC' is an invalid date because February never has 30 days. All other dates in the list appear to be valid. The correct value for this date should be the last day of February in 2022, which is February 28th. 
    -- record_updated_at: The problem is that '2022-02-30 17:23:13.000000 UTC' is an invalid date because February 30th doesn't exist in any calendar year. February can have at most 29 days in a leap year. The correct value should be the last valid day of February 2022, which is February 28th, 2022. All other dates in the list appear to be valid and don't need correction. 
    SELECT
        "call_id",
        "account_id",
        "call_notes",
        "answered_by",
        "caller_name",
        CASE
            WHEN "record_created_at" = '''2022-02-30 17:23:13.000000 UTC''' THEN '''2022-02-28 17:23:13.000000 UTC'''
            ELSE "record_created_at"
        END AS "record_created_at",
        "call_direction",
        "call_duration",
        CASE
            WHEN "call_end_time" = '''2022-02-30 17:23:13.000000 UTC''' THEN '''2022-02-28 17:23:13.000000 UTC'''
            ELSE "call_end_time"
        END AS "call_end_time",
        "forwarded_from",
        "caller_number",
        "caller_number_formatted",
        "group_id",
        "outgoing_caller_id",
        "parent_call_id",
        "call_price",
        "price_currency",
        "queue_time",
        CASE
            WHEN "call_start_time" = '''2022-02-30 17:23:13.000000 UTC''' THEN '''2022-02-28 17:23:13.000000 UTC'''
            ELSE "call_start_time"
        END AS "call_start_time",
        "call_status",
        "recipient_number",
        "recipient_number_formatted",
        "trunk_id",
        CASE
            WHEN "record_updated_at" = '''2022-02-30 17:23:13.000000 UTC''' THEN '''2022-02-28 17:23:13.000000 UTC'''
            ELSE "record_updated_at"
        END AS "record_updated_at"
    FROM "twilio_call_data_projected_renamed"
),

"twilio_call_data_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- answered_by: from DECIMAL to VARCHAR
    -- call_end_time: from VARCHAR to TIMESTAMP
    -- call_notes: from DECIMAL to VARCHAR
    -- call_start_time: from VARCHAR to TIMESTAMP
    -- caller_name: from DECIMAL to VARCHAR
    -- caller_number: from INT to VARCHAR
    -- forwarded_from: from INT to VARCHAR
    -- group_id: from DECIMAL to VARCHAR
    -- parent_call_id: from DECIMAL to VARCHAR
    -- recipient_number: from INT to VARCHAR
    -- record_created_at: from VARCHAR to TIMESTAMP
    -- record_updated_at: from VARCHAR to TIMESTAMP
    -- trunk_id: from DECIMAL to VARCHAR
    SELECT
        "call_id",
        "account_id",
        "call_direction",
        "call_duration",
        "caller_number_formatted",
        "outgoing_caller_id",
        "call_price",
        "price_currency",
        "queue_time",
        "call_status",
        "recipient_number_formatted",
        CAST("answered_by" AS VARCHAR) AS "answered_by",
        CASE
            WHEN "call_end_time" = '2022-02-30 17:23:13.000000 UTC' THEN 
                strptime('2022-02-28 17:23:13.000000 UTC', '%Y-%m-%d %H:%M:%S.%f UTC')
            ELSE 
                strptime("call_end_time", '%Y-%m-%d %H:%M:%S.%f UTC')
        END AS "call_end_time",
        CAST("call_notes" AS VARCHAR) AS "call_notes",
        CASE
            WHEN "call_start_time" = '2022-02-30 17:23:13.000000 UTC' THEN CAST('2022-02-28 17:23:13.000000 UTC' AS TIMESTAMP)
            ELSE CAST("call_start_time" AS TIMESTAMP)
        END AS "call_start_time",
        CAST("caller_name" AS VARCHAR) AS "caller_name",
        CAST("caller_number" AS VARCHAR) AS "caller_number",
        CAST("forwarded_from" AS VARCHAR) AS "forwarded_from",
        CAST("group_id" AS VARCHAR) AS "group_id",
        CAST("parent_call_id" AS VARCHAR) AS "parent_call_id",
        CAST("recipient_number" AS VARCHAR) AS "recipient_number",
        CASE
            WHEN TRY_CAST("record_created_at" AS TIMESTAMP) IS NOT NULL
            THEN CAST("record_created_at" AS TIMESTAMP)
            ELSE strptime(replace("record_created_at", '2022-02-30', '2022-02-28'), '%Y-%m-%d %H:%M:%S.%f UTC')
        END AS "record_created_at",
        CASE
            WHEN SUBSTRING("record_updated_at", 1, 10) = '2022-02-30'
            THEN CAST(REPLACE("record_updated_at", '2022-02-30', '2022-02-28') AS TIMESTAMP)
            ELSE CAST("record_updated_at" AS TIMESTAMP)
        END AS "record_updated_at",
        CAST("trunk_id" AS VARCHAR) AS "trunk_id"
    FROM "twilio_call_data_projected_renamed_cleaned"
),

"twilio_call_data_projected_renamed_cleaned_casted_missing_handled" AS (
    -- Handling missing values: There are 6 columns with unacceptable missing values
    -- call_notes has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- call_price has 22.22 percent missing. Strategy: 🔄 Unchanged
    -- caller_name has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- group_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- parent_call_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- trunk_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "call_id",
        "account_id",
        "call_direction",
        "call_duration",
        "caller_number_formatted",
        "outgoing_caller_id",
        "call_price",
        "price_currency",
        "queue_time",
        "call_status",
        "recipient_number_formatted",
        "answered_by",
        "call_end_time",
        "call_start_time",
        "caller_number",
        "forwarded_from",
        "recipient_number",
        "record_created_at",
        "record_updated_at"
    FROM "twilio_call_data_projected_renamed_cleaned_casted"
)

-- COCOON BLOCK END
SELECT * FROM "twilio_call_data_projected_renamed_cleaned_casted_missing_handled"

version: 2
models:
- name: stg_twilio_call_data
  description: The table is about Twilio call data. It contains details of individual
    phone calls, including unique call ID, account ID, call direction, duration, start
    and end times, phone numbers involved, call status, and pricing information. Each
    row represents a single call event with its associated metadata, capturing both
    inbound and completed calls.
  columns:
  - name: call_id
    description: Unique identifier for the call
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each call. For this
        table, each row represents a single call event. The call_id is unique across
        rows as it's designed to be a distinct identifier for each call.
  - name: account_id
    description: Unique identifier for the Twilio account
    tests:
    - not_null
  - name: call_direction
    description: Direction of the call (inbound/outbound)
    tests:
    - not_null
    - accepted_values:
        values:
        - inbound
        - outbound
  - name: call_duration
    description: Length of the call in seconds
    tests:
    - not_null
  - name: caller_number_formatted
    description: Formatted phone number of the caller
    tests:
    - not_null
  - name: outgoing_caller_id
    description: Caller ID for outgoing calls
    tests:
    - not_null
  - name: call_price
    description: Cost of the call
    tests:
    - not_null
  - name: price_currency
    description: Currency unit for the call price
    tests:
    - not_null
  - name: queue_time
    description: Time the call spent in queue
    tests:
    - not_null
  - name: call_status
    description: Current status of the call
    tests:
    - not_null
    - accepted_values:
        values:
        - completed
        - canceled
        - in progress
        - queued
        - ringing
        - no answer
        - busy
        - failed
        - voicemail
  - name: recipient_number_formatted
    description: Formatted phone number of the call recipient
    tests:
    - not_null
  - name: answered_by
    description: Who answered the call
    cocoon_meta:
      missing_acceptable: Not applicable for canceled or unanswered calls.
  - name: call_end_time
    description: Timestamp when the call ended
    tests:
    - not_null
  - name: call_start_time
    description: Timestamp when the call started
    tests:
    - not_null
  - name: caller_number
    description: Phone number of the caller
    tests:
    - not_null
  - name: forwarded_from
    description: Number from which the call was forwarded
    tests:
    - not_null
  - name: recipient_number
    description: Phone number of the call recipient
    tests:
    - not_null
  - name: record_created_at
    description: Timestamp when the call record was created
    tests:
    - not_null
  - name: record_updated_at
    description: Timestamp when the call record was last updated
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 23:42:59.416057+00:00
WITH 
"twilio_usage_record_data_projected" AS (
    -- Projection: Selecting 12 out of 13 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "account_id",
        "category",
        "end_date",
        "start_date",
        "as_of",
        "count_",
        "count_unit",
        "description",
        "price",
        "price_unit",
        "usage_",
        "usage_unit"
    FROM "memory"."main"."twilio_usage_record_data"
),

"twilio_usage_record_data_projected_casted" AS (
    -- Column Type Casting: 
    -- as_of: from VARCHAR to TIMESTAMP
    -- end_date: from VARCHAR to DATE
    -- start_date: from VARCHAR to DATE
    SELECT
        "account_id",
        "category",
        "count_",
        "count_unit",
        "description",
        "price",
        "price_unit",
        "usage_",
        "usage_unit",
        CAST("as_of" AS TIMESTAMP) AS "as_of",
        CAST("end_date" AS DATE) AS "end_date",
        CAST("start_date" AS DATE) AS "start_date"
    FROM "twilio_usage_record_data_projected"
)

-- COCOON BLOCK END
SELECT * FROM "twilio_usage_record_data_projected_casted"

version: 2
models:
- name: stg_twilio_usage_record_data
  description: The table is about Twilio usage records. It contains details of MMS
    usage for a specific account. Each row represents a usage record on a particular
    date. The table includes information such as the number of messages sent, their
    cost, and the usage quantity. It also provides timestamps for when the records
    were last updated.
  columns:
  - name: account_id
    description: account_id
    tests:
    - not_null
  - name: category
    description: category
    tests:
    - not_null
    - accepted_values:
        values:
        - mms
        - plain
        - peanut
        - peanut butter
        - pretzel
        - crispy
        - caramel
        - dark chocolate
        - almond
        - mint
  - name: count_
    description: count_
    tests:
    - not_null
  - name: count_unit
    description: count_unit
    tests:
    - not_null
    - accepted_values:
        values:
        - count
        - messages
        - threads
        - conversations
        - chats
        - emails
        - texts
        - posts
        - replies
        - comments
        - notifications
  - name: description
    description: description
    tests:
    - not_null
    - accepted_values:
        values:
        - MMS
        - SMS
  - name: price
    description: price
    tests:
    - not_null
  - name: price_unit
    description: price_unit
    tests:
    - not_null
  - name: usage_
    description: usage_
    tests:
    - not_null
  - name: usage_unit
    description: usage_unit
    tests:
    - not_null
    - accepted_values:
        values:
        - count
        - bytes
        - seconds
        - minutes
        - hours
        - days
        - kilobytes
        - megabytes
        - gigabytes
        - terabytes
        - requests
        - transactions
        - messages
        - api calls
        - queries
        - operations
        - segments
  - name: as_of
    description: as_of
    tests:
    - not_null
  - name: end_date
    description: end_date
    tests:
    - not_null
  - name: start_date
    description: start_date
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 23:37:52.826731+00:00
WITH 
"twilio_message_data_projected" AS (
    -- Projection: Selecting 17 out of 18 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "account_id",
        "body",
        "created_at",
        "date_sent",
        "direction",
        "error_code",
        "error_message",
        "from_",
        "messaging_service_sid",
        "num_media",
        "num_segments",
        "price",
        "price_unit",
        "status",
        "to_",
        "updated_at"
    FROM "memory"."main"."twilio_message_data"
),

"twilio_message_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> message_id
    -- body -> message_content
    -- date_sent -> sent_at
    -- direction -> message_direction
    -- error_message -> error_description
    -- from_ -> sender_number
    -- messaging_service_sid -> messaging_service_id
    -- num_media -> media_count
    -- num_segments -> segment_count
    -- price -> message_cost
    -- price_unit -> currency
    -- status -> delivery_status
    -- to_ -> recipient_number
    SELECT 
        "id" AS "message_id",
        "account_id",
        "body" AS "message_content",
        "created_at",
        "date_sent" AS "sent_at",
        "direction" AS "message_direction",
        "error_code",
        "error_message" AS "error_description",
        "from_" AS "sender_number",
        "messaging_service_sid" AS "messaging_service_id",
        "num_media" AS "media_count",
        "num_segments" AS "segment_count",
        "price" AS "message_cost",
        "price_unit" AS "currency",
        "status" AS "delivery_status",
        "to_" AS "recipient_number",
        "updated_at"
    FROM "twilio_message_data_projected"
),

"twilio_message_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- created_at: from VARCHAR to TIMESTAMP
    -- error_code: from DECIMAL to VARCHAR
    -- error_description: from DECIMAL to VARCHAR
    -- messaging_service_id: from DECIMAL to VARCHAR
    -- recipient_number: from INT to VARCHAR
    -- sender_number: from INT to VARCHAR
    -- sent_at: from VARCHAR to TIMESTAMP
    -- updated_at: from VARCHAR to TIMESTAMP
    SELECT
        "message_id",
        "account_id",
        "message_content",
        "message_direction",
        "media_count",
        "segment_count",
        "message_cost",
        "currency",
        "delivery_status",
        CAST("created_at" AS TIMESTAMP) AS "created_at",
        CAST("error_code" AS VARCHAR) AS "error_code",
        CAST("error_description" AS VARCHAR) AS "error_description",
        CAST("messaging_service_id" AS VARCHAR) AS "messaging_service_id",
        CAST("recipient_number" AS VARCHAR) AS "recipient_number",
        CAST("sender_number" AS VARCHAR) AS "sender_number",
        CAST("sent_at" AS TIMESTAMP) AS "sent_at",
        CAST("updated_at" AS TIMESTAMP) AS "updated_at"
    FROM "twilio_message_data_projected_renamed"
),

"twilio_message_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 1 columns with unacceptable missing values
    -- messaging_service_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "message_id",
        "account_id",
        "message_content",
        "message_direction",
        "media_count",
        "segment_count",
        "message_cost",
        "currency",
        "delivery_status",
        "created_at",
        "error_code",
        "error_description",
        "recipient_number",
        "sender_number",
        "sent_at",
        "updated_at"
    FROM "twilio_message_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "twilio_message_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_twilio_message_data
  description: The table is about Twilio messages. It contains details of each message,
    including unique ID, account ID, message body, creation time, sent time, direction,
    error information, sender and recipient numbers, messaging service SID, media
    count, segment count, price, price unit, status, and update time. Each row represents
    a single outbound SMS message with its associated metadata and delivery information.
  columns:
  - name: message_id
    description: Unique identifier for the SMS message
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each SMS message.
        For this table, each row represents a single outbound SMS message. The message_id
        appears to be unique across rows, as it's a standard practice for message
        systems to assign unique identifiers to each message.
  - name: account_id
    description: Unique identifier for the Twilio account
    tests:
    - not_null
  - name: message_content
    description: Encrypted content of the SMS message
    tests:
    - not_null
  - name: message_direction
    description: Indicates the direction of the message (outbound/inbound)
    tests:
    - not_null
    - accepted_values:
        values:
        - outbound
        - inbound
        - outbound-api
  - name: media_count
    description: Number of media attachments in the message
    tests:
    - not_null
  - name: segment_count
    description: Number of segments the message was split into
    tests:
    - not_null
  - name: message_cost
    description: Cost of sending the message
    tests:
    - not_null
  - name: currency
    description: Currency unit for the message cost
    tests:
    - not_null
  - name: delivery_status
    description: Current status of the message delivery
    tests:
    - not_null
    - accepted_values:
        values:
        - delivered
        - failed
        - bounced
        - deferred
        - delayed
        - queued
        - sending
        - sent
        - rejected
        - spam
        - unknown
  - name: created_at
    description: Timestamp when the message record was created
    tests:
    - not_null
  - name: error_code
    description: Error code if message delivery failed
    cocoon_meta:
      missing_acceptable: Not applicable for successfully delivered messages.
  - name: error_description
    description: Detailed error message if delivery failed
    cocoon_meta:
      missing_acceptable: Not applicable for successfully delivered messages.
  - name: recipient_number
    description: Phone number of the recipient
    tests:
    - not_null
  - name: sender_number
    description: Phone number of the sender
    tests:
    - not_null
  - name: sent_at
    description: Timestamp when the message was sent
    tests:
    - not_null
  - name: updated_at
    description: Timestamp of the last update to the record
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 23:40:26.363718+00:00
WITH 
"twilio_messaging_service_data_projected" AS (
    -- Projection: Selecting 22 out of 23 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "_fivetran_deleted",
        "account_id",
        "area_code_geomatch",
        "created_at",
        "fallback_method",
        "fallback_to_long_code",
        "fallback_url",
        "friendly_name",
        "inbound_method",
        "inbound_request_url",
        "mms_converter",
        "scan_message_content",
        "smart_encoding",
        "status_callback",
        "sticky_sender",
        "synchronous_validation",
        "updated_at",
        "us_app_to_person_registered",
        "use_inbound_webhook_on_number",
        "usecase",
        "validity_period"
    FROM "memory"."main"."twilio_messaging_service_data"
),

"twilio_messaging_service_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> service_id
    -- _fivetran_deleted -> is_deleted
    -- fallback_method -> fallback_http_method
    -- friendly_name -> service_name
    -- inbound_method -> inbound_http_method
    -- status_callback -> status_callback_url
    -- us_app_to_person_registered -> us_a2p_10dlc_registered
    -- use_inbound_webhook_on_number -> use_inbound_webhook
    -- validity_period -> validity_period_seconds
    SELECT 
        "id" AS "service_id",
        "_fivetran_deleted" AS "is_deleted",
        "account_id",
        "area_code_geomatch",
        "created_at",
        "fallback_method" AS "fallback_http_method",
        "fallback_to_long_code",
        "fallback_url",
        "friendly_name" AS "service_name",
        "inbound_method" AS "inbound_http_method",
        "inbound_request_url",
        "mms_converter",
        "scan_message_content",
        "smart_encoding",
        "status_callback" AS "status_callback_url",
        "sticky_sender",
        "synchronous_validation",
        "updated_at",
        "us_app_to_person_registered" AS "us_a2p_10dlc_registered",
        "use_inbound_webhook_on_number" AS "use_inbound_webhook",
        "usecase",
        "validity_period" AS "validity_period_seconds"
    FROM "twilio_messaging_service_data_projected"
),

"twilio_messaging_service_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- created_at: from VARCHAR to TIMESTAMP
    -- fallback_url: from DECIMAL to VARCHAR
    -- inbound_request_url: from DECIMAL to VARCHAR
    -- status_callback_url: from DECIMAL to VARCHAR
    -- updated_at: from VARCHAR to TIMESTAMP
    SELECT
        "service_id",
        "is_deleted",
        "account_id",
        "area_code_geomatch",
        "fallback_http_method",
        "fallback_to_long_code",
        "service_name",
        "inbound_http_method",
        "mms_converter",
        "scan_message_content",
        "smart_encoding",
        "sticky_sender",
        "synchronous_validation",
        "us_a2p_10dlc_registered",
        "use_inbound_webhook",
        "usecase",
        "validity_period_seconds",
        CAST("created_at" AS TIMESTAMP) AS "created_at",
        CAST("fallback_url" AS VARCHAR) AS "fallback_url",
        CAST("inbound_request_url" AS VARCHAR) AS "inbound_request_url",
        CAST("status_callback_url" AS VARCHAR) AS "status_callback_url",
        CAST("updated_at" AS TIMESTAMP) AS "updated_at"
    FROM "twilio_messaging_service_data_projected_renamed"
),

"twilio_messaging_service_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 2 columns with unacceptable missing values
    -- inbound_request_url has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- status_callback_url has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "service_id",
        "is_deleted",
        "account_id",
        "area_code_geomatch",
        "fallback_http_method",
        "fallback_to_long_code",
        "service_name",
        "inbound_http_method",
        "mms_converter",
        "scan_message_content",
        "smart_encoding",
        "sticky_sender",
        "synchronous_validation",
        "us_a2p_10dlc_registered",
        "use_inbound_webhook",
        "usecase",
        "validity_period_seconds",
        "created_at",
        "fallback_url",
        "updated_at"
    FROM "twilio_messaging_service_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "twilio_messaging_service_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_twilio_messaging_service_data
  description: The table is about Twilio messaging service data. It contains configuration
    details for a messaging service, including its ID, account ID, creation date,
    and various settings. Key fields cover functionality like fallback behavior, inbound
    message handling, encoding options, and webhook usage. The table also includes
    flags for features like MMS conversion and sender stickiness.
  columns:
  - name: service_id
    description: Unique identifier for the messaging service
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for the messaging service.
        For this table, each row represents a distinct messaging service configuration.
        The service_id is likely to be unique across rows as it's a standard practice
        to use unique identifiers for services.
  - name: is_deleted
    description: Indicates if the record is deleted
    tests:
    - not_null
  - name: account_id
    description: Unique identifier for the Twilio account
    tests:
    - not_null
  - name: area_code_geomatch
    description: Enables geomatching based on area code
    tests:
    - not_null
  - name: fallback_http_method
    description: HTTP method for fallback requests
    tests:
    - not_null
    - accepted_values:
        values:
        - GET
        - POST
        - PUT
        - DELETE
        - PATCH
        - HEAD
        - OPTIONS
        - TRACE
        - CONNECT
  - name: fallback_to_long_code
    description: Allows fallback to long code numbers
    tests:
    - not_null
  - name: service_name
    description: Human-readable name for the messaging service
    tests:
    - not_null
  - name: inbound_http_method
    description: HTTP method for inbound message requests
    tests:
    - not_null
    - accepted_values:
        values:
        - GET
        - POST
        - PUT
        - DELETE
        - HEAD
        - OPTIONS
        - PATCH
        - TRACE
        - CONNECT
  - name: mms_converter
    description: Enables MMS to SMS conversion
    tests:
    - not_null
  - name: scan_message_content
    description: Determines content scanning behavior
    tests:
    - not_null
    - accepted_values:
        values:
        - inherit
        - enable
        - disable
        - force
  - name: smart_encoding
    description: Enables smart character encoding
    tests:
    - not_null
  - name: sticky_sender
    description: Maintains consistent sender for recipients
    tests:
    - not_null
  - name: synchronous_validation
    description: Enables synchronous number validation
    tests:
    - not_null
  - name: us_a2p_10dlc_registered
    description: Registered for US A2P 10DLC
    tests:
    - not_null
  - name: use_inbound_webhook
    description: Uses webhook for inbound messages
    tests:
    - not_null
  - name: usecase
    description: Declared use case for the service
    tests:
    - not_null
  - name: validity_period_seconds
    description: Message validity period in seconds
    tests:
    - not_null
  - name: created_at
    description: Timestamp of messaging service creation
    tests:
    - not_null
  - name: fallback_url
    description: URL for fallback requests
    cocoon_meta:
      missing_acceptable: Not needed when fallback_to_long_code is True.
  - name: updated_at
    description: Timestamp of last update
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 23:41:41.817864+00:00
WITH 
"twilio_outgoing_caller_id_data_projected" AS (
    -- Projection: Selecting 5 out of 6 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "created_at",
        "friendly_name",
        "phone_number",
        "updated_at"
    FROM "memory"."main"."twilio_outgoing_caller_id_data"
),

"twilio_outgoing_caller_id_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> caller_id_sid
    -- created_at -> creation_timestamp
    -- friendly_name -> display_name
    -- phone_number -> encrypted_phone_number
    -- updated_at -> last_updated_timestamp
    SELECT 
        "id" AS "caller_id_sid",
        "created_at" AS "creation_timestamp",
        "friendly_name" AS "display_name",
        "phone_number" AS "encrypted_phone_number",
        "updated_at" AS "last_updated_timestamp"
    FROM "twilio_outgoing_caller_id_data_projected"
),

"twilio_outgoing_caller_id_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- caller_id_sid: The problem is that all values in the caller_id_sid column contain the substring 'ez' which appears to be an anomaly in the otherwise standard format of Twilio Phone Number SIDs. Twilio Phone Number SIDs typically follow the pattern 'PN' followed by 32 hexadecimal characters. The 'ez' substring is likely a typo or an error in the identifier generation process. The correct values should have this 'ez' substring removed to conform to the standard Twilio Phone Number SID format. 
    -- display_name: The problem is that the display_name column contains a phone number ('(555) 555-5555') instead of a name. This is unusual because the display_name field typically contains a person's name, not their contact information. Phone numbers should be stored in a separate column dedicated to contact information. The correct value for display_name should be a name or an empty string if the name is unknown. 
    SELECT
        CASE
            WHEN "caller_id_sid" = 'PN44c122e81553f4502ae1ed93bez832ed' THEN 'PN44c122e81553f4502ae1ed93b832ed'
            WHEN "caller_id_sid" = 'PN9964151032a6aeb47dc10d199eza8fcb' THEN 'PN9964151032a6aeb47dc10d199a8fcb'
            WHEN "caller_id_sid" = 'PNd7b72b5139b563564a37c3661ezea947' THEN 'PNd7b72b5139b563564a37c3661ea947'
            ELSE "caller_id_sid"
        END AS "caller_id_sid",
        "creation_timestamp",
        CASE
            WHEN "display_name" = '''(555) 555-5555''' THEN ''''
            ELSE "display_name"
        END AS "display_name",
        "encrypted_phone_number",
        "last_updated_timestamp"
    FROM "twilio_outgoing_caller_id_data_projected_renamed"
),

"twilio_outgoing_caller_id_data_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- creation_timestamp: from VARCHAR to TIMESTAMP
    -- last_updated_timestamp: from VARCHAR to TIMESTAMP
    SELECT
        "caller_id_sid",
        "display_name",
        "encrypted_phone_number",
        CAST("creation_timestamp" AS TIMESTAMP) AS "creation_timestamp",
        CAST("last_updated_timestamp" AS TIMESTAMP) AS "last_updated_timestamp"
    FROM "twilio_outgoing_caller_id_data_projected_renamed_cleaned"
)

-- COCOON BLOCK END
SELECT * FROM "twilio_outgoing_caller_id_data_projected_renamed_cleaned_casted"

version: 2
models:
- name: stg_twilio_outgoing_caller_id_data
  description: The table is about Twilio outgoing caller IDs. It contains details
    of each caller ID, including a unique identifier, creation date, friendly name,
    encrypted phone number, and last update time. Each row represents a single outgoing
    caller ID configuration in the Twilio system. The data appears to be anonymized
    for privacy, with phone numbers encrypted.
  columns:
  - name: caller_id_sid
    description: Unique identifier for the caller ID
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each caller ID. For
        this table, each row represents a single outgoing caller ID configuration
        in the Twilio system. The caller_id_sid appears to be unique across rows,
        as it's described as a "unique identifier".
  - name: display_name
    description: Human-readable name for the caller ID
    tests:
    - not_null
  - name: encrypted_phone_number
    description: Encrypted phone number associated with the caller ID
    tests:
    - not_null
  - name: creation_timestamp
    description: Timestamp when the caller ID was created
    tests:
    - not_null
  - name: last_updated_timestamp
    description: Timestamp of the last update to the caller ID
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 23:30:22.217681+00:00
WITH 
"twilio_address_data_projected" AS (
    -- Projection: Selecting 14 out of 15 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "account_id",
        "city",
        "created_at",
        "customer_name",
        "emergency_enabled",
        "friendly_name",
        "iso_country",
        "postal_code",
        "region",
        "street",
        "updated_at",
        "validated",
        "verified"
    FROM "memory"."main"."twilio_address_data"
),

"twilio_address_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> address_id
    -- created_at -> creation_timestamp
    -- iso_country -> country_code
    -- region -> state_or_region
    -- street -> street_address
    -- updated_at -> last_updated_timestamp
    -- validated -> is_validated
    -- verified -> is_verified
    SELECT 
        "id" AS "address_id",
        "account_id",
        "city",
        "created_at" AS "creation_timestamp",
        "customer_name",
        "emergency_enabled",
        "friendly_name",
        "iso_country" AS "country_code",
        "postal_code",
        "region" AS "state_or_region",
        "street" AS "street_address",
        "updated_at" AS "last_updated_timestamp",
        "validated" AS "is_validated",
        "verified" AS "is_verified"
    FROM "twilio_address_data_projected"
),

"twilio_address_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- friendly_name: The problem is that the friendly_name column contains a single unusual value that combines a general label ("Address") with a precise timestamp. This is not a typical format for a friendly name, which should be more human-readable and less technical. The correct value should be just "Address" without the timestamp. 
    SELECT
        "address_id",
        "account_id",
        "city",
        "creation_timestamp",
        "customer_name",
        "emergency_enabled",
        CASE
            WHEN "friendly_name" = 'Address 2022-06-23T06:01:26.506384Z' THEN 'Address'
            ELSE "friendly_name"
        END AS "friendly_name",
        "country_code",
        "postal_code",
        "state_or_region",
        "street_address",
        "last_updated_timestamp",
        "is_validated",
        "is_verified"
    FROM "twilio_address_data_projected_renamed"
),

"twilio_address_data_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- creation_timestamp: from VARCHAR to TIMESTAMP
    -- customer_name: from DECIMAL to VARCHAR
    -- last_updated_timestamp: from VARCHAR to TIMESTAMP
    -- postal_code: from INT to VARCHAR
    SELECT
        "address_id",
        "account_id",
        "city",
        "emergency_enabled",
        "friendly_name",
        "country_code",
        "state_or_region",
        "street_address",
        "is_validated",
        "is_verified",
        CAST("creation_timestamp" AS TIMESTAMP) AS "creation_timestamp",
        CAST("customer_name" AS VARCHAR) AS "customer_name",
        CAST("last_updated_timestamp" AS TIMESTAMP) AS "last_updated_timestamp",
        CAST("postal_code" AS VARCHAR) AS "postal_code"
    FROM "twilio_address_data_projected_renamed_cleaned"
),

"twilio_address_data_projected_renamed_cleaned_casted_missing_handled" AS (
    -- Handling missing values: There are 1 columns with unacceptable missing values
    -- customer_name has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "address_id",
        "account_id",
        "city",
        "emergency_enabled",
        "friendly_name",
        "country_code",
        "state_or_region",
        "street_address",
        "is_validated",
        "is_verified",
        "creation_timestamp",
        "last_updated_timestamp",
        "postal_code"
    FROM "twilio_address_data_projected_renamed_cleaned_casted"
)

-- COCOON BLOCK END
SELECT * FROM "twilio_address_data_projected_renamed_cleaned_casted_missing_handled"

version: 2
models:
- name: stg_twilio_address_data
  description: The table is about Twilio address data. It contains details of addresses
    linked to Twilio accounts. Each record includes an ID, account ID, location information
    (city, country, postal code, region, street), customer name, and various flags.
    The table also tracks when the address was created and last updated. It indicates
    if the address is emergency-enabled, validated, and verified.
  columns:
  - name: address_id
    description: Unique identifier for the address record
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is a unique identifier for each address record. For
        this table, each row represents a distinct address linked to a Twilio account.
        The address_id is unique across rows.
  - name: account_id
    description: Unique identifier for the Twilio account
    tests:
    - not_null
  - name: city
    description: City where the address is located
    tests:
    - not_null
  - name: emergency_enabled
    description: Indicates if address is enabled for emergency services
    tests:
    - not_null
  - name: friendly_name
    description: Human-readable name for the address
    tests:
    - not_null
  - name: country_code
    description: Two-letter ISO country code
    tests:
    - not_null
  - name: state_or_region
    description: State or region of the address
    tests:
    - not_null
  - name: street_address
    description: Street address
    tests:
    - not_null
  - name: is_validated
    description: Indicates if the address has been validated
    tests:
    - not_null
  - name: is_verified
    description: Indicates if the address has been verified
    tests:
    - not_null
  - name: creation_timestamp
    description: Timestamp when the address record was created
    tests:
    - not_null
  - name: last_updated_timestamp
    description: Timestamp when the address record was last updated
    tests:
    - not_null
  - name: postal_code
    description: Postal code of the address
    tests:
    - not_null

-- Slowly Changing Dimension: Dimension keys are "account_id"
-- Effective date columns are "last_update_date"
-- We will create Type 1 SCD (latest snapshot)
SELECT 
    "account_id",
    "account_display_name",
    "parent_account_id",
    "account_status",
    "account_type",
    "account_creation_date"
FROM (
     SELECT 
            "account_id",
            "account_display_name",
            "parent_account_id",
            "account_status",
            "account_type",
            "account_creation_date",
            ROW_NUMBER() OVER (
                PARTITION BY "account_id" 
                ORDER BY "last_update_date" 
            DESC) AS "cocoon_rn"
    FROM "stg_twilio_account_history_data"
) ranked
WHERE "cocoon_rn" = 1

version: 2
models:
- name: snapshot_twilio_account_history_data
  description: The table is about current Twilio account information. It contains
    the most up-to-date details for each unique Twilio account. The table includes
    account IDs, display names, parent account IDs, statuses, types, and creation
    dates. It represents the latest snapshot of Twilio account data, excluding historical
    versions and update timestamps.
  columns:
  - name: account_id
    description: Unique identifier for the Twilio account
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: Unique dimension key, derived from the slowly changing dimension
  - name: account_display_name
    description: User-defined name for the account
    tests:
    - not_null
  - name: parent_account_id
    description: ID of the parent account
    tests:
    - not_null
  - name: account_status
    description: Current status of the account
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - suspended
        - closed
        - pending
        - on hold
        - dormant
        - restricted
  - name: account_type
    description: Type of Twilio account
    tests:
    - not_null
    - accepted_values:
        values:
        - Full
        - Trial
        - Free
        - Paid
        - Upgraded
        - Enterprise
  - name: account_creation_date
    description: Timestamp when the account was created
    tests:
    - not_null
cocoon_meta:
  scd_base_table: stg_twilio_account_history_data