Data Analytics Expert Labs

🎯 Goal: Load and validate customer dataset

📝 Data Quality Checks:
Before training, validate data quality: check for nulls, duplicates, data types, value ranges. Bad data = bad models. Use pandas profiling or custom validation functions.

💻 Configuration:
1. Source: customer_data.csv
2. Rows: 50,000 records
3. Features: 15 columns
4. Click "Load Data"

🎯 Goal: Clean and prepare data for modeling

📝 Preprocessing Steps:
• Handle missing values (impute or drop)
• Remove outliers (IQR method or Z-score)
• Encode categorical variables (one-hot or label encoding)
• Scale numerical features (StandardScaler or MinMaxScaler)

💻 Required Actions:
1. Strategy: impute_mean
2. Encoding: one_hot
3. Scaling: standard_scaler
4. Click "Preprocess"

🎯 Goal: Create derived features to improve model performance

📝 Feature Types:
Create interaction features (product/ratio of existing features), polynomial features, binning continuous variables, aggregations (customer lifetime value, recency/frequency/monetary).

💻 Configuration:
1. Add feature: tenure_revenue_ratio
2. Add feature: usage_frequency_bucket
3. Click "Generate Features"

🎯 Goal: Train multiple classification models

📝 Model Selection:
Try multiple algorithms: Logistic Regression (baseline), Random Forest (ensemble), XGBoost (gradient boosting), SVM (support vector machines). Compare performance to select best.

💻 Required Models:
1. Logistic Regression
2. Random Forest
3. XGBoost
4. Click "Train Models"

🎯 Goal: Calculate metrics and compare models

📝 Key Metrics:
• Accuracy: Overall correctness
• Precision: Positive prediction accuracy
• Recall: True positive rate
• F1-Score: Harmonic mean of precision/recall
• AUC-ROC: Model discrimination ability

💻 Action:
Click "Evaluate Models"

🎯 Goal: Select and deploy top-performing model

💻 Deployment:
1. Review model comparison
2. Select best model by F1-score
3. Click "Deploy Model"
4. Generate predictions on test set

🎯 Goal: Identify slow queries using query history

📝 Performance Analysis:
Use Snowflake's QUERY_HISTORY view to find queries with high execution time, high bytes scanned, or full table scans. Look for missing joins, unindexed filters, or large result sets.

💻 Command:
SELECT query_id, query_text, execution_time FROM query_history WHERE execution_time > 60 ORDER BY execution_time DESC LIMIT 10;

🎯 Goal: Add clustering to improve table scan performance

📝 Clustering Keys:
Clustering organizes data physically by specified columns. Best for columns used in WHERE clauses, JOINs, and ORDER BY. Snowflake auto-maintains clusters as data changes.

💻 Command:
ALTER TABLE sales_fact CLUSTER BY (date_id, region_id);

🎯 Goal: Pre-compute expensive aggregations

📝 Materialized Views:
Store pre-computed query results. Perfect for frequently-run aggregations (daily sales, monthly KPIs). Snowflake auto-refreshes when base tables change. Much faster than recomputing every time.

💻 Command:
CREATE MATERIALIZED VIEW daily_sales_mv AS SELECT date_id, SUM(amount) FROM sales_fact GROUP BY date_id;

🎯 Goal: Reduce join execution time

📝 Join Optimization:
• Filter before joining (reduce row count)
• Join smaller tables first
• Use proper join types (INNER vs LEFT)
• Ensure join keys have statistics
• Consider broadcasting small dimension tables

💻 Optimized Query:
SELECT * FROM sales_fact f JOIN (SELECT * FROM dim_date WHERE year = 2023) d ON f.date_id = d.date_id;

🎯 Goal: Enable auto-scaling for variable workloads

📝 Auto-Scaling:
Auto-scaling adds compute clusters when query queue builds up. Set MIN and MAX clusters. Scale-out handles concurrent queries, scale-up handles complex single queries. Balance performance vs cost.

💻 Command:
ALTER WAREHOUSE analytics_wh SET MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 4 AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;

🎯 Goal: Measure optimization impact

💻 Validation:
1. Re-run slow queries from Step 1
2. Compare execution time before/after
3. Check bytes scanned reduction
4. Verify clustering effectiveness

🎯 Goal: Establish connection to S3 data source

📝 S3 Authentication:
Use IAM roles (best practice) or access keys. Configure bucket policy to allow cross-account access if needed. Use S3 Select for efficient querying of large files.

💻 Configuration:
1. Service: AWS S3
2. Bucket: globaldata-source
3. Region: us-east-1
4. Click "Connect"

🎯 Goal: Configure ADF pipeline for transformation

📝 ADF Pipelines:
Azure Data Factory orchestrates data movement and transformation. Create linked services (connections), datasets (data structures), and pipelines (workflows). Use mapping data flows for complex transformations.

💻 Configuration:
1. Service: Azure Data Factory
2. Pipeline: S3-to-BigQuery
3. Click "Configure"

🎯 Goal: Setup BigQuery as destination warehouse

📝 BigQuery Connection:
Use service account with BigQuery Data Editor role. Enable BigQuery Data Transfer Service for scheduled loads. Consider partitioning and clustering on target tables for performance.

💻 Configuration:
1. Service: GCP BigQuery
2. Project: globaldata-analytics
3. Dataset: integrated_data
4. Click "Connect"

🎯 Goal: Execute ETL pipeline across clouds

📝 Data Transformation:
Apply transformations: data type conversions, column renaming, filtering, aggregations. Validate data quality (nulls, duplicates, constraints). Handle errors with retry logic and dead letter queues.

💻 Action:
1. Source: S3 CSV files (10GB)
2. Transform: Clean + Aggregate
3. Destination: BigQuery table
4. Click "Start Transfer"

🎯 Goal: Replicate BigQuery data to Snowflake

📝 Cross-Platform Sync:
Export from BigQuery to GCS, then COPY INTO Snowflake. Use Snowpipe for continuous loading. Configure external stages pointing to cloud storage. Consider change data capture (CDC) for incremental updates.

💻 Configuration:
1. Source: BigQuery
2. Destination: Snowflake
3. Mode: Incremental
4. Click "Sync"

🎯 Goal: Verify pipeline success and monitor ongoing transfers

💻 Monitoring:
1. Check transfer logs for errors
2. Verify row counts match source
3. Validate data quality rules
4. Setup CloudWatch/Azure Monitor alerts