Create SQL transformations on CustomerPurchaseActivities

We'll be doing a series of SQL transformations. Do not apply the SQL transformations until the very end.

1. Similar to before keep all the preselected settings. Under Configure ingest click on the Construct SQL rollup and/or transformation button:

Construct SQL transfomrations

2. First SQL transformation: change amount_due to an int type

We’re going to change amount_due from a string to an int so we can do different types of aggregations.

Action Item: Delete all the code SQL transformation editor and apply the code snippet below. You'll notice we've applied Lines 1-4:

SQL code

3. Second SQL transformation: change event_time to _event_time:

We're going to apply our second SQL transformation so we can query the field faster.

Action Item: Delete all the code SQL transformation editor and apply the code snippet below. You'll notice we've applied Line 3:

SQL code

4. Third SQL transformation: convert fields

Action Item: Delete all the code SQL transformation editor and apply the code snippet below. You'll notice we've applied Line 4 and 5:

SQL code

5. Add the remaining fields:

Action Item: Delete all the code SQL transformation editor and apply the code snippet below. You'll notice we've applied Lines 5-7:

SQL code

SQL Code

6. Go ahead and Apply the SQL transformations at the bottom of the SQL transformation page.

7. Keep the collection default settings as is. You can learn more about VI sizing and document retention by clicking on the links.

8. Create the collection at the bottom of the collection page.

NOTE: You can find us on the Rockset Community if you have questions or comments about the workshop.