-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-26 00:51:15.538935+00:00
WITH 
"part_renamed" AS (
    -- Rename: Renaming columns
    -- P_PARTKEY -> part_id
    -- P_NAME -> part_name
    -- P_MFGR -> manufacturer_code
    -- P_CATEGORY -> category_code
    -- P_BRAND1 -> brand_code
    -- P_COLOR -> color
    -- P_TYPE -> type_and_material
    -- P_SIZE -> size_
    -- P_CONTAINER -> container_type
    SELECT 
        "P_PARTKEY" AS "part_id",
        "P_NAME" AS "part_name",
        "P_MFGR" AS "manufacturer_code",
        "P_CATEGORY" AS "category_code",
        "P_BRAND1" AS "brand_code",
        "P_COLOR" AS "color",
        "P_TYPE" AS "type_and_material",
        "P_SIZE" AS "size_",
        "P_CONTAINER" AS "container_type"
    FROM "memory"."main"."part"
)

-- COCOON BLOCK END
SELECT *
FROM "part_renamed"

version: 2
models:
- name: stg_part
  description: The table is about parts. It contains details of various parts including
    their unique key, name, manufacturer, category, brand, color, type, size, and
    container. Each row represents a different part with its specific attributes.
    The table seems to be a catalog or inventory of parts with their characteristics
    and classifications.
  columns:
  - name: part_id
    description: Unique identifier for the part
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents a unique identifier for each part. For
        this table, each row represents a different part, and part_id appears to be
        unique across rows.
      data_type:
        current_data_type: INT
  - name: part_name
    description: Descriptive name of the part
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: ^[a-z]+ [a-z]+$
        summary: Two color names or descriptors separated by a space
      data_type:
        current_data_type: VARCHAR
  - name: manufacturer_code
    description: Manufacturer code of the part
    tests:
    - not_null
    - accepted_values:
        values:
        - MFGR#2
        - MFGR#4
        - MFGR#1
        - MFGR#5
        - MFGR#3
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: category_code
    description: Category code of the part
    tests:
    - not_null
    - accepted_values:
        values:
        - MFGR#23
        - MFGR#41
        - MFGR#14
        - MFGR#11
        - MFGR#24
        - MFGR#54
        - MFGR#53
        - MFGR#22
        - MFGR#45
        - MFGR#32
        - MFGR#42
        - MFGR#34
        - MFGR#51
        - MFGR#13
        - MFGR#31
        - MFGR#25
        - MFGR#21
        - MFGR#44
        - MFGR#43
        - MFGR#33
        - MFGR#52
        - MFGR#55
        - MFGR#35
        - MFGR#12
        - MFGR#15
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: brand_code
    description: Brand code of the part
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: MFGR#\d{3,4}
        summary: Manufacturer codes starting with "MFGR#" followed by 3 or 4 digits
      data_type:
        current_data_type: VARCHAR
  - name: color
    description: Color of the part
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: type_and_material
    description: Type and material of the part
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: (ECONOMY|MEDIUM|SMALL|LARGE|PROMO|STANDARD) (ANODIZED|POLISHED|PLATED|BURNISHED|BRUSHED)
          (STEEL|NICKEL|BRASS|COPPER|TIN)
        summary: Product type, treatment, and material in all caps
      data_type:
        current_data_type: VARCHAR
  - name: size_
    description: Size of the part
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: container_type
    description: Container type for the part
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-26 00:47:19.528883+00:00
WITH 
"customer_renamed" AS (
    -- Rename: Renaming columns
    -- C_CUSTKEY -> customer_id
    -- C_NAME -> customer_name
    -- C_ADDRESS -> street_address
    -- C_CITY -> city
    -- C_NATION -> country
    -- C_REGION -> region
    -- C_PHONE -> phone_number
    -- C_MKTSEGMENT -> market_segment
    SELECT 
        "C_CUSTKEY" AS "customer_id",
        "C_NAME" AS "customer_name",
        "C_ADDRESS" AS "street_address",
        "C_CITY" AS "city",
        "C_NATION" AS "country",
        "C_REGION" AS "region",
        "C_PHONE" AS "phone_number",
        "C_MKTSEGMENT" AS "market_segment"
    FROM "memory"."main"."customer"
),

"customer_renamed_trimmed" AS (
    -- Trim Leading and Trailing Spaces
    SELECT
        "customer_id",
        "customer_name",
        "city",
        "country",
        "region",
        "phone_number",
        "market_segment",
        TRIM("street_address") AS "street_address"
    FROM "customer_renamed"
),

"customer_renamed_trimmed_cleaned" AS (
    -- Clean unusual string values: 
    -- city: The problem is that the city column contains country names followed by numbers, with inconsistent spacing and truncation. The country names should be standardized, and the numbers (which likely represent some kind of code or index) should be separated. "UNITED ST" is truncated and should be "UNITED STATES". "UNITED KI" should be "UNITED KINGDOM". "SAUDI ARA" should be "SAUDI ARABIA". The correct values should have consistent spacing between the country name and the number, with the country name in full.
    -- street_address: Values starting with commas are unusual. Random strings like '2z1 vZUo2qjaxg7qJouIBb' are not typical for street addresses.
    SELECT
        "customer_id",
        "customer_name",
        CASE
            WHEN "city" = 'UNITED ST4' THEN 'UNITED STATES 4'
            WHEN "city" = 'UNITED ST0' THEN 'UNITED STATES 0'
            WHEN "city" = 'UNITED ST1' THEN 'UNITED STATES 1'
            WHEN "city" = 'UNITED KI5' THEN 'UNITED KINGDOM 5'
            WHEN "city" = 'SAUDI ARA7' THEN 'SAUDI ARABIA 7'
            WHEN "city" = 'ARGENTINA4' THEN 'ARGENTINA 4'
            WHEN "city" = 'ARGENTINA5' THEN 'ARGENTINA 5'
            WHEN "city" = 'MOZAMBIQU7' THEN 'MOZAMBIQUE 7'
            WHEN "city" = 'MOZAMBIQU9' THEN 'MOZAMBIQUE 9'
            WHEN "city" = 'RUSSIA   5' THEN 'RUSSIA 5'
            WHEN "city" = 'RUSSIA   6' THEN 'RUSSIA 6'
            WHEN "city" = 'RUSSIA   3' THEN 'RUSSIA 3'
            WHEN "city" = 'MOROCCO  5' THEN 'MOROCCO 5'
            WHEN "city" = 'FRANCE   8' THEN 'FRANCE 8'
            WHEN "city" = 'JAPAN    3' THEN 'JAPAN 3'
            WHEN "city" = 'ALGERIA  8' THEN 'ALGERIA 8'
            WHEN "city" = 'ALGERIA  0' THEN 'ALGERIA 0'
            WHEN "city" = 'ALGERIA  5' THEN 'ALGERIA 5'
            WHEN "city" = 'CHINA    5' THEN 'CHINA 5'
            WHEN "city" = 'EGYPT    7' THEN 'EGYPT 7'
            WHEN "city" = 'EGYPT    1' THEN 'EGYPT 1'
            WHEN "city" = 'EGYPT    3' THEN 'EGYPT 3'
            WHEN "city" = 'JORDAN   5' THEN 'JORDAN 5'
            WHEN "city" = 'JORDAN   8' THEN 'JORDAN 8'
            WHEN "city" = 'KENYA    2' THEN 'KENYA 2'
            WHEN "city" = 'KENYA    9' THEN 'KENYA 9'
            WHEN "city" = 'PERU     8' THEN 'PERU 8'
            WHEN "city" = 'CANADA   2' THEN 'CANADA 2'
            WHEN "city" = 'CANADA   4' THEN 'CANADA 4'
            WHEN "city" = 'CANADA   9' THEN 'CANADA 9'
            WHEN "city" = 'CANADA   0' THEN 'CANADA 0'
            WHEN "city" = 'VIETNAM  0' THEN 'VIETNAM 0'
            WHEN "city" = 'GERMANY  0' THEN 'GERMANY 0'
            WHEN "city" = 'GERMANY  9' THEN 'GERMANY 9'
            WHEN "city" = 'GERMANY  4' THEN 'GERMANY 4'
            WHEN "city" = 'IRAN     4' THEN 'IRAN 4'
            WHEN "city" = 'IRAN     6' THEN 'IRAN 6'
            WHEN "city" = 'IRAN     7' THEN 'IRAN 7'
            WHEN "city" = 'IRAQ     4' THEN 'IRAQ 4'
            WHEN "city" = 'IRAQ     9' THEN 'IRAQ 9'
            WHEN "city" = 'IRAQ     5' THEN 'IRAQ 5'
            WHEN "city" = 'INDIA    8' THEN 'INDIA 8'
            WHEN "city" = 'INDONESIA1' THEN 'INDONESIA 1'
            WHEN "city" = 'INDONESIA3' THEN 'INDONESIA 3'
            WHEN "city" = 'BRAZIL   4' THEN 'BRAZIL 4'
            WHEN "city" = 'ROMANIA  4' THEN 'ROMANIA 4'
            WHEN "city" = 'ROMANIA  7' THEN 'ROMANIA 7'
            WHEN "city" = 'MOZAMBIQU2' THEN 'MOZAMBIQUE'
            WHEN "city" = 'ROMANIA  1' THEN 'ROMANIA'
            WHEN "city" = 'ROMANIA  3' THEN 'ROMANIA'
            WHEN "city" = 'VIETNAM  5' THEN 'VIETNAM'
            WHEN "city" = 'ARGENTINA2' THEN 'ARGENTINA'
            WHEN "city" = 'CHINA    2' THEN 'CHINA'
            WHEN "city" = 'CHINA    4' THEN 'CHINA'
            WHEN "city" = 'FRANCE   0' THEN 'FRANCE'
            WHEN "city" = 'JAPAN    9' THEN 'JAPAN'
            WHEN "city" = 'UNITED KI3' THEN 'UNITED KINGDOM'
            WHEN "city" = 'ALGERIA  2' THEN 'ALGERIA'
            WHEN "city" = 'ARGENTINA7' THEN 'ARGENTINA'
            WHEN "city" = 'BRAZIL   8' THEN 'BRAZIL'
            WHEN "city" = 'ETHIOPIA 8' THEN 'ETHIOPIA'
            WHEN "city" = 'FRANCE   6' THEN 'FRANCE'
            WHEN "city" = 'INDONESIA2' THEN 'INDONESIA'
            WHEN "city" = 'JORDAN   2' THEN 'JORDAN'
            WHEN "city" = 'KENYA    3' THEN 'KENYA'
            WHEN "city" = 'ROMANIA  5' THEN 'ROMANIA'
            WHEN "city" = 'UNITED KI0' THEN 'UNITED KINGDOM'
            WHEN "city" = 'ETHIOPIA 6' THEN 'ETHIOPIA'
            WHEN "city" = 'INDONESIA7' THEN 'INDONESIA'
            WHEN "city" = 'MOROCCO  4' THEN 'MOROCCO'
            WHEN "city" = 'MOROCCO  8' THEN 'MOROCCO'
            WHEN "city" = 'MOZAMBIQU1' THEN 'MOZAMBIQUE'
            WHEN "city" = 'MOZAMBIQU3' THEN 'MOZAMBIQUE'
            WHEN "city" = 'UNITED ST2' THEN 'UNITED STATES'
            WHEN "city" = 'UNITED ST5' THEN 'UNITED STATES'
            WHEN "city" = 'EGYPT    0' THEN 'EGYPT'
            WHEN "city" = 'EGYPT    8' THEN 'EGYPT'
            WHEN "city" = 'EGYPT    9' THEN 'EGYPT'
            WHEN "city" = 'INDIA    9' THEN 'INDIA'
            WHEN "city" = 'IRAN     1' THEN 'IRAN'
            WHEN "city" = 'IRAQ     3' THEN 'IRAQ'
            WHEN "city" = 'PERU     6' THEN 'PERU'
            WHEN "city" = 'UNITED ST6' THEN 'UNITED STATES'
            WHEN "city" = 'UNITED ST8' THEN 'UNITED STATES'
            WHEN "city" = 'CANADA   6' THEN 'CANADA'
            WHEN "city" = 'CHINA    0' THEN 'CHINA'
            WHEN "city" = 'CHINA    1' THEN 'CHINA'
            WHEN "city" = 'CHINA    3' THEN 'CHINA'
            WHEN "city" = 'GERMANY  6' THEN 'GERMANY'
            WHEN "city" = 'INDONESIA8' THEN 'INDONESIA'
            WHEN "city" = 'IRAQ     7' THEN 'IRAQ'
            WHEN "city" = 'JAPAN    1' THEN 'JAPAN'
            WHEN "city" = 'JAPAN    6' THEN 'JAPAN'
            WHEN "city" = 'ROMANIA  6' THEN 'ROMANIA'
            WHEN "city" = 'VIETNAM  1' THEN 'VIETNAM'
            WHEN "city" = 'ARGENTINA8' THEN 'ARGENTINA'
            WHEN "city" = 'GERMANY  7' THEN 'GERMANY'
            WHEN "city" = 'SAUDI ARA1' THEN 'SAUDI ARABIA 1'
            WHEN "city" = 'SAUDI ARA2' THEN 'SAUDI ARABIA 2'
            WHEN "city" = 'SAUDI ARA4' THEN 'SAUDI ARABIA 4'
            WHEN "city" = 'UNITED KI2' THEN 'UNITED KINGDOM 2'
            WHEN "city" = 'UNITED KI7' THEN 'UNITED KINGDOM 7'
            WHEN "city" = 'MOZAMBIQU5' THEN 'MOZAMBIQUE 5'
            WHEN "city" = 'INDONESIA6' THEN 'INDONESIA 6'
            WHEN "city" = 'IRAN     0' THEN 'IRAN 0'
            WHEN "city" = 'IRAQ     6' THEN 'IRAQ 6'
            WHEN "city" = 'KENYA    1' THEN 'KENYA 1'
            WHEN "city" = 'KENYA    7' THEN 'KENYA 7'
            WHEN "city" = 'MOROCCO  1' THEN 'MOROCCO 1'
            WHEN "city" = 'VIETNAM  2' THEN 'VIETNAM 2'
            WHEN "city" = 'BRAZIL   2' THEN 'BRAZIL 2'
            WHEN "city" = 'CANADA   3' THEN 'CANADA 3'
            WHEN "city" = 'CHINA    6' THEN 'CHINA 6'
            WHEN "city" = 'GERMANY  5' THEN 'GERMANY 5'
            WHEN "city" = 'INDIA    4' THEN 'INDIA 4'
            WHEN "city" = 'IRAN     3' THEN 'IRAN 3'
            WHEN "city" = 'JAPAN    0' THEN 'JAPAN 0'
            WHEN "city" = 'JORDAN   3' THEN 'JORDAN 3'
            WHEN "city" = 'MOROCCO  9' THEN 'MOROCCO 9'
            WHEN "city" = 'VIETNAM  8' THEN 'VIETNAM 8'
            WHEN "city" = 'ARGENTINA3' THEN 'ARGENTINA 3'
            WHEN "city" = 'CHINA    9' THEN 'CHINA 9'
            WHEN "city" = 'FRANCE   2' THEN 'FRANCE 2'
            WHEN "city" = 'GERMANY  8' THEN 'GERMANY 8'
            WHEN "city" = 'INDIA    3' THEN 'INDIA 3'
            WHEN "city" = 'INDIA    7' THEN 'INDIA 7'
            WHEN "city" = 'JORDAN   1' THEN 'JORDAN 1'
            WHEN "city" = 'JORDAN   7' THEN 'JORDAN 7'
            WHEN "city" = 'KENYA    0' THEN 'KENYA 0'
            WHEN "city" = 'PERU     0' THEN 'PERU 0'
            WHEN "city" = 'BRAZIL   0' THEN 'BRAZIL 0'
            WHEN "city" = 'BRAZIL   7' THEN 'BRAZIL 7'
            WHEN "city" = 'FRANCE   9' THEN 'FRANCE 9'
            WHEN "city" = 'IRAN     5' THEN 'IRAN 5'
            WHEN "city" = 'JAPAN    5' THEN 'JAPAN 5'
            WHEN "city" = 'KENYA    8' THEN 'KENYA 8'
            WHEN "city" = 'MOROCCO  6' THEN 'MOROCCO 6'
            WHEN "city" = 'RUSSIA   9' THEN 'RUSSIA 9'
            WHEN "city" = 'CANADA   1' THEN 'CANADA 1'
            WHEN "city" = 'EGYPT    5' THEN 'EGYPT 5'
            WHEN "city" = 'INDIA    0' THEN 'INDIA 0'
            WHEN "city" = 'INDONESIA5' THEN 'INDONESIA 5'
            WHEN "city" = 'IRAN     9' THEN 'IRAN 9'
            WHEN "city" = 'JORDAN   6' THEN 'JORDAN 6'
            WHEN "city" = 'PERU     4' THEN 'PERU 4'
            WHEN "city" = 'PERU     9' THEN 'PERU'
            WHEN "city" = 'RUSSIA   4' THEN 'RUSSIA'
            WHEN "city" = 'RUSSIA   7' THEN 'RUSSIA'
            WHEN "city" = 'SAUDI ARA9' THEN 'SAUDI ARABIA'
            WHEN "city" = 'UNITED KI1' THEN 'UNITED KINGDOM'
            WHEN "city" = 'ALGERIA  1' THEN 'ALGERIA'
            WHEN "city" = 'CHINA    8' THEN 'CHINA'
            WHEN "city" = 'FRANCE   5' THEN 'FRANCE'
            WHEN "city" = 'GERMANY  1' THEN 'GERMANY'
            WHEN "city" = 'INDIA    1' THEN 'INDIA'
            WHEN "city" = 'IRAN     8' THEN 'IRAN'
            WHEN "city" = 'KENYA    6' THEN 'KENYA'
            WHEN "city" = 'ROMANIA  2' THEN 'ROMANIA'
            WHEN "city" = 'RUSSIA   8' THEN 'RUSSIA'
            WHEN "city" = 'UNITED KI8' THEN 'UNITED KINGDOM'
            WHEN "city" = 'VIETNAM  6' THEN 'VIETNAM'
            WHEN "city" = 'VIETNAM  9' THEN 'VIETNAM'
            WHEN "city" = 'ARGENTINA0' THEN 'ARGENTINA'
            WHEN "city" = 'BRAZIL   6' THEN 'BRAZIL'
            WHEN "city" = 'ETHIOPIA 0' THEN 'ETHIOPIA'
            WHEN "city" = 'ETHIOPIA 1' THEN 'ETHIOPIA'
            WHEN "city" = 'FRANCE   3' THEN 'FRANCE'
            WHEN "city" = 'FRANCE   7' THEN 'FRANCE'
            WHEN "city" = 'GERMANY  2' THEN 'GERMANY'
            WHEN "city" = 'INDONESIA0' THEN 'INDONESIA'
            WHEN "city" = 'IRAN     2' THEN 'IRAN'
            WHEN "city" = 'IRAQ     1' THEN 'IRAQ'
            WHEN "city" = 'IRAQ     8' THEN 'IRAQ'
            WHEN "city" = 'KENYA    5' THEN 'KENYA'
            WHEN "city" = 'MOZAMBIQU8' THEN 'MOZAMBIQUE'
            WHEN "city" = 'PERU     3' THEN 'PERU'
            WHEN "city" = 'UNITED KI4' THEN 'UNITED KINGDOM'
            WHEN "city" = 'ALGERIA  7' THEN 'ALGERIA'
            WHEN "city" = 'CANADA   7' THEN 'CANADA'
            WHEN "city" = 'CANADA   8' THEN 'CANADA'
            WHEN "city" = 'IRAQ     2' THEN 'IRAQ'
            WHEN "city" = 'SAUDI ARA5' THEN 'SAUDI ARABIA'
            WHEN "city" = 'UNITED ST9' THEN 'UNITED STATES'
            WHEN "city" = 'VIETNAM  4' THEN 'VIETNAM'
            WHEN "city" = 'GERMANY  3' THEN 'GERMANY'
            WHEN "city" = 'IRAQ     0' THEN 'IRAQ'
            WHEN "city" = 'JORDAN   0' THEN 'JORDAN'
            WHEN "city" = 'MOROCCO  0' THEN 'MOROCCO'
            WHEN "city" = 'MOROCCO  3' THEN 'MOROCCO'
            WHEN "city" = 'ROMANIA  9' THEN 'ROMANIA'
            WHEN "city" = 'SAUDI ARA3' THEN 'SAUDI ARABIA'
            WHEN "city" = 'UNITED KI9' THEN 'UNITED KINGDOM'
            WHEN "city" = 'UNITED ST7' THEN 'UNITED STATES'
            WHEN "city" = 'BRAZIL   1' THEN 'BRAZIL'
            WHEN "city" = 'BRAZIL   9' THEN 'BRAZIL'
            WHEN "city" = 'EGYPT    6' THEN 'EGYPT'
            WHEN "city" = 'FRANCE   4' THEN 'FRANCE'
            WHEN "city" = 'JAPAN    7' THEN 'JAPAN'
            WHEN "city" = 'JAPAN    8' THEN 'JAPAN'
            WHEN "city" = 'MOZAMBIQU6' THEN 'MOZAMBIQUE'
            WHEN "city" = 'PERU     5' THEN 'PERU'
            WHEN "city" = 'PERU     7' THEN 'PERU'
            WHEN "city" = 'RUSSIA   2' THEN 'RUSSIA'
            WHEN "city" = 'UNITED KI6' THEN 'UNITED KINGDOM'
            WHEN "city" = 'ARGENTINA1' THEN 'ARGENTINA'
            WHEN "city" = 'BRAZIL   5' THEN 'BRAZIL'
            WHEN "city" = 'CHINA    7' THEN 'CHINA'
            WHEN "city" = 'FRANCE   1' THEN 'FRANCE'
            WHEN "city" = 'INDIA    5' THEN 'INDIA'
            WHEN "city" = 'JAPAN    2' THEN 'JAPAN'
            WHEN "city" = 'JORDAN   9' THEN 'JORDAN'
            WHEN "city" = 'UNITED ST3' THEN 'UNITED STATES'
            WHEN "city" = 'ALGERIA  4' THEN 'ALGERIA'
            WHEN "city" = 'ARGENTINA6' THEN 'ARGENTINA'
            WHEN "city" = 'INDONESIA9' THEN 'INDONESIA'
            WHEN "city" = 'MOROCCO  2' THEN 'MOROCCO'
            WHEN "city" = 'PERU     1' THEN 'PERU'
            WHEN "city" = 'ROMANIA  0' THEN 'ROMANIA'
            WHEN "city" = 'RUSSIA   1' THEN 'RUSSIA'
            WHEN "city" = 'INDIA    6' THEN 'INDIA'
            WHEN "city" = 'INDONESIA4' THEN 'INDONESIA'
            WHEN "city" = 'PERU     2' THEN 'PERU'
            WHEN "city" = 'SAUDI ARA6' THEN 'SAUDI ARABIA'
            WHEN "city" = 'VIETNAM  7' THEN 'VIETNAM'
            WHEN "city" = 'BRAZIL   3' THEN 'BRAZIL'
            WHEN "city" = 'EGYPT    2' THEN 'EGYPT'
            WHEN "city" = 'EGYPT    4' THEN 'EGYPT'
            WHEN "city" = 'ETHIOPIA 4' THEN 'ETHIOPIA'
            WHEN "city" = 'SAUDI ARA8' THEN 'SAUDI ARABIA'
            WHEN "city" = 'ETHIOPIA 2' THEN 'ETHIOPIA'
            WHEN "city" = 'MOZAMBIQU4' THEN 'MOZAMBIQUE'
            WHEN "city" = 'ROMANIA  8' THEN 'ROMANIA'
            WHEN "city" = 'JAPAN    4' THEN 'JAPAN'
            WHEN "city" = 'VIETNAM  3' THEN 'VIETNAM'
            WHEN "city" = 'INDIA    2' THEN 'INDIA'
            WHEN "city" = 'JORDAN   4' THEN 'JORDAN'
            WHEN "city" = 'ALGERIA  3' THEN 'ALGERIA'
            WHEN "city" = 'ALGERIA  9' THEN 'ALGERIA'
            WHEN "city" = 'KENYA    4' THEN 'KENYA'
            WHEN "city" = 'MOZAMBIQU0' THEN 'MOZAMBIQUE'
            WHEN "city" = 'RUSSIA   0' THEN 'RUSSIA'
            WHEN "city" = 'SAUDI ARA0' THEN 'SAUDI ARABIA'
            WHEN "city" = 'ALGERIA  6' THEN 'ALGERIA'
            WHEN "city" = 'MOROCCO  7' THEN 'MOROCCO'
            WHEN "city" = 'CANADA   5' THEN 'CANADA'
            ELSE "city"
        END AS "city",
        "country",
        "region",
        "phone_number",
        "market_segment",
        "street_address"
    FROM "customer_renamed_trimmed"
)

-- COCOON BLOCK END
SELECT *
FROM "customer_renamed_trimmed_cleaned"

version: 2
models:
- name: stg_customer
  description: The table is about customers. It contains customer details including
    a unique customer key, name, address, city, nation, region, phone number, and
    market segment. Each row represents a single customer with their specific information.
    The table appears to be part of a larger database system, likely for managing
    customer information in a business context.
  columns:
  - name: customer_id
    description: Unique identifier for each customer
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents a unique identifier for each customer.
        For this table, each row is for a single customer. customer_id appears to
        be unique across rows, as it's formatted as "Customer#000000001", "Customer#000000002",
        etc.
      data_type:
        current_data_type: INT
  - name: customer_name
    description: Customer's full name
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: Customer#\d{9}
        summary: Customer IDs in the format of Customer# followed by a 9-digit number
      data_type:
        current_data_type: VARCHAR
  - name: city
    description: Customer's city of residence
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: '[A-Z ]+\s\d$'
        summary: Country name followed by a space and a single digit
      - regex: ^[A-Z ]+$
        summary: Country name only
      - regex: ARGENTINA\d
        summary: Text starting with "ARGENTINA" followed by a single digit
      data_type:
        current_data_type: VARCHAR
  - name: country
    description: Customer's country of residence
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: region
    description: Geographical region of the customer
    tests:
    - not_null
    - accepted_values:
        values:
        - EUROPE
        - AMERICA
        - MIDDLE EAST
        - ASIA
        - AFRICA
    cocoon_meta:
      future_accepted_values:
      - OCEANIA
      - ANTARCTICA
      - CARIBBEAN
      - CENTRAL AMERICA
      - SOUTH AMERICA
      data_type:
        current_data_type: VARCHAR
  - name: phone_number
    description: Customer's phone number
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: \d{2}-\d{3}-\d{3}-\d{4}
        summary: Phone numbers in the format of XX-XXX-XXX-XXXX, where X represents
          a digit
      data_type:
        current_data_type: VARCHAR
  - name: market_segment
    description: Market segment the customer belongs to
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: street_address
    description: Customer's street address
    tests:
    - not_null
    cocoon_meta:
      unusual_values: Values starting with commas are unusual. Random strings like
        '2z1 vZUo2qjaxg7qJouIBb' are not typical for street addresses.
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-26 00:52:51.226296+00:00
WITH 
"supplier_renamed" AS (
    -- Rename: Renaming columns
    -- S_SUPPKEY -> supplier_id
    -- S_NAME -> supplier_name
    -- S_ADDRESS -> street_address
    -- S_CITY -> city
    -- S_NATION -> country
    -- S_REGION -> region
    -- S_PHONE -> phone_number
    SELECT 
        "S_SUPPKEY" AS "supplier_id",
        "S_NAME" AS "supplier_name",
        "S_ADDRESS" AS "street_address",
        "S_CITY" AS "city",
        "S_NATION" AS "country",
        "S_REGION" AS "region",
        "S_PHONE" AS "phone_number"
    FROM "memory"."main"."supplier"
),

"supplier_renamed_trimmed" AS (
    -- Trim Leading and Trailing Spaces
    SELECT
        "supplier_id",
        "supplier_name",
        "city",
        "country",
        "region",
        "phone_number",
        TRIM("street_address") AS "street_address"
    FROM "supplier_renamed"
),

"supplier_renamed_trimmed_cleaned" AS (
    -- Clean unusual string values: 
    -- city: The problem is that some city names are truncated or have inconsistent spacing. The "SAUDI ARA" entries are truncated and should be "SAUDI ARABIA". The "INDONESIA" entries have inconsistent spacing, with "INDONESIA4" having no space and "INDONESIA1" having one space. All other entries have 2 spaces between the country name and number. The correct values should have consistent formatting with 2 spaces between the country name and number for all entries.
    -- street_address: All values are unusual because they start with commas and consist of random alphanumeric strings instead of typical address components.
    SELECT
        "supplier_id",
        "supplier_name",
        CASE
            WHEN "city" = 'INDONESIA4' THEN 'INDONESIA 4'
            WHEN "city" = 'INDONESIA1' THEN 'INDONESIA 1'
            WHEN "city" = 'SAUDI ARA0' THEN 'SAUDI ARABIA 0'
            WHEN "city" = 'SAUDI ARA7' THEN 'SAUDI ARABIA 7'
            WHEN "city" = 'SAUDI ARA5' THEN 'SAUDI ARABIA 5'
            WHEN "city" = 'SAUDI ARA8' THEN 'SAUDI ARABIA 8'
            WHEN "city" = 'SAUDI ARA1' THEN 'SAUDI ARA 1'
            WHEN "city" = 'SAUDI ARA6' THEN 'SAUDI ARA 6'
            WHEN "city" = 'INDONESIA8' THEN 'INDONESIA 8'
            WHEN "city" = 'INDONESIA3' THEN 'INDONESIA 3'
            WHEN "city" = 'INDONESIA7' THEN 'INDONESIA 7'
            WHEN "city" = 'INDONESIA9' THEN 'INDONESIA 9'
            WHEN "city" = 'INDONESIA0' THEN 'INDONESIA 0'
            WHEN "city" = 'UNITED KI9' THEN 'UNITED KI 9'
            WHEN "city" = 'UNITED ST0' THEN 'UNITED ST 0'
            WHEN "city" = 'UNITED ST8' THEN 'UNITED ST 8'
            WHEN "city" = 'UNITED ST9' THEN 'UNITED ST 9'
            WHEN "city" = 'ARGENTINA7' THEN 'ARGENTINA 7'
            WHEN "city" = 'ARGENTINA8' THEN 'ARGENTINA 8'
            WHEN "city" = 'SAUDI ARA2' THEN 'SAUDI AR 2'
            WHEN "city" = 'SAUDI ARA9' THEN 'SAUDI AR 9'
            WHEN "city" = 'INDONESIA2' THEN 'INDONESI 2'
            WHEN "city" = 'UNITED ST4' THEN 'UNITED S 4'
            WHEN "city" = 'UNITED ST5' THEN 'UNITED S 5'
            WHEN "city" = 'UNITED ST6' THEN 'UNITED S 6'
            WHEN "city" = 'UNITED ST7' THEN 'UNITED S 7'
            WHEN "city" = 'UNITED KI0' THEN 'UNITED K 0'
            WHEN "city" = 'MOZAMBIQU2' THEN 'MOZAMBIQ 2'
            WHEN "city" = 'MOZAMBIQU6' THEN 'MOZAMBIQ 6'
            WHEN "city" = 'INDONESIA5' THEN 'INDONESIA 5'
            WHEN "city" = 'INDONESIA6' THEN 'INDONESIA 6'
            ELSE "city"
        END AS "city",
        "country",
        "region",
        "phone_number",
        "street_address"
    FROM "supplier_renamed_trimmed"
)

-- COCOON BLOCK END
SELECT *
FROM "supplier_renamed_trimmed_cleaned"

version: 2
models:
- name: stg_supplier
  description: The table is about suppliers. It contains details of each supplier
    including their unique key, name, address, city, nation, region, and phone number.
    Each row represents a single supplier with their specific information. The table
    appears to be a part of a larger database system, likely used for supply chain
    management or procurement processes.
  columns:
  - name: supplier_id
    description: Unique identifier for the supplier
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents a unique identifier for each supplier.
        For this table, each row represents a single supplier, and supplier_id appears
        to be unique across rows.
      data_type:
        current_data_type: INT
  - name: supplier_name
    description: Name of the supplier
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: Supplier#\d{9}
        summary: Supplier names in the format of "Supplier#" followed by a 9-digit
          number
      data_type:
        current_data_type: VARCHAR
  - name: city
    description: City where the supplier is located
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: '[A-Z\s]{3,14}\s\d'
        summary: Country name (uppercase, potentially truncated) followed by space
          and single digit
      - regex: '[A-Z\s]{5,11}\d'
        summary: Abbreviated country names (in uppercase) followed by a single digit
      data_type:
        current_data_type: VARCHAR
  - name: country
    description: Country where the supplier is based
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: region
    description: Geographical region of the supplier
    tests:
    - not_null
    - accepted_values:
        values:
        - ASIA
        - MIDDLE EAST
        - AFRICA
        - EUROPE
        - AMERICA
    cocoon_meta:
      future_accepted_values:
      - NORTH AMERICA
      - SOUTH AMERICA
      - OCEANIA
      data_type:
        current_data_type: VARCHAR
  - name: phone_number
    description: Contact phone number for the supplier
    tests:
    - not_null
    cocoon_meta:
      patterns:
      - regex: 1[01]-\d{3}-\d{3}-\d{4}
        summary: Phone numbers in the format of XX-XXX-XXX-XXXX, where X is a digit
      - regex: \d{2}-\d{3}-\d{3}-\d{4}
        summary: Phone numbers in the format of 12-xxx-xxx-xxxx where x represents
          digits
      data_type:
        current_data_type: VARCHAR
  - name: street_address
    description: Street address of the supplier
    tests:
    - not_null
    cocoon_meta:
      unusual_values: All values are unusual because they start with commas and consist
        of random alphanumeric strings instead of typical address components.
      patterns:
      - regex: ^,[\w\s]+$
        summary: Values starting with a comma followed by alphanumeric characters
          and spaces
      - regex: ^\d+,[\w\s]+$
        summary: Values starting with digits followed by a comma and alphanumeric
          characters
      - regex: ^[\w\s,]+$
        summary: Alphanumeric strings with possible spaces and commas
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-26 00:50:02.289320+00:00
WITH 
"lineorder_renamed" AS (
    -- Rename: Renaming columns
    -- LO_ORDERKEY -> order_id
    -- LO_LINENUMBER -> line_number
    -- LO_CUSTKEY -> customer_id
    -- LO_PARTKEY -> product_id
    -- LO_SUPPKEY -> supplier_id
    -- LO_ORDERDATE -> order_date
    -- LO_ORDERPRIORITY -> order_priority
    -- LO_SHIPPRIORITY -> shipping_priority
    -- LO_QUANTITY -> quantity
    -- LO_EXTENDEDPRICE -> line_item_total
    -- LO_ORDTOTALPRICE -> order_total
    -- LO_DISCOUNT -> discount_percentage
    -- LO_REVENUE -> line_item_revenue
    -- LO_SUPPLYCOST -> supply_cost
    -- LO_TAX -> tax_percentage
    -- LO_COMMITDATE -> commit_date
    -- LO_SHIPMODE -> shipping_mode
    SELECT 
        "LO_ORDERKEY" AS "order_id",
        "LO_LINENUMBER" AS "line_number",
        "LO_CUSTKEY" AS "customer_id",
        "LO_PARTKEY" AS "product_id",
        "LO_SUPPKEY" AS "supplier_id",
        "LO_ORDERDATE" AS "order_date",
        "LO_ORDERPRIORITY" AS "order_priority",
        "LO_SHIPPRIORITY" AS "shipping_priority",
        "LO_QUANTITY" AS "quantity",
        "LO_EXTENDEDPRICE" AS "line_item_total",
        "LO_ORDTOTALPRICE" AS "order_total",
        "LO_DISCOUNT" AS "discount_percentage",
        "LO_REVENUE" AS "line_item_revenue",
        "LO_SUPPLYCOST" AS "supply_cost",
        "LO_TAX" AS "tax_percentage",
        "LO_COMMITDATE" AS "commit_date",
        "LO_SHIPMODE" AS "shipping_mode"
    FROM "memory"."main"."lineorder"
),

"lineorder_renamed_null" AS (
    -- NULL Imputation: Impute Null to Disguised Missing Values
    -- order_priority: ['4-NOT SPECIFIED']
    SELECT 
        CASE
            WHEN "order_priority" = '4-NOT SPECIFIED' THEN NULL
            ELSE "order_priority"
        END AS "order_priority",
        "supply_cost",
        "quantity",
        "line_item_total",
        "tax_percentage",
        "commit_date",
        "shipping_mode",
        "line_number",
        "shipping_priority",
        "product_id",
        "discount_percentage",
        "order_id",
        "customer_id",
        "order_total",
        "line_item_revenue",
        "order_date",
        "supplier_id"
    FROM "lineorder_renamed"
),

"lineorder_renamed_null_casted" AS (
    -- Column Type Casting: 
    -- commit_date: from INT to DATE
    -- discount_percentage: from INT to DECIMAL
    -- line_item_revenue: from INT to DECIMAL
    -- line_item_total: from INT to DECIMAL
    -- order_date: from INT to DATE
    -- order_total: from INT to DECIMAL
    -- supply_cost: from INT to DECIMAL
    -- tax_percentage: from INT to DECIMAL
    SELECT
        "order_priority",
        "quantity",
        "shipping_mode",
        "line_number",
        "shipping_priority",
        "product_id",
        "order_id",
        "customer_id",
        "supplier_id",
        strptime(CAST("commit_date" AS VARCHAR), '%Y%m%d') 
        AS "commit_date",
        CAST("discount_percentage" AS DECIMAL) 
        AS "discount_percentage",
        CAST("line_item_revenue" AS DECIMAL) 
        AS "line_item_revenue",
        CAST("line_item_total" AS DECIMAL) 
        AS "line_item_total",
        strptime(CAST("order_date" AS VARCHAR), '%Y%m%d') 
        AS "order_date",
        CAST("order_total" AS DECIMAL) 
        AS "order_total",
        CAST("supply_cost" AS DECIMAL) 
        AS "supply_cost",
        CAST("tax_percentage" AS DECIMAL) 
        AS "tax_percentage"
    FROM "lineorder_renamed_null"
)

-- COCOON BLOCK END
SELECT *
FROM "lineorder_renamed_null_casted"

version: 2
models:
- name: stg_lineorder
  description: The table is about line items in orders. It includes order details
    like order key, line number, customer key, part key, supplier key, and order date.
    It also contains shipping information, quantities, prices, discounts, and revenue.
    The table provides a comprehensive view of each line item in an order, including
    financial and logistical aspects.
  columns:
  - name: order_priority
    description: Priority level of the order
    tests:
    - accepted_values:
        values:
        - 5-LOW
        - 2-HIGH
        - 1-URGENT
        - 3-MEDIUM
    cocoon_meta:
      missing_reason: Not applicable for non-priority or standard orders.
      future_accepted_values:
      - 4-NORMAL
      data_type:
        current_data_type: VARCHAR
  - name: quantity
    description: Quantity of items ordered
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: shipping_mode
    description: Mode of shipping for the line item
    tests:
    - not_null
    - accepted_values:
        values:
        - REG AIR
        - FOB
        - SHIP
        - TRUCK
        - AIR
        - RAIL
        - MAIL
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: line_number
    description: Line item number within an order
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: shipping_priority
    description: Priority level for shipping the order
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: product_id
    description: Unique identifier for the product or part
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: order_id
    description: Unique identifier for each order
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: customer_id
    description: Unique identifier for the customer
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: supplier_id
    description: Unique identifier for the supplier
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: commit_date
    description: Committed delivery date for the line item
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
        expected_data_type: DATE
  - name: discount_percentage
    description: Discount percentage applied to the line item
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: line_item_revenue
    description: Revenue generated from the line item
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: line_item_total
    description: Total price for the line item
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: order_date
    description: Date when the order was placed
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
        expected_data_type: DATE
  - name: order_total
    description: Total price for the entire order
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: supply_cost
    description: Cost of supplying the item
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: tax_percentage
    description: Tax percentage applied to the line item
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  tests: []