Transform data in Data Editor mode

Add a model asset in Code editor mode

Switch to the Code editor mode in the top left corner by clicking on the Data catalog dropdown

Create a SQL and YML file

1. Open the models folder

2. Create new file: stg_payments.sql

3. Insert the following SQL query:

   
   

   _21

   with source as (

   _21

   _21

   select * from {{ source('raw_jaffle_shop_data', 'raw_payments') }}

   _21

   _21

   ),

   _21

   _21

   renamed as (

   _21

   _21

   select

   _21

   "id" as payment_id,

   _21

   "order_id" as order_id,

   _21

   "payment_method" as payment_method,

   _21

   _21

   -- `amount` is currently stored in cents, so we convert it to dollars

   _21

   TO_NUMBER(REPLACE("amount", ',', '')) / 100 as amount

   _21

   _21

   from source

   _21

   _21

   )

   _21

   _21

   select * from renamed

   
   

4. Create new file: stg_payments.yml

5. Insert the following YML:

   
   

   _17

   version: 2

   _17

   _17

   models:

   _17

   - name: stg_payments

   _17

   columns:

   _17

   - name: payment_id

   _17

   description: ""

   _17

   data_type: STRING

   _17

   tests: []

   _17

   - name: payment_method

   _17

   description: ""

   _17

   data_type: STRING

   _17

   tests: []

   _17

   - name: order_id

   _17

   data_type: STRING

   _17

   - name: amount

   _17

   data_type: NUMERIC

   
   

   Make sure you save both files.

6. Preview the model by clicking on Query Data on the right side of the bottom drawer or by using the hotkey CTRL + RETURN while having your cursor in the SQL code. Verify that you can see the renamed order_id and converted amount column.

Commit the SQL and YML file

1. Click on the Commit & Push button at the top, you should again see two files:
   • stg_payments.sql
   • stg_payments.yml
2. Name your Commit: adding stg_payments in code editor mode
3. Commit your changes
4. Wait for the pre-configured checks

Build the table

1. Click on the stg_payments.sql file in the left file explorer
2. Open the bottom drawer
3. Navigate to Build
4. You should see a pre-defined build command, if you don’t see a build command, enter y42 build -s stg_payments
5. Click on Build now
6. Observe the build job and wait for the job to be Ready

Go back to the Data catalog mode, you should automatically see your newly created stg_payments asset’s query and columns metadata defined in the stg_payments.yml

View materialized tables in your warehouse

1. Navigate to your warehouse
2. Navigate to the Y42 database / project
3. Open the y42__{space_name}__dev schema / dataset Verify that you can see your newly materialized dev table: stg_payments

Merge the changes into main

1. Go back to Y42
2. Click on the three dots ⋯ next to the refresh button in the top bar
3. Click on Merge branch
4. In the opened pop up, you should see dev being merged into main and the following message: Branch can be automatically merged (1 commit behind)
5. Click on Merge You will automatically be redirected to your main branch