gold, ingots, treasure, bullion, gold bars, wealth, gold, gold, gold, gold, gold

Advanced Analytics in the Gold Layer: Leveraging Window Functions for Insights and Synthetic Testing

Data / By /

In this post, we will explore how to use SQL window functions within the Gold layer to solve two common needs: calculating granular aggregates and generating synthetic test data for healthcare scenarios.

Modern data engineering has converged on the Medallion Architecture as the gold standard for managing scalable data lakes. By organizing data into Bronze (raw), Silver (cleansed), and Gold (curated) layers using the Delta file format, organizations can ensure high performance and ACID compliance. This structure transforms fragmented operational data—like raw patient check-ins—into reliable, versioned assets ready for high-stakes clinical and operational analysis.

To make this data accessible, Azure users leverage Azure Synapse Serverless SQL Pools to define external tables over these Delta files. This creates a powerful logical database schema that bridges the gap between the data lake and the end-user. For a data analyst, this setup provides the familiar structure of a traditional SQL warehouse without the physical infrastructure overhead, allowing for high-speed queries directly against the lake.

Despite the evolution of data tools, SQL remains the undisputed language of choice for data professionals across all industries. Its ability to express complex logic clearly makes it the ideal tool for solving sophisticated business requirements.

Understanding Window Functions

Typically, when you perform aggregations like SUM() or COUNT(), you use a GROUP BY clause. This collapses your dataset, returning one summary row for every group. While useful, it means you lose the granularity of the original individual rows.

Window functions change the game. They allow you to “enhance” your data by appending aggregate values to every single row without changing the total row count.

Below is a healthcare-focused example using a patient scheduling table. This query answers questions like: “What was the longest appointment duration recorded for this clinic?” while still displaying the details of every individual appointment record.

Healthcare Example: Silver Layer Patient Scheduling

This query analyzes the [health_system].[patient_scheduling] table to provide context for each clinic location.

SQL

SELECT TOP 15
      [appointment_date]
    , [clinic_name]
    , [department_id]
    , [provider_name]
    , [appointment_duration_min]
    , [check_in_count]
    , [no_show_count]
    
    -- The maximum duration ever recorded for this specific clinic
    , MAX(appointment_duration_min) OVER (
        PARTITION BY [clinic_name]
    ) AS max_duration_for_clinic 

    -- Counts how many appointments in this clinic exceeded 30 minutes
    , SUM(
        CASE WHEN appointment_duration_min > 30 THEN 1 ELSE 0 END 
    ) OVER (
        PARTITION BY [clinic_name]
    ) AS high_volume_appointments_count

    -- Total number of records available for this specific clinic
    , COUNT(*) OVER (
        PARTITION BY [clinic_name]
    ) AS total_clinic_records

    -- Ranks appointments by date per clinic (1 = Most Recent)
    , ROW_NUMBER() OVER (
        PARTITION BY [clinic_name]
        ORDER BY [appointment_date] DESC
    ) AS RecencyRank 

FROM [silver].[health_system].[patient_scheduling]
ORDER BY appointment_date DESC

Generating Synthetic Test Data

Beyond reporting, window functions are incredibly useful for Data Engineering tasks, such as creating temporary test data in the Gold layer.

The Requirement

Suppose we have 5 distinct clinics in our scheduling system. To test a new dashboard, we need 2 additional “dummy” rows for every clinic, dated for today. We don’t want to manually type out values; we just want to duplicate the structure of our most recent records.

The Logic

We can use ROW_NUMBER() to identify the two most recent records for every clinic. By wrapping this logic in a subquery and filtering where the rank is $\le 2$, we isolate a subset of data that we can then “mask” with today’s date and append back to our results.

The Implementation

We create a Gold View that combines our real Silver data with our generated test data using a UNION ALL.

SQL

CREATE VIEW [gold].[v_patient_scheduling_testing]
AS 
/* 1. Return all actual production data */
SELECT 
      [appointment_date]
    , [clinic_name]
    , [department_id]
    , [provider_name]
    , [appointment_duration_min]
    , [check_in_count]
    , [no_show_count]
FROM silver.[health_system].[patient_scheduling]

UNION ALL

/* 2. Append synthetic test data (2 rows per clinic) */
SELECT      
      CAST(GETDATE() AS DATETIME2) AS appointment_date -- Set date to today
    , [clinic_name]
    , [department_id]
    , [provider_name]
    , [appointment_duration_min]
    , [check_in_count]
    , [no_show_count] 
FROM
(
    SELECT 
          *
        , ROW_NUMBER() OVER (
            PARTITION BY [clinic_name]
            ORDER BY [appointment_date] DESC
        ) AS RowRank 
    FROM silver.[health_system].[patient_scheduling]
) AS RankedData
WHERE RankedData.RowRank <= 2;
GO

-- Preview the results
SELECT TOP 100 * FROM gold.v_patient_scheduling_testing
ORDER BY appointment_date DESC;

Final Thoughts: Why use a View with UNION ALL?

The elegance of this solution lies in its immutability. The “fake” test data is never actually written to your storage or appended to your Delta tables. It only exists in memory the moment the view is called.

This allows analysts to build and test reports against “today’s data” without a single INSERT statement, keeping your Silver layer pristine while providing a flexible, “logical” Gold layer for development.

Must Read