DP-203 Design and implement data storage — All Questions With Answers

Refer to the exhibit.

```json
{
  "data": [
    {
      "name": "order_data",
      "path": "orders/*.parquet",
      "partitionBy": ["year", "month", "day"],
      "format": "parquet",
      "options": {
        "compression": "snappy"
      }
    }
  ],
  "source": {
    "provider": "AzureDataLakeStorage",
    "connectionString": "DefaultEndpointsProtocol=https;AccountName=storagedatalake;AccountKey=...;EndpointSuffix=core.windows.net",
    "container": "data"
  },
  "sink": {
    "provider": "AzureSynapseAnalytics",
    "table": "dbo.orders",
    "staging": {
      "linkedServiceName": "AzureDataLakeStorage",
      "folderPath": "staging"
    }
  },
  "copyBehavior": "MergeFiles",
  "faultTolerance": {
    "skipIncompatibleFiles": true,
    "skipIncompatibleRows": true
  }
}
```

Refer to the exhibit.

```sql
CREATE EXTERNAL DATA SOURCE MyDataSource
WITH (
    LOCATION = 'abfss://data@storagedatalake.dfs.core.windows.net',
    TYPE = HADOOP,
    CREDENTIAL = MyCredential
);

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

CREATE EXTERNAL TABLE dbo.Orders (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2)
)
WITH (
    LOCATION = '/orders/',
    DATA_SOURCE = MyDataSource,
    FILE_FORMAT = MyFileFormat
);
```

Refer to the exhibit.

az storage blob show \
  --account-name exampledatalake \
  --container-name raw \
  --name sensor/2023/01/01/data.parquet \
  --query "properties.blobTier"

Output: "Cool"

Refer to the exhibit.

CREATE EXTERNAL DATA SOURCE myDataSource
WITH (
  LOCATION = 'abfss://container@storageaccount.dfs.core.windows.net',
  CREDENTIAL = myCredential
);

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

CREATE EXTERNAL TABLE myExternalTable
(
  ProductID int,
  ProductName varchar(100),
  Price decimal(10,2)
)
WITH (
  LOCATION = 'sales/products/',
  DATA_SOURCE = myDataSource,
  FILE_FORMAT = myFileFormat
);

Refer to the exhibit.

{
  "RoleName": "Storage Blob Data Contributor",
  "Type": "BuiltInRole",
  "Description": "Allows for read, write, and delete access to Azure Storage containers and blobs.",
  "Actions": [
    "Microsoft.Storage/storageAccounts/blobServices/containers/read",
    "Microsoft.Storage/storageAccounts/blobServices/containers/write",
    "Microsoft.Storage/storageAccounts/blobServices/containers/delete"
  ],
  "NotActions": [],
  "DataActions": [
    "Microsoft.Storage/storageAccounts/blobServices/containers/blobs/read",
    "Microsoft.Storage/storageAccounts/blobServices/containers/blobs/write",
    "Microsoft.Storage/storageAccounts/blobServices/containers/blobs/delete"
  ],
  "NotDataActions": [],
  "AssignableScopes": ["/subscriptions/..."]
}

Refer to the exhibit.

SELECT 
  COUNT(DISTINCT user_id) AS unique_users,
  COUNT(DISTINCT session_id) AS unique_sessions
FROM visits
WHERE date BETWEEN '2024-01-01' AND '2024-01-31';

Refer to the exhibit.

{
  "policyRule": {
    "if": {
      "field": "type",
      "equals": "Microsoft.Storage/storageAccounts"
    },
    "then": {
      "effect": "deny",
      "details": {
        "field": "Microsoft.Storage/storageAccounts/networkAcls.defaultAction",
        "equals": "Allow"
      }
    }
  }
}

Refer to the exhibit.

CREATE EXTERNAL DATA SOURCE MyDataSource
WITH (
  LOCATION = 'abfss://container@storageaccount.dfs.core.windows.net',
  TYPE = HADOOP,
  CREDENTIAL = MyCredential
);

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

CREATE EXTERNAL TABLE dbo.Sales
(
  SaleID INT,
  ProductID INT,
  Quantity INT,
  SaleDate DATE
)
WITH (
  LOCATION = '/sales/',
  DATA_SOURCE = MyDataSource,
  FILE_FORMAT = ParquetFormat
);

Refer to the exhibit.

{
  "properties": {
    "dataLakeStorageAccountDetails": [
      {
        "accountUrl": "https://mystorageaccount.dfs.core.windows.net"
      }
    ],
    "defaultDataLakeStorage": {
      "accountUrl": "https://mystorageaccount.dfs.core.windows.net",
      "filesystem": "synapseworkspace"
    },
    "sqlAdministratorLogin": "adminuser",
    "sqlAdministratorLoginPassword": "",
    "managedResourceGroupName": "managedRG",
    "purviewConfiguration": {
      "purviewResourceId": "/subscriptions/sub-id/resourceGroups/rg/providers/Microsoft.Purview/accounts/purview-account"
    },
    "encryption": {
      "cmk": {
        "key": {
          "name": "cmk-key",
          "keyVaultUrl": "https://kv.vault.azure.net/"
        }
      }
    }
  }
}

Refer to the exhibit.

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Synapse/workspaces",
      "apiVersion": "2021-06-01",
      "name": "myworkspace",
      "location": "eastus",
      "properties": {
        "defaultDataLakeStorage": {
          "accountUrl": "https://mystorageaccount.dfs.core.windows.net",
          "filesystem": "synapse"
        },
        "sqlAdministratorLogin": "admin",
        "sqlAdministratorLoginPassword": "P@ssw0rd123!",
        "managedVirtualNetwork": "default"
      },
      "identity": {
        "type": "SystemAssigned"
      }
    }
  ]
}

Refer to the exhibit.

```json
{
  "type": "Microsoft.Storage/storageAccounts/blobServices/containers/immutabilityPolicies",
  "apiVersion": "2021-09-01",
  "name": "audit-container/policy",
  "properties": {
    "immutabilityPeriodSinceCreationInDays": 2555,
    "allowProtectedAppendWrites": true
  }
}
```

Refer to the exhibit.

```
CREATE EXTERNAL DATA SOURCE SalesData
WITH (
    LOCATION = 'https://datalakegen2.dfs.core.windows.net/sales',
    CREDENTIAL = StorageCred
);

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

CREATE EXTERNAL TABLE dbo.Sales (
    SaleID INT,
    ProductID INT,
    SaleDate DATE,
    Amount DECIMAL(10,2)
)
WITH (
    LOCATION = '/year=2024/',
    DATA_SOURCE = SalesData,
    FILE_FORMAT = ParquetFormat
);
```

{
  "name": "CustomerOrders",
  "properties": {
    "ResourceType": "Microsoft.DocumentDB/databaseAccounts/sqlDatabases/containers",
    "Options": {
      "throughput": 400
    },
    "Resource": {
      "id": "CustomerContainer",
      "partitionKey": {
        "paths": ["/customerId"],
        "kind": "Hash"
      },
      "indexingPolicy": {
        "indexingMode": "consistent",
        "automatic": true,
        "includedPaths": [
          {
            "path": "/*"
          }
        ],
        "excludedPaths": [
          {
            "path": "/\"_etag\"/?"
          }
        ]
      },
      "uniqueKeyPolicy": {
        "uniqueKeys": [
          {
            "paths": ["/orderId"]
          }
        ]
      }
    }
  }
}

Refer to the exhibit.
{
  "dataLakeStorage": {
    "type": "AzureBlobFS",
    "linkedServiceName": "LS_ADLSGen2",
    "parameters": {
      "folderPath": {
        "value": "@dataset().folderPath",
        "type": "Expression"
      }
    },
    "typeProperties": {
      "folderPath": {
        "value": "@{dataset().folderPath}",
        "type": "Expression"
      },
      "fileName": "sales.parquet",
      "format": {
        "type": "ParquetFormat"
      }
    }
  },
  "parameters": {
    "folderPath": {
      "type": "string"
    }
  }
}