Customers Data Lake Schema [Up to date as of Nov 6, 2023]

[
  {
    "Name": "id",
    "Type": "string"
  },
  {
    "Name": "tenant_id",
    "Type": "string"
  },
  {
    "Name": "bc_customer_id",
    "Type": "bigint"
  },
  {
    "Name": "title",
    "Type": "string"
  },
  {
    "Name": "first_name",
    "Type": "string"
  },
  {
    "Name": "last_name",
    "Type": "string"
  },
  {
    "Name": "company",
    "Type": "string"
  },
  {
    "Name": "email",
    "Type": "string"
  },
  {
    "Name": "phone",
    "Type": "string"
  },
  {
    "Name": "tax_exempt_code",
    "Type": "string"
  },
  {
    "Name": "facebook",
    "Type": "string"
  },
  {
    "Name": "instagram",
    "Type": "string"
  },
  {
    "Name": "twitter",
    "Type": "string"
  },
  {
    "Name": "tiktok",
    "Type": "string"
  },
  {
    "Name": "created_by",
    "Type": "string"
  },
  {
    "Name": "created_at",
    "Type": "timestamp"
  },
  {
    "Name": "updated_at",
    "Type": "timestamp"
  },
  {
    "Name": "deleted_at",
    "Type": "timestamp"
  },
  {
    "Name": "wdc_website_id",
    "Type": "string"
  },
  {
    "Name": "wdc_member_id",
    "Type": "string"
  },
  {
    "Name": "wdc_customer_number",
    "Type": "string"
  },
  {
    "Name": "wdc_alt_account_number",
    "Type": "string"
  },
  {
    "Name": "wdc_date_added",
    "Type": "timestamp"
  },
  {
    "Name": "wdc_is_active_club_member",
    "Type": "string"
  },
  {
    "Name": "customer_group_id",
    "Type": "string"
  },
  {
    "Name": "date_of_birth",
    "Type": "timestamp"
  },
  {
    "Name": "password_reseted_at",
    "Type": "timestamp"
  },
  {
    "Name": "phone_country_code",
    "Type": "string"
  },
  {
    "Name": "lifetime_total_order",
    "Type": "decimal(28,6)"
  },
  {
    "Name": "lifetime_average_order",
    "Type": "decimal(28,6)"
  },
  {
    "Name": "lifetime_order_count",
    "Type": "int"
  },
  {
    "Name": "last_order_datetime",
    "Type": "timestamp"
  },
  {
    "Name": "bc_shopper_id",
    "Type": "string"
  },
  {
    "Name": "loyalty_points",
    "Type": "int"
  },
  {
    "Name": "fortis_customer_id",
    "Type": "string"
  },
  {
    "Name": "customer_segments",
    "Type": "array<struct<id:string,tenant_id:string,deleted_at:timestamp,created_at:timestamp,updated_at:timestamp,customer_id:string,segment_id:string>>"
  },
  {
    "Name": "customer_tags",
    "Type": "array<struct<id:string,tenant_id:string,customer_id:string,tag_id:string,created_at:timestamp,updated_at:timestamp,deleted_at:timestamp>>"
  },
  {
    "Name": "customer_relationships",
    "Type": "array<struct<id:string,tenant_id:string,first_name:string,last_name:string,nickname:string,relationship_type:string,birthday_day:int,birthday_month:int,phone:string,relationship_notes:string,created_at:timestamp,updated_at:timestamp,deleted_at:timestamp,customer_id:string,phone_country_code:string,linked_customer_id:string>>"
  },
  {
    "Name": "customer_dates",
    "Type": "array<struct<id:string,tenant_id:string,date_day:int,date_month:int,customer_date_notes:string,created_at:timestamp,updated_at:timestamp,deleted_at:timestamp,customer_id:string,date_type_option_id:string>>"
  },
  {
    "Name": "addresses",
    "Type": "array<struct<id:string,tenant_id:string,first_name:string,last_name:string,address1:string,address2:string,country_code:string,zip:string,subdivision:string,city:string,phone:string,date_of_birth:timestamp,address_notes:string,default:string,address_type:string,created_at:timestamp,updated_at:timestamp,deleted_at:timestamp,customer_id:string,email:string,business_name:string,bc_address_id:bigint,phone_country_code:string>>"
  },
  {
    "Name": "account_settings",
    "Type": "array<struct<id:string,tenant_id:string,sales_associate_id:string,referred_by_cust_id:string,transaction_email:string,transaction_phone:string,transaction_sms:string,marketing_email:string,marketing_phone:string,marketing_sms:string,cookies_necessary:string,cookies_functional:string,cookies_analysis:string,cookies_targeting:string,created_at:timestamp,updated_at:timestamp,deleted_at:timestamp,customer_id:string,last_updated_transaction_email:timestamp,last_updated_transaction_phone:timestamp,last_updated_transaction_sms:timestamp,last_updated_marketing_email:timestamp,last_updated_marketing_phone:timestamp,last_updated_marketing_sms:timestamp,last_updated_cookies_necessary:timestamp,last_updated_cookies_functional:timestamp,last_updated_cookies_analysis:timestamp,last_updated_cookies_targeting:timestamp>>"
  },
  {
    "Name": "notes",
    "Type": "array<struct<id:string,tenant_id:string,subject:string,description:string,highlighted:string,note_type:string,created_at:timestamp,updated_at:timestamp,deleted_at:timestamp,customer_id:string>>"
  },
  {
    "Name": "tasks",
    "Type": "array<struct<id:string,tenant_id:string,subject:string,status:string,description:string,due_date:timestamp,assignee_id:string,task_type:string,created_at:timestamp,updated_at:timestamp,deleted_at:timestamp,customer_id:string>>"
  },
  {
    "Name": "wallets",
    "Type": "array<struct<id:string,tenant_id:string,store_credits:decimal(28,6),created_at:timestamp,updated_at:timestamp,deleted_at:timestamp,customer_id:string>>"
  },
  {
    "Name": "p_tenant_id",
    "Type": "string",
    "PartitionKey": "Partition (0)"
  },
  {
    "Name": "year",
    "Type": "string",
    "PartitionKey": "Partition (1)"
  },
  {
    "Name": "month",
    "Type": "string",
    "PartitionKey": "Partition (2)"
  },
  {
    "Name": "day",
    "Type": "string",
    "PartitionKey": "Partition (3)"
  },
  {
    "Name": "source",
    "Type": "string",
    "PartitionKey": "Partition (4)"
  }
]