merica-pipelines/dbt/models/silver/silver_cems_emission_enriched.sql

-- dbt Silver Cleansed CEMS Telemetry Enriched with Master Sources
SELECT 
  t.log_id,
  t.telemetry_timestamp,
  t.source_id,
  s.facility_name,
  s.industry_sector,
  s.chimney_code,
  s.region_zone,
  t.so2_mg_nm3,
  s.max_so2_limit_mg_nm3,
  t.nox_mg_nm3,
  s.max_nox_limit_mg_nm3,
  t.pm_dust_mg_nm3,
  s.max_pm_limit_mg_nm3,
  t.co_mg_nm3,
  t.flue_gas_flow_m3_s,
  t.flue_temp_celsius,
  ROUND((t.so2_mg_nm3 * t.flue_gas_flow_m3_s * 3600.0) / 1000000.0, 3) AS so2_mass_rate_kg_h,
  ROUND((t.nox_mg_nm3 * t.flue_gas_flow_m3_s * 3600.0) / 1000000.0, 3) AS nox_mass_rate_kg_h,
  ROUND((t.pm_dust_mg_nm3 * t.flue_gas_flow_m3_s * 3600.0) / 1000000.0, 3) AS pm_mass_rate_kg_h,
  CASE 
    WHEN t.so2_mg_nm3 > s.max_so2_limit_mg_nm3 OR t.nox_mg_nm3 > s.max_nox_limit_mg_nm3 OR t.pm_dust_mg_nm3 > s.max_pm_limit_mg_nm3 
    THEN 'EXCEEDING_LIMIT'
    ELSE 'COMPLIANT'
  END AS compliance_status,
  t.sensor_status
FROM {{ ref('bronze_raw_cems_telemetry_logs') }} t
JOIN {{ ref('bronze_ref_emission_sources') }} s
  ON t.source_id = s.source_id
WHERE t.sensor_status = 'VALID'