-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"invoice_data_projected" AS (
    -- Projection: Selecting 17 out of 18 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "amount",
        "currency",
        "customer_id",
        "date_",
        "description",
        "discountable",
        "invoice_id",
        "is_deleted",
        "livemode",
        "period_end",
        "period_start",
        "proration",
        "quantity",
        "subscription_id",
        "subscription_item_id",
        "unit_amount"
    FROM "invoice_data"
),

"invoice_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> line_item_id
    -- date_ -> transaction_date
    -- discountable -> is_discountable
    -- livemode -> is_live_transaction
    -- period_end -> billing_period_end
    -- period_start -> billing_period_start
    -- proration -> is_prorated
    -- unit_amount -> unit_price
    SELECT 
        "id" AS "line_item_id",
        "amount",
        "currency",
        "customer_id",
        "date_" AS "transaction_date",
        "description",
        "discountable" AS "is_discountable",
        "invoice_id",
        "is_deleted",
        "livemode" AS "is_live_transaction",
        "period_end" AS "billing_period_end",
        "period_start" AS "billing_period_start",
        "proration" AS "is_prorated",
        "quantity",
        "subscription_id",
        "subscription_item_id",
        "unit_amount" AS "unit_price"
    FROM "invoice_data_projected"
),

"invoice_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- description: The problem is that 'description_here' is a placeholder value and not actual descriptive content. It appears to be the only value present in the description column, which suggests that real descriptions are missing. The correct values should be actual, meaningful descriptions of the items or entities being described. However, since we don't have access to the real descriptions, we can't provide them. 
    SELECT
        "line_item_id",
        "amount",
        "currency",
        "customer_id",
        "transaction_date",
        CASE
            WHEN "description" = '''description_here''' THEN ''''
            ELSE "description"
        END AS "description",
        "is_discountable",
        "invoice_id",
        "is_deleted",
        "is_live_transaction",
        "billing_period_end",
        "billing_period_start",
        "is_prorated",
        "quantity",
        "subscription_id",
        "subscription_item_id",
        "unit_price"
    FROM "invoice_data_projected_renamed"
),

"invoice_data_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- amount: from INT to DECIMAL
    -- billing_period_end: from VARCHAR to TIMESTAMP
    -- billing_period_start: from VARCHAR to TIMESTAMP
    -- transaction_date: from DECIMAL to DATE
    -- unit_price: from INT to DECIMAL
    SELECT
        "line_item_id",
        "currency",
        "customer_id",
        "description",
        "is_discountable",
        "invoice_id",
        "is_deleted",
        "is_live_transaction",
        "is_prorated",
        "quantity",
        "subscription_id",
        "subscription_item_id",
        CAST("amount" AS DECIMAL) AS "amount",
        CAST("billing_period_end" AS TIMESTAMP) AS "billing_period_end",
        CAST("billing_period_start" AS TIMESTAMP) AS "billing_period_start",
        CAST("transaction_date" AS DATE) AS "transaction_date",
        CAST("unit_price" AS DECIMAL) AS "unit_price"
    FROM "invoice_data_projected_renamed_cleaned"
)

-- COCOON BLOCK END
SELECT * FROM "invoice_data_projected_renamed_cleaned_casted"

version: 2
models:
- name: stg_invoice_data
  description: The table is about invoice data. It contains details such as invoice
    ID, amount, currency, customer ID, date, description, and subscription information.
    The table includes fields for discounts, proration, quantity, and unit amount.
    It also tracks whether the invoice is deleted or in livemode. The data seems to
    represent individual line items on invoices, with information about the associated
    subscription and billing period.
  columns:
  - name: line_item_id
    description: Unique identifier for the line item
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each line item. For
        this table, each row represents a single line item on an invoice. line_item_id
        is likely to be unique across rows.
  - name: currency
    description: Currency of the amount charged
    tests:
    - not_null
    - accepted_values:
        values:
        - usd
        - eur
        - gbp
        - jpy
        - cad
        - aud
        - chf
        - cny
        - hkd
        - nzd
        - krw
        - sgd
        - inr
        - mxn
        - brl
        - sek
        - nok
        - dkk
        - pln
        - zar
        - thb
        - rub
        - try
  - name: customer_id
    description: Unique identifier for the customer
    tests:
    - not_null
  - name: description
    description: Description of the line item
    tests:
    - not_null
  - name: is_discountable
    description: Whether the item is eligible for discounts
    tests:
    - not_null
  - name: invoice_id
    description: Unique identifier for the invoice
    tests:
    - not_null
  - name: is_deleted
    description: Indicates if the item has been deleted
    tests:
    - not_null
  - name: is_live_transaction
    description: Indicates if the transaction is live or test
    tests:
    - not_null
  - name: is_prorated
    description: Indicates if the amount is prorated
    tests:
    - not_null
  - name: quantity
    description: Quantity of the item
    tests:
    - not_null
  - name: subscription_id
    description: Unique identifier for the subscription
    tests:
    - not_null
  - name: subscription_item_id
    description: Unique identifier for the subscription item
    tests:
    - not_null
  - name: amount
    description: Amount charged for the line item
    tests:
    - not_null
  - name: billing_period_end
    description: End date of the billing period
    tests:
    - not_null
  - name: billing_period_start
    description: Start date of the billing period
    tests:
    - not_null
  - name: transaction_date
    description: Date of the invoice or transaction
    cocoon_meta:
      missing_acceptable: Not applicable for non-live transactions or future-dated
        invoices.
  - name: unit_price
    description: Price per unit of the item
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"charge_data_projected" AS (
    -- Projection: Selecting 53 out of 54 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "amount",
        "amount_refunded",
        "application",
        "application_fee_amount",
        "balance_transaction_id",
        "bank_account_id",
        "captured",
        "card_id",
        "connected_account_id",
        "created",
        "currency",
        "customer_id",
        "description",
        "destination",
        "failure_code",
        "failure_message",
        "fraud_details_stripe_report",
        "fraud_details_user_report",
        "invoice_id",
        "livemode",
        "metadata",
        "on_behalf_of",
        "outcome_network_status",
        "outcome_reason",
        "outcome_risk_level",
        "outcome_risk_score",
        "outcome_seller_message",
        "outcome_type",
        "paid",
        "payment_intent_id",
        "receipt_email",
        "receipt_number",
        "receipt_url",
        "refunded",
        "shipping_address_city",
        "shipping_address_country",
        "shipping_address_line_1",
        "shipping_address_line_2",
        "shipping_address_postal_code",
        "shipping_address_state",
        "shipping_carrier",
        "shipping_name",
        "shipping_phone",
        "shipping_tracking_number",
        "source_id",
        "source_transfer",
        "statement_descriptor",
        "status",
        "transfer_data_destination",
        "transfer_group",
        "transfer_id",
        "calculated_statement_descriptor"
    FROM "charge_data"
),

"charge_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> charge_id
    -- amount -> amount_cents
    -- amount_refunded -> refunded_cents
    -- application_fee_amount -> app_fee_amount
    -- captured -> is_captured
    -- created -> created_at
    -- fraud_details_stripe_report -> stripe_fraud_report
    -- fraud_details_user_report -> user_fraud_report
    -- livemode -> is_live
    -- outcome_network_status -> network_status
    -- outcome_risk_level -> risk_level
    -- outcome_risk_score -> risk_score
    -- outcome_seller_message -> seller_message
    -- paid -> is_paid
    -- receipt_email -> hashed_receipt_email
    -- refunded -> is_refunded
    -- shipping_address_city -> shipping_city
    -- shipping_address_country -> shipping_country
    -- shipping_address_line_1 -> shipping_address_1
    -- shipping_address_line_2 -> shipping_address_2
    -- shipping_address_postal_code -> shipping_postal_code
    -- shipping_address_state -> shipping_state
    -- shipping_name -> shipping_recipient_name
    -- status -> charge_status
    -- transfer_data_destination -> transfer_destination
    SELECT 
        "id" AS "charge_id",
        "amount" AS "amount_cents",
        "amount_refunded" AS "refunded_cents",
        "application",
        "application_fee_amount" AS "app_fee_amount",
        "balance_transaction_id",
        "bank_account_id",
        "captured" AS "is_captured",
        "card_id",
        "connected_account_id",
        "created" AS "created_at",
        "currency",
        "customer_id",
        "description",
        "destination",
        "failure_code",
        "failure_message",
        "fraud_details_stripe_report" AS "stripe_fraud_report",
        "fraud_details_user_report" AS "user_fraud_report",
        "invoice_id",
        "livemode" AS "is_live",
        "metadata",
        "on_behalf_of",
        "outcome_network_status" AS "network_status",
        "outcome_reason",
        "outcome_risk_level" AS "risk_level",
        "outcome_risk_score" AS "risk_score",
        "outcome_seller_message" AS "seller_message",
        "outcome_type",
        "paid" AS "is_paid",
        "payment_intent_id",
        "receipt_email" AS "hashed_receipt_email",
        "receipt_number",
        "receipt_url",
        "refunded" AS "is_refunded",
        "shipping_address_city" AS "shipping_city",
        "shipping_address_country" AS "shipping_country",
        "shipping_address_line_1" AS "shipping_address_1",
        "shipping_address_line_2" AS "shipping_address_2",
        "shipping_address_postal_code" AS "shipping_postal_code",
        "shipping_address_state" AS "shipping_state",
        "shipping_carrier",
        "shipping_name" AS "shipping_recipient_name",
        "shipping_phone",
        "shipping_tracking_number",
        "source_id",
        "source_transfer",
        "statement_descriptor",
        "status" AS "charge_status",
        "transfer_data_destination" AS "transfer_destination",
        "transfer_group",
        "transfer_id",
        "calculated_statement_descriptor"
    FROM "charge_data_projected"
),

"charge_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- app_fee_amount: from DECIMAL to VARCHAR
    -- balance_transaction_id: from DECIMAL to VARCHAR
    -- bank_account_id: from DECIMAL to VARCHAR
    -- connected_account_id: from DECIMAL to VARCHAR
    -- created_at: from VARCHAR to TIMESTAMP
    -- destination: from DECIMAL to VARCHAR
    -- invoice_id: from DECIMAL to VARCHAR
    -- metadata: from VARCHAR to JSON
    -- on_behalf_of: from DECIMAL to VARCHAR
    -- receipt_number: from DECIMAL to VARCHAR
    -- receipt_url: from DECIMAL to VARCHAR
    -- risk_score: from DECIMAL to VARCHAR
    -- shipping_address_1: from DECIMAL to VARCHAR
    -- shipping_address_2: from DECIMAL to VARCHAR
    -- shipping_carrier: from DECIMAL to VARCHAR
    -- shipping_city: from DECIMAL to VARCHAR
    -- shipping_country: from DECIMAL to VARCHAR
    -- shipping_phone: from DECIMAL to VARCHAR
    -- shipping_postal_code: from DECIMAL to VARCHAR
    -- shipping_recipient_name: from DECIMAL to VARCHAR
    -- shipping_state: from DECIMAL to VARCHAR
    -- shipping_tracking_number: from DECIMAL to VARCHAR
    -- source_id: from DECIMAL to VARCHAR
    -- source_transfer: from DECIMAL to VARCHAR
    -- statement_descriptor: from DECIMAL to VARCHAR
    -- stripe_fraud_report: from DECIMAL to VARCHAR
    -- transfer_destination: from DECIMAL to VARCHAR
    -- transfer_group: from DECIMAL to VARCHAR
    -- transfer_id: from DECIMAL to VARCHAR
    -- user_fraud_report: from DECIMAL to VARCHAR
    SELECT
        "charge_id",
        "amount_cents",
        "refunded_cents",
        "application",
        "is_captured",
        "card_id",
        "currency",
        "customer_id",
        "description",
        "failure_code",
        "failure_message",
        "is_live",
        "network_status",
        "outcome_reason",
        "risk_level",
        "seller_message",
        "outcome_type",
        "is_paid",
        "payment_intent_id",
        "hashed_receipt_email",
        "is_refunded",
        "charge_status",
        "calculated_statement_descriptor",
        CAST("app_fee_amount" AS VARCHAR) AS "app_fee_amount",
        CAST("balance_transaction_id" AS VARCHAR) AS "balance_transaction_id",
        CAST("bank_account_id" AS VARCHAR) AS "bank_account_id",
        CAST("connected_account_id" AS VARCHAR) AS "connected_account_id",
        CAST("created_at" AS TIMESTAMP) AS "created_at",
        CAST("destination" AS VARCHAR) AS "destination",
        CAST("invoice_id" AS VARCHAR) AS "invoice_id",
        CAST("metadata" AS JSON) AS "metadata",
        CAST("on_behalf_of" AS VARCHAR) AS "on_behalf_of",
        CAST("receipt_number" AS VARCHAR) AS "receipt_number",
        CAST("receipt_url" AS VARCHAR) AS "receipt_url",
        CAST("risk_score" AS VARCHAR) AS "risk_score",
        CAST("shipping_address_1" AS VARCHAR) AS "shipping_address_1",
        CAST("shipping_address_2" AS VARCHAR) AS "shipping_address_2",
        CAST("shipping_carrier" AS VARCHAR) AS "shipping_carrier",
        CAST("shipping_city" AS VARCHAR) AS "shipping_city",
        CAST("shipping_country" AS VARCHAR) AS "shipping_country",
        CAST("shipping_phone" AS VARCHAR) AS "shipping_phone",
        CAST("shipping_postal_code" AS VARCHAR) AS "shipping_postal_code",
        CAST("shipping_recipient_name" AS VARCHAR) AS "shipping_recipient_name",
        CAST("shipping_state" AS VARCHAR) AS "shipping_state",
        CAST("shipping_tracking_number" AS VARCHAR) AS "shipping_tracking_number",
        CAST("source_id" AS VARCHAR) AS "source_id",
        CAST("source_transfer" AS VARCHAR) AS "source_transfer",
        CAST("statement_descriptor" AS VARCHAR) AS "statement_descriptor",
        CAST("stripe_fraud_report" AS VARCHAR) AS "stripe_fraud_report",
        CAST("transfer_destination" AS VARCHAR) AS "transfer_destination",
        CAST("transfer_group" AS VARCHAR) AS "transfer_group",
        CAST("transfer_id" AS VARCHAR) AS "transfer_id",
        CAST("user_fraud_report" AS VARCHAR) AS "user_fraud_report"
    FROM "charge_data_projected_renamed"
),

"charge_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 16 columns with unacceptable missing values
    -- app_fee_amount has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- application has 93.75 percent missing. Strategy: 🗑️ Drop Column
    -- balance_transaction_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- bank_account_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- connected_account_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- destination has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- on_behalf_of has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- risk_score has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- source_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- source_transfer has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- statement_descriptor has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- stripe_fraud_report has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- transfer_destination has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- transfer_group has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- transfer_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- user_fraud_report has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "charge_id",
        "amount_cents",
        "refunded_cents",
        "is_captured",
        "card_id",
        "currency",
        "customer_id",
        "description",
        "failure_code",
        "failure_message",
        "is_live",
        "network_status",
        "outcome_reason",
        "risk_level",
        "seller_message",
        "outcome_type",
        "is_paid",
        "payment_intent_id",
        "hashed_receipt_email",
        "is_refunded",
        "charge_status",
        "calculated_statement_descriptor",
        "created_at",
        "invoice_id",
        "metadata",
        "receipt_number",
        "receipt_url",
        "shipping_address_1",
        "shipping_address_2",
        "shipping_carrier",
        "shipping_city",
        "shipping_country",
        "shipping_phone",
        "shipping_postal_code",
        "shipping_recipient_name",
        "shipping_state",
        "shipping_tracking_number"
    FROM "charge_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "charge_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_charge_data
  description: The table is about charge transactions. It contains details like amount,
    currency, customer ID, card details, and transaction status. Most charges failed
    due to card declines. The table includes information on the reason for failure,
    risk assessment, and metadata. All charges are in USD and were made on February
    18, 2021.
  columns:
  - name: charge_id
    description: Unique identifier for the charge
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each charge transaction.
        For this table, each row represents a distinct charge transaction. The charge_id
        is unique across rows.
  - name: amount_cents
    description: Transaction amount in cents
    tests:
    - not_null
  - name: refunded_cents
    description: Amount refunded in cents
    tests:
    - not_null
  - name: is_captured
    description: Whether the charge was captured
    tests:
    - not_null
  - name: card_id
    description: ID of the card used
    tests:
    - not_null
  - name: currency
    description: Currency of the charge
    tests:
    - not_null
    - accepted_values:
        values:
        - usd
        - eur
        - gbp
        - jpy
        - cad
        - aud
        - chf
        - cny
        - hkd
        - nzd
        - sek
        - krw
        - sgd
        - nok
        - mxn
        - inr
        - rub
        - zar
        - try
        - brl
        - twd
        - dkk
        - pln
        - thb
        - idr
  - name: customer_id
    description: ID of the customer
    tests:
    - not_null
  - name: description
    description: Description of the charge
    tests:
    - not_null
  - name: failure_code
    description: Code indicating reason for failure
    tests:
    - not_null
    - accepted_values:
        values:
        - card_declined
        - insufficient_funds
        - expired_card
        - invalid_card_number
        - invalid_cvc
        - incorrect_cvc
        - lost_card
        - stolen_card
        - fraudulent_card
        - processing_error
        - issuer_not_available
        - do_not_honor
        - card_not_supported
        - currency_not_supported
        - duplicate_transaction
        - authentication_required
        - blocked_card
        - restricted_card
        - card_velocity_exceeded
        - withdrawal_count_limit_exceeded
  - name: failure_message
    description: Message describing the failure
    tests:
    - not_null
  - name: is_live
    description: Whether charge was in live mode
    tests:
    - not_null
  - name: network_status
    description: Network status of the charge outcome
    tests:
    - not_null
    - accepted_values:
        values:
        - declined_by_network
        - approved
        - declined_by_issuer
        - network_error
        - timeout
        - invalid_card
        - insufficient_funds
        - lost_or_stolen_card
        - suspected_fraud
        - expired_card
        - processing_error
        - card_not_supported
        - card_restricted
        - do_not_honor
        - pickup_card
  - name: outcome_reason
    description: Reason for the charge outcome
    tests:
    - not_null
    - accepted_values:
        values:
        - do_not_honor
        - insufficient_funds
        - lost_card
        - stolen_card
        - expired_card
        - invalid_card_number
        - invalid_cvc
        - card_not_supported
        - processing_error
        - fraudulent
        - generic_decline
        - card_velocity_exceeded
        - suspected_fraud
        - card_restricted
        - currency_not_supported
        - approved
        - partially_approved
        - authentication_required
        - incorrect_address
        - transaction_not_allowed
  - name: risk_level
    description: Risk level of the charge outcome
    tests:
    - not_null
    - accepted_values:
        values:
        - low
        - normal
        - medium
        - high
        - critical
  - name: seller_message
    description: Seller message for the charge outcome
    tests:
    - not_null
    - accepted_values:
        values:
        - do_not_honor
        - insufficient_funds
        - lost_card
        - stolen_card
        - expired_card
        - invalid_card_number
        - invalid_cvc
        - card_declined
        - processing_error
        - incorrect_pin
        - incorrect_zip
        - invalid_expiry_date
        - card_not_supported
        - fraudulent
        - pickup_card
        - restricted_card
        - security_violation
        - service_not_allowed
        - transaction_not_allowed
        - try_again_later
        - The bank returned the decline code `do_not_honor`.
  - name: outcome_type
    description: Type of the charge outcome
    tests:
    - not_null
    - accepted_values:
        values:
        - issuer_declined
        - approved
        - blocked
        - expired_card
        - insufficient_funds
        - lost_card
        - stolen_card
        - invalid_amount
        - invalid_number
        - fraud_suspect
        - processor_declined
        - authentication_required
        - card_not_supported
        - do_not_honor
        - duplicate_transaction
        - generic_decline
  - name: is_paid
    description: Indicates if the charge was successful
    tests:
    - not_null
  - name: payment_intent_id
    description: Unique identifier for the payment intent
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for the payment intent
        associated with each charge. While it's likely to be unique for each transaction
        in this table, it's possible that multiple charges could be associated with
        the same payment intent in some cases.
  - name: hashed_receipt_email
    description: Hashed email address for receipt
    tests:
    - not_null
  - name: is_refunded
    description: Indicates if the charge was refunded
    tests:
    - not_null
  - name: charge_status
    description: Current status of the charge
    tests:
    - not_null
    - accepted_values:
        values:
        - failed
        - succeeded
        - pending
        - refunded
        - canceled
        - disputed
        - authorized
        - captured
        - expired
        - requires_payment_method
        - requires_confirmation
        - requires_action
        - processing
  - name: calculated_statement_descriptor
    description: ''
    tests:
    - not_null
  - name: created_at
    description: Timestamp of charge creation
    tests:
    - not_null
  - name: invoice_id
    description: ID of associated invoice
    cocoon_meta:
      missing_acceptable: Failed transactions don't generate invoices
  - name: metadata
    description: Additional metadata for the charge
    tests:
    - not_null
  - name: receipt_number
    description: Receipt number if available
    cocoon_meta:
      missing_acceptable: Failed transactions don't generate receipts
  - name: receipt_url
    description: URL to access the receipt
    cocoon_meta:
      missing_acceptable: Failed transactions don't generate receipts
  - name: shipping_address_1
    description: First line of shipping address
    cocoon_meta:
      missing_acceptable: No shipping involved in this transaction
  - name: shipping_address_2
    description: Second line of shipping address
    cocoon_meta:
      missing_acceptable: No shipping involved in this transaction
  - name: shipping_carrier
    description: Name of shipping carrier
    cocoon_meta:
      missing_acceptable: No shipping involved in this transaction
  - name: shipping_city
    description: City in shipping address
    cocoon_meta:
      missing_acceptable: No shipping involved in this transaction
  - name: shipping_country
    description: Country in shipping address
    cocoon_meta:
      missing_acceptable: No shipping involved in this transaction
  - name: shipping_phone
    description: Phone number for shipping
    cocoon_meta:
      missing_acceptable: No shipping involved in this transaction
  - name: shipping_postal_code
    description: Postal code in shipping address
    cocoon_meta:
      missing_acceptable: No shipping involved in this transaction
  - name: shipping_recipient_name
    description: Name of recipient for shipping
    cocoon_meta:
      missing_acceptable: No shipping involved in this transaction
  - name: shipping_state
    description: State in shipping address
    cocoon_meta:
      missing_acceptable: No shipping involved in this transaction
  - name: shipping_tracking_number
    description: Tracking number for shipment
    cocoon_meta:
      missing_acceptable: No shipping involved in this transaction

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"price_data_projected" AS (
    -- Projection: Selecting 22 out of 23 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "active",
        "billing_scheme",
        "created",
        "currency",
        "invoice_item_id",
        "is_deleted",
        "livemode",
        "lookup_key",
        "metadata",
        "nickname",
        "product_id",
        "recurring_aggregate_usage",
        "recurring_interval",
        "recurring_interval_count",
        "recurring_usage_type",
        "tiers_mode",
        "transform_quantity_divide_by",
        "transform_quantity_round",
        "type",
        "unit_amount",
        "unit_amount_decimal"
    FROM "price_data"
),

"price_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> price_id
    -- active -> is_active
    -- created -> creation_date
    -- currency -> currency_code
    -- livemode -> is_live_mode
    -- nickname -> friendly_name
    -- recurring_aggregate_usage -> recurring_usage_aggregation
    -- transform_quantity_divide_by -> quantity_divide_by
    -- transform_quantity_round -> quantity_round
    -- type -> price_type
    SELECT 
        "id" AS "price_id",
        "active" AS "is_active",
        "billing_scheme",
        "created" AS "creation_date",
        "currency" AS "currency_code",
        "invoice_item_id",
        "is_deleted",
        "livemode" AS "is_live_mode",
        "lookup_key",
        "metadata",
        "nickname" AS "friendly_name",
        "product_id",
        "recurring_aggregate_usage" AS "recurring_usage_aggregation",
        "recurring_interval",
        "recurring_interval_count",
        "recurring_usage_type",
        "tiers_mode",
        "transform_quantity_divide_by" AS "quantity_divide_by",
        "transform_quantity_round" AS "quantity_round",
        "type" AS "price_type",
        "unit_amount",
        "unit_amount_decimal"
    FROM "price_data_projected"
),

"price_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- creation_date: from VARCHAR to TIMESTAMP
    -- friendly_name: from DECIMAL to VARCHAR
    -- lookup_key: from DECIMAL to VARCHAR
    -- metadata: from VARCHAR to JSON
    -- quantity_divide_by: from DECIMAL to VARCHAR
    -- quantity_round: from DECIMAL to VARCHAR
    -- recurring_interval: from DECIMAL to VARCHAR
    -- recurring_interval_count: from DECIMAL to VARCHAR
    -- recurring_usage_aggregation: from DECIMAL to VARCHAR
    -- recurring_usage_type: from DECIMAL to VARCHAR
    -- tiers_mode: from DECIMAL to VARCHAR
    -- unit_amount_decimal: from INT to DECIMAL
    SELECT
        "price_id",
        "is_active",
        "billing_scheme",
        "currency_code",
        "invoice_item_id",
        "is_deleted",
        "is_live_mode",
        "product_id",
        "price_type",
        "unit_amount",
        CAST("creation_date" AS TIMESTAMP) AS "creation_date",
        CAST("friendly_name" AS VARCHAR) AS "friendly_name",
        CAST("lookup_key" AS VARCHAR) AS "lookup_key",
        CAST("metadata" AS JSON) AS "metadata",
        CAST("quantity_divide_by" AS VARCHAR) AS "quantity_divide_by",
        CAST("quantity_round" AS VARCHAR) AS "quantity_round",
        CAST("recurring_interval" AS VARCHAR) AS "recurring_interval",
        CAST("recurring_interval_count" AS VARCHAR) AS "recurring_interval_count",
        CAST("recurring_usage_aggregation" AS VARCHAR) AS "recurring_usage_aggregation",
        CAST("recurring_usage_type" AS VARCHAR) AS "recurring_usage_type",
        CAST("tiers_mode" AS VARCHAR) AS "tiers_mode",
        CAST("unit_amount_decimal" AS DECIMAL(10,2)) AS "unit_amount_decimal"
    FROM "price_data_projected_renamed"
),

"price_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 4 columns with unacceptable missing values
    -- friendly_name has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- lookup_key has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- quantity_divide_by has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- quantity_round has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "price_id",
        "is_active",
        "billing_scheme",
        "currency_code",
        "invoice_item_id",
        "is_deleted",
        "is_live_mode",
        "product_id",
        "price_type",
        "unit_amount",
        "creation_date",
        "metadata",
        "recurring_interval",
        "recurring_interval_count",
        "recurring_usage_aggregation",
        "recurring_usage_type",
        "tiers_mode",
        "unit_amount_decimal"
    FROM "price_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "price_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_price_data
  description: The table is about pricing information. It includes details like price
    ID, active status, billing scheme, currency, and amount. The table also contains
    fields related to recurring pricing, such as interval and usage type. Additional
    information includes creation date, associated product ID, and metadata. The table
    seems to store comprehensive pricing data for various products or services.
  columns:
  - name: price_id
    description: Unique identifier for the price
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is a unique identifier for each price. For this table,
        each row represents a distinct price entry. price_id is unique across rows
        as it's designed to be a primary identifier.
  - name: is_active
    description: Indicates if the price is currently active
    tests:
    - not_null
  - name: billing_scheme
    description: The pricing model for the product
    tests:
    - not_null
    - accepted_values:
        values:
        - per_unit
        - tiered
  - name: currency_code
    description: The currency code for the price
    tests:
    - not_null
  - name: invoice_item_id
    description: Associated invoice item identifier
    tests:
    - not_null
  - name: is_deleted
    description: Indicates if the price has been deleted
    tests:
    - not_null
  - name: is_live_mode
    description: Whether the price is in live or test mode
    tests:
    - not_null
  - name: product_id
    description: Identifier of the associated product
    tests:
    - not_null
  - name: price_type
    description: The type of pricing (one-time or recurring)
    tests:
    - not_null
    - accepted_values:
        values:
        - one_time
        - recurring
  - name: unit_amount
    description: Price amount in smallest currency unit
    tests:
    - not_null
  - name: creation_date
    description: Timestamp of when the price was created
    tests:
    - not_null
  - name: metadata
    description: Additional information about the price
    tests:
    - not_null
  - name: recurring_interval
    description: Billing frequency for recurring prices
    cocoon_meta:
      missing_acceptable: Not applicable for one-time pricing
  - name: recurring_interval_count
    description: Number of intervals between billings
    cocoon_meta:
      missing_acceptable: Not applicable for one-time pricing
  - name: recurring_usage_aggregation
    description: Usage aggregation for recurring prices
    cocoon_meta:
      missing_acceptable: Not applicable for one-time pricing
  - name: recurring_usage_type
    description: Usage calculation for recurring prices
    cocoon_meta:
      missing_acceptable: Not applicable for one-time pricing
  - name: tiers_mode
    description: Defines how tiers are handled
    cocoon_meta:
      missing_acceptable: Not applicable for per_unit billing scheme
  - name: unit_amount_decimal
    description: Decimal representation of unit amount
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"fee_data_projected" AS (
    -- Projection: Selecting 8 out of 9 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "balance_transaction_id",
        "index_",
        "amount",
        "application",
        "connected_account_id",
        "currency",
        "description",
        "type"
    FROM "fee_data"
),

"fee_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- balance_transaction_id -> transaction_id
    -- index_ -> group_index
    -- amount -> amount_cents
    -- currency -> currency_code
    -- description -> category_hash
    -- type -> type_hash
    SELECT 
        "balance_transaction_id" AS "transaction_id",
        "index_" AS "group_index",
        "amount" AS "amount_cents",
        "application",
        "connected_account_id",
        "currency" AS "currency_code",
        "description" AS "category_hash",
        "type" AS "type_hash"
    FROM "fee_data_projected"
),

"fee_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- application: from DECIMAL to VARCHAR
    -- connected_account_id: from DECIMAL to VARCHAR
    SELECT
        "transaction_id",
        "group_index",
        "amount_cents",
        "currency_code",
        "category_hash",
        "type_hash",
        CAST("application" AS VARCHAR) AS "application",
        CAST("connected_account_id" AS VARCHAR) AS "connected_account_id"
    FROM "fee_data_projected_renamed"
),

"fee_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 2 columns with unacceptable missing values
    -- application has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- connected_account_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "transaction_id",
        "group_index",
        "amount_cents",
        "currency_code",
        "category_hash",
        "type_hash"
    FROM "fee_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "fee_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_fee_data
  description: The table is about fee transactions. It includes details such as the
    transaction ID, amount, currency, description, and type. All transactions are
    in USD. The amounts vary significantly, from 99 to 22215. Each transaction has
    a unique balance_transaction_id. The description and type fields contain consistent
    values across all entries, suggesting they might be category identifiers.
  columns:
  - name: transaction_id
    description: Unique identifier for the transaction
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each transaction.
        For this table, each row represents a distinct fee transaction. The transaction_id
        appears to be unique across rows, as each sample has a different value.
  - name: group_index
    description: Constant value, possibly for grouping
    tests:
    - not_null
  - name: amount_cents
    description: Transaction amount in cents
    tests:
    - not_null
  - name: currency_code
    description: Currency of the transaction
    tests:
    - not_null
  - name: category_hash
    description: Identifier or hash for transaction category
    tests:
    - not_null
  - name: type_hash
    description: Identifier or hash for transaction type
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"credit_note_line_item_data_projected" AS (
    -- Projection: Selecting 10 out of 11 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "credit_note_id",
        "id",
        "amount",
        "discount_amount",
        "description",
        "livemode",
        "quantity",
        "type",
        "unit_amount",
        "unit_amount_decimal"
    FROM "credit_note_line_item_data"
),

"credit_note_line_item_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> line_item_id
    -- amount -> total_amount
    -- description -> item_description
    -- livemode -> is_live_mode
    -- type -> line_item_type
    -- unit_amount -> unit_price_cents
    -- unit_amount_decimal -> unit_price_precise
    SELECT 
        "credit_note_id",
        "id" AS "line_item_id",
        "amount" AS "total_amount",
        "discount_amount",
        "description" AS "item_description",
        "livemode" AS "is_live_mode",
        "quantity",
        "type" AS "line_item_type",
        "unit_amount" AS "unit_price_cents",
        "unit_amount_decimal" AS "unit_price_precise"
    FROM "credit_note_line_item_data_projected"
),

"credit_note_line_item_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- item_description: The problem is that 'description_here' is a placeholder value and not a real item description. This is unusual because it doesn't provide any actual information about the item. The correct value should be an empty string, as we don't have the real description and leaving it blank is more appropriate than using a placeholder. 
    SELECT
        "credit_note_id",
        "line_item_id",
        "total_amount",
        "discount_amount",
        CASE
            WHEN "item_description" = '''description_here''' THEN ''''
            ELSE "item_description"
        END AS "item_description",
        "is_live_mode",
        "quantity",
        "line_item_type",
        "unit_price_cents",
        "unit_price_precise"
    FROM "credit_note_line_item_data_projected_renamed"
),

"credit_note_line_item_data_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- discount_amount: from INT to DECIMAL
    -- total_amount: from INT to DECIMAL
    -- unit_price_precise: from INT to DECIMAL
    SELECT
        "credit_note_id",
        "line_item_id",
        "item_description",
        "is_live_mode",
        "quantity",
        "line_item_type",
        "unit_price_cents",
        CAST("discount_amount" AS DECIMAL) AS "discount_amount",
        CAST("total_amount" AS DECIMAL) AS "total_amount",
        CAST("unit_price_precise" AS DECIMAL) AS "unit_price_precise"
    FROM "credit_note_line_item_data_projected_renamed_cleaned"
)

-- COCOON BLOCK END
SELECT * FROM "credit_note_line_item_data_projected_renamed_cleaned_casted"

version: 2
models:
- name: stg_credit_note_line_item_data
  description: The table is about credit note line items. It contains details of individual
    items within a credit note. Each row represents a line item with information like
    amount, quantity, unit price, and description. The credit_note_id links the line
    item to its parent credit note. The table includes both custom line items and
    invoice line items. It also tracks whether each item is in live mode or not.
  columns:
  - name: credit_note_id
    description: Unique identifier of the parent credit note
    tests:
    - not_null
  - name: line_item_id
    description: Unique identifier for the line item
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the unique identifier for each line item.
        For this table, each row is a distinct line item. line_item_id should be unique
        across rows as it's designed to identify each specific line item.
  - name: item_description
    description: Brief description of the line item
    tests:
    - not_null
  - name: is_live_mode
    description: Indicates if the item is in live mode
    tests:
    - not_null
  - name: quantity
    description: Number of units for the line item
    tests:
    - not_null
  - name: line_item_type
    description: Type of line item (custom or invoice)
    tests:
    - not_null
    - accepted_values:
        values:
        - custom_line_item
        - invoice_line_item
  - name: unit_price_cents
    description: Price per unit in cents
    tests:
    - not_null
  - name: discount_amount
    description: Amount of discount applied to the line item
    tests:
    - not_null
  - name: total_amount
    description: Total amount for the line item
    tests:
    - not_null
  - name: unit_price_precise
    description: Precise unit amount with decimal places
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"account_data_projected" AS (
    -- Projection: Selecting 110 out of 111 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "business_profile_mcc",
        "business_profile_name",
        "business_profile_product_description",
        "business_profile_support_address_city",
        "business_profile_support_address_country",
        "business_profile_support_address_line_1",
        "business_profile_support_address_line_2",
        "business_profile_support_address_postal_code",
        "business_profile_support_address_state",
        "business_profile_support_email",
        "business_profile_support_phone",
        "business_profile_support_url",
        "business_profile_url",
        "business_type",
        "capabilities_afterpay_clearpay_payments",
        "capabilities_au_becs_debit_payments",
        "capabilities_bacs_debit_payments",
        "capabilities_bancontact_payments",
        "capabilities_card_issuing",
        "capabilities_card_payments",
        "capabilities_cartes_bancaires_payments",
        "capabilities_eps_payments",
        "capabilities_fpx_payments",
        "capabilities_giropay_payments",
        "capabilities_grabpay_payments",
        "capabilities_ideal_payments",
        "capabilities_jcb_payments",
        "capabilities_legacy_payments",
        "capabilities_oxxo_payments",
        "capabilities_p_24_payments",
        "capabilities_platform_payments",
        "capabilities_sepa_debit_payments",
        "capabilities_sofort_payments",
        "capabilities_tax_reporting_us_1099_k",
        "capabilities_tax_reporting_us_1099_misc",
        "capabilities_transfers",
        "charges_enabled",
        "company_address_city",
        "company_address_country",
        "company_address_kana_city",
        "company_address_kana_country",
        "company_address_kana_line_1",
        "company_address_kana_line_2",
        "company_address_kana_postal_code",
        "company_address_kana_state",
        "company_address_kana_town",
        "company_address_kanji_city",
        "company_address_kanji_country",
        "company_address_kanji_line_1",
        "company_address_kanji_line_2",
        "company_address_kanji_postal_code",
        "company_address_kanji_state",
        "company_address_kanji_town",
        "company_address_line_1",
        "company_address_line_2",
        "company_address_postal_code",
        "company_address_state",
        "company_directors_provided",
        "company_executives_provided",
        "company_name",
        "company_name_kana",
        "company_name_kanji",
        "company_owners_provided",
        "company_phone",
        "company_structure",
        "company_tax_id_provided",
        "company_tax_id_registrar",
        "company_vat_id_provided",
        "company_verification_document_back",
        "company_verification_document_details",
        "company_verification_document_details_code",
        "company_verification_document_front",
        "country",
        "created",
        "default_currency",
        "details_submitted",
        "email",
        "individual_id",
        "is_deleted",
        "metadata",
        "payouts_enabled",
        "requirements_current_deadline",
        "requirements_currently_due",
        "requirements_disabled_reason",
        "requirements_errors",
        "requirements_eventually_due",
        "requirements_past_due",
        "requirements_pending_verification",
        "settings_branding_icon",
        "settings_branding_logo",
        "settings_branding_primary_color",
        "settings_card_payments_decline_on_avs_failure",
        "settings_card_payments_decline_on_cvc_failure",
        "settings_card_payments_statement_descriptor_prefix",
        "settings_dashboard_display_name",
        "settings_dashboard_timezone",
        "settings_payments_statement_descriptor",
        "settings_payments_statement_descriptor_kana",
        "settings_payments_statement_descriptor_kanji",
        "settings_payouts_debit_negative_balances",
        "settings_payouts_schedule_delay_days",
        "settings_payouts_schedule_interval",
        "settings_payouts_schedule_monthly_anchor",
        "settings_payouts_schedule_weekly_anchor",
        "settings_payouts_statement_descriptor",
        "tos_acceptance_date",
        "tos_acceptance_ip",
        "tos_acceptance_user_agent",
        "type"
    FROM "account_data"
),

"account_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> account_id
    -- business_profile_mcc -> merchant_category_code
    -- business_profile_name -> business_name
    -- business_profile_product_description -> product_description
    -- business_profile_support_address_city -> support_city
    -- business_profile_support_address_country -> support_country
    -- business_profile_support_address_line_1 -> support_address_line1
    -- business_profile_support_address_line_2 -> support_address_line2
    -- business_profile_support_address_postal_code -> support_postal_code
    -- business_profile_support_address_state -> support_state
    -- business_profile_support_email -> support_email
    -- business_profile_support_phone -> support_phone
    -- business_profile_support_url -> support_url
    -- business_profile_url -> business_url
    -- capabilities_afterpay_clearpay_payments -> afterpay_clearpay_status
    -- capabilities_au_becs_debit_payments -> au_becs_debit_status
    -- capabilities_bacs_debit_payments -> bacs_debit_status
    -- capabilities_bancontact_payments -> bancontact_status
    -- capabilities_card_issuing -> card_issuing_status
    -- capabilities_card_payments -> card_payments_status
    -- capabilities_cartes_bancaires_payments -> cartes_bancaires_status
    -- capabilities_eps_payments -> eps_status
    -- capabilities_fpx_payments -> fpx_status
    -- capabilities_giropay_payments -> giropay_status
    -- capabilities_grabpay_payments -> grabpay_status
    -- capabilities_ideal_payments -> ideal_status
    -- capabilities_jcb_payments -> jcb_status
    -- capabilities_legacy_payments -> legacy_payments_status
    -- capabilities_oxxo_payments -> oxxo_status
    -- capabilities_p_24_payments -> p24_status
    -- capabilities_platform_payments -> platform_payments_status
    -- capabilities_sepa_debit_payments -> sepa_debit_payments_status
    -- capabilities_sofort_payments -> sofort_payments_status
    -- capabilities_tax_reporting_us_1099_k -> tax_reporting_1099k_status
    -- capabilities_tax_reporting_us_1099_misc -> tax_reporting_1099misc_status
    -- capabilities_transfers -> transfers_status
    -- company_owners_provided -> owners_info_provided
    -- company_phone -> business_phone
    -- company_structure -> legal_structure
    -- company_tax_id_provided -> tax_id_provided
    -- company_tax_id_registrar -> tax_id_issuer
    -- company_vat_id_provided -> vat_id_provided
    -- company_verification_document_back -> verification_doc_back
    -- company_verification_document_details -> verification_doc_details
    -- company_verification_document_details_code -> verification_doc_code
    -- company_verification_document_front -> verification_doc_front
    -- created -> account_creation_date
    -- email -> account_email
    -- requirements_current_deadline -> current_requirements_deadline
    -- requirements_currently_due -> current_requirements
    -- requirements_errors -> requirement_errors
    -- requirements_eventually_due -> future_requirements
    -- requirements_past_due -> past_due_requirements
    -- requirements_pending_verification -> pending_verification
    -- settings_branding_icon -> branding_icon
    -- settings_branding_logo -> branding_logo_file_id
    -- settings_branding_primary_color -> branding_primary_color
    -- settings_card_payments_decline_on_avs_failure -> decline_on_avs_failure
    -- settings_card_payments_decline_on_cvc_failure -> decline_on_cvc_failure
    -- settings_card_payments_statement_descriptor_prefix -> statement_descriptor_prefix
    -- settings_dashboard_display_name -> dashboard_display_name
    -- settings_dashboard_timezone -> dashboard_timezone
    -- settings_payments_statement_descriptor -> payment_statement_descriptor
    -- settings_payments_statement_descriptor_kana -> payment_statement_descriptor_kana
    -- settings_payments_statement_descriptor_kanji -> payment_statement_descriptor_kanji
    -- settings_payouts_debit_negative_balances -> debit_negative_balances
    -- settings_payouts_schedule_delay_days -> payout_delay_days
    -- settings_payouts_schedule_interval -> payout_schedule_interval
    -- settings_payouts_schedule_monthly_anchor -> payout_monthly_anchor
    -- settings_payouts_schedule_weekly_anchor -> payout_weekly_anchor
    -- settings_payouts_statement_descriptor -> payout_statement_descriptor
    -- type -> account_type
    SELECT 
        "id" AS "account_id",
        "business_profile_mcc" AS "merchant_category_code",
        "business_profile_name" AS "business_name",
        "business_profile_product_description" AS "product_description",
        "business_profile_support_address_city" AS "support_city",
        "business_profile_support_address_country" AS "support_country",
        "business_profile_support_address_line_1" AS "support_address_line1",
        "business_profile_support_address_line_2" AS "support_address_line2",
        "business_profile_support_address_postal_code" AS "support_postal_code",
        "business_profile_support_address_state" AS "support_state",
        "business_profile_support_email" AS "support_email",
        "business_profile_support_phone" AS "support_phone",
        "business_profile_support_url" AS "support_url",
        "business_profile_url" AS "business_url",
        "business_type",
        "capabilities_afterpay_clearpay_payments" AS "afterpay_clearpay_status",
        "capabilities_au_becs_debit_payments" AS "au_becs_debit_status",
        "capabilities_bacs_debit_payments" AS "bacs_debit_status",
        "capabilities_bancontact_payments" AS "bancontact_status",
        "capabilities_card_issuing" AS "card_issuing_status",
        "capabilities_card_payments" AS "card_payments_status",
        "capabilities_cartes_bancaires_payments" AS "cartes_bancaires_status",
        "capabilities_eps_payments" AS "eps_status",
        "capabilities_fpx_payments" AS "fpx_status",
        "capabilities_giropay_payments" AS "giropay_status",
        "capabilities_grabpay_payments" AS "grabpay_status",
        "capabilities_ideal_payments" AS "ideal_status",
        "capabilities_jcb_payments" AS "jcb_status",
        "capabilities_legacy_payments" AS "legacy_payments_status",
        "capabilities_oxxo_payments" AS "oxxo_status",
        "capabilities_p_24_payments" AS "p24_status",
        "capabilities_platform_payments" AS "platform_payments_status",
        "capabilities_sepa_debit_payments" AS "sepa_debit_payments_status",
        "capabilities_sofort_payments" AS "sofort_payments_status",
        "capabilities_tax_reporting_us_1099_k" AS "tax_reporting_1099k_status",
        "capabilities_tax_reporting_us_1099_misc" AS "tax_reporting_1099misc_status",
        "capabilities_transfers" AS "transfers_status",
        "charges_enabled",
        "company_address_city",
        "company_address_country",
        "company_address_kana_city",
        "company_address_kana_country",
        "company_address_kana_line_1",
        "company_address_kana_line_2",
        "company_address_kana_postal_code",
        "company_address_kana_state",
        "company_address_kana_town",
        "company_address_kanji_city",
        "company_address_kanji_country",
        "company_address_kanji_line_1",
        "company_address_kanji_line_2",
        "company_address_kanji_postal_code",
        "company_address_kanji_state",
        "company_address_kanji_town",
        "company_address_line_1",
        "company_address_line_2",
        "company_address_postal_code",
        "company_address_state",
        "company_directors_provided",
        "company_executives_provided",
        "company_name",
        "company_name_kana",
        "company_name_kanji",
        "company_owners_provided" AS "owners_info_provided",
        "company_phone" AS "business_phone",
        "company_structure" AS "legal_structure",
        "company_tax_id_provided" AS "tax_id_provided",
        "company_tax_id_registrar" AS "tax_id_issuer",
        "company_vat_id_provided" AS "vat_id_provided",
        "company_verification_document_back" AS "verification_doc_back",
        "company_verification_document_details" AS "verification_doc_details",
        "company_verification_document_details_code" AS "verification_doc_code",
        "company_verification_document_front" AS "verification_doc_front",
        "country",
        "created" AS "account_creation_date",
        "default_currency",
        "details_submitted",
        "email" AS "account_email",
        "individual_id",
        "is_deleted",
        "metadata",
        "payouts_enabled",
        "requirements_current_deadline" AS "current_requirements_deadline",
        "requirements_currently_due" AS "current_requirements",
        "requirements_disabled_reason",
        "requirements_errors" AS "requirement_errors",
        "requirements_eventually_due" AS "future_requirements",
        "requirements_past_due" AS "past_due_requirements",
        "requirements_pending_verification" AS "pending_verification",
        "settings_branding_icon" AS "branding_icon",
        "settings_branding_logo" AS "branding_logo_file_id",
        "settings_branding_primary_color" AS "branding_primary_color",
        "settings_card_payments_decline_on_avs_failure" AS "decline_on_avs_failure",
        "settings_card_payments_decline_on_cvc_failure" AS "decline_on_cvc_failure",
        "settings_card_payments_statement_descriptor_prefix" AS "statement_descriptor_prefix",
        "settings_dashboard_display_name" AS "dashboard_display_name",
        "settings_dashboard_timezone" AS "dashboard_timezone",
        "settings_payments_statement_descriptor" AS "payment_statement_descriptor",
        "settings_payments_statement_descriptor_kana" AS "payment_statement_descriptor_kana",
        "settings_payments_statement_descriptor_kanji" AS "payment_statement_descriptor_kanji",
        "settings_payouts_debit_negative_balances" AS "debit_negative_balances",
        "settings_payouts_schedule_delay_days" AS "payout_delay_days",
        "settings_payouts_schedule_interval" AS "payout_schedule_interval",
        "settings_payouts_schedule_monthly_anchor" AS "payout_monthly_anchor",
        "settings_payouts_schedule_weekly_anchor" AS "payout_weekly_anchor",
        "settings_payouts_statement_descriptor" AS "payout_statement_descriptor",
        "tos_acceptance_date",
        "tos_acceptance_ip",
        "tos_acceptance_user_agent",
        "type" AS "account_type"
    FROM "account_data_projected"
),

"account_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- account_creation_date: from DECIMAL to DATE
    -- account_id: from INT to VARCHAR
    -- au_becs_debit_status: from DECIMAL to VARCHAR
    -- bacs_debit_status: from DECIMAL to VARCHAR
    -- branding_icon: from DECIMAL to VARCHAR
    -- business_phone: from DECIMAL to VARCHAR
    -- business_type: from DECIMAL to VARCHAR
    -- card_issuing_status: from DECIMAL to VARCHAR
    -- cartes_bancaires_status: from DECIMAL to VARCHAR
    -- company_address_city: from DECIMAL to VARCHAR
    -- company_address_country: from DECIMAL to VARCHAR
    -- company_address_kana_city: from DECIMAL to VARCHAR
    -- company_address_kana_country: from DECIMAL to VARCHAR
    -- company_address_kana_line_1: from DECIMAL to VARCHAR
    -- company_address_kana_line_2: from DECIMAL to VARCHAR
    -- company_address_kana_postal_code: from DECIMAL to VARCHAR
    -- company_address_kana_state: from DECIMAL to VARCHAR
    -- company_address_kana_town: from DECIMAL to VARCHAR
    -- company_address_kanji_city: from DECIMAL to VARCHAR
    -- company_address_kanji_country: from DECIMAL to VARCHAR
    -- company_address_kanji_line_1: from DECIMAL to VARCHAR
    -- company_address_kanji_line_2: from DECIMAL to VARCHAR
    -- company_address_kanji_postal_code: from DECIMAL to VARCHAR
    -- company_address_kanji_state: from DECIMAL to VARCHAR
    -- company_address_kanji_town: from DECIMAL to VARCHAR
    -- company_address_line_1: from DECIMAL to VARCHAR
    -- company_address_line_2: from DECIMAL to VARCHAR
    -- company_address_postal_code: from DECIMAL to VARCHAR
    -- company_address_state: from DECIMAL to VARCHAR
    -- company_directors_provided: from DECIMAL to BOOLEAN
    -- company_executives_provided: from DECIMAL to BOOLEAN
    -- company_name: from DECIMAL to VARCHAR
    -- company_name_kana: from DECIMAL to VARCHAR
    -- company_name_kanji: from DECIMAL to VARCHAR
    -- current_requirements: from DECIMAL to VARCHAR
    -- current_requirements_deadline: from DECIMAL to DATE
    -- decline_on_avs_failure: from DECIMAL to BOOLEAN
    -- decline_on_cvc_failure: from DECIMAL to BOOLEAN
    -- fpx_status: from DECIMAL to VARCHAR
    -- future_requirements: from DECIMAL to VARCHAR
    -- grabpay_status: from DECIMAL to VARCHAR
    -- individual_id: from DECIMAL to VARCHAR
    -- jcb_status: from DECIMAL to VARCHAR
    -- legacy_payments_status: from DECIMAL to VARCHAR
    -- legal_structure: from DECIMAL to VARCHAR
    -- metadata: from DECIMAL to JSON
    -- owners_info_provided: from DECIMAL to BOOLEAN
    -- oxxo_status: from DECIMAL to VARCHAR
    -- past_due_requirements: from DECIMAL to ARRAY
    -- payment_statement_descriptor_kana: from DECIMAL to VARCHAR
    -- payment_statement_descriptor_kanji: from DECIMAL to VARCHAR
    -- payout_monthly_anchor: from DECIMAL to INT
    -- payout_statement_descriptor: from DECIMAL to VARCHAR
    -- payout_weekly_anchor: from DECIMAL to VARCHAR
    -- pending_verification: from DECIMAL to ARRAY
    -- product_description: from DECIMAL to VARCHAR
    -- requirement_errors: from DECIMAL to ARRAY
    -- requirements_disabled_reason: from DECIMAL to VARCHAR
    -- statement_descriptor_prefix: from DECIMAL to VARCHAR
    -- support_phone: from DECIMAL to VARCHAR
    -- support_postal_code: from INT to VARCHAR
    -- tax_id_issuer: from DECIMAL to VARCHAR
    -- tax_id_provided: from DECIMAL to BOOLEAN
    -- tax_reporting_1099k_status: from DECIMAL to VARCHAR
    -- tax_reporting_1099misc_status: from DECIMAL to VARCHAR
    -- tos_acceptance_date: from DECIMAL to TIMESTAMP
    -- tos_acceptance_ip: from DECIMAL to VARCHAR
    -- tos_acceptance_user_agent: from DECIMAL to VARCHAR
    -- transfers_status: from DECIMAL to VARCHAR
    -- vat_id_provided: from DECIMAL to BOOLEAN
    -- verification_doc_back: from DECIMAL to VARCHAR
    -- verification_doc_code: from DECIMAL to VARCHAR
    -- verification_doc_details: from DECIMAL to VARCHAR
    -- verification_doc_front: from DECIMAL to VARCHAR
    SELECT
        "merchant_category_code",
        "business_name",
        "support_city",
        "support_country",
        "support_address_line1",
        "support_address_line2",
        "support_state",
        "support_email",
        "support_url",
        "business_url",
        "afterpay_clearpay_status",
        "bancontact_status",
        "card_payments_status",
        "eps_status",
        "giropay_status",
        "ideal_status",
        "p24_status",
        "platform_payments_status",
        "sepa_debit_payments_status",
        "sofort_payments_status",
        "charges_enabled",
        "country",
        "default_currency",
        "details_submitted",
        "account_email",
        "is_deleted",
        "payouts_enabled",
        "branding_logo_file_id",
        "branding_primary_color",
        "dashboard_display_name",
        "dashboard_timezone",
        "payment_statement_descriptor",
        "debit_negative_balances",
        "payout_delay_days",
        "payout_schedule_interval",
        "account_type",
        CAST("account_creation_date" AS DATE) AS "account_creation_date",
        CAST("account_id" AS VARCHAR) AS "account_id",
        CAST("au_becs_debit_status" AS VARCHAR) AS "au_becs_debit_status",
        CAST("bacs_debit_status" AS VARCHAR) AS "bacs_debit_status",
        CAST("branding_icon" AS VARCHAR) AS "branding_icon",
        CAST("business_phone" AS VARCHAR) AS "business_phone",
        CAST("business_type" AS VARCHAR) AS "business_type",
        CAST("card_issuing_status" AS VARCHAR) AS "card_issuing_status",
        CAST("cartes_bancaires_status" AS VARCHAR) AS "cartes_bancaires_status",
        CAST("company_address_city" AS VARCHAR) AS "company_address_city",
        CAST("company_address_country" AS VARCHAR) AS "company_address_country",
        CAST("company_address_kana_city" AS VARCHAR) AS "company_address_kana_city",
        CAST("company_address_kana_country" AS VARCHAR) AS "company_address_kana_country",
        CAST("company_address_kana_line_1" AS VARCHAR) AS "company_address_kana_line_1",
        CAST("company_address_kana_line_2" AS VARCHAR) AS "company_address_kana_line_2",
        CAST("company_address_kana_postal_code" AS VARCHAR) AS "company_address_kana_postal_code",
        CAST("company_address_kana_state" AS VARCHAR) AS "company_address_kana_state",
        CAST("company_address_kana_town" AS VARCHAR) AS "company_address_kana_town",
        CAST("company_address_kanji_city" AS VARCHAR) AS "company_address_kanji_city",
        CAST("company_address_kanji_country" AS VARCHAR) AS "company_address_kanji_country",
        CAST("company_address_kanji_line_1" AS VARCHAR) AS "company_address_kanji_line_1",
        CAST("company_address_kanji_line_2" AS VARCHAR) AS "company_address_kanji_line_2",
        CAST("company_address_kanji_postal_code" AS VARCHAR) AS "company_address_kanji_postal_code",
        CAST("company_address_kanji_state" AS VARCHAR) AS "company_address_kanji_state",
        CAST("company_address_kanji_town" AS VARCHAR) AS "company_address_kanji_town",
        CAST("company_address_line_1" AS VARCHAR) AS "company_address_line_1",
        CAST("company_address_line_2" AS VARCHAR) AS "company_address_line_2",
        CAST("company_address_postal_code" AS VARCHAR) AS "company_address_postal_code",
        CAST("company_address_state" AS VARCHAR) AS "company_address_state",
        CAST("company_directors_provided" AS BOOLEAN) AS "company_directors_provided",
        CAST("company_executives_provided" AS BOOLEAN) AS "company_executives_provided",
        CAST("company_name" AS VARCHAR) AS "company_name",
        CAST("company_name_kana" AS VARCHAR) AS "company_name_kana",
        CAST("company_name_kanji" AS VARCHAR) AS "company_name_kanji",
        CAST("current_requirements" AS VARCHAR) AS "current_requirements",
        CAST("current_requirements_deadline" AS DATE) AS "current_requirements_deadline",
        CAST("decline_on_avs_failure" AS BOOLEAN) AS "decline_on_avs_failure",
        CAST("decline_on_cvc_failure" AS BOOLEAN) AS "decline_on_cvc_failure",
        CAST("fpx_status" AS VARCHAR) AS "fpx_status",
        CAST("future_requirements" AS VARCHAR) AS "future_requirements",
        CAST("grabpay_status" AS VARCHAR) AS "grabpay_status",
        CAST("individual_id" AS VARCHAR) AS "individual_id",
        CAST("jcb_status" AS VARCHAR) AS "jcb_status",
        CAST("legacy_payments_status" AS VARCHAR) AS "legacy_payments_status",
        CAST("legal_structure" AS VARCHAR) AS "legal_structure",
        CAST("metadata" AS JSON) AS "metadata",
        CAST("owners_info_provided" AS BOOLEAN) AS "owners_info_provided",
        CAST("oxxo_status" AS VARCHAR) AS "oxxo_status",
        "past_due_requirements" AS "past_due_requirements",
        CAST("payment_statement_descriptor_kana" AS VARCHAR) AS "payment_statement_descriptor_kana",
        CAST("payment_statement_descriptor_kanji" AS VARCHAR) AS "payment_statement_descriptor_kanji",
        CAST("payout_monthly_anchor" AS INT) AS "payout_monthly_anchor",
        CAST("payout_statement_descriptor" AS VARCHAR) AS "payout_statement_descriptor",
        CAST("payout_weekly_anchor" AS VARCHAR) AS "payout_weekly_anchor",
        "pending_verification" AS "pending_verification",
        CAST("product_description" AS VARCHAR) AS "product_description",
        string_split(CAST("requirement_errors" AS VARCHAR), ',') AS "requirement_errors",
        CAST("requirements_disabled_reason" AS VARCHAR) AS "requirements_disabled_reason",
        CAST("statement_descriptor_prefix" AS VARCHAR) AS "statement_descriptor_prefix",
        CAST("support_phone" AS VARCHAR) AS "support_phone",
        CAST("support_postal_code" AS VARCHAR) AS "support_postal_code",
        CAST("tax_id_issuer" AS VARCHAR) AS "tax_id_issuer",
        CAST("tax_id_provided" AS BOOLEAN) AS "tax_id_provided",
        CAST("tax_reporting_1099k_status" AS VARCHAR) AS "tax_reporting_1099k_status",
        CAST("tax_reporting_1099misc_status" AS VARCHAR) AS "tax_reporting_1099misc_status",
        CAST("tos_acceptance_date" AS TIMESTAMP) AS "tos_acceptance_date",
        CAST("tos_acceptance_ip" AS VARCHAR) AS "tos_acceptance_ip",
        CAST("tos_acceptance_user_agent" AS VARCHAR) AS "tos_acceptance_user_agent",
        CAST("transfers_status" AS VARCHAR) AS "transfers_status",
        CAST("vat_id_provided" AS BOOLEAN) AS "vat_id_provided",
        CAST("verification_doc_back" AS VARCHAR) AS "verification_doc_back",
        CAST("verification_doc_code" AS VARCHAR) AS "verification_doc_code",
        CAST("verification_doc_details" AS VARCHAR) AS "verification_doc_details",
        CAST("verification_doc_front" AS VARCHAR) AS "verification_doc_front"
    FROM "account_data_projected_renamed"
),

"account_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 46 columns with unacceptable missing values
    -- account_creation_date has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- branding_icon has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- business_phone has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- business_type has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- card_issuing_status has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- cartes_bancaires_status has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- company_address_city has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- company_address_country has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- company_address_line_1 has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- company_address_line_2 has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- company_address_postal_code has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- company_address_state has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- company_directors_provided has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- company_executives_provided has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- company_name has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- current_requirements has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- current_requirements_deadline has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- decline_on_avs_failure has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- decline_on_cvc_failure has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- future_requirements has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- individual_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- legacy_payments_status has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- legal_structure has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- metadata has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- owners_info_provided has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- past_due_requirements has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- payout_statement_descriptor has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- pending_verification has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- product_description has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- requirement_errors has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- requirements_disabled_reason has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- statement_descriptor_prefix has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- support_phone has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- tax_id_issuer has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- tax_id_provided has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- tax_reporting_1099k_status has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- tax_reporting_1099misc_status has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- tos_acceptance_date has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- tos_acceptance_ip has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- tos_acceptance_user_agent has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- transfers_status has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- vat_id_provided has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- verification_doc_back has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- verification_doc_code has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- verification_doc_details has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- verification_doc_front has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "merchant_category_code",
        "business_name",
        "support_city",
        "support_country",
        "support_address_line1",
        "support_address_line2",
        "support_state",
        "support_email",
        "support_url",
        "business_url",
        "afterpay_clearpay_status",
        "bancontact_status",
        "card_payments_status",
        "eps_status",
        "giropay_status",
        "ideal_status",
        "p24_status",
        "platform_payments_status",
        "sepa_debit_payments_status",
        "sofort_payments_status",
        "charges_enabled",
        "country",
        "default_currency",
        "details_submitted",
        "account_email",
        "is_deleted",
        "payouts_enabled",
        "branding_logo_file_id",
        "branding_primary_color",
        "dashboard_display_name",
        "dashboard_timezone",
        "payment_statement_descriptor",
        "debit_negative_balances",
        "payout_delay_days",
        "payout_schedule_interval",
        "account_type",
        "account_id",
        "au_becs_debit_status",
        "bacs_debit_status",
        "company_address_kana_city",
        "company_address_kana_country",
        "company_address_kana_line_1",
        "company_address_kana_line_2",
        "company_address_kana_postal_code",
        "company_address_kana_state",
        "company_address_kana_town",
        "company_address_kanji_city",
        "company_address_kanji_country",
        "company_address_kanji_line_1",
        "company_address_kanji_line_2",
        "company_address_kanji_postal_code",
        "company_address_kanji_state",
        "company_address_kanji_town",
        "company_name_kana",
        "company_name_kanji",
        "fpx_status",
        "grabpay_status",
        "jcb_status",
        "oxxo_status",
        "payment_statement_descriptor_kana",
        "payment_statement_descriptor_kanji",
        "payout_monthly_anchor",
        "payout_weekly_anchor",
        "support_postal_code"
    FROM "account_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "account_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_account_data
  description: The table is about a Stripe business account. It contains details like
    business profile, capabilities, company information, settings, and compliance
    requirements. Key fields include business name, address, supported payment methods,
    payout settings, and legal acceptance details. The account belongs to Hogwarts
    School, with education-related merchant category code and wizarding world URLs.
  columns:
  - name: merchant_category_code
    description: Merchant Category Code for the business
    tests:
    - not_null
  - name: business_name
    description: Name of the business
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the name of the business. For this table,
        each row is a unique business account. Business names are generally unique,
        especially for a specific platform like Stripe.
  - name: support_city
    description: City of the business support address
    tests:
    - not_null
  - name: support_country
    description: Country of the business support address
    tests:
    - not_null
  - name: support_address_line1
    description: First line of the business support address
    tests:
    - not_null
  - name: support_address_line2
    description: Second line of the business support address
    tests:
    - not_null
  - name: support_state
    description: State of the business support address
    tests:
    - not_null
    - accepted_values:
        values:
        - AL
        - AK
        - AZ
        - AR
        - CA
        - CO
        - CT
        - DE
        - FL
        - GA
        - HI
        - ID
        - IL
        - IN
        - IA
        - KS
        - KY
        - LA
        - ME
        - MD
        - MA
        - MI
        - MN
        - MS
        - MO
        - MT
        - NE
        - NV
        - NH
        - NJ
        - NM
        - NY
        - NC
        - ND
        - OH
        - OK
        - OR
        - PA
        - RI
        - SC
        - SD
        - TN
        - TX
        - UT
        - VT
        - VA
        - WA
        - WV
        - WI
        - WY
        - DC
        - AS
        - GU
        - MP
        - PR
        - VI
  - name: support_email
    description: Support email address for the business
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the support email address for the business.
        For this table, each row is a unique business account. Email addresses are
        generally unique for each business account on platforms like Stripe.
  - name: support_url
    description: Support website URL for the business
    tests:
    - not_null
    - unique
    - accepted_values:
        values:
        - https://www.wizardingworld.com/help
        - https://www.wizardingworld.com/
    cocoon_meta:
      uniqueness: This column represents the support website URL for the business.
        For this table, each row is a unique business account. Support URLs are likely
        to be unique for each business, especially on a platform like Stripe.
  - name: business_url
    description: Main website URL for the business
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the main website URL for the business. For
        this table, each row is a unique business account. Business URLs are typically
        unique for each business.
  - name: afterpay_clearpay_status
    description: Afterpay/Clearpay payment capability status
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - pending
        - rejected
  - name: bancontact_status
    description: Bancontact payment capability status
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - pending
  - name: card_payments_status
    description: Card payment capability status
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - pending
        - suspended
        - restricted
        - blocked
        - expired
        - not_available
  - name: eps_status
    description: EPS payment capability status
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - suspended
        - pending
        - blocked
        - limited
        - restricted
        - unavailable
        - maintenance
        - error
  - name: giropay_status
    description: Giropay payment capability status
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - pending
  - name: ideal_status
    description: iDEAL payment capability status
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - pending
        - suspended
        - disabled
        - unavailable
  - name: p24_status
    description: Przelewy24 payment capability status
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - pending
  - name: platform_payments_status
    description: Status of platform payment capability
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - pending
        - suspended
        - disabled
        - maintenance
        - error
  - name: sepa_debit_payments_status
    description: Status of SEPA debit payment capability
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - pending
        - suspended
  - name: sofort_payments_status
    description: Status of Sofort payment capability
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - pending
        - suspended
        - unavailable
        - error
  - name: charges_enabled
    description: Whether charges are enabled for the account
    tests:
    - not_null
  - name: country
    description: Country where the company is registered
    tests:
    - not_null
  - name: default_currency
    description: Default currency for the account
    tests:
    - not_null
  - name: details_submitted
    description: Indicates if all required details were submitted
    tests:
    - not_null
  - name: account_email
    description: Primary email address for the account
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the primary email address for the account.
        For this table, each row is a unique Stripe business account. Email addresses
        are typically unique for each account in financial systems, making this a
        good candidate for a unique identifier.
  - name: is_deleted
    description: Indicates if the account has been deleted
    tests:
    - not_null
  - name: payouts_enabled
    description: Indicates if payouts are enabled for account
    tests:
    - not_null
  - name: branding_logo_file_id
    description: File ID for the company's branding logo
    tests:
    - not_null
  - name: branding_primary_color
    description: Primary color for branding in hexadecimal format
    tests:
    - not_null
  - name: dashboard_display_name
    description: Display name for the Stripe dashboard
    tests:
    - not_null
  - name: dashboard_timezone
    description: Timezone setting for the Stripe dashboard
    tests:
    - not_null
  - name: payment_statement_descriptor
    description: Description appearing on customer's credit card statement
    tests:
    - not_null
  - name: debit_negative_balances
    description: Allow debiting account for negative balances
    tests:
    - not_null
  - name: payout_delay_days
    description: Number of days to delay scheduled payouts
    tests:
    - not_null
  - name: payout_schedule_interval
    description: Frequency of scheduled payouts
    tests:
    - not_null
    - accepted_values:
        values:
        - daily
        - weekly
        - biweekly
        - monthly
        - quarterly
        - semi-annually
        - annually
  - name: account_type
    description: Type of Stripe account
    tests:
    - not_null
    - accepted_values:
        values:
        - standard
        - express
        - custom
  - name: account_id
    description: Unique identifier for the Stripe account
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the unique identifier for the Stripe account.
        For this table, each row is for a unique Stripe business account. This is
        unique across rows as it's designed to be a unique identifier for each account.
  - name: au_becs_debit_status
    description: Australian BECS Direct Debit payment capability status
    cocoon_meta:
      missing_acceptable: Australian payment system not applicable for US company
  - name: bacs_debit_status
    description: BACS Direct Debit payment capability status
    cocoon_meta:
      missing_acceptable: UK payment system not applicable for US company
  - name: company_address_kana_city
    description: City in Kana script for Japanese addresses
    cocoon_meta:
      missing_acceptable: Kana address format not applicable for US company
  - name: company_address_kana_country
    description: Country in Kana script for Japanese addresses
    cocoon_meta:
      missing_acceptable: Kana address format not applicable for US company
  - name: company_address_kana_line_1
    description: Address line 1 in Kana script
    cocoon_meta:
      missing_acceptable: Kana address format not applicable for US company
  - name: company_address_kana_line_2
    description: Address line 2 in Kana script
    cocoon_meta:
      missing_acceptable: Kana address format not applicable for US company
  - name: company_address_kana_postal_code
    description: Postal code in Kana script
    cocoon_meta:
      missing_acceptable: Kana address format not applicable for US company
  - name: company_address_kana_state
    description: State in Kana script for Japanese addresses
    cocoon_meta:
      missing_acceptable: Kana address format not applicable for US company
  - name: company_address_kana_town
    description: Town in Kana script for Japanese addresses
    cocoon_meta:
      missing_acceptable: Kana address format not applicable for US company
  - name: company_address_kanji_city
    description: City in Kanji script for Japanese addresses
    cocoon_meta:
      missing_acceptable: Kanji address format not applicable for US company
  - name: company_address_kanji_country
    description: Country in Kanji script for Japanese addresses
    cocoon_meta:
      missing_acceptable: Kanji address format not applicable for US company
  - name: company_address_kanji_line_1
    description: Address line 1 in Kanji script
    cocoon_meta:
      missing_acceptable: Kanji address format not applicable for US company
  - name: company_address_kanji_line_2
    description: Address line 2 in Kanji script
    cocoon_meta:
      missing_acceptable: Kanji address format not applicable for US company
  - name: company_address_kanji_postal_code
    description: Postal code in Kanji script
    cocoon_meta:
      missing_acceptable: Kanji address format not applicable for US company
  - name: company_address_kanji_state
    description: State in Kanji script for Japanese addresses
    cocoon_meta:
      missing_acceptable: Kanji address format not applicable for US company
  - name: company_address_kanji_town
    description: Town in Kanji script for Japanese addresses
    cocoon_meta:
      missing_acceptable: Kanji address format not applicable for US company
  - name: company_name_kana
    description: Company name in Japanese kana characters
    cocoon_meta:
      missing_acceptable: Kana company name not applicable for US company
  - name: company_name_kanji
    description: Company name in Japanese kanji characters
    cocoon_meta:
      missing_acceptable: Kanji company name not applicable for US company
  - name: fpx_status
    description: FPX payment capability status
    cocoon_meta:
      missing_acceptable: FPX is a Malaysian payment method, not used globally.
  - name: grabpay_status
    description: GrabPay payment capability status
    cocoon_meta:
      missing_acceptable: GrabPay is a Southeast Asian payment method, not used globally.
  - name: jcb_status
    description: JCB payment capability status
    cocoon_meta:
      missing_acceptable: JCB is a Japanese credit card, not used globally.
  - name: oxxo_status
    description: OXXO payment capability status
    cocoon_meta:
      missing_acceptable: OXXO is a Mexican payment method, not used globally.
  - name: payment_statement_descriptor_kana
    description: Payment statement descriptor in Japanese kana
    cocoon_meta:
      missing_acceptable: Kana script is only used for Japanese language.
  - name: payment_statement_descriptor_kanji
    description: Payment statement descriptor in Japanese kanji
    cocoon_meta:
      missing_acceptable: Kanji script is only used for Japanese language.
  - name: payout_monthly_anchor
    description: Day of month for monthly payouts
    cocoon_meta:
      missing_acceptable: Not applicable for daily payout schedule.
  - name: payout_weekly_anchor
    description: Day of week for weekly payouts
    cocoon_meta:
      missing_acceptable: Not applicable for daily payout schedule.
  - name: support_postal_code
    description: Postal code of the business support address
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"balance_transaction_data_projected" AS (
    -- Projection: Selecting 14 out of 15 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "amount",
        "available_on",
        "connected_account_id",
        "created",
        "currency",
        "description",
        "exchange_rate",
        "fee",
        "net",
        "source",
        "status",
        "type",
        "payout_id"
    FROM "balance_transaction_data"
),

"balance_transaction_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> transaction_id
    -- amount -> transaction_amount_cents
    -- available_on -> funds_available_date
    -- created -> transaction_created_at
    -- currency -> currency_code
    -- description -> transaction_description
    -- fee -> fee_amount_cents
    -- net -> net_amount_cents
    -- source -> transaction_source_id
    -- status -> transaction_status
    -- type -> transaction_type
    SELECT 
        "id" AS "transaction_id",
        "amount" AS "transaction_amount_cents",
        "available_on" AS "funds_available_date",
        "connected_account_id",
        "created" AS "transaction_created_at",
        "currency" AS "currency_code",
        "description" AS "transaction_description",
        "exchange_rate",
        "fee" AS "fee_amount_cents",
        "net" AS "net_amount_cents",
        "source" AS "transaction_source_id",
        "status" AS "transaction_status",
        "type" AS "transaction_type",
        "payout_id"
    FROM "balance_transaction_data_projected"
),

"balance_transaction_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- connected_account_id: from DECIMAL to VARCHAR
    -- funds_available_date: from VARCHAR to DATE
    -- transaction_created_at: from VARCHAR to TIMESTAMP
    SELECT
        "transaction_id",
        "transaction_amount_cents",
        "currency_code",
        "transaction_description",
        "exchange_rate",
        "fee_amount_cents",
        "net_amount_cents",
        "transaction_source_id",
        "transaction_status",
        "transaction_type",
        "payout_id",
        CAST("connected_account_id" AS VARCHAR) AS "connected_account_id",
        CAST("funds_available_date" AS DATE) AS "funds_available_date",
        CAST("transaction_created_at" AS TIMESTAMP) AS "transaction_created_at"
    FROM "balance_transaction_data_projected_renamed"
),

"balance_transaction_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 2 columns with unacceptable missing values
    -- exchange_rate has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- payout_id has 22.22 percent missing. Strategy: 🔄 Unchanged
    SELECT
        "transaction_id",
        "transaction_amount_cents",
        "currency_code",
        "transaction_description",
        "fee_amount_cents",
        "net_amount_cents",
        "transaction_source_id",
        "transaction_status",
        "transaction_type",
        "payout_id",
        "connected_account_id",
        "funds_available_date",
        "transaction_created_at"
    FROM "balance_transaction_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "balance_transaction_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_balance_transaction_data
  description: The table is about balance transactions. It includes details such as
    transaction ID, amount, date, currency, description, fees, net amount, source,
    status, and type. Each row represents a financial transaction like a charge or
    payout. The table tracks monetary movements, including both incoming (charges)
    and outgoing (payouts) transactions, with associated fees and net amounts.
  columns:
  - name: transaction_id
    description: Unique identifier for the transaction
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each transaction.
        For this table, each row is for a single financial transaction, and transaction_id
        is unique across rows.
  - name: transaction_amount_cents
    description: Transaction amount in cents
    tests:
    - not_null
  - name: currency_code
    description: Currency code of the transaction
    tests:
    - not_null
  - name: transaction_description
    description: Unique identifier or description of the transaction
    tests:
    - not_null
  - name: fee_amount_cents
    description: Fee amount in cents
    tests:
    - not_null
  - name: net_amount_cents
    description: Net amount after fees in cents
    tests:
    - not_null
  - name: transaction_source_id
    description: Source of the transaction (charge or payout ID)
    tests:
    - not_null
  - name: transaction_status
    description: Current status of the transaction
    tests:
    - not_null
    - accepted_values:
        values:
        - pending
        - completed
        - failed
        - cancelled
        - processing
        - rejected
        - on hold
        - refunded
        - partially refunded
  - name: transaction_type
    description: Type of transaction (charge or payout)
    tests:
    - not_null
    - accepted_values:
        values:
        - charge
        - payout
  - name: payout_id
    description: Identifier for the associated payout
    tests:
    - not_null
  - name: connected_account_id
    description: ID of the connected account (empty in this data)
    cocoon_meta:
      missing_acceptable: Not applicable for direct transactions without connected
        accounts.
  - name: funds_available_date
    description: Date when the funds become available
    tests:
    - not_null
  - name: transaction_created_at
    description: Date and time when the transaction was created
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"payment_method_card_data_projected" AS (
    -- Projection: Selecting 8 out of 9 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "payment_method_id",
        "brand",
        "charge_id",
        "description",
        "fingerprint",
        "funding",
        "type",
        "wallet_type"
    FROM "payment_method_card_data"
),

"payment_method_card_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- brand -> payment_provider_id
    -- fingerprint -> card_fingerprint
    -- funding -> funding_type
    -- type -> payment_method_type
    -- wallet_type -> digital_wallet_type
    SELECT 
        "payment_method_id",
        "brand" AS "payment_provider_id",
        "charge_id",
        "description",
        "fingerprint" AS "card_fingerprint",
        "funding" AS "funding_type",
        "type" AS "payment_method_type",
        "wallet_type" AS "digital_wallet_type"
    FROM "payment_method_card_data_projected"
),

"payment_method_card_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- charge_id: from DECIMAL to VARCHAR
    -- description: from DECIMAL to VARCHAR
    -- digital_wallet_type: from DECIMAL to VARCHAR
    -- payment_method_type: from DECIMAL to VARCHAR
    SELECT
        "payment_method_id",
        "payment_provider_id",
        "card_fingerprint",
        "funding_type",
        CAST("charge_id" AS VARCHAR) AS "charge_id",
        CAST("description" AS VARCHAR) AS "description",
        CAST("digital_wallet_type" AS VARCHAR) AS "digital_wallet_type",
        CAST("payment_method_type" AS VARCHAR) AS "payment_method_type"
    FROM "payment_method_card_data_projected_renamed"
),

"payment_method_card_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 3 columns with unacceptable missing values
    -- charge_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- description has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- payment_method_type has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "payment_method_id",
        "payment_provider_id",
        "card_fingerprint",
        "funding_type",
        "digital_wallet_type"
    FROM "payment_method_card_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "payment_method_card_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_payment_method_card_data
  description: The table is about payment method card details. It includes unique
    payment method IDs, a consistent brand identifier, and fingerprints for each card.
    The funding type is specified as credit for all entries. Some fields like charge_id,
    description, type, and wallet_type are empty. The table appears to store essential
    information for credit card payment methods.
  columns:
  - name: payment_method_id
    description: Unique identifier for the payment method
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each payment method.
        For this table, each row represents a distinct payment method, and the payment_method_id
        appears to be unique across rows.
  - name: payment_provider_id
    description: Identifier for the payment provider or card issuer
    tests:
    - not_null
  - name: card_fingerprint
    description: Unique identifier for the specific card
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each specific card.
        For this table, each row represents a distinct payment method, and the card_fingerprint
        appears to be unique across rows.
  - name: funding_type
    description: Type of funding source for the payment method
    tests:
    - not_null
    - accepted_values:
        values:
        - credit
        - debit
        - cash
        - check
        - wire transfer
        - cryptocurrency
        - gift card
        - store credit
        - loyalty points
        - prepaid card
        - mobile payment
        - bank transfer
  - name: digital_wallet_type
    description: Specifies the digital wallet used, if applicable
    cocoon_meta:
      missing_acceptable: Not applicable for non-digital wallet transactions.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"card_data_projected" AS (
    -- Projection: Selecting 29 out of 30 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "account_id",
        "address_city",
        "address_country",
        "address_line_1",
        "address_line_1_check",
        "address_line_2",
        "address_state",
        "address_zip",
        "address_zip_check",
        "brand",
        "connected_account_id",
        "country",
        "created",
        "currency",
        "customer_id",
        "cvc_check",
        "dynamic_last_4",
        "exp_month",
        "exp_year",
        "fingerprint",
        "funding",
        "is_deleted",
        "last_4",
        "name",
        "network",
        "recipient",
        "tokenization_method",
        "metadata"
    FROM "card_data"
),

"card_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> payment_method_id
    -- address_city -> billing_city
    -- address_country -> billing_country
    -- address_line_1 -> billing_address_line1
    -- address_line_1_check -> address_line1_verification
    -- address_line_2 -> billing_address_line2
    -- address_state -> billing_state
    -- address_zip -> billing_zip
    -- address_zip_check -> zip_verification
    -- brand -> card_brand
    -- country -> issuing_country
    -- created -> creation_datetime
    -- cvc_check -> cvc_verification
    -- dynamic_last_4 -> dynamic_last4_digits
    -- exp_month -> expiration_month
    -- exp_year -> expiration_year
    -- fingerprint -> card_fingerprint
    -- funding -> funding_type
    -- last_4 -> last4_digits
    -- name -> cardholder_name
    -- network -> payment_network
    -- recipient -> recipient_info
    SELECT 
        "id" AS "payment_method_id",
        "account_id",
        "address_city" AS "billing_city",
        "address_country" AS "billing_country",
        "address_line_1" AS "billing_address_line1",
        "address_line_1_check" AS "address_line1_verification",
        "address_line_2" AS "billing_address_line2",
        "address_state" AS "billing_state",
        "address_zip" AS "billing_zip",
        "address_zip_check" AS "zip_verification",
        "brand" AS "card_brand",
        "connected_account_id",
        "country" AS "issuing_country",
        "created" AS "creation_datetime",
        "currency",
        "customer_id",
        "cvc_check" AS "cvc_verification",
        "dynamic_last_4" AS "dynamic_last4_digits",
        "exp_month" AS "expiration_month",
        "exp_year" AS "expiration_year",
        "fingerprint" AS "card_fingerprint",
        "funding" AS "funding_type",
        "is_deleted",
        "last_4" AS "last4_digits",
        "name" AS "cardholder_name",
        "network" AS "payment_network",
        "recipient" AS "recipient_info",
        "tokenization_method",
        "metadata"
    FROM "card_data_projected"
),

"card_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- card_brand: The problem is that the card_brand column contains a value that appears to be a hash or encrypted string ('553f65f270c86a4a05c964dc20e8cc70') instead of an actual card brand name. This is unusual because card brands are typically recognizable names like Visa, Mastercard, American Express, etc. The hash-like value doesn't provide any meaningful information about the card brand. Since we don't have enough information to determine the actual card brand, and the current value is not useful, the most appropriate action is to map this unusual value to an empty string. 
    -- metadata: The problem is that there are empty JSON objects ('{}') in the metadata column, which are inconsistent with the other entries. The correct values should all follow the pattern of having an "external_id" key with a string value. The empty JSON objects are likely placeholder entries or errors that should be corrected. 
    SELECT
        "payment_method_id",
        "account_id",
        "billing_city",
        "billing_country",
        "billing_address_line1",
        "address_line1_verification",
        "billing_address_line2",
        "billing_state",
        "billing_zip",
        "zip_verification",
        CASE
            WHEN "card_brand" = '''553f65f270c86a4a05c964dc20e8cc70''' THEN ''''
            ELSE "card_brand"
        END AS "card_brand",
        "connected_account_id",
        "issuing_country",
        "creation_datetime",
        "currency",
        "customer_id",
        "cvc_verification",
        "dynamic_last4_digits",
        "expiration_month",
        "expiration_year",
        "card_fingerprint",
        "funding_type",
        "is_deleted",
        "last4_digits",
        "cardholder_name",
        "payment_network",
        "recipient_info",
        "tokenization_method",
        CASE
            WHEN "metadata" = '''{}''' THEN '{"external_id":""}'
            ELSE "metadata"
        END AS "metadata"
    FROM "card_data_projected_renamed"
),

"card_data_projected_renamed_cleaned_null" AS (
    -- NULL Imputation: Impute Null to Disguised Missing Values
    -- metadata: ['{}']
    SELECT 
        CASE
            WHEN "metadata" = '{}' THEN NULL
            ELSE "metadata"
        END AS "metadata",
        "issuing_country",
        "billing_city",
        "expiration_year",
        "currency",
        "last4_digits",
        "payment_method_id",
        "address_line1_verification",
        "tokenization_method",
        "customer_id",
        "expiration_month",
        "billing_address_line1",
        "payment_network",
        "funding_type",
        "connected_account_id",
        "recipient_info",
        "dynamic_last4_digits",
        "zip_verification",
        "billing_zip",
        "creation_datetime",
        "account_id",
        "billing_address_line2",
        "cardholder_name",
        "billing_country",
        "card_brand",
        "is_deleted",
        "cvc_verification",
        "billing_state",
        "card_fingerprint"
    FROM "card_data_projected_renamed_cleaned"
),

"card_data_projected_renamed_cleaned_null_casted" AS (
    -- Column Type Casting: 
    -- account_id: from DECIMAL to VARCHAR
    -- address_line1_verification: from DECIMAL to VARCHAR
    -- billing_address_line1: from DECIMAL to VARCHAR
    -- billing_address_line2: from DECIMAL to VARCHAR
    -- billing_city: from DECIMAL to VARCHAR
    -- billing_country: from DECIMAL to VARCHAR
    -- billing_state: from DECIMAL to VARCHAR
    -- billing_zip: from DECIMAL to VARCHAR
    -- cardholder_name: from DECIMAL to VARCHAR
    -- connected_account_id: from DECIMAL to VARCHAR
    -- creation_datetime: from VARCHAR to TIMESTAMP
    -- customer_id: from DECIMAL to VARCHAR
    -- dynamic_last4_digits: from DECIMAL to VARCHAR
    -- last4_digits: from INT to VARCHAR
    -- metadata: from VARCHAR to JSON
    -- recipient_info: from DECIMAL to VARCHAR
    -- tokenization_method: from DECIMAL to VARCHAR
    -- zip_verification: from DECIMAL to VARCHAR
    SELECT
        "issuing_country",
        "expiration_year",
        "currency",
        "payment_method_id",
        "expiration_month",
        "payment_network",
        "funding_type",
        "card_brand",
        "is_deleted",
        "cvc_verification",
        "card_fingerprint",
        CAST("account_id" AS VARCHAR) AS "account_id",
        CAST("address_line1_verification" AS VARCHAR) AS "address_line1_verification",
        CAST("billing_address_line1" AS VARCHAR) AS "billing_address_line1",
        CAST("billing_address_line2" AS VARCHAR) AS "billing_address_line2",
        CAST("billing_city" AS VARCHAR) AS "billing_city",
        CAST("billing_country" AS VARCHAR) AS "billing_country",
        CAST("billing_state" AS VARCHAR) AS "billing_state",
        CAST("billing_zip" AS VARCHAR) AS "billing_zip",
        CAST("cardholder_name" AS VARCHAR) AS "cardholder_name",
        CAST("connected_account_id" AS VARCHAR) AS "connected_account_id",
        CAST("creation_datetime" AS TIMESTAMP) AS "creation_datetime",
        CAST("customer_id" AS VARCHAR) AS "customer_id",
        CAST("dynamic_last4_digits" AS VARCHAR) AS "dynamic_last4_digits",
        CAST("last4_digits" AS VARCHAR) AS "last4_digits",
        CAST("metadata" AS JSON) AS "metadata",
        CAST("recipient_info" AS VARCHAR) AS "recipient_info",
        CAST("tokenization_method" AS VARCHAR) AS "tokenization_method",
        CAST("zip_verification" AS VARCHAR) AS "zip_verification"
    FROM "card_data_projected_renamed_cleaned_null"
),

"card_data_projected_renamed_cleaned_null_casted_missing_handled" AS (
    -- Handling missing values: There are 15 columns with unacceptable missing values
    -- account_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- address_line1_verification has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- billing_address_line1 has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- billing_address_line2 has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- billing_city has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- billing_country has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- billing_state has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- billing_zip has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- cardholder_name has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- connected_account_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- customer_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- dynamic_last4_digits has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- recipient_info has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- tokenization_method has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- zip_verification has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "issuing_country",
        "expiration_year",
        "currency",
        "payment_method_id",
        "expiration_month",
        "payment_network",
        "funding_type",
        "card_brand",
        "is_deleted",
        "cvc_verification",
        "card_fingerprint",
        "creation_datetime",
        "last4_digits",
        "metadata"
    FROM "card_data_projected_renamed_cleaned_null_casted"
)

-- COCOON BLOCK END
SELECT * FROM "card_data_projected_renamed_cleaned_null_casted_missing_handled"

version: 2
models:
- name: stg_card_data
  description: The table is about payment methods. It contains details such as card
    ID, brand, country, currency, expiration date, last 4 digits, funding type, and
    creation date. Each row represents a unique payment method with its associated
    attributes. The table includes metadata and checks for address and CVC. Most fields
    are related to card information and payment processing details.
  columns:
  - name: issuing_country
    description: Country where the payment method was issued
    tests:
    - not_null
  - name: expiration_year
    description: Expiration year of the payment card
    tests:
    - not_null
  - name: currency
    description: Currency associated with the payment method
    tests:
    - not_null
  - name: payment_method_id
    description: Unique identifier for the payment method
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each payment method.
        For this table, each row represents a distinct payment method, and the payment_method_id
        appears to be unique across rows.
  - name: expiration_month
    description: Expiration month of the payment card
    tests:
    - not_null
  - name: payment_network
    description: Payment network processing the transaction
    tests:
    - not_null
    - accepted_values:
        values:
        - Visa
        - Mastercard
        - American Express
        - Discover
        - JCB
        - UnionPay
        - Diners Club
        - PayPal
        - Stripe
        - Square
        - Alipay
        - WeChat Pay
        - Apple Pay
        - Google Pay
        - Samsung Pay
        - Interac
        - Klarna
        - Affirm
        - Afterpay
        - Venmo
        - 553f65f270c86a4a05c964dc20e8cc70
  - name: funding_type
    description: Funding type of the payment method (e.g., credit, debit)
    tests:
    - not_null
    - accepted_values:
        values:
        - credit
        - debit
        - prepaid
        - gift card
        - bank transfer
        - cash
        - check
        - cryptocurrency
        - mobile payment
        - wire transfer
        - loyalty points
        - store credit
  - name: card_brand
    description: Brand or network of the payment card
    tests:
    - not_null
    - accepted_values:
        values:
        - Visa
        - Mastercard
        - American Express
        - Discover
        - JCB
        - Diners Club
        - UnionPay
        - Maestro
        - Visa Electron
        - China T-Union
        - RuPay
        - InterPayment
        - InstaPayment
        - UATP
        - Dankort
        - Mir
        - Troy
        - Elo
        - Hipercard
        - BC Card
        - 553f65f270c86a4a05c964dc20e8cc70
  - name: is_deleted
    description: Indicates if the payment method has been deleted
    tests:
    - not_null
  - name: cvc_verification
    description: Status of the Card Verification Code check
    tests:
    - not_null
    - accepted_values:
        values:
        - pass
        - fail
        - not_checked
  - name: card_fingerprint
    description: Unique identifier for the card number
    tests:
    - not_null
  - name: creation_datetime
    description: Date and time when the payment method was created
    tests:
    - not_null
  - name: last4_digits
    description: Last 4 digits of the payment card number
    tests:
    - not_null
  - name: metadata
    description: Additional information stored as key-value pairs
    cocoon_meta:
      missing_acceptable: Optional field for additional information, may not be needed.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"invoice_line_item_data_projected" AS (
    -- Projection: Selecting 17 out of 18 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "invoice_id",
        "amount",
        "currency",
        "description",
        "discountable",
        "livemode",
        "period_end",
        "period_start",
        "plan_id",
        "proration",
        "quantity",
        "subscription_id",
        "subscription_item_id",
        "type",
        "unique_id",
        "metadata"
    FROM "invoice_line_item_data"
),

"invoice_line_item_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> line_item_id
    -- discountable -> is_discountable
    -- livemode -> is_live_mode
    -- period_end -> billing_period_end
    -- period_start -> billing_period_start
    -- proration -> is_prorated
    -- type -> line_item_type
    -- unique_id -> alternate_item_id
    SELECT 
        "id" AS "line_item_id",
        "invoice_id",
        "amount",
        "currency",
        "description",
        "discountable" AS "is_discountable",
        "livemode" AS "is_live_mode",
        "period_end" AS "billing_period_end",
        "period_start" AS "billing_period_start",
        "plan_id",
        "proration" AS "is_prorated",
        "quantity",
        "subscription_id",
        "subscription_item_id",
        "type" AS "line_item_type",
        "unique_id" AS "alternate_item_id",
        "metadata"
    FROM "invoice_line_item_data_projected"
),

"invoice_line_item_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- description: The problem is that 'description_here' is a placeholder value and not actual descriptive content. It appears to be the only value present in the description column, which suggests that real descriptions are missing. The correct values should be actual, meaningful descriptions of the items or entities being described. However, since we don't have access to the real descriptions, we can't provide them. 
    SELECT
        "line_item_id",
        "invoice_id",
        "amount",
        "currency",
        CASE
            WHEN "description" = '''description_here''' THEN ''''
            ELSE "description"
        END AS "description",
        "is_discountable",
        "is_live_mode",
        "billing_period_end",
        "billing_period_start",
        "plan_id",
        "is_prorated",
        "quantity",
        "subscription_id",
        "subscription_item_id",
        "line_item_type",
        "alternate_item_id",
        "metadata"
    FROM "invoice_line_item_data_projected_renamed"
),

"invoice_line_item_data_projected_renamed_cleaned_null" AS (
    -- NULL Imputation: Impute Null to Disguised Missing Values
    -- metadata: ['{}']
    SELECT 
        CASE
            WHEN "metadata" = '{}' THEN NULL
            ELSE "metadata"
        END AS "metadata",
        "line_item_type",
        "line_item_id",
        "subscription_item_id",
        "alternate_item_id",
        "invoice_id",
        "quantity",
        "is_live_mode",
        "amount",
        "currency",
        "billing_period_end",
        "billing_period_start",
        "subscription_id",
        "plan_id",
        "is_prorated",
        "description",
        "is_discountable"
    FROM "invoice_line_item_data_projected_renamed_cleaned"
),

"invoice_line_item_data_projected_renamed_cleaned_null_casted" AS (
    -- Column Type Casting: 
    -- amount: from INT to DECIMAL
    -- billing_period_end: from VARCHAR to TIMESTAMP
    -- billing_period_start: from VARCHAR to TIMESTAMP
    -- line_item_type: from DECIMAL to VARCHAR
    -- metadata: from VARCHAR to JSON
    SELECT
        "line_item_id",
        "subscription_item_id",
        "alternate_item_id",
        "invoice_id",
        "quantity",
        "is_live_mode",
        "currency",
        "subscription_id",
        "plan_id",
        "is_prorated",
        "description",
        "is_discountable",
        CAST("amount" AS DECIMAL) AS "amount",
        CAST("billing_period_end" AS TIMESTAMP) AS "billing_period_end",
        CAST("billing_period_start" AS TIMESTAMP) AS "billing_period_start",
        CAST("line_item_type" AS VARCHAR) AS "line_item_type",
        CAST("metadata" AS JSON) AS "metadata"
    FROM "invoice_line_item_data_projected_renamed_cleaned_null"
)

-- COCOON BLOCK END
SELECT * FROM "invoice_line_item_data_projected_renamed_cleaned_null_casted"

version: 2
models:
- name: stg_invoice_line_item_data
  description: The table is about invoice line items. It contains details such as
    the amount, currency, description, and time period for each item. It also includes
    information about associated plans, subscriptions, and discounts. The table stores
    metadata and unique identifiers for each line item. This data likely represents
    individual charges or services on a customer's invoice.
  columns:
  - name: line_item_id
    description: Unique identifier for the line item
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each line item on
        an invoice. For this table, each row is a separate line item, and the line_item_id
        should be unique across rows.
  - name: subscription_item_id
    description: ID of the specific subscription item
    tests:
    - not_null
  - name: alternate_item_id
    description: Another unique identifier for the item
    tests:
    - not_null
  - name: invoice_id
    description: ID of the associated invoice
    tests:
    - not_null
  - name: quantity
    description: Number of units for the line item
    tests:
    - not_null
  - name: is_live_mode
    description: Indicates if the item is in live mode
    tests:
    - not_null
  - name: currency
    description: Currency code for the amount
    tests:
    - not_null
  - name: subscription_id
    description: ID of the associated subscription
    tests:
    - not_null
  - name: plan_id
    description: ID of the associated plan
    tests:
    - not_null
  - name: is_prorated
    description: Indicates if the item is prorated
    tests:
    - not_null
  - name: description
    description: Brief description of the line item
    tests:
    - not_null
  - name: is_discountable
    description: Whether the item is eligible for discounts
    tests:
    - not_null
  - name: amount
    description: Monetary value of the line item
    tests:
    - not_null
  - name: billing_period_end
    description: End date of the billing period
    tests:
    - not_null
  - name: billing_period_start
    description: Start date of the billing period
    tests:
    - not_null
  - name: line_item_type
    description: Type of the line item
    cocoon_meta:
      missing_acceptable: May not be applicable for all types of transactions.
  - name: metadata
    description: Additional custom data in JSON format
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"payment_intent_data_projected" AS (
    -- Projection: Selecting 31 out of 32 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "amount",
        "amount_capturable",
        "amount_received",
        "application",
        "application_fee_amount",
        "canceled_at",
        "cancellation_reason",
        "capture_method",
        "confirmation_method",
        "created",
        "currency",
        "customer_id",
        "description",
        "last_payment_error_charge_id",
        "last_payment_error_code",
        "last_payment_error_decline_code",
        "last_payment_error_doc_url",
        "last_payment_error_message",
        "last_payment_error_param",
        "last_payment_error_source_id",
        "last_payment_error_type",
        "livemode",
        "on_behalf_of",
        "payment_method_id",
        "receipt_email",
        "source_id",
        "statement_descriptor",
        "status",
        "transfer_data_destination",
        "transfer_group"
    FROM "payment_intent_data"
),

"payment_intent_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> payment_intent_id
    -- amount -> amount_in_cents
    -- created -> created_at
    -- currency -> currency_code
    -- last_payment_error_charge_id -> error_charge_id
    -- last_payment_error_code -> error_code
    -- last_payment_error_decline_code -> decline_code
    -- last_payment_error_doc_url -> error_doc_url
    -- last_payment_error_message -> error_message
    -- last_payment_error_param -> error_param
    -- last_payment_error_source_id -> error_source_id
    -- last_payment_error_type -> error_type
    -- livemode -> is_live
    -- transfer_data_destination -> transfer_destination
    SELECT 
        "id" AS "payment_intent_id",
        "amount" AS "amount_in_cents",
        "amount_capturable",
        "amount_received",
        "application",
        "application_fee_amount",
        "canceled_at",
        "cancellation_reason",
        "capture_method",
        "confirmation_method",
        "created" AS "created_at",
        "currency" AS "currency_code",
        "customer_id",
        "description",
        "last_payment_error_charge_id" AS "error_charge_id",
        "last_payment_error_code" AS "error_code",
        "last_payment_error_decline_code" AS "decline_code",
        "last_payment_error_doc_url" AS "error_doc_url",
        "last_payment_error_message" AS "error_message",
        "last_payment_error_param" AS "error_param",
        "last_payment_error_source_id" AS "error_source_id",
        "last_payment_error_type" AS "error_type",
        "livemode" AS "is_live",
        "on_behalf_of",
        "payment_method_id",
        "receipt_email",
        "source_id",
        "statement_descriptor",
        "status",
        "transfer_data_destination" AS "transfer_destination",
        "transfer_group"
    FROM "payment_intent_data_projected"
),

"payment_intent_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- application_fee_amount: from DECIMAL to VARCHAR
    -- canceled_at: from VARCHAR to TIMESTAMP
    -- cancellation_reason: from DECIMAL to VARCHAR
    -- created_at: from VARCHAR to TIMESTAMP
    -- decline_code: from DECIMAL to VARCHAR
    -- error_charge_id: from DECIMAL to VARCHAR
    -- error_code: from DECIMAL to VARCHAR
    -- error_doc_url: from DECIMAL to VARCHAR
    -- error_message: from DECIMAL to VARCHAR
    -- error_param: from DECIMAL to VARCHAR
    -- error_source_id: from DECIMAL to VARCHAR
    -- error_type: from DECIMAL to VARCHAR
    -- on_behalf_of: from DECIMAL to VARCHAR
    -- source_id: from DECIMAL to VARCHAR
    -- statement_descriptor: from DECIMAL to VARCHAR
    -- transfer_destination: from DECIMAL to VARCHAR
    -- transfer_group: from DECIMAL to VARCHAR
    SELECT
        "payment_intent_id",
        "amount_in_cents",
        "amount_capturable",
        "amount_received",
        "application",
        "capture_method",
        "confirmation_method",
        "currency_code",
        "customer_id",
        "description",
        "is_live",
        "payment_method_id",
        "receipt_email",
        "status",
        CAST("application_fee_amount" AS VARCHAR) AS "application_fee_amount",
        CAST("canceled_at" AS TIMESTAMP) AS "canceled_at",
        CAST("cancellation_reason" AS VARCHAR) AS "cancellation_reason",
        CAST("created_at" AS TIMESTAMP) AS "created_at",
        CAST("decline_code" AS VARCHAR) AS "decline_code",
        CAST("error_charge_id" AS VARCHAR) AS "error_charge_id",
        CAST("error_code" AS VARCHAR) AS "error_code",
        CAST("error_doc_url" AS VARCHAR) AS "error_doc_url",
        CAST("error_message" AS VARCHAR) AS "error_message",
        CAST("error_param" AS VARCHAR) AS "error_param",
        CAST("error_source_id" AS VARCHAR) AS "error_source_id",
        CAST("error_type" AS VARCHAR) AS "error_type",
        CAST("on_behalf_of" AS VARCHAR) AS "on_behalf_of",
        CAST("source_id" AS VARCHAR) AS "source_id",
        CAST("statement_descriptor" AS VARCHAR) AS "statement_descriptor",
        CAST("transfer_destination" AS VARCHAR) AS "transfer_destination",
        CAST("transfer_group" AS VARCHAR) AS "transfer_group"
    FROM "payment_intent_data_projected_renamed"
),

"payment_intent_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 9 columns with unacceptable missing values
    -- application has 63.64 percent missing. Strategy: 🔄 Unchanged
    -- application_fee_amount has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- customer_id has 36.36 percent missing. Strategy: 🔄 Unchanged
    -- on_behalf_of has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- receipt_email has 63.64 percent missing. Strategy: 🔄 Unchanged
    -- source_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- statement_descriptor has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- transfer_destination has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- transfer_group has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "payment_intent_id",
        "amount_in_cents",
        "amount_capturable",
        "amount_received",
        "application",
        "capture_method",
        "confirmation_method",
        "currency_code",
        "customer_id",
        "description",
        "is_live",
        "payment_method_id",
        "receipt_email",
        "status",
        "canceled_at",
        "cancellation_reason",
        "created_at",
        "decline_code",
        "error_charge_id",
        "error_code",
        "error_doc_url",
        "error_message",
        "error_param",
        "error_source_id",
        "error_type"
    FROM "payment_intent_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "payment_intent_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_payment_intent_data
  description: The table is about payment intents. It includes details such as amount,
    currency, customer ID, payment method ID, and status. Each row represents a single
    payment intent with a unique ID. The table tracks information like creation date,
    capture method, and whether the payment succeeded. It also includes fields for
    errors and cancellations, though most are empty in these samples.
  columns:
  - name: payment_intent_id
    description: Unique identifier for the payment intent
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each payment intent.
        For this table, each row is for a single payment intent, and payment_intent_id
        is unique across rows.
  - name: amount_in_cents
    description: Payment amount in the smallest currency unit
    tests:
    - not_null
  - name: amount_capturable
    description: Amount available for capture
    tests:
    - not_null
  - name: amount_received
    description: Amount actually received
    tests:
    - not_null
  - name: application
    description: Associated application for the payment
    tests:
    - not_null
  - name: capture_method
    description: Method used to capture payment
    tests:
    - not_null
    - accepted_values:
        values:
        - automatic
        - manual
        - card
        - bank_transfer
        - cash
        - check
        - paypal
        - apple_pay
        - google_pay
        - cryptocurrency
        - wire_transfer
        - direct_debit
        - invoice
  - name: confirmation_method
    description: Method used to confirm payment
    tests:
    - not_null
    - accepted_values:
        values:
        - automatic
        - manual
        - email
        - sms
        - phone call
        - in-app notification
        - bank statement
        - receipt
        - invoice
        - credit card statement
        - payment gateway confirmation
        - third-party verification
  - name: currency_code
    description: Currency code for the payment
    tests:
    - not_null
  - name: customer_id
    description: Unique identifier for the customer
    tests:
    - not_null
  - name: description
    description: Description of the payment intent
    tests:
    - not_null
  - name: is_live
    description: Whether the payment is live or test
    tests:
    - not_null
  - name: payment_method_id
    description: ID of the payment method used
    tests:
    - not_null
  - name: receipt_email
    description: Email address for receipt
    tests:
    - not_null
  - name: status
    description: Current status of the payment intent
    tests:
    - not_null
    - accepted_values:
        values:
        - succeeded
        - processing
        - requires_payment_method
        - requires_confirmation
        - requires_action
        - canceled
        - requires_capture
  - name: canceled_at
    description: Timestamp of cancellation
    tests:
    - not_null
  - name: cancellation_reason
    description: Reason for cancellation if applicable
    cocoon_meta:
      missing_acceptable: Not applicable for transactions that weren't cancelled.
  - name: created_at
    description: Timestamp of payment intent creation
    tests:
    - not_null
  - name: decline_code
    description: Decline code if payment was declined
    cocoon_meta:
      missing_acceptable: Not applicable for successful transactions.
  - name: error_charge_id
    description: ID of charge with last error
    cocoon_meta:
      missing_acceptable: Not applicable for transactions without errors.
  - name: error_code
    description: Error code of last failed attempt
    cocoon_meta:
      missing_acceptable: Not applicable for transactions without errors.
  - name: error_doc_url
    description: URL to error documentation
    cocoon_meta:
      missing_acceptable: Not applicable for transactions without errors.
  - name: error_message
    description: Error message for last failed attempt
    cocoon_meta:
      missing_acceptable: Not applicable for transactions without errors.
  - name: error_param
    description: Parameter that caused the error
    cocoon_meta:
      missing_acceptable: Not applicable for transactions without errors.
  - name: error_source_id
    description: ID of source causing the error
    cocoon_meta:
      missing_acceptable: Not applicable for transactions without errors.
  - name: error_type
    description: Type of the last error
    cocoon_meta:
      missing_acceptable: Not applicable for transactions without errors.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"subscription_history_data_projected" AS (
    -- Projection: Selecting 25 out of 26 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "_fivetran_active",
        "application_fee_percent",
        "billing",
        "billing_cycle_anchor",
        "billing_threshold_amount_gte",
        "billing_threshold_reset_billing_cycle_anchor",
        "cancel_at",
        "cancel_at_period_end",
        "canceled_at",
        "created",
        "current_period_end",
        "current_period_start",
        "customer_id",
        "days_until_due",
        "default_source_id",
        "ended_at",
        "livemode",
        "metadata",
        "quantity",
        "start_date",
        "status",
        "tax_percent",
        "trial_end",
        "trial_start"
    FROM "subscription_history_data"
),

"subscription_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> subscription_id
    -- _fivetran_active -> is_active
    -- application_fee_percent -> app_fee_percentage
    -- billing -> billing_method
    -- billing_cycle_anchor -> billing_cycle_start
    -- billing_threshold_amount_gte -> billing_threshold
    -- billing_threshold_reset_billing_cycle_anchor -> reset_billing_cycle_on_threshold
    -- cancel_at -> scheduled_cancellation_date
    -- canceled_at -> cancellation_date
    -- created -> creation_date
    -- days_until_due -> payment_due_days
    -- default_source_id -> default_payment_source_id
    -- ended_at -> end_date
    -- livemode -> is_live_mode
    -- quantity -> subscription_quantity
    -- status -> subscription_status
    -- tax_percent -> tax_percentage
    -- trial_end -> trial_end_date
    -- trial_start -> trial_start_date
    SELECT 
        "id" AS "subscription_id",
        "_fivetran_active" AS "is_active",
        "application_fee_percent" AS "app_fee_percentage",
        "billing" AS "billing_method",
        "billing_cycle_anchor" AS "billing_cycle_start",
        "billing_threshold_amount_gte" AS "billing_threshold",
        "billing_threshold_reset_billing_cycle_anchor" AS "reset_billing_cycle_on_threshold",
        "cancel_at" AS "scheduled_cancellation_date",
        "cancel_at_period_end",
        "canceled_at" AS "cancellation_date",
        "created" AS "creation_date",
        "current_period_end",
        "current_period_start",
        "customer_id",
        "days_until_due" AS "payment_due_days",
        "default_source_id" AS "default_payment_source_id",
        "ended_at" AS "end_date",
        "livemode" AS "is_live_mode",
        "metadata",
        "quantity" AS "subscription_quantity",
        "start_date",
        "status" AS "subscription_status",
        "tax_percent" AS "tax_percentage",
        "trial_end" AS "trial_end_date",
        "trial_start" AS "trial_start_date"
    FROM "subscription_history_data_projected"
),

"subscription_history_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- billing_method: The problem is that both values 'fdfjk' and 'fdsiew' in the billing_method column are meaningless strings that do not represent valid billing methods. These appear to be random character combinations with no discernible meaning or pattern. The correct values for a billing method column should be actual payment methods like 'credit card', 'debit card', 'PayPal', 'bank transfer', etc. Since we don't have information about the correct billing methods, we'll map these meaningless strings to an empty string. 
    SELECT
        "subscription_id",
        "is_active",
        "app_fee_percentage",
        CASE
            WHEN "billing_method" = '''fdfjk''' THEN ''''
            WHEN "billing_method" = '''fdsiew''' THEN ''''
            ELSE "billing_method"
        END AS "billing_method",
        "billing_cycle_start",
        "billing_threshold",
        "reset_billing_cycle_on_threshold",
        "scheduled_cancellation_date",
        "cancel_at_period_end",
        "cancellation_date",
        "creation_date",
        "current_period_end",
        "current_period_start",
        "customer_id",
        "payment_due_days",
        "default_payment_source_id",
        "end_date",
        "is_live_mode",
        "metadata",
        "subscription_quantity",
        "start_date",
        "subscription_status",
        "tax_percentage",
        "trial_end_date",
        "trial_start_date"
    FROM "subscription_history_data_projected_renamed"
),

"subscription_history_data_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- app_fee_percentage: from INT to DECIMAL
    -- billing_cycle_start: from VARCHAR to TIMESTAMP
    -- cancellation_date: from VARCHAR to TIMESTAMP
    -- creation_date: from VARCHAR to TIMESTAMP
    -- current_period_end: from VARCHAR to TIMESTAMP
    -- current_period_start: from VARCHAR to TIMESTAMP
    -- end_date: from VARCHAR to TIMESTAMP
    -- metadata: from VARCHAR to JSON
    -- scheduled_cancellation_date: from VARCHAR to TIMESTAMP
    -- start_date: from VARCHAR to TIMESTAMP
    -- tax_percentage: from INT to DECIMAL
    -- trial_end_date: from VARCHAR to TIMESTAMP
    -- trial_start_date: from VARCHAR to TIMESTAMP
    SELECT
        "subscription_id",
        "is_active",
        "billing_method",
        "billing_threshold",
        "reset_billing_cycle_on_threshold",
        "cancel_at_period_end",
        "customer_id",
        "payment_due_days",
        "default_payment_source_id",
        "is_live_mode",
        "subscription_quantity",
        "subscription_status",
        CAST("app_fee_percentage" AS DECIMAL) AS "app_fee_percentage",
        CAST("billing_cycle_start" AS TIMESTAMP) AS "billing_cycle_start",
        CAST("cancellation_date" AS TIMESTAMP) AS "cancellation_date",
        CAST("creation_date" AS TIMESTAMP) AS "creation_date",
        CAST("current_period_end" AS TIMESTAMP) AS "current_period_end",
        CAST("current_period_start" AS TIMESTAMP) AS "current_period_start",
        CAST("end_date" AS TIMESTAMP) AS "end_date",
        CAST("metadata" AS JSON) AS "metadata",
        CAST("scheduled_cancellation_date" AS TIMESTAMP) AS "scheduled_cancellation_date",
        CAST("start_date" AS TIMESTAMP) AS "start_date",
        CAST("tax_percentage" AS DECIMAL) AS "tax_percentage",
        CAST("trial_end_date" AS TIMESTAMP) AS "trial_end_date",
        CAST("trial_start_date" AS TIMESTAMP) AS "trial_start_date"
    FROM "subscription_history_data_projected_renamed_cleaned"
)

-- COCOON BLOCK END
SELECT * FROM "subscription_history_data_projected_renamed_cleaned_casted"

version: 2
models:
- name: stg_subscription_history_data
  description: The table is about subscription history. It contains details of individual
    subscriptions, including billing information, dates, status, and customer details.
    Each row represents a unique subscription with its ID, customer ID, billing cycle,
    trial period, cancellation details, and other relevant subscription attributes.
    The table tracks the lifecycle of subscriptions from creation to cancellation
    or expiration.
  columns:
  - name: subscription_id
    description: Unique identifier for the subscription
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each subscription.
        For this table, each row represents a unique subscription, and subscription_id
        is designed to be unique across rows.
  - name: is_active
    description: Indicates if the subscription is currently active
    tests:
    - not_null
  - name: billing_method
    description: Billing method or frequency
    tests:
    - not_null
    - accepted_values:
        values:
        - Monthly
        - Quarterly
        - Annually
        - Semi-annually
        - Weekly
        - Bi-weekly
        - Daily
        - One-time
        - Pay-per-use
        - Subscription
        - Installment
        - Prepaid
        - Postpaid
        - Metered
        - Flat rate
        - fdfjk
        - fdsiew
  - name: billing_threshold
    description: Minimum amount to trigger billing
    tests:
    - not_null
  - name: reset_billing_cycle_on_threshold
    description: Resets billing cycle anchor when threshold reached
    tests:
    - not_null
  - name: cancel_at_period_end
    description: Indicates if subscription cancels at period end
    tests:
    - not_null
  - name: customer_id
    description: Unique identifier for the customer
    tests:
    - not_null
  - name: payment_due_days
    description: Number of days until payment is due
    tests:
    - not_null
  - name: default_payment_source_id
    description: ID of default payment source
    tests:
    - not_null
  - name: is_live_mode
    description: Indicates if subscription is in live mode
    tests:
    - not_null
  - name: subscription_quantity
    description: Number of units in the subscription
    tests:
    - not_null
  - name: subscription_status
    description: Current status of the subscription
    tests:
    - not_null
    - accepted_values:
        values:
        - active
        - inactive
        - pending
        - cancelled
        - expired
        - suspended
        - trialing
        - past_due
        - unpaid
        - on_hold
  - name: app_fee_percentage
    description: Percentage of the subscription fee taken as application fee
    tests:
    - not_null
  - name: billing_cycle_start
    description: Date that anchors the billing cycle
    tests:
    - not_null
  - name: cancellation_date
    description: Date when subscription was canceled
    tests:
    - not_null
  - name: creation_date
    description: Date when subscription was created
    tests:
    - not_null
  - name: current_period_end
    description: End date of current billing period
    tests:
    - not_null
  - name: current_period_start
    description: Start date of current billing period
    tests:
    - not_null
  - name: end_date
    description: Date when subscription ended
    tests:
    - not_null
  - name: metadata
    description: Additional information about the subscription
    tests:
    - not_null
  - name: scheduled_cancellation_date
    description: Date when subscription is set to cancel
    tests:
    - not_null
  - name: start_date
    description: Date when subscription started
    tests:
    - not_null
  - name: tax_percentage
    description: Percentage of tax applied to subscription
    tests:
    - not_null
  - name: trial_end_date
    description: End date of trial period
    tests:
    - not_null
  - name: trial_start_date
    description: Start date of trial period
    tests:
    - not_null