gistfile1.txt

WITH osh_ranked AS (
    SELECT 
        customer_id, 
        ca_credit_turnover_amt AS osh_ca,
        eff_start_date,
        eff_end_date
    FROM (
        SELECT 
            customer_id, 
            ca_credit_turnover_amt,
            eff_start_date,
            eff_end_date,
            ROW_NUMBER() OVER (
                PARTITION BY customer_id 
                ORDER BY eff_start_date DESC, eff_end_date DESC
            ) AS rn
        FROM bsl_ukc_ngcb_bankdata_ukr
        WHERE bus_mth = 202505
    ) osh_sub
    WHERE osh_sub.rn = 1
),

cdh_ranked AS (
    SELECT 
        customer_id, 
        ca_credit_turnover_amt AS cdh_ca,
        eff_start_date,
        eff_end_date
    FROM (
        SELECT 
            customer_id, 
            ca_credit_turnover_amt,
            eff_start_date,
            eff_end_date,
            ROW_NUMBER() OVER (
                PARTITION BY customer_id 
                ORDER BY eff_start_date DESC, eff_end_date DESC
            ) AS rn
        FROM bsl_ukc_ngcb_bankdata_ukr_CHG1019913799_bkp
    ) cdh_sub
    WHERE cdh_sub.rn = 1
),

latest_data AS (
    SELECT 
        osh.customer_id, 
        osh.osh_ca, 
        cdh.cdh_ca
    FROM osh_ranked osh
    JOIN cdh_ranked cdh 
        ON osh.customer_id = cdh.customer_id
    WHERE osh.osh_ca != cdh.cdh_ca
)
SELECT 
    xref.ppeaccountid, 
    xref.cis_customer_id, 
    latest_data.osh_ca, 
    latest_data.cdh_ca
FROM bsl_ukc_ngcb_account_cardholder_xref xref
JOIN latest_data 
    ON xref.cis_customer_id = latest_data.customer_id;

debug-ci:
  stage: debug
  script:
    - echo "=== GITLAB CI METADATA ==="
    - echo "CI_SERVER_URL=$CI_SERVER_URL"
    - echo "CI_PROJECT_PATH=$CI_PROJECT_PATH"
    - echo "CI_PIPELINE_ID=$CI_PIPELINE_ID"
    - echo "CI_JOB_ID=$CI_JOB_ID"
    - echo "CI_JOB_URL=$CI_JOB_URL"
    - echo "CI_RUNNER_ID=$CI_RUNNER_ID"
    - echo "CI_RUNNER_DESCRIPTION=$CI_RUNNER_DESCRIPTION"
    - echo "CI_RUNNER_TAGS=$CI_RUNNER_TAGS"
    - echo "CI_RUNNER_EXECUTABLE_ARCH=$CI_RUNNER_EXECUTABLE_ARCH"
    
    ```

default:
  before_script:
    - echo "Slug is $SERVER_SLUG"
    - |
      if ! grep -q "^${SERVER_SLUG}=" ci/server-map.env; then
        echo "Invalid SERVER_SLUG=$SERVER_SLUG"
        exit 1
      fi
    - LINE=$(grep "^${SERVER_SLUG}=" ci/server-map.env | cut -d '=' -f2)
    - HOST=$(echo $LINE | cut -d '|' -f1)
    - SMB_PATH=$(echo $LINE | cut -d '|' -f2)
    - export HOST SMB_PATH
    - echo "Resolved HOST=$HOST"
    
    ```

import os
import sys
from pathlib import Path

def load_properties(path: Path) -> dict:
    data = {}
    for line in path.read_text(encoding="utf-8").splitlines():
        line = line.strip()
        if not line or line.startswith("#"):
            continue
        if "=" not in line:
            continue
        k, v = line.split("=", 1)
        data[k.strip()] = v.strip()
    return data

def main():
    env = os.environ.get("DEPLOY_ENV", "").lower()
    if env not in ("sit", "uat", "prd", "prod"):
        raise SystemExit("DEPLOY_ENV must be one of: SIT/UAT/PRD")

    env = "prd" if env == "prod" else env

    prop_file = Path(f"properties/{env}.properties")
    if not prop_file.exists():
        raise SystemExit(f"Missing properties file: {prop_file}")

    props = load_properties(prop_file)

    template_path = Path("src/config.py.template")
    out_path = Path("src/config.py")

    template = template_path.read_text(encoding="utf-8")

    # Replace {{KEY}} with Python-safe quoted values using repr()
    for k, v in props.items():
        template = template.replace(f"{{{{{k}}}}}", repr(v))

    # Optional: fail if some placeholders remain
    if "{{" in template and "}}" in template:
        # If you want strict behavior, uncomment:
        # raise SystemExit("Some placeholders were not replaced. Check template vs properties keys.")
        pass

    out_path.write_text(template, encoding="utf-8")
    print(f"Generated {out_path} from {prop_file}")

if __name__ == "__main__":
    main()

TOKEN_PATTERN = re.compile(r"@([A-Za-z_][A-Za-z0-9_]*)@")

$WIN_SERVER = ((Get-Content .ci/server-map.env | ? { $_ -match "^$([regex]::Escape($env:SERVER_SLUG))=" } | select -First 1) -split "=",2)[1].Trim()

$dirs = @(
  "D:\Apps\ETL\logs",
  "D:\Apps\ETL\input",
  "D:\Apps\ETL\output"
)

Invoke-Command -Session $session -ScriptBlock {
    param([string[]]$paths)

    foreach ($p in $paths) {
        New-Item -ItemType Directory -Path $p -Force -ErrorAction Stop | Out-Null
    }
    "All directories ensured."
} -ArgumentList (,$dirs)

<#
.SYNOPSIS
  Copies a ZIP from GitLab runner to a remote Windows server and extracts it into
  D:\Groups\PMS\DataInterface, overwriting only files present in the ZIP (no deletes).

.REQUIREMENTS
  - WinRM enabled for New-PSSession connectivity
  - GitLab CI variables set (see below)
  - PowerShell supports Copy-Item -ToSession (Windows PowerShell 5.1+ / PowerShell 7+)

.RECOMMENDED GITLAB CI/CD VARIABLES
  SERVER_SLUG   : key used to lookup server from .ci/server-map.env (optional if WIN_SERVER already set)
  WIN_SERVER    : (optional) server hostname/ip, if not using server-map.env
  WIN_USER      : username for remote server
  WIN_PASS      : password for remote server (masked/protected)
  WIN_DOMAIN    : optional (if needed) e.g. MYDOMAIN. If empty, user is treated as local/UPN.
  DEPLOY_ZIP    : optional relative path to zip in repo, default dist\data-interface.zip
  REMOTE_WORKDIR: optional, default D:\Groups\PMS\DataInterface
#>

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"

function Get-ServerFromMapFile {
    param(
        [Parameter(Mandatory=$true)][string]$MapFile,
        [Parameter(Mandatory=$true)][string]$ServerSlug
    )

    if (-not (Test-Path -LiteralPath $MapFile)) {
        throw "Server map file not found: $MapFile"
    }

    # Read file, ignore comments/blank lines, find "^SLUG="
    $pattern = "^{0}=" -f [regex]::Escape($ServerSlug)
    $line = Get-Content -LiteralPath $MapFile |
        Where-Object {
            $_ -and $_.Trim() -ne "" -and -not $_.Trim().StartsWith("#") -and $_ -match $pattern
        } |
        Select-Object -First 1

    if (-not $line) {
        throw "SERVER_SLUG '$ServerSlug' not found in $MapFile"
    }

    # Split only on first "="
    return (($line -split "=", 2)[1]).Trim().Trim('"')
}

try {
    Write-Host "=== Deploy started ==="

    # --------- Inputs (from GitLab CI variables / defaults) ----------
    $repoDir = $env:CI_PROJECT_DIR
    if (-not $repoDir) { $repoDir = (Get-Location).Path }

    $remoteWorkDir = if ($env:REMOTE_WORKDIR) { $env:REMOTE_WORKDIR } else { "D:\Groups\PMS\DataInterface" }

    $zipRelative = if ($env:DEPLOY_ZIP) { $env:DEPLOY_ZIP } else { "dist\data-interface.zip" }
    $localZip = Join-Path $repoDir $zipRelative

    if (-not (Test-Path -LiteralPath $localZip)) {
        throw "Local ZIP not found: $localZip"
    }

    # Resolve WIN_SERVER either directly or via server-map.env
    $winServer = $env:WIN_SERVER
    if (-not $winServer -or $winServer.Trim() -eq "") {
        if (-not $env:SERVER_SLUG) {
            throw "WIN_SERVER is empty and SERVER_SLUG is not set. Set either WIN_SERVER or SERVER_SLUG in GitLab variables."
        }
        $winServer = Get-ServerFromMapFile -MapFile (Join-Path $repoDir ".ci\server-map.env") -ServerSlug $env:SERVER_SLUG
    }

    if (-not $env:WIN_USER) { throw "WIN_USER is not set in GitLab CI/CD Variables." }
    if (-not $env:WIN_PASS) { throw "WIN_PASS is not set in GitLab CI/CD Variables." }

    $userName = if ($env:WIN_DOMAIN -and $env:WIN_DOMAIN.Trim() -ne "") {
        "{0}\{1}" -f $env:WIN_DOMAIN.Trim(), $env:WIN_USER.Trim()
    } else {
        $env:WIN_USER.Trim()
    }

    Write-Host "Target server     : $winServer"
    Write-Host "Remote work dir   : $remoteWorkDir"
    Write-Host "Local ZIP         : $localZip"
    Write-Host "ZIP name on server: data-interface.zip"

    # --------- Create PSSession ----------
    $securePass = ConvertTo-SecureString $env:WIN_PASS -AsPlainText -Force
    $cred = New-Object System.Management.Automation.PSCredential($userName, $securePass)

    Write-Host "Creating remote session..."
    $session = New-PSSession -ComputerName $winServer -Credential $cred

    try {
        # --------- Ensure working dir exists (does not affect existing data) ----------
        Write-Host "Ensuring remote working directory exists..."
        Invoke-Command -Session $session -ScriptBlock {
            param($dir)
            New-Item -ItemType Directory -Path $dir -Force | Out-Null
        } -ArgumentList $remoteWorkDir

        # --------- Copy ZIP to remote ----------
        $remoteZip = Join-Path $remoteWorkDir "data-interface.zip"

        Write-Host "Copying ZIP to remote..."
        Copy-Item -Path $localZip -Destination $remoteZip -ToSession $session -Force

        # --------- Extract ZIP into work dir (merge update only, no deletes) ----------
        Write-Host "Extracting ZIP on remote (merge update only)..."
        Invoke-Command -Session $session -ScriptBlock {
            param($zipPath, $destDir)

            if (-not (Test-Path -LiteralPath $zipPath -PathType Leaf)) {
                throw "Remote ZIP not found: $zipPath"
            }

            New-Item -ItemType Directory -Path $destDir -Force | Out-Null

            # Expand-Archive with -Force overwrites only files present in zip.
            # It does NOT delete extra existing files/folders in destination.
            Expand-Archive -Path $zipPath -DestinationPath $destDir -Force

            "✅ Extracted '$zipPath' into '$destDir' (merged; no deletes)."
        } -ArgumentList $remoteZip, $remoteWorkDir

        # --------- Optional: cleanup ZIP (uncomment if you don't want to keep it) ----------
        # Write-Host "Cleaning up remote ZIP..."
        # Invoke-Command -Session $session -ScriptBlock {
        #     param($zipPath)
        #     Remove-Item -LiteralPath $zipPath -Force
        # } -ArgumentList $remoteZip

        Write-Host "=== Deploy completed successfully ✅ ==="
    }
    finally {
        if ($session) {
            Write-Host "Closing remote session..."
            Remove-PSSession $session
        }
    }
}
catch {
    Write-Error "=== Deploy failed ❌ ==="
    Write-Error $_
    exit 1
}

<#
.SYNOPSIS
  Copies a ZIP from GitLab runner to a remote Windows server and extracts it.
#>

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"

function Get-ServerFromMapFile {
    param(
        [Parameter(Mandatory=$true)][string]$MapFile,
        [Parameter(Mandatory=$true)][string]$ServerSlug
    )

    if (-not (Test-Path -LiteralPath $MapFile)) {
        throw "Server map file not found: $MapFile"
    }

    $pattern = "^{0}=" -f [regex]::Escape($ServerSlug)
    $line = Get-Content -LiteralPath $MapFile |
        Where-Object {
            $_ -and $_.Trim() -ne "" -and -not $_.Trim().StartsWith("#") -and $_ -match $pattern
        } |
        Select-Object -First 1

    if (-not $line) {
        throw "SERVER_SLUG '$ServerSlug' not found in $MapFile"
    }

    return (($line -split "=", 2)[1]).Trim().Trim('"')
}

try {
    Write-Host "=== Deploy started ==="

    # Inputs (from GitLab CI variables / defaults)
    $repoDir = $env:CI_PROJECT_DIR
    if (-not $repoDir) { $repoDir = (Get-Location).Path }

    $remoteWorkDir = if ($env:REMOTE_WORKDIR) { $env:REMOTE_WORKDIR } else { "D:\Groups\PMS\DataInterface" }

    $zipRelative = if ($env:DEPLOY_ZIP) { $env:DEPLOY_ZIP } else { "dist\data-interface.zip" }
    $localZip = Join-Path $repoDir $zipRelative

    if (-not (Test-Path -LiteralPath $localZip)) {
        throw "Local ZIP not found: $localZip"
    }

    # Resolve WIN_SERVER either directly or via server-map.env
    $winServer = $env:WIN_SERVER
    if (-not $winServer -or $winServer.Trim() -eq "") {
        if (-not $env:SERVER_SLUG) {
            throw "WIN_SERVER is empty and SERVER_SLUG is not set."
        }
        $winServer = Get-ServerFromMapFile -MapFile (Join-Path $repoDir ".ci\server-map.env") -ServerSlug $env:SERVER_SLUG
    }

    if (-not $env:WIN_USER) { throw "WIN_USER is not set." }
    if (-not $env:WIN_PASS) { throw "WIN_PASS is not set." }

    $userName = if ($env:WIN_DOMAIN -and $env:WIN_DOMAIN.Trim() -ne "") {
        "{0}\{1}" -f $env:WIN_DOMAIN.Trim(), $env:WIN_USER.Trim()
    } else {
        $env:WIN_USER.Trim()
    }

    Write-Host "Target server     : $winServer"
    Write-Host "Remote work dir   : $remoteWorkDir"
    Write-Host "Local ZIP         : $localZip"

    # Create PSSession
    $securePass = ConvertTo-SecureString $env:WIN_PASS -AsPlainText -Force
    $cred = New-Object System.Management.Automation.PSCredential($userName, $securePass)

    Write-Host "Creating remote session..."
    $session = New-PSSession -ComputerName $winServer -Credential $cred

    try {
        # Ensure working dir exists
        Write-Host "Ensuring remote directory exists..."
        Invoke-Command -Session $session -ScriptBlock {
            param($dir)
            New-Item -ItemType Directory -Path $dir -Force | Out-Null
        } -ArgumentList $remoteWorkDir

        # Copy ZIP to remote
        $remoteZip = Join-Path $remoteWorkDir "data-interface.zip"
        Write-Host "Copying ZIP to remote..."
        Copy-Item -Path $localZip -Destination $remoteZip -ToSession $session -Force

        # Extract ZIP (merge update only - no deletes)
        Write-Host "Extracting ZIP on remote..."
        Invoke-Command -Session $session -ScriptBlock {
            param($zipPath, $destDir)

            if (-not (Test-Path -LiteralPath $zipPath -PathType Leaf)) {
                throw "Remote ZIP not found: $zipPath"
            }

            New-Item -ItemType Directory -Path $destDir -Force | Out-Null
            Expand-Archive -Path $zipPath -DestinationPath $destDir -Force

            Write-Host "Extracted '$zipPath' into '$destDir' (merged; no deletes)."
        } -ArgumentList $remoteZip, $remoteWorkDir

        # Cleanup remote ZIP (optional)
        Write-Host "Cleaning up remote ZIP..."
        Invoke-Command -Session $session -ScriptBlock {
            param($zipPath)
            Remove-Item -LiteralPath $zipPath -Force
        } -ArgumentList $remoteZip

        W
        
        ```rite-Host "=== Deploy completed successfully ==="
    }
    finally {
        if ($session) {
            Remove-PSSession $session
        }
    }
}
catch {
    Write-Error "=== Deploy failed ==="
    Write-Error $_.Exception.Message
    exit 1
}

<#
.SYNOPSIS
  Copies a ZIP from GitLab runner to a remote Windows server and extracts it.
#>

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"

function Get-ServerFromMapFile {
    param(
        [Parameter(Mandatory=$true)][string]$MapFile,
        [Parameter(Mandatory=$true)][string]$ServerSlug
    )

    if (-not (Test-Path -LiteralPath $MapFile)) {
        throw "Server map file not found: $MapFile"
    }

    $pattern = "^{0}=" -f [regex]::Escape($ServerSlug)
    $line = Get-Content -LiteralPath $MapFile |
        Where-Object {
            $_ -and $_.Trim() -ne "" -and -not $_.Trim().StartsWith("#") -and $_ -match $pattern
        } |
        Select-Object -First 1

    if (-not $line) {
        throw "SERVER_SLUG '$ServerSlug' not found in $MapFile"
    }

    return (($line -split "=", 2)[1]).Trim().Trim('"')
}

try {
    Write-Host "=== Deploy started ==="

    # Inputs (from GitLab CI variables / defaults)
    $repoDir = $env:CI_PROJECT_DIR
    if (-not $repoDir) { $repoDir = (Get-Location).Path }

    $remoteWorkDir = if ($env:REMOTE_WORKDIR) { $env:REMOTE_WORKDIR } else { "D:\Groups\PMS\DataInterface" }

    $zipRelative = if ($env:DEPLOY_ZIP) { $env:DEPLOY_ZIP } else { "dist\data-interface.zip" }
    $localZip = Join-Path $repoDir $zipRelative

    if (-not (Test-Path -LiteralPath $localZip)) {
        throw "Local ZIP not found: $localZip"
    }

    # Resolve WIN_SERVER either directly or via server-map.env
    $winServer = $env:WIN_SERVER
    if (-not $winServer -or $winServer.Trim() -eq "") {
        if (-not $env:SERVER_SLUG) {
            throw "WIN_SERVER is empty and SERVER_SLUG is not set."
        }
        $winServer = Get-ServerFromMapFile -MapFile (Join-Path $repoDir ".ci\server-map.env") -ServerSlug $env:SERVER_SLUG
    }

    if (-not $env:WIN_USER) { throw "WIN_USER is not set." }
    if (-not $env:WIN_PASS) { throw "WIN_PASS is not set." }

    $userName = if ($env:WIN_DOMAIN -and $env:WIN_DOMAIN.Trim() -ne "") {
        "{0}\{1}" -f $env:WIN_DOMAIN.Trim(), $env:WIN_USER.Trim()
    } else {
        $env:WIN_USER.Trim()
    }

    Write-Host "Target server     : $winServer"
    Write-Host "Remote work dir   : $remoteWorkDir"
    Write-Host "Local ZIP         : $localZip"

    # Create PSSession
    $securePass = ConvertTo-SecureString $env:WIN_PASS -AsPlainText -Force
    $cred = New-Object System.Management.Automation.PSCredential($userName, $securePass)

    Write-Host "Creating remote session..."
    $session = New-PSSession -ComputerName $winServer -Credential $cred

    try {
        # Ensure working dir exists
        Write-Host "Ensuring remote directory exists..."
        Invoke-Command -Session $session -ScriptBlock {
            param($dir)
            New-Item -ItemType Directory -Path $dir -Force | Out-Null
        } -ArgumentList $remoteWorkDir

        # Copy ZIP to remote
        $remoteZip = Join-Path $remoteWorkDir "data-interface.zip"
        Write-Host "Copying ZIP to remote..."
        Copy-Item -Path $localZip -Destination $remoteZip -ToSession $session -Force

        # Extract ZIP (merge update only - no deletes)
        Write-Host "Extracting ZIP on remote..."
        Invoke-Command -Session $session -ScriptBlock {
            param($zipPath, $destDir)

            if (-not (Test-Path -LiteralPath $zipPath -PathType Leaf)) {
                throw "Remote ZIP not found: $zipPath"
            }

            New-Item -ItemType Directory -Path $destDir -Force | Out-Null
            Expand-Archive -Path $zipPath -DestinationPath $destDir -Force

            Write-Host "Extracted '$zipPath' into '$destDir' (merged; no deletes)."
        } -ArgumentList $remoteZip, $remoteWorkDir

        # Cleanup remote ZIP (optional)
        Write-Host "Cleaning up remote ZIP..."
        Invoke-Command -Session $session -ScriptBlock {
            param($zipPath)
            Remove-Item -LiteralPath $zipPath -Force
        } -ArgumentList $remoteZip

        W
        
        ```rite-Host "=== Deploy completed successfully ==="
    }
    finally {
        if ($session) {
            Remove-PSSession $session
        }
    }
}
catch {
    Write-Error "=== Deploy failed ==="
    Write-Error $_.Exception.Message
    exit 1
}

$outputsDir = Join-Path $destPath "outputs"

if (Test-Path $outputsDir) {
    Get-ChildItem $outputsDir -Force | Move-Item -Destination $destPath -Force
    Remove-Item $outputsDir -Recurse -Force
}

Expand-Archive -Path $zipPath -DestinationPath $destPath -Force

$outputsDir = Join-Path $destPath "outputs"

if (Test-Path $outputsDir) {
    Get-ChildItem $outputsDir -Force | ForEach-Object {
        $target = Join-Path $destPath $_.Name

        if (Test-Path $target) {
            Remove-Item $target -Recurse -Force
        }

        Move-Item $_.FullName $destPath
    }

    Remove-Item $outputsDir -Recurse -Force
}

i got an gcp data analyst interview at Deloitte. I want to explain my current project at my current company Accenture.

the project is for developing and managing an ETL pipeline for barclays Mortage data domain process vast amounts of loan, customer, and market data to help the bank assess risk, predict market trends, and streamline the lending experience.

these are the below points i have added into my resume under Experience section

Data Engineer at Accenture

• Engineered BigQuery data warehouse solutions on GCP with optimized partitioning and clustering strategies, achieving 60% query performance improvement and 35% cost reduction.
• Deployed event-driven pipelines using GCP Cloud Run, EventArc, and Kafka for real-time business workflows, improving system decoupling and scalability.
• Orchestrated data pipelines via Airflow to deliver curated data to Elasticsearch, MySQL, PostgreSQL, and Kafka, enabling real-time analytics and cross-service consumption.

Aslo include/show experience in also Dataproc, BigQuery.

Now for my interview. answer the question.
Explain you current project ETL pipeline and what is your role in it.