azure-data-migration-to-data-lake

KO | EN

gitlite — search

#javascript #python #ai #go #machine-learning #rust #deep-learning #macos #windows #awesome #security #pytorch #mcp #openai

azure-data-migration-to-data-lake

(★ 1)

Azure Data Migration using SHIR, Azure Data Factory, ADLS Gen2, Parquet, Job Trigger and Incremental Load

azure-data-migration-to-data-lake 최신버젼 다운로드

최종 버전 다운로드 (.zip)

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# Data Migration to Azure Data Lake

<br>

### A reusable Azure ingestion framework for moving data from a **new self-hosted SQL Server** into **ADLS Gen2**

[![Azure Data Factory](https://img.shields.io/badge/Azure-Data%20Factory-0078D4?logo=microsoftazure&logoColor=white)](https://raw.githubusercontent.com/malikaowerapat/azure-data-migration-to-data-lake/main/)
[![SQL Server](https://img.shields.io/badge/SQL-Server-CC2927?logo=microsoftsqlserver&logoColor=white)](https://raw.githubusercontent.com/malikaowerapat/azure-data-migration-to-data-lake/main/)
[![ADLS Gen2](https://img.shields.io/badge/ADLS-Gen2-00A4EF?logo=microsoftazure&logoColor=white)](https://raw.githubusercontent.com/malikaowerapat/azure-data-migration-to-data-lake/main/)
[![Parquet](https://img.shields.io/badge/File-Parquet-6D5DFC)](https://raw.githubusercontent.com/malikaowerapat/azure-data-migration-to-data-lake/main/)
[![Metadata Driven](https://img.shields.io/badge/Pattern-Metadata--Driven-22C55E)](https://raw.githubusercontent.com/malikaowerapat/azure-data-migration-to-data-lake/main/)
[![Incremental Load](https://img.shields.io/badge/Load-Incremental-F59E0B)](https://raw.githubusercontent.com/malikaowerapat/azure-data-migration-to-data-lake/main/)
[![Job Trigger](https://img.shields.io/badge/Automation-Schedule%20Trigger-7C3AED)](https://raw.githubusercontent.com/malikaowerapat/azure-data-migration-to-data-lake/main/)

**Created by Werapat Malikhao — Data Analyst & Data Engineering**

[🌐 **Open Interactive Demo**](https://malikaowerapat.github.io/azure-data-migration-to-data-lake/) ·
[🧭 Architecture](#architecture-at-a-glance) ·
[⏰ Trigger JSON](adf/trigger/tr_schedule_metadata_ingestion.json) ·
[🔄 Incremental SQL](sql/05_incremental_watermark_pattern.sql)

</div>

---

## Project Target

> **Move the ingestion source from the old server to a new self-hosted SQL Server, then create a standard Azure landing process that can scale to additional tables.**

| Before | Target design | Result |
|---|---|---|
| Data was read from the previous source environment | Read tables from the **new server** through **SHIR** | A secure and repeatable data path into Azure |
| One-off extraction logic would be difficult to maintain | Use one **metadata-driven ADF pipeline** | New tables can be onboarded through configuration |
| Output structure could vary between tables | Standardize **Parquet**, **Bronze**, and **Silver** paths | Consistent analytics-ready data for Power BI, SQL, or Spark |

### In one line

```text
New SQL Server → Self-hosted IR → Azure Data Factory → ADLS Gen2 Bronze → Silver → Analytics
```

---

## Architecture at a Glance

```mermaid
flowchart LR
    subgraph Private["Self-hosted Environment"]
        SQL[(New SQL Server)]
        SHIR[Self-hosted Integration Runtime]
        SQL -->|Secure read| SHIR
    end

subgraph Azure["Microsoft Azure"]
        CT[(dbo.ADF_Control_Table)]
        ADF[Azure Data Factory]
        B[(Bronze<br/>Timestamped Parquet)]
        S[(Silver<br/>Latest Usable Table)]
        CT -->|Metadata| ADF
        SHIR -->|Copy Activity| ADF
        ADF --> B
        B -->|Latest version| S
    end

S --> PBI[Power BI / SQL / Spark]
```

---

## What Problem Does This Solve?

| Engineering challenge | Design response |
|---|---|
| The source database is inside a private network | Use **SHIR** as the controlled bridge to Azure Data Factory |
| Many tables must be migrated | Drive ingestion from `dbo.ADF_Control_Table` |
| Master and transaction data behave differently | Support `FULL` and `INCREMENTAL` load types |
| SQL extracts need consistent standards | Approve reusable table/query patterns before adding them to metadata |
| Every run needs traceability | Store timestamps, status, row counts, errors, and watermark state |
| Downstream tools need efficient files | Store analytics-ready data in **Parquet** |

---

## Why SHIR?

**Self-hosted Integration Runtime is required because the SQL source is not directly exposed to Azure.**

| Reason | Practical value |
|---|---|
| Private-source connectivity | Allows ADF to reach SQL Server inside the self-hosted environment |
| Controlled network path | Avoids exposing the database directly to the public internet |
| Centralized execution bridge | The same runtime can support multiple approved data sources |
| Operational visibility | When SHIR is unavailable, the pipeline clearly fails at the connectivity layer |
| Scale and availability | Additional SHIR nodes can be introduced when higher availability is needed |

> SHIR is the **secure bridge**. ADF remains responsible for orchestration, scheduling, monitoring, and data movement logic.

---

## Why Parquet?

**Parquet is used because the destination is an analytics-oriented data lake rather than an operational transaction system.**

| Parquet capability | Why it matters |
|---|---|
| Column-oriented format | Analytics engines can read only the columns required by a query |
| Strong compression | Reduces storage usage and data scanned |
| Schema and data type support | Preserves a more reliable structure than plain text files |
| Broad analytics compatibility | Works well with Power BI, SQL engines, Spark, and lakehouse tools |
| Partition-friendly | Supports scalable folder structures such as `load_date=YYYY-MM-DD` |

---

## End-to-End Data Flow

| Step | What happens | Main component |
|---:|---|---|
| 1 | A scheduled trigger starts the published pipeline | ADF Trigger |
| 2 | Active ingestion definitions are read | `dbo.ADF_Control_Table` |
| 3 | A ForEach loop processes each table or approved query | ADF Pipeline |
| 4 | The source is accessed through the private network bridge | SHIR |
| 5 | Copy Activity extracts full or incremental rows | ADF Copy Activity |
| 6 | Each run is written as timestamped Parquet | Bronze |
| 7 | The latest usable table is published | Silver |
| 8 | Status, rows, errors, and watermark are recorded | Audit metadata |
| 9 | The output becomes available for analytics | Power BI / SQL / Spark |

---

## Metadata-Driven Control Table

The control table is the **operating center** of the framework. Instead of creating a separate pipeline for each table, the reusable pipeline reads configuration rows.

| Metadata field | Purpose |
|---|---|
| `IsActive` | Enable or disable a load without removing configuration |
| `SourceType` | Select `TABLE` or approved `QUERY` extraction |
| `SourceSchema` / `SourceObject` | Identify the source object |
| `TableGroup` | Separate `MASTER`, `TRANSACTION`, and `REFERENCE` workloads |
| `LoadType` | Select `FULL` or `INCREMENTAL` behavior |
| `WatermarkColumn` / `WatermarkValue` | Define incremental extraction boundaries |
| `SinkContainer` / `SinkDirectory` / `SinkFileName` | Standardize ADLS placement |
| `CopyPriority` / `ParallelCopies` | Control execution order and throughput |
| Audit fields | Capture status, duration, rows copied, and errors |

### Onboard a new table

```sql
INSERT INTO dbo.ADF_Control_Table
(
    SourceSystem,
    SourceType,
    SourceSchema,
    SourceObject,
    TableGroup,
    LoadType,
    SinkContainer,
    SinkDirectory,
    SinkFileName
)
VALUES
(
    'ERP_DEMO',
    'TABLE',
    'dbo',
    'WarehouseMaster',
    'MASTER',
    'FULL',
    'bronze',
    'erp/master/warehouse/',
    'warehouse_master.parquet'
);
```

**Result:** the reusable pipeline can discover the table on the next run. No dedicated pipeline is required.

---

## Bronze → Silver Data Lake Design

```text
adls-container/
├── bronze/
│   └── erp-demo/
│       └── transaction/
│           └── sales/
│               └── load_date=2026-07-22/
│                   └── run_ts=20260722010000/
│                       └── sales_transaction.parquet
│
└── silver/
    └── erp-demo/
        └── transaction/
            └── sales/
                └── current/
                    └── sales_transaction.parquet
```

### Bronze

- Keeps the output of **every ADF run**
- Includes load date and run timestamp
- Preserves history for troubleshooting and reprocessing
- Stores the source output as Parquet

### Silver

- Exposes the **latest usable version** of each table
- Provides a stable path for downstream reporting
- Reduces the need for Power BI to search multiple run folders
- Can be extended with validation, de-duplication, and business transformation rules

---

## Job Trigger & Scheduling

The reusable pipeline is separated from the trigger so scheduling can change without redesigning the ingestion logic.

| Trigger type | Recommended use |
|---|---|
| **Schedule Trigger** | Predictable daily, hourly, or weekly database ingestion |
| **Tumbling Window** | Fixed processing windows requiring retry, dependencies, or backfill |
| **Storage Event** | Start a pipeline after a supported storage event |

### Default project pattern

```text
01:00 → Schedule Trigger → Metadata Pipeline → Table Group → Bronze → Silver
```

The example trigger passes:

```json
{
  "p_TableGroup": "TRANSACTION",
  "p_ExecutionMode": "INCREMENTAL"
}
```

See [`adf/trigger/tr_schedule_metadata_ingestion.json`](adf/trigger/tr_schedule_metadata_ingestion.json).

---

## Incremental Load Strategy

For large transaction tables, the pipeline copies **only new or changed rows**.

```sql
WHERE LastModifiedDate > @OldWatermark
  AND LastModifiedDate <= @NewWatermark
```

### Watermark sequence

1. Read the previous successful watermark.
2. Capture a fixed upper boundary from the source.
3. Copy only rows inside the extraction window.
4. Write delta rows to the timestamped Bronze path.
5. Publish the latest usable table to Silver.
6. Update the watermark **only after Copy Activity succeeds**.

| Use `FULL` when | Use `INCREMENTAL` when |
|---|---|
| The table is small | The table is large or grows continuously |
| It is a master/reference table | It is a transaction or event table |
| No reliable change column exists | A stable timestamp, sequence, identity, CDC, or change-tracking field exists |

See [`sql/05_incremental_watermark_pattern.sql`](sql/05_incremental_watermark_pattern.sql).

---

## Table and Query Standards

<details>
<summary><strong>Standard table extraction</strong></summary>

- Use metadata to identify schema and object.
- Keep source and sink datasets parameterized.
- Avoid creating duplicate ADF datasets for every table.
- Apply table-group and load-type rules from the control table.

</details>

<details>
<summary><strong>Standard SQL query extraction</strong></summary>

Queries should be written and validated in SSMS before approval.

- Use explicit column lists rather than `SELECT *`
- Use stable business names and data types
- Apply SARGable incremental filters
- Define deterministic watermark boundaries
- Avoid credentials, environment-specific secrets, and destructive SQL

See [`sql/04_standard_query_template.sql`](sql/04_standard_query_template.sql).

</details>

---

## Reliability, Monitoring & Security

| Area | Design consideration |
|---|---|
| SHIR availability | The pipeline cannot reach the private source when the SHIR node is offline |
| Authentication | Use managed identity or Key Vault-backed secrets where possible |
| Retry and timeout | Configure activity-level retry and timeout policies |
| Watermark safety | Update the watermark only after a successful copy |
| High availability | Use multiple SHIR nodes when required |
| Least privilege | Restrict ADF, SQL, and ADLS permissions to the minimum required |
| Public GitHub safety | Never commit server names, credentials, connection strings, SAS tokens, access keys, or SHIR keys |

> This repository uses sanitized demo names. Organization-specific identifiers and confidential data are intentionally excluded.

---

## What I Built

- Analyzed the new source server and table extraction requirements
- Configured the secure data path through SHIR
- Designed reusable ADF source and sink parameters
- Built a metadata-driven table and query ingestion pattern
- Standardized master, transaction, reference, full, and incremental behavior
- Designed Bronze and Silver storage conventions
- Added schedule-trigger and trigger-monitoring concepts
- Designed a watermark-based incremental load pattern
- Documented failure behavior when SHIR or the source is unavailable
- Prepared the output for Power BI and other analytics tools

---

## Recruiter Snapshot

| Capability | Evidence in this project |
|---|---|
| Azure orchestration | Parameterized ADF pipeline and schedule trigger |
| Private network integration | Self-hosted Integration Runtime |
| Scalable ingestion | Metadata/control-table-driven pattern |
| SQL engineering | Standard table/query extraction and watermark filters |
| Data lake design | Bronze/Silver hierarchy with Parquet |
| Automation | Scheduled job execution and repeatable onboarding |
| Reliability | Audit status, error handling, and safe watermark updates |
| Analytics readiness | Stable Silver output for Power BI, SQL, and Spark |

> **Key takeaway:** This is not a one-off Copy Activity. It is a maintainable ingestion framework designed for secure connectivity, repeatable onboarding, incremental loading, standardized storage, and operational visibility.

---

## Repository Structure

```text
.
├── index.html
├── README.md
├── assets/
│   ├── readme-cover.png
│   ├── architecture.png
│   └── werapat-profile.jpg
├── adf/
│   └── trigger/
│       └── tr_schedule_metadata_ingestion.json
├── sql/
│   ├── 04_standard_query_template.sql
│   └── 05_incremental_watermark_pattern.sql
├── docs/
│   └── job-trigger-and-incremental-load.md
└── PROFILE_SNIPPET.md
```

---

## Technology Stack

`Azure Data Factory` · `Self-hosted Integration Runtime` · `SQL Server` · `SSMS` · `ADLS Gen2` · `Parquet` · `Metadata-Driven Pipelines` · `Parameterized Datasets` · `Schedule Trigger` · `Incremental Loading` · `Watermarking` · `Operational Logging` · `Bronze / Silver Design` · `Power BI`

---

## Run the Demo Locally

Open `index.html` directly, or run:

```bash
python -m http.server 8000
```

Then open:

```text
http://localhost:8000
```

---

## Publish with GitHub Pages

1. Upload all files to the repository root.
2. Open **Settings → Pages**.
3. Select **Deploy from a branch**.
4. Choose `main` and `/ (root)`.
5. Save and wait for deployment.

Expected URL:

```text
https://malikaowerapat.github.io/azure-data-migration-to-data-lake/
```

---

## Author

**Werapat Malikhao (Boss)**  
Data Analyst · Power BI Specialist · Data Engineering

This project demonstrates how I approach a real data migration requirement: understand the source constraint, design the data path, standardize the framework, automate execution, and prepare trusted data for analytics.

</td>
</tr>
</table>

---

## References

- [Microsoft Learn — Pipeline execution and triggers](https://learn.microsoft.com/azure/data-factory/concepts-pipeline-execution-triggers)
- [Microsoft Learn — Create a schedule trigger](https://learn.microsoft.com/azure/data-factory/how-to-create-schedule-trigger)
- [Microsoft Learn — Incremental copy overview](https://learn.microsoft.com/azure/data-factory/tutorial-incremental-copy-overview)
- [Microsoft Learn — Metadata-driven data copy](https://learn.microsoft.com/azure/data-factory/copy-data-tool-metadata-driven)

### Secure source connectivity · Reusable orchestration · Standardized lake storage · Analytics-ready output

</div>

azure-data-migration-to-data-lake

azure-data-migration-to-data-lake 최신버젼 다운로드

오늘의 추천 프로젝트

Was this content helpful?

Recent Feedback

azure-data-migration-to-data-lake

azure-data-migration-to-data-lake 최신버젼 다운로드

오늘의 추천 프로젝트

Was this content helpful?

Recent Feedback

Download README