Analytics and Data Warehouse Concepts

Analytics is the process of examining data to draw conclusions and support decision-making. In a business context, analytics answers questions like 'What were total sales last quarter?' or 'Which products have the highest return rate?' To answer these questions efficiently, you need a system optimized for read-heavy, complex queries that aggregate large volumes of historical data. This is where a data warehouse comes in. A data warehouse is a centralized repository designed for online analytical processing (OLAP). It stores data from multiple sources, cleaned, transformed, and structured for fast query performance. This contrasts with online transaction processing (OLTP) systems, which are optimized for high-volume, low-latency writes (e.g., order entry, banking transactions). OLTP systems use normalized schemas to minimize redundancy and ensure fast inserts/updates, but they are terrible for analytical queries because those queries would require joining many tables and scanning huge indexes. The exam expects you to know the key differences between OLTP and OLAP.

The most common data warehouse schema is the star schema. It consists of one or more fact tables surrounded by dimension tables. A fact table stores quantitative, measurable data (e.g., sales amount, quantity sold, order count). It usually has a composite key made up of foreign keys referencing dimension tables. Fact tables are often very large (billions of rows) and are the primary target for aggregation queries. Dimension tables store descriptive attributes (e.g., customer name, product category, date, store location). They are smaller and allow you to slice and dice fact data. For example, a sales fact table might have columns: DateKey, ProductKey, CustomerKey, SalesAmount, Quantity. The dimension tables would be DimDate, DimProduct, DimCustomer. A query like 'Total sales by product category for Q1 2023' would join the fact table with DimProduct and DimDate and aggregate. The star schema is denormalized – dimension tables can have redundancy (e.g., product category repeated for each product) – but this is intentional to reduce the number of joins and improve query speed. The exam may ask you to identify which tables are facts and which are dimensions.

Data gets into a data warehouse via an ETL pipeline. Extract: Data is pulled from source systems (databases, APIs, flat files). Transform: Data is cleaned (remove duplicates, handle nulls), validated, aggregated, and reshaped into the star schema. Load: The transformed data is inserted into the warehouse tables. In Azure, you can use Azure Data Factory or Azure Synapse Pipelines to orchestrate ETL. The exam may test your understanding of ETL vs. ELT (Extract, Load, Transform), where ELT loads raw data first and transforms later inside the warehouse (common with modern cloud warehouses like Synapse). ETL is traditional and ensures the warehouse only contains clean data; ELT is faster for initial load but requires transformation compute.

Azure Synapse Analytics is Microsoft's cloud data warehouse solution. It combines big data analytics and data warehousing. The core component is Synapse SQL, which provides two query models: dedicated SQL pools (formerly SQL Data Warehouse) and serverless SQL pools. Dedicated SQL pools provision fixed compute resources (DWU – Data Warehouse Units) and store data on persistent storage. Serverless SQL pools query data directly from files in Azure Data Lake Storage, charging per query. Synapse also integrates with Apache Spark for big data processing and Synapse Pipelines for ETL. On the exam, you should know that Synapse uses a massively parallel processing (MPP) architecture: data is distributed across 60 distributions per compute node, and queries are parallelized. The distribution is controlled by a hash or round-robin distribution key. Choosing the right distribution key is critical for performance. The exam may ask about distribution types: Hash distribution (evenly distributes rows based on a column's hash – good for large fact tables) and Round-robin (random distribution – good for small tables or staging).

PolyBase is a technology in Synapse that allows you to query external data sources (like Azure Blob Storage, Azure Data Lake Storage, or Hadoop) using T-SQL. It enables data virtualization – you don't need to load data into the warehouse to query it. PolyBase uses external tables with schemas defined on top of files (CSV, Parquet, ORC). This is handy for combining on-premises data with cloud data. The exam may test that PolyBase supports read-only queries on external data, and you can use CREATE EXTERNAL TABLE syntax. Note: PolyBase is also available in SQL Server 2016+ and Azure SQL Database (limited).

Modern analytics often involves a data lake – a repository that stores raw data in its native format (structured, semi-structured, unstructured). Azure Data Lake Storage Gen2 is a hierarchical file system on top of Azure Blob Storage. A data lake offers flexibility and low-cost storage but requires significant effort to query directly. A data warehouse provides structured, clean data with fast query performance but is more expensive. A data lakehouse (like Azure Synapse with Spark) merges the two: it stores raw data in a data lake but adds a metadata layer (like Delta Lake) to provide ACID transactions and schema enforcement. The exam expects you to understand the trade-offs: data lake = cheap storage, schema-on-read; data warehouse = fast queries, schema-on-write; data lakehouse = best of both.

In a data warehouse, dimension attributes can change over time (e.g., a customer moves to a new city). Slowly Changing Dimensions (SCD) are techniques to handle these changes. The most common types are: - Type 0: Do nothing – keep original values. - Type 1: Overwrite – no history kept. - Type 2: Add a new row with a new surrogate key and effective dates – preserves full history. - Type 3: Add a new column to store the previous value – limited history. The exam may ask which SCD type to use for a given requirement. For example, if you need to track historical changes, use Type 2.

To speed up queries, data warehouses use partitioning – dividing large tables into smaller segments based on a column (e.g., date). Partitioning allows query engines to skip irrelevant partitions (partition elimination). In Synapse, you can partition tables on the distribution key or a separate partition column. Additionally, columnstore indexes are the default in Synapse – they store data column-wise instead of row-wise, enabling high compression and faster aggregation queries. Rowstore indexes (B-tree) are better for point lookups. The exam may ask about columnstore vs. rowstore: columnstore is for large scans, rowstore for small lookups.

You query a data warehouse using standard T-SQL, but with some limitations (e.g., no UPDATE/DELETE on columnstore indexes without rebuild). Common analytical functions include SUM, COUNT, AVG, GROUP BY, and window functions like ROW_NUMBER(), RANK(). The exam may test your ability to write simple aggregation queries or interpret query results. Also, understand the concept of materialized views – pre-computed and stored query results that improve performance. In Synapse, you can create materialized views that are automatically refreshed.

Data warehouses contain sensitive business data. Security mechanisms include: - Row-level security (RLS): Restrict rows based on user identity. - Column-level security: Restrict access to specific columns. - Dynamic data masking: Obfuscate sensitive data in query results. - Azure Active Directory (AAD) authentication: Use managed identities or service principals. - Firewall rules: Control IP access. The exam may ask which security feature to use for a given scenario (e.g., RLS for a salesperson to see only their region).

Azure Synapse provides Dynamic Management Views (DMVs) to monitor query performance, resource usage, and wait statistics. You can also use Azure Monitor and Synapse Studio to view query plans and identify bottlenecks. Common optimization techniques include:

- Choosing the right distribution key (hash on a column with high cardinality and even distribution). - Using appropriate partitioning (e.g., monthly partitions for date). - Ensuring statistics are up-to-date (UPDATE STATISTICS). - Avoiding small tables without distribution (use round-robin). The exam may test your knowledge of DMVs like sys.dm_pdw_exec_requests to find long-running queries.

A large retail chain with thousands of stores needs to analyze daily sales across all stores. Their OLTP system (SQL Server) handles point-of-sale transactions but struggles with queries like 'Compare sales of winter jackets vs. raincoats by region for the last 3 years.' They build a data warehouse on Azure Synapse using a star schema. The fact table FactSales contains one row per transaction line (grain: product per transaction). Dimensions: DimDate, DimProduct, DimStore, DimCustomer. They use hash distribution on ProductKey to distribute data evenly. They partition by month for easy data management. ETL runs nightly via Azure Data Factory, pulling from the OLTP database, transforming (standardizing product names, calculating discounts), and loading. The warehouse supports dashboards in Power BI that refresh daily. Without the warehouse, the OLTP system would be overloaded by analytical queries, causing transaction slowdowns. Misconfiguration: initially, they used round-robin distribution on the fact table, leading to data skew and poor query performance. Switching to hash distribution on a high-cardinality column (ProductKey) balanced the load and improved query times by 70%.

A health insurance company processes millions of claims daily. They need to detect fraud patterns and analyze claim rejection rates. They use Azure Data Lake Storage Gen2 as a data lake to store raw claim files (JSON/Parquet). Using Synapse serverless SQL pool, they query the data lake directly without loading into a dedicated pool. This is cost-effective because queries are sporadic. They create external tables over the Parquet files and run T-SQL queries to aggregate claim amounts by provider, diagnosis code, and date. For deeper analysis, they use Synapse Spark notebooks to run machine learning models. The challenge is data variety: claim formats change over time. They handle schema evolution using Delta Lake on the data lake, which provides ACID transactions. A common mistake: forgetting to partition the data lake files by date, leading to full scans of years of data. They partition by year/month/day, achieving partition elimination and reducing query cost.

A bank must produce regulatory reports (e.g., Basel III) that require historical data up to 10 years. They use a dedicated SQL pool in Synapse with 100 TB of data. They use Type 2 slowly changing dimensions to track customer address changes. The fact table is partitioned by quarter and distributed by hash on AccountKey. They create materialized views for common aggregates (e.g., daily balance per account) to speed up reports. Security is critical: they implement row-level security so that branch managers see only their branch data. They also use column-level security to hide account numbers from non-privileged users. Performance tuning: they monitor sys.dm_pdw_exec_requests and find queries with high total_elapsed_time and use the query plan to identify missing statistics. They also use result-set caching for repeated queries. A frequent issue is that the ETL pipeline fails due to data type mismatches; they use data flow transformations to cast columns.