Databricks Quickstart

Before You Begin

• Go through our Databricks Connection Guide to get your account set up with Coalesce.
• Add the AccuWeather data source to your catalog.
  • forecast_daily_calendar_metric
• Create a Project and Workspace.
  • You can skip creating a version control account.

Need Databricks Access?

Reach out to our sales team.

Add a Data Source

In your Workspace, you are on the Build Interface. This is where you'll build your data pipelines.

1. Make sure you are on Nodes.

2. Click on the "+" sign > Add Sources.

   

3. Add Sources to SQL Pipeline will open. Find the forecast_daily_calendar_metric table. Double-clicking the source will give you a preview of the data.

   

4. Then click Add Source.

5. The source Node is added to your DAG. Now you can start building.

Adding Data From Different Sources

We support multiple data types from different sources. Take a look at the External Data Package.

Add a Stage Node

1. Right-click on the source Node > Add Node > Stage.

   

2. It will open the added stage Node in the Node Editor.

3. Next to the name, click the pencil and name it stg_weather_metrics. Databricks Node names are formatted as lowercase.

4. Then click Create and Run.

   

5. You’ve just populated your first table in Databricks. Click on the Results tab. You can see each stage complete and the SQL executed.

What Create and Run Do

Create renders and executes all SQL stages of the create template defined in the Node Type definition. Run renders and executes all SQL stages of the run template defined in the Node Type definition.

Add a Transformation

1. If the Node isn’t open, you can open the stg_weather_metrics by double clicking the Node or right-click and select Edit.

2. Sort the column by name, by clicking on the Column Name table header.

3. Scroll to the column named temperature.

4. Double-click in the Transform column next to temperature.

5. Add ROUND({{SRC}}, 1). This uses standard SQL and Helper Tokens. SRC evaluates to the fully qualified name and location. After adding, it should update to the correct SRC. For example, ROUND(`forecast_hourly_metric`.`temperature`, 1).

   

6. Click Validate Select. Validate Select lets you validate your transformations and joins.

7. Scroll down in the Results panel to see the SQL applied to temperature.

   

8. Click Create and Run.

9. In the Data panel, scroll over to temperature, and see that the value has been rounded to one decimal place.

Add a Dimension Node

You’ll create a Dimension Node that will track how weather metrics change over time and each location. First we'll add some transformations to our stage Node to prep the data.

1. Make sure you're in the stg_weather_metrics Node.

2. You need to delete any columns not being used. Un-sort the columns by clicking the Column Name until the arrows disappear. Click and drag the following columns so they are at the top:

   1. city_name
   2. country_code
   3. datetime_valid_local
   4. temperature
   5. humidity_relative
   6. wind_speed
   7. precipitation_type_desc
   8. cloud_cover_perc_total

3. Click on the first column to be deleted, hold Shift or Command then select the last column to be deleted. Right-click and Delete the columns.

   A demo showing selecting multiple columns for deletion.

4. Rename the column datetime_valid_local to measurement_time. Double click on measurement_time to rename it.

5. Rename cloud_cover_perc_total to cloud_cover_percent.

6. Add the following transformations to standardize the data:

   1. temperature - ROUND({{SRC}}, 1) to round to one decimal place, if not already added.
   2. humidity_relative - ROUND({{SRC}}, 1) to round to one decimal place.
   3. precipitation_type_desc - COALESCE({{SRC}}, 'None') to replace null values.

7. Then double click in the Data Type for cloud_cover_percent and type INTEGER.

   

8. Click Validate Select to preview the SQL that will be run.

9. Then click Create and Run.

10. Return to your DAG and create a Dimension Node off the stg_weather_metrics. Name it dim_weather_metrics.

11. Under Options, select the following as Business Keys by selecting them in the box, then click the arrow to move them over.

    1. city_name
    2. country_code
    3. measurement_time

12. In Change Tracking, select the following:

    1. temperature
    2. precipitation_type_desc
    3. wind_speed
    4. cloud_cover_percent

13. Then click Create and Run.

Create a Fact Node

You'll create a Fact Node to track historical data. You'll also use the Join tab.

1. Create a Stage Node called stg_weather_facts off the Dimension Node dim_weather_metrics.

2. You'll need to create a Join. The Join tab can be used to accept any valid SQL. We are joining the source data, forecast_hourly_metric to dim_weather_metrics. The conditions are the city_name, country_code, and measurement_time must match. This will join all results even if there are no matches. They will be null values.

   FROM {{ ref('ACCUWEATHER', 'forecast_hourly_metric') }} `forecast_hourly_metric`
   LEFT JOIN {{ ref('<YOUR-STORAGE-LOCATION-NAME>', 'dim_weather_metrics') }} `dim_weather_metrics`
       ON `forecast_hourly_metric`.`city_name` = `dim_weather_metrics`.`city_name`
       AND `forecast_hourly_metric`.`country_code` = `dim_weather_metrics`.`country_code`
       AND `forecast_hourly_metric`.`datetime_valid_local` = `dim_weather_metrics`.`measurement_time`
   

3. Create and Run the Node.

4. Click on the browser tab to see that the stg_weather_facts has a reference to the source and dimension Node.

   

5. Create a Fact Node from the stg_weather_facts.

6. Name it fct_weather_facts.

7. Set the Business Keys as:

   1. city_name
   2. country_code
   3. measurement_time

8. Create and Run the Node.

The final DAG

Wrap Up

Here's what you did:

You Joined the source Node to the stg_weather_facts to get raw measurements like humidity and precipitation_type_desc. This is the data you are tracking.

Stg_weather_facts will have the dim_weather_metrics_key. This makes sure each row has the correct historical weather data. Another way of saying this is: "On this day, time, and place, the weather description was X." Another example, "This forecast measurement ties to dim_weather_metrics_key at 124 to 124 in the fact Node and that record said it was cloudy, 68°F, and humid at that time."

Databricks Query

You can also query this in Databricks.

Avg Humidity by cloud cover type

SELECT
  d.cloud_cover_percent,
  COUNT(*) AS measurement_count,
  ROUND(AVG(f.humidity_relative), 1) AS avg_humidity
FROM fct_weather_facts f
JOIN dim_weather_metrics d
  ON f.dim_weather_metrics_key = d.dim_weather_metrics_key
GROUP BY d.cloud_cover_percent
ORDER BY d.cloud_cover_percent;

Daily temperature trends by city

SELECT
  f.measurement_time::DATE AS date,
  f.city_name,
  ROUND(AVG(d.temperature), 1) AS avg_temp
FROM fct_weather_facts f
JOIN dim_weather_metrics d
  ON f.dim_weather_metrics_key = d.dim_weather_metrics_key
GROUP BY date, f.city_name
ORDER BY date, f.city_name;

Daily temperature trends by city