-
Notifications
You must be signed in to change notification settings - Fork 1
/
Shopify LandingZone Table Creation.txt
275 lines (270 loc) · 10.7 KB
/
Shopify LandingZone Table Creation.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
Shopify- Snowflake lannding Zone
After data extraction from Shopify itself(via python scripts and REST-API), the landing zone tables are created in the snowflake environment.
The extracted data from shopify is loaded to these landing zone tables.
create or replace TABLE database.schema.CUSTOMERS (
CUSTOMER_ID NUMBER(38,0),
EMAIL VARCHAR(16777216),
ACCEPTS_MARKETING VARCHAR(16777216),
CREATED_AT TIMESTAMP_NTZ(9),
UPDATED_AT TIMESTAMP_NTZ(9),
FIRST_NAME VARCHAR(16777216),
LAST_NAME VARCHAR(16777216),
ORDERS_COUNT VARCHAR(16777216),
CUSTOMER_STATE VARCHAR(16777216),
TOTAL_SPENT VARCHAR(16777216),
LAST_ORDER_ID VARCHAR(16777216),
NOTE VARCHAR(16777216),
VERIFIED_EMAIL VARCHAR(16777216),
MULTIPASS_IDENTIFIER VARCHAR(16777216),
TAX_EXEMPT VARCHAR(16777216),
TAGS VARCHAR(16777216),
LAST_ORDER_NAME VARCHAR(16777216),
CURRENCY VARCHAR(16777216),
PHONE VARCHAR(16777216),
ADDRESSES VARCHAR(16777216),
ACCEPTS_MARKETING_UPDATED_AT VARCHAR(16777216),
MARKETING_OPT_IN_LEVEL VARCHAR(16777216),
TAX_EXEMPTIONS VARCHAR(16777216),
EMAIL_MARKETING_CONSENT VARCHAR(16777216),
SMS_MARKETING_CONSENT VARCHAR(16777216),
ADMIN_GRAPHQL_API_ID VARCHAR(16777216),
DEFAULT_ADDRESS VARCHAR(16777216),
SHA1_CUSTOMER_ID BINARY(8388608) AS (SHA1_BINARY(CAST(CUSTOMER_ID AS VARCHAR(16777216)))),
SOURCE_SYSTEM VARCHAR(50) DEFAULT 'SHOPIFY_CUSTOMERS',
LDTS TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP()
);
---------------------------------------------------------------------------------------------------------
create or replace TABLE database.schema.ORDERS (
ORDER_ID NUMBER(38,0),
ADMIN_GRAPHQL_API_ID VARCHAR(16777216),
APP_ID VARCHAR(16777216),
BROWSER_IP VARCHAR(16777216),
BUYER_ACCEPTS_MARKETING VARCHAR(16777216),
CANCEL_REASON VARCHAR(16777216),
CANCELLED_AT VARCHAR(16777216),
CART_TOKEN VARCHAR(16777216),
CHECKOUT_ID VARCHAR(16777216),
CHECKOUT_TOKEN VARCHAR(16777216),
CLIENT_DETAILS VARCHAR(16777216),
CLOSED_AT VARCHAR(16777216),
CONFIRMED VARCHAR(16777216),
CONTACT_EMAIL VARCHAR(16777216),
CREATED_AT TIMESTAMP_NTZ(9),
CURRENCY VARCHAR(16777216),
CURRENT_SUBTOTAL_PRICE VARCHAR(16777216),
CURRENT_SUBTOTAL_PRICE_SET VARCHAR(16777216),
CURRENT_TOTAL_DISCOUNTS VARCHAR(16777216),
CURRENT_TOTAL_DISCOUNTS_SET VARCHAR(16777216),
CURRENT_TOTAL_DUTIES_SET VARCHAR(16777216),
CURRENT_TOTAL_PRICE VARCHAR(16777216),
CURRENT_TOTAL_PRICE_SET VARCHAR(16777216),
CURRENT_TOTAL_TAX VARCHAR(16777216),
CURRENT_TOTAL_TAX_SET VARCHAR(16777216),
CUSTOMER_LOCALE VARCHAR(16777216),
DEVICE_ID VARCHAR(16777216),
DISCOUNT_CODES VARCHAR(16777216),
EMAIL VARCHAR(16777216),
ESTIMATED_TAXES VARCHAR(16777216),
FINANCIAL_STATUS VARCHAR(16777216),
FULFILLMENT_STATUS VARCHAR(16777216),
GATEWAY VARCHAR(16777216),
LANDING_SITE VARCHAR(16777216),
LANDING_SITE_REF VARCHAR(16777216),
LOCATION_ID VARCHAR(16777216),
NAME VARCHAR(16777216),
NOTE VARCHAR(16777216),
NOTE_ATTRIBUTES VARCHAR(16777216),
NUMBER VARCHAR(16777216),
ORDER_NUMBER VARCHAR(16777216),
ORDER_STATUS_URL VARCHAR(16777216),
ORIGINAL_TOTAL_DUTIES_SET VARCHAR(16777216),
PAYMENT_GATEWAY_NAMES VARCHAR(16777216),
PHONE VARCHAR(16777216),
PRESENTMENT_CURRENCY VARCHAR(16777216),
PROCESSED_AT TIMESTAMP_NTZ(9),
PROCESSING_METHOD VARCHAR(16777216),
REFERENCE VARCHAR(16777216),
REFERRING_SITE VARCHAR(16777216),
SOURCE_IDENTIFIER VARCHAR(16777216),
SOURCE_NAME VARCHAR(16777216),
SOURCE_URL VARCHAR(16777216),
SUBTOTAL_PRICE VARCHAR(16777216),
SUBTOTAL_PRICE_SET VARCHAR(16777216),
TAGS VARCHAR(16777216),
TAX_LINES VARCHAR(16777216),
TAXES_INCLUDED VARCHAR(16777216),
TEST VARCHAR(16777216),
TOKEN VARCHAR(16777216),
TOTAL_DISCOUNTS VARCHAR(16777216),
TOTAL_DISCOUNTS_SET VARCHAR(16777216),
TOTAL_LINE_ITEMS_PRICE VARCHAR(16777216),
TOTAL_LINE_ITEMS_PRICE_SET VARCHAR(16777216),
TOTAL_OUTSTANDING VARCHAR(16777216),
TOTAL_PRICE VARCHAR(16777216),
TOTAL_PRICE_SET VARCHAR(16777216),
TOTAL_PRICE_USD VARCHAR(16777216),
TOTAL_SHIPPING_PRICE_SET VARCHAR(16777216),
TOTAL_TAX VARCHAR(16777216),
TOTAL_TAX_SET VARCHAR(16777216),
TOTAL_TIP_RECEIVED VARCHAR(16777216),
TOTAL_WEIGHT VARCHAR(16777216),
UPDATED_AT TIMESTAMP_NTZ(9),
USER_ID VARCHAR(16777216),
BILLING_ADDRESS VARCHAR(16777216),
CUSTOMER VARCHAR(16777216),
DISCOUNT_APPLICATIONS VARCHAR(16777216),
FULFILLMENTS VARCHAR(16777216),
LINE_ITEMS VARCHAR(16777216),
PAYMENT_TERMS VARCHAR(16777216),
REFUNDS VARCHAR(16777216),
SHIPPING_ADDRESS VARCHAR(16777216),
SHIPPING_LINES VARCHAR(16777216),
PAYMENT_DETAILS VARCHAR(16777216),
CUSTOMER_ID NUMBER(38,0),
SHA1_ORDER_ID BINARY(8388608) AS (SHA1_BINARY(CAST(ORDER_ID AS VARCHAR(16777216)))),
SHA1_CUSTOMER_ID BINARY(8388608) AS (SHA1_BINARY(CAST(CUSTOMER_ID AS VARCHAR(16777216)))),
SOURCE_SYSTEM VARCHAR(50) DEFAULT 'SHOPIFY_ORDERS',
LDTS TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP()
);
---------------------------------------------------------------------------------------------------------
create or replace TABLE database.schema.ORDERS_LINE_ITEMS (
LINE_ITEM_ID NUMBER(38,0),
ADMIN_GRAPHQL_API_ID VARCHAR(16777216),
FULFILLABLE_QUANTITY VARCHAR(16777216),
FULFILLMENT_SERVICE VARCHAR(16777216),
FULFILLMENT_STATUS VARCHAR(16777216),
GIFT_CARD VARCHAR(16777216),
GRAMS VARCHAR(16777216),
NAME VARCHAR(16777216),
PRICE VARCHAR(16777216),
PRODUCT_EXISTS VARCHAR(16777216),
PRODUCT_ID NUMBER(38,0),
PROPERTIES VARCHAR(16777216),
QUANTITY VARCHAR(16777216),
REQUIRES_SHIPPING VARCHAR(16777216),
SKU VARCHAR(16777216),
TAXABLE VARCHAR(16777216),
TITLE VARCHAR(16777216),
TOTAL_DISCOUNT VARCHAR(16777216),
VARIANT_ID NUMBER(38,0),
VARIANT_INVENTORY_MANAGEMENT VARCHAR(16777216),
VARIANT_TITLE VARCHAR(16777216),
VENDOR VARCHAR(16777216),
TAX_LINES VARCHAR(16777216),
DUTIES VARCHAR(16777216),
DISCOUNT_ALLOCATIONS VARCHAR(16777216),
ORIGIN_LOCATION_ID VARCHAR(16777216),
ORIGIN_LOCATION_COUNTRY_CODE VARCHAR(16777216),
ORIGIN_LOCATION_PROVINCE_CODE VARCHAR(16777216),
ORIGIN_LOCATION_NAME VARCHAR(16777216),
ORIGIN_LOCATION_ADDRESS1 VARCHAR(16777216),
ORIGIN_LOCATION_ADDRESS2 VARCHAR(16777216),
ORIGIN_LOCATION_CITY VARCHAR(16777216),
ORIGIN_LOCATION_ZIP VARCHAR(16777216),
PRICE_SET_SHOP_MONEY_AMOUNT VARCHAR(16777216),
PRICE_SET_SHOP_MONEY_CURRENCY_CODE VARCHAR(16777216),
PRICE_SET_PRESENTMENT_MONEY_AMOUNT VARCHAR(16777216),
PRICE_SET_PRESENTMENT_MONEY_CURRENCY_CODE VARCHAR(16777216),
TOTAL_DISCOUNT_SET_SHOP_MONEY_AMOUNT VARCHAR(16777216),
TOTAL_DISCOUNT_SET_SHOP_MONEY_CURRENCY_CODE VARCHAR(16777216),
TOTAL_DISCOUNT_SET_PRESENTMENT_MONEY_AMOUNT VARCHAR(16777216),
TOTAL_DISCOUNT_SET_PRESENTMENT_MONEY_CURRENCY_CODE VARCHAR(16777216),
ORDER_ID NUMBER(38,0),
SHA1_ORDER_ID BINARY(8388608) AS (SHA1_BINARY(CAST(ORDER_ID AS VARCHAR(16777216)))),
SHA1_LINE_ITEM_ID BINARY(8388608) AS (SHA1_BINARY(CAST(LINE_ITEM_ID AS VARCHAR(16777216)))),
SHA1_PRODUCT_ID BINARY(8388608) AS (SHA1_BINARY(CAST(PRODUCT_ID AS VARCHAR(16777216)))),
SHA1_VARIANT_ID BINARY(8388608) AS (SHA1_BINARY(CAST(VARIANT_ID AS VARCHAR(16777216)))),
SOURCE_SYSTEM VARCHAR(50) DEFAULT 'SHOPIFY_ORDER_TRANSACTIONS',
LDTS TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP(),
UPDATED_AT TIMESTAMP_NTZ(9),
CREATED_AT TIMESTAMP_NTZ(9)
);
---------------------------------------------------------------------------------------------------------
create or replace database.schema.ORDER_TRANSACTIONS (
TRANSACTION_ID NUMBER(38,0),
ORDER_ID NUMBER(38,0),
KIND VARCHAR(16777216),
GATEWAY VARCHAR(16777216),
STATUS VARCHAR(16777216),
MESSAGE VARCHAR(16777216),
CREATED_AT VARCHAR(16777216),
TEST VARCHAR(16777216),
TRANSACTION_AUTHORIZATION VARCHAR(16777216),
LOCATION_ID VARCHAR(16777216),
USER_ID VARCHAR(16777216),
PARENT_ID VARCHAR(16777216),
PROCESSED_AT VARCHAR(16777216),
DEVICE_ID VARCHAR(16777216),
ERROR_CODE VARCHAR(16777216),
SOURCE_NAME VARCHAR(16777216),
RECEIPT VARCHAR(16777216),
AMOUNT VARCHAR(16777216),
CURRENCY VARCHAR(16777216),
PAYMENT_ID VARCHAR(16777216),
TOTAL_UNSETTLED_SET VARCHAR(16777216),
ADMIN_GRAPHQL_API_ID VARCHAR(16777216),
PAYMENT_DETAILS VARCHAR(16777216),
PAYMENTS_REFUND_ATTRIBUTES VARCHAR(16777216),
SHA1_ORDER_ID BINARY(8388608) AS (SHA1_BINARY(CAST(ORDER_ID AS VARCHAR(16777216)))),
SHA1_TRANSACTION_ID BINARY(8388608) AS (SHA1_BINARY(CAST(TRANSACTION_ID AS VARCHAR(16777216)))),
SOURCE_SYSTEM VARCHAR(50) DEFAULT 'SHOPIFY_ORDER_TRANSACTIONS',
LDTS TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP()
);
---------------------------------------------------------------------------------------------------------
create or replace TABLE database.schema.PRODUCTS (
PRODUCT_ID NUMBER(38,0),
TITLE VARCHAR(16777216),
BODY_HTML VARCHAR(16777216),
VENDOR VARCHAR(16777216),
PRODUCT_TYPE VARCHAR(16777216),
CREATED_AT TIMESTAMP_NTZ(9),
PRODUCT_HANDLE VARCHAR(16777216),
UPDATED_AT TIMESTAMP_NTZ(9),
PUBLISHED_AT VARCHAR(25),
TEMPLATE_SUFFIX VARCHAR(16777216),
STATUS VARCHAR(16777216),
PUBLISHED_SCOPE VARCHAR(16777216),
TAGS VARCHAR(16777216),
ADMIN_GRAPHQL_API_ID VARCHAR(16777216),
VARIANTS VARCHAR(16777216),
OPTIONS VARCHAR(16777216),
IMAGES VARCHAR(16777216),
IMAGE VARCHAR(16777216),
SHA1_PRODUCT_KEY BINARY(8388608) AS (SHA1_BINARY(CAST(PRODUCT_ID AS VARCHAR(16777216)))),
SOURCE_SYSTEM VARCHAR(50) DEFAULT 'SHOPIFY_PRODUCTS',
LDTS TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP()
);
---------------------------------------------------------------------------------------------------------
create or replace TABLE database.schema.PRODUCTS_VARIANTS (
VARIANT_ID NUMBER(38,0),
PRODUCT_ID NUMBER(38,0),
TITLE VARCHAR(16777216),
PRICE VARCHAR(16777216),
SKU VARCHAR(16777216),
POSITION NUMBER(38,0),
INVENTORY_POLICY VARCHAR(16777216),
COMPARE_AT_PRICE VARCHAR(16777216),
FULFILLMENT_SERVICE VARCHAR(16777216),
INVENTORY_MANAGEMENT VARCHAR(16777216),
OPTION1 VARCHAR(16777216),
OPTION2 VARCHAR(16777216),
OPTION3 VARCHAR(16777216),
CREATED_AT TIMESTAMP_NTZ(9),
UPDATED_AT TIMESTAMP_NTZ(9),
TAXABLE BOOLEAN,
BARCODE VARCHAR(16777216),
GRAMS NUMBER(38,0),
IMAGE_ID FLOAT,
WEIGHT FLOAT,
WEIGHT_UNIT VARCHAR(16777216),
INVENTORY_ITEM_ID NUMBER(38,0),
INVENTORY_QUANTITY NUMBER(38,0),
OLD_INVENTORY_QUANTITY NUMBER(38,0),
REQUIRES_SHIPPING BOOLEAN,
ADMIN_GRAPHQL_API_ID VARCHAR(16777216),
SHA1_VARIANT_ID BINARY(8388608) AS (SHA1_BINARY(CAST(VARIANT_ID AS VARCHAR(16777216)))),
SHA1_PRODUCT_ID BINARY(8388608) AS (SHA1_BINARY(CAST(PRODUCT_ID AS VARCHAR(16777216)))),
SOURCE_SYSTEM VARCHAR(50) DEFAULT 'SHOPIFY_PRODUCTS_VARIANTS',
LDTS TIMESTAMP_NTZ(9) DEFAULT CURRENT_TIMESTAMP()
);
---------------------------------------------------------------------------------------------------------