📊 Data Engineering & Cloud Data Warehousing

Welcome to the Data Engineering & Data Warehousing repository! This repository serves as a comprehensive collection of coursework, laboratory guides, hands-on assignments, architectural documentations, ETL job designs, and analytical datasets covering core principles of data engineering, dimensional modeling, cloud data warehousing (Snowflake, AWS Redshift, Google BigQuery), data integration, and business intelligence.

---

📑 Table of Contents

1. Overview
2. Core Knowledge Domains
   • 1. Dimensional Modeling & Schema Design
   • 2. Cloud Data Warehousing (Snowflake, AWS Redshift & Google BigQuery)
   • 3. Data Integration & ETL Pipelines (Talend)
   • 4. Business Intelligence & Analytics (Tableau)
3. Repository Directory & File Map
4. Datasets & Data Sources
5. Prerequisites & Tools Setup
6. Troubleshooting & Best Practices
7. Author

---

🌟 Overview

Modern Data Engineering bridges operational source systems and enterprise analytical reporting. This repository documents end-to-end data workflows, including:

• Designing Star Schemas and Snowflake Schemas with Fact and Dimension tables.
• Handling Slowly Changing Dimensions (SCD), Step Dimensions, and Super/Subtype Dimension Patterns.
• Provisioning and configuring Snowflake Data Warehouses (Virtual Warehouses, querying database schemas like snowbearair_db.promo_catalog_sales, JSON semi-structured data querying, decoupled compute & storage).
• Provisioning and optimizing AWS Redshift clusters (Distribution Keys, Sort Keys, S3 COPY commands).
• Designing Google BigQuery data warehouses leveraging partitioned and clustered tables.
• Building robust ETL/ELT Data Integration Pipelines using Talend Open Studio / Talend Cloud.
• Developing interactive dashboards and advanced data visualizations in Tableau Desktop.

---

🧠 Core Knowledge Domains

1. Dimensional Modeling & Schema Design

• Concepts: Business Process Matrix, Grain Definition, Fact Tables (Transaction, Periodic Snapshot, Accumulating Snapshot), Dimension Tables.
• Star vs Snowflake Schema: Normalization of dimension hierarchies (Snowflaked Dimensions) vs denormalized Star Schema trade-offs.
• Advanced Patterns:
  • Slowly Changing Dimensions (SCD Type 1, Type 2, Type 3).
  • Super/Subtype dimensions, Step dimensions, and Dimension Hierarchies.
• Key Resources:
  • RN7945_Lab 1_Dimensional Modeling-2U-1.docx & RN7945Assignment 1_Dimensional Modeling.docx
  • Dimensional Modeling Practice (1).docx
  • SCD Practice Questions (1).docx
  • Super_Subtype, Step Dimension Practice (2).docx

2. Cloud Data Warehousing (Snowflake, AWS Redshift & Google BigQuery)

• Snowflake Data Platform:
  • Multi-cluster shared-data cloud architecture.
  • Decoupled storage and compute (Virtual Warehouses).
  • Query execution over structured and semi-structured (JSON/VARIANT) data.
  • Practical Lab & Assignment: Provisioning Virtual Warehouses and executing analytical SQL queries on snowbearair_db.promo_catalog_sales.
• AWS Amazon Redshift:
  • Columnar storage architecture, Node types (RA3, Dense Storage/Compute).
  • Data distribution styles (KEY, EVEN, ALL).
  • Compound and Interleaved Sort Keys.
  • High-speed data loading from Amazon S3 via the COPY command.
  • Document: Cloud Data Warehousing_RedShift-2.docx
• Google BigQuery:
  • Serverless architecture (Slots, Dremel execution engine).
  • Table Partitioning (by ingestion time or column timestamp/date) and Clustering.
  • Document: Cloud Data warehousing_BigQ_v2.docx
• Hands-on Assignments:
  • RN7945_Assignment 3-Cloud DW Assignment_v2 (1).docx (Snowflake & Cloud DW)
  • Assignment 3-Cloud DW Assignment_v2.docx

3. Data Integration & ETL Pipelines (Talend)

• Talend Studio & Cloud Integration:
  • Job creation, context variables, metadata connection management.
  • Components: tFileInputDelimited, tMap, tAggregateRow, tJoin, tPostgresqlOutput, tRedshiftOutput.
  • Data validation, null handling, transformation logic, and error handling.
• Key Resources:
  • RN7945_Assignment 4-Data Integration-v3 .docx
  • Talend Data Integration Tasks (2).docx
  • Talend Studio from Cloud (1).docx

4. Business Intelligence & Analytics (Tableau)

• Tableau Fundamentals & Advanced Visualizations:
  • Basic charts, bar plots, scatter plots, geographic maps.
  • Advanced calculations, LOD expressions (FIXED, INCLUDE, EXCLUDE), dual-axis charts, parameters, and interactive dashboards.
• Key Resources:
  • RN7945Lab 2_Tableau Basic-1.pdf & Lab 2_Tableau Basic-1-2.docx
  • RN7945_Lab 3_Advanced Tableau.docx & Lab 3_Advanced Tableau (2).docx
  • RN7945_Assignment 2_Tableau Analysis (1).docx

---

📁 Repository Directory & File Map

Category	File Name	Description
Assignments	RN7945Assignment 1_Dimensional Modeling.docx	Assignment 1: Dimensional Modeling & ERD Design
	RN7945_Assignment 2_Tableau Analysis (1).docx	Assignment 2: Business Analytics & Tableau Dashboards
	RN7945_Assignment 3-Cloud DW Assignment_v2 (1).docx	Assignment 3: Snowflake, AWS Redshift & Google BigQuery
	RN7945_Assignment 4-Data Integration-v3 .docx	Assignment 4: Talend ETL Data Integration
Labs	RN7945_Lab 1_Dimensional Modeling-2U-1.docx	Lab 1: Dimensional Modeling & Snowflake Schema exercises
	RN7945Lab 2_Tableau Basic-1.pdf	Lab 2: Introduction to Tableau Desktop
	RN7945_Lab 3_Advanced Tableau.docx	Lab 3: Advanced Tableau Visualizations & LOD Expressions
Architecture & Guides	Cloud Data Warehousing_RedShift-2.docx	Guide to AWS Redshift architecture & loading
	Cloud Data warehousing_BigQ_v2.docx	Guide to Google BigQuery partitioning & performance
	Common Trouble Shooting...docx	Solutions for AWS IAM role & S3 access key errors
	Software Download Instruction_v4 (1).docx	Environment setup instructions for software tools
Datasets	tickitdb (1).zip & tickitdb-1.zip	AWS Redshift TICKIT sample database
	Sample - Superstore.xlsx	Classic Superstore retail dataset for Tableau analytics
	DW_DataSets.zip	Comprehensive Data Warehousing data package
	Customer, Order, Product CSV files	Relational source tables for ETL testing

---

🗄 Datasets & Data Sources

1. Snowflake Promo Catalog Sales Database (snowbearair_db.promo_catalog_sales):
   • Used in Assignment 3 for warehouse querying, filtering, and catalog sales aggregation.
2. AWS TICKIT Database (tickitdb.zip):
   • Contains 7 tables representing a sports/event ticketing database: USERS, VENUE, CATEGORY, DATE, EVENT, LISTING, SALES.
   • Used for demonstrating Redshift distribution/sort keys and join optimizations.
3. Superstore Sales (Sample - Superstore.xlsx):
   • Retail sales order data including Orders, Returns, and People across various regions.
4. Partitioned Customer & Order CSV Files:
   • CSV datasets (Cust_Part1, Cust_Part2, Cust_Part3, CustomerDetail, OrderLine, Charges, Product) for testing file merging and pipeline staging in Talend.

---

🛠 Prerequisites & Tools Setup

• Database / Data Warehousing:
  • Snowflake Data Cloud Account (Virtual Warehouses)
  • AWS Account (Amazon Redshift, Amazon S3, AWS IAM)
  • Google Cloud Platform (Google BigQuery)
• ETL / Integration:
  • Talend Open Studio for Data Integration (v7.x / v8.x)
• Business Intelligence:
  • Tableau Desktop or Tableau Public
• Spreadsheet / Tools:
  • Microsoft Excel / LibreOffice for dataset inspection

---

🔧 Troubleshooting & Best Practices

• AWS IAM S3 Access Errors: Refer to Common Trouble Shooting- AWS Access Key ID Error and IAM role authority to S3 bucket Error Solutions (1).docx for step-by-step role trust policy configurations.
• Redshift COPY performance: Ensure S3 files are split into multiples of your slice count and compressed using gzip or bzip2.
• BigQuery Partitioning: Use partition filters in WHERE clauses to avoid full table scans and control query costs.

---

👤 Author

• Gopi Krishna Reddy Katkuri
• GitHub: @katkurigopi05
• Repository: https://github.com/katkurigopi05/data_engineering