Data Roles and Core Concepts

In any organization that works with data, different individuals have different responsibilities. The DP-900 exam expects you to understand the three primary data roles: data engineer, data analyst, and data scientist. These roles are not just job titles; they represent distinct sets of tasks, tools, and objectives. The exam will test you on which role performs which activities and which Azure services they commonly use.

In a typical data project, the flow is:

The exam often presents scenarios describing a task and asks you to identify which role is responsible. For example: "Who would design a pipeline to copy data from Azure Blob Storage to Azure SQL Database?" Answer: Data engineer.

Data can be classified by its structure. The DP-900 exam tests your ability to distinguish between these three types and identify appropriate storage solutions.

Another core concept is the difference between Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP). The exam expects you to understand the characteristics and appropriate use cases for each.

OLTP (Transactional Processing) - Designed for high-volume, low-latency transactions (e.g., order processing, banking transactions). - Optimized for INSERT, UPDATE, DELETE operations with high concurrency. - Uses normalized schemas to reduce redundancy and ensure data integrity (ACID properties). - Azure services: Azure SQL Database, Azure SQL Managed Instance, Azure Database for MySQL/PostgreSQL.

OLAP (Analytical Processing) - Designed for complex queries and aggregations over large historical datasets (e.g., BI reports, data mining). - Optimized for read-heavy workloads with large scans and aggregations. - Uses denormalized schemas (star or snowflake) to improve query performance. - Azure services: Azure Synapse SQL pool (dedicated SQL pool), Azure Analysis Services, Power BI.

| Aspect | OLTP | OLAP | |--------|------|------| | Purpose | Handle day-to-day transactions | Support business intelligence and analytics | | Data updates | Frequent, small writes | Bulk loads, periodic refreshes | | Query type | Simple, point queries (e.g., get order by ID) | Complex aggregations (e.g., total sales by region) | | Schema | Highly normalized (3NF) | Denormalized (star/snowflake) | | Response time | Milliseconds to seconds | Seconds to minutes | | Historical data | Limited (recent data) | Extensive (years of data) |

Data can be processed in two primary modes:

The exam may ask you to recommend a processing approach based on latency requirements.

Understanding how data is stored in Azure is critical. Key concepts include:

Azure Storage accounts offer several redundancy options:

The exam may ask which redundancy option provides the highest durability or availability.

While DP-900 is conceptual, you may see Azure CLI or PowerShell commands in exam scenarios. For example:

az storage account create --name mystorageaccount --resource-group myResourceGroup --location eastus --sku Standard_LRS

az storage blob upload --account-name mystorageaccount --container-name mycontainer --name myfile.txt --file /path/to/myfile.txt

SELECT COUNT(*) FROM Sales WHERE OrderDate > '2023-01-01';

Common wrong answers on DP-900 include:

Scenario 1: E-commerce Company Building a Customer 360 View

A large e-commerce company wants a unified view of customer interactions across web, mobile, and in-store. The data engineer builds Azure Data Factory pipelines to ingest clickstream logs (semi-structured JSON), transaction records (structured SQL), and customer service transcripts (unstructured text) into Azure Data Lake Storage Gen2. They then use Azure Databricks to clean and join the data, outputting structured tables to Azure Synapse SQL pool. The data analyst connects Power BI to Synapse, creates measures like 'Customer Lifetime Value', and publishes a dashboard for marketing. The data scientist uses Azure Machine Learning to build a churn prediction model using the same curated data. Common misconfiguration: if the data engineer fails to partition the data by date, queries become slow and expensive. In production, the pipeline processes 5 TB daily with a 99.9% SLA.

Scenario 2: Healthcare Provider Implementing Real-Time Patient Monitoring

A hospital network streams vital signs from IoT devices to Azure Event Hubs. A data engineer sets up Azure Stream Analytics to filter abnormal readings and write alerts to Azure SQL Database (OLTP) for immediate action. The same Stream Analytics job aggregates data into Azure Blob Storage (Parquet format) for historical analysis. The data scientist uses Azure Databricks to train a sepsis prediction model on the historical data. The data analyst creates a Power BI dashboard showing real-time bed occupancy and alert trends. Key performance consideration: Stream Analytics must handle 10,000 events per second with less than 5-second latency. A common mistake is using batch processing, which would delay critical alerts.

Scenario 3: Financial Services Firm Migrating Data Warehouse

A bank migrates its on-premises SQL Server data warehouse to Azure Synapse Analytics. The data engineer uses Azure Data Factory to copy historical data (10 years, 50 TB) using PolyBase for fast loading. They choose a dedicated SQL pool with 100 DWU (Data Warehouse Units) initially, scaling to 500 DWU during nightly ETL. The data analyst recreates existing SSRS reports in Power BI, using DirectQuery for live access. The data scientist uses the Synapse SQL pool as a source for Azure Machine Learning to detect fraudulent transactions. Common pitfall: not using appropriate distribution keys (e.g., hash distribution on CustomerID) leads to data skew and poor query performance. Monitoring with DMVs like sys.dm_pdw_exec_requests is essential.