🌐 Data Engineering Overview: From
Source to Reporting
🚀 What is Data Engineering?
Data Engineering refers to the processes and tools used to collect data (from various sources),
transform it into usable formats, and make it available for downstream analytics and reporting. It
bridges the gap between data producers (upstream) and data consumers (downstream).
📥 Upstream: Data Ingestion (Sources)
Data can originate from a variety of sources:
● APIs: e.g., Jira API, Azure DevOps API
● Databases: SQL Server, Oracle, PostgreSQL, etc.
● Files: Excel, CSV, JSON
● Platforms: SharePoint, Web Portals, IoT devices
These sources provide raw data, which needs to be collected using ingestion tools or pipelines.
🔐 Azure Key Vault
Used to store sensitive information such as API keys, connection strings, and passwords
securely.
🔄 Types of Processing
🟡 Batch Processing
● Data is collected and processed in scheduled batches (e.g., once daily).
● Suitable for use cases where real-time updates are not required.
🟢 Real-Time (Streaming) Processing
● Data is ingested and processed within seconds (e.g., ~5 seconds delay).
● Example: Netflix Squid Game — when a user watches Squid Game, similar
recommendations are shown instantly.
📬 Messaging and Streaming Tools
📨 Kafka (Distributed Messaging System)
● Acts as a queue to stream data from producers to consumers.
● Data is published in topics and consumed in real-time or near-real-time.
● Used for building real-time data pipelines.
☁️ Data Storage: Cloud-Based Object Storage
🟦 Azure Data Lake (ADLS)
● Microsoft’s cloud solution for large-scale data storage.
● Supports structured, semi-structured, and unstructured data.
🟧 Amazon S3
● Amazon’s object storage service.
● Often used to store data in Parquet format (highly efficient columnar storage).
🛠️ ETL & Pipeline Tools
🛠 Azure Data Factory (ADF)
● ETL (Extract, Transform, Load) tool used to design pipelines.
● Retrieves data from sources and loads it into storage for further processing.
🧪 Data Transformation
⚙️ PySpark
● Python API for Apache Spark.
● Used to process large datasets stored in data lakes.
● Helps transform data into structured formats (tables).
🧱 Storage Layers (Medallion Architecture)
The data lake is divided into 3 layers:
1. Bronze Layer – Raw data from the source.
2. Silver Layer – Cleaned and enriched data.
3. Gold Layer – Final business-ready data used for reporting.
Note: At every stage (layer), Delta Tables are used to enable ACID transactions
and version control.
🧮 OLTP vs OLAP
Term Description
OLTP (Online Transaction Used for real-time transactions and supports ACID properties.
Processing) E.g., transactional systems.
OLAP (Online Analytical Optimized for complex analytical queries. Used for data
Processing) modeling and BI.
🗃️ Data Modeling
🧩 Fact and Dimension Tables
● Fact Tables: Store quantitative data (measurable metrics like sales, revenue).
● Dimension Tables: Store descriptive attributes (like date, product, customer info).
⭐ Star Schema
● Central Fact table connected to multiple Dimension tables.
● Simpler design, less granularity.
❄️ Snowflake Schema
● Extension of the star schema with normalized dimension tables.
● Used when more granularity and detailed dimension data is needed.
📊 BI & Reporting
After the Gold Layer:
● SQL Views are created on top of Gold Tables.
● Datasets are prepared and published for reporting tools like Power BI, Domo, or
Tableau.
● These datasets are consumed by end users or business teams.
📁 Project-Level Example: DRE Product
Let’s take the example of a project named DRE (Data Reporting Engine).
🔹 Data Sources:
● Jira API
● DevOps API
● SharePoint Configuration Files
🔹 Services Used:
● Azure Key Vault – for secure credential storage
● Azure Data Factory – for building pipelines
● Azure Data Lake – to store intermediate and final datasets
● PySpark Code – to build OLTP-compliant tables
● Delta Tables – used at each medallion stage for versioned, reliable data
🔹 Final Output:
● SQL Views → Dataset → Power BI Dashboard
🔁 Final Data Flow Summary
scss
CopyEdit
Source (Jira, DevOps, SharePoint)
⬇
Azure Data Factory (ETL)
⬇
Azure Data Lake (Bronze Layer)
⬇
PySpark Logic
⬇
Silver Layer → Data Transformation
⬇
Gold Layer (Final Tables)
⬇
SQL Views → Power BI Dataset
This is how a typical Data Engineering Pipeline is built and maintained—from source
ingestion, through transformation and modeling, to final consumption in BI tools.
📡 Source Systems (APIs, DBs, SharePoint, Excel)
🔐 Azure Key Vault (for secure credentials)
⬇
🛠️ Azure Data Factory (Extract & Load via ETL)
⬇
🗂️ Data Lake – Bronze Layer (Raw Ingested Data)
⬇
🧮 PySpark Job – Data Cleaning & Validation
⬇
📁 Data Lake – Silver Layer (Cleaned Structured Data)
⬇
🧠 PySpark – Business Logic, Joins, Aggregations
⬇
🏆 Data Lake – Gold Layer (Final Modeled Tables - Delta)
⬇
📊 SQL Views (Optional - Logic Layer for BI Tools)
⬇
📈 BI Tools (Power BI / Tableau / Domo Dashboards & Reports)
⬇
—- Interview Preparation Stuff -
✅ Best Questions to Ask on Source Systems (Interview
& Project Discussions)
These questions help clarify the scope, reduce assumptions, and ensure your pipeline
design is resilient and scalable.
🔹 1. What are the Source Systems?
Purpose: Understand where the data is coming from and its nature.
● What types of sources are involved? (e.g., relational DBs, APIs, flat files, cloud apps like
Salesforce?)
● Is the data internal (owned) or external (third-party)?
🔹 2. What is the Data Format?
Purpose: Decide the ingestion parser and storage format.
● Is the data structured (SQL), semi-structured (JSON, XML), or unstructured (PDF,
images)?
● Are there nested fields or arrays in the data?
🔹 3. What is the Frequency of Data Arrival?
Purpose: Helps define whether to use batch or streaming pipelines.
● Is it real-time (e.g., Kafka), near-real-time, daily, or weekly?
● Can the schedule change based on business needs?
🔹 4. What is the Volume of Data?
Purpose: Helps determine the right storage, partitioning, and compute.
● What is the daily/monthly volume (e.g., 10M rows/day)?
● Is there a growth estimate for the next 6-12 months?
🔹 5. What is the Latency Expectation?
Purpose: Helps define SLAs and real-time vs batch trade-offs.
● Do users need this data within minutes, hours, or next day?
● Is near real-time reporting required?
🔹 6. What is the Data Quality & Schema Stability?
Purpose: Prepare for evolving schemas and quality issues.
● Does the schema change often?
● Are nulls, duplicates, or outliers expected?
● Are there primary keys or natural keys?
🔹 7. What is the Authentication Mechanism?
Purpose: Determine how to securely access the source.
● Do we use basic auth, OAuth, VPN, SSH, or token-based access?
● Are there rate limits or throttling?
🔹 8. Are There Any Business Filters We Need to Apply?
Purpose: Avoid ingesting unnecessary or sensitive data.
● Should we exclude test records or archived data?
● Any GDPR/PII handling requirements?
🔹 9. Who Owns the Source System?
Purpose: Clarifies accountability and ongoing support.
● Is there a contact person or owner we can coordinate with?
● What happens when data doesn’t arrive?
🎨 Beautiful End-to-End Design Summary (Medallion
Architecture + Tools)
Here's a clean visual that you can sketch, build on PowerPoint, or include in a project
deck/interview:
pgsql
CopyEdit
+----------------------------+ +---------------------------+
| Source Systems | | Streaming Sources |
|----------------------------| |---------------------------|
| - MySQL (Orders) | | - Kafka (User Clicks) |
| - Postgres (Products) | | - Event Hubs (App Logs) |
| - Google Ads (API) | +---------------------------+
| - CRM (Salesforce API) |
| - Excel (Returns) |
+-------------+--------------+
|
▼
+-------------v--------------+
| Ingestion Layer | ← (Airbyte / ADF / Kafka / PySpark)
+----------------------------+
| - Incremental + CDC |
| - File drops & APIs |
+-------------+--------------+
|
▼
+-------------v--------------+
| Raw Layer (Bronze) | ← (ADLS / S3 / Blob Storage)
+----------------------------+
| - Data stored as-is |
| - Maintain schema logs |
+-------------+--------------+
|
▼
+-------------v--------------+
| Cleaned Layer (Silver) | ← (Delta Tables / Spark Jobs)
+----------------------------+
| - Type casting |
| - Null handling |
| - Joins & filters applied |
+-------------+--------------+
|
▼
+-------------v--------------+
| Curated Layer (Gold) | ← (DBT / SQL Models / Delta Lake)
+----------------------------+
| - Star schema modeling |
| - Denormalized wide tables|
| - SCD Type 2 handling |
+-------------+--------------+
|
▼
+-------------v--------------+
| BI & ML Consumption | ← (Power BI / Tableau / ML Models)
+----------------------------+
| - Executive dashboards |
| - Customer segmentation |
| - Recommendation systems |
+----------------------------+
💎 Bonus: KPIs to Track in E-commerce Data Pipeline
Category KPIs
Sales Revenue, AOV, Orders/Day
Returns Return Rate, Reason Codes
Product Conversion %, Top SKUs
Customer LTV, Retention, Signup Source
Marketing Campaign ROI, CAC
Site Cart Abandonment Rate, Session
Performance Length
📌 Final Impression Tips (for Interviews):
✔ "I always start by deeply understanding the business problem, not just the tech stack."
✔ "For each source, I validate volume, format, and update frequency before picking ingestion
tools."
✔ "I use Medallion Architecture (Bronze → Silver → Gold) with Delta Lake to manage data
lifecycle efficiently."
✔ "Every transformation step is documented, modular, and reproducible for audits and future
scaling."