🎉 Get access to Y42! Read more →
Docs
Getting started
5. Transform data in Asset List mode

Transform data in Data Catalog mode

Overview

Time to work some model magic! We'll create models to transform the data. First, we'll create a stg_orders model using the Y42 Data catalog mode, which will extract relevant columns from the raw_orders table.

Instructions

Add a model asset in Data catalog mode

  1. Press on +
  2. Select model
  3. Name the model: stg_orders
  4. Insert the following query

_19
with source as (
_19
_19
select * from {{ source('raw_jaffle_shop_data','raw_orders') }}
_19
_19
),
_19
_19
renamed as (
_19
_19
select
_19
"id" as order_id,
_19
"user_id" as customer_id,
_19
"order_date" as order_date,
_19
"status" as status
_19
_19
from source
_19
_19
)
_19
_19
select * from renamed

  1. 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 column.

Creating a model using the Data Catalog mode automatically creates a .sql file with the query above and a .yml file referencing the query.

Commit & push the changes

  1. Click on the Commit & Push button at the top, you should see two tracked files:
    • stg_orders.sql
    • stg_orders.yml
  2. Add a commit name: adding stg_orders in data catalog mode
  3. Click on Commit
  4. Wait for the pre-configured checks

Build the model

  1. Click on the table in the left file selector
  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_orders
  5. Click on Build now
  6. Observe the build job and wait for the job to be Ready

Add metadata to the model and preview data

  1. Click on Sync columns, this will add 3 columns
    • ORDER_DATE
    • CUSTOMER_ID
    • STATUS
    • ORDER_ID
  2. Commit the table metadata
    • Click on the Commit & Push button at the top
    • Name your Commit: adding stg_orders metadata
    • Commit your changes
    • Wait for the pre-configured checks
  3. View the transformed data in the Data tab in the bottom drawer

Up next

Outstanding! You've added the stg_orders model using the Data Catalog mode that contain the transformation logic for our data pipeline. Next, we'll use the Code Editor to create the stg_orders model.

4. Build the source assets6. View materialized assets in your warehouse