Flyers 6 – Flyers — Data Cleansing

BEGIN
    CREATE TABLE #temptable (
        LoadDate date,
        NumDays INT
    )

    DECLARE @adidold AS INT = 0
    DECLARE @adidnew AS INT
    DECLARE @loaddate AS DATE

    DECLARE c CURSOR FOR
        SELECT LoadDate, MIN(AdID) idd
        FROM FACT_Ad_v3
        GROUP BY LoadDate
        ORDER BY LoadDate

    OPEN c
    FETCH NEXT FROM c INTO @loaddate, @adidnew

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO #temptable VALUES (
            @loaddate,
            (
                SELECT CAST(CAST(@loaddate AS DATETIME) AS INT)
                     - CAST(CAST((SELECT Loaddate FROM FACT_Ad_v3 WHERE AdID = @adidold) AS DATETIME) AS INT)
            )
        )
        SET @adidold = @adidnew
        FETCH NEXT FROM c INTO @loaddate, @adidnew
    END

    CLOSE c
    DEALLOCATE c

    SELECT * FROM #temptable
    ORDER BY NumDays DESC

    DROP TABLE #temptable
END

The results of this query show a lot more missing data than I was hoping for. The key cut-off periods we care about are 7, 13, and 14. To calculate the maximum data sets that are missing we will look at the worst case scenario where the first failure in a series begins the same day an advertisement changes, meaning we will need seven days in a row of failures to lose the data. Likewise, missing two periods of data could occur in as little time as 14 days. To calculate the minimum data sets that are missing we will look at the best case scenario where the failures start the day after an advertisement changes and resumes on the last day of the following data set (think a Tuesday through the following Sunday) resulting in a gap of 13 days potentially not losing data. There are 37 instances where data failed to load for at least 8 days, and 5 periods of at least 14 days, Giving us a maximum of 42 lost data periods. There are 6 periods of missing loads for at least 13 days. Therefore there are between 6 and 42 missing sets of advertisement data prior to implementing a solution to address API timeouts, which occurred about 21.5 months after the pipeline began running, which is roughly 93 weeks. This tells us we should still have between 51 and 87 sets of weekly advertisement data for analysis.

• Invest additional time to deep dive into the root cause of the failures. There might be a timeout setting that would fix the issue (in fact there is a timeout setting within the dataflow transformation, however all my tests show the API returning results in a matter of seconds, much faster than the default 30 seconds timeout).
• Add pipeline logic to kick off additional workflow upon failure. The simplest of these would be to send a notification upon failure to an email address that the responsible owner/team would monitor. This would alert someone that the pipeline failed, and allow the appropriate party to check for underlying changes that may have caused the failure, or kick off the pipeline again as needed.
• The pipeline and dataflows could be separated by store so that a failure loading one doesn’t prevent the others from loading. There is a balance of cost and value to consider when making changes that will cause the cost of operations to increase. Each dataflow requires an Apache Spark Cluster to spin up, which takes up the majority of the computation time. Splitting one dataflow into five separate dataflows would roughly 5x the cost of running this project.
• The pipeline can be set to retry a certain number of times. This is the simplest solution. It will potentially cause ADF to use more resources as it will continue attempting to run for the specified number of attempts. The difference between this solution and splitting the dataflows (or even more extreme a combination of splitting and setting a maximum number of retries) is that splitting the dataflows guarantees longer execution times as each dataflow will require a cluster to spin up. Setting a maximum number of retries for the pipeline could execute on the first time and be finished early.

CREATE PROCEDURE [dbo].[UpdateFact] AS
BEGIN
    DECLARE @productid INT

    DECLARE c CURSOR FOR
        SELECT DISTINCT ProductID
        FROM FACT_Ad_v3

    OPEN c
    FETCH NEXT FROM c INTO @productid

    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE FACT_Ad_v3
        SET BadProductFlag = 0
        WHERE ProductID = @productid
          AND StartDate = (
                SELECT MIN(StartDate)
                FROM FACT_Ad_v3
                WHERE ProductID = @productid
          )
          AND MerchantID = (
                SELECT MerchantID
                FROM FACT_Ad_v3
                WHERE AdID = (
                    SELECT MIN(AdID)
                    FROM FACT_Ad_v3
                    WHERE ProductID = @productid
                )
          );

        FETCH NEXT FROM c INTO @productid
    END

    CLOSE c
    DEALLOCATE c
END
GO