The provided SQL code defines a stored procedure with the...

September 2, 2025 at 03:55 PM

CREATE OR REPLACE PROCEDURE DA_DEV_SILVER.EMHM_STAGING.SP_DIM_AGED_AR_GRP_INSERT()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
AS '
DECLARE
  target_table STRING DEFAULT ''DA_DEV_SILVER.EMHM_STAGING.DIM_AGED_AR_GRP'';
  rows_loaded NUMBER;
  MASSAGE VARCHAR DEFAULT ''STARTING'';
BEGIN
  -- Ensure target exists
  EXECUTE IMMEDIATE ''
    CREATE TABLE IF NOT EXISTS '' || target_table || ''(AGEDARGRPAK STRING)'';
    MASSAGE := MASSAGE || target_table ||''TABLE CREATED'' ;

  -- Staging pattern: full refresh
  EXECUTE IMMEDIATE ''TRUNCATE TABLE '' || target_table;

  -- Load data
  EXECUTE IMMEDIATE ''
    INSERT INTO '' || target_table || '' (AGEDARGRPAK)
    WITH unioned AS (
      SELECT tnpc.NewClass AS AgedARGrpAK
      FROM SANDBOX.POC_SILVER.MGSNEW_INSUR_PLAN ipl
      LEFT JOIN TABLE(
        DA_DEV_SILVER.STAGING.TFNNEWPAYORCLASS(ipl.class::STRING, ipl.carrierkey::STRING)
      ) tnpc

      UNION ALL

      SELECT tnpc.NewClass AS AgedARGrpAK
      FROM SANDBOX.POC_SILVER.VSQL_INSUR_PLAN ipl
      LEFT JOIN TABLE(
        DA_DEV_SILVER.STAGING.TFNNEWPAYORCLASS(ipl.class::STRING, ipl.carrierkey::STRING)
      ) tnpc

      UNION ALL

      SELECT drc."Description" AS AgedARGrpAK
      FROM SANDBOX.POC_SILVER.DICTFSC df
      JOIN SANDBOX.POC_SILVER.DICTREPORTINGCATEGORY drc
        ON df."ReportingCategory1" = drc."Id"
    )
    SELECT DISTINCT TRIM(AgedARGrpAK)
    FROM unioned
    WHERE AgedARGrpAK IS NOT NULL
      AND LENGTH(TRIM(AgedARGrpAK)) > 0
  '';
 MASSAGE := MASSAGE || ''DATA LOADED'';
  rows_loaded := SQLROWCOUNT;
  MASSAGE := MASSAGE || rows_loaded|| ''NO OF RECORDS INSERTED'';

RETURN MASSAGE;
END;
';

The provided SQL code defines a stored procedure with the following functionality:

Purpose

The procedure is named SP_DIM_AGED_AR_GRP_INSERT and is intended to reload data into a target table, likely as part of an ETL (Extract, Transform, Load) process. Specifically, it:

Ensures a target table exists.
Performs a full refresh of the target table by truncating and reloading it with transformed data from multiple sources.

Detailed Steps

Setup Variables:
- target_table: Stores the name of the target table (DA_DEV_SILVER.EMHM_STAGING.DIM_AGED_AR_GRP).
- rows_loaded: Stores the number of rows loaded into the target table.
- MASSAGE: A log or message variable used to track progress.
Ensure Target Table Exists:
- Using EXECUTE IMMEDIATE, the procedure ensures the target table (DIM_AGED_AR_GRP) exists. If it doesn't exist, it creates the table with a single column AGEDARGRPAK (type STRING).
Truncate the Target Table:
- Deletes all data from the target table to prepare it for a fresh load.
Load Data into the Target Table:
- Data is sourced from three different datasets:
  - The first dataset uses SANDBOX.POC_SILVER.MGSNEW_INSUR_PLAN joined with a function DA_DEV_SILVER.STAGING.TFNNEWPAYORCLASS.
  - The second dataset uses SANDBOX.POC_SILVER.VSQL_INSUR_PLAN joined with the same function.
  - The third dataset retrieves descriptions mapped from SANDBOX.POC_SILVER.DICTFSC joined with SANDBOX.POC_SILVER.DICTREPORTINGCATEGORY.
- The results of the three queries are combined using a UNION ALL in a Common Table Expression (CTE) named unioned.
- Deduplicated, trimmed, and validated (IS NOT NULL and LENGTH > 0) data is loaded into the target table column AGEDARGRPAK.
Track and Return Progress:
- The procedure appends "progress messages" to the MASSAGE variable at each step (e.g., when the table is created, data is loaded, and the number of rows inserted).
- The SQL function SQLROWCOUNT captures the number of rows inserted, and this count is included in the progress message.
- The MASSAGE message is returned as the result of the procedure to notify the caller about the operation's success or progress details.

Example Output

Upon successful execution, the procedure returns a log message via the MASSAGE variable. For example:

STARTINGDA_DEV_SILVER.EMHM_STAGING.DIM_AGED_AR_GRP TABLE CREATEDDATA LOADED123 NO OF RECORDS INSERTED

This message indicates:

The table was created (if it didn't already exist).
Data was loaded into the table.
A total of 123 rows were inserted.

Summary

This code implements a stored procedure for maintaining and refreshing the contents of the DIM_AGED_AR_GRP table. It ensures the table exists, clears previous data, and reloads it with new, transformed, deduplicated records pulled from multiple source systems. The procedure also logs and returns the process's status and results.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr