Real-world data movement and orchestration patterns using ...

Real-world data movement and orchestration patterns using ...

Learn. Connect. Explore. BRK2279 Real-World Data Movement and Orchestration Patterns

using Azure Data Factory Jason Horner, Attunix - @jasonhorner Cathrine Wilhelmsen, Inmeta - @cathrinew Agenda Overview

Design Patterns Preview of? Overview of Azure Data Factory

Azure Data Factory Sources Data Warehouse ETL / ELT

Analysis Reporting Azure Data Factory ETL / ELT Visual UI

Drag and Drop Code Support Python, .NET, ARM Control Flow Loop, Branch, If

SSIS Execution Lift and Shift ETL / ELT ETL - Extract Transform Load ETL - Extract Transform Load

ETL - Extract Transform Load ETL - Extract Transform Load ETL - Extract Transform Load ETL - Extract Transform Load

ELT - Extract Load Transform ELT - Extract Load Transform ELT - Extract Load Transform ELT - Extract Load Transform

ELT - Extract Load Transform ELT - Extract Load Transform ELT ETL

Azure Data Factory Concepts Azure Data Factory Concepts Pipelines Activities Linked

Services Datasets Triggers Integration Runtime

Azure Data Factory Design Patterns What are Design Patterns? Reusable solutions for common problems:

Description or template Formalized best practices Not finished designs that can be transformed directly into source or machine code Why use Design Patterns?

Use tested, proven and documented solutions to: Speed up development

Prevent issues than can cause problems later Improve code readability Design Patterns 1. 2. 3.

4. Truncate and Load Merge Load Incremental Load Bulk Table Transfer Full Extract: Truncate and Load

Specific use cases: All data needed, but replication is not available Small data sets that change often

No historical requirements Very simple, but can be considered an antipattern Full Extract: Truncate and Load Source

Sink Source Table Sink Table Full Extract: Merge Load

Specific use cases: All data needed, but replication is not available Medium data sets that have few changes

Need to minimize churn on the staging tables Adds complexity, doesnt solve the incremental extract from source Full Extract: Merge Load Source Source

Table Sink Table Type Stored Procedure Sink Table

Incremental Load Specific use cases: All data needed, including a robust history

Large data sets that have many changes Need to minimize churn on the staging tables and load on source systems Often requires changes to the source system (triggers, added columns, or engine features) Incremental Load

Source Sink Source Table Change Table

Change Tracking Current Version Table Type Stored

Procedure Control Table (High Watermark) Sink Table

Delta Detection Hash Comparison (Full Extract) High Watermark (Incremental Load) Change Tracking (Incremental Load) Other: column-by-column comparison, triggers, row versioning, modified dates, temporal

tables Delta Detection: High BE W ARY thes Watermark e appro of

ache s! Based on ascending integer or datetime Store the highest value in a control table or calculate by SELECT MAX() FROM Table Based on ascending date

Update or Create Assumes data is not updated and that the dates are maintained automatically Delta Detection: Change Tracking Lightweight solution for tracking data changes:

Has a row changed? Which rows have been changed? What kind of change was it? Which columns were changed?

Only tracks the latest change to a row Bulk Table Transfer Specific use cases:

Hundreds to thousands of tables to copy Similar loading patterns for all tables Need to minimize amount of code in solution Adds complexity, requires database tables to manage state

Bulk Table Transfer Source Source Table Sink Table Type

Stored Procedure Control Table List Sink Table Log Table

Auditing: Batches Every ETL Process should start by creating a Batch Batches are logical concepts used to tie multipipeline load processes together for Auditing and Logging A batch is closed when a nightly process is completed (Fail or Success)

Auditing: Common Columns CreatedDate - Date row was inserted CreatedBatchId - Batch that inserted row ModifiedDate - Date row was updated ModfiedBatchId - Batch that updated row IsDeleted - Indicates if record has been removed

Logging: Common Columns Row Counts - Selected, Inserted, Modified, Ignored ExecutionTime - Begin, End, Duration LoadStatus - Fail, Success Demo:

Solution Overview Jason Horner Design Patterns: Key Take Aways Model your Metadata correctly Make composable single purpose Pipelines Leverage Parameters and User Properties Lookup, Foreach, and Metadata, activities are powerful

Edit the JSON files directly when you hit a wall Preview of? Azure Data Factory Data Flows

Azure Data Factory Data Flows ETL / ELT Visual Authoring Drag and Drop Azure Databricks No Code

Transform At Scale Join, Split, Aggregate, Lookup, Filter, Sort, Derived Column Azure Data Factory Data Flows ETL / ELT

Demo: Azure Data Factory Data Flows Cathrine Wilhelmsen Thank you! Jason Horner, Attunix [email protected]

@jasonhorner Cathrine Wilhelmsen, Inmeta [email protected] @cathrinew Please evaluate this

session Your feedback is important to us! Please evaluate this session through MyEvaluations on the mobile app or website. Download the app: https://aka.ms/ignite.mobileApp

Go to the website: https://myignite.techcommunity.microsoft.com/evalu ations Copyright Microsoft Corporation. All rights reserved.

Recently Viewed Presentations

  • Writing I

    Writing I

    Summarize/paraphrase passages Quoting Sources (in brief) Use quotations sparingly and strategically. Use quotations only when the language is so unique that you must use it; that is, the language adds "color, power, or character," to your project. ... Quote use...
  • Do Now  name and count the atoms 1.

    Do Now name and count the atoms 1.

    Title: Endo vs Exo. L.O. SWBAT determine if a reaction is endothermic or exothermic. Chemical Bond Review - Polar vs Non Polar. ... What is the difference between endothermic and exothermic reactions? Give an example of an endothermic reaction. Give...
  • Response to Carbon Monoxide Incidents Firefighter III Scott

    Response to Carbon Monoxide Incidents Firefighter III Scott

    Response to Carbon Monoxide Incidents Firefighter III Scott Sanders Overview Introduction Carbon Monoxide (CO) Properties Possible sources of CO CO Health Hazards CO levels - what they mean Initial response procedures Atmospheric monitoring equipment Carbon Monoxide Detectors Introduction Purpose: Familiarize...
  • Kepler's Laws of Planetary Motion - Kawameeh Middle School

    Kepler's Laws of Planetary Motion - Kawameeh Middle School

    of the planets in our solar system. ... Kepler's 1st Law. Orbits of planets are ellipses with the Sun at one focus. **Aphelion is the point on the orbit furthest to the Sun **Perihelion is the point on the orbit...
  • Year 10: Physical Education

    Year 10: Physical Education

    Topics Covered . Reading and Analysing Poetry Students study an anthology of curated poems from classic and contemporary forms.Students analyse features and conventions of specific poetry, imagery and different interpretations in order to produce their own poetry recording and a...
  • Figure 10.9B_3

    Figure 10.9B_3

    (2) The big res dpi gat eth ere dra g. (3) The big rep iga tet her edr ag. 2. The authors have noted elsewhere that "A random mutation is like a random shot in the dark. It is not...
  • "Inner Eurasia As A Unit Of World History"

    "Inner Eurasia As A Unit Of World History"

    "Inner Eurasia" As One Of The Basic Units Of Eurasian And World History. Ever since history emerged as a distinct discipline in nineteenth-century Europe, most historians have treated the national state as their main unit of analysis
  • A comparison of ACF fitting methods A.J. Ribeiro

    A comparison of ACF fitting methods A.J. Ribeiro

    FITACF is the most suitable method for fitting ACFs during runtime at the radars. FITEX2 is capable for fitting velocities without bias and with reasonable errors, but it is slower. 1 full day of Themis-tauscan data takes ~ 15 seconds...