-- Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.
-- SPDX-License-Identifier: MIT-0

DROP TABLE IF EXISTS dw_mystore.customer_dim CASCADE;

CREATE TABLE dw_mystore.customer_dim (
    customer_key            int         ENCODE raw NOT NULL SORTKEY GENERATED BY DEFAULT AS IDENTITY
   ,customer_id             varchar(16) ENCODE zstd NOT NULL
   ,salutation              varchar(10) ENCODE bytedict NOT NULL
   ,first_name              varchar(20) ENCODE zstd NOT NULL
   ,last_name               varchar(30) ENCODE zstd NOT NULL
   ,birth_country           varchar(30) ENCODE bytedict NOT NULL
   ,email_address           varchar(50) ENCODE zstd NOT NULL
   ,birth_date              date        ENCODE delta32k NOT NULL
   ,gender                  varchar(10) ENCODE zstd NOT NULL
   ,marital_status          varchar(10) ENCODE bytedict NOT NULL
   ,education_status        varchar(30) ENCODE bytedict NOT NULL
   ,purchase_estimate       int         ENCODE zstd NOT NULL
   ,credit_rating           varchar(10) ENCODE bytedict NOT NULL
   ,buy_potential           varchar(10) ENCODE bytedict NOT NULL
   ,vehicle_count           int         ENCODE az64 NOT NULL
   ,income_band_lower_bound int         ENCODE zstd NOT NULL
   ,income_band_upper_bound int         ENCODE zstd NOT NULL
   ,start_date              timestamp   ENCODE az64 NOT NULL
   ,address_id              varchar(16) ENCODE zstd NOT NULL
   ,address_date            timestamp   ENCODE az64 NOT NULL
   ,street                  varchar(40) ENCODE zstd NOT NULL
   ,city                    varchar(60) ENCODE zstd NOT NULL
   ,county                  varchar(30) ENCODE zstd NOT NULL
   ,state                   varchar(2)  ENCODE bytedict NOT NULL
   ,zip                     varchar(10) ENCODE zstd NOT NULL
   ,country                 varchar(20) ENCODE zstd NOT NULL
   ,gmt_offset              decimal(5,2)ENCODE zstd NOT NULL
   ,location_type           varchar(20) ENCODE zstd NOT NULL
   ,scd_start_date          timestamp   ENCODE az64 NOT NULL
   ,scd_end_date            timestamp   ENCODE az64 NOT NULL
   ,scd_current_flag        varchar(1)  ENCODE zstd NOT NULL
   ,dw_insert_date          timestamp   ENCODE az64 NOT NULL  DEFAULT SYSDATE
   ,dw_update_date          timestamp   ENCODE az64
   ,CONSTRAINT pk_customer_dim PRIMARY KEY ( customer_key )
)
DISTKEY (customer_key);