📦 Tutorial 3: Data Lake Setup and Configuration¶

$namingStandards = @"
# Data Lake Naming Standards

## File Naming
- **Format**: `{source}_{entity}_{date}_{version}.{format}`
- **Example**: `salesforce_customers_20240115_v1.parquet`

## Folder Naming
- **Partitions**: Use Hive-style partitioning
  - Format: `column=value`
  - Example: `year=2024/month=01/day=15`

- **Layers**: Use singular lowercase names
  - Bronze: `raw/`
  - Silver: `curated/`
  - Gold: `enriched/`

## Container Naming
- **Pattern**: `{purpose}-{environment}`
- **Examples**:
  - `synapse-data` (default workspace data)
  - `external-sources` (external data sources)
  - `archive-historical` (long-term retention)
"@

$namingStandards | Out-File "data-lake-naming-standards.md" -Encoding UTF8
Write-Host "✅ Naming standards documented" -ForegroundColor Green

# Get storage account key for container operations
$storageKey = az storage account keys list \
  --account-name $naming.StorageAccount \
  --resource-group $naming.ResourceGroupName \
  --query "[0].value" \
  --output tsv

# Note: synapse-data container already exists from Tutorial 2

# Create external sources container
az storage fs create \
  --name "external-sources" \
  --account-name $naming.StorageAccount \
  --account-key $storageKey

Write-Host "✅ Container created: external-sources" -ForegroundColor Green

# Create archive container with Cool access tier
az storage fs create \
  --name "archive-historical" \
  --account-name $naming.StorageAccount \
  --account-key $storageKey

Write-Host "✅ Container created: archive-historical" -ForegroundColor Green

# List all containers
Write-Host "`n📦 Storage Containers:" -ForegroundColor Cyan
az storage fs list \
  --account-name $naming.StorageAccount \
  --account-key $storageKey \
  --output table

# Function to create directory structure
function New-DataLakeDirectory {
    param(
        [string]$Container,
        [string]$Path,
        [string]$StorageAccount,
        [string]$StorageKey
    )

    az storage fs directory create \
        --file-system $Container \
        --name $Path \
        --account-name $StorageAccount \
        --account-key $StorageKey \
        --output none
}

Write-Host "🏗️ Creating data lake folder structure..." -ForegroundColor Cyan

# Create layers in synapse-data container
$layers = @('raw', 'curated', 'enriched', 'staging', 'archive')
foreach ($layer in $layers) {
    New-DataLakeDirectory -Container "synapse-data" -Path $layer `
        -StorageAccount $naming.StorageAccount -StorageKey $storageKey
    Write-Host "  ✅ Created: /$layer" -ForegroundColor Green
}

# Create entity folders in each layer (except staging and archive)
$entities = @('sales', 'customers', 'products', 'transactions')
foreach ($entity in $entities) {
    # Raw layer
    New-DataLakeDirectory -Container "synapse-data" -Path "raw/$entity" `
        -StorageAccount $naming.StorageAccount -StorageKey $storageKey

    # Curated layer
    New-DataLakeDirectory -Container "synapse-data" -Path "curated/$entity" `
        -StorageAccount $naming.StorageAccount -StorageKey $storageKey

    Write-Host "  ✅ Created entity folders: $entity" -ForegroundColor Green
}

# Create gold layer aggregates
$aggregates = @('customer_360', 'sales_metrics', 'product_analytics')
foreach ($aggregate in $aggregates) {
    New-DataLakeDirectory -Container "synapse-data" -Path "enriched/$aggregate" `
        -StorageAccount $naming.StorageAccount -StorageKey $storageKey
    Write-Host "  ✅ Created aggregate folder: $aggregate" -ForegroundColor Green
}

Write-Host "`n✅ Data lake folder structure created successfully" -ForegroundColor Green

# List directory structure
Write-Host "`n📂 Data Lake Directory Structure:" -ForegroundColor Cyan

az storage fs directory list \
  --file-system "synapse-data" \
  --account-name $naming.StorageAccount \
  --account-key $storageKey \
  --output table | Select-Object -First 20

Write-Host "`n💡 Tip: Explore full structure in Azure Storage Explorer or Synapse Studio" -ForegroundColor Yellow

# Create linked service JSON definition
$linkedServiceDef = @"
{
  "name": "AzureDataLakeStorage_Default",
  "properties": {
    "type": "AzureBlobFS",
    "typeProperties": {
      "url": "https://$($naming.StorageAccount).dfs.core.windows.net"
    },
    "connectVia": {
      "referenceName": "AutoResolveIntegrationRuntime",
      "type": "IntegrationRuntimeReference"
    },
    "annotations": []
  }
}
"@

$linkedServiceDef | Out-File "linkedservice-adls.json" -Encoding UTF8

# Create linked service using Synapse CLI
az synapse linked-service create \
  --workspace-name $naming.SynapseWorkspace \
  --name "AzureDataLakeStorage_Default" \
  --file linkedservice-adls.json

Write-Host "✅ ADLS Gen2 linked service created" -ForegroundColor Green

# First, create a Key Vault if not exists
$keyVaultName = $naming.KeyVault

# Check if Key Vault exists
$kvExists = az keyvault show --name $keyVaultName --resource-group $naming.ResourceGroupName 2>$null

if (-not $kvExists) {
    # Create Key Vault
    az keyvault create \
      --name $keyVaultName \
      --resource-group $naming.ResourceGroupName \
      --location $naming.Location \
      --enable-soft-delete true \
      --retention-days 90 \
      --tags Project=SynapseTutorial Environment=Learning

    Write-Host "✅ Key Vault created: $keyVaultName" -ForegroundColor Green

    # Grant workspace managed identity access to Key Vault
    $workspaceMI = az synapse workspace show \
      --name $naming.SynapseWorkspace \
      --resource-group $naming.ResourceGroupName \
      --query identity.principalId \
      --output tsv

    az keyvault set-policy \
      --name $keyVaultName \
      --object-id $workspaceMI \
      --secret-permissions get list

    Write-Host "✅ Key Vault access granted to workspace managed identity" -ForegroundColor Green
} else {
    Write-Host "✅ Key Vault already exists: $keyVaultName" -ForegroundColor Green
}

# Create Key Vault linked service definition
$kvLinkedServiceDef = @"
{
  "name": "AzureKeyVault_Credentials",
  "properties": {
    "type": "AzureKeyVault",
    "typeProperties": {
      "baseUrl": "https://$keyVaultName.vault.azure.net/"
    },
    "annotations": []
  }
}
"@

$kvLinkedServiceDef | Out-File "linkedservice-keyvault.json" -Encoding UTF8

# Create linked service
az synapse linked-service create \
  --workspace-name $naming.SynapseWorkspace \
  --name "AzureKeyVault_Credentials" \
  --file linkedservice-keyvault.json

Write-Host "✅ Key Vault linked service created" -ForegroundColor Green

Write-Host "`n🔗 Configured Linked Services:" -ForegroundColor Cyan
az synapse linked-service list \
  --workspace-name $naming.SynapseWorkspace \
  --output table

# Test linked service connectivity
Write-Host "`n🧪 Testing Linked Service Connectivity..." -ForegroundColor Cyan
Write-Host "  Open Synapse Studio → Manage → Linked services" -ForegroundColor Yellow
Write-Host "  Click on each linked service" -ForegroundColor Yellow
Write-Host "  Click 'Test connection' button" -ForegroundColor Yellow
Write-Host "  Verify 'Connection successful' message" -ForegroundColor Yellow

Write-Host "🔐 Configuring Container Access Controls..." -ForegroundColor Cyan

# Get current user and workspace managed identity
$currentUser = az ad signed-in-user show --query id -o tsv
$workspaceMI = az synapse workspace show \
  --name $naming.SynapseWorkspace \
  --resource-group $naming.ResourceGroupName \
  --query identity.principalId \
  --output tsv

# Storage account scope for role assignments
$storageScope = "/subscriptions/$(az account show --query id -o tsv)/resourceGroups/$($naming.ResourceGroupName)/providers/Microsoft.Storage/storageAccounts/$($naming.StorageAccount)"

# Assign Storage Blob Data Contributor to current user (if not already assigned)
az role assignment create \
  --role "Storage Blob Data Contributor" \
  --assignee $currentUser \
  --scope $storageScope \
  2>$null

Write-Host "✅ Current user granted Storage Blob Data Contributor" -ForegroundColor Green

# Verify managed identity has access (should be assigned in Tutorial 2)
$miRoleAssignment = az role assignment list \
  --assignee $workspaceMI \
  --scope $storageScope \
  --role "Storage Blob Data Contributor" \
  --output tsv

if ($miRoleAssignment) {
    Write-Host "✅ Workspace managed identity has Storage Blob Data Contributor" -ForegroundColor Green
} else {
    Write-Host "⚠️ Assigning Storage Blob Data Contributor to workspace managed identity" -ForegroundColor Yellow
    az role assignment create \
      --role "Storage Blob Data Contributor" \
      --assignee $workspaceMI \
      --scope $storageScope
}

# Note: ACL configuration requires storage account key or user delegation SAS

Write-Host "`n🔒 Configuring Folder ACLs..." -ForegroundColor Cyan

# Set ACLs on raw folder (read/write for workspace MI)
az storage fs access set \
  --acl "user::rwx,user:${workspaceMI}:rwx,group::r-x,other::---" \
  --path "raw" \
  --file-system "synapse-data" \
  --account-name $naming.StorageAccount \
  --auth-mode login

Write-Host "✅ ACLs configured for /raw folder" -ForegroundColor Green

# Set ACLs on curated folder
az storage fs access set \
  --acl "user::rwx,user:${workspaceMI}:rwx,group::r-x,other::---" \
  --path "curated" \
  --file-system "synapse-data" \
  --account-name $naming.StorageAccount \
  --auth-mode login

Write-Host "✅ ACLs configured for /curated folder" -ForegroundColor Green

# Set ACLs on enriched folder (read-only for broader access)
az storage fs access set \
  --acl "user::rwx,user:${workspaceMI}:r-x,group::r-x,other::---" \
  --path "enriched" \
  --file-system "synapse-data" \
  --account-name $naming.StorageAccount \
  --auth-mode login

Write-Host "✅ ACLs configured for /enriched folder (read-only)" -ForegroundColor Green

$accessPolicy = @"
# Data Lake Access Control Policy

## RBAC Assignments (Storage Account Level)

| Principal | Role | Scope | Justification |
|-----------|------|-------|---------------|
| Current User | Storage Blob Data Contributor | Storage Account | Development and testing |
| Workspace Managed Identity | Storage Blob Data Contributor | Storage Account | Spark/SQL access to all data |

## ACL Configuration (Folder Level)

### Raw Layer (`/raw`)
- **Purpose**: Ingested raw data storage
- **Access**: Read/Write for workspace MI, Read for group
- **ACL**: `user::rwx,user:{MI}:rwx,group::r-x,other::---`

### Curated Layer (`/curated`)
- **Purpose**: Cleaned and validated data
- **Access**: Read/Write for workspace MI, Read for group
- **ACL**: `user::rwx,user:{MI}:rwx,group::r-x,other::---`

### Enriched Layer (`/enriched`)
- **Purpose**: Business-ready aggregated data
- **Access**: Read-only for workspace MI and group
- **ACL**: `user::rwx,user:{MI}:r-x,group::r-x,other::---`

## Best Practices

1. **Principle of Least Privilege**: Grant minimum required permissions
2. **Use Managed Identity**: Prefer MI over keys/SAS for service authentication
3. **Segregate by Layer**: Different access levels per medallion layer
4. **Audit Regularly**: Review access logs and role assignments quarterly
5. **Document Changes**: Maintain changelog of access modifications
"@

$accessPolicy | Out-File "data-lake-access-policy.md" -Encoding UTF8
Write-Host "✅ Access control policy documented" -ForegroundColor Green

# Create lifecycle management policy
$lifecyclePolicy = @"
{
  "rules": [
    {
      "enabled": true,
      "name": "MoveRawToCool",
      "type": "Lifecycle",
      "definition": {
        "actions": {
          "baseBlob": {
            "tierToCool": {
              "daysAfterModificationGreaterThan": 30
            }
          }
        },
        "filters": {
          "blobTypes": ["blockBlob"],
          "prefixMatch": ["synapse-data/raw/"]
        }
      }
    },
    {
      "enabled": true,
      "name": "ArchiveOldData",
      "type": "Lifecycle",
      "definition": {
        "actions": {
          "baseBlob": {
            "tierToArchive": {
              "daysAfterModificationGreaterThan": 90
            }
          }
        },
        "filters": {
          "blobTypes": ["blockBlob"],
          "prefixMatch": ["synapse-data/archive/"]
        }
      }
    },
    {
      "enabled": true,
      "name": "DeleteStagingData",
      "type": "Lifecycle",
      "definition": {
        "actions": {
          "baseBlob": {
            "delete": {
              "daysAfterModificationGreaterThan": 7
            }
          }
        },
        "filters": {
          "blobTypes": ["blockBlob"],
          "prefixMatch": ["synapse-data/staging/"]
        }
      }
    }
  ]
}
"@

$lifecyclePolicy | Out-File "lifecycle-policy.json" -Encoding UTF8

# Apply lifecycle management policy
az storage account management-policy create \
  --account-name $naming.StorageAccount \
  --resource-group $naming.ResourceGroupName \
  --policy @lifecycle-policy.json

Write-Host "✅ Lifecycle management policy configured" -ForegroundColor Green

# Enable soft delete for blobs (7-day retention)
az storage account blob-service-properties update \
  --account-name $naming.StorageAccount \
  --resource-group $naming.ResourceGroupName \
  --enable-delete-retention true \
  --delete-retention-days 7

# Enable soft delete for containers (7-day retention)
az storage account blob-service-properties update \
  --account-name $naming.StorageAccount \
  --resource-group $naming.ResourceGroupName \
  --enable-container-delete-retention true \
  --container-delete-retention-days 7

Write-Host "✅ Soft delete enabled (7-day retention)" -ForegroundColor Green

Write-Host "`n🔍 Validating Data Lake Configuration..." -ForegroundColor Cyan

# Test 1: List containers
Write-Host "`n📦 Containers:" -ForegroundColor Yellow
az storage fs list \
  --account-name $naming.StorageAccount \
  --auth-mode login \
  --output table

# Test 2: List root directories in synapse-data
Write-Host "`n📂 Root Directories in synapse-data:" -ForegroundColor Yellow
az storage fs directory list \
  --file-system "synapse-data" \
  --account-name $naming.StorageAccount \
  --auth-mode login \
  --output table

# Test 3: Verify ACLs
Write-Host "`n🔒 ACL Configuration for /raw:" -ForegroundColor Yellow
az storage fs access show \
  --path "raw" \
  --file-system "synapse-data" \
  --account-name $naming.StorageAccount \
  --auth-mode login

# Create sample data file
$sampleData = @"
id,name,category,price
1,Product A,Electronics,99.99
2,Product B,Furniture,249.50
3,Product C,Electronics,149.99
"@

$sampleData | Out-File "sample-products.csv" -Encoding UTF8

# Upload to raw layer
az storage fs file upload \
  --file-system "synapse-data" \
  --path "raw/products/sample-products.csv" \
  --source "sample-products.csv" \
  --account-name $naming.StorageAccount \
  --auth-mode login \
  --overwrite

Write-Host "✅ Sample data uploaded to /raw/products/" -ForegroundColor Green

# Verify file exists
az storage fs file show \
  --file-system "synapse-data" \
  --path "raw/products/sample-products.csv" \
  --account-name $naming.StorageAccount \
  --auth-mode login \
  --query "{Name:name, Size:properties.contentLength, LastModified:properties.lastModified}" \
  --output table

Write-Host "`n📊 Storage Account Metrics:" -ForegroundColor Cyan

# Get storage account usage
$storageUsage = az storage account show \
  --name $naming.StorageAccount \
  --resource-group $naming.ResourceGroupName \
  --query "{Name:name, Tier:accessTier, ProvisioningState:provisioningState}" \
  --output json | ConvertFrom-Json

Write-Host "  Account: $($storageUsage.Name)" -ForegroundColor White
Write-Host "  Tier: $($storageUsage.Tier)" -ForegroundColor White
Write-Host "  State: $($storageUsage.ProvisioningState)" -ForegroundColor White

# Get capacity used (requires Azure Monitor)
Write-Host "`n💾 Storage Capacity:" -ForegroundColor Cyan
az monitor metrics list \
  --resource "/subscriptions/$(az account show --query id -o tsv)/resourceGroups/$($naming.ResourceGroupName)/providers/Microsoft.Storage/storageAccounts/$($naming.StorageAccount)" \
  --metric "UsedCapacity" \
  --output table

# Create storage-specific budget alert
az consumption budget create \
  --budget-name "storage-tutorial-budget" \
  --amount 50 \
  --time-grain Monthly \
  --start-date "$(Get-Date -Format 'yyyy-MM-01')" \
  --end-date "$(Get-Date -Year $((Get-Date).Year + 1) -Format 'yyyy-MM-01')" \
  --resource-group-filter $naming.ResourceGroupName

Write-Host "✅ Storage budget alert configured ($50/month)" -ForegroundColor Green

$operationsRunbook = @"
# Data Lake Operations Runbook

## Daily Operations

### Data Ingestion
\`\`\`powershell
# Upload files to raw layer
az storage fs file upload \
  --file-system "synapse-data" \
  --path "raw/{entity}/{filename}" \
  --source "{local-file}" \
  --account-name "$($naming.StorageAccount)" \
  --auth-mode login
\`\`\`

### Data Quality Checks
- Verify file upload completion
- Check file sizes against expected ranges
- Validate file formats and schemas

## Weekly Operations

### Storage Cleanup
\`\`\`powershell
# Remove old staging files (> 7 days)
# Note: Automated via lifecycle policy
\`\`\`

### Access Review
- Review role assignments
- Audit access logs
- Remove unnecessary permissions

## Monthly Operations

### Cost Review
- Analyze storage tier distribution
- Review lifecycle policy effectiveness
- Identify optimization opportunities

### Capacity Planning
- Monitor storage growth trends
- Forecast capacity needs
- Plan for scaling

## Incident Response

### Access Denied Errors
1. Verify RBAC role assignments
2. Check ACL permissions
3. Confirm managed identity configuration
4. Review firewall rules

### Data Loss Prevention
1. Check soft delete enabled
2. Verify backup retention settings
3. Test restore procedures
4. Document recovery steps
"@

$operationsRunbook | Out-File "data-lake-operations-runbook.md" -Encoding UTF8
Write-Host "✅ Operations runbook created" -ForegroundColor Green

$configSummary = @"
# Data Lake Configuration Summary
Generated: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')

## Storage Account
- **Name**: $($naming.StorageAccount)
- **Resource Group**: $($naming.ResourceGroupName)
- **Location**: $($naming.LocationName)
- **Type**: Azure Data Lake Storage Gen2
- **Hierarchical Namespace**: Enabled

## Containers
1. **synapse-data** (Default workspace data)
   - Tier: Hot
   - Purpose: Main analytics data lake

2. **external-sources** (External data)
   - Tier: Hot
   - Purpose: Data from external systems

3. **archive-historical** (Historical data)
   - Tier: Cool
   - Purpose: Long-term data retention

## Folder Structure (Medallion Architecture)
- **/raw** - Bronze layer (ingested data)
- **/curated** - Silver layer (cleaned data)
- **/enriched** - Gold layer (business aggregates)
- **/staging** - Temporary processing
- **/archive** - Historical retention

## Linked Services
- AzureDataLakeStorage_Default (Managed Identity)
- AzureKeyVault_Credentials (Managed Identity)

## Access Control
- **RBAC**: Storage Blob Data Contributor (User + Workspace MI)
- **ACLs**: Configured per layer with appropriate permissions
- **Soft Delete**: Enabled (7-day retention)

## Lifecycle Management
- Raw data → Cool tier after 30 days
- Archive data → Archive tier after 90 days
- Staging data → Deleted after 7 days

## Next Steps
- Tutorial 4: Batch data ingestion
- Tutorial 5: Streaming data ingestion
- Tutorial 6: Spark pool configuration
"@

$configSummary | Out-File "data-lake-configuration-summary.md" -Encoding UTF8
Write-Host "✅ Configuration summary saved" -ForegroundColor Green

Write-Host "🔍 Validating Data Lake Configuration..." -ForegroundColor Cyan

$validationPassed = $true

# Check containers exist
$containers = az storage fs list --account-name $naming.StorageAccount --auth-mode login --output json | ConvertFrom-Json
$requiredContainers = @("synapse-data", "external-sources", "archive-historical")
foreach ($container in $requiredContainers) {
    if ($containers.name -contains $container) {
        Write-Host "✅ Container exists: $container" -ForegroundColor Green
    } else {
        Write-Host "❌ Container missing: $container" -ForegroundColor Red
        $validationPassed = $false
    }
}

# Check folders exist
$folders = az storage fs directory list --file-system "synapse-data" --account-name $naming.StorageAccount --auth-mode login --output json | ConvertFrom-Json
$requiredFolders = @("raw", "curated", "enriched", "staging", "archive")
foreach ($folder in $requiredFolders) {
    if ($folders.name -contains $folder) {
        Write-Host "✅ Folder exists: /$folder" -ForegroundColor Green
    } else {
        Write-Host "❌ Folder missing: /$folder" -ForegroundColor Red
        $validationPassed = $false
    }
}

# Check linked services
$linkedServices = az synapse linked-service list --workspace-name $naming.SynapseWorkspace --output json | ConvertFrom-Json
if ($linkedServices.Count -ge 2) {
    Write-Host "✅ Linked services configured ($($linkedServices.Count) services)" -ForegroundColor Green
} else {
    Write-Host "⚠️ Expected at least 2 linked services" -ForegroundColor Yellow
}

# Final result
if ($validationPassed) {
    Write-Host "`n✅ All validations passed! Ready for Tutorial 4." -ForegroundColor Green
} else {
    Write-Host "`n❌ Some validations failed. Please review and fix issues." -ForegroundColor Red
}

# Verify authentication mode
az storage fs directory create \
  --file-system "synapse-data" \
  --name "test-folder" \
  --account-name $naming.StorageAccount \
  --auth-mode login  # Use login instead of account key

# Verify managed identity has Storage Blob Data Contributor role
$workspaceMI = az synapse workspace show --name $naming.SynapseWorkspace --resource-group $naming.ResourceGroupName --query identity.principalId -o tsv
az role assignment create \
  --role "Storage Blob Data Contributor" \
  --assignee $workspaceMI \
  --scope "/subscriptions/$(az account show --query id -o tsv)/resourceGroups/$($naming.ResourceGroupName)/providers/Microsoft.Storage/storageAccounts/$($naming.StorageAccount)"
# Wait 5-10 minutes for propagation

# Verify firewall rules allow Synapse workspace
az synapse workspace firewall-rule list --workspace-name $naming.SynapseWorkspace --resource-group $naming.ResourceGroupName
# Ensure "AllowAllAzureIPs" rule exists (start: 0.0.0.0, end: 0.0.0.0)