Convert SSIS packages to BigQuery SQL

<!-- Package: Shipment_ETL.dtsx -->
<!-- Data Flow Task: Load Shipments -->

OLE DB Source: [dbo].[STG_SHIPMENTS]
  SELECT ShipmentID, WarehouseID,
         Weight, ShipDate, Carrier
  FROM STG_SHIPMENTS
  WHERE ProcessedFlag = 0

Derived Column: [ShipCategory]
  (DT_WSTR,10)(
    Weight > 500  ? "Freight" :
    Weight > 50   ? "Parcel" :
    "Envelope"
  )

Lookup: [DIM_WAREHOUSES]
  JOIN ON WarehouseID = WhseID
  OUTPUT: WarehouseName, Region

OLE DB Destination: [dbo].[FACT_SHIPMENTS]
  INSERT INTO FACT_SHIPMENTS
  (ShipmentID, WarehouseName, Region,
   ShipCategory, Weight, ShipDate)

-- SSIS Data Flow → BigQuery SQL

-- MERGE replaces full Data Flow pipeline
MERGE INTO `project.dataset.fact_shipments` tgt
USING (
  SELECT
    s.ShipmentID,
    w.WarehouseName,
    w.Region,
    CASE
      WHEN s.Weight > 500 THEN 'Freight'
      WHEN s.Weight > 50  THEN 'Parcel'
      ELSE 'Envelope'
    END AS ShipCategory,
    s.Weight,
    s.ShipDate
  FROM `project.dataset.stg_shipments` s
  LEFT JOIN `project.dataset.dim_warehouses` w
    ON s.WarehouseID = w.WhseID
  WHERE s.ProcessedFlag = 0
) src
ON tgt.ShipmentID = src.ShipmentID
WHEN MATCHED THEN UPDATE SET
  tgt.WarehouseName = src.WarehouseName,
  tgt.ShipCategory  = src.ShipCategory,
  tgt.Weight        = src.Weight
WHEN NOT MATCHED THEN INSERT (
  ShipmentID, WarehouseName, Region,
  ShipCategory, Weight, ShipDate
) VALUES (
  src.ShipmentID, src.WarehouseName,
  src.Region, src.ShipCategory,
  src.Weight, src.ShipDate
);

-- Scheduled query replaces SSIS scheduling
-- Cloud Composer DAG for complex orchestration
-- with retry, branching, and alerting