Data Analytics Big Data and Modern Data Infrastructure Syllabus

What is a 'data mart'?

A data mart is a subset of a data warehouse focused on a specific business line or department (e.g., sales, finance). It contains a curated slice of data tailored to that group's analytical needs.

Define ETL and its three stages.

ETL stands for Extract, Transform, Load: data is extracted from sources, transformed (cleaned, aggregated, conformed) in a staging area, and then loaded into the target warehouse. Transformation happens before loading.

Define ELT and how it reorders the ETL process.

ELT stands for Extract, Load, Transform: raw data is extracted and loaded first into the target (often a cloud warehouse or lake), and transformation is performed inside the target using its compute power. It suits large volumes and modern cloud platforms.

State two situations that favor ELT over ETL.

ELT is favored when using scalable cloud warehouses/lakes with cheap in-place compute, when handling very large or unstructured volumes, and when you want to preserve raw data for flexible/repeated transformation. ETL suits smaller volumes needing strict pre-load quality/compliance.

What is an idempotent data pipeline and why does it matter?

An idempotent pipeline produces the same result no matter how many times it is run for a given input/time window. It matters because it makes re-runs and failure recovery safe, avoiding duplicate or corrupted data.

What is dimensional modeling, and who popularized it?

Dimensional modeling, popularized by Ralph Kimball, structures data warehouse data into fact tables and dimension tables to make analytical queries intuitive and fast, optimizing for readability and performance over normalization.

Distinguish a fact table from a dimension table.

A fact table stores quantitative, measurable events (measures/metrics like sales amount) plus foreign keys to dimensions and is typically long and narrow. A dimension table stores descriptive context (who, what, where, when) with attributes used for filtering/grouping.

Compare a star schema with a snowflake schema.

A star schema has a central fact table connected directly to denormalized dimension tables (fewer joins, faster, simpler). A snowflake schema normalizes dimensions into sub-dimension tables (less redundancy, more joins, more complex). Star is generally preferred for BI.

What are the three grain types of fact tables?

Transaction fact tables (one row per event, finest grain), periodic snapshot fact tables (state at regular intervals, e.g., daily balances), and accumulating snapshot fact tables (one row per process instance, updated as it progresses through milestones).

What is a Slowly Changing Dimension (SCD), and what do Type 1 and Type 2 do?

An SCD handles changes to dimension attributes over time. Type 1 overwrites the old value (no history). Type 2 adds a new row with versioning (effective dates/flags) to preserve full history.

What defines a 'cloud data platform', and name three major providers' warehouse offerings.

A cloud data platform delivers storage, processing, and analytics as managed, elastically scalable services. Examples: Amazon Redshift (AWS), Google BigQuery (GCP), Azure Synapse Analytics (Azure); Snowflake and Databricks are major cloud-agnostic platforms.

What is the key architectural feature of Snowflake and BigQuery regarding compute and storage?

They separate (decouple) storage from compute, allowing each to scale independently. Compute clusters (e.g., Snowflake virtual warehouses, BigQuery slots) can be sized/paused per workload while data sits in shared, elastic storage — enabling pay-per-use and concurrency.

What is BI (Business Intelligence) architecture, in terms of its typical layers?

A typical BI architecture has a data source layer, an integration/ETL layer, a storage layer (warehouse/mart), and a presentation/analytics layer (dashboards, reports, tools). Data flows from operational sources through transformation into curated stores consumed by BI tools.

Name three widely used BI/visualization tools.

Tableau, Microsoft Power BI, and Looker (others include Qlik Sense, MicroStrategy, and Google Data Studio/Looker Studio).

What is OLAP, and how does it contrast with OLTP?

OLAP (Online Analytical Processing) is optimized for complex analytical queries over large historical datasets (aggregations, multidimensional analysis). OLTP (Online Transaction Processing) is optimized for many small, fast read/write transactions (day-to-day operations, high concurrency, current data).

What is an OLAP cube?

An OLAP cube is a multidimensional data structure that pre-aggregates measures across several dimensions (e.g., product x region x time), enabling fast slicing and dicing of data along those axes.

Define the OLAP operations: roll-up, drill-down, slice, dice, and pivot.

Roll-up aggregates to a higher level (e.g., day to month); drill-down goes to finer detail; slice selects one dimension value to get a subcube; dice selects a subcube on multiple dimension ranges; pivot (rotate) reorients the cube's dimensions for a new view.

Contrast MOLAP, ROLAP, and HOLAP.

MOLAP stores data in a pre-computed multidimensional cube (fast, storage-heavy). ROLAP runs analysis directly on relational tables via SQL (scalable, slower). HOLAP is a hybrid, storing aggregates in a cube and detail in relational tables.

What is self-service analytics, and what is its main benefit?

Self-service analytics lets business users explore data and build their own reports/dashboards with minimal reliance on IT or data teams. Its main benefit is faster, democratized insight and reduced bottlenecks, though it requires governance to ensure consistency.

What is a 'semantic layer' in a self-service BI context?

A semantic layer is a business-friendly abstraction that maps raw data into consistent, defined metrics, dimensions, and names (e.g., 'revenue', 'active user'). It ensures all users get consistent, governed definitions when building their own analyses.

What distinguishes reporting from ad hoc analysis?

Reporting delivers standardized, recurring, predefined views of KPIs (often scheduled/automated) for monitoring. Ad hoc analysis is exploratory, one-off investigation to answer a specific new question, typically flexible and unstructured.

Why automate reporting, and name two common automation mechanisms.

Automating reporting saves time, reduces manual error, and ensures timely delivery. Mechanisms include scheduled report refreshes/email subscriptions in BI tools, and orchestration/scheduling tools (e.g., cron, Airflow) that run pipelines and push outputs automatically.

What framework helps in framing a business problem before analysis?

Start by defining the business question/objective, identifying stakeholders and decisions to be influenced, specifying required metrics and success criteria, checking data availability, and translating the business question into an analytical/statistical question. A common structure is Define, Measure, Analyze, Interpret, Act.

Distinguish a business metric from a business question when framing problems.

A business question is the decision-oriented ask (e.g., 'Why is churn rising?'); a metric is the quantifiable measure used to answer or track it (e.g., monthly churn rate). Good framing translates vague questions into specific, measurable metrics.

What is A/B testing, and what is the purpose of the control and treatment groups?

A/B testing is a randomized controlled experiment comparing two variants. The control group (A) sees the existing version; the treatment group (B) sees the change. Random assignment isolates the change's causal effect on a chosen metric.

In A/B testing, define the null and alternative hypotheses.

The null hypothesis $H_0$ states there is no difference between variants (any observed difference is due to chance). The alternative hypothesis $H_1$ states there is a real difference (or improvement) caused by the treatment.

What are Type I and Type II errors in experimentation, and what symbols denote their rates?

A Type I error (false positive, rate $\alpha$) rejects a true null hypothesis. A Type II error (false negative, rate $\beta$) fails to reject a false null hypothesis. Statistical power equals $1 - \beta$.

What does a p-value represent, and how is significance typically decided?

The p-value is the probability of observing a result at least as extreme as the data, assuming $H_0$ is true. If $p < \alpha$ (commonly $\alpha = 0.05$), the result is deemed statistically significant and $H_0$ is rejected.

What is statistical power, and name two factors that increase it.

Statistical power ($1 - \beta$) is the probability of correctly detecting a true effect. It increases with larger sample size, larger true effect size, lower variance, and a higher (less strict) significance level $\alpha$.

In domain analytics, what are two key domain-specific metrics in marketing/e-commerce?

Common metrics include conversion rate $\left(\frac{\text{conversions}}{\text{visitors}}\right)$, Customer Lifetime Value (CLV/LTV), Customer Acquisition Cost (CAC), churn rate, and average order value (AOV). Domain analytics tailors metrics and methods to a field's context.

What are the essential stages of an end-to-end data analytics capstone project, and what is the golden rule for presenting results to stakeholders?

Stages: (1) define the business problem/objectives, (2) acquire and integrate data, (3) clean and transform (ETL/ELT), (4) explore and analyze/model, (5) validate, (6) visualize and build dashboards, and (7) communicate insights and recommendations. When presenting to stakeholders, the golden rule is to lead with the insight and recommended action (not the methodology), tailor to the audience, tell a clear story with the data, and tie every finding back to business impact.