Docs
8. Transform data in Code Editor mode

Transform data in Data Editor mode

Overview

Let's use the Code Editor mode and use VSCode in the browser to add a stg_payments model on our dev branch.

Instructions

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

Up next

Staging layer done! Let's take a step back and take a look at what you've built using the Lineage mode.