dbt + NOAA data pipeline

South Alabama Tornado Watch

A working analytics-engineering portfolio build: public weather sources, dbt models, reproducible tests, and an event explorer that keeps confirmed records separate from preliminary reports.

Published pipeline dataLast successful refresh: Jul 11, 2026, 4:28 PM CDT

Why weather

A local interest with a real data-modeling problem

I live in Dothan, Alabama, where severe weather awareness is a practical part of the year. Tornadoes are the weather events I follow most closely. They also make an unusually useful analytics-engineering case study because an operational warning and a confirmed tornado are related, but they are not the same fact.

The project keeps those source semantics explicit: confirmed NOAA event records and preliminary Local Storm Reports are separate facts, not labels added after the analysis is finished.

Technical implementation

A public, inspectable data product

01 Python ingests NCEI Storm Events records and preliminary IEM Local Storm Reports into a DuckDB raw schema.
02 dbt builds typed source models, geography and intensity dimensions, and confirmed-event, preliminary-report, and current-event facts.
03 A scheduled artifact exports versioned event data, project metadata, and dbt docs. This page reads them through a cached same-origin API, not from the browser.

dbt project explorer

See the data product behind the event explorer

This is a versioned view of the project files, dbt graph, and test results from the exact pipeline run that produced the weather artifact.

Repository dbt docs

dbt models

declared sources

57/57

tests passed

cd0cd17

pinned commit

source: 2seed: 1staging: 2intermediate: 2marts: 5exposure: 2

models/marts/tornado_events/fct_tornado_events.sql

sql · 167 lines

View on GitHub

1WITH confirmed_cutoff AS (2    SELECT MAX(occurred_at_utc) AS max_confirmed_occurred_at_utc3    FROM {{ ref('int_ncei__tornado_events_enriched') }}4)5, confirmed AS (6    SELECT7        event.event_id8        , event.occurred_at9        , event.occurred_at_utc10        , event.occurred_at_utc_offset11        , event.state12        , event.county13        , event.begin_location14        , event.end_location15        , intensity.rating_code16        , intensity.scale_system17        , intensity.rating_value18        , intensity.intensity_class19        , intensity.wind_estimate_low_mph20        , intensity.wind_estimate_high_mph21        , intensity.wind_estimate_note22        , event.path_length_miles23        , event.path_width_yards24        , event.begin_latitude25        , event.begin_longitude26        , event.end_latitude27        , event.end_longitude28        , event.injuries29        , event.fatalities30        , event.property_damage_usd31        , event.crop_damage_usd32        , event.narrative33        , event.source_url34        , event.is_alabama35        , event.is_dixie_cohort36        , event.is_tornado_cohort37        , 'confirmed' AS record_status38        , 'ncei_storm_events' AS source_system39        , FALSE AS is_surveyed_track40        , CAST(NULL AS varchar) AS source_attribution41        , CAST(NULL AS varchar) AS wfo42        , 'ncei_storm_events:' || event.event_id AS event_key43    FROM {{ ref('int_ncei__tornado_events_enriched') }} AS event44    LEFT JOIN {{ ref('dim_tornado_intensities') }} AS intensity45        ON {{ normalized_rating_code('event.rating_code') }} = intensity.rating_code46)47, preliminary AS (48    SELECT49        event.event_id50        , event.occurred_at51        , event.occurred_at_utc52        , event.occurred_at_utc_offset53        , event.state54        , event.county55        , event.begin_location56        , event.end_location57        , event.rating_code58        , event.path_length_miles59        , event.path_width_yards60        , event.begin_latitude61        , event.begin_longitude62        , event.end_latitude63        , event.end_longitude64        , event.injuries65        , event.fatalities66        , event.property_damage_usd67        , event.crop_damage_usd68        , event.narrative69        , event.source_url70        , event.is_alabama71        , event.is_dixie_cohort72        , event.is_tornado_cohort73        , event.source_attribution74        , event.wfo75        , 'preliminary' AS record_status76        , 'iem_lsr' AS source_system77        , FALSE AS is_surveyed_track78        , CAST(NULL AS varchar) AS scale_system79        , CAST(NULL AS integer) AS rating_value80        , CAST('Preliminary report' AS varchar) AS intensity_class81        , CAST(NULL AS integer) AS wind_estimate_low_mph82        , CAST(NULL AS integer) AS wind_estimate_high_mph83        , CAST('Preliminary Local Storm Reports do not include a damage-based F or EF wind estimate.' AS varchar)84            AS wind_estimate_note85        , 'iem_lsr:' || event.event_id AS event_key86    FROM {{ ref('int_iem__tornado_reports_conformed') }} AS event87    CROSS JOIN confirmed_cutoff88    WHERE event.occurred_at_utc > confirmed_cutoff.max_confirmed_occurred_at_utc89)90SELECT91    event_key92    , event_id93    , occurred_at94    , occurred_at_utc95    , occurred_at_utc_offset96    , state97    , county98    , begin_location99    , end_location100    , rating_code101    , scale_system102    , rating_value103    , intensity_class104    , wind_estimate_low_mph105    , wind_estimate_high_mph106    , wind_estimate_note107    , path_length_miles108    , path_width_yards109    , begin_latitude110    , begin_longitude111    , end_latitude112    , end_longitude113    , injuries114    , fatalities115    , property_damage_usd116    , crop_damage_usd117    , narrative118    , source_url119    , is_alabama120    , is_dixie_cohort121    , is_tornado_cohort122    , source_attribution123    , wfo124    , record_status125    , source_system126    , is_surveyed_track127FROM confirmed128UNION ALL129SELECT130    event_key131    , event_id132    , occurred_at133    , occurred_at_utc134    , occurred_at_utc_offset135    , state136    , county137    , begin_location138    , end_location139    , rating_code140    , scale_system141    , rating_value142    , intensity_class143    , wind_estimate_low_mph144    , wind_estimate_high_mph145    , wind_estimate_note146    , path_length_miles147    , path_width_yards148    , begin_latitude149    , begin_longitude150    , end_latitude151    , end_longitude152    , injuries153    , fatalities154    , property_damage_usd155    , crop_damage_usd156    , narrative157    , source_url158    , is_alabama159    , is_dixie_cohort160    , is_tornado_cohort161    , source_attribution162    , wfo163    , record_status164    , source_system165    , is_surveyed_track166FROM preliminary167

fct_tornado_events

One row per confirmed NCEI event or eligible preliminary IEM point report admitted to the current portfolio dataset.

Layer: marts
Relation: prod_marts.fct_tornado_events
Build: Passed
Columns: 36
Materialization: table
Contract: Enforced
Owner: Chase Burkhalter
Maturity: high

Attached tests

assert_annual_counts_reconcile · Passedassert_event_domains · Passedassert_preliminary_after_confirmed_cutoff · Passedassert_preliminary_fields_are_unset · Passednot_null_fct_tornado_events_event_key · Passedunique_fct_tornado_events_event_key · Passednot_null_fct_tornado_events_event_id · Passednot_null_fct_tornado_events_occurred_at · Passednot_null_fct_tornado_events_occurred_at_utc · Passednot_null_fct_tornado_events_occurred_at_utc_offset · Passednot_null_fct_tornado_events_state · Passednon_negative_fct_tornado_events_injuries · Passednon_negative_fct_tornado_events_fatalities · Passedaccepted_values_fct_tornado_events_record_status__confirmed__preliminary · Passedaccepted_values_fct_tornado_events_source_system__ncei_storm_events__iem_lsr · Passed

Model grain

fct_tornado_events is the contracted canonical fact. It combines confirmed NCEI events with preliminary IEM point reports after the latest confirmed cutoff while keeping status and source explicit.

Rich event context

The explorer preserves the original F or EF rating, rating-based wind estimate, endpoint coordinates, path length and width, reported impacts, and source narrative where the source provides them.

Data quality

dbt validates keys, rating values, dimensions, record status, and non-negative impact measures before an artifact can publish.

Event-level mart

Tornado Event Explorer

Select a source-backed confirmed event or preliminary point report to inspect rating details where available, reported impacts, narrative, and coordinate context.

From yearThrough yearMonthAnalysis regionMinimum rating

Loading records

Date	Location	Rating / wind estimate	Path	Impact	Open detail

Methodology and safety note

NOAA Storm Events records are confirmed events. Iowa State Mesonet Local Storm Reports are preliminary point reports after the latest NCEI cutoff. “Dixie Alley” and “Tornado Alley” are project-defined state cohorts for comparison, not official boundaries. F and EF wind ranges are damage-based estimates, not instrument measurements. Endpoint connections are not surveyed tracks. This is a portfolio data demonstration, not a life-safety product.

A local interest with a real data-modeling problem

The project keeps those source semantics explicit: confirmed NOAA event records and preliminary Local Storm Reports are separate facts, not labels added after the analysis is finished.

A public, inspectable data product

01 Python ingests NCEI Storm Events records and preliminary IEM Local Storm Reports into a DuckDB raw schema.

02 dbt builds typed source models, geography and intensity dimensions, and confirmed-event, preliminary-report, and current-event facts.

03 A scheduled artifact exports versioned event data, project metadata, and dbt docs. This page reads them through a cached same-origin API, not from the browser.

1WITH confirmed_cutoff AS (2 SELECT MAX(occurred_at_utc) AS max_confirmed_occurred_at_utc3 FROM {{ ref('int_ncei__tornado_events_enriched') }}4)5, confirmed AS (6 SELECT7 event.event_id8 , event.occurred_at9 , event.occurred_at_utc10 , event.occurred_at_utc_offset11 , event.state12 , event.county13 , event.begin_location14 , event.end_location15 , intensity.rating_code16 , intensity.scale_system17 , intensity.rating_value18 , intensity.intensity_class19 , intensity.wind_estimate_low_mph20 , intensity.wind_estimate_high_mph21 , intensity.wind_estimate_note22 , event.path_length_miles23 , event.path_width_yards24 , event.begin_latitude25 , event.begin_longitude26 , event.end_latitude27 , event.end_longitude28 , event.injuries29 , event.fatalities30 , event.property_damage_usd31 , event.crop_damage_usd32 , event.narrative33 , event.source_url34 , event.is_alabama35 , event.is_dixie_cohort36 , event.is_tornado_cohort37 , 'confirmed' AS record_status38 , 'ncei_storm_events' AS source_system39 , FALSE AS is_surveyed_track40 , CAST(NULL AS varchar) AS source_attribution41 , CAST(NULL AS varchar) AS wfo42 , 'ncei_storm_events:' || event.event_id AS event_key43 FROM {{ ref('int_ncei__tornado_events_enriched') }} AS event44 LEFT JOIN {{ ref('dim_tornado_intensities') }} AS intensity45 ON {{ normalized_rating_code('event.rating_code') }} = intensity.rating_code46)47, preliminary AS (48 SELECT49 event.event_id50 , event.occurred_at51 , event.occurred_at_utc52 , event.occurred_at_utc_offset53 , event.state54 , event.county55 , event.begin_location56 , event.end_location57 , event.rating_code58 , event.path_length_miles59 , event.path_width_yards60 , event.begin_latitude61 , event.begin_longitude62 , event.end_latitude63 , event.end_longitude64 , event.injuries65 , event.fatalities66 , event.property_damage_usd67 , event.crop_damage_usd68 , event.narrative69 , event.source_url70 , event.is_alabama71 , event.is_dixie_cohort72 , event.is_tornado_cohort73 , event.source_attribution74 , event.wfo75 , 'preliminary' AS record_status76 , 'iem_lsr' AS source_system77 , FALSE AS is_surveyed_track78 , CAST(NULL AS varchar) AS scale_system79 , CAST(NULL AS integer) AS rating_value80 , CAST('Preliminary report' AS varchar) AS intensity_class81 , CAST(NULL AS integer) AS wind_estimate_low_mph82 , CAST(NULL AS integer) AS wind_estimate_high_mph83 , CAST('Preliminary Local Storm Reports do not include a damage-based F or EF wind estimate.' AS varchar)84 AS wind_estimate_note85 , 'iem_lsr:' || event.event_id AS event_key86 FROM {{ ref('int_iem__tornado_reports_conformed') }} AS event87 CROSS JOIN confirmed_cutoff88 WHERE event.occurred_at_utc > confirmed_cutoff.max_confirmed_occurred_at_utc89)90SELECT91 event_key92 , event_id93 , occurred_at94 , occurred_at_utc95 , occurred_at_utc_offset96 , state97 , county98 , begin_location99 , end_location100 , rating_code101 , scale_system102 , rating_value103 , intensity_class104 , wind_estimate_low_mph105 , wind_estimate_high_mph106 , wind_estimate_note107 , path_length_miles108 , path_width_yards109 , begin_latitude110 , begin_longitude111 , end_latitude112 , end_longitude113 , injuries114 , fatalities115 , property_damage_usd116 , crop_damage_usd117 , narrative118 , source_url119 , is_alabama120 , is_dixie_cohort121 , is_tornado_cohort122 , source_attribution123 , wfo124 , record_status125 , source_system126 , is_surveyed_track127FROM confirmed128UNION ALL129SELECT130 event_key131 , event_id132 , occurred_at133 , occurred_at_utc134 , occurred_at_utc_offset135 , state136 , county137 , begin_location138 , end_location139 , rating_code140 , scale_system141 , rating_value142 , intensity_class143 , wind_estimate_low_mph144 , wind_estimate_high_mph145 , wind_estimate_note146 , path_length_miles147 , path_width_yards148 , begin_latitude149 , begin_longitude150 , end_latitude151 , end_longitude152 , injuries153 , fatalities154 , property_damage_usd155 , crop_damage_usd156 , narrative157 , source_url158 , is_alabama159 , is_dixie_cohort160 , is_tornado_cohort161 , source_attribution162 , wfo163 , record_status164 , source_system165 , is_surveyed_track166FROM preliminary167

Tornado Event Explorer

Select a source-backed confirmed event or preliminary point report to inspect rating details where available, reported impacts, narrative, and coordinate context.

From yearThrough yearMonthAnalysis regionMinimum rating

Loading records

Date	Location	Rating / wind estimate	Path	Impact	Open detail

Methodology and safety note