Claude Code in action with dbt

Exit code 1
14:02:39  Running with dbt=1.11.6
14:02:40  Registered adapter: duckdb=1.10.1
14:02:40  [WARNING][MissingArgumentsPropertyInGenericTestDeprecation]: Deprecated
functionality
Found top-level arguments to test `relationships` defined on 'stg_measures' in
package 'flood_monitoring' (models/staging/_stg_models.yml). Arguments to
generic tests should be nested under the `arguments` property.
14:02:40  Found 25 data tests, 6 models, 1 snapshot, 3 sources, 480 macros
14:02:40
14:02:40  Concurrency: 1 threads (target='dev')
14:02:40
14:02:40  1 of 32 START sql view model main_staging.stg_measures ......................... [RUN]
14:02:40  1 of 32 OK created sql view model main_staging.stg_measures .................... [OK in 0.03s]
14:02:40  2 of 32 START sql view model main_staging.stg_readings ......................... [RUN]
14:02:40  2 of 32 OK created sql view model main_staging.stg_readings .................... [OK in 0.01s]
14:02:40  3 of 32 START sql view model main_staging.stg_stations ......................... [RUN]
14:02:40  3 of 32 OK created sql view model main_staging.stg_stations .................... [OK in 0.01s]
14:02:40  4 of 32 START test not_null_stg_measures_measure_id ............................ [RUN]
14:02:40  4 of 32 PASS not_null_stg_measures_measure_id .................................. [PASS in 0.01s]
14:02:40  5 of 32 START test not_null_stg_measures_station_id ............................ [RUN]
14:02:40  5 of 32 PASS not_null_stg_measures_station_id .................................. [PASS in 0.01s]
14:02:40  6 of 32 START test unique_stg_measures_measure_id .............................. [RUN]
14:02:40  6 of 32 PASS unique_stg_measures_measure_id .................................... [PASS in 0.01s]
14:02:40  7 of 32 START test not_null_stg_readings_measure_id ............................ [RUN]
14:02:40  7 of 32 PASS not_null_stg_readings_measure_id .................................. [PASS in 0.09s]
14:02:40  8 of 32 START test not_null_stg_readings_reading_timestamp ..................... [RUN]
14:02:40  8 of 32 PASS not_null_stg_readings_reading_timestamp ........................... [PASS in 0.05s]
14:02:40  9 of 32 START test not_null_stg_readings_reading_value ......................... [RUN]
14:02:40  9 of 32 PASS not_null_stg_readings_reading_value ............................... [PASS in 0.01s]
14:02:40  10 of 32 START test accepted_values_stg_stations_status__Active__Closed__Suspended  [RUN]
14:02:40  10 of 32 FAIL 5 accepted_values_stg_stations_status__Active__Closed__Suspended . [FAIL 5 in 0.01s]
14:02:40  11 of 32 START test not_null_stg_stations_latitude ............................. [RUN]
14:02:40  11 of 32 FAIL 631 not_null_stg_stations_latitude ............................... [FAIL 631 in 0.01s]
14:02:40  12 of 32 START test not_null_stg_stations_longitude ............................ [RUN]
14:02:40  12 of 32 FAIL 631 not_null_stg_stations_longitude .............................. [FAIL 631 in 0.01s]
14:02:40  13 of 32 START test not_null_stg_stations_station_id ........................... [RUN]
14:02:40  13 of 32 PASS not_null_stg_stations_station_id ................................. [PASS in 0.01s]
14:02:40  14 of 32 START test not_null_stg_stations_station_name ......................... [RUN]
14:02:40  14 of 32 PASS not_null_stg_stations_station_name ............................... [PASS in 0.01s]
14:02:40  15 of 32 START test relationships_stg_measures_station_id__station_id__ref_stg_stations_  [RUN]
14:02:40  15 of 32 FAIL 90 relationships_stg_measures_station_id__station_id__ref_stg_stations_  [FAIL 90 in 0.01s]
14:02:40  16 of 32 START test unique_stg_stations_station_id ............................. [RUN]
14:02:40  16 of 32 PASS unique_stg_stations_station_id ................................... [PASS in 0.01s]
14:02:40  17 of 32 START sql table model main_marts.dim_measures ......................... [RUN]
14:02:40  17 of 32 OK created sql table model main_marts.dim_measures .................... [OK in 0.03s]
14:02:40  18 of 32 START sql incremental model main_marts.fct_readings ................... [RUN]
14:02:41  18 of 32 OK created sql incremental model main_marts.fct_readings .............. [OK in 0.74s]
14:02:41  19 of 32 SKIP relation main_marts.dim_stations ................................. [SKIP]
14:02:41  20 of 32 SKIP relation snapshots.snp_stations .................................. [SKIP]
14:02:41  21 of 32 START test not_null_dim_measures_measure_id ........................... [RUN]
14:02:41  21 of 32 PASS not_null_dim_measures_measure_id .................................

... [9192 characters truncated] ...

f 32 SKIP relation main_marts.dim_stations ................................. [SKIP]
14:02:41  20 of 32 SKIP relation snapshots.snp_stations .................................. [SKIP]
14:02:41  21 of 32 START test not_null_dim_measures_measure_id ........................... [RUN]
14:02:41  21 of 32 PASS not_null_dim_measures_measure_id ................................. [PASS in 0.01s]
14:02:41  22 of 32 START test not_null_dim_measures_station_id ........................... [RUN]
14:02:41  22 of 32 PASS not_null_dim_measures_station_id ................................. [PASS in 0.01s]
14:02:41  23 of 32 START test unique_dim_measures_measure_id ............................. [RUN]
14:02:41  23 of 32 PASS unique_dim_measures_measure_id ................................... [PASS in 0.01s]
14:02:41  24 of 32 START test assert_readings_values_in_reasonable_range ................. [RUN]
14:02:41  24 of 32 FAIL 1880 assert_readings_values_in_reasonable_range .................. [FAIL 1880 in 0.01s]
14:02:41  25 of 32 START test not_null_fct_readings_measure_id ........................... [RUN]
14:02:41  25 of 32 PASS not_null_fct_readings_measure_id ................................. [PASS in 0.01s]
14:02:41  26 of 32 START test not_null_fct_readings_reading_timestamp .................... [RUN]
14:02:41  26 of 32 PASS not_null_fct_readings_reading_timestamp .......................... [PASS in 0.01s]
14:02:41  27 of 32 START test not_null_fct_readings_reading_value ........................ [RUN]
14:02:41  27 of 32 PASS not_null_fct_readings_reading_value .............................. [PASS in 0.01s]
14:02:41  28 of 32 START test relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [RUN]
14:02:41  28 of 32 PASS relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [PASS in 0.01s]
14:02:41  29 of 32 SKIP test not_null_dim_stations_station_id ............................ [SKIP]
14:02:41  30 of 32 SKIP test not_null_dim_stations_station_name .......................... [SKIP]
14:02:41  31 of 32 SKIP test relationships_dim_measures_station_id__station_id__ref_dim_stations_  [SKIP]
14:02:41  32 of 32 SKIP test unique_dim_stations_station_id .............................. [SKIP]
14:02:41
14:02:41  Finished running 1 incremental model, 1 snapshot, 2 table models, 25 data tests, 3 view models in 0 hours 0 minutes and 1.24 seconds (1.24s).
14:02:41
14:02:41  Completed with 5 errors, 0 partial successes, and 0 warnings:
14:02:41
14:02:41  Failure in test accepted_values_stg_stations_status__Active__Closed__Suspended (models/staging/_stg_models.yml)
14:02:41    Got 5 results, configured to fail if != 0
14:02:41
14:02:41    compiled code at target/compiled/flood_monitoring/models/staging/_stg_models.yml/accepted_values_stg_stations_status__Active__Closed__Suspended.sql
14:02:41
14:02:41  Failure in test not_null_stg_stations_latitude (models/staging/_stg_models.yml)
14:02:41    Got 631 results, configured to fail if != 0
14:02:41
14:02:41    compiled code at target/compiled/flood_monitoring/models/staging/_stg_models.yml/not_null_stg_stations_latitude.sql
14:02:41
14:02:41  Failure in test not_null_stg_stations_longitude (models/staging/_stg_models.yml)
14:02:41    Got 631 results, configured to fail if != 0
14:02:41
14:02:41    compiled code at target/compiled/flood_monitoring/models/staging/_stg_models.yml/not_null_stg_stations_longitude.sql
14:02:41
14:02:41  Failure in test relationships_stg_measures_station_id__station_id__ref_stg_stations_ (models/staging/_stg_models.yml)
14:02:41    Got 90 results, configured to fail if != 0
14:02:41
14:02:41    compiled code at target/compiled/flood_monitoring/models/staging/_stg_models.yml/relationships_stg_measures_c9b3c5abdfef2456c04e8e7879bbaf46.sql
14:02:41
14:02:41  Failure in test assert_readings_values_in_reasonable_range (tests/assert_readings_values_in_reasonable_range.sql)
14:02:41    Got 1880 results, configured to fail if != 0
14:02:41
14:02:41    compiled code at target/compiled/flood_monitoring/tests/assert_readings_values_in_reasonable_range.sql
14:02:41
14:02:41  Done. PASS=21 WARN=0 ERROR=5 SKIP=6 NO-OP=0 TOTAL=32
14:02:41  [WARNING][DeprecationsSummary]: Deprecated functionality
Summary of encountered deprecations:
- MissingArgumentsPropertyInGenericTestDeprecation: 3 occurrences
To see all deprecation instances instead of just the first occurrence of each,
run command again with the `--show-all-deprecations` flag. You may also need to
run with `--no-partial-parse` as some deprecations are only encountered during
parsing.

python3 -c "
import duckdb
con = duckdb.connect('/workspace/flood_monitoring/flood_monitoring.duckdb')

# Check what status values exist
result = con.execute('''
    SELECT status, count(*)
    FROM main_staging.stg_stations
    GROUP BY status
    ORDER BY count(*) DESC
''').fetchall()
print('=== STATUS VALUES ===')
for r in result:
    print(f'  {r[0]}: {r[1]}')

# Check null lat/long pattern
result = con.execute('''
    SELECT status, count(*)
    FROM main_staging.stg_stations
    WHERE latitude IS NULL
    GROUP BY status
''').fetchall()
print()
print('=== NULL LAT/LONG BY STATUS ===')
for r in result:
    print(f'  {r[0]}: {r[1]}')

# Check orphan measures
result = con.execute('''
    SELECT m.station_id, count(*) as cnt
    FROM main_staging.stg_measures m
    LEFT JOIN main_staging.stg_stations s ON m.station_id = s.station_id
    WHERE s.station_id IS NULL
    GROUP BY m.station_id
    ORDER BY cnt DESC
    LIMIT 10
''').fetchall()
print()
print('=== ORPHAN MEASURES (top 10) ===')
for r in result:
    print(f'  station_id={r[0]}: {r[1]} measures')

# Check readings range violations
result = con.execute('''
    SELECT parameter_type,
           min(reading_value) as min_val,
           max(reading_value) as max_val,
           count(*) as cnt
    FROM main_marts.fct_readings
    WHERE (parameter_type = 'level' AND (reading_value < -10 OR reading_value > 200))
       OR (parameter_type = 'flow' AND (reading_value < -1 OR reading_value > 10000))
       OR (parameter_type = 'temperature' AND (reading_value < -50 OR reading_value > 60))
    GROUP BY parameter_type
''').fetchall()
print()
print('=== RANGE VIOLATIONS ===')
for r in result:
    print(f'  {r[0]}: min={r[1]}, max={r[2]}, count={r[3]}')

con.close()
" 2>&1

=== STATUS VALUES ===
  None: 2901
  "Active": 2508
  "Suspended": 29
  "Closed": 18
  "ukcmf": 5
  ["Active","http://environment.data.gov.uk/flood-monitoring/def/core/statusSuspended"]: 2

=== NULL LAT/LONG BY STATUS ===
  None: 631

=== ORPHAN MEASURES (top 10) ===
  station_id=2167: 2 measures
  station_id=2269TH: 2 measures
  station_id=0730TH: 2 measures
  station_id=2229_w1TH: 2 measures
  station_id=E15657: 2 measures
  station_id=E9250: 2 measures
  station_id=2229_w2TH: 2 measures
  station_id=1090_w2TH: 2 measures
  station_id=3073TH: 2 measures
  station_id=2041: 1 measures

=== RANGE VIOLATIONS ===
  level: min=-99.0, max=nan, count=1501
  flow: min=-32.768, max=nan, count=379

python3 -c "
import duckdb
con = duckdb.connect('/workspace/flood_monitoring/flood_monitoring.duckdb')
# Check what status looks like raw
result = con.execute('''
    SELECT typeof(status), status
    FROM raw_stations
    LIMIT 5
''').fetchall()
print('=== RAW STATUS ===')
for r in result:
    print(f'  type={r[0]}, val={r[1]}')

# Check a null-status station
result = con.execute('''
    SELECT notation, label, status, lat, long
    FROM raw_stations
    WHERE status IS NULL
    LIMIT 3
''').fetchall()
print()
print('=== NULL STATUS STATIONS ===')
for r in result:
    print(f'  {r}')
con.close()
" 2>&1

=== RAW STATUS ===
  type=JSON, val="http://environment.data.gov.uk/flood-monitoring/def/core/statusActive"
  type=JSON, val="http://environment.data.gov.uk/flood-monitoring/def/core/statusActive"
  type=JSON, val="http://environment.data.gov.uk/flood-monitoring/def/core/statusActive"
  type=JSON, val="http://environment.data.gov.uk/flood-monitoring/def/core/statusActive"
  type=JSON, val="http://environment.data.gov.uk/flood-monitoring/def/core/statusActive"

=== NULL STATUS STATIONS ===
  ('E70024', '"Lowestoft"', None, '52.47311', '1.75011')
  ('E72624', '"Avonmouth Portbury"', None, '51.5', '-2.72847')
  ('47140', '"CANWORTHY WATER BRIDGE"', None, '50.697595', '-4.509797')

python3 -c "
import duckdb
con = duckdb.connect('/workspace/flood_monitoring/flood_monitoring.duckdb')
# Check ukcmf status
result = con.execute('''
    SELECT notation, status
    FROM raw_stations
    WHERE cast(status as varchar) LIKE '%ukcmf%'
    LIMIT 5
''').fetchall()
print('=== UKCMF STATUS ===')
for r in result:
    print(f'  {r}')

# Check array status
result = con.execute('''
    SELECT notation, status, typeof(status)
    FROM raw_stations
    WHERE json_array_length(status) > 0
    LIMIT 5
''').fetchall()
print()
print('=== ARRAY STATUS ===')
for r in result:
    print(f'  {r}')
con.close()
" 2>&1

=== UKCMF STATUS ===
  ('067027_TG_127', '"http://environment.data.gov.uk/flood-monitoring/def/core/statusukcmf"')
  ('067021', '"http://environment.data.gov.uk/flood-monitoring/def/core/statusukcmf"')
  ('067624_TG_126', '"http://environment.data.gov.uk/flood-monitoring/def/core/statusukcmf"')
  ('067015_TG_132', '"http://environment.data.gov.uk/flood-monitoring/def/core/statusukcmf"')
  ('067005_TG_116', '"http://environment.data.gov.uk/flood-monitoring/def/core/statusukcmf"')

=== ARRAY STATUS ===
  ('0018', '["http://environment.data.gov.uk/flood-monitoring/def/core/statusActive","http://environment.data.gov.uk/flood-monitoring/def/core/statusSuspended"]', 'JSON')
  ('E85123', '["http://environment.data.gov.uk/flood-monitoring/def/core/statusActive","http://environment.data.gov.uk/flood-monitoring/def/core/statusSuspended"]', 'JSON')

with source as (

    select * from {{ source('flood_monitoring_api', 'raw_stations') }}

),

renamed as (

    select
        -- Primary key
        notation                                        as station_id,

        -- Descriptive attributes
        label                                           as station_name,
        town,
        "riverName"                                     as river_name,
        "catchmentName"                                 as catchment_name,

        -- Geographic coordinates (may be null for some stations)
        try_cast(lat as double)                         as latitude,
        try_cast(long as double)                        as longitude,
        try_cast(easting as integer)                    as easting,
        try_cast(northing as integer)                   as northing,

        -- Station metadata
        "RLOIid"                                        as rloi_id,
        "stationReference"                              as station_reference,
        "wiskiID"                                       as wiski_id,
        "dateOpened"                                    as date_opened,

        -- Status: extract the short status from the URI
        -- Handles: JSON strings, arrays (take first element), nulls, and
        -- the "ukcmf" variant
        case
            when status is null then 'Unknown'
            else regexp_replace(
                -- For arrays, take the first element; for strings, use as-is
                case
                    when json_type(status) = 'ARRAY'
                        then cast(status->>0 as varchar)
                    else cast(status as varchar)
                end,
                '.*status',
                ''
            )
        end                                             as status,

        -- Audit
        loaded_at

    from source

)

select * from renamed

with source as (

    select * from {{ source('flood_monitoring_api', 'raw_readings') }}

),

cleaned as (

    select
        -- Timestamp
        cast("dateTime" as timestamp)                   as reading_timestamp,

        -- Foreign key: extract measure notation from the URI
        regexp_replace(
            measure,
            'http://environment\.data\.gov\.uk/flood-monitoring/id/measures/',
            ''
        )                                               as measure_id,

        -- Value cleaning: handle pipe-delimited values (e.g. "0.770|0.688")
        -- by taking the first value, then cast to double
        try_cast(
            case
                when value like '%|%' then split_part(value, '|', 1)
                else value
            end as double
        )                                               as reading_value,

        -- Flag rows that had pipe-delimited values
        value like '%|%'                                as has_pipe_delimited_value,

        -- Keep original value for debugging
        value                                           as raw_value,

        -- Audit
        loaded_at

    from source

),

filtered as (

    -- Exclude rows where value is null or NaN after cleaning
    select * from cleaned
    where reading_value is not null
      and isnan(reading_value) = false

)

select * from filtered

     1→with source as (
     2→
     3→    select * from {{ source('flood_monitoring_api', 'raw_readings') }}
     4→
     5→),
     6→
     7→cleaned as (
     8→
     9→    select
    10→        -- Timestamp
    11→        cast("dateTime" as timestamp)                   as reading_timestamp,
    12→
    13→        -- Foreign key: extract measure notation from the URI
    14→        regexp_replace(
    15→            measure,
    16→            'http://environment\.data\.gov\.uk/flood-monitoring/id/measures/',
    17→            ''
    18→        )                                               as measure_id,
    19→
    20→        -- Value cleaning: handle pipe-delimited values (e.g. "0.770|0.688")
    21→        -- by taking the first value, then cast to double
    22→        try_cast(
    23→            case
    24→                when value like '%|%' then split_part(value, '|', 1)
    25→                else value
    26→            end as double
    27→        )                                               as reading_value,
    28→
    29→        -- Flag rows that had pipe-delimited values
    30→        value like '%|%'                                as has_pipe_delimited_value,
    31→
    32→        -- Flag rows where value couldn't be cast to double
    33→        try_cast(
    34→            case
    35→                when value like '%|%' then split_part(value, '|', 1)
    36→                else value
    37→            end as double
    38→        ) is null
    39→        and value is not null                           as has_invalid_value,
    40→
    41→        -- Keep original value for debugging
    42→        value                                           as raw_value,
    43→
    44→        -- Audit
    45→        loaded_at
    46→
    47→    from source
    48→
    49→),
    50→
    51→filtered as (
    52→
    53→    -- Exclude rows where value is null after cleaning
    54→    select * from cleaned
    55→    where reading_value is not null
    56→
    57→)
    58→
    59→select * from filtered
    60→

        -- Flag rows where value couldn't be cast to double
        try_cast(
            case
                when value like '%|%' then split_part(value, '|', 1)
                else value
            end as double
        ) is null
        and value is not null                           as has_invalid_value,

        -- Keep original value for debugging
        value                                           as raw_value,

        -- Keep original value for debugging
        value                                           as raw_value,

    -- Exclude rows where value is null after cleaning
    select * from cleaned
    where reading_value is not null

    -- Exclude rows where value is null or NaN after cleaning
    select * from cleaned
    where reading_value is not null
      and isnan(reading_value) = false

version: 2

models:
  - name: stg_stations
    description: >
      Cleaned and renamed station metadata from the Environment Agency API.
      Each row represents a physical monitoring station. Note that ~12% of
      stations lack geographic coordinates and some lack a status value.
    columns:
      - name: station_id
        description: Unique station identifier (notation)
        tests:
          - unique
          - not_null

      - name: station_name
        description: Human-readable station name
        tests:
          - not_null

      - name: latitude
        description: WGS84 latitude (null for ~12% of stations)

      - name: longitude
        description: WGS84 longitude (null for ~12% of stations)

      - name: status
        description: >
          Station operational status. Most common values are Active, Closed,
          Suspended, and ukcmf. Stations without a status URI are set to Unknown.
        tests:
          - not_null
          - accepted_values:
              arguments:
                values: ['Active', 'Closed', 'Suspended', 'ukcmf', 'Unknown']

  - name: stg_measures
    description: >
      Cleaned and renamed measure definitions from the Environment Agency API.
      Each measure describes a specific type of reading at a station
      (e.g., water level downstream, flow rate).
    columns:
      - name: measure_id
        description: Unique measure identifier (notation)
        tests:
          - unique
          - not_null

      - name: station_id
        description: >
          Foreign key to the station this measure belongs to.
          A small number of measures reference stations not in the stations endpoint
          (likely decommissioned stations).
        tests:
          - not_null
          - relationships:
              arguments:
                to: ref('stg_stations')
                field: station_id
              config:
                severity: warn

      - name: parameter_type
        description: Short parameter type (level, flow, temperature, wind)

      - name: unit_name
        description: Unit of measurement (mAOD, mASD, m3/s, etc.)

  - name: stg_readings
    description: >
      Cleaned readings from the Environment Agency API and CSV archives.
      Known data quality issues handled:
      - Pipe-delimited values (e.g. "0.770|0.688") split to take first value
      - Non-numeric values filtered out via try_cast
      - NaN values filtered out
    columns:
      - name: reading_timestamp
        description: When the reading was taken
        tests:
          - not_null

      - name: measure_id
        description: Foreign key to the measure this reading belongs to
        tests:
          - not_null

      - name: reading_value
        description: The numeric measurement value after cleaning
        tests:
          - not_null

-- Warn on reading values outside a generous physical range.
-- Known issues in the data:
-- - Sentinel values like -99 used for error/missing readings
-- - Occasional telemetry glitches producing extreme outliers
-- - Some groundwater levels can be legitimately high (mAOD)
-- This test uses warn severity as outliers are expected in real telemetry.

{{ config(severity='warn') }}

select
    measure_id,
    reading_timestamp,
    reading_value,
    parameter_type
from {{ ref('fct_readings') }}
where
    (parameter_type = 'level' and (reading_value < -50 or reading_value > 500))
    or (parameter_type = 'flow' and (reading_value < -50 or reading_value > 50000))
    or (parameter_type = 'temperature' and (reading_value < -50 or reading_value > 60))

     1→version: 2
     2→
     3→models:
     4→  - name: dim_stations
     5→    description: >
     6→      Dimension table for monitoring stations. Enriched with a count and
     7→      list of available measure types per station.
     8→    columns:
     9→      - name: station_id
    10→        description: Unique station identifier
    11→        tests:
    12→          - unique
    13→          - not_null
    14→
    15→      - name: station_name
    16→        description: Human-readable station name
    17→        tests:
    18→          - not_null
    19→
    20→      - name: status
    21→        description: "Operational status: Active, Closed, or Suspended"
    22→
    23→      - name: measure_count
    24→        description: Number of distinct measures at this station
    25→
    26→  - name: dim_measures
    27→    description: >
    28→      Dimension table for measures. Each measure defines what is being
    29→      measured at a station (parameter, qualifier, units, period).
    30→    columns:
    31→      - name: measure_id
    32→        description: Unique measure identifier
    33→        tests:
    34→          - unique
    35→          - not_null
    36→
    37→      - name: station_id
    38→        description: Foreign key to dim_stations
    39→        tests:
    40→          - not_null
    41→          - relationships:
    42→              to: ref('dim_stations')
    43→              field: station_id
    44→
    45→      - name: parameter_type
    46→        description: "What is measured: level, flow, temperature, wind"
    47→
    48→      - name: unit_name
    49→        description: Unit of measurement
    50→
    51→  - name: fct_readings
    52→    description: >
    53→      Fact table of hydrometric readings. Each row is a single timestamped
    54→      measurement. Incrementally loaded. Enriched with measure context for
    55→      query convenience.
    56→    columns:
    57→      - name: measure_id
    58→        description: Foreign key to dim_measures
    59→        tests:
    60→          - not_null
    61→          - relationships:
    62→              to: ref('dim_measures')
    63→              field: measure_id
    64→
    65→      - name: reading_timestamp
    66→        description: When the reading was taken
    67→        tests:
    68→          - not_null
    69→
    70→      - name: reading_value
    71→        description: The numeric measurement value
    72→        tests:
    73→          - not_null
    74→
    75→      - name: station_id
    76→        description: Foreign key to dim_stations (denormalized from measure)
    77→
    78→      - name: parameter_type
    79→        description: "What was measured: level, flow, temperature, wind"
    80→
    81→      - name: reading_date
    82→        description: Date portion of the reading timestamp
    83→

version: 2

models:
  - name: dim_stations
    description: >
      Dimension table for monitoring stations. Enriched with a count and
      list of available measure types per station.
    columns:
      - name: station_id
        description: Unique station identifier
        tests:
          - unique
          - not_null

      - name: station_name
        description: Human-readable station name
        tests:
          - not_null

      - name: status
        description: "Operational status: Active, Closed, Suspended, ukcmf, or Unknown"

      - name: measure_count
        description: Number of distinct measures at this station

  - name: dim_measures
    description: >
      Dimension table for measures. Each measure defines what is being
      measured at a station (parameter, qualifier, units, period).
    columns:
      - name: measure_id
        description: Unique measure identifier
        tests:
          - unique
          - not_null

      - name: station_id
        description: Foreign key to dim_stations
        tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_stations')
                field: station_id
              config:
                severity: warn

      - name: parameter_type
        description: "What is measured: level, flow, temperature, wind"

      - name: unit_name
        description: Unit of measurement

  - name: fct_readings
    description: >
      Fact table of hydrometric readings. Each row is a single timestamped
      measurement. Incrementally loaded. Enriched with measure context for
      query convenience.
    columns:
      - name: measure_id
        description: Foreign key to dim_measures
        tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_measures')
                field: measure_id
              config:
                severity: warn

      - name: reading_timestamp
        description: When the reading was taken
        tests:
          - not_null

      - name: reading_value
        description: The numeric measurement value
        tests:
          - not_null

      - name: station_id
        description: Foreign key to dim_stations (denormalized from measure)

      - name: parameter_type
        description: "What was measured: level, flow, temperature, wind"

      - name: reading_date
        description: Date portion of the reading timestamp

Exit code 1
14:05:32  Running with dbt=1.11.6
14:05:32  Registered adapter: duckdb=1.10.1
14:05:32  Found 1 snapshot, 6 models, 24 data tests, 3 sources, 480 macros
14:05:32
14:05:32  Concurrency: 1 threads (target='dev')
14:05:32
14:05:32  1 of 31 START sql view model main_staging.stg_measures ......................... [RUN]
14:05:32  1 of 31 OK created sql view model main_staging.stg_measures .................... [OK in 0.03s]
14:05:32  2 of 31 START sql view model main_staging.stg_readings ......................... [RUN]
14:05:32  2 of 31 OK created sql view model main_staging.stg_readings .................... [OK in 0.01s]
14:05:32  3 of 31 START sql view model main_staging.stg_stations ......................... [RUN]
14:05:32  3 of 31 OK created sql view model main_staging.stg_stations .................... [OK in 0.03s]
14:05:32  4 of 31 START test not_null_stg_measures_measure_id ............................ [RUN]
14:05:32  4 of 31 PASS not_null_stg_measures_measure_id .................................. [PASS in 0.02s]
14:05:32  5 of 31 START test not_null_stg_measures_station_id ............................ [RUN]
14:05:32  5 of 31 PASS not_null_stg_measures_station_id .................................. [PASS in 0.01s]
14:05:32  6 of 31 START test unique_stg_measures_measure_id .............................. [RUN]
14:05:32  6 of 31 PASS unique_stg_measures_measure_id .................................... [PASS in 0.01s]
14:05:32  7 of 31 START test not_null_stg_readings_measure_id ............................ [RUN]
14:05:32  7 of 31 PASS not_null_stg_readings_measure_id .................................. [PASS in 0.08s]
14:05:32  8 of 31 START test not_null_stg_readings_reading_timestamp ..................... [RUN]
14:05:32  8 of 31 PASS not_null_stg_readings_reading_timestamp ........................... [PASS in 0.02s]
14:05:32  9 of 31 START test not_null_stg_readings_reading_value ......................... [RUN]
14:05:32  9 of 31 PASS not_null_stg_readings_reading_value ............................... [PASS in 0.01s]
14:05:32  10 of 31 START test accepted_values_stg_stations_status__Active__Closed__Suspended__ukcmf__Unknown  [RUN]
14:05:32  10 of 31 FAIL 4 accepted_values_stg_stations_status__Active__Closed__Suspended__ukcmf__Unknown  [FAIL 4 in 0.01s]
14:05:32  11 of 31 START test not_null_stg_stations_station_id ........................... [RUN]
14:05:32  11 of 31 PASS not_null_stg_stations_station_id ................................. [PASS in 0.01s]
14:05:32  12 of 31 START test not_null_stg_stations_station_name ......................... [RUN]
14:05:32  12 of 31 PASS not_null_stg_stations_station_name ............................... [PASS in 0.01s]
14:05:32  13 of 31 START test not_null_stg_stations_status ............................... [RUN]
14:05:32  13 of 31 PASS not_null_stg_stations_status ..................................... [PASS in 0.01s]
14:05:32  14 of 31 START test relationships_stg_measures_station_id__station_id__ref_stg_stations_  [RUN]
14:05:32  14 of 31 WARN 90 relationships_stg_measures_station_id__station_id__ref_stg_stations_  [WARN 90 in 0.01s]
14:05:32  15 of 31 START test unique_stg_stations_station_id ............................. [RUN]
14:05:32  15 of 31 PASS unique_stg_stations_station_id ................................... [PASS in 0.01s]
14:05:32  16 of 31 START sql table model main_marts.dim_measures ......................... [RUN]
14:05:32  16 of 31 OK created sql table model main_marts.dim_measures .................... [OK in 0.03s]
14:05:32  17 of 31 START sql incremental model main_marts.fct_readings ................... [RUN]
14:05:33  17 of 31 OK created sql incremental model main_marts.fct_readings .............. [OK in 0.78s]
14:05:33  18 of 31 SKIP relation main_marts.dim_stations ................................. [SKIP]
14:05:33  19 of 31 SKIP relation snapshots.snp_stations .................................. [SKIP]
14:05:33  20 of 31 START test not_null_dim_measures_measure_id ........................... [RUN]
14:05:33  20 of 31 PASS not_null_dim_measures_measure_id ................................. [PASS in 0.01s]
14:05:33  21 of 31 START test not_null_dim_measures_station_id ........................... [RUN]
14:05:33  21 of 31 PASS not_null_dim_measures_station_id ................................. [PASS in 0.04s]
14:05:33  22 of 31 START test unique_dim_measures_measure_id ............................. [RUN]
14:05:33  22 of 31 PASS unique_dim_measures_measure_id ................................... [PASS in 0.01s]
14:05:33  23 of 31 START test assert_readings_value

... [5998 characters truncated] ...

............................... [RUN]
14:05:32  13 of 31 PASS not_null_stg_stations_status ..................................... [PASS in 0.01s]
14:05:32  14 of 31 START test relationships_stg_measures_station_id__station_id__ref_stg_stations_  [RUN]
14:05:32  14 of 31 WARN 90 relationships_stg_measures_station_id__station_id__ref_stg_stations_  [WARN 90 in 0.01s]
14:05:32  15 of 31 START test unique_stg_stations_station_id ............................. [RUN]
14:05:32  15 of 31 PASS unique_stg_stations_station_id ................................... [PASS in 0.01s]
14:05:32  16 of 31 START sql table model main_marts.dim_measures ......................... [RUN]
14:05:32  16 of 31 OK created sql table model main_marts.dim_measures .................... [OK in 0.03s]
14:05:32  17 of 31 START sql incremental model main_marts.fct_readings ................... [RUN]
14:05:33  17 of 31 OK created sql incremental model main_marts.fct_readings .............. [OK in 0.78s]
14:05:33  18 of 31 SKIP relation main_marts.dim_stations ................................. [SKIP]
14:05:33  19 of 31 SKIP relation snapshots.snp_stations .................................. [SKIP]
14:05:33  20 of 31 START test not_null_dim_measures_measure_id ........................... [RUN]
14:05:33  20 of 31 PASS not_null_dim_measures_measure_id ................................. [PASS in 0.01s]
14:05:33  21 of 31 START test not_null_dim_measures_station_id ........................... [RUN]
14:05:33  21 of 31 PASS not_null_dim_measures_station_id ................................. [PASS in 0.04s]
14:05:33  22 of 31 START test unique_dim_measures_measure_id ............................. [RUN]
14:05:33  22 of 31 PASS unique_dim_measures_measure_id ................................... [PASS in 0.01s]
14:05:33  23 of 31 START test assert_readings_values_in_reasonable_range ................. [RUN]
14:05:33  23 of 31 WARN 567 assert_readings_values_in_reasonable_range ................... [WARN 567 in 0.01s]
14:05:33  24 of 31 START test not_null_fct_readings_measure_id ........................... [RUN]
14:05:33  24 of 31 PASS not_null_fct_readings_measure_id ................................. [PASS in 0.01s]
14:05:33  25 of 31 START test not_null_fct_readings_reading_timestamp .................... [RUN]
14:05:33  25 of 31 PASS not_null_fct_readings_reading_timestamp .......................... [PASS in 0.01s]
14:05:33  26 of 31 START test not_null_fct_readings_reading_value ........................ [RUN]
14:05:33  26 of 31 PASS not_null_fct_readings_reading_value .............................. [PASS in 0.01s]
14:05:33  27 of 31 START test relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [RUN]
14:05:33  27 of 31 PASS relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [PASS in 0.01s]
14:05:33  28 of 31 SKIP test not_null_dim_stations_station_id ............................ [SKIP]
14:05:33  29 of 31 SKIP test not_null_dim_stations_station_name .......................... [SKIP]
14:05:33  30 of 31 SKIP test relationships_dim_measures_station_id__station_id__ref_dim_stations_  [SKIP]
14:05:33  31 of 31 SKIP test unique_dim_stations_station_id .............................. [SKIP]
14:05:33
14:05:33  Finished running 1 incremental model, 1 snapshot, 2 table models, 24 data tests, 3 view models in 0 hours 0 minutes and 1.31 seconds (1.31s).
14:05:33
14:05:33  Completed with 1 error, 0 partial successes, and 2 warnings:
14:05:33
14:05:33  Failure in test accepted_values_stg_stations_status__Active__Closed__Suspended__ukcmf__Unknown (models/staging/_stg_models.yml)
14:05:33    Got 4 results, configured to fail if != 0
14:05:33
14:05:33    compiled code at target/compiled/flood_monitoring/models/staging/_stg_models.yml/accepted_values_stg_stations_5b62dbf00d329c33e1814b9a1c93d855.sql
14:05:33
14:05:33  Warning in test relationships_stg_measures_station_id__station_id__ref_stg_stations_ (models/staging/_stg_models.yml)
14:05:33  Got 90 results, configured to warn if != 0
14:05:33
14:05:33    compiled code at target/compiled/flood_monitoring/models/staging/_stg_models.yml/relationships_stg_measures_c9b3c5abdfef2456c04e8e7879bbaf46.sql
14:05:33
14:05:33  Warning in test assert_readings_values_in_reasonable_range (tests/assert_readings_values_in_reasonable_range.sql)
14:05:33  Got 567 results, configured to warn if != 0
14:05:33
14:05:33    compiled code at target/compiled/flood_monitoring/tests/assert_readings_values_in_reasonable_range.sql
14:05:33
14:05:33  Done. PASS=22 WARN=2 ERROR=1 SKIP=6 NO-OP=0 TOTAL=31

python3 -c "
import duckdb
con = duckdb.connect('/workspace/flood_monitoring/flood_monitoring.duckdb')
result = con.execute('''
    SELECT status, count(*)
    FROM main_staging.stg_stations
    GROUP BY status
    ORDER BY count(*) DESC
''').fetchall()
print('=== CURRENT STATUS VALUES ===')
for r in result:
    print(f'  \"{r[0]}\": {r[1]}')
con.close()
" 2>&1

=== CURRENT STATUS VALUES ===
  "Unknown": 2901
  "Active"": 2508
  "Suspended"": 29
  "Closed"": 18
  "ukcmf"": 5
  "Active": 2

     1→with source as (
     2→
     3→    select * from {{ source('flood_monitoring_api', 'raw_stations') }}
     4→
     5→),
     6→
     7→renamed as (
     8→
     9→    select
    10→        -- Primary key
    11→        notation                                        as station_id,
    12→
    13→        -- Descriptive attributes
    14→        label                                           as station_name,
    15→        town,
    16→        "riverName"                                     as river_name,
    17→        "catchmentName"                                 as catchment_name,
    18→
    19→        -- Geographic coordinates (may be null for some stations)
    20→        try_cast(lat as double)                         as latitude,
    21→        try_cast(long as double)                        as longitude,
    22→        try_cast(easting as integer)                    as easting,
    23→        try_cast(northing as integer)                   as northing,
    24→
    25→        -- Station metadata
    26→        "RLOIid"                                        as rloi_id,
    27→        "stationReference"                              as station_reference,
    28→        "wiskiID"                                       as wiski_id,
    29→        "dateOpened"                                    as date_opened,
    30→
    31→        -- Status: extract the short status from the URI
    32→        -- Handles: JSON strings, arrays (take first element), nulls, and
    33→        -- the "ukcmf" variant
    34→        case
    35→            when status is null then 'Unknown'
    36→            else regexp_replace(
    37→                -- For arrays, take the first element; for strings, use as-is
    38→                case
    39→                    when json_type(status) = 'ARRAY'
    40→                        then cast(status->>0 as varchar)
    41→                    else cast(status as varchar)
    42→                end,
    43→                '.*status',
    44→                ''
    45→            )
    46→        end                                             as status,
    47→
    48→        -- Audit
    49→        loaded_at
    50→
    51→    from source
    52→
    53→)
    54→
    55→select * from renamed
    56→

        -- Status: extract the short status from the URI
        -- Handles: JSON strings, arrays (take first element), nulls, and
        -- the "ukcmf" variant
        case
            when status is null then 'Unknown'
            else regexp_replace(
                -- For arrays, take the first element; for strings, use as-is
                case
                    when json_type(status) = 'ARRAY'
                        then cast(status->>0 as varchar)
                    else cast(status as varchar)
                end,
                '.*status',
                ''
            )
        end                                             as status,

        -- Status: extract the short status from the URI
        -- Handles: JSON strings, arrays (take first element), nulls, and
        -- the "ukcmf" variant. Use ->> to extract text without JSON quotes.
        case
            when status is null then 'Unknown'
            else regexp_replace(
                case
                    when json_type(status) = 'ARRAY'
                        then status->>0
                    else status->>'$'
                end,
                '.*status',
                ''
            )
        end                                             as status,

14:06:17  Running with dbt=1.11.6
14:06:18  Registered adapter: duckdb=1.10.1
14:06:18  Found 1 snapshot, 6 models, 24 data tests, 3 sources, 480 macros
14:06:18
14:06:18  Concurrency: 1 threads (target='dev')
14:06:18
14:06:18  1 of 31 START sql view model main_staging.stg_measures ......................... [RUN]
14:06:18  1 of 31 OK created sql view model main_staging.stg_measures .................... [OK in 0.04s]
14:06:18  2 of 31 START sql view model main_staging.stg_readings ......................... [RUN]
14:06:18  2 of 31 OK created sql view model main_staging.stg_readings .................... [OK in 0.02s]
14:06:18  3 of 31 START sql view model main_staging.stg_stations ......................... [RUN]
14:06:18  3 of 31 OK created sql view model main_staging.stg_stations .................... [OK in 0.02s]
14:06:18  4 of 31 START test not_null_stg_measures_measure_id ............................ [RUN]
14:06:18  4 of 31 PASS not_null_stg_measures_measure_id .................................. [PASS in 0.01s]
14:06:18  5 of 31 START test not_null_stg_measures_station_id ............................ [RUN]
14:06:18  5 of 31 PASS not_null_stg_measures_station_id .................................. [PASS in 0.01s]
14:06:18  6 of 31 START test unique_stg_measures_measure_id .............................. [RUN]
14:06:18  6 of 31 PASS unique_stg_measures_measure_id .................................... [PASS in 0.01s]
14:06:18  7 of 31 START test not_null_stg_readings_measure_id ............................ [RUN]
14:06:18  7 of 31 PASS not_null_stg_readings_measure_id .................................. [PASS in 0.09s]
14:06:18  8 of 31 START test not_null_stg_readings_reading_timestamp ..................... [RUN]
14:06:18  8 of 31 PASS not_null_stg_readings_reading_timestamp ........................... [PASS in 0.02s]
14:06:18  9 of 31 START test not_null_stg_readings_reading_value ......................... [RUN]
14:06:18  9 of 31 PASS not_null_stg_readings_reading_value ............................... [PASS in 0.01s]
14:06:18  10 of 31 START test accepted_values_stg_stations_status__Active__Closed__Suspended__ukcmf__Unknown  [RUN]
14:06:18  10 of 31 PASS accepted_values_stg_stations_status__Active__Closed__Suspended__ukcmf__Unknown  [PASS in 0.01s]
14:06:18  11 of 31 START test not_null_stg_stations_station_id ........................... [RUN]
14:06:18  11 of 31 PASS not_null_stg_stations_station_id ................................. [PASS in 0.01s]
14:06:18  12 of 31 START test not_null_stg_stations_station_name ......................... [RUN]
14:06:18  12 of 31 PASS not_null_stg_stations_station_name ............................... [PASS in 0.01s]
14:06:18  13 of 31 START test not_null_stg_stations_status ............................... [RUN]
14:06:18  13 of 31 PASS not_null_stg_stations_status ..................................... [PASS in 0.01s]
14:06:18  14 of 31 START test relationships_stg_measures_station_id__station_id__ref_stg_stations_  [RUN]
14:06:18  14 of 31 WARN 90 relationships_stg_measures_station_id__station_id__ref_stg_stations_  [WARN 90 in 0.01s]
14:06:18  15 of 31 START test unique_stg_stations_station_id ............................. [RUN]
14:06:18  15 of 31 PASS unique_stg_stations_station_id ................................... [PASS in 0.01s]
14:06:18  16 of 31 START sql table model main_marts.dim_measures ......................... [RUN]
14:06:18  16 of 31 OK created sql table model main_marts.dim_measures .................... [OK in 0.03s]
14:06:18  17 of 31 START sql incremental model main_marts.fct_readings ................... [RUN]
14:06:19  17 of 31 OK created sql incremental model main_marts.fct_readings .............. [OK in 0.77s]
14:06:19  18 of 31 START sql table model main_marts.dim_stations ......................... [RUN]
14:06:19  18 of 31 OK created sql table model main_marts.dim_stations .................... [OK in 0.03s]
14:06:19  19 of 31 START snapshot snapshots.snp_stations ................................. [RUN]
14:06:19  19 of 31 OK snapshotted snapshots.snp_stations ................................. [OK in 0.04s]
14:06:19  20 of 31 START test not_null_dim_measures_measure_id ........................... [RUN]
14:06:19  20 of 31 PASS not_null_dim_measures_measure_id ................................. [PASS in 0.01s]
14:06:19  21 of 31 START test not_null_dim_measures_station_id ........................... [RUN]
14:06:19  21 of 31 PASS not_null_dim_measures_station_id ................................. [PASS in 0.01s]
14:06:19  22 of 31 START test unique_dim_measures_measure_id ............................. [RUN]
14:06:19  22 of 31 PASS unique_dim_measures_measure_id ................................... [PASS in 0.01s]
14:06:19  23 of 31 START test assert_readings_values_in_reasonable_range ................. [RUN]
14:06:19  23 of 31 WARN 567 assert_readings_values_in_reasonable_range ................... [WARN 567 in 0.01s]
14:06:19  24 of 31 START test not_null_fct_readings_measure_id ........................... [RUN]
14:06:19  24 of 31 PASS not_null_fct_readings_measure_id ................................. [PASS in 0.01s]
14:06:19  25 of 31 START test not_null_fct_readings_reading_timestamp .................... [RUN]
14:06:19  25 of 31 PASS not_null_fct_readings_reading_timestamp .......................... [PASS in 0.01s]
14:06:19  26 of 31 START test not_null_fct_readings_reading_value ........................ [RUN]
14:06:19  26 of 31 PASS not_null_fct_readings_reading_value .............................. [PASS in 0.01s]
14:06:19  27 of 31 START test relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [RUN]
14:06:19  27 of 31 PASS relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [PASS in 0.01s]
14:06:19  28 of 31 START test not_null_dim_stations_station_id ........................... [RUN]
14:06:19  28 of 31 PASS not_null_dim_stations_station_id ................................. [PASS in 0.01s]
14:06:19  29 of 31 START test not_null_dim_stations_station_name ......................... [RUN]
14:06:19  29 of 31 PASS not_null_dim_stations_station_name ............................... [PASS in 0.01s]
14:06:19  30 of 31 START test relationships_dim_measures_station_id__station_id__ref_dim_stations_  [RUN]
14:06:19  30 of 31 WARN 90 relationships_dim_measures_station_id__station_id__ref_dim_stations_  [WARN 90 in 0.01s]
14:06:19  31 of 31 START test unique_dim_stations_station_id ............................. [RUN]
14:06:19  31 of 31 PASS unique_dim_stations_station_id ................................... [PASS in 0.01s]
14:06:19
14:06:19  Finished running 1 incremental model, 1 snapshot, 2 table models, 24 data tests, 3 view models in 0 hours 0 minutes and 1.37 seconds (1.37s).
14:06:19
14:06:19  Completed with 3 warnings:
14:06:19
14:06:19  Warning in test relationships_stg_measures_station_id__station_id__ref_stg_stations_ (models/staging/_stg_models.yml)
14:06:19  Got 90 results, configured to warn if != 0
14:06:19
14:06:19    compiled code at target/compiled/flood_monitoring/models/staging/_stg_models.yml/relationships_stg_measures_c9b3c5abdfef2456c04e8e7879bbaf46.sql
14:06:19
14:06:19  Warning in test assert_readings_values_in_reasonable_range (tests/assert_readings_values_in_reasonable_range.sql)
14:06:19  Got 567 results, configured to warn if != 0
14:06:19
14:06:19    compiled code at target/compiled/flood_monitoring/tests/assert_readings_values_in_reasonable_range.sql
14:06:19
14:06:19  Warning in test relationships_dim_measures_station_id__station_id__ref_dim_stations_ (models/marts/_marts_models.yml)
14:06:19  Got 90 results, configured to warn if != 0
14:06:19
14:06:19    compiled code at target/compiled/flood_monitoring/models/marts/_marts_models.yml/relationships_dim_measures_ecdc550b700458cc9102b8d4e9f0c20e.sql
14:06:19
14:06:19  Done. PASS=28 WARN=3 ERROR=0 SKIP=0 NO-OP=0 TOTAL=31

python3 -c "
import duckdb
con = duckdb.connect('/workspace/flood_monitoring/flood_monitoring.duckdb')

print('=== TABLE COUNTS ===')
for schema in ['main', 'main_staging', 'main_marts', 'snapshots']:
    tables = con.execute(f\"SELECT table_name FROM information_schema.tables WHERE table_schema = '{schema}'\").fetchall()
    for t in tables:
        cnt = con.execute(f'SELECT count(*) FROM {schema}.\"{t[0]}\"').fetchone()[0]
        print(f'  {schema}.{t[0]}: {cnt:,} rows')

print()
print('=== SAMPLE dim_stations ===')
rows = con.execute('SELECT station_id, station_name, river_name, status, measure_count FROM main_marts.dim_stations LIMIT 5').fetchall()
for r in rows:
    print(f'  {r}')

print()
print('=== SAMPLE fct_readings ===')
rows = con.execute('SELECT measure_id, reading_timestamp, reading_value, parameter_type, station_id FROM main_marts.fct_readings LIMIT 5').fetchall()
for r in rows:
    print(f'  {r}')

print()
print('=== SNAPSHOT snp_stations ===')
rows = con.execute('SELECT station_id, station_name, status, dbt_valid_from, dbt_valid_to FROM snapshots.snp_stations LIMIT 3').fetchall()
for r in rows:
    print(f'  {r}')

con.close()
" 2>&1

=== TABLE COUNTS ===
  main.raw_measures: 6,917 rows
  main.raw_readings: 1,429,383 rows
  main.raw_stations: 5,463 rows
  main_staging.stg_measures: 6,917 rows
  main_staging.stg_readings: 1,429,318 rows
  main_staging.stg_stations: 5,463 rows
  main_marts.dim_measures: 6,917 rows
  main_marts.dim_stations: 5,463 rows
  main_marts.fct_readings: 1,429,318 rows
  main_marts.fct_readings__dbt_backup: 1,429,318 rows
  snapshots.snp_stations: 5,463 rows

=== SAMPLE dim_stations ===
  ('1029TH', '"Bourton Dickler"', 'River Dikler', 'Active', 2)
  ('E2043', '"Surfleet Sluice"', 'River Glen', 'Active', 1)
  ('52119', '"Gaw Bridge"', 'River Parrett', 'Active', 1)
  ('E21136', '"Hemingford"', 'River Great Ouse', 'Active', 1)
  ('2067', '"Swindon"', 'Smestow Brook', 'Active', 1)

=== SAMPLE fct_readings ===
  ('DIFF_TB3_TB1-level-tidal_level-i-15_min-m', datetime.datetime(2025, 3, 1, 10, 4, 10), 0.433, 'level', 'DIFF_TB3_TB1')
  ('50120-level-stage-i-15_min-m', datetime.datetime(2025, 3, 1, 9, 15), 0.546, 'level', '50120')
  ('E72003-level-stage-i-15_min-m', datetime.datetime(2025, 3, 1, 9, 30), 0.116, 'level', 'E72003')
  ('46118-level-stage-i-15_min-m', datetime.datetime(2025, 3, 1, 9, 45), 0.41, 'level', '46118')
  ('E24732-rainfall-tipping_bucket_raingauge-t-15_min-mm', datetime.datetime(2025, 3, 1, 7, 15), 0.0, 'rainfall', 'E24732')

=== SNAPSHOT snp_stations ===
  ('1029TH', '"Bourton Dickler"', 'Active', datetime.datetime(2026, 3, 9, 14, 6, 19, 719023), None)
  ('E2043', '"Surfleet Sluice"', 'Active', datetime.datetime(2026, 3, 9, 14, 6, 19, 719023), None)
  ('52119', '"Gaw Bridge"', 'Active', datetime.datetime(2026, 3, 9, 14, 6, 19, 719023), None)

     1→with source as (
     2→
     3→    select * from {{ source('flood_monitoring_api', 'raw_stations') }}
     4→
     5→),
     6→
     7→renamed as (
     8→
     9→    select
    10→        -- Primary key
    11→        notation                                        as station_id,
    12→
    13→        -- Descriptive attributes
    14→        label                                           as station_name,
    15→        town,
    16→        "riverName"                                     as river_name,
    17→        "catchmentName"                                 as catchment_name,
    18→
    19→        -- Geographic coordinates (may be null for some stations)
    20→        try_cast(lat as double)                         as latitude,
    21→        try_cast(long as double)                        as longitude,
    22→        try_cast(easting as integer)                    as easting,
    23→        try_cast(northing as integer)                   as northing,
    24→
    25→        -- Station metadata
    26→        "RLOIid"                                        as rloi_id,
    27→        "stationReference"                              as station_reference,
    28→        "wiskiID"                                       as wiski_id,
    29→        "dateOpened"                                    as date_opened,
    30→
    31→        -- Status: extract the short status from the URI
    32→        -- Handles: JSON strings, arrays (take first element), nulls, and
    33→        -- the "ukcmf" variant. Use ->> to extract text without JSON quotes.
    34→        case
    35→            when status is null then 'Unknown'
    36→            else regexp_replace(
    37→                case
    38→                    when json_type(status) = 'ARRAY'
    39→                        then status->>0
    40→                    else status->>'$'
    41→                end,
    42→                '.*status',
    43→                ''
    44→            )
    45→        end                                             as status,
    46→
    47→        -- Audit
    48→        loaded_at
    49→
    50→    from source
    51→
    52→)
    53→
    54→select * from renamed
    55→

python3 -c "
import duckdb
con = duckdb.connect('/workspace/flood_monitoring/flood_monitoring.duckdb')
# Check types of label/town/etc
result = con.execute('''
    SELECT typeof(label), typeof(town), typeof(\"riverName\"), typeof(\"catchmentName\"),
           typeof(\"RLOIid\"), typeof(\"stationReference\"), typeof(\"wiskiID\"),
           typeof(notation)
    FROM raw_stations LIMIT 1
''').fetchall()
print('Types:', result[0])

# Check a sample
result = con.execute('''
    SELECT notation, label, label->>'$' as label2, typeof(notation)
    FROM raw_stations LIMIT 3
''').fetchall()
for r in result:
    print(r)
con.close()
" 2>&1

Types: ('JSON', 'VARCHAR', 'VARCHAR', 'JSON', 'JSON', 'VARCHAR', 'VARCHAR', 'VARCHAR')
('1029TH', '"Bourton Dickler"', 'Bourton Dickler', 'VARCHAR')
('E2043', '"Surfleet Sluice"', 'Surfleet Sluice', 'VARCHAR')
('52119', '"Gaw Bridge"', 'Gaw Bridge', 'VARCHAR')

        -- Primary key
        notation                                        as station_id,

        -- Descriptive attributes
        label                                           as station_name,
        town,
        "riverName"                                     as river_name,
        "catchmentName"                                 as catchment_name,

        -- Geographic coordinates (may be null for some stations)
        try_cast(lat as double)                         as latitude,
        try_cast(long as double)                        as longitude,
        try_cast(easting as integer)                    as easting,
        try_cast(northing as integer)                   as northing,

        -- Station metadata
        "RLOIid"                                        as rloi_id,
        "stationReference"                              as station_reference,
        "wiskiID"                                       as wiski_id,

        -- Primary key
        notation                                        as station_id,

        -- Descriptive attributes (some fields are JSON-typed, use ->> to extract text)
        label->>'$'                                     as station_name,
        town,
        "riverName"                                     as river_name,
        "catchmentName"->>'$'                           as catchment_name,

        -- Geographic coordinates (may be null for some stations)
        try_cast(lat as double)                         as latitude,
        try_cast(long as double)                        as longitude,
        try_cast(easting as integer)                    as easting,
        try_cast(northing as integer)                   as northing,

        -- Station metadata
        "RLOIid"->>'$'                                  as rloi_id,
        "stationReference"                              as station_reference,
        "wiskiID"                                       as wiski_id,

python3 -c "
import duckdb
con = duckdb.connect('/workspace/flood_monitoring/flood_monitoring.duckdb')
result = con.execute('''
    SELECT typeof(notation), typeof(label), typeof(station), typeof(parameter),
           typeof(\"parameterName\"), typeof(qualifier), typeof(\"unitName\"),
           typeof(\"valueType\"), typeof(period), typeof(\"datumType\"),
           typeof(\"stationReference\")
    FROM raw_measures LIMIT 1
''').fetchall()
print('Measure types:')
cols = ['notation','label','station','parameter','parameterName','qualifier','unitName','valueType','period','datumType','stationReference']
for c, t in zip(cols, result[0]):
    print(f'  {c}: {t}')
con.close()
" 2>&1

Measure types:
  notation: VARCHAR
  label: VARCHAR
  station: VARCHAR
  parameter: VARCHAR
  parameterName: VARCHAR
  qualifier: VARCHAR
  unitName: VARCHAR
  valueType: VARCHAR
  period: BIGINT
  datumType: VARCHAR
  stationReference: VARCHAR

Exit code 1
14:07:51  Running with dbt=1.11.6
14:07:51  Registered adapter: duckdb=1.10.1
14:07:51  Found 1 snapshot, 6 models, 24 data tests, 3 sources, 480 macros
14:07:51
14:07:51  Concurrency: 1 threads (target='dev')
14:07:51
14:07:51  1 of 31 START sql view model main_staging.stg_measures ......................... [RUN]
14:07:51  1 of 31 OK created sql view model main_staging.stg_measures .................... [OK in 0.03s]
14:07:51  2 of 31 START sql view model main_staging.stg_readings ......................... [RUN]
14:07:51  2 of 31 OK created sql view model main_staging.stg_readings .................... [OK in 0.01s]
14:07:51  3 of 31 START sql view model main_staging.stg_stations ......................... [RUN]
14:07:51  3 of 31 OK created sql view model main_staging.stg_stations .................... [OK in 0.01s]
14:07:51  4 of 31 START test not_null_stg_measures_measure_id ............................ [RUN]
14:07:51  4 of 31 PASS not_null_stg_measures_measure_id .................................. [PASS in 0.02s]
14:07:51  5 of 31 START test not_null_stg_measures_station_id ............................ [RUN]
14:07:51  5 of 31 PASS not_null_stg_measures_station_id .................................. [PASS in 0.01s]
14:07:51  6 of 31 START test unique_stg_measures_measure_id .............................. [RUN]
14:07:51  6 of 31 PASS unique_stg_measures_measure_id .................................... [PASS in 0.01s]
14:07:51  7 of 31 START test not_null_stg_readings_measure_id ............................ [RUN]
14:07:51  7 of 31 PASS not_null_stg_readings_measure_id .................................. [PASS in 0.09s]
14:07:51  8 of 31 START test not_null_stg_readings_reading_timestamp ..................... [RUN]
14:07:51  8 of 31 PASS not_null_stg_readings_reading_timestamp ........................... [PASS in 0.02s]
14:07:51  9 of 31 START test not_null_stg_readings_reading_value ......................... [RUN]
14:07:51  9 of 31 PASS not_null_stg_readings_reading_value ............................... [PASS in 0.01s]
14:07:51  10 of 31 START test accepted_values_stg_stations_status__Active__Closed__Suspended__ukcmf__Unknown  [RUN]
14:07:51  10 of 31 PASS accepted_values_stg_stations_status__Active__Closed__Suspended__ukcmf__Unknown  [PASS in 0.01s]
14:07:51  11 of 31 START test not_null_stg_stations_station_id ........................... [RUN]
14:07:51  11 of 31 PASS not_null_stg_stations_station_id ................................. [PASS in 0.01s]
14:07:51  12 of 31 START test not_null_stg_stations_station_name ......................... [RUN]
14:07:51  12 of 31 PASS not_null_stg_stations_station_name ............................... [PASS in 0.01s]
14:07:51  13 of 31 START test not_null_stg_stations_status ............................... [RUN]
14:07:51  13 of 31 PASS not_null_stg_stations_status ..................................... [PASS in 0.01s]
14:07:51  14 of 31 START test relationships_stg_measures_station_id__station_id__ref_stg_stations_  [RUN]
14:07:51  14 of 31 WARN 90 relationships_stg_measures_station_id__station_id__ref_stg_stations_  [WARN 90 in 0.02s]
14:07:51  15 of 31 START test unique_stg_stations_station_id ............................. [RUN]
14:07:51  15 of 31 PASS unique_stg_stations_station_id ................................... [PASS in 0.01s]
14:07:51  16 of 31 START sql table model main_marts.dim_measures ......................... [RUN]
14:07:51  16 of 31 OK created sql table model main_marts.dim_measures .................... [OK in 0.04s]
14:07:51  17 of 31 START sql incremental model main_marts.fct_readings ................... [RUN]
14:07:52  17 of 31 OK created sql incremental model main_marts.fct_readings .............. [OK in 0.91s]
14:07:52  18 of 31 START sql table model main_marts.dim_stations ......................... [RUN]
14:07:52  18 of 31 OK created sql table model main_marts.dim_stations .................... [OK in 0.05s]
14:07:52  19 of 31 START snapshot snapshots.snp_stations ................................. [RUN]
14:07:52  19 of 31 ERROR snapshotting snapshots.snp_stations ............................. [ERROR in 0.06s]
14:07:52  20 of 31 START test not_null_dim_measures_measure_id ........................... [RUN]
14:07:52  20 of 31 PASS not_null_dim_measures_measure_id ................................. [PASS in 0.01s]
14:07:52  21 of 31 START test not_null_dim_measures_station_id ........................... [RUN]
14:07:52  21 of 31 PASS not_null_dim_measures_station_id ................................. [PASS in 0.01s]
14:07:52  22 of 31 START test unique_dim_measures_measure

... [8270 characters truncated] ...

.......... [RUN]
14:07:52  18 of 31 OK created sql table model main_marts.dim_stations .................... [OK in 0.05s]
14:07:52  19 of 31 START snapshot snapshots.snp_stations ................................. [RUN]
14:07:52  19 of 31 ERROR snapshotting snapshots.snp_stations ............................. [ERROR in 0.06s]
14:07:52  20 of 31 START test not_null_dim_measures_measure_id ........................... [RUN]
14:07:52  20 of 31 PASS not_null_dim_measures_measure_id ................................. [PASS in 0.01s]
14:07:52  21 of 31 START test not_null_dim_measures_station_id ........................... [RUN]
14:07:52  21 of 31 PASS not_null_dim_measures_station_id ................................. [PASS in 0.01s]
14:07:52  22 of 31 START test unique_dim_measures_measure_id ............................. [RUN]
14:07:52  22 of 31 PASS unique_dim_measures_measure_id ................................... [PASS in 0.01s]
14:07:52  23 of 31 START test assert_readings_values_in_reasonable_range ................. [RUN]
14:07:53  23 of 31 WARN 567 assert_readings_values_in_reasonable_range ................... [WARN 567 in 0.01s]
14:07:53  24 of 31 START test not_null_fct_readings_measure_id ........................... [RUN]
14:07:53  24 of 31 PASS not_null_fct_readings_measure_id ................................. [PASS in 0.01s]
14:07:53  25 of 31 START test not_null_fct_readings_reading_timestamp .................... [RUN]
14:07:53  25 of 31 PASS not_null_fct_readings_reading_timestamp .......................... [PASS in 0.01s]
14:07:53  26 of 31 START test not_null_fct_readings_reading_value ........................ [RUN]
14:07:53  26 of 31 PASS not_null_fct_readings_reading_value .............................. [PASS in 0.01s]
14:07:53  27 of 31 START test relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [RUN]
14:07:53  27 of 31 PASS relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [PASS in 0.02s]
14:07:53  28 of 31 START test not_null_dim_stations_station_id ........................... [RUN]
14:07:53  28 of 31 PASS not_null_dim_stations_station_id ................................. [PASS in 0.01s]
14:07:53  29 of 31 START test not_null_dim_stations_station_name ......................... [RUN]
14:07:53  29 of 31 PASS not_null_dim_stations_station_name ............................... [PASS in 0.01s]
14:07:53  30 of 31 START test relationships_dim_measures_station_id__station_id__ref_dim_stations_  [RUN]
14:07:53  30 of 31 WARN 90 relationships_dim_measures_station_id__station_id__ref_dim_stations_  [WARN 90 in 0.03s]
14:07:53  31 of 31 START test unique_dim_stations_station_id ............................. [RUN]
14:07:53  31 of 31 PASS unique_dim_stations_station_id ................................... [PASS in 0.01s]
14:07:53
14:07:53  Finished running 1 incremental model, 1 snapshot, 2 table models, 24 data tests, 3 view models in 0 hours 0 minutes and 1.61 seconds (1.61s).
14:07:53
14:07:53  Completed with 1 error, 0 partial successes, and 3 warnings:
14:07:53
14:07:53  Failure in snapshot snp_stations (snapshots/snp_stations.sql)
14:07:53    Runtime Error in snapshot snp_stations (snapshots/snp_stations.sql)
  Conversion Error: Malformed JSON at byte 0 of input: unexpected character.  Input: "Derwent" when casting from source column catchment_name

  LINE 132:         ) or snapshotted_data."catchment_name" != source_data."catchment_name"
                                                              ^
14:07:53
14:07:53  Warning in test relationships_stg_measures_station_id__station_id__ref_stg_stations_ (models/staging/_stg_models.yml)
14:07:53  Got 90 results, configured to warn if != 0
14:07:53
14:07:53    compiled code at target/compiled/flood_monitoring/models/staging/_stg_models.yml/relationships_stg_measures_c9b3c5abdfef2456c04e8e7879bbaf46.sql
14:07:53
14:07:53  Warning in test assert_readings_values_in_reasonable_range (tests/assert_readings_values_in_reasonable_range.sql)
14:07:53  Got 567 results, configured to warn if != 0
14:07:53
14:07:53    compiled code at target/compiled/flood_monitoring/tests/assert_readings_values_in_reasonable_range.sql
14:07:53
14:07:53  Warning in test relationships_dim_measures_station_id__station_id__ref_dim_stations_ (models/marts/_marts_models.yml)
14:07:53  Got 90 results, configured to warn if != 0
14:07:53
14:07:53    compiled code at target/compiled/flood_monitoring/models/marts/_marts_models.yml/relationships_dim_measures_ecdc550b700458cc9102b8d4e9f0c20e.sql
14:07:53
14:07:53  Done. PASS=27 WARN=3 ERROR=1 SKIP=0 NO-OP=0 TOTAL=31

python3 -c "
import duckdb
con = duckdb.connect('/workspace/flood_monitoring/flood_monitoring.duckdb')
con.execute('DROP TABLE IF EXISTS snapshots.snp_stations')
print('Dropped snapshot table')
con.close()
" 2>&1

14:08:16  Running with dbt=1.11.6
14:08:16  Registered adapter: duckdb=1.10.1
14:08:16  Found 1 snapshot, 6 models, 24 data tests, 3 sources, 480 macros
14:08:16
14:08:16  Concurrency: 1 threads (target='dev')
14:08:16
14:08:16  1 of 31 START sql view model main_staging.stg_measures ......................... [RUN]
14:08:16  1 of 31 OK created sql view model main_staging.stg_measures .................... [OK in 0.03s]
14:08:16  2 of 31 START sql view model main_staging.stg_readings ......................... [RUN]
14:08:16  2 of 31 OK created sql view model main_staging.stg_readings .................... [OK in 0.01s]
14:08:16  3 of 31 START sql view model main_staging.stg_stations ......................... [RUN]
14:08:16  3 of 31 OK created sql view model main_staging.stg_stations .................... [OK in 0.01s]
14:08:16  4 of 31 START test not_null_stg_measures_measure_id ............................ [RUN]
14:08:16  4 of 31 PASS not_null_stg_measures_measure_id .................................. [PASS in 0.01s]
14:08:16  5 of 31 START test not_null_stg_measures_station_id ............................ [RUN]
14:08:16  5 of 31 PASS not_null_stg_measures_station_id .................................. [PASS in 0.01s]
14:08:16  6 of 31 START test unique_stg_measures_measure_id .............................. [RUN]
14:08:16  6 of 31 PASS unique_stg_measures_measure_id .................................... [PASS in 0.01s]
14:08:16  7 of 31 START test not_null_stg_readings_measure_id ............................ [RUN]
14:08:16  7 of 31 PASS not_null_stg_readings_measure_id .................................. [PASS in 0.09s]
14:08:16  8 of 31 START test not_null_stg_readings_reading_timestamp ..................... [RUN]
14:08:17  8 of 31 PASS not_null_stg_readings_reading_timestamp ........................... [PASS in 0.02s]
14:08:17  9 of 31 START test not_null_stg_readings_reading_value ......................... [RUN]
14:08:17  9 of 31 PASS not_null_stg_readings_reading_value ............................... [PASS in 0.01s]
14:08:17  10 of 31 START test accepted_values_stg_stations_status__Active__Closed__Suspended__ukcmf__Unknown  [RUN]
14:08:17  10 of 31 PASS accepted_values_stg_stations_status__Active__Closed__Suspended__ukcmf__Unknown  [PASS in 0.01s]
14:08:17  11 of 31 START test not_null_stg_stations_station_id ........................... [RUN]
14:08:17  11 of 31 PASS not_null_stg_stations_station_id ................................. [PASS in 0.01s]
14:08:17  12 of 31 START test not_null_stg_stations_station_name ......................... [RUN]
14:08:17  12 of 31 PASS not_null_stg_stations_station_name ............................... [PASS in 0.01s]
14:08:17  13 of 31 START test not_null_stg_stations_status ............................... [RUN]
14:08:17  13 of 31 PASS not_null_stg_stations_status ..................................... [PASS in 0.01s]
14:08:17  14 of 31 START test relationships_stg_measures_station_id__station_id__ref_stg_stations_  [RUN]
14:08:17  14 of 31 WARN 90 relationships_stg_measures_station_id__station_id__ref_stg_stations_  [WARN 90 in 0.02s]
14:08:17  15 of 31 START test unique_stg_stations_station_id ............................. [RUN]
14:08:17  15 of 31 PASS unique_stg_stations_station_id ................................... [PASS in 0.01s]
14:08:17  16 of 31 START sql table model main_marts.dim_measures ......................... [RUN]
14:08:17  16 of 31 OK created sql table model main_marts.dim_measures .................... [OK in 0.04s]
14:08:17  17 of 31 START sql incremental model main_marts.fct_readings ................... [RUN]
14:08:17  17 of 31 OK created sql incremental model main_marts.fct_readings .............. [OK in 0.13s]
14:08:17  18 of 31 START sql table model main_marts.dim_stations ......................... [RUN]
14:08:17  18 of 31 OK created sql table model main_marts.dim_stations .................... [OK in 0.03s]
14:08:17  19 of 31 START snapshot snapshots.snp_stations ................................. [RUN]
14:08:17  19 of 31 OK snapshotted snapshots.snp_stations ................................. [OK in 0.04s]
14:08:17  20 of 31 START test not_null_dim_measures_measure_id ........................... [RUN]
14:08:17  20 of 31 PASS not_null_dim_measures_measure_id ................................. [PASS in 0.01s]
14:08:17  21 of 31 START test not_null_dim_measures_station_id ........................... [RUN]
14:08:17  21 of 31 PASS not_null_dim_measures_station_id ................................. [PASS in 0.01s]
14:08:17  22 of 31 START test unique_dim_measures_measure_id ............................. [RUN]
14:08:17  22 of 31 PASS unique_dim_measures_measure_id ................................... [PASS in 0.01s]
14:08:17  23 of 31 START test assert_readings_values_in_reasonable_range ................. [RUN]
14:08:17  23 of 31 WARN 567 assert_readings_values_in_reasonable_range ................... [WARN 567 in 0.02s]
14:08:17  24 of 31 START test not_null_fct_readings_measure_id ........................... [RUN]
14:08:17  24 of 31 PASS not_null_fct_readings_measure_id ................................. [PASS in 0.01s]
14:08:17  25 of 31 START test not_null_fct_readings_reading_timestamp .................... [RUN]
14:08:17  25 of 31 PASS not_null_fct_readings_reading_timestamp .......................... [PASS in 0.01s]
14:08:17  26 of 31 START test not_null_fct_readings_reading_value ........................ [RUN]
14:08:17  26 of 31 PASS not_null_fct_readings_reading_value .............................. [PASS in 0.01s]
14:08:17  27 of 31 START test relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [RUN]
14:08:17  27 of 31 PASS relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [PASS in 0.02s]
14:08:17  28 of 31 START test not_null_dim_stations_station_id ........................... [RUN]
14:08:17  28 of 31 PASS not_null_dim_stations_station_id ................................. [PASS in 0.01s]
14:08:17  29 of 31 START test not_null_dim_stations_station_name ......................... [RUN]
14:08:17  29 of 31 PASS not_null_dim_stations_station_name ............................... [PASS in 0.01s]
14:08:17  30 of 31 START test relationships_dim_measures_station_id__station_id__ref_dim_stations_  [RUN]
14:08:17  30 of 31 WARN 90 relationships_dim_measures_station_id__station_id__ref_dim_stations_  [WARN 90 in 0.01s]
14:08:17  31 of 31 START test unique_dim_stations_station_id ............................. [RUN]
14:08:17  31 of 31 PASS unique_dim_stations_station_id ................................... [PASS in 0.01s]
14:08:17
14:08:17  Finished running 1 incremental model, 1 snapshot, 2 table models, 24 data tests, 3 view models in 0 hours 0 minutes and 0.73 seconds (0.73s).
14:08:17
14:08:17  Completed with 3 warnings:
14:08:17
14:08:17  Warning in test relationships_stg_measures_station_id__station_id__ref_stg_stations_ (models/staging/_stg_models.yml)
14:08:17  Got 90 results, configured to warn if != 0
14:08:17
14:08:17    compiled code at target/compiled/flood_monitoring/models/staging/_stg_models.yml/relationships_stg_measures_c9b3c5abdfef2456c04e8e7879bbaf46.sql
14:08:17
14:08:17  Warning in test assert_readings_values_in_reasonable_range (tests/assert_readings_values_in_reasonable_range.sql)
14:08:17  Got 567 results, configured to warn if != 0
14:08:17
14:08:17    compiled code at target/compiled/flood_monitoring/tests/assert_readings_values_in_reasonable_range.sql
14:08:17
14:08:17  Warning in test relationships_dim_measures_station_id__station_id__ref_dim_stations_ (models/marts/_marts_models.yml)
14:08:17  Got 90 results, configured to warn if != 0
14:08:17
14:08:17    compiled code at target/compiled/flood_monitoring/models/marts/_marts_models.yml/relationships_dim_measures_ecdc550b700458cc9102b8d4e9f0c20e.sql
14:08:17
14:08:17  Done. PASS=28 WARN=3 ERROR=0 SKIP=0 NO-OP=0 TOTAL=31

python3 -c "
import duckdb
con = duckdb.connect('/workspace/flood_monitoring/flood_monitoring.duckdb')

print('=== FINAL DATA SUMMARY ===')
print()

# dim_stations
cnt = con.execute('SELECT count(*) FROM main_marts.dim_stations').fetchone()[0]
print(f'dim_stations: {cnt:,} stations')
rows = con.execute('SELECT station_id, station_name, river_name, town, status, measure_count FROM main_marts.dim_stations WHERE status = \'Active\' LIMIT 3').fetchall()
for r in rows:
    print(f'  {r}')

print()

# dim_measures
cnt = con.execute('SELECT count(*) FROM main_marts.dim_measures').fetchone()[0]
print(f'dim_measures: {cnt:,} measures')
rows = con.execute('SELECT parameter_type, count(*) FROM main_marts.dim_measures GROUP BY parameter_type ORDER BY count(*) DESC').fetchall()
for r in rows:
    print(f'  {r[0]}: {r[1]:,}')

print()

# fct_readings
cnt = con.execute('SELECT count(*) FROM main_marts.fct_readings').fetchone()[0]
min_dt = con.execute('SELECT min(reading_timestamp) FROM main_marts.fct_readings').fetchone()[0]
max_dt = con.execute('SELECT max(reading_timestamp) FROM main_marts.fct_readings').fetchone()[0]
print(f'fct_readings: {cnt:,} readings from {min_dt} to {max_dt}')

print()

# Snapshot
cnt = con.execute('SELECT count(*) FROM snapshots.snp_stations').fetchone()[0]
print(f'snp_stations: {cnt:,} snapshot rows')
rows = con.execute('SELECT station_id, station_name, status, dbt_valid_from, dbt_valid_to FROM snapshots.snp_stations LIMIT 3').fetchall()
for r in rows:
    print(f'  {r}')

con.close()
" 2>&1

=== FINAL DATA SUMMARY ===

dim_stations: 5,463 stations
  ('1029TH', 'Bourton Dickler', 'River Dikler', 'Little Rissington', 'Active', 2)
  ('E2043', 'Surfleet Sluice', 'River Glen', 'Surfleet Seas End', 'Active', 1)
  ('52119', 'Gaw Bridge', 'River Parrett', 'Kingsbury Episcopi', 'Active', 1)

dim_measures: 6,917 measures
  level: 5,370
  rainfall: 1,129
  flow: 366
  temperature: 25
  wind: 19
  velocity: 5
  area: 1
  people_count: 1
  state: 1

fct_readings: 1,429,318 readings from 2025-03-01 00:00:00 to 2025-03-03 23:59:10

snp_stations: 5,463 snapshot rows
  ('1029TH', 'Bourton Dickler', 'Active', datetime.datetime(2026, 3, 9, 14, 8, 17, 324882), None)
  ('E2043', 'Surfleet Sluice', 'Active', datetime.datetime(2026, 3, 9, 14, 8, 17, 324882), None)
  ('52119', 'Gaw Bridge', 'Active', datetime.datetime(2026, 3, 9, 14, 8, 17, 324882), None)

python3 -c "
import duckdb
con = duckdb.connect('/workspace/flood_monitoring/flood_monitoring.duckdb')
con.execute('DROP TABLE IF EXISTS main_marts.fct_readings__dbt_backup')
print('Cleaned up backup table')
con.close()
" 2>&1

/workspace/flood_monitoring/dbt_project.yml
/workspace/flood_monitoring/macros/create_raw_sources.sql
/workspace/flood_monitoring/macros/load_raw_data.sql
/workspace/flood_monitoring/models/marts/_marts_models.yml
/workspace/flood_monitoring/models/marts/dim_measures.sql
/workspace/flood_monitoring/models/marts/dim_stations.sql
/workspace/flood_monitoring/models/marts/fct_readings.sql
/workspace/flood_monitoring/models/staging/_sources.yml
/workspace/flood_monitoring/models/staging/_stg_models.yml
/workspace/flood_monitoring/models/staging/stg_measures.sql
/workspace/flood_monitoring/models/staging/stg_readings.sql
/workspace/flood_monitoring/models/staging/stg_stations.sql
/workspace/flood_monitoring/snapshots/snp_stations.sql
/workspace/flood_monitoring/tests/assert_readings_values_in_reasonable_range.sql

14:09:14  Running with dbt=1.11.6
14:09:15  Registered adapter: duckdb=1.10.1
14:09:15  Found 1 snapshot, 6 models, 24 data tests, 3 sources, 477 macros
14:09:15
14:09:15  Concurrency: 1 threads (target='dev')
14:09:15
14:09:15  1 of 31 START sql view model main_staging.stg_measures ......................... [RUN]
14:09:15  1 of 31 OK created sql view model main_staging.stg_measures .................... [OK in 0.04s]
14:09:15  2 of 31 START sql view model main_staging.stg_readings ......................... [RUN]
14:09:15  2 of 31 OK created sql view model main_staging.stg_readings .................... [OK in 0.02s]
14:09:15  3 of 31 START sql view model main_staging.stg_stations ......................... [RUN]
14:09:15  3 of 31 OK created sql view model main_staging.stg_stations .................... [OK in 0.03s]
14:09:15  4 of 31 START test not_null_stg_measures_measure_id ............................ [RUN]
14:09:15  4 of 31 PASS not_null_stg_measures_measure_id .................................. [PASS in 0.02s]
14:09:15  5 of 31 START test not_null_stg_measures_station_id ............................ [RUN]
14:09:15  5 of 31 PASS not_null_stg_measures_station_id .................................. [PASS in 0.02s]
14:09:15  6 of 31 START test unique_stg_measures_measure_id .............................. [RUN]
14:09:15  6 of 31 PASS unique_stg_measures_measure_id .................................... [PASS in 0.01s]
14:09:15  7 of 31 START test not_null_stg_readings_measure_id ............................ [RUN]
14:09:15  7 of 31 PASS not_null_stg_readings_measure_id .................................. [PASS in 0.08s]
14:09:15  8 of 31 START test not_null_stg_readings_reading_timestamp ..................... [RUN]
14:09:15  8 of 31 PASS not_null_stg_readings_reading_timestamp ........................... [PASS in 0.02s]
14:09:15  9 of 31 START test not_null_stg_readings_reading_value ......................... [RUN]
14:09:15  9 of 31 PASS not_null_stg_readings_reading_value ............................... [PASS in 0.01s]
14:09:15  10 of 31 START test accepted_values_stg_stations_status__Active__Closed__Suspended__ukcmf__Unknown  [RUN]
14:09:15  10 of 31 PASS accepted_values_stg_stations_status__Active__Closed__Suspended__ukcmf__Unknown  [PASS in 0.01s]
14:09:15  11 of 31 START test not_null_stg_stations_station_id ........................... [RUN]
14:09:15  11 of 31 PASS not_null_stg_stations_station_id ................................. [PASS in 0.01s]
14:09:15  12 of 31 START test not_null_stg_stations_station_name ......................... [RUN]
14:09:15  12 of 31 PASS not_null_stg_stations_station_name ............................... [PASS in 0.01s]
14:09:15  13 of 31 START test not_null_stg_stations_status ............................... [RUN]
14:09:15  13 of 31 PASS not_null_stg_stations_status ..................................... [PASS in 0.01s]
14:09:15  14 of 31 START test relationships_stg_measures_station_id__station_id__ref_stg_stations_  [RUN]
14:09:15  14 of 31 WARN 90 relationships_stg_measures_station_id__station_id__ref_stg_stations_  [WARN 90 in 0.02s]
14:09:15  15 of 31 START test unique_stg_stations_station_id ............................. [RUN]
14:09:15  15 of 31 PASS unique_stg_stations_station_id ................................... [PASS in 0.01s]
14:09:15  16 of 31 START sql table model main_marts.dim_measures ......................... [RUN]
14:09:15  16 of 31 OK created sql table model main_marts.dim_measures .................... [OK in 0.04s]
14:09:15  17 of 31 START sql incremental model main_marts.fct_readings ................... [RUN]
14:09:15  17 of 31 OK created sql incremental model main_marts.fct_readings .............. [OK in 0.13s]
14:09:15  18 of 31 START sql table model main_marts.dim_stations ......................... [RUN]
14:09:16  18 of 31 OK created sql table model main_marts.dim_stations .................... [OK in 0.03s]
14:09:16  19 of 31 START snapshot snapshots.snp_stations ................................. [RUN]
14:09:16  19 of 31 OK snapshotted snapshots.snp_stations ................................. [OK in 0.08s]
14:09:16  20 of 31 START test not_null_dim_measures_measure_id ........................... [RUN]
14:09:16  20 of 31 PASS not_null_dim_measures_measure_id ................................. [PASS in 0.01s]
14:09:16  21 of 31 START test not_null_dim_measures_station_id ........................... [RUN]
14:09:16  21 of 31 PASS not_null_dim_measures_station_id ................................. [PASS in 0.01s]
14:09:16  22 of 31 START test unique_dim_measures_measure_id ............................. [RUN]
14:09:16  22 of 31 PASS unique_dim_measures_measure_id ................................... [PASS in 0.01s]
14:09:16  23 of 31 START test assert_readings_values_in_reasonable_range ................. [RUN]
14:09:16  23 of 31 WARN 567 assert_readings_values_in_reasonable_range ................... [WARN 567 in 0.02s]
14:09:16  24 of 31 START test not_null_fct_readings_measure_id ........................... [RUN]
14:09:16  24 of 31 PASS not_null_fct_readings_measure_id ................................. [PASS in 0.01s]
14:09:16  25 of 31 START test not_null_fct_readings_reading_timestamp .................... [RUN]
14:09:16  25 of 31 PASS not_null_fct_readings_reading_timestamp .......................... [PASS in 0.01s]
14:09:16  26 of 31 START test not_null_fct_readings_reading_value ........................ [RUN]
14:09:16  26 of 31 PASS not_null_fct_readings_reading_value .............................. [PASS in 0.01s]
14:09:16  27 of 31 START test relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [RUN]
14:09:16  27 of 31 PASS relationships_fct_readings_measure_id__measure_id__ref_dim_measures_  [PASS in 0.02s]
14:09:16  28 of 31 START test not_null_dim_stations_station_id ........................... [RUN]
14:09:16  28 of 31 PASS not_null_dim_stations_station_id ................................. [PASS in 0.01s]
14:09:16  29 of 31 START test not_null_dim_stations_station_name ......................... [RUN]
14:09:16  29 of 31 PASS not_null_dim_stations_station_name ............................... [PASS in 0.01s]
14:09:16  30 of 31 START test relationships_dim_measures_station_id__station_id__ref_dim_stations_  [RUN]
14:09:16  30 of 31 WARN 90 relationships_dim_measures_station_id__station_id__ref_dim_stations_  [WARN 90 in 0.01s]
14:09:16  31 of 31 START test unique_dim_stations_station_id ............................. [RUN]
14:09:16  31 of 31 PASS unique_dim_stations_station_id ................................... [PASS in 0.01s]
14:09:16
14:09:16  Finished running 1 incremental model, 1 snapshot, 2 table models, 24 data tests, 3 view models in 0 hours 0 minutes and 0.86 seconds (0.86s).
14:09:16
14:09:16  Completed with 3 warnings:
14:09:16
14:09:16  Warning in test relationships_stg_measures_station_id__station_id__ref_stg_stations_ (models/staging/_stg_models.yml)
14:09:16  Got 90 results, configured to warn if != 0
14:09:16
14:09:16    compiled code at target/compiled/flood_monitoring/models/staging/_stg_models.yml/relationships_stg_measures_c9b3c5abdfef2456c04e8e7879bbaf46.sql
14:09:16
14:09:16  Warning in test assert_readings_values_in_reasonable_range (tests/assert_readings_values_in_reasonable_range.sql)
14:09:16  Got 567 results, configured to warn if != 0
14:09:16
14:09:16    compiled code at target/compiled/flood_monitoring/tests/assert_readings_values_in_reasonable_range.sql
14:09:16
14:09:16  Warning in test relationships_dim_measures_station_id__station_id__ref_dim_stations_ (models/marts/_marts_models.yml)
14:09:16  Got 90 results, configured to warn if != 0
14:09:16
14:09:16    compiled code at target/compiled/flood_monitoring/models/marts/_marts_models.yml/relationships_dim_measures_ecdc550b700458cc9102b8d4e9f0c20e.sql
14:09:16
14:09:16  Done. PASS=28 WARN=3 ERROR=0 SKIP=0 NO-OP=0 TOTAL=31