# Building an E-Commerce System with TiDB Cloud and Snowflake

![20220720-190155](https://static.pingcap.com/files/2022/07/20220720-190155-1024x341.jpeg "20220720-190155")

> **Authors**: [Zhen Chen](http://github.com/shczhen), [Zhiyuan Liang](http://github.com/Mini256) (TiDB Community Engineers at PingCAP)  
**Editors**: Fendy Feng, Tom Dewan

[TiDB Cloud](https://docs.pingcap.com/tidbcloud/public-preview?utm_source=ossinsight&utm_medium=referral) is a fully-managed Database-as-a-Service (DBaaS) that brings everything great about TiDB, an open source Hybrid Transactional and Analytical Processing (HTAP) database with horizontal scalability, to your cloud. [Snowflake](https://www.snowflake.com/) is a cloud computing-based data warehousing company that provides cloud-based data storage and analytics service.

By combining these two Software-as-a-Service (SaaS) products together, we quickly built an online e-commerce system. This system can process a large amount of data in real time. It also alleviates the scalability bottleneck of traditional MySQL solutions— especially when the amount of business data skyrockets. Besides, this system is easier to operate and maintain.

In this tutorial, we will introduce this e-commerce solution and walk you through how to configure and use it. You can also check out the following video directly.

## A quick view of this solution

This system uses TiDB Cloud to process and store business data such as orders, expresses and user profile data in real time, and uses Snowflake as a data warehouse to archive cold data and provide data warehouse modeling. Due to TiDB’s powerful real-time analysis capabilities, the solution provides you a unified view to check the analytical results, and can convert the results into quick business intelligence for decision making. The interface of this solution has six sections, including the Dashboard, Customers, Items, Recommended Items, Orders, and Pipelines.

### Dashboard

The **Dashboard** gives you a quick overview of all the analytical results returned by TiDB Cloud. For example, you can check the top sales in real time by product items and their prices, check the total amount of money earned, the total number of orders sold, and the total number of customers on a specific day or in real time.

![Dashboard](https://lh3.googleusercontent.com/RLh1z-ekR509CuFhfTuLcyGMaCs5NshPhbRA18_pWDQMWK_q1Wy-bkskm4Hr9fGP4DFgXCwLEckSdvNC__ouJ4U2T--nlPxETtU4xIoNBW_JSfBLhEEESYLfe0xQu_LGcNXXYUlB_UXVdvh0BZGw0Fg)

At the bottom of the **Dashboard** page, you also see a real-time analysis chart that filters today’s orders from the order table, calculates the sum, and writes it into the `order_stats_history` table every two minutes.

![real-time analysis](https://lh3.googleusercontent.com/K-snA4V-ktUYbPk8yhhfk0IzZ8vHB4mjCzvNSvRMG8zBrfAVyi-c9W8uNiCjwDXgPk0AM_03TcFURYOlLgQxqzHEk90ndh_j4NDEZazwN9DCNQdk1lqCSY6XaC7MBMgrLZKXpr_nzQdoJB2MLmCjfPc)

### Customers

Snowflake labels each customer as a high or low net worth user according to their spending. If a customer’s spending is greater than the average spending of all customers, they are labeled as a high-net-worth user, otherwise they are labeled as a low-net-worth user. On the **Customer** page, you can easily check any customer’s user label and their average spending by entering their usernames.

![Customers](https://lh6.googleusercontent.com/9DpxdwfuaoXiw35ohge3TGcMi4w6cEbti3ZWZXGHh0FSPuJLUTusSNXklzphsDkj8Q54p11ALbAUaE18WlRVBJhblZq8-m4LzVAz0Js2kN3B4sdKsIACz0L0M0sgqezlHnkEQDKZiYe7KhbRpZdCAAc)

### Items

The **Items** page displays all the products with their prices.

![Items](https://lh6.googleusercontent.com/7ZZrLI_LFLCpEQ-9m-xVvKkf3DtyzIn4XNusx9tHX6upv5hEVqBbOjQkfvKktwPFMMGGNYBAufQ3T9ZC2pkHyVFAeuubxAJoS41dMlk3MedG6LOIvhmAmjKcDxoJHABWkrbcfFCyna8p3hd4psKalzw)

### Recommended Items

If you enter the username of any customer on the **Recommended Items** page, you can get a list of recommended products for this customer according to their purchasing preferences.

![Recommended Items](https://lh6.googleusercontent.com/pM3ulRi1Z4LOFKzxEQ_7429J9iiJ7xPeB5o2cv2dDy-ljhLfJB9YERqWm9N_pXw0Nqn-7LV2QBH3TZHbgxn-IoQSWYeNH-iIZ55wbJU_aqDrpyK98dAJjGNIpr8_VHQPEcvlIxY75mHixBpfpZQ-vMw)

### Orders

On the **Orders** page, you can check all the orders in real time, and by entering the username of any customers, you can also check all their orders in real time.

![Orders](https://lh5.googleusercontent.com/ELasbiu8EhHEram4mpNU_ofyFhlqjFm1Y4xfv4vmZzCaUhfSjwpbHNfT_d35ghhfefA6S_Y3TQfBk7XkP4rjH6NTILyX4lwY_AOi1Hr8eiRFgtcjPOq80CVMmksdIrc5v9vXVyWx4NXvAO4m2P9kQTU)

### Pipeline

The **Pipeline** page guides you to build the pipeline between TiDB Cloud and Snowflake. We will cover this in detail later.

### ![Pipeline](https://lh6.googleusercontent.com/B0wLrMiDt1l3MnU22OTLm3-ofr-ggWQuEDykcG0lAWjqE-XACkEP0XNtqgy_GjUFFIELnvvg-R637ZzteUrgElpcJ7menJXpezuwwX-bvYV-CVNewkjFPw747_eA-I0SmL9l5TO2XTb6Lgfu3xzqB80)

## System architecture

The diagram below shows the architecture of our e-commerce system. It includes three layers: the business layer (frontend application), the data center layer (TiDB Cloud), and the data warehouse layer (Snowflake).

![System architecture](https://lh5.googleusercontent.com/2qDULeAABgnjEx3M4M1IfbmLnREmuIj9FYmvzZ7_GzDwIKdCTq4e2lWiaDvuztLzDP52E6PUJQBT7dp8cldvFOnP-g8k3Ol0bxCHO1WxRtwWdJjEMpCoBX3dTXoWdA-uoPGwrcuCjo-L6EqhIIz6u28) _The demo architecture_

### Business layer

The business layer is where frontend applications generate a large amount of business data such as orders and shipping information.

### Data center layer

In the data center layer, TiDB Cloud collects and stores the business data migrated from the business layer. It provides data services, such as recommending hot-selling products with high or low unit prices, filtering high- or low-net-worth customers, and generating weekly sales reports. TiDB Cloud also provides a unified view where users can query their orders and shipping information in real time, and business staff can monitor daily product sales, analyze user consumption, and label users according to their purchasing behaviors in real time.

### Data warehouse layer

In this layer, Snowflake serves as a data warehouse to archive cold data such as historical orders and express information from TiDB Cloud. Snowflake also offers a complete data warehouse modeling; for example, it can calculate the monthly bill of each user.

## Before you try this solution

This solution needs a custom backend to perform all data operations. So, if you’d like to try this by yourself, review the following configuration guide to learn how to configure and use it.

## **Step by step configuration guide**

### Run the demo backend

1. Open the [GitHub repository](https://github.com/Mini256/tidb-snowflake-e-commerce-demo) of our application and click **Open in Gitpod**.![Open in Gitpod](https://lh6.googleusercontent.com/pHZLPTwR7qKjXCyzUlzI-JZN_tYfEmMtlnOKj2vfBLyebattorZ77-rI_iOVPqyAxih1Fvi-bGVgNECARG0fUfa7oHpNhQwUBxfqqsDit06HFsrTT2Vz_4A_Qhkw7SiHats7pzvNvmSHONXPLU1kwRU)
2. Authorize Gitpod to access your GitHub account. Click **Continue with GitHub** and sign in to your GiHub account.  
    ![Continue with GitHub](https://lh3.googleusercontent.com/NveWF6USmLHT5v_6Ls_w7oLWS0oUiqZ8MV8kF9BJBLioR0m4pCqnAf3m9qPDeC_sNqHJG90sOvDoQGAXB6YMq425oYTsjzyro9qDKmTOICEUE5LXjgPdnxdhPFNeb0_utoxt6LlGRp6AWTG62KGytOQ)
3. After you finish the authorization, a new workspace runs automatically. We have defined the scripts in `gitpod.yml`.
4. On the **Simple Browser** tab, under the **Endpoint** field, click **Check**. When port `8080` is ready, the Gitpod workspace will open a preview tab of the live demo.

If the workspace runs successfully, the backend server will serve port `8080`. However, in the Gitpod workspace, the port needs to be forwarded. You can find the public port `8080` URL in the **Endpoint** field on the **Simple Browser** tab as shown in the screenshot below.![image](https://lh3.googleusercontent.com/UHQ6ADXr_VN42XcbiaNYyyh_dtM4x1O9an7EobOjQ-VbRhXFw7wCiH-3QVk0AWtBTfYbCmHmJkWkDWUhH6rIn_21gSJ4DBJ2rX9AGoa6Znf4PaAe_BBbK4QFu-vqfUvXQfGapZ4b2p-Z4-S0STn9kEw)

**Warning:** The Gitpod preview tab has limitations and may cause your operations to fail. To avoid this, we recommend you copy the **Endpoint** URL and paste to a new browser tab.

### Configure the demo

1. Visit this [demo](https://tidb-snowflake-e-commerce-demo.vercel.app) and paste the endpoint URL you copied earlier to the **Endpoint** field. Then, click **Check**. Now, port `8080` is successfully connected. ![image](https://lh5.googleusercontent.com/Fc3Nr0fQG7SmTRwK9VL5F0xUnB2hknrGbIuvD2LuJuIFUmxVTdWOY4Tk8j4_TxrsbGRc-Jxplvxwwja3dXgN40BKSwZMxQxellZa2RnzDat1pgGmJXu18SEIE5I_uIVlgu4PJe7k9NbmdAYGLVWpl8E)
2. Click **Walkthrough** and follow the guide to [sign in](https://tidbcloud.com/) and configure your TiDB Cloud. If you do not have a TiDB Cloud account, click [here](https://tidbcloud.com/free-trial) to sign up for one first. ![image](https://lh6.googleusercontent.com/u8R4PF7oAju8gl7Bb7fL474X4GN3e-zVqLMDBvXjjNkXnKFs_9GIWHBC7IjRA4KltEjCFrH8ihqHU0uKtqKl-Zi_W72Tbxz1HLJBgEoyXolWFYLp52SB1Gm26K_HpXCSZ-sgaTy7Uhf2VMj2m4v3o44)

### Configure the TiDB Cloud connection

1. Create a [Developer Tier cluster](https://tidbcloud.com/console/create-cluster?tier=dev), which lets you try TiDB Cloud for free for one year. On the **Create a Cluster** (Dev Tier) page, set up your cluster name, password, cloud provider (only AWS is available for the Dev Tier), and region (a nearby region is recommended). Then click **Create** to create your cluster.
    Note: It takes about 5 to 15 minutes to create your TiDB Cloud cluster. You can check the creation status on the [**Active Clusters**](https://tidbcloud.com/console/clusters) page.
2. After you create a cluster, you are directed to the **Active Clusters** page. On the right side of this page, click **Connect**.  
    ![active clusters](https://lh6.googleusercontent.com/z0R8CNDqXhzqW9mJW0W1GpPlivz6J-WFKxgQryNSpGhA8a9fsSbU-V9a81OEXvjvdR6TMJCEmHKcgKtsKRE1hbfUR1ipNNoSOwfDJD_2TyDJv-MSWvFCoTHYl1bT1PORDqcYaCAb5-Km4YDo_suMLwQ)
3. On the pop-up window, click **Add Your Current IP Address** to fill in your current IP address. Then, click **Create Filter** to create a traffic filter.
4. Copy the MySQL string. You’ll use it later to connect with a SQL client.![Connect to TiDB](https://lh3.googleusercontent.com/13d4U1injPLQfz7rIVvHCpJ5ySYMaQnPzjDcu-MKEXO_9ld24YWJcR2nY6dr7jerGq7vAN0bDQCn8hM_jnxqREg-hEbgxSzzCwqYIrtyM9C8DgjBh1NuccVLOkxyChuWSax4gu9qAu6ZCJfapf535ac)Note: For the usage of Gitpod Workspace, you can set an **Allow All traffic filter** here by entering an IP address of `0.0.0.0/0`.  
    ![image](https://lh4.googleusercontent.com/UY7hkUrOUK4OhufXW95nKUNuxPrYI_lwc-BP8iTylwzjXppj0XnsW5gkoH8K0CkRZ1ShO0fMMxwWc92G-6GsChcH3EQz_S8ja7Hlzmbqvjr0wcmy7Mk_mjC4eTk0nzUOl9OFjviV1d-TIgSylWhqOhQ)
5. Return to the [demo page](https://tidb-snowflake-e-commerce-demo.vercel.app/) and configure the TiDB Cloud Host Address and password. In this demo, we create a database `ECOMMERCE`.  
    ![image](https://lh3.googleusercontent.com/h6I-QsSxzLI-hGJstIJHb6vEJAJRvAeCDIscFT29giVgHrQ5CqLK1FT32hHvXwQ7FFy1UH0qM10_DggudMnmisqokh7P6-RXEDpV2eMLkCP6xA_KM_BFulQvNM58fu5wU8GUPFhP_8uZK-LVI-4BG68)
6. Connect to TiDB Cloud. Check your input fields and make sure each field is correct. Click **Continue**.
7. After you successfully connect TiDB Cloud, click **Create** to create a schema. ![image](https://lh6.googleusercontent.com/l1jHH9dNMdBt_4Gr5LqkiF3Xhjf8lVa6uEYO5G-hSSVUZsgg1Q-7PX82SIdmOfbMiD7Cpfp-kJkdb02dNGSr8tD0dDabdsdOwPGsnPVB-QIUJLtMKG07L21Y0Yh3WTeaO0BqPqvMFQRX2QoF-KlxPHg)
8. Import the initial data. Click **Import**. ![image](https://lh5.googleusercontent.com/bwO8Adm3wWIy4OFjoIn4eB5EbVeyuQd67UznbjsZJef3ywNi3Oq9C2FjTLlQol8YKD-05VNaD4PnAAJkwC9dvqzAcAdkYOUpAfUpGpcrV4yD0Hf4jBD_yV-urpLQfx-ez4oRwJzp9qzyzFQ6XDxNke0)

#### Configure the Snowflake connection

To get guidance on how to create your Snowflake cluster, click **Walkthrough** .

![image](https://lh4.googleusercontent.com/FZELG6r9wQprYilPVYQ-4FLY90YHmrMLmFF8KZ6akzmolHgAVnrYaY0U5POKeEp8n7WMR6QPBeZOInLw9Py2K8jGuldXXBSjqklee2vbkyY6ofCT4h0N638WqY-CY1U-kHT6oD1YdLw2x_jWaX0y6Zg)

#### Create the Snowflake Cluster

1. Click [here](http://snowflake.com) to register a Snowflake account and choose your preferred cluster version, cloud provider, and region.
2. After you register a Snowflake account, you will receive an email containing registration details such as your username and password. To activate your account, click the Snowflake link in the email.
3. After your account is activated, you get your account URL for login. ![image](https://lh4.googleusercontent.com/TICifavOnXdn_k_BOJT7ICGa6ZmFsMwNg-Dd3DAojUNEXJhqhZVg-GYnPSY8_-6lNW5gXLlceDLr87FDuNAGDIMCL2BGlqQUNuGhhymJFjrkF3pAUTALl6sZwCWgKXRLnK3rPig8MeHryXJIaQeZbc4)
4. Log in to your account.
5. On the Snowflake manage page, in the left navigation panel, select **Admin > Partner Connect**. In the **Data Integration** area, select **ETleap**.![image](https://lh3.googleusercontent.com/WNs8uuSH3xJeoSv6m6UYfiNDoNjxKPVS3hP3-AvWpBbi9tjpbOpYRxGuMW8_CaDBjcjkpNr5GINraWoFkJN575MlxrnEwDwUOuEWFvFUQbmlU0tksAa2X3Tap5vdvaLqZi221b77W4ukyMSVuMdIVHc)  
    Note: (1) This step is for a further data pipeline from TiDB to Snowflake. (2) The Snowflake trial account and the ETleap account expire after 30 days and 15 days respectively.
6. On the **Connect to Etleap** pop-up, click **Connect**![image](https://lh3.googleusercontent.com/2Icvr-eY5HQ6FV6oTvfgZLnQdfwd6WN2LQ8FObeAVau9VdasOlH7aIombTw7I3IvIVoJvmWZvubqgnmK1SWoiGt4upW6-4MbLiSrsoIwRO-XXHqcaaVWbZOJEh-dGbBB01aHJEk0lKluXY0DblWEubE)
7. Activate your ETleap account. On the confirmation pop-up, click **Activate**. On the ETleap page, click **Create Account** to finish the activation process. ![image](https://lh5.googleusercontent.com/RR2ODWWNYVd5GObwXxcPb9xo4WKRT6FCkYx_uYr0BgNsBw1NuOoU6TiRFmFMaZsXUBtNyrKcnKPhyQIq1235Qe1k2DJnD9gQCrdqNg8xgjMRZE2mncXZdkcg8lVhokT14kkhLAjO7POrS7R7A8zPpZQ) ![image](https://lh4.googleusercontent.com/QUAmf3AM5Vn8-GxTjgMDt41E0U2VpQs5rfQn5CPg0hUm1-lMyMwREcyWxmTVuB85ExgVfFHE9h6wehSpWnGU4byCDUm_IXZ6Sp4gzPfwGotIQrIOb4zijwM8NGW3kzeux2POL2q6aSznpAcnQvuso00)
8. After your account is set up, you can get information as shown below. You will use this data in subsequent steps.

    ```bash
    SNOWSQL_HOST=GQ01328.ap-northeast-1.aws.snowflakecomputing.com
    SNOWSQL_ACCOUNT=GQ01328
    SNOWSQL_WAREHOUSE=PC_ETLEAP_WH
    SNOWSQL_DATABASE=PC_ETLEAP_DB
    SNOWSQL_SCHEMA=PUBLIC
    SNOWSQL_USER=\<admin username\>
    SNOWSQL_ROLE=ACCOUNTADMIN
    SNOWSQL_PWD=\<admin password\>
    ```

#### Connect to Snowflake

1. Return to the [demo page](https://tidb-snowflake-e-commerce-demo.vercel.app/). Under **Connect to Snowflake**, complete the fields with the information you just saved and click **Continue**. The backend checks the connection.![image](https://lh3.googleusercontent.com/ykbD-JoRWUFEqTjzrgerrzvVZmx0fhQN4PrXy7_Zj9-VCa8WjJxeO3tmRurxcM8t_YmSSNwzhauOzTl8-Qycbe-7kh1AO1Z3KTTuQ86ALBw3Mb0kxGiOuUVcllqP7KWRjT3F_5f476Xfx7mms5xj0_w)
2. After Snowflake is connected, click **Create** to create a Snowflake Schema.
3. After you create the schema, click **Continue**. This displays the demo console page.![image](https://lh5.googleusercontent.com/9Dew2s-rG_I-wiTlIXGi1i5OOh3DeNJHF6QWFyPaN3AwsnowOw_eu5j9DCBLc7lcnMKVTUwYmX4zNrHDDNvEPKtYyM-Jc31V0y-DlPb0aw4s7oTEm-vjzS3Oflm_iy2u6jjzfCHU7rs5KqTyBDyjuvI)

### Create a pipeline between TiDB Cloud and Snowflake

After you finish the steps above, you are directed to the console page. On this page, you can perform offline analytics on Snowflake and create a pipeline between TiDB and Snowflake. Then, the Snowflake analytical data can be written back to TiDB.

![Perform offline analysis on Snowflake](https://lh5.googleusercontent.com/UttMzM2H7rwT0Df-2sWX7LBeSvc--Zs_gmX0UnD3AQJzLz-pItNU6ufXf43xS8Jj_ay3wWzd-ZXoAcf0O3I2njaZSbQHytHo3KYjoeI_A96P8S8rZT1ZGCKd7GOSKUyfwsIUAt20OjdnXyQDCaaSlg8)

### ETL data from TiDB to Snowflake

By using Extract, Transform, Load (ETL) tools such as ETleap, you can synchronize data from TiDB to Snowflake. For step-by-step synchronization guidance, click [ETleap guidance](https://github.com/Mini256/tidb-snowflake-e-commerce-demo/blob/main/docs/PIPLINE_ETLEAP.md).

### Perform offline analysis on Snowflake

There are three different calculation jobs for analytics. Select each job and click **Calc User Labels on Snowflake** one by one to run the calculations.

![image](https://lh3.googleusercontent.com/4JBT4L9-bAD_A_hFajro8v-Uj-kc71hNkv95MsIBhC30Ob5XbdMBoY2CkmncMrY0cm2JyWA3asftfARkyFYnmm2UWkQcumWAoRzrieqJWNuK3ZnNS2E9tww0tlYSwr2v1oJ-gPFtDsuq3TK_lDJ5VtA)

### Write back data to TiDB

To write the analytical data from Snowflake to TiDB, go to the **Write Back from Snowflake to TiDB** section and click **Write Back**.

![image](https://lh5.googleusercontent.com/OCi515WbCCo3y6I_aumt3XyDn4ESgWUIHRofBt9FBsPILuEu3641maxZHRNNWPCHV8jAjPYb9Ygv5bCMbEAY1xK7Njz1qfcRFjSKwlIcxFwK9QAM_2BzCIc6w8nNkRMAl8IMHCT9BQjL_bbnyUoCfG4)

After you write the analytical data back to TiDB, you have finished configuring the system.

### Validate the results

After you finish configuring this demo, you can validate the results to see if you have successfully configured this demo by clicking the buttons on the left panel. For example, click **Dashboard** to show a clear view of top sales by the type and price of items. Click **Customers** to check each customer’s user label and their average spending, and click **Recommended Items** to check the recommended product list of a specified customer. Click **Orders** to check all the orders made by each customer.

![Dashboard](https://lh4.googleusercontent.com/AHbFHAqCOoN2sUGiKpbeC_94dONN2SK6ZLN2iZjB-AOO86k_fv7nr5M6DhKd8JG_IPlBg5TjuOBVSFcaQZmRcTZqdC9bLdjSTILCfLWasdsrEPceqKaILEsIM4UUwjpHStaFhV9w0bn0n3l04-fATO8)

![Customers](https://lh5.googleusercontent.com/MULsECh5cPXxkezCiT69dHNC-eA3M7aIRacqkYVatlOIN_e48Kl-WFGfW34KQU_fb-cXbgyh4NI1RSdpqaGfqRtSEz_NgiKFOAVkQ5Nw3JvU5PH38wnmXH8lp8tafaKNUZlc1-CxdKz44TbhJRnt-zM)

![Recommend](https://lh5.googleusercontent.com/S8S9TQh0HuMjUtaN4skAb76Ru5hA7L5sZjnABdwpz-bJx3G_mt1PumA29KbOkFpw8aRNr4tloyF25ojHU_w3VY5AmbMkXZkdc5aswCOFKIszJYmSk8LLh9ZFsvEJVwdEoTFSznLLUrzdhoqRLYnBUfE)

![Orders](https://lh6.googleusercontent.com/g4Cp252t6ZLevzT96fG6ESdqk6ygXFkz7OcpdRVVaeLRE0-DfY0G3rUDLokR9xtBC_bzAN_zisgkmIaduq1ic1DElJxCJ393JGRlCdjnUmG9Wh3X0YPcSuc4re1ymWNIyMUwbf3VXK4Q0fIgXsv5iUs)

Now, you can work with this solution immediately. If you are interested in how we built our solution, you can find the [source code](https://github.com/Mini256/tidb-snowflake-e-commerce-demo) on GitHub.  You can also try [TiDB Cloud](https://tidbcloud.com/free-trial) in just a few minutes.

**Keep reading:**  
[Using Retool and TiDB Cloud to Build a Real-Time Kanban in 30 Minutes](/blog/using-retool-and-tidb-cloud-to-build-a-real-time-kanban-in-30-minutes/)  
[Speeding up a SaaS Platform by 5x in a Day without Changing the Code](/blog/speeding-up-a-saas-platform-by-5x-in-a-day-without-changing-the-code/)  
[Analytics on TiDB Cloud with Databricks](/blog/analytics-on-tidb-cloud-with-databricks/)

### Original Blog Article

[Building an E-Commerce System with TiDB Cloud and Snowflake](https://www.pingcap.com/blog/building-an-e-commerce-system-with-tidb-cloud-and-snowflake/).