DP-900 Describe an analytics workload on Azure — All Questions With Answers

Refer to the exhibit.

```kusto
StormEvents
| where EventType == "Tornado"
| summarize TornadoCount = count() by State
| order by TornadoCount desc
| take 5
```

Refer to the exhibit.

{
  "type": "Microsoft.Synapse/workspaces/sqlPools",
  "apiVersion": "2021-06-01",
  "properties": {
    "collation": "SQL_Latin1_General_CP1_CI_AS",
    "maxSizeBytes": 263882790666240,
    "provisioningState": "Succeeded",
    "status": "Online",
    "restorePointInTime": "2023-01-15T08:00:00Z"
  }
}

Refer to the exhibit.

```json
{
  "type": "Microsoft.Storage/storageAccounts",
  "apiVersion": "2023-01-01",
  "name": "[parameters('storageAccountName')]",
  "kind": "StorageV2",
  "properties": {
    "isHnsEnabled": true
  }
}
```

Refer to the exhibit.

```json
{
  "dataflows": [
    {
      "name": "ProcessSales",
      "source": {
        "type": "AzureSqlDatabase",
        "connection": "SalesDB",
        "query": "SELECT * FROM Sales"
      },
      "sink": {
        "type": "AzureBlobStorage",
        "folderPath": "salesdata",
        "fileType": "Parquet"
      }
    }
  ]
}
```

Refer to the exhibit.

$storageAccount = Get-AzStorageAccount -ResourceGroupName 'rg-analytics' -Name 'salesdatalake'
$ctx = $storageAccount.Context
$files = Get-AzStorageBlob -Container 'raw' -Context $ctx -Prefix 'sales/2023/'
$files | Where-Object {$_.LastModified -gt (Get-Date).AddDays(-7)}

Refer to the exhibit.

{
  "name": "sales-pipeline",
  "properties": {
    "activities": [
      {
        "name": "CopySalesData",
        "type": "Copy",
        "inputs": [{"referenceName": "SalesOnPrem", "type": "DatasetReference"}],
        "outputs": [{"referenceName": "SalesAzureSQL", "type": "DatasetReference"}],
        "typeProperties": {
          "source": {
            "type": "SqlServerSource",
            "sqlReaderQuery": "SELECT * FROM Sales WHERE OrderDate >= '2023-01-01'"
          },
          "sink": {
            "type": "SqlSink",
            "writeBatchSize": 10000
          }
        }
      }
    ]
  }
}

Refer to the exhibit.

{
  "type": "Microsoft.Storage/storageAccounts",
  "apiVersion": "2023-01-01",
  "properties": {
    "isHnsEnabled": true,
    "accessTier": "Hot",
    "supportsHttpsTrafficOnly": true
  }
}

Refer to the exhibit.

Query: 
// KQL query executed in Azure Data Explorer
StormEvents
| where State == "TEXAS"
| summarize Count = count() by EventType
| top 5 by Count

Refer to the exhibit.

{
  "type": "Microsoft.Synapse/workspaces",
  "apiVersion": "2021-06-01-preview",
  "properties": {
    "defaultDataLakeStorage": {
      "accountUrl": "https://datalake123.dfs.core.windows.net",
      "filesystem": "synapse-workspace"
    },
    "sqlAdministratorLogin": "adminuser",
    "sqlAdministratorLoginPassword": "P@ssw0rd123!"
  }
}

Refer to the exhibit.

```json
{
  "type": "Microsoft.StreamAnalytics/streamingjobs",
  "apiVersion": "2020-03-01",
  "properties": {
    "sku": {
      "name": "StandardV2"
    },
    "inputs": [
      {
        "name": "input",
        "properties": {
          "type": "Stream",
          "datasource": {
            "type": "Microsoft.EventHub/EventHub",
            "properties": {
              "eventHubName": "telemetry-events",
              "consumerGroupName": "$Default"
            }
          }
        }
      }
    ],
    "outputs": [
      {
        "name": "output",
        "properties": {
          "datasource": {
            "type": "Microsoft.Sql/Server/Database",
            "properties": {
              "server": "sqlserver123.database.windows.net",
              "database": "sqldb",
              "table": "SensorData"
            }
          }
        }
      }
    ],
    "transformation": {
      "name": "TransformTelemetry",
      "properties": {
        "streamingUnits": 6,
        "query": "SELECT SensorId, AVG(Temperature) AS AvgTemp INTO output FROM input GROUP BY SensorId, TumblingWindow(minute, 5)"
      }
    }
  }
}
```

Refer to the exhibit.

```json
{
  "name": "CopyFromOnPrem",
  "properties": {
    "activities": [
      {
        "name": "CopyData",
        "type": "Copy",
        "inputs": [
          {
            "referenceName": "OnPremSqlServerDataset",
            "type": "DatasetReference"
          }
        ],
        "outputs": [
          {
            "referenceName": "AzureSqlDatabaseDataset",
            "type": "DatasetReference"
          }
        ],
        "typeProperties": {
          "source": {
            "type": "SqlSource",
            "sqlReaderQuery": "SELECT * FROM Sales.Orders WHERE OrderDate > '2025-01-01'"
          },
          "sink": {
            "type": "SqlSink",
            "writeBatchSize": 10000
          }
        }
      }
    ],
    "integrationRuntime": {
      "referenceName": "SelfHostedIR",
      "type": "IntegrationRuntimeReference"
    }
  }
}
```

Refer to the exhibit.

```kusto
let startTime = datetime(2025-01-01T00:00:00Z);
let endTime = datetime(2025-01-02T00:00:00Z);
AzureDiagnostics
| where ResourceType == "AZURESQLDB"
| where TimeGenerated between (startTime .. endTime)
| where OperationName == "QueryThrottled"
| summarize Count = count() by DatabaseName, bin(TimeGenerated, 1h)
| render timechart
```

Refer to the exhibit.

```sql
CREATE EXTERNAL DATA SOURCE MyDataSource WITH (
    LOCATION = 'https://mystorageaccount.dfs.core.windows.net/container',
    CREDENTIAL = MyCredential
);

CREATE EXTERNAL FILE FORMAT MyFileFormat WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

CREATE EXTERNAL TABLE dbo.Sales (
    SaleID INT,
    ProductName VARCHAR(100),
    SaleAmount DECIMAL(10,2),
    SaleDate DATE
)
WITH (
    LOCATION = '/sales/',
    DATA_SOURCE = MyDataSource,
    FILE_FORMAT = MyFileFormat
);
```

Refer to the exhibit.

```json
{
  "name": "CopyDataPipeline",
  "properties": {
    "activities": [
      {
        "name": "CopyFromBlobToSQL",
        "type": "Copy",
        "inputs": [
          {
            "referenceName": "BlobInputDataset",
            "type": "DatasetReference"
          }
        ],
        "outputs": [
          {
            "referenceName": "SQLOutputDataset",
            "type": "DatasetReference"
          }
        ],
        "typeProperties": {
          "source": {
            "type": "DelimitedTextSource",
            "storeSettings": {
              "type": "AzureBlobStorageReadSettings",
              "recursive": true
            }
          },
          "sink": {
            "type": "SqlSink",
            "writeBatchSize": 10000
          }
        }
      }
    ]
  }
}

Refer to the exhibit.

Power BI DAX expression:
```
Total Sales = 
CALCULATE(
    SUM(Sales[Amount]),
    Sales[Channel] = "Online"
)
```

{
  "type": "Microsoft.Storage/storageAccounts/blobServices/containers",
  "apiVersion": "2023-01-01",
  "name": "[concat(parameters('storageAccountName'), '/default/', parameters('containerName'))]",
  "dependsOn": [
    "[resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName'))]"
  ],
  "properties": {
    "publicAccess": "None"
  }
}

{
  "version": "1.0",
  "aggregations": [
    {
      "table": "Sales",
      "measure": "SUM(Amount)",
      "dimensions": ["ProductCategory", "Region", "Date"],
      "aggregationLevel": "Monthly"
    }
  ]
}