Kỹ sư dữ liệu

## Ôn thi & Chuẩn bị cho vị trí Kỹ sư Dữ liệu - BIDV ### 1. Lộ trình chuẩn bị 2 tuần (Khuyến nghị) #### Tuần 1: Ôn tập kiến thức nền tảng **Ngày 1-2: SQL Mastery** ``` Sáng: Ôn Window Functions (RANK, DENSE_RANK, ROW_NUMBER, LEAD, LAG, FIRST_VALUE, LAST_VALUE) Chiều: Thực hành với sample database (PostgreSQL practice) Tối: Giải 5-10 bài SQL trên LeetCode (Medium level) ``` **Ngày 3-4: Python & Data Processing** ``` Sáng: Pandas advanced (merge, join, groupby, pivot_table) Chiều: NumPy vectorization, memory optimization Tối: Practice với dataset thực tế (100k+ rows) ``` **Ngày 5-6: System Design Fundamentals** ``` Sáng: Data modeling (3NF, star schema, snowflake) Chiều: ETL/ELT concepts, data quality frameworks Tối: Đọc về cloud data services (Azure Data Factory, Synapse, Data Lake) ``` **Ngày 7: Ngày nghỉ/ôn nhẹ** #### Tuần 2: Luyện tập chuyên sâu + Mock Interview **Ngày 8-9: Luyện giải bài tập thực tế** ``` Sáng: Practice problems về: - Sửa lỗi slow SQL queries - Design data pipeline từ đầu đến cuối - Xử lý data quality issues Chiều: Ôn behavioral questions + STAR method ``` **Ngày 10-11: Chuẩn bị cho ngành ngân hàng** ``` Sáng: Tìm hiểu về: - Banking data types (transaction, customer, account) - Regulatory requirements (PCI-DSS, data retention) - Common banking data challenges Chiều: Research BIDV cụ thể ``` **Ngày 12-13: Mock Interview** ``` - Thực hành phỏng vấn với bạn bè - Record lại và tự review - Tập trung vào phần yếu đã phát hiện ``` **Ngày 14: Chuẩn bị cuối cùng** ``` - Review all notes - Chuẩn bị outfit - In ấn hồ sơ (bản cứng) - Check logistics (địa điểm, thời gian) ``` ### 2. Tài liệu tham khảo #### Sách (Essential) | Tên sách | Tác giả | Mức độ quan trọng | |----------|---------|------------------| | The Data Warehouse Toolkit | Ralph Kimball | ⭐⭐⭐⭐⭐ | | Fundamentals of Data Engineering | Joe Reis & Matt Housley | ⭐⭐⭐⭐⭐ | | SQL for Data Scientists | Richmond HCl | ⭐⭐⭐⭐ | | Designing Data-Intensive Applications | Martin Kleppmann | ⭐⭐⭐⭐ | #### Online Resources ``` Website: - Mode Analytics SQL Tutorial - W3Schools SQL - Databricks Academy (free courses) - Azure documentation Practice Platforms: - LeetCode (SQL section - medium to hard) - StrataScratch - DataLemur (SQL interview questions) YouTube Channels: - Alex the Analyst - Tina Huang - TechTFQ ``` #### GitHub Repositories để tham khảo ``` - Data Engineering projects examples - dbt best practices - Data pipeline templates - SQL interview questions solutions ``` ### 3. Kiến thức nền cần nắm vững #### SQL Advanced Topics ```sql -- 1. Window Functions (BẮT BUỘC) SELECT employee_id, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank, LAG(salary, 1) OVER (ORDER BY hire_date) as prev_salary, SUM(salary) OVER (PARTITION BY department) as dept_total FROM employees; -- 2. Complex JOINs -- 3. CTEs for readability -- 4. DATE/TIME manipulation -- 5. String functions -- 6. Conditional aggregation ``` #### Python Libraries Focus ```python # Pandas - Master these: import pandas as pd # Merge/Join pd.merge(df1, df2, on='key', how='inner') # GroupBy with aggregation df.groupby('category').agg({ 'value': ['sum', 'mean', 'count'], 'date': ['min', 'max'] }) # Pivot tables df.pivot_table(values='sales', index='region', columns='quarter', aggfunc='sum') # Handling missing data df.fillna(method='ffill') df.dropna(thresh=0.7) # DateTime operations df['date'] = pd.to_datetime(df['date']) df['month'] = df['date'].dt.to_period('M') ``` #### Data Engineering Concepts ``` 1. Data Modeling - Star Schema vs Snowflake - Normalization vs Denormalization - Slowly Changing Dimensions (SCD) 2. ETL vs ELT - Batch vs Streaming - Change Data Capture (CDC) 3. Data Quality - Data profiling - Data validation rules - Data cleansing techniques 4. Cloud Data Services (Azure focus for Vietnam banks) - Azure Data Factory - Azure Synapse Analytics - Azure Databricks - Azure Data Lake Storage ``` ### 4. Sample Technical Interview Questions **SQL Challenge:** ```sql -- Given a transactions table: -- transaction_id, account_id, amount, transaction_date, transaction_type -- Write a query to show month-over-month growth by account WITH monthly_totals AS ( SELECT account_id, DATE_TRUNC('month', transaction_date) as month, SUM(amount) as total_amount FROM transactions GROUP BY account_id, DATE_TRUNC('month', transaction_date) ) SELECT current.account_id, current.month, previous.month as prev_month, current.total_amount, previous.total_amount as prev_total, ROUND((current.total_amount - previous.total_amount) / previous.total_amount * 100, 2) as mom_growth_pct FROM monthly_totals current LEFT JOIN monthly_totals previous ON current.account_id = previous.account_id AND current.month = previous.month + INTERVAL '1 month' ORDER BY current.account_id, current.month; ``` **Python Challenge:** ```python # Given a DataFrame with duplicate customer IDs, # find customers with highest total transaction value # Solution 1: Using groupby top_customers = df.groupby('customer_id')['transaction_value'].sum()\ .sort_values(ascending=False)\ .head(10) # Solution 2: Using merge with aggregation customer_totals = df.groupby('customer_id')['transaction_value'].sum().reset_index() customer_totals.columns = ['customer_id', 'total_value'] top_customers = customer_totals.nlargest(10, 'total_value') ```