Senior Data Analytical Specialist Scientist

Core Concepts You Must Understand

ETL vs. ELT:

• ETL = Extract → Transform in a staging area → Load into warehouse. Traditional approach for complex transformation logic.
• ELT = Extract → Load raw data first → Transform inside the warehouse using its compute power. Modern approach (Snowflake, BigQuery, Redshift).

The Three Phases Explained:

1. Extract: Pull data from source systems (databases, APIs, flat files, spreadsheets). Challenges: different formats, connection protocols, incremental vs. full loads.
2. Transform: Clean data (remove duplicates, handle nulls), standardize formats (dates, currencies), apply business rules (derived fields), join data from multiple sources, validate data quality.
3. Load: Write to the target warehouse or data mart. Strategies: full refresh, incremental append, upsert (update existing + insert new).

Data Warehouse Design:

• Star Schema: A central fact table (measures/metrics like transaction amounts, counts) surrounded by dimension tables (descriptive attributes: dates, locations, products). Simple to query, optimized for read-heavy analytics.
• Snowflake Schema: Same as star but dimensions are normalized into sub-tables. Saves storage but requires more joins.
• Fact Tables: Store quantitative event data. Each row = one event/transaction.
• Dimension Tables: Store descriptive context (who, what, where, when).
• Slowly Changing Dimensions (SCD): Type 1 = overwrite (lose history). Type 2 = new row with versioning (preserve history). Type 3 = old/new columns.

Common ETL Tools:

Types of ML Problems

The ML Lifecycle — Step by Step

1. Problem Framing: The most important step. Translate the business question into a specific ML task. “We want to reduce wait times” becomes “Can we predict daily volume to optimize staffing?” (regression). Define what success metric matters to the business.
2. Data Collection & EDA: Gather datasets, explore distributions, correlations, outliers, missing values. Visualize with histograms, scatter plots, heatmaps. Assess: do you have enough data?
3. Feature Engineering: Create features from raw data (e.g., “day of week” from a date). Handle missing values (impute, flag, or drop). Encode categoricals (one-hot, label, target encoding). Scale numericals (StandardScaler, MinMaxScaler). Select features (remove correlated, use importance rankings).
4. Model Selection & Training: Start with a simple baseline (e.g., logistic regression) to establish a benchmark. Try 2–3 more complex models (Random Forest, XGBoost). Use k-fold cross-validation. Tune hyperparameters (GridSearchCV, RandomizedSearchCV).
5. Evaluation Metrics:
   • Classification: Accuracy (balanced data), Precision (minimize false positives), Recall (minimize false negatives), F1-Score (balance of both), AUC-ROC (overall discrimination)
   • Regression: RMSE, MAE, R-squared
   • Always use the metric that aligns with business impact.
6. Model Interpretability:
   • SHAP values — shows how each feature contributes to individual predictions. Gold standard.
   • LIME — explains individual predictions with a local approximation.
   • Feature importance — global view of which features matter most.
   • In government, interpretability is often non-negotiable — decisions affecting citizens must be explainable.
7. Deployment & Monitoring: Deploy as batch scoring, API, or embedded in a dashboard. Monitor for model drift. Set retraining schedules.

Window Functions (most important for senior SQL roles)

Window functions perform calculations across a set of rows related to the current row, without collapsing the result set like GROUP BY does.

CTEs (Common Table Expressions)

A CTE is a temporary named result set defined with WITH. It makes complex queries readable and maintainable. Recursive CTEs reference themselves — used for hierarchical data (org charts, category trees).

Performance Optimization

• Indexes: B-tree (default, good for equality/range), covering indexes (include all needed columns)
• Execution plans: Use EXPLAIN/EXPLAIN ANALYZE to see how the database processes your query
• Partitioning: Split large tables by date or category so queries only scan relevant partitions
• Avoid pitfalls: Functions on indexed columns prevent index use (e.g., WHERE YEAR(date_col) = 2025 is slower than WHERE date_col >= '2025-01-01')

Data Governance — The Framework

Data governance is the set of policies, processes, roles, and standards that ensure data is managed as a strategic asset. The “rules of the road” for data.

• Data Quality: Is the data accurate, complete, consistent, and timely? Measured with quality rules and monitored with dashboards.
• Data Ownership: Every dataset must have a designated business owner accountable for its accuracy. This is NOT an IT role.
• Data Stewardship: Day-to-day operators who maintain quality, enforce standards, and manage data dictionaries. Owners = “accountable”; Stewards = “responsible.”
• Data Access & Security: Who can see what data, under what conditions. Tied directly to FIPPA and data classification.
• Data Standards: Naming conventions, format standards, integration standards.

Data Lineage — What It Is

Data lineage is the documented trail of where data comes from, how it’s transformed, and where it goes. It answers: “Where did this number come from?”

Why lineage matters:

• Impact analysis: If a source system changes, you know every downstream report affected
• Debugging: When a dashboard shows an unexpected number, trace back to the source
• Compliance: FIPPA may require showing where personal information flows
• Trust: Stakeholders trust analytics when they can see provenance

Power BI Architecture

• Power Query (M language): Data transformation layer. Connect, clean, merge, shape data BEFORE it reaches the model. The “T” in ETL within Power BI.
• Data Model: Relationships between tables. Star schema: fact tables linked to dimensions. Relationships = one-to-many with cross-filter direction.
• DAX (Data Analysis Expressions):
  • CALCULATE() — modifies filter context. The single most important DAX function.
  • FILTER() — returns a filtered table. Used inside CALCULATE.
  • ALL() — removes filters. Used for “percentage of total” calculations.
  • Time Intelligence: SAMEPERIODLASTYEAR(), DATEADD(), TOTALYTD()
• Row-Level Security (RLS): DAX filter expressions so users only see authorized data. Critical in government.
• Power BI Service: Cloud platform for publishing, sharing, scheduled refresh, workspace governance.

Dashboard Design Principles

• One chart, one message: Each visualization should answer one specific question
• Progressive disclosure: Overview first (summary KPIs), then drill-down for details
• The 5-second rule: A user should understand the key message within 5 seconds
• Right chart type: Bars = comparison, Lines = trends, Maps = geographic, Tables = exact values, KPI cards = key metrics

Types of Data Quality Issues

Techniques for Handling Messy Data

Imputation (for missing values):

• Mean/Median: Replace with average. Simple but reduces variance.
• Mode: For categoricals, use the most common value.
• Predictive: Use other features to predict the missing value (KNN, regression imputation).
• Flag and keep: Create a binary column is_missing_income = 1/0 so the model can learn from missingness itself.
• Domain rules: e.g., if postal code starts with “M”, infer Ontario/Toronto.

Deduplication:

• Exact matching on key fields (name + birthdate + postal code)
• Fuzzy matching using Levenshtein distance, Jaro-Winkler, or Soundex
• Probabilistic record linkage algorithms
• Choose a “golden record” — the most complete or most recent version

Requirements Elicitation Techniques

• Stakeholder interviews: One-on-one conversations to understand decision-making processes and pain points.
• Current-state assessment: What data exists today? What reports? What’s manual vs. automated?
• Decision mapping: For each key decision: “What question needs answering? What data would answer it? Do we have it? In what quality?”
• User stories: “As a [role], I need [data/report], so that I can [make decision/take action].”

Data Fitness Assessment (5 Dimensions)

Common Predictive Models in Government

• Demand forecasting: Predicting service volumes to optimize staffing
• Risk scoring: Classifying cases by risk level for prioritized review
• Churn prediction: Predicting which clients will disengage
• Anomaly detection: Identifying fraud, errors, or emerging issues
• Scenario modelling: “What if” analysis for policy decisions

Model Validation — Key Concepts

• Train/Test Split: 70–80% train, 20–30% evaluate. Never evaluate on training data.
• K-Fold Cross-Validation: Split into K folds, train on K-1, test on remaining. Repeat K times. More reliable than single split.
• Confusion Matrix:
  Predicted + Predicted - Actual + TP FN Actual - FP TN Precision = TP / (TP + FP) “Of those I flagged, how many correct?” Recall = TP / (TP + FN) “Of all actual positives, how many caught?”
• Baseline comparison: Always compare your model against a simple baseline. If your model doesn’t beat it, it’s not adding value.

FIPPA Basics

• Personal information: Any recorded info about an identifiable individual — name, address, SIN, health info, employment, opinions, even IP addresses.
• Collection limitation: Only collect what is directly necessary for the program’s purpose.
• Use limitation: Only use for the purpose collected, unless consent or legal exception.
• Disclosure limitation: Cannot disclose to third parties without consent or legal authority.
• Individual access: People have the right to see their own data and request corrections.

Privacy-Preserving Techniques

Common Tensions Between Business and IT

• Speed vs. quality: Business wants dashboards yesterday. IT wants proper pipelines with testing. Your role: propose an MVP that delivers value quickly while building toward the full solution.
• Flexibility vs. standards: Business wants custom solutions. IT wants standardization. Your role: design within existing standards while advocating for necessary exceptions.
• Access vs. security: Business wants open data. IT wants locked-down systems. Your role: implement appropriate access controls that enable analytics without compromising security.

Key Roles & How to Work With Them

The Layered Communication Model

Think of your report as three layers, like a newspaper:

1. Headline layer (for everyone): 2–3 sentences. What’s the key message? What action is needed? This is what the executive reads.
2. Story layer (for engaged stakeholders): 1–2 paragraphs. What happened, why it matters, what we’re doing. This is what the program manager reads.
3. Detail layer (for technical readers): Query performance, data quality metrics, pipeline logs. Often an appendix.

RAG Status (Red / Amber / Green)

• Green: On track, no issues
• Amber: At risk, issues identified but mitigation in place
• Red: Off track, immediate action or escalation needed

Each status item: name, RAG colour, one-line explanation, action being taken if not Green.

Sample Report Structure

Reporting Best Practices

• Lead with decisions needed. If the reader does nothing else, what must they know or approve?
• Consistent structure. Same format every week so readers can scan quickly.
• Separate “what happened” from “what it means.” Impact, not activities.
• Quantify progress. “Completed 7 of 10 reports” beats “making good progress.”
• Flag risks early. Amber with time to act is better than Red as a surprise.