Azure Data Engineering Project - End-to-End Azure Data Platform

In my recent project, I built an end-to-end Azure data platform that ingested semi-structured JSON data from multiple APIs and delivered analytics-ready datasets modelled in a star schema within Azure Synapse. The solution was metadata-driven, secure, and well governed, and designed to scale as new data sources were onboarded.



For ingestion, I used Azure Data Factory and followed a metadata-driven approach rather than hardcoding logic into pipelines. I maintained control and watermark tables that defined how each API source should be processed, including source name, API endpoint, authentication method, pagination logic, incremental load column, target schema and table, load type, and whether the data contained PII.

The watermark tables stored the last successful load timestamp or record ID, pipeline run status, and load timings. At runtime, ADF pipelines read from these tables and updated the watermark after successful execution, enabling true incremental loading and restartability.

Design approach: One generic, reusable pipeline handled multiple APIs using metadata and parameters instead of creating one pipeline per source.

The ADF pipelines were fully generic and parameterised. A single pipeline accepted parameters such as source name, entity name, API URL, file path, target schema, target table, load type, and watermark value. These were driven from metadata lookups and passed into Databricks notebooks.

For transformations, I used Azure Databricks, which was particularly effective for handling nested and semi-structured JSON. Using Spark, I flattened complex structures, handled schema drift, applied business rules, and enforced data quality checks.

I implemented a medallion architecture in ADLS Gen2: the Bronze layer stored raw API JSON payloads, the Silver layer stored cleansed and normalised data in Parquet format, and the Gold layer contained curated business entities loaded into Azure Synapse.

Data governance was applied throughout the platform. This included schema validation, record count checks, null checks, duplicate detection, and role-based access control across ADLS, Databricks, and Synapse, with full auditability.

One of the source systems contained PII data. All secrets and credentials were stored in Azure Key Vault. PII columns were identified via metadata and masked or encrypted in the Silver layer. In Synapse, access was controlled using column-level security, dynamic data masking, and RBAC.

For monitoring, I integrated Azure Monitor and Log Analytics to capture pipeline metrics and failures. I also implemented Azure Logic Apps to send email alerts when pipelines failed.

Finally, the Gold layer was modelled in Azure Synapse using fact and dimension tables following a star schema. Materialised views were created for key KPIs, supporting fast reporting in Power BI.

Overall, the solution was scalable, metadata-driven, secure, and well governed, with a clear separation between ingestion, transformation, and consumption layers.

Popular posts from this blog

Exploring the Largest UK Employers: A Power BI Visualization

Image
Understanding employment distribution among top companies can provide valuable insights into industry dominance and workforce trends. In this blog, I analyze the largest employers in the UK using a Power BI table visualization, sourced from CompaniesMarketCap . Source:  CompaniesMarketCap Key Insights from the Data: Compass Group leads the ranking with 550,000 employees, dominating the food service industry. Tesco, the retail giant, follows with 330,000 employees. HSBC, a major player in banking, employs over 215,000 people. The total workforce among the top companies surpasses 1.98 million employees. Visualizing in Power BI: Using a table visualization, we can clearly compare the number of employees across different companies. Power BI’s sorting, aggregation, and filtering features enhance data readability and analysis. However, incorporating bar charts, conditional formatting, and KPIs could make the insights even more compelling. What’s Next? Would you add more interactive eleme...
Read more

Master Databricks Asset Bundles Through Hands-On Practice

15 min read | 100% Practical Guide Forget theory. Forget abstract examples. This is a hands-on, build-as-you-learn guide to mastering YAML through the lens of Databricks Asset Bundles (DABs) . By the end of this post, you'll go from never writing YAML to confidently deploying production-grade data pipelines as code. 🎯 What You'll Build: A complete Databricks workspace configuration including jobs, clusters, notebooks, and permissions—all defined in YAML and deployable with a single command. Level 0: YAML Basics BEGINNER The Golden Rules Rule #1: YAML uses spaces for indentation , never tabs. Standard is 2 spaces per level. Rule #2: YAML is case-sensitive . Name ≠ name Rule #3: Indentation = Structure . It defines parent-child relationships. ...
Read more

PySpark Important Last-Minute Notes

Image
If you are preparing for Data Engineering interviews , Spark projects , or need a quick PySpark revision , this post consolidates the most important PySpark concepts in one place. Best for: Data Engineers, Big Data Developers, Azure/Databricks/Microsoft Fabric users, and anyone doing last-minute interview preparation. What is PySpark? PySpark is the Python API for Apache Spark , an open-source distributed computing framework used for large-scale data processing. Why PySpark? Distributed, in-memory processing Faster than traditional batch systems Scales across clusters Supports SQL, Streaming, and Machine Learning Common use cases ETL / ELT pipelines Big data analytics Machine learning at scale Real-time and batch processing Spark Cluster Architecture A Spark cluster typically consists of: Master Node: Manages resources and schedules tasks Worker Nodes: Execute tasks in parallel This architecture enables efficient distributed p...
Read more