Create a Mesh Data Product

Here, we'll work with the Taxi Domain and Weather Domain to create a data mesh ready for analysis. If you haven't done so already, create a Taxi Domain and create a Weather Domain.

Step 1: Import Data Shares

Select Connect to a Data Share from the tool panel. Select MESH_DOMAIN_TAXI_DATA and select subscribe.

Repeat these steps for MESH_DOMAIN_WEATHER_DATA.

Step 2: Filter and Join Weather and Taxi Data

Next, we'll work with the data from the weather domain and prepare it for joining with the taxi domain.

Create a UNIQUE WEATHER BY DAY Transform

Create a new Transform Connector from the MESH_DOMAIN_WEATHER_DATA Data Share by left-selecting the vertical ellipsis and selecting Create new Transform.

Name the transform "UNIQUE_WEATHER_BY_DAY" and paste the following code into the query editor.

UNIQUE_WEATHER_BY_DAY

SELECT 
    WEATHER_DATE,
    DATATYPE,
    STATION,
    WEATHER,
    PRECIPITATION,
    STATION_NAME
     from (
        select *, row_number() over (
            partition by WEATHER_DATE
            order by STATION_NAME desc
        ) as row_num
        from {{MESH_DOMAIN_WEATHER_DATA}}
    ) as ordered_weather
WHERE ordered_weather.row_num = 1

SELECT 
    WEATHER_DATE,
    DATATYPE,
    STATION,
    WEATHER,
    PRECIPITATION,
    STATION_NAME
     from (
        select *, row_number() over (
            partition by WEATHER_DATE
            order by STATION_NAME desc
        ) as row_num
        from {{MESH_DOMAIN_WEATHER_DATA}}
    ) as ordered_weather
WHERE ordered_weather.row_num = 1

SELECT 
    WEATHER_DATE,
    DATATYPE,
    STATION,
    WEATHER,
    PRECIPITATION,
    STATION_NAME
     from (
        select *, row_number() over (
            partition by WEATHER_DATE
            order by STATION_NAME desc
        ) as row_num
        from {{MESH_DOMAIN_WEATHER_DATA}}
    ) as ordered_weather
WHERE ordered_weather.row_num = 1

Create a Weather and Rides Transform

Create a new Transform Connector from the UNIQUE_WEATHER_BY_DAY Transform by left-selecting the vertical ellipsis and selecting Create new Transform.

Name the transform "WEATHER_AND_RIDES" and paste the following code into the query editor.

WEATHER_AND_RIDES

SELECT 
T.*, 
W.WEATHER
FROM {{MESH_DOMAIN_TAXI_DATA}} T
LEFT JOIN {{UNIQUE_WEATHER_BY_DAY}} W ON W.WEATHER_DATE = T.RIDE_DATE

SELECT 
T.*, 
W.WEATHER
FROM {{MESH_DOMAIN_TAXI_DATA}} T
LEFT JOIN {{UNIQUE_WEATHER_BY_DAY}} W ON W.WEATHER_DATE = T.RIDE_DATE

SELECT 
T.*, 
W.WEATHER
FROM {{MESH_DOMAIN_TAXI_DATA}} T
LEFT JOIN {{UNIQUE_WEATHER_BY_DAY}} W ON W.WEATHER_DATE = T.RIDE_DATE

Step 3: Create a Final Data Product

The final three data products are created using Transforms.

Create a Bad Weather Rides Transform

Create a new Transform Connector from the WEATHER_AND_RIDES Transform by left-selecting the vertical ellipsis and selecting Create new Transform.

Name the transform "BAD_WEATHER_RIDES" and paste the following code into the query editor:

BAD_WEATHER_RIDES

SELECT * FROM {{Weather_and_Rides}} AS w
WHERE w.WEATHER NOT IN('Not Rainy')

SELECT * FROM {{Weather_and_Rides}} AS w
WHERE w.WEATHER NOT IN('Not Rainy')

SELECT * FROM {{Weather_and_Rides}} AS w
WHERE w.WEATHER NOT IN('Not Rainy')

Create an All Rides with Weather Transform

Create a new Transform Connector from the Weather and Rides Transform by left-selecting the vertical ellipsis and selecting Create new Transform.

Name the transform "ALL_RIDES_WITH_WEATHER" and paste the following code into the query editor.

ALL_RIDES_WITH_WEATHER

SELECT * FROM {{Weather_and_Rides}} AS w

SELECT * FROM {{Weather_and_Rides}} AS w

SELECT * FROM {{Weather_and_Rides}} AS w

Create a Good Weather Rides Transform

Create a new Transform Connector from the Weather and Rides Transform by left-selecting the vertical ellipsis and selecting Create new Transform.

Name the transform "Good Weather Rides" and paste the following code into the query editor.

Good Weather Rides

SELECT * FROM {{Weather_and_Rides}} AS w
WHERE w.WEATHER = 'Not Rainy'

SELECT * FROM {{Weather_and_Rides}} AS w
WHERE w.WEATHER = 'Not Rainy'

SELECT * FROM {{Weather_and_Rides}} AS w
WHERE w.WEATHER = 'Not Rainy'

Your completed DAG should look like this:

Next Steps

Congrats on creating your first data product! For the remainder of the lab, we'll work through using the data you brought with you. Or, if you didn't bring your own data, we've created challenges for you to complete. As always, don't hesitate to ask when you have questions!