-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:59:48.468752+00:00
WITH 
"survey_response_projected" AS (
    -- Projection: Selecting 18 out of 19 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "distribution_channel",
        "duration_in_seconds",
        "end_date",
        "finished",
        "ip_address",
        "last_modified_date",
        "location_latitude",
        "location_longitude",
        "progress",
        "recipient_email",
        "recipient_first_name",
        "recipient_last_name",
        "recorded_date",
        "start_date",
        "status",
        "survey_id",
        "user_language"
    FROM "memory"."main"."survey_response"
),

"survey_response_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> response_id
    -- duration_in_seconds -> survey_duration_seconds
    -- end_date -> response_end_datetime
    -- finished -> is_survey_completed
    -- ip_address -> respondent_ip_address
    -- last_modified_date -> last_modified_datetime
    -- location_latitude -> respondent_latitude
    -- location_longitude -> respondent_longitude
    -- progress -> survey_progress_percentage
    -- recorded_date -> response_recorded_datetime
    -- start_date -> response_start_datetime
    -- status -> response_status
    -- user_language -> respondent_language
    SELECT 
        "id" AS "response_id",
        "distribution_channel",
        "duration_in_seconds" AS "survey_duration_seconds",
        "end_date" AS "response_end_datetime",
        "finished" AS "is_survey_completed",
        "ip_address" AS "respondent_ip_address",
        "last_modified_date" AS "last_modified_datetime",
        "location_latitude" AS "respondent_latitude",
        "location_longitude" AS "respondent_longitude",
        "progress" AS "survey_progress_percentage",
        "recipient_email",
        "recipient_first_name",
        "recipient_last_name",
        "recorded_date" AS "response_recorded_datetime",
        "start_date" AS "response_start_datetime",
        "status" AS "response_status",
        "survey_id",
        "user_language" AS "respondent_language"
    FROM "survey_response_projected"
),

"survey_response_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- recipient_last_name: The problem is that the last name "Aggarwak=l" contains an equals sign (=), which is not typical in names. It appears that the equals sign was mistakenly included, possibly due to a data entry error or parsing issue. The correct value should be "Aggarwal", which is a common Indian surname. 
    SELECT
        "response_id",
        "distribution_channel",
        "survey_duration_seconds",
        "response_end_datetime",
        "is_survey_completed",
        "respondent_ip_address",
        "last_modified_datetime",
        "respondent_latitude",
        "respondent_longitude",
        "survey_progress_percentage",
        "recipient_email",
        "recipient_first_name",
        CASE
            WHEN "recipient_last_name" = '''Aggarwak=l''' THEN '''Aggarwal'''
            ELSE "recipient_last_name"
        END AS "recipient_last_name",
        "response_recorded_datetime",
        "response_start_datetime",
        "response_status",
        "survey_id",
        "respondent_language"
    FROM "survey_response_projected_renamed"
),

"survey_response_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- is_survey_completed: from INT to BIT
    -- last_modified_datetime: from VARCHAR to TIMESTAMP
    -- response_end_datetime: from VARCHAR to TIMESTAMP
    -- response_recorded_datetime: from VARCHAR to TIMESTAMP
    -- response_start_datetime: from VARCHAR to TIMESTAMP
    SELECT
        "response_id",
        "distribution_channel",
        "survey_duration_seconds",
        "respondent_ip_address",
        "respondent_latitude",
        "respondent_longitude",
        "survey_progress_percentage",
        "recipient_email",
        "recipient_first_name",
        "recipient_last_name",
        "response_status",
        "survey_id",
        "respondent_language",
        CAST("is_survey_completed" AS BIT) AS "is_survey_completed",
        CAST("last_modified_datetime" AS TIMESTAMP) AS "last_modified_datetime",
        CAST("response_end_datetime" AS TIMESTAMP) AS "response_end_datetime",
        CAST("response_recorded_datetime" AS TIMESTAMP) AS "response_recorded_datetime",
        CAST("response_start_datetime" AS TIMESTAMP) AS "response_start_datetime"
    FROM "survey_response_projected_renamed_cleaned"
),

"survey_response_projected_renamed_cleaned_casted_missing_handled" AS (
    -- Handling missing values: There are 4 columns with unacceptable missing values
    -- recipient_first_name has 80.0 percent missing. Strategy: 🔄 Unchanged
    -- recipient_last_name has 80.0 percent missing. Strategy: 🔄 Unchanged
    -- respondent_latitude has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- respondent_longitude has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "response_id",
        "distribution_channel",
        "survey_duration_seconds",
        "respondent_ip_address",
        "survey_progress_percentage",
        "recipient_email",
        "recipient_first_name",
        "recipient_last_name",
        "response_status",
        "survey_id",
        "respondent_language",
        "is_survey_completed",
        "last_modified_datetime",
        "response_end_datetime",
        "response_recorded_datetime",
        "response_start_datetime"
    FROM "survey_response_projected_renamed_cleaned_casted"
)

-- COCOON BLOCK END
SELECT * FROM "survey_response_projected_renamed_cleaned_casted_missing_handled"

version: 2
models:
- name: stg_survey_response
  description: The table is about survey responses. It contains details like response
    ID, distribution channel, duration, start/end dates, IP address, respondent information,
    survey progress, and survey ID. Each row represents a single survey response with
    associated metadata. The table tracks both completed and incomplete survey attempts
    across different channels (email, anonymous) with timestamps and respondent details
    when available.
  columns:
  - name: response_id
    description: Unique identifier for the survey response
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each survey response.
        For this table, each row is for a single survey response. response_id is unique
        across rows.
  - name: distribution_channel
    description: Method used to distribute the survey
    tests:
    - not_null
    - accepted_values:
        values:
        - anonymous
        - email
        - phone
        - mail
        - in-person
        - online
        - social media
  - name: survey_duration_seconds
    description: Time taken to complete the survey in seconds
    tests:
    - not_null
  - name: respondent_ip_address
    description: IP address of the respondent
    tests:
    - not_null
  - name: survey_progress_percentage
    description: Percentage of survey completed
    tests:
    - not_null
  - name: recipient_email
    description: Email address of the survey recipient
    cocoon_meta:
      missing_acceptable: Not applicable for anonymous distribution channels
  - name: recipient_first_name
    description: First name of the survey recipient
    tests:
    - not_null
  - name: recipient_last_name
    description: Last name of the survey recipient
    tests:
    - not_null
  - name: response_status
    description: Status of the survey response
    tests:
    - not_null
  - name: survey_id
    description: Unique identifier for the survey
    tests:
    - not_null
  - name: respondent_language
    description: Language used by the respondent
    tests:
    - not_null
    - accepted_values:
        values:
        - EN
        - ES
        - FR
        - DE
        - IT
        - PT
        - RU
        - ZH
        - JA
        - KO
        - AR
        - HI
        - BN
        - UR
        - FA
        - TR
        - VI
        - TH
        - ID
        - NL
        - PL
        - SV
        - FI
        - 'NO'
        - DA
  - name: is_survey_completed
    description: Indicates if the survey was completed (0=No, 1=Yes)
    tests:
    - not_null
  - name: last_modified_datetime
    description: Date and time of last modification to the response
    tests:
    - not_null
  - name: response_end_datetime
    description: Date and time when the survey response ended
    tests:
    - not_null
  - name: response_recorded_datetime
    description: Date and time when the response was recorded
    tests:
    - not_null
  - name: response_start_datetime
    description: Date and time when the survey response started
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:42:00.767074+00:00
WITH 
"directory_contact_projected" AS (
    -- Projection: Selecting 17 out of 18 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "directory_id",
        "id",
        "creation_date",
        "directory_unsubscribe_date",
        "directory_unsubscribed",
        "email",
        "email_domain",
        "embedded_data_last_active_time",
        "embedded_data_last_response_date",
        "embedded_data_login_date",
        "ext_ref",
        "first_name",
        "language_",
        "last_modified",
        "last_name",
        "phone",
        "write_blanks"
    FROM "memory"."main"."directory_contact"
),

"directory_contact_projected_missing_handled" AS (
    -- Handling missing values: There are 10 columns with unacceptable missing values
    -- directory_unsubscribe_date has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- embedded_data_last_active_time has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- embedded_data_last_response_date has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- embedded_data_login_date has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- ext_ref has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- first_name has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- language_ has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- last_name has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- phone has 80.0 percent missing. Strategy: 🔄 Unchanged
    -- write_blanks has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "directory_id",
        "id",
        "creation_date",
        "directory_unsubscribed",
        "email",
        "email_domain",
        "first_name",
        "last_modified",
        "last_name",
        "phone"
    FROM "directory_contact_projected"
)

-- COCOON BLOCK END
SELECT * FROM "directory_contact_projected_missing_handled"

version: 2
models:
- name: stg_directory_contact
  description: Please summarize the table
  columns:
  - name: directory_id
    description: directory_id
    tests:
    - not_null
  - name: id
    description: id
    tests:
    - not_null
  - name: creation_date
    description: creation_date
    tests:
    - not_null
  - name: directory_unsubscribed
    description: directory_unsubscribed
    tests:
    - not_null
  - name: email
    description: email
    tests:
    - not_null
  - name: email_domain
    description: email_domain
    tests:
    - not_null
  - name: first_name
    description: first_name
    tests:
    - not_null
  - name: last_modified
    description: last_modified
    tests:
    - not_null
  - name: last_name
    description: last_name
    tests:
    - not_null
  - name: phone
    description: phone
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:41:53.401328+00:00
WITH 
"contact_mailing_list_membership_projected" AS (
    -- Projection: Selecting 8 out of 9 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "contact_lookup_id",
        "contact_id",
        "directory_id",
        "mailing_list_id",
        "name",
        "owner_id",
        "unsubscribe_date",
        "unsubscribed"
    FROM "memory"."main"."contact_mailing_list_membership"
),

"contact_mailing_list_membership_projected_missing_handled" AS (
    -- Handling missing values: There are 1 columns with unacceptable missing values
    -- unsubscribe_date has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "contact_lookup_id",
        "contact_id",
        "directory_id",
        "mailing_list_id",
        "name",
        "owner_id",
        "unsubscribed"
    FROM "contact_mailing_list_membership_projected"
)

-- COCOON BLOCK END
SELECT * FROM "contact_mailing_list_membership_projected_missing_handled"

version: 2
models:
- name: stg_contact_mailing_list_membership
  description: Please summarize the table
  columns:
  - name: contact_lookup_id
    description: contact_lookup_id
    tests:
    - not_null
  - name: contact_id
    description: contact_id
    tests:
    - not_null
  - name: directory_id
    description: directory_id
    tests:
    - not_null
  - name: mailing_list_id
    description: mailing_list_id
    tests:
    - not_null
  - name: name
    description: name
    tests:
    - not_null
  - name: owner_id
    description: owner_id
    tests:
    - not_null
  - name: unsubscribed
    description: unsubscribed
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:56:59.737905+00:00
WITH 
"survey_projected" AS (
    -- Projection: Selecting 73 out of 74 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "_fivetran_deleted",
        "auto_scoring_category",
        "brand_base_url",
        "brand_id",
        "bundle_short_name",
        "composition_type",
        "creator_id",
        "default_scoring_category",
        "division_id",
        "is_active",
        "last_accessed",
        "last_activated",
        "last_modified",
        "option_active_response_set",
        "option_anonymize_response",
        "option_auto_confirm_start",
        "option_autoadvance",
        "option_autoadvance_pages",
        "option_autofocus",
        "option_available_languages",
        "option_back_button",
        "option_ballot_box_stuffing_prevention",
        "option_collect_geo_location",
        "option_confirm_start",
        "option_custom_styles",
        "option_email_thank_you",
        "option_eosredirect_url",
        "option_highlight_questions",
        "option_inactive_survey",
        "option_new_scoring",
        "option_next_button",
        "option_no_index",
        "option_page_transition",
        "option_partial_data",
        "option_partial_data_close_after",
        "option_password_protection",
        "option_previous_button",
        "option_progress_bar_display",
        "option_protect_selection_ids",
        "option_recaptcha_v_3",
        "option_referer_check",
        "option_referer_url",
        "option_relevant_id",
        "option_relevant_idlockout_period",
        "option_response_summary",
        "option_save_and_continue",
        "option_secure_response_files",
        "option_show_export_tags",
        "option_skin",
        "option_skin_library",
        "option_skin_question_width",
        "option_skin_type",
        "option_survey_creation_date",
        "option_survey_expiration",
        "option_survey_language",
        "option_survey_meta_description",
        "option_survey_name",
        "option_survey_protection",
        "option_survey_termination",
        "option_survey_title",
        "option_validate_message",
        "owner_id",
        "project_category",
        "project_type",
        "registry_sha",
        "registry_version",
        "schema_version",
        "scoring_summary_after_questions",
        "scoring_summary_after_survey",
        "scoring_summary_category",
        "survey_name",
        "survey_status"
    FROM "memory"."main"."survey"
),

"survey_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> survey_id
    -- _fivetran_deleted -> is_deleted
    -- option_active_response_set -> active_response_set
    -- option_anonymize_response -> anonymize_response
    -- option_auto_confirm_start -> auto_confirm_start
    -- option_autoadvance -> autoadvance
    -- option_autoadvance_pages -> autoadvance_pages
    -- option_autofocus -> autofocus
    -- option_available_languages -> available_languages
    -- option_back_button -> show_back_button
    -- option_ballot_box_stuffing_prevention -> prevent_ballot_stuffing
    -- option_collect_geo_location -> collect_geolocation
    -- option_confirm_start -> confirm_start
    -- option_custom_styles -> custom_styles
    -- option_email_thank_you -> send_thank_you_email
    -- option_eosredirect_url -> end_of_survey_redirect_url
    -- option_highlight_questions -> highlight_questions
    -- option_inactive_survey -> inactive_survey_behavior
    -- option_new_scoring -> new_scoring_enabled
    -- option_next_button -> next_button_text
    -- option_no_index -> no_index_enabled
    -- option_page_transition -> page_transition_effect
    -- option_partial_data -> partial_data_allowed
    -- option_partial_data_close_after -> partial_data_close_time
    -- option_password_protection -> password_protected
    -- option_previous_button -> previous_button_text
    -- option_progress_bar_display -> progress_bar_display
    -- option_protect_selection_ids -> protect_selection_ids
    -- option_recaptcha_v_3 -> recaptcha_v3_settings
    -- option_referer_check -> referer_check_enabled
    -- option_referer_url -> allowed_referer_url
    -- option_relevant_id -> survey_relevant_id
    -- option_relevant_idlockout_period -> relevant_id_lockout_period
    -- option_response_summary -> response_summary_enabled
    -- option_save_and_continue -> save_and_continue_enabled
    -- option_secure_response_files -> secure_response_files_enabled
    -- option_show_export_tags -> show_export_tags
    -- option_skin -> survey_skin_settings
    -- option_skin_library -> skin_library_name
    -- option_skin_question_width -> skin_question_width
    -- option_skin_type -> skin_type
    -- option_survey_creation_date -> survey_creation_date
    -- option_survey_expiration -> survey_expiration_date
    -- option_survey_language -> survey_language
    -- option_survey_meta_description -> survey_meta_description
    -- option_survey_protection -> survey_protection_type
    -- option_survey_termination -> survey_termination_message
    -- option_survey_title -> survey_title
    -- option_validate_message -> validation_message
    -- scoring_summary_after_questions -> show_question_scoring
    -- scoring_summary_after_survey -> show_survey_scoring
    -- scoring_summary_category -> scoring_category
    SELECT 
        "id" AS "survey_id",
        "_fivetran_deleted" AS "is_deleted",
        "auto_scoring_category",
        "brand_base_url",
        "brand_id",
        "bundle_short_name",
        "composition_type",
        "creator_id",
        "default_scoring_category",
        "division_id",
        "is_active",
        "last_accessed",
        "last_activated",
        "last_modified",
        "option_active_response_set" AS "active_response_set",
        "option_anonymize_response" AS "anonymize_response",
        "option_auto_confirm_start" AS "auto_confirm_start",
        "option_autoadvance" AS "autoadvance",
        "option_autoadvance_pages" AS "autoadvance_pages",
        "option_autofocus" AS "autofocus",
        "option_available_languages" AS "available_languages",
        "option_back_button" AS "show_back_button",
        "option_ballot_box_stuffing_prevention" AS "prevent_ballot_stuffing",
        "option_collect_geo_location" AS "collect_geolocation",
        "option_confirm_start" AS "confirm_start",
        "option_custom_styles" AS "custom_styles",
        "option_email_thank_you" AS "send_thank_you_email",
        "option_eosredirect_url" AS "end_of_survey_redirect_url",
        "option_highlight_questions" AS "highlight_questions",
        "option_inactive_survey" AS "inactive_survey_behavior",
        "option_new_scoring" AS "new_scoring_enabled",
        "option_next_button" AS "next_button_text",
        "option_no_index" AS "no_index_enabled",
        "option_page_transition" AS "page_transition_effect",
        "option_partial_data" AS "partial_data_allowed",
        "option_partial_data_close_after" AS "partial_data_close_time",
        "option_password_protection" AS "password_protected",
        "option_previous_button" AS "previous_button_text",
        "option_progress_bar_display" AS "progress_bar_display",
        "option_protect_selection_ids" AS "protect_selection_ids",
        "option_recaptcha_v_3" AS "recaptcha_v3_settings",
        "option_referer_check" AS "referer_check_enabled",
        "option_referer_url" AS "allowed_referer_url",
        "option_relevant_id" AS "survey_relevant_id",
        "option_relevant_idlockout_period" AS "relevant_id_lockout_period",
        "option_response_summary" AS "response_summary_enabled",
        "option_save_and_continue" AS "save_and_continue_enabled",
        "option_secure_response_files" AS "secure_response_files_enabled",
        "option_show_export_tags" AS "show_export_tags",
        "option_skin" AS "survey_skin_settings",
        "option_skin_library" AS "skin_library_name",
        "option_skin_question_width" AS "skin_question_width",
        "option_skin_type" AS "skin_type",
        "option_survey_creation_date" AS "survey_creation_date",
        "option_survey_expiration" AS "survey_expiration_date",
        "option_survey_language" AS "survey_language",
        "option_survey_meta_description" AS "survey_meta_description",
        "option_survey_name",
        "option_survey_protection" AS "survey_protection_type",
        "option_survey_termination" AS "survey_termination_message",
        "option_survey_title" AS "survey_title",
        "option_validate_message" AS "validation_message",
        "owner_id",
        "project_category",
        "project_type",
        "registry_sha",
        "registry_version",
        "schema_version",
        "scoring_summary_after_questions" AS "show_question_scoring",
        "scoring_summary_after_survey" AS "show_survey_scoring",
        "scoring_summary_category" AS "scoring_category",
        "survey_name",
        "survey_status"
    FROM "survey_projected"
),

"survey_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- allowed_referer_url: from DECIMAL to VARCHAR
    -- anonymize_response: from DECIMAL to VARCHAR
    -- auto_confirm_start: from DECIMAL to VARCHAR
    -- auto_scoring_category: from DECIMAL to VARCHAR
    -- available_languages: from DECIMAL to VARCHAR
    -- bundle_short_name: from DECIMAL to VARCHAR
    -- collect_geolocation: from DECIMAL to VARCHAR
    -- composition_type: from DECIMAL to VARCHAR
    -- confirm_start: from DECIMAL to VARCHAR
    -- creator_id: from DECIMAL to VARCHAR
    -- custom_styles: from VARCHAR to JSON
    -- default_scoring_category: from DECIMAL to VARCHAR
    -- division_id: from DECIMAL to VARCHAR
    -- end_of_survey_redirect_url: from DECIMAL to VARCHAR
    -- inactive_survey_behavior: from DECIMAL to VARCHAR
    -- is_active: from DECIMAL to VARCHAR
    -- last_accessed: from DECIMAL to TIMESTAMP
    -- last_activated: from VARCHAR to TIMESTAMP
    -- last_modified: from VARCHAR to TIMESTAMP
    -- option_survey_name: from DECIMAL to VARCHAR
    -- page_transition_effect: from DECIMAL to VARCHAR
    -- partial_data_close_time: from DECIMAL to VARCHAR
    -- password_protected: from DECIMAL to VARCHAR
    -- progress_bar_display: from DECIMAL to VARCHAR
    -- protect_selection_ids: from DECIMAL to VARCHAR
    -- recaptcha_v3_settings: from DECIMAL to VARCHAR
    -- referer_check_enabled: from DECIMAL to VARCHAR
    -- registry_sha: from DECIMAL to VARCHAR
    -- registry_version: from DECIMAL to VARCHAR
    -- relevant_id_lockout_period: from DECIMAL to VARCHAR
    -- response_summary_enabled: from DECIMAL to VARCHAR
    -- schema_version: from DECIMAL to VARCHAR
    -- scoring_category: from DECIMAL to VARCHAR
    -- send_thank_you_email: from DECIMAL to VARCHAR
    -- show_export_tags: from DECIMAL to VARCHAR
    -- survey_creation_date: from VARCHAR to TIMESTAMP
    -- survey_expiration_date: from DECIMAL to TIMESTAMP
    -- survey_meta_description: from DECIMAL to VARCHAR
    -- survey_relevant_id: from DECIMAL to VARCHAR
    -- survey_skin_settings: from VARCHAR to JSON
    -- validation_message: from DECIMAL to VARCHAR
    SELECT
        "survey_id",
        "is_deleted",
        "brand_base_url",
        "brand_id",
        "active_response_set",
        "autoadvance",
        "autoadvance_pages",
        "autofocus",
        "show_back_button",
        "prevent_ballot_stuffing",
        "highlight_questions",
        "new_scoring_enabled",
        "next_button_text",
        "no_index_enabled",
        "partial_data_allowed",
        "previous_button_text",
        "save_and_continue_enabled",
        "secure_response_files_enabled",
        "skin_library_name",
        "skin_question_width",
        "skin_type",
        "survey_language",
        "survey_protection_type",
        "survey_termination_message",
        "survey_title",
        "owner_id",
        "project_category",
        "project_type",
        "show_question_scoring",
        "show_survey_scoring",
        "survey_name",
        "survey_status",
        CAST("allowed_referer_url" AS VARCHAR) AS "allowed_referer_url",
        CAST("anonymize_response" AS VARCHAR) AS "anonymize_response",
        CAST("auto_confirm_start" AS VARCHAR) AS "auto_confirm_start",
        CAST("auto_scoring_category" AS VARCHAR) AS "auto_scoring_category",
        CAST("available_languages" AS VARCHAR) AS "available_languages",
        CAST("bundle_short_name" AS VARCHAR) AS "bundle_short_name",
        CAST("collect_geolocation" AS VARCHAR) AS "collect_geolocation",
        CAST("composition_type" AS VARCHAR) AS "composition_type",
        CAST("confirm_start" AS VARCHAR) AS "confirm_start",
        CAST("creator_id" AS VARCHAR) AS "creator_id",
        CAST("custom_styles" AS JSON) AS "custom_styles",
        CAST("default_scoring_category" AS VARCHAR) AS "default_scoring_category",
        CAST("division_id" AS VARCHAR) AS "division_id",
        CAST("end_of_survey_redirect_url" AS VARCHAR) AS "end_of_survey_redirect_url",
        CAST("inactive_survey_behavior" AS VARCHAR) AS "inactive_survey_behavior",
        CAST("is_active" AS VARCHAR) AS "is_active",
        CAST("last_accessed" AS TIMESTAMP) AS "last_accessed",
        CAST("last_activated" AS TIMESTAMP) AS "last_activated",
        CAST("last_modified" AS TIMESTAMP) AS "last_modified",
        CAST("option_survey_name" AS VARCHAR) AS "option_survey_name",
        CAST("page_transition_effect" AS VARCHAR) AS "page_transition_effect",
        CAST("partial_data_close_time" AS VARCHAR) AS "partial_data_close_time",
        CAST("password_protected" AS VARCHAR) AS "password_protected",
        CAST("progress_bar_display" AS VARCHAR) AS "progress_bar_display",
        CAST("protect_selection_ids" AS VARCHAR) AS "protect_selection_ids",
        CAST("recaptcha_v3_settings" AS VARCHAR) AS "recaptcha_v3_settings",
        CAST("referer_check_enabled" AS VARCHAR) AS "referer_check_enabled",
        CAST("registry_sha" AS VARCHAR) AS "registry_sha",
        CAST("registry_version" AS VARCHAR) AS "registry_version",
        CAST("relevant_id_lockout_period" AS VARCHAR) AS "relevant_id_lockout_period",
        CAST("response_summary_enabled" AS VARCHAR) AS "response_summary_enabled",
        CAST("schema_version" AS VARCHAR) AS "schema_version",
        CAST("scoring_category" AS VARCHAR) AS "scoring_category",
        CAST("send_thank_you_email" AS VARCHAR) AS "send_thank_you_email",
        CAST("show_export_tags" AS VARCHAR) AS "show_export_tags",
        CAST("survey_creation_date" AS TIMESTAMP) AS "survey_creation_date",
        CAST("survey_expiration_date" AS TIMESTAMP) AS "survey_expiration_date",
        CAST("survey_meta_description" AS VARCHAR) AS "survey_meta_description",
        CAST("survey_relevant_id" AS VARCHAR) AS "survey_relevant_id",
        CAST("survey_skin_settings" AS JSON) AS "survey_skin_settings",
        CAST("validation_message" AS VARCHAR) AS "validation_message"
    FROM "survey_projected_renamed"
),

"survey_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 39 columns with unacceptable missing values
    -- allowed_referer_url has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- anonymize_response has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- auto_confirm_start has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- auto_scoring_category has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- available_languages has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- bundle_short_name has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- collect_geolocation has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- composition_type has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- confirm_start has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- creator_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- custom_styles has 80.0 percent missing. Strategy: 🔄 Unchanged
    -- default_scoring_category has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- division_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- end_of_survey_redirect_url has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- inactive_survey_behavior has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- is_active has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- last_accessed has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- last_activated has 80.0 percent missing. Strategy: 🔄 Unchanged
    -- option_survey_name has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- page_transition_effect has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- partial_data_close_time has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- password_protected has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- progress_bar_display has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- protect_selection_ids has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- recaptcha_v3_settings has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- referer_check_enabled has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- registry_sha has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- registry_version has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- relevant_id_lockout_period has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- response_summary_enabled has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- schema_version has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- scoring_category has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- send_thank_you_email has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- show_export_tags has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- skin_question_width has 80.0 percent missing. Strategy: 🔄 Unchanged
    -- survey_meta_description has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- survey_relevant_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- survey_title has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- validation_message has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "survey_id",
        "is_deleted",
        "brand_base_url",
        "brand_id",
        "active_response_set",
        "autoadvance",
        "autoadvance_pages",
        "autofocus",
        "show_back_button",
        "prevent_ballot_stuffing",
        "highlight_questions",
        "new_scoring_enabled",
        "next_button_text",
        "no_index_enabled",
        "partial_data_allowed",
        "previous_button_text",
        "save_and_continue_enabled",
        "secure_response_files_enabled",
        "skin_library_name",
        "skin_question_width",
        "skin_type",
        "survey_language",
        "survey_protection_type",
        "survey_termination_message",
        "survey_title",
        "owner_id",
        "project_category",
        "project_type",
        "show_question_scoring",
        "show_survey_scoring",
        "survey_name",
        "survey_status",
        "custom_styles",
        "last_activated",
        "last_modified",
        "survey_creation_date",
        "survey_expiration_date",
        "survey_skin_settings"
    FROM "survey_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "survey_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_survey
  description: The table is about surveys. Each row represents a single survey with
    various configuration options. It includes details like survey ID, brand information,
    creator ID, response settings, scoring options, language preferences, and survey
    status. The table also contains metadata such as creation date, last modified
    date, and various customization options for the survey's appearance and behavior.
  columns:
  - name: survey_id
    description: Unique identifier for the survey
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each survey. For
        this table, each row represents a unique survey, and the survey_id appears
        to be a unique string for each row. This column is likely to be unique across
        all rows and can serve as a primary key for the table.
  - name: is_deleted
    description: Indicates if the record was deleted
    tests:
    - not_null
  - name: brand_base_url
    description: Base URL for the brand's Qualtrics instance
    tests:
    - not_null
  - name: brand_id
    description: Unique identifier for the brand
    tests:
    - not_null
  - name: active_response_set
    description: Active response set option
    tests:
    - not_null
  - name: autoadvance
    description: Option to automatically advance questions
    cocoon_meta:
      missing_acceptable: Feature may not be enabled for all surveys
  - name: autoadvance_pages
    description: Option to automatically advance pages
    cocoon_meta:
      missing_acceptable: Feature may not be enabled for all surveys
  - name: autofocus
    description: Option to automatically focus on first question
    cocoon_meta:
      missing_acceptable: Feature may not be enabled for all surveys
  - name: show_back_button
    description: Option to show back button
    tests:
    - not_null
  - name: prevent_ballot_stuffing
    description: Option to prevent ballot box stuffing
    tests:
    - not_null
  - name: highlight_questions
    description: Option to highlight questions
    tests:
    - accepted_values:
        values:
        - 'off'
        - 'on'
    cocoon_meta:
      missing_acceptable: Feature may not be enabled for all surveys
  - name: new_scoring_enabled
    description: New scoring system enabled
    tests:
    - not_null
  - name: next_button_text
    description: Text or symbol for next button
    tests:
    - not_null
    - accepted_values:
        values:
        - "\u2192"
        - '>'
        - '>>'
        - "\u23F5"
        - "\u21D2"
        - "\u2794"
        - "\u279C"
        - "\u25B6"
        - Next
        - Forward
        - Continue
        - Proceed
        - Advance
        - " \u2192 "
  - name: no_index_enabled
    description: Prevent search engine indexing
    tests:
    - not_null
    - accepted_values:
        values:
        - 'Yes'
        - 'No'
  - name: partial_data_allowed
    description: Allow partial data submission
    tests:
    - not_null
  - name: previous_button_text
    description: Text or symbol for previous button
    tests:
    - not_null
    - accepted_values:
        values:
        - "\u2190"
        - "\u25C4"
        - "\xAB"
        - "\u27E8"
        - "\u2B9C"
        - "\u2BC7"
        - Previous
        - Prev
        - Back
        - <
        - '<<'
        - "\u276E"
        - "\u23EA"
        - "\U0001F844"
        - "\U0001F878"
        - " \u2190 "
  - name: save_and_continue_enabled
    description: Save and continue feature enabled
    tests:
    - not_null
  - name: secure_response_files_enabled
    description: Secure response files enabled
    tests:
    - not_null
  - name: skin_library_name
    description: Skin library name
    tests:
    - not_null
  - name: skin_question_width
    description: Width of question in skin
    tests:
    - not_null
  - name: skin_type
    description: Type of skin used
    tests:
    - not_null
    - accepted_values:
        values:
        - MQ
        - Bootstrap
        - Foundation
        - Bulma
        - Tailwind
        - Material
        - Semantic UI
        - UIkit
        - Skeleton
        - Pure
        - Milligram
        - Materialize
        - Primer
        - Spectre
        - Picnic
        - Chota
        - Water.css
        - Sakura
        - MVP.css
        - Mini.css
        - templated
  - name: survey_language
    description: Survey language
    tests:
    - not_null
    - accepted_values:
        values:
        - EN
        - ES
        - FR
        - DE
        - IT
        - PT
        - RU
        - ZH
        - JA
        - KO
        - AR
        - HI
        - BN
        - PA
        - TE
        - MR
        - TA
        - UR
        - GU
        - KN
  - name: survey_protection_type
    description: Survey protection type
    tests:
    - not_null
    - accepted_values:
        values:
        - PublicSurvey
        - PasswordProtected
        - InvitationOnly
        - IPRestricted
        - TimeRestricted
        - GeographicallyRestricted
        - SingleResponse
        - AnonymousResponses
        - EncryptedResponses
        - CaptchaProtected
  - name: survey_termination_message
    description: Survey termination message
    tests:
    - not_null
  - name: survey_title
    description: Title displayed for the survey
    tests:
    - not_null
  - name: owner_id
    description: Unique identifier of the survey owner
    tests:
    - not_null
  - name: project_category
    description: Category of the survey project
    tests:
    - not_null
    - accepted_values:
        values:
        - CX
        - CORE
        - NPS
        - CSAT
        - Employee Experience
        - Market Research
        - Product Feedback
        - Brand Awareness
        - Customer Loyalty
        - Voice of Customer
        - User Experience
        - Website Feedback
        - Event Feedback
        - Business-to-Business
        - Academic Research
  - name: project_type
    description: Type of the survey project
    tests:
    - accepted_values:
        values:
        - CustomerSatisfactionScore
        - NetPromoterScore
        - ProductFeedback
        - EmployeeSatisfaction
        - MarketResearch
        - BrandAwareness
        - EventFeedback
        - WebsiteUsability
        - CourseEvaluation
        - PatientExperience
        - PoliticalPolling
        - SocialMediaSentiment
        - ConceptTesting
        - CustomerNeeds
        - CompetitiveAnalysis
        - UserExperience
        - ServiceQuality
        - PricingSensitivity
        - AdvertisingEffectiveness
        - DemographicStudy
    cocoon_meta:
      missing_acceptable: Some surveys might not belong to a specific project type
  - name: show_question_scoring
    description: Display scoring summary after each question
    tests:
    - not_null
  - name: show_survey_scoring
    description: Display scoring summary after survey completion
    tests:
    - not_null
  - name: survey_name
    description: Name of the survey
    tests:
    - not_null
  - name: survey_status
    description: Current status of the survey
    tests:
    - not_null
    - accepted_values:
        values:
        - Inactive
        - Active
  - name: custom_styles
    description: Custom CSS styles for the survey
    tests:
    - not_null
  - name: last_activated
    description: Timestamp of last survey activation
    tests:
    - not_null
  - name: last_modified
    description: Timestamp of last survey modification
    tests:
    - not_null
  - name: survey_creation_date
    description: Survey creation date
    tests:
    - not_null
  - name: survey_expiration_date
    description: Survey expiration date
    cocoon_meta:
      missing_acceptable: Surveys without expiration dates don't need this field.
  - name: survey_skin_settings
    description: Survey skin or theme settings
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:57:52.211204+00:00
WITH 
"survey_embedded_data_projected" AS (
    -- Projection: Selecting 4 out of 5 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "import_id",
        "key_",
        "response_id",
        "value_"
    FROM "memory"."main"."survey_embedded_data"
),

"survey_embedded_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- import_id -> data_category
    -- key_ -> data_key
    -- value_ -> data_value
    SELECT 
        "import_id" AS "data_category",
        "key_" AS "data_key",
        "response_id",
        "value_" AS "data_value"
    FROM "survey_embedded_data_projected"
),

"survey_embedded_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- data_value: from DECIMAL to VARCHAR
    SELECT
        "data_category",
        "data_key",
        "response_id",
        CAST("data_value" AS VARCHAR) AS "data_value"
    FROM "survey_embedded_data_projected_renamed"
),

"survey_embedded_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 1 columns with unacceptable missing values
    -- data_value has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "data_category",
        "data_key",
        "response_id"
    FROM "survey_embedded_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "survey_embedded_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_survey_embedded_data
  description: The table is about survey embedded data. It contains information related
    to survey responses. The table has columns for import_id, key_, response_id, and
    value_. The import_id and key_ seem to represent categories like "Website" and
    "country". The response_id is a unique identifier for each survey response. The
    value_ column appears to be empty in the given samples.
  columns:
  - name: data_category
    description: Category or type of embedded data
    tests:
    - not_null
  - name: data_key
    description: Specific identifier for the embedded data type
    tests:
    - not_null
  - name: response_id
    description: Unique identifier for each survey response
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each survey response.
        For this table, each row appears to be a single piece of embedded data for
        a survey response. The response_id is likely to be unique across rows for
        each unique survey response, even though it may appear multiple times for
        different data categories of the same response.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 23:00:30.364905+00:00
WITH 
"survey_version_projected" AS (
    -- Projection: Selecting 9 out of 10 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "survey_id",
        "_fivetran_deleted",
        "creation_date",
        "description",
        "published",
        "user_id",
        "version_number",
        "was_published"
    FROM "memory"."main"."survey_version"
),

"survey_version_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> version_id
    -- _fivetran_deleted -> is_deleted
    -- published -> is_published
    -- was_published -> was_ever_published
    SELECT 
        "id" AS "version_id",
        "survey_id",
        "_fivetran_deleted" AS "is_deleted",
        "creation_date",
        "description",
        "published" AS "is_published",
        "user_id",
        "version_number",
        "was_published" AS "was_ever_published"
    FROM "survey_version_projected"
),

"survey_version_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- creation_date: from VARCHAR to TIMESTAMP
    -- version_id: from INT to VARCHAR
    SELECT
        "survey_id",
        "is_deleted",
        "description",
        "is_published",
        "user_id",
        "version_number",
        "was_ever_published",
        CAST("creation_date" AS TIMESTAMP) AS "creation_date",
        CAST("version_id" AS VARCHAR) AS "version_id"
    FROM "survey_version_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "survey_version_projected_renamed_casted"

version: 2
models:
- name: stg_survey_version
  description: The table is about survey versions. It contains details like survey
    ID, creation date, description, publication status, user ID, and version number.
    Each row represents a specific version of a survey. The table tracks whether a
    version was published or autosaved. It also includes a flag for deleted records
    and a unique identifier for each version.
  columns:
  - name: survey_id
    description: Unique identifier for the survey
    tests:
    - not_null
  - name: is_deleted
    description: Flag indicating if the record was deleted
    tests:
    - not_null
  - name: description
    description: Brief description of the survey version
    tests:
    - not_null
  - name: is_published
    description: Flag indicating if the version is currently published
    tests:
    - not_null
  - name: user_id
    description: Unique identifier of the user who created the version
    tests:
    - not_null
  - name: version_number
    description: Sequential number of the survey version
    tests:
    - not_null
  - name: was_ever_published
    description: Flag indicating if the version was ever published
    tests:
    - not_null
  - name: creation_date
    description: Date and time when the survey version was created
    tests:
    - not_null
  - name: version_id
    description: Unique identifier for the survey version
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is described as a unique identifier for the survey version.
        For this table, each row represents a specific version of a survey. Given
        that it's explicitly stated as a unique identifier, it should be unique across
        all rows.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:48:57.242024+00:00
WITH 
"question_projected" AS (
    -- Projection: Selecting 18 out of 19 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "survey_id",
        "_fivetran_deleted",
        "data_export_tag",
        "data_visibility_hidden",
        "data_visibility_private",
        "next_answer_id",
        "next_choice_id",
        "question_description",
        "question_description_option",
        "question_text",
        "question_text_unsafe",
        "question_type",
        "selector",
        "sub_selector",
        "validation_setting_force_response",
        "validation_setting_force_response_type",
        "validation_setting_type"
    FROM "memory"."main"."question"
),

"question_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> question_id
    -- _fivetran_deleted -> is_deleted
    -- data_export_tag -> export_tag
    -- data_visibility_hidden -> is_hidden
    -- data_visibility_private -> is_private
    -- question_description_option -> description_display_option
    -- question_text_unsafe -> raw_question_text
    -- selector -> selector_type
    -- sub_selector -> sub_selector_type
    -- validation_setting_force_response -> force_response
    -- validation_setting_force_response_type -> force_response_type
    -- validation_setting_type -> validation_type
    SELECT 
        "id" AS "question_id",
        "survey_id",
        "_fivetran_deleted" AS "is_deleted",
        "data_export_tag" AS "export_tag",
        "data_visibility_hidden" AS "is_hidden",
        "data_visibility_private" AS "is_private",
        "next_answer_id",
        "next_choice_id",
        "question_description",
        "question_description_option" AS "description_display_option",
        "question_text",
        "question_text_unsafe" AS "raw_question_text",
        "question_type",
        "selector" AS "selector_type",
        "sub_selector" AS "sub_selector_type",
        "validation_setting_force_response" AS "force_response",
        "validation_setting_force_response_type" AS "force_response_type",
        "validation_setting_type" AS "validation_type"
    FROM "question_projected"
),

"question_projected_renamed_trimmed" AS (
    -- Trim Leading and Trailing Spaces
    SELECT
        "question_id",
        "survey_id",
        "is_deleted",
        "export_tag",
        "is_hidden",
        "is_private",
        "next_answer_id",
        "next_choice_id",
        "description_display_option",
        "question_text",
        "raw_question_text",
        "question_type",
        "selector_type",
        "sub_selector_type",
        "force_response",
        "force_response_type",
        "validation_type",
        TRIM("question_description") AS "question_description"
    FROM "question_projected_renamed"
),

"question_projected_renamed_trimmed_cleaned" AS (
    -- Clean unusual string values: 
    -- question_text: The problem is that 'Click to write the question text' is a placeholder value that should be replaced with actual content. 'Tell About experience ? ' has typos, improper capitalization, and an HTML entity that should be removed. 'Marks' appears to be the only valid entry. The correct values should be consistent and meaningful question text. 
    -- raw_question_text: The problem is that this column contains some unusual values. "Click to write the question text" is likely a placeholder and not an actual question. "Tell About experience ? " contains an HTML entity ( ) which should be removed. "Marks" seems to be the only valid value. The correct values should be either "Marks" or an empty string for invalid entries. 
    -- question_description: The problem is that 'Click to write the question text' is a placeholder value that should not appear in actual data, and 'Tell About experience ?' has grammatical errors and inconsistent capitalization. The correct values should be either 'Marks' (which appears to be a valid category) or a properly formatted question about experience. 
    SELECT
        "question_id",
        "survey_id",
        "is_deleted",
        "export_tag",
        "is_hidden",
        "is_private",
        "next_answer_id",
        "next_choice_id",
        "description_display_option",
        CASE
            WHEN "question_text" = '''Click to write the question text''' THEN ''''
            WHEN "question_text" = '''Tell About experience ? ''' THEN '''Tell about experience?'''
            ELSE "question_text"
        END AS "question_text",
        CASE
            WHEN "raw_question_text" = '''Click to write the question text''' THEN ''''
            WHEN "raw_question_text" = '''Tell About experience ? ''' THEN '''Tell About experience ?'''
            ELSE "raw_question_text"
        END AS "raw_question_text",
        "question_type",
        "selector_type",
        "sub_selector_type",
        "force_response",
        "force_response_type",
        "validation_type",
        CASE
            WHEN "question_description" = '''Click to write the question text''' THEN ''''
            WHEN "question_description" = '''Tell About experience ?''' THEN '''Tell about experience?'''
            ELSE "question_description"
        END AS "question_description"
    FROM "question_projected_renamed_trimmed"
),

"question_projected_renamed_trimmed_cleaned_null" AS (
    -- NULL Imputation: Impute Null to Disguised Missing Values
    -- question_text: ['Click to write the question text']
    -- raw_question_text: ['Click to write the question text']
    -- question_description: ['Click to write the question text']
    SELECT 
        CASE
            WHEN "question_text" = 'Click to write the question text' THEN NULL
            ELSE "question_text"
        END AS "question_text",
        CASE
            WHEN "raw_question_text" = 'Click to write the question text' THEN NULL
            ELSE "raw_question_text"
        END AS "raw_question_text",
        CASE
            WHEN "question_description" = 'Click to write the question text' THEN NULL
            ELSE "question_description"
        END AS "question_description",
        "question_id",
        "description_display_option",
        "question_type",
        "is_hidden",
        "is_deleted",
        "next_choice_id",
        "selector_type",
        "is_private",
        "force_response",
        "export_tag",
        "next_answer_id",
        "survey_id",
        "sub_selector_type",
        "force_response_type",
        "validation_type"
    FROM "question_projected_renamed_trimmed_cleaned"
),

"question_projected_renamed_trimmed_cleaned_null_casted" AS (
    -- Column Type Casting: 
    -- force_response: from DECIMAL to VARCHAR
    -- force_response_type: from DECIMAL to VARCHAR
    -- is_hidden: from BOOLEAN to VARCHAR
    -- is_private: from BOOLEAN to VARCHAR
    -- validation_type: from DECIMAL to VARCHAR
    SELECT
        "question_text",
        "raw_question_text",
        "question_description",
        "question_id",
        "description_display_option",
        "question_type",
        "is_deleted",
        "next_choice_id",
        "selector_type",
        "export_tag",
        "next_answer_id",
        "survey_id",
        "sub_selector_type",
        CAST("force_response" AS VARCHAR) AS "force_response",
        CAST("force_response_type" AS VARCHAR) AS "force_response_type",
        CAST("is_hidden" AS VARCHAR) AS "is_hidden",
        CAST("is_private" AS VARCHAR) AS "is_private",
        CAST("validation_type" AS VARCHAR) AS "validation_type"
    FROM "question_projected_renamed_trimmed_cleaned_null"
),

"question_projected_renamed_trimmed_cleaned_null_casted_missing_handled" AS (
    -- Handling missing values: There are 8 columns with unacceptable missing values
    -- force_response has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- force_response_type has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- is_hidden has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- is_private has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- question_description has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- question_text has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- raw_question_text has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- validation_type has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "question_text",
        "raw_question_text",
        "question_description",
        "question_id",
        "description_display_option",
        "question_type",
        "is_deleted",
        "next_choice_id",
        "selector_type",
        "export_tag",
        "next_answer_id",
        "survey_id",
        "sub_selector_type",
        "is_hidden",
        "is_private"
    FROM "question_projected_renamed_trimmed_cleaned_null_casted"
)

-- COCOON BLOCK END
SELECT * FROM "question_projected_renamed_trimmed_cleaned_null_casted_missing_handled"

version: 2
models:
- name: stg_question
  description: The table is about survey questions. It contains details like question
    ID, survey ID, question text, question type, and various settings. Each row represents
    a unique question within a survey. The table includes fields for visibility, data
    export tags, validation settings, and selector types. This structure allows for
    flexible question configuration across different surveys.
  columns:
  - name: question_text
    description: The text of the question
    tests:
    - not_null
    - accepted_values:
        values:
        - A+
        - A
        - A-
        - B+
        - B
        - B-
        - C+
        - C
        - C-
        - D+
        - D
        - D-
        - F
        - Pass
        - Fail
        - Incomplete
        - Withdrawn
        - '1'
        - '2'
        - '3'
        - '4'
        - '5'
        - Marks
        - Tell About experience ? 
  - name: raw_question_text
    description: Unprocessed question text
    tests:
    - not_null
    - accepted_values:
        values:
        - A+
        - A
        - A-
        - B+
        - B
        - B-
        - C+
        - C
        - C-
        - D+
        - D
        - D-
        - F
        - 100%
        - 90-99%
        - 80-89%
        - 70-79%
        - 60-69%
        - 50-59%
        - 0-49%
        - Pass
        - Fail
        - Incomplete
        - Withdrawn
        - Marks
        - Tell About experience ? 
  - name: question_description
    description: Description of the question
    tests:
    - not_null
  - name: question_id
    description: Unique identifier for the question
    tests:
    - not_null
  - name: description_display_option
    description: Option for question description display
    tests:
    - not_null
    - accepted_values:
        values:
        - UseText
        - UseHTML
        - NoDisplay
        - UseMarkdown
  - name: question_type
    description: Type of question (e.g., CS, DB)
    tests:
    - not_null
    - accepted_values:
        values:
        - CS
        - DB
        - Math
        - Physics
        - Chemistry
        - Biology
        - History
        - Geography
        - Literature
        - Language
        - Economics
        - Psychology
        - Sociology
        - Philosophy
        - Art
        - Music
        - Political Science
        - Engineering
        - Environmental Science
        - Anthropology
  - name: is_deleted
    description: Indicates if the record is deleted
    tests:
    - not_null
  - name: next_choice_id
    description: ID for the next choice
    tests:
    - not_null
  - name: selector_type
    description: Selector type for the question
    tests:
    - not_null
  - name: export_tag
    description: Tag for data export purposes
    tests:
    - not_null
  - name: next_answer_id
    description: ID for the next answer
    tests:
    - not_null
  - name: survey_id
    description: Unique identifier for the survey
    tests:
    - not_null
  - name: sub_selector_type
    description: Sub-selector type for the question
    cocoon_meta:
      missing_acceptable: Not applicable for question types without sub-selectors.
  - name: is_hidden
    description: Indicates if the question is hidden
    tests:
    - not_null
    - accepted_values:
        values:
        - 'True'
        - 'False'
        - 'false'
  - name: is_private
    description: Indicates if the question is private
    tests:
    - not_null
    - accepted_values:
        values:
        - 'True'
        - 'False'
        - 'false'

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:41:57.026219+00:00
WITH 
"core_mailing_list_projected" AS (
    -- Projection: Selecting 6 out of 7 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "library_id",
        "name",
        "category",
        "folder",
        "_fivetran_deleted"
    FROM "memory"."main"."core_mailing_list"
)

-- COCOON BLOCK END
SELECT * FROM "core_mailing_list_projected"

version: 2
models:
- name: stg_core_mailing_list
  description: Please summarize the table
  columns:
  - name: id
    description: id
    tests:
    - not_null
  - name: library_id
    description: library_id
    tests:
    - not_null
  - name: name
    description: name
    tests:
    - not_null
  - name: category
    description: category
    tests:
    - not_null
  - name: folder
    description: folder
    tests:
    - not_null
  - name: _fivetran_deleted
    description: _fivetran_deleted
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:50:48.307661+00:00
WITH 
"question_response_projected" AS (
    -- Projection: Selecting 9 out of 10 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "_fivetran_id",
        "loop_id",
        "question",
        "question_id",
        "question_option_key",
        "response_id",
        "sub_question_key",
        "sub_question_text",
        "value_"
    FROM "memory"."main"."question_response"
),

"question_response_projected_renamed" AS (
    -- Rename: Renaming columns
    -- _fivetran_id -> record_id
    -- question -> question_topic
    -- question_option_key -> option_key
    -- value_ -> response_value
    SELECT 
        "_fivetran_id" AS "record_id",
        "loop_id",
        "question" AS "question_topic",
        "question_id",
        "question_option_key" AS "option_key",
        "response_id",
        "sub_question_key",
        "sub_question_text",
        "value_" AS "response_value"
    FROM "question_response_projected"
),

"question_response_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- loop_id: from DECIMAL to VARCHAR
    -- option_key: from DECIMAL to VARCHAR
    -- sub_question_key: from DECIMAL to VARCHAR
    -- sub_question_text: from DECIMAL to VARCHAR
    SELECT
        "record_id",
        "question_topic",
        "question_id",
        "response_id",
        "response_value",
        CAST("loop_id" AS VARCHAR) AS "loop_id",
        CAST("option_key" AS VARCHAR) AS "option_key",
        CAST("sub_question_key" AS VARCHAR) AS "sub_question_key",
        CAST("sub_question_text" AS VARCHAR) AS "sub_question_text"
    FROM "question_response_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "question_response_projected_renamed_casted"

version: 2
models:
- name: stg_question_response
  description: The table is about survey question responses. It contains details like
    question ID, response ID, and response value. Each row represents a response to
    a specific question. The "question" column shows the question topic, while "value_"
    contains the numerical response. Some questions may have sub-questions or options,
    as indicated by additional columns. The table appears to track responses across
    different survey questions.
  columns:
  - name: record_id
    description: Unique identifier for each record
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column appears to be a unique identifier for each record in
        the survey. For this table, each row represents a response to a specific question.
        The record_id seems to be a hashed value that is unique across rows.
  - name: question_topic
    description: Topic or subject of the survey question
    tests:
    - not_null
  - name: question_id
    description: Unique identifier for each question
    tests:
    - not_null
  - name: response_id
    description: Unique identifier for each response
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column appears to be a unique identifier for each response
        in the survey. For this table, each row represents a response to a specific
        question. The response_id seems to be a unique identifier across rows.
  - name: response_value
    description: Numerical value of the response
    tests:
    - not_null
  - name: loop_id
    description: Identifier for looped questions (currently empty)
    cocoon_meta:
      missing_acceptable: Not applicable for non-looped questions.
  - name: option_key
    description: Key for question options (currently empty)
    cocoon_meta:
      missing_acceptable: Not applicable for non-multiple choice questions.
  - name: sub_question_key
    description: Key for sub-questions (currently empty)
    cocoon_meta:
      missing_acceptable: Not applicable for main questions without sub-questions.
  - name: sub_question_text
    description: Text of sub-questions (currently empty)
    cocoon_meta:
      missing_acceptable: Not applicable for main questions without sub-questions.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:46:51.239370+00:00
WITH 
"distribution_contact_projected" AS (
    -- Projection: Selecting 12 out of 13 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "contact_id",
        "distribution_id",
        "contact_frequency_rule_id",
        "contact_lookup_id",
        "opened_at",
        "response_completed_at",
        "response_id",
        "response_started_at",
        "sent_at",
        "status",
        "survey_link",
        "survey_session_id"
    FROM "memory"."main"."distribution_contact"
),

"distribution_contact_projected_renamed" AS (
    -- Rename: Renaming columns
    -- opened_at -> survey_opened_at
    -- sent_at -> survey_sent_at
    -- status -> survey_status
    SELECT 
        "contact_id",
        "distribution_id",
        "contact_frequency_rule_id",
        "contact_lookup_id",
        "opened_at" AS "survey_opened_at",
        "response_completed_at",
        "response_id",
        "response_started_at",
        "sent_at" AS "survey_sent_at",
        "status" AS "survey_status",
        "survey_link",
        "survey_session_id"
    FROM "distribution_contact_projected"
),

"distribution_contact_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- contact_frequency_rule_id: from DECIMAL to VARCHAR
    -- response_completed_at: from DECIMAL to TIMESTAMP
    -- response_id: from DECIMAL to VARCHAR
    -- response_started_at: from DECIMAL to TIMESTAMP
    -- survey_opened_at: from VARCHAR to TIMESTAMP
    -- survey_sent_at: from VARCHAR to TIMESTAMP
    -- survey_session_id: from DECIMAL to VARCHAR
    SELECT
        "contact_id",
        "distribution_id",
        "contact_lookup_id",
        "survey_status",
        "survey_link",
        CAST("contact_frequency_rule_id" AS VARCHAR) AS "contact_frequency_rule_id",
        CAST("response_completed_at" AS TIMESTAMP) AS "response_completed_at",
        CAST("response_id" AS VARCHAR) AS "response_id",
        CAST("response_started_at" AS TIMESTAMP) AS "response_started_at",
        CAST("survey_opened_at" AS TIMESTAMP) AS "survey_opened_at",
        CAST("survey_sent_at" AS TIMESTAMP) AS "survey_sent_at",
        CAST("survey_session_id" AS VARCHAR) AS "survey_session_id"
    FROM "distribution_contact_projected_renamed"
),

"distribution_contact_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 3 columns with unacceptable missing values
    -- contact_frequency_rule_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- survey_opened_at has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- survey_session_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "contact_id",
        "distribution_id",
        "contact_lookup_id",
        "survey_status",
        "survey_link",
        "response_completed_at",
        "response_id",
        "response_started_at",
        "survey_opened_at",
        "survey_sent_at"
    FROM "distribution_contact_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "distribution_contact_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_distribution_contact
  description: The table shows the distribution of surveys to contacts. It includes
    contact IDs, distribution IDs, and contact lookup IDs. It tracks survey status
    (e.g., Opened, Pending), sent and opened timestamps, and survey links. The table
    relates contacts to specific survey distributions and captures response data,
    including start and completion times. It represents the lifecycle of survey distribution
    and response tracking.
  columns:
  - name: contact_id
    description: Unique identifier for the contact
    tests:
    - not_null
  - name: distribution_id
    description: Unique identifier for the survey distribution
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each survey distribution.
        For this table, each row represents a specific survey distribution to a contact.
        The distribution_id appears to be unique across rows, as it's designed to
        identify each unique distribution.
  - name: contact_lookup_id
    description: Lookup identifier for the contact
    tests:
    - not_null
  - name: survey_status
    description: Current status of the survey
    tests:
    - not_null
    - accepted_values:
        values:
        - Opened
        - Pending
        - Closed
        - Completed
        - Canceled
        - Archived
  - name: survey_link
    description: URL link to the survey
    tests:
    - not_null
  - name: response_completed_at
    description: Timestamp when the survey response was completed
    cocoon_meta:
      missing_acceptable: Not applicable for surveys not yet completed.
  - name: response_id
    description: Unique identifier for the survey response
    cocoon_meta:
      missing_acceptable: Not applicable for surveys not yet responded to.
  - name: response_started_at
    description: Timestamp when the survey response was started
    cocoon_meta:
      missing_acceptable: Not applicable for surveys not yet started.
  - name: survey_opened_at
    description: Timestamp when the survey was opened
    tests:
    - not_null
  - name: survey_sent_at
    description: Timestamp when the survey was sent
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:41:50.303242+00:00
WITH 
"block_projected" AS (
    -- Projection: Selecting 8 out of 9 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "survey_id",
        "_fivetran_deleted",
        "block_locking",
        "block_visibility",
        "description",
        "randomize_questions",
        "type"
    FROM "memory"."main"."block"
),

"block_projected_missing_handled" AS (
    -- Handling missing values: There are 3 columns with unacceptable missing values
    -- block_locking has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- block_visibility has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- randomize_questions has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "id",
        "survey_id",
        "_fivetran_deleted",
        "description",
        "type"
    FROM "block_projected"
)

-- COCOON BLOCK END
SELECT * FROM "block_projected_missing_handled"

version: 2
models:
- name: stg_block
  description: Please summarize the table
  columns:
  - name: id
    description: id
    tests:
    - not_null
  - name: survey_id
    description: survey_id
    tests:
    - not_null
  - name: _fivetran_deleted
    description: _fivetran_deleted
    tests:
    - not_null
  - name: description
    description: description
    tests:
    - not_null
  - name: type
    description: type
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:42:02.933253+00:00
WITH 
"directory_mailing_list_projected" AS (
    -- Projection: Selecting 7 out of 8 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "directory_id",
        "id",
        "_fivetran_deleted",
        "creation_date",
        "last_modified_date",
        "name",
        "owner_id"
    FROM "memory"."main"."directory_mailing_list"
)

-- COCOON BLOCK END
SELECT * FROM "directory_mailing_list_projected"

version: 2
models:
- name: stg_directory_mailing_list
  description: Please summarize the table
  columns:
  - name: directory_id
    description: directory_id
    tests:
    - not_null
  - name: id
    description: id
    tests:
    - not_null
  - name: _fivetran_deleted
    description: _fivetran_deleted
    tests:
    - not_null
  - name: creation_date
    description: creation_date
    tests:
    - not_null
  - name: last_modified_date
    description: last_modified_date
    tests:
    - not_null
  - name: name
    description: name
    tests:
    - not_null
  - name: owner_id
    description: owner_id
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:41:58.109139+00:00
WITH 
"directory_projected" AS (
    -- Projection: Selecting 9 out of 10 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "_fivetran_deleted",
        "deduplication_criteria_email",
        "deduplication_criteria_external_data_reference",
        "deduplication_criteria_first_name",
        "deduplication_criteria_last_name",
        "deduplication_criteria_phone",
        "is_default",
        "name"
    FROM "memory"."main"."directory"
)

-- COCOON BLOCK END
SELECT * FROM "directory_projected"

version: 2
models:
- name: stg_directory
  description: Please summarize the table
  columns:
  - name: id
    description: id
    tests:
    - not_null
  - name: _fivetran_deleted
    description: _fivetran_deleted
    tests:
    - not_null
  - name: deduplication_criteria_email
    description: deduplication_criteria_email
    tests:
    - not_null
  - name: deduplication_criteria_external_data_reference
    description: deduplication_criteria_external_data_reference
    tests:
    - not_null
  - name: deduplication_criteria_first_name
    description: deduplication_criteria_first_name
    tests:
    - not_null
  - name: deduplication_criteria_last_name
    description: deduplication_criteria_last_name
    tests:
    - not_null
  - name: deduplication_criteria_phone
    description: deduplication_criteria_phone
    tests:
    - not_null
  - name: is_default
    description: is_default
    tests:
    - not_null
  - name: name
    description: name
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:41:51.567164+00:00
WITH 
"block_question_projected" AS (
    -- Projection: Selecting 4 out of 5 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "block_id",
        "question_id",
        "survey_id",
        "_fivetran_deleted"
    FROM "memory"."main"."block_question"
)

-- COCOON BLOCK END
SELECT * FROM "block_question_projected"

version: 2
models:
- name: stg_block_question
  description: Please summarize the table
  columns:
  - name: block_id
    description: block_id
    tests:
    - not_null
  - name: question_id
    description: question_id
    tests:
    - not_null
  - name: survey_id
    description: survey_id
    tests:
    - not_null
  - name: _fivetran_deleted
    description: _fivetran_deleted
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:51:31.889107+00:00
WITH 
"sub_question_projected" AS (
    -- Projection: Selecting 6 out of 7 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "key_",
        "question_id",
        "survey_id",
        "_fivetran_deleted",
        "choice_data_export_tag",
        "text"
    FROM "memory"."main"."sub_question"
),

"sub_question_projected_renamed" AS (
    -- Rename: Renaming columns
    -- key_ -> answer_choice_id
    -- _fivetran_deleted -> is_deleted
    -- choice_data_export_tag -> export_tag
    -- text -> answer_text
    SELECT 
        "key_" AS "answer_choice_id",
        "question_id",
        "survey_id",
        "_fivetran_deleted" AS "is_deleted",
        "choice_data_export_tag" AS "export_tag",
        "text" AS "answer_text"
    FROM "sub_question_projected"
),

"sub_question_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- export_tag: from DECIMAL to VARCHAR
    SELECT
        "answer_choice_id",
        "question_id",
        "survey_id",
        "is_deleted",
        "answer_text",
        CAST("export_tag" AS VARCHAR) AS "export_tag"
    FROM "sub_question_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "sub_question_projected_renamed_casted"

version: 2
models:
- name: stg_sub_question
  description: The table represents a relation between survey questions and their
    possible answers or options. Each row contains a key, question ID, survey ID,
    and text representing an answer choice. The question_id links multiple options
    to a single question. The survey_id associates questions with a specific survey.
    This structure allows for organizing multiple choice questions within surveys.
  columns:
  - name: answer_choice_id
    description: Unique identifier for the answer choice
    tests:
    - not_null
  - name: question_id
    description: Identifier for the question
    tests:
    - not_null
  - name: survey_id
    description: Identifier for the survey
    tests:
    - not_null
  - name: is_deleted
    description: Indicates if the record is deleted
    tests:
    - not_null
  - name: answer_text
    description: The text of the answer choice
    tests:
    - not_null
    - accepted_values:
        values:
        - C
        - Java
        - Product
        - QA
        - Support
        - Python
        - JavaScript
        - DevOps
        - UI/UX
        - Data Science
        - Machine Learning
        - Mobile Development
        - Cloud Computing
        - Security
        - Database
        - Frontend
        - Backend
        - Full Stack
        - Project Management
        - Customer Success
        - Sales
        - Marketing
        - HR
        - Finance
        - Legal
  - name: export_tag
    description: Additional tag for data export purposes
    cocoon_meta:
      missing_acceptable: Optional tag, not required for all answer choices.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:45:16.233443+00:00
WITH 
"distribution_projected" AS (
    -- Projection: Selecting 24 out of 25 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "_fivetran_deleted",
        "created_date",
        "header_from_email",
        "header_from_name",
        "header_reply_to_email",
        "header_subject",
        "message_library_id",
        "message_message_id",
        "message_message_text",
        "modified_date",
        "organization_id",
        "owner_id",
        "parent_distribution_id",
        "recipient_contact_id",
        "recipient_library_id",
        "recipient_mailing_list_id",
        "recipient_sample_id",
        "request_status",
        "request_type",
        "send_date",
        "survey_link_expiration_date",
        "survey_link_link_type",
        "survey_link_survey_id"
    FROM "memory"."main"."distribution"
),

"distribution_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> distribution_id
    -- _fivetran_deleted -> is_deleted
    -- header_from_email -> sender_email
    -- header_from_name -> sender_name
    -- header_reply_to_email -> reply_to_email
    -- header_subject -> email_subject
    -- message_message_id -> message_id
    -- message_message_text -> message_text
    -- recipient_mailing_list_id -> mailing_list_id
    -- recipient_sample_id -> sample_id
    -- request_status -> distribution_status
    -- request_type -> distribution_type
    -- survey_link_expiration_date -> survey_expiration_date
    -- survey_link_link_type -> survey_link_type
    -- survey_link_survey_id -> survey_id
    SELECT 
        "id" AS "distribution_id",
        "_fivetran_deleted" AS "is_deleted",
        "created_date",
        "header_from_email" AS "sender_email",
        "header_from_name" AS "sender_name",
        "header_reply_to_email" AS "reply_to_email",
        "header_subject" AS "email_subject",
        "message_library_id",
        "message_message_id" AS "message_id",
        "message_message_text" AS "message_text",
        "modified_date",
        "organization_id",
        "owner_id",
        "parent_distribution_id",
        "recipient_contact_id",
        "recipient_library_id",
        "recipient_mailing_list_id" AS "mailing_list_id",
        "recipient_sample_id" AS "sample_id",
        "request_status" AS "distribution_status",
        "request_type" AS "distribution_type",
        "send_date",
        "survey_link_expiration_date" AS "survey_expiration_date",
        "survey_link_link_type" AS "survey_link_type",
        "survey_link_survey_id" AS "survey_id"
    FROM "distribution_projected"
),

"distribution_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- created_date: from VARCHAR to TIMESTAMP
    -- message_id: from DECIMAL to VARCHAR
    -- message_library_id: from DECIMAL to VARCHAR
    -- message_text: from DECIMAL to VARCHAR
    -- modified_date: from VARCHAR to TIMESTAMP
    -- parent_distribution_id: from DECIMAL to VARCHAR
    -- send_date: from VARCHAR to TIMESTAMP
    -- survey_expiration_date: from VARCHAR to TIMESTAMP
    SELECT
        "distribution_id",
        "is_deleted",
        "sender_email",
        "sender_name",
        "reply_to_email",
        "email_subject",
        "organization_id",
        "owner_id",
        "recipient_contact_id",
        "recipient_library_id",
        "mailing_list_id",
        "sample_id",
        "distribution_status",
        "distribution_type",
        "survey_link_type",
        "survey_id",
        CAST("created_date" AS TIMESTAMP) AS "created_date",
        CAST("message_id" AS VARCHAR) AS "message_id",
        CAST("message_library_id" AS VARCHAR) AS "message_library_id",
        CAST("message_text" AS VARCHAR) AS "message_text",
        CAST("modified_date" AS TIMESTAMP) AS "modified_date",
        CAST("parent_distribution_id" AS VARCHAR) AS "parent_distribution_id",
        CAST("send_date" AS TIMESTAMP) AS "send_date",
        CAST("survey_expiration_date" AS TIMESTAMP) AS "survey_expiration_date"
    FROM "distribution_projected_renamed"
),

"distribution_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 7 columns with unacceptable missing values
    -- message_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- message_library_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- message_text has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- parent_distribution_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- recipient_contact_id has 80.0 percent missing. Strategy: 🔄 Unchanged
    -- recipient_library_id has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- survey_expiration_date has 20.0 percent missing. Strategy: 🔄 Unchanged
    SELECT
        "distribution_id",
        "is_deleted",
        "sender_email",
        "sender_name",
        "reply_to_email",
        "email_subject",
        "organization_id",
        "owner_id",
        "recipient_contact_id",
        "recipient_library_id",
        "mailing_list_id",
        "sample_id",
        "distribution_status",
        "distribution_type",
        "survey_link_type",
        "survey_id",
        "created_date",
        "modified_date",
        "send_date",
        "survey_expiration_date"
    FROM "distribution_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "distribution_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_distribution
  description: The table is about distribution of surveys. It contains unique distribution
    IDs. The table includes details like creation date, sender information, and recipient
    data. It specifies survey links, expiration dates, and request types. The table
    tracks the status of distributions. It associates distributions with specific
    organizations and owners. The data covers various survey-related actions like
    invites and test emails.
  columns:
  - name: distribution_id
    description: Unique identifier for the distribution
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is a unique identifier for each distribution. For this
        table, each row represents a unique distribution, and the distribution_id
        is unique across rows.
  - name: is_deleted
    description: Indicates if the record has been deleted
    tests:
    - not_null
  - name: sender_email
    description: Email address of the sender
    cocoon_meta:
      missing_acceptable: Not applicable for GeneratedInvite distribution types
  - name: sender_name
    description: Name of the sender
    cocoon_meta:
      missing_acceptable: Not applicable for GeneratedInvite distribution types
  - name: reply_to_email
    description: Reply-to email address for the distribution
    cocoon_meta:
      missing_acceptable: Not applicable for GeneratedInvite distribution types
  - name: email_subject
    description: Subject line of the distribution email
    cocoon_meta:
      missing_acceptable: Not applicable for GeneratedInvite distribution types
  - name: organization_id
    description: ID of the organization associated with distribution
    tests:
    - not_null
  - name: owner_id
    description: ID of the owner of the distribution
    tests:
    - not_null
  - name: recipient_contact_id
    description: ID of the recipient contact
    tests:
    - not_null
  - name: recipient_library_id
    description: ID of the recipient library
    tests:
    - not_null
  - name: mailing_list_id
    description: ID of the recipient mailing list
    cocoon_meta:
      missing_acceptable: Not applicable for TestEmail distribution types
  - name: sample_id
    description: ID of the recipient sample
    cocoon_meta:
      missing_acceptable: Not applicable for non-sample based distributions
  - name: distribution_status
    description: Current status of the distribution request
    tests:
    - not_null
    - accepted_values:
        values:
        - Generated
        - Done
        - In Progress
        - Pending
        - Failed
        - Cancelled
        - On Hold
  - name: distribution_type
    description: Type of distribution request
    tests:
    - not_null
    - accepted_values:
        values:
        - GeneratedInvite
        - TestEmail
        - ManualInvite
        - BulkInvite
        - ReminderInvite
        - FollowUpInvite
        - EventSpecificInvite
  - name: survey_link_type
    description: Type of survey link
    tests:
    - accepted_values:
        values:
        - Individual
        - Group
        - Anonymous
        - Public
        - Private
        - One-time
        - Reusable
        - Embedded
        - QR Code
        - Email
        - SMS
        - Social Media
    cocoon_meta:
      missing_acceptable: Not applicable for TestEmail distribution types
  - name: survey_id
    description: ID of the associated survey
    tests:
    - not_null
  - name: created_date
    description: Date and time when the distribution was created
    tests:
    - not_null
  - name: modified_date
    description: Date and time of last modification
    tests:
    - not_null
  - name: send_date
    description: Date and time when the distribution was sent
    tests:
    - not_null
  - name: survey_expiration_date
    description: Expiration date of the survey link
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 22:49:45.805052+00:00
WITH 
"question_option_projected" AS (
    -- Projection: Selecting 6 out of 7 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "key_",
        "question_id",
        "survey_id",
        "_fivetran_deleted",
        "recode_value",
        "text"
    FROM "memory"."main"."question_option"
),

"question_option_projected_renamed" AS (
    -- Rename: Renaming columns
    -- key_ -> option_id
    -- _fivetran_deleted -> is_deleted
    -- recode_value -> option_value
    -- text -> option_text
    SELECT 
        "key_" AS "option_id",
        "question_id",
        "survey_id",
        "_fivetran_deleted" AS "is_deleted",
        "recode_value" AS "option_value",
        "text" AS "option_text"
    FROM "question_option_projected"
)

-- COCOON BLOCK END
SELECT * FROM "question_option_projected_renamed"

version: 2
models:
- name: stg_question_option
  description: The table is about question options in surveys. It contains details
    of each option, including a unique key, question ID, survey ID, deletion status,
    recode value, and option text. Each row represents a single option for a specific
    question in a particular survey. The table allows for tracking and managing multiple
    choice options across different surveys and questions.
  columns:
  - name: option_id
    description: Unique identifier for the option
    tests:
    - not_null
  - name: question_id
    description: Identifier for the question
    tests:
    - not_null
  - name: survey_id
    description: Unique identifier for the survey
    tests:
    - not_null
  - name: is_deleted
    description: Indicates if the record is deleted
    tests:
    - not_null
  - name: option_value
    description: Numerical value assigned to the option
    tests:
    - not_null
  - name: option_text
    description: Text content of the option
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-07-05 23:02:37.875113+00:00
WITH 
"user_projected" AS (
    -- Projection: Selecting 21 out of 22 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "_fivetran_deleted",
        "account_creation_date",
        "account_expiration_date",
        "account_status",
        "division_id",
        "email",
        "first_name",
        "language_",
        "last_login_date",
        "last_name",
        "organization_id",
        "password_expiration_date",
        "password_last_changed_date",
        "response_count_auditable",
        "response_count_deleted",
        "response_count_generated",
        "time_zone",
        "unsubscribed",
        "user_type",
        "username"
    FROM "memory"."main"."user"
),

"user_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> user_id
    -- _fivetran_deleted -> is_deleted
    -- language_ -> preferred_language
    -- response_count_auditable -> auditable_response_count
    -- response_count_deleted -> deleted_response_count
    -- response_count_generated -> generated_response_count
    -- unsubscribed -> is_unsubscribed
    SELECT 
        "id" AS "user_id",
        "_fivetran_deleted" AS "is_deleted",
        "account_creation_date",
        "account_expiration_date",
        "account_status",
        "division_id",
        "email",
        "first_name",
        "language_" AS "preferred_language",
        "last_login_date",
        "last_name",
        "organization_id",
        "password_expiration_date",
        "password_last_changed_date",
        "response_count_auditable" AS "auditable_response_count",
        "response_count_deleted" AS "deleted_response_count",
        "response_count_generated" AS "generated_response_count",
        "time_zone",
        "unsubscribed" AS "is_unsubscribed",
        "user_type",
        "username"
    FROM "user_projected"
),

"user_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- first_name: The problem is that 'Cat' and 'Puppy' are animal names, not typical human first names. They are likely placeholder or test data that was mistakenly left in the dataset. Since these values do not represent actual human first names, they should be replaced with empty strings to indicate missing or invalid data. 
    -- last_name: The problem is that both "Developers" and "Meow" are unusual for last names. "Developers" appears to be a job title or group name rather than a family name. "Meow" is typically associated with the sound a cat makes and is not a common surname. Neither of these represents typical family names. Since we don't have additional information to determine the correct last names for these entries, the best approach is to map these unusual values to an empty string, indicating that the last name is unknown or not provided. 
    SELECT
        "user_id",
        "is_deleted",
        "account_creation_date",
        "account_expiration_date",
        "account_status",
        "division_id",
        "email",
        CASE
            WHEN "first_name" = '''Cat''' THEN ''''
            WHEN "first_name" = '''Puppy''' THEN ''''
            ELSE "first_name"
        END AS "first_name",
        "preferred_language",
        "last_login_date",
        CASE
            WHEN "last_name" = '''Developers''' THEN ''''
            WHEN "last_name" = '''Meow''' THEN ''''
            ELSE "last_name"
        END AS "last_name",
        "organization_id",
        "password_expiration_date",
        "password_last_changed_date",
        "auditable_response_count",
        "deleted_response_count",
        "generated_response_count",
        "time_zone",
        "is_unsubscribed",
        "user_type",
        "username"
    FROM "user_projected_renamed"
),

"user_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- account_creation_date: from DECIMAL to TIMESTAMP
    -- account_expiration_date: from DECIMAL to TIMESTAMP
    -- auditable_response_count: from DECIMAL to INT
    -- deleted_response_count: from DECIMAL to INT
    -- division_id: from DECIMAL to VARCHAR
    -- generated_response_count: from DECIMAL to INT
    -- is_unsubscribed: from DECIMAL to BOOLEAN
    -- last_login_date: from DECIMAL to TIMESTAMP
    -- organization_id: from DECIMAL to VARCHAR
    -- password_expiration_date: from DECIMAL to TIMESTAMP
    -- password_last_changed_date: from DECIMAL to TIMESTAMP
    -- preferred_language: from DECIMAL to VARCHAR
    -- time_zone: from DECIMAL to VARCHAR
    SELECT
        "user_id",
        "is_deleted",
        "account_status",
        "email",
        "first_name",
        "last_name",
        "user_type",
        "username",
        CAST("account_creation_date" AS TIMESTAMP) AS "account_creation_date",
        CAST("account_expiration_date" AS TIMESTAMP) AS "account_expiration_date",
        CAST("auditable_response_count" AS INT) AS "auditable_response_count",
        CAST("deleted_response_count" AS INT) AS "deleted_response_count",
        CAST("division_id" AS VARCHAR) AS "division_id",
        CAST("generated_response_count" AS INT) AS "generated_response_count",
        CAST("is_unsubscribed" AS BOOLEAN) AS "is_unsubscribed",
        CAST("last_login_date" AS TIMESTAMP) AS "last_login_date",
        CAST("organization_id" AS VARCHAR) AS "organization_id",
        CAST("password_expiration_date" AS TIMESTAMP) AS "password_expiration_date",
        CAST("password_last_changed_date" AS TIMESTAMP) AS "password_last_changed_date",
        CAST("preferred_language" AS VARCHAR) AS "preferred_language",
        CAST("time_zone" AS VARCHAR) AS "time_zone"
    FROM "user_projected_renamed_cleaned"
),

"user_projected_renamed_cleaned_casted_missing_handled" AS (
    -- Handling missing values: There are 12 columns with unacceptable missing values
    -- account_creation_date has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- auditable_response_count has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- deleted_response_count has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- division_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- generated_response_count has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- is_unsubscribed has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- last_login_date has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- organization_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- password_expiration_date has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- password_last_changed_date has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- preferred_language has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- time_zone has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "user_id",
        "is_deleted",
        "account_status",
        "email",
        "first_name",
        "last_name",
        "user_type",
        "username",
        "account_expiration_date"
    FROM "user_projected_renamed_cleaned_casted"
)

-- COCOON BLOCK END
SELECT * FROM "user_projected_renamed_cleaned_casted_missing_handled"

version: 2
models:
- name: stg_user
  description: The table is about user accounts. It contains user details like ID,
    email, name, and account status. The table includes information on account creation,
    expiration, and last login dates. It also tracks user types, organizations, and
    response counts. Some fields like language and time zone suggest customization
    options for users.
  columns:
  - name: user_id
    description: Unique identifier for the user
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is a unique identifier for each user. For this table,
        each row represents a distinct user account. user_id appears to be unique
        across rows, as it's designed to be a specific identifier for each user.
  - name: is_deleted
    description: Indicates if the record is deleted
    tests:
    - not_null
  - name: account_status
    description: Current status of the user account
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - suspended
        - pending
        - locked
        - closed
        - banned
        - archived
  - name: email
    description: User's email address
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the user's email address. In most systems,
        email addresses are unique for each user account. However, there's a slight
        possibility that in some systems, multiple accounts could share an email (e.g.,
        work accounts). Based on the sample data, it appears to be unique.
  - name: first_name
    description: User's first name
    tests:
    - not_null
  - name: last_name
    description: User's last name
    tests:
    - not_null
  - name: user_type
    description: Type or role of the user
    tests:
    - not_null
    - accepted_values:
        values:
        - UT_BRANDADMIN
        - UT_ADMIN
        - UT_USER
        - UT_MANAGER
        - UT_SUPPORT
        - UT_GUEST
        - UT_DEVELOPER
        - UT_ANALYST
        - UT_MODERATOR
        - UT_EDITOR
  - name: username
    description: User's username for login
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the user's username for login. In most systems,
        usernames are unique for each user account. Based on the sample data, it appears
        to be unique across rows.
  - name: account_expiration_date
    description: Date when the user account expires
    cocoon_meta:
      missing_acceptable: Active accounts may not have an expiration date.

-- Slowly Changing Dimension: Dimension keys are "directory_id", "id"
-- Effective date columns are "last_modified_date"
-- We will create Type 1 SCD (latest snapshot)
SELECT 
    "directory_id",
    "id",
    "_fivetran_deleted",
    "creation_date",
    "name",
    "owner_id"
FROM (
     SELECT 
            "directory_id",
            "id",
            "_fivetran_deleted",
            "creation_date",
            "name",
            "owner_id",
            ROW_NUMBER() OVER (
                PARTITION BY "directory_id", "id" 
                ORDER BY "last_modified_date" 
            DESC) AS "cocoon_rn"
    FROM "stg_directory_mailing_list"
) ranked
WHERE "cocoon_rn" = 1

version: 2
models:
- name: snapshot_directory_mailing_list
  description: The table is about mailing lists in a directory. It tracks the most
    recent version of each mailing list, identified by a unique combination of directory_id
    and id. The table includes information such as the creation date, name, and owner
    of each mailing list. It excludes version-related information like last modified
    date and deletion status.
  columns:
  - name: directory_id
    description: directory_id
    tests:
    - not_null
  - name: id
    description: id
    tests:
    - not_null
  - name: _fivetran_deleted
    description: _fivetran_deleted
    tests:
    - not_null
  - name: creation_date
    description: creation_date
    tests:
    - not_null
  - name: name
    description: name
    tests:
    - not_null
  - name: owner_id
    description: owner_id
    tests:
    - not_null
cocoon_meta:
  scd_base_table: stg_directory_mailing_list

-- Slowly Changing Dimension: Dimension keys are "survey_id"
-- Effective date columns are "version_number"
-- We will create Type 1 SCD (latest snapshot)
SELECT 
    "survey_id",
    "is_deleted",
    "description",
    "is_published",
    "user_id",
    "was_ever_published",
    "creation_date",
    "version_id"
FROM (
     SELECT 
            "survey_id",
            "is_deleted",
            "description",
            "is_published",
            "user_id",
            "was_ever_published",
            "creation_date",
            "version_id",
            ROW_NUMBER() OVER (
                PARTITION BY "survey_id" 
                ORDER BY "version_number" 
            DESC) AS "cocoon_rn"
    FROM "stg_survey_version"
) ranked
WHERE "cocoon_rn" = 1

version: 2
models:
- name: snapshot_survey_version
  description: The table is about the latest versions of surveys. It tracks the most
    recent state of each survey, including its ID, deletion status, description, publication
    status, associated user, and creation date. The table indicates whether a survey
    was ever published and provides a unique identifier for each survey's latest version.
    It excludes version-specific information, focusing on the current state of each
    survey.
  columns:
  - name: survey_id
    description: Unique identifier for the survey
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: Unique dimension key, derived from the slowly changing dimension
  - name: is_deleted
    description: Flag indicating if the record was deleted
    tests:
    - not_null
  - name: description
    description: Brief description of the survey version
    tests:
    - not_null
  - name: is_published
    description: Flag indicating if the version is currently published
    tests:
    - not_null
  - name: user_id
    description: Unique identifier of the user who created the version
    tests:
    - not_null
  - name: was_ever_published
    description: Flag indicating if the version was ever published
    tests:
    - not_null
  - name: creation_date
    description: Date and time when the survey version was created
    tests:
    - not_null
  - name: version_id
    description: Unique identifier for the survey version
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is described as a unique identifier for the survey version.
        For this table, each row represents a specific version of a survey. Given
        that it's explicitly stated as a unique identifier, it should be unique across
        all rows.
cocoon_meta:
  scd_base_table: stg_survey_version