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 Feeds

Select Connect to a Data Feed 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 Feed 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!