DA-ASSOC Syllabus — Learning Objectives by Topic

What’s covered

• Topic 1: Databricks SQL Foundations
• Topic 2: Window Functions & Analytics Patterns
• Topic 3: Databricks SQL Warehouses & Performance Basics
• Topic 4: Dashboards, Visualizations & Alerts

Topic 1: Databricks SQL Foundations

Practice this topic →

1.1 Query fundamentals and result correctness

• Write and interpret basic SELECT queries with filters, aliases, and ordering.
• Explain how NULL values affect comparisons and filtering and choose safe null-handling patterns.
• Use CASE expressions to create conditional logic and derived columns.
• Differentiate DISTINCT from GROUP BY and choose the correct construct for a scenario.
• Given a scenario, identify why a query returns unexpected results (null logic, filter placement, join semantics).
• Explain why limiting columns early and filtering early improves performance conceptually.

1.2 Joins and relationship reasoning

• Choose the correct join type (inner/left/full/anti) for a described business need.
• Diagnose duplicate amplification caused by non-unique join keys and many-to-many joins.
• Explain how filtering after a LEFT join can unintentionally change the join to INNER semantics.
• Use semi/anti joins conceptually to answer existence and missing-record questions.
• Given a scenario, select the correct join keys based on grain (order-level vs item-level vs customer-level).
• Explain why join order and pre-aggregation can reduce data volume and improve performance.

1.3 Aggregations and group-based metrics

• Write GROUP BY aggregations and interpret grouped result sets.
• Use conditional aggregation to compute multiple metrics in a single pass.
• Choose between COUNT(*), COUNT(column), and COUNT(DISTINCT ...) based on nulls and uniqueness requirements.
• Explain common aggregation pitfalls: double counting due to joins and incorrect grouping keys.
• Given a scenario, select the correct grain for metrics (daily, monthly, per user).
• Describe how to validate aggregated results using reconciliation checks and sampling.

Topic 2: Window Functions & Analytics Patterns

Practice this topic →

2.1 Ranking and deduplication with windows

• Use ROW_NUMBER, RANK, and DENSE_RANK appropriately and explain how ties affect results.
• Partition by the correct key and order by the correct column to select “latest” or “top” records.
• Use window-based deduplication to select a deterministic record from duplicates.
• Recognize when window functions are required vs when GROUP BY is sufficient.
• Given a scenario, identify why a ranking query returns unexpected rows (missing PARTITION BY, wrong ORDER BY).
• Explain why filtering on window results (e.g., rn=1) must happen in an outer query/CTE.

2.2 Running totals and rolling metrics

• Compute running totals using window frames and explain frame boundaries conceptually.
• Differentiate ROWS vs RANGE frames at a high level and identify common use cases.
• Build rolling windows (e.g., last 7 days) and explain the effect of ordering and frame definitions.
• Recognize how missing data points affect rolling metrics and how to handle gaps conceptually.
• Given a scenario, choose a rolling metric approach that matches business reporting intent.
• Explain why window-based metrics require careful ordering and stable timestamps.

2.3 Cohorts and retention-style queries (awareness)

• Explain cohort analysis at a high level (group users by first event) and common metrics.
• Build a cohort key using date truncation and identify the correct grain (weekly/monthly).
• Recognize common retention pitfalls: counting events instead of users and join duplication.
• Given a scenario, choose the correct cohort definition for a business question.
• Explain why time zones and timestamp normalization can affect cohort correctness.
• Describe how to validate cohort queries with smaller sampled datasets.

Topic 3: Databricks SQL Warehouses & Performance Basics

Practice this topic →

3.1 SQL workspace basics (queries, saved queries, parameters)

• Describe how to create and reuse saved queries and why parameterization improves reusability.
• Use parameters/filters conceptually to build flexible dashboards and reports.
• Explain why consistent naming and documentation improves shared analytics workflows.
• Recognize the difference between ad-hoc exploration and production reporting queries.
• Given a scenario, choose the safest way to share analytics logic (saved query + dashboard) rather than copy/paste.
• Describe basic query troubleshooting steps (validate filters, validate joins, validate data freshness).

3.2 Performance intuition for analysts (concept-level)

• Explain why filtering early and selecting fewer columns reduces scan cost conceptually.
• Recognize that DISTINCT and ORDER BY can be expensive and should be used intentionally.
• Identify how partition pruning works at a conceptual level and why filtering on partition columns helps.
• Explain why joining large tables without reducing size can be slow and expensive.
• Given a scenario, choose the highest-ROI performance improvement (filter early, avoid unnecessary DISTINCT, pre-aggregate).
• Describe how to sanity-check query cost by comparing row counts and scan volume over time (concept-level).

3.3 Tables, views, and basic governance awareness

• Differentiate tables from views and identify when views provide safer sharing and abstraction.
• Explain why access control matters for shared datasets and why write permissions are higher risk.
• Recognize that schema changes can break dashboards and why stable definitions matter.
• Given a scenario, choose a governance-friendly way to publish metrics (curated table/view with ownership).
• Explain why documenting metric definitions reduces inconsistent reporting across teams.
• Describe why environment separation (dev/prod) reduces accidental production impact.

Topic 4: Dashboards, Visualizations & Alerts

Practice this topic →

4.1 Visualization selection and interpretation

• Choose appropriate chart types for common analytics questions (trend, distribution, comparison).
• Explain why axes, aggregation levels, and filters affect interpretation and can mislead if inconsistent.
• Recognize when to use log scale or normalization for skewed distributions (concept-level).
• Given a scenario, pick the visualization that best communicates the intended insight.
• Describe why consistent time grains (daily/weekly/monthly) matter for trend dashboards.
• Explain why data freshness and source-of-truth labeling improves dashboard trust.

4.2 Dashboard hygiene and reusable filtering

• Design dashboards with clear metric definitions and consistent filters/parameters.
• Explain why dashboard performance depends on query efficiency and scoped datasets.
• Recognize common dashboard pitfalls: conflicting filters, duplicated definitions, and stale data.
• Given a scenario, choose a dashboard layout that separates overview from drill-down effectively.
• Describe how to document assumptions and definitions for shared dashboards.
• Explain why minimizing the number of heavy queries improves user experience and cost.

4.3 Alerts and operational reporting (awareness)

• Describe the purpose of alerts and when to use threshold-based vs trend-based monitoring (concept-level).
• Identify the risk of noisy alerts and why thresholds should align with business impact.
• Explain why alerts should include context (time window, filter scope, owner) for faster response.
• Given a scenario, choose a safe alert design that avoids false positives due to missing data.
• Describe why alerting depends on data freshness and pipeline reliability.
• Explain why alert ownership and runbooks reduce response time.