Ingestion, ETL, and Stream Processing with Azure Databricks

Published Nov 30 2020 06:00 AM 7,476 Views

This post is part of a multi-part series titled "Patterns with Azure Databricks".  Each highlighted pattern holds true to the key principles of building a Lakehouse architecture with Azure Databricks:

 

MikeCornell-Databricks_1-1608076592474.png

 

 

  1. A Data Lake to store all data, with a curated layer in an open-source format.  The format should support ACID transactions for reliability and should also be optimized for efficient queries.  This is done with Azure Data Lake Store plus Delta Lake.
  2. A foundational compute layer built on open standards.  The foundational compute Layer should support most core use cases for the Data Lake including curated data lake (ETL and stream processing), data science and ML, and SQL analytics on the data lake.  Azure Databricks provides these capabilities using open standards that ensure rapid innovation and are non-locking and future proof.
  3. Easy integration for additional and/or new use cases.  No single service can do everything.  There are always going to be new or additional use cases that aren't core to the Lakehouse.  This is where easy integrations between the core Lakehouse services and other Azure data services and tools ensure that any analytics use case can be tackled.

Pattern for Ingestion, ETL, and Stream Processing

Companies need to ingest data in any format, of any size, and at any speed into the cloud in a consistent and repeatable way. Once that data is ingested into the cloud, it needs to be moved into the open, curated data lake, where it can be processed further to be used by high value use cases such as SQL analytics, BI, reporting, and data science and machine learning.

 

MikeCornell_0-1606337288033.png

 

The diagram above demonstrates a common pattern used by many companies to ingest and process data of all types, sizes, and speed into a curated data lake.  Let's look at the 3 major components of the pattern:

 

  1. There are several great tools in Azure for ingesting raw data from external sources into the cloud.  Azure Data Factory provides the standard for importing data on a schedule or trigger from almost any data source and landing it in its raw format into Azure Data Lake Storage/Blob Storage.  Other services such as Azure IoT Hub and Azure Event Hubs provide fully managed services for real time ingestion.  Using a mix of Azure Data Factory and Azure IoT/Event Hubs should allow a company to get data of just about any type, size, and speed into Azure. 

    MikeCornell_0-1606339083241.png

  2. After landing the raw data into Azure, companies typically move it into the raw, or Bronze, layer of the curated data lake.  This usually means just taking the data in its raw, source format, and converting it to the open, transactional Delta Lake format where it can be more efficiently and reliably queried and processed.  Ingesting the data into the Bronze curated layer can be done in a number of ways including: 
     

     

    MikeCornell_0-1606339528397.png

     
    1. Basic, open Apache Spark APIs in Azure Databricks for reading streaming events from Event/IoT Hubs and then writing those events or raw files to the Delta Lake format.
    2. The COPY INTO command to easily copy data from a source file/directory directly into Delta Lake.
    3. The Azure Databricks Auto Loader to efficiently grab files as they arrive in the data lake and write them to the Delta Lake format.
    4. The Azure Data Factory Copy Activity which supports copying data from any of its supported formats into the Delta Lake format.
       

       

  3. After the raw data has been ingested to the Bronze layer, companies perform additional ETL and stream processing tasks to filter, clean, transform, join, and aggregate the data into more curated Silver and Gold datasets. Using Azure Databricks as the foundational service for these processing tasks provides companies with a single, consistent compute engine (the Delta Engine) built on open standards with support for programming languages they are already familiar with (SQL, Python, R, Scala).  It also provides them with repeatable DevOps processes and ephemeral compute clusters sized to their individual workloads. 

    MikeCornell_0-1606340949593.png

     

The ingestion, ETL, and stream processing pattern discussed above has been used successfully with many different companies across many different industries and verticals.  It also holds true to the key principles discussed for building Lakehouse architecture with Azure Databricks: 1) using an open, curated data lake for all data (Delta Lake), 2) using a foundational compute layer built on open standards for the core ETL and stream processing (Azure Databricks), and 3) using easy integrations with other services like Azure Data Factory and IoT/Event Hubs which specialize in ingesting data into the cloud.

 

If you are interested in learning more about Azure Databricks, attend an event, and check back soon for additional blogs in the "Patterns with Azure Databricks" series.

1 Comment
Visitor

Great article Mike, thanks very much.

%3CLINGO-SUB%20id%3D%22lingo-sub-1932258%22%20slang%3D%22en-US%22%3EIngestion%2C%20ETL%2C%20and%20Stream%20Processing%20with%20Azure%20Databricks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1932258%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20post%20is%20part%20of%20a%20multi-part%20series%20titled%20%22Patterns%20with%20Azure%20Databricks%22.%26nbsp%3B%20Each%20highlighted%20pattern%20holds%20true%20to%20the%20key%20principles%20of%20building%20a%20Lakehouse%20architecture%20with%20Azure%20Databricks%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22MikeCornell-Databricks_1-1608076592474.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F240925iC9D96F21D80F82D3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22MikeCornell-Databricks_1-1608076592474.png%22%20alt%3D%22MikeCornell-Databricks_1-1608076592474.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3E%3CSTRONG%3EA%20Data%20Lake%20to%20store%20all%20data%2C%20with%20a%20curated%20layer%20in%20an%20open-source%20format.%3C%2FSTRONG%3E%26nbsp%3B%20The%20format%20should%20support%20ACID%20transactions%20for%20reliability%20and%20should%20also%20be%20optimized%20for%20efficient%20queries.%26nbsp%3B%20This%20is%20done%20with%20Azure%20Data%20Lake%20Store%20plus%20Delta%20Lake.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EA%20foundational%20compute%20layer%20built%20on%20open%20standards.%3C%2FSTRONG%3E%26nbsp%3B%20The%20foundational%20compute%20Layer%20should%20support%20%3CSTRONG%3Emost%20core%3C%2FSTRONG%3E%20use%20cases%20for%20the%20Data%20Lake%20including%20curated%20data%20lake%20(ETL%20and%20stream%20processing)%2C%20data%20science%20and%20ML%2C%20and%20SQL%20analytics%20on%20the%20data%20lake.%26nbsp%3B%20Azure%20Databricks%20provides%20these%20capabilities%20using%20open%20standards%20that%20ensure%20rapid%20innovation%20and%20are%20non-locking%20and%20future%20proof.%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EEasy%20integration%20for%20additional%20and%2For%20new%20use%20cases.%3C%2FSTRONG%3E%26nbsp%3B%20No%20single%20service%20can%20do%20everything.%26nbsp%3B%20There%20are%20always%20going%20to%20be%20new%20or%20additional%20use%20cases%20that%20aren't%20core%20to%20the%20Lakehouse.%26nbsp%3B%20This%20is%20where%20easy%20integrations%20between%20the%20core%20Lakehouse%20services%20and%20other%20Azure%20data%20services%20and%20tools%20ensure%20that%20any%20analytics%20use%20case%20can%20be%20tackled.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CH2%20id%3D%22toc-hId--1184600916%22%20id%3D%22toc-hId--1184600916%22%3EPattern%20for%20Ingestion%2C%20ETL%2C%20and%20Stream%20Processing%3C%2FH2%3E%0A%3CP%3ECompanies%20need%20to%20ingest%20data%20in%20any%20format%2C%20of%20any%20size%2C%20and%20at%20any%20speed%20into%20the%20cloud%20in%20a%20consistent%20and%20repeatable%20way.%20Once%20that%20data%20is%20ingested%20into%20the%20cloud%2C%20it%20needs%20to%20be%20moved%20into%20the%20open%2C%20curated%20data%20lake%2C%20where%20it%20can%20be%20processed%20further%20to%20be%20used%20by%20high%20value%20use%20cases%20such%20as%20SQL%20analytics%2C%20BI%2C%20reporting%2C%20and%20data%20science%20and%20machine%20learning.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MikeCornell_0-1606337288033.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236130i31FB17600BDA9E13%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22MikeCornell_0-1606337288033.png%22%20alt%3D%22MikeCornell_0-1606337288033.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20diagram%20above%20demonstrates%20a%20common%20pattern%20used%20by%20many%20companies%20to%20ingest%20and%20process%20data%20of%20all%20types%2C%20sizes%2C%20and%20speed%20into%20a%20curated%20data%20lake.%26nbsp%3B%20Let's%20look%20at%20the%203%20major%20components%20of%20the%20pattern%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3EThere%20are%20several%20great%20tools%20in%20Azure%20for%20ingesting%20raw%20data%20from%20external%20sources%20into%20the%20cloud.%26nbsp%3B%20Azure%20Data%20Factory%20provides%20the%20standard%20for%20importing%20data%20on%20a%20schedule%20or%20trigger%20from%20almost%20any%20data%20source%20and%20landing%20it%20in%20its%20raw%20format%20%3CSPAN%3Einto%20Azure%20Data%20Lake%20Storage%2FBlob%20Storage%3C%2FSPAN%3E.%26nbsp%3B%20Other%20services%20such%20as%20Azure%20IoT%20Hub%20and%20Azure%20Event%20Hubs%20provide%20fully%20managed%20services%20for%20real%20time%20ingestion.%26nbsp%3B%20Using%20a%20mix%20of%20Azure%20Data%20Factory%20and%20Azure%20IoT%2FEvent%20Hubs%20should%20allow%20a%20company%20to%20get%20data%20of%20just%20about%20any%20type%2C%20size%2C%20and%20speed%20into%20Azure.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MikeCornell_0-1606339083241.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236142iD42A7DB1CDBB95A0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MikeCornell_0-1606339083241.png%22%20alt%3D%22MikeCornell_0-1606339083241.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FLI%3E%0A%3CLI%3EAfter%20landing%20the%20raw%20data%20into%20Azure%2C%20companies%20typically%20move%20it%20into%20the%20raw%2C%20or%20Bronze%2C%20layer%20of%20the%20curated%20data%20lake.%26nbsp%3B%20This%20usually%20means%20just%20taking%20the%20data%20in%20its%20raw%2C%20source%20format%2C%20and%20converting%20it%20to%20the%20open%2C%20transactional%20Delta%20Lake%20format%20where%20it%20can%20be%20more%20efficiently%20and%20reliably%20queried%20and%20processed.%26nbsp%3B%20Ingesting%20the%20data%20into%20the%20Bronze%20curated%20layer%20can%20be%20done%20in%20a%20number%20of%20ways%20including%3A%26nbsp%3B%3CDIV%20id%3D%22tinyMceEditorMikeCornell_3%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Delta%20Ingest%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236144iF03E52A9FEEEB592%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MikeCornell_0-1606339528397.png%22%20alt%3D%22MikeCornell_0-1606339528397.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorMikeCornell_1%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3COL%20class%3D%22lia-list-style-type-lower-alpha%22%3E%0A%3CLI%3EBasic%2C%20open%20Apache%20Spark%20APIs%20in%20Azure%20Databricks%20for%20reading%20streaming%20events%20from%20Event%2FIoT%20Hubs%20and%20then%20writing%20those%20events%20or%20raw%20files%20to%20the%20Delta%20Lake%20format.%3C%2FLI%3E%0A%3CLI%3EThe%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F33hvGsg%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3ECOPY%20INTO%20command%3C%2FA%3E%20to%20easily%20copy%20data%20from%20a%20source%20file%2Fdirectory%20directly%20into%20Delta%20Lake.%3C%2FLI%3E%0A%3CLI%3EThe%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3l8L3sS%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EAzure%20Databricks%20Auto%20Loader%3C%2FA%3E%20%3CSPAN%3Eto%20efficiently%20grab%20files%20as%20they%20arrive%20in%20the%20data%20lake%20and%20write%3C%2FSPAN%3E%20them%20to%20the%20Delta%20Lake%20format.%3C%2FLI%3E%0A%3CLI%3EThe%20%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3q0QCgP%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EAzure%20Data%20Factory%20Copy%20Activity%3C%2FA%3E%20which%20supports%20copying%20data%20from%20any%20of%20its%20supported%20formats%20into%20the%20Delta%20Lake%20format.%3CDIV%20id%3D%22tinyMceEditorMikeCornell_2%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3C%2FLI%3E%0A%3CLI%3EAfter%20the%20raw%20data%20has%20been%20ingested%20to%20the%20Bronze%20layer%2C%20companies%20perform%20additional%20ETL%20and%20stream%20processing%20tasks%20to%20filter%2C%20clean%2C%20transform%2C%20join%2C%20and%20aggregate%20the%20data%20into%20more%20curated%20Silver%20and%20Gold%20datasets.%20Using%20Azure%20Databricks%20as%20the%20foundational%20service%20for%20these%20processing%20tasks%20provides%20companies%20with%20a%20single%2C%20consistent%20compute%20engine%20(%3CA%20href%3D%22https%3A%2F%2Fdbricks.co%2F3fsw0ZX%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ethe%20Delta%20Engine%3C%2FA%3E)%20built%20on%20open%20standards%20with%20support%20for%20programming%20languages%20they%20are%20already%20familiar%20with%20(SQL%2C%20Python%2C%20R%2C%20Scala).%26nbsp%3B%20It%20also%20provides%20them%20with%20repeatable%20DevOps%20processes%20and%20ephemeral%20compute%20clusters%20sized%20to%20their%20individual%20workloads.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MikeCornell_0-1606340949593.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236145i88B7310F99868941%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MikeCornell_0-1606340949593.png%22%20alt%3D%22MikeCornell_0-1606340949593.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%3EThe%20ingestion%2C%20ETL%2C%20and%20stream%20processing%20pattern%20discussed%20above%20has%20been%20used%20successfully%20with%20many%20different%20companies%20across%20many%20different%20industries%20and%20verticals.%26nbsp%3B%20It%20also%20holds%20true%20to%20the%20key%20principles%20discussed%20for%20building%20Lakehouse%20architecture%20with%20Azure%20Databricks%3A%201)%20using%20an%20open%2C%20curated%20data%20lake%20for%20all%20data%20(Delta%20Lake)%2C%202)%20using%20a%20foundational%20compute%20layer%20built%20on%20open%20standards%20for%20the%20core%20ETL%20and%20stream%20processing%20(Azure%20Databricks)%2C%20and%203)%20using%20easy%20integrations%20with%20other%20services%20like%20Azure%20Data%20Factory%20and%20IoT%2FEvent%20Hubs%20which%20specialize%20in%20ingesting%20data%20into%20the%20cloud.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20interested%20in%20learning%20more%20about%20Azure%20Databricks%2C%20%3CA%20href%3D%22https%3A%2F%2Fdatabricks.com%2Fazure-databricks-events%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Eattend%20an%20event%3C%2FA%3E%2C%20and%20check%20back%20soon%20for%20additional%20blogs%20in%20the%20%22Patterns%20with%20Azure%20Databricks%22%20series.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1932258%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MikeCornell-Databricks_0-1609947208581.png%22%20style%3D%22width%3A%20436px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F244545i68C0A9A9ED58830D%2Fimage-dimensions%2F436x194%3Fv%3D1.0%22%20width%3D%22436%22%20height%3D%22194%22%20role%3D%22button%22%20title%3D%22MikeCornell-Databricks_0-1609947208581.png%22%20alt%3D%22MikeCornell-Databricks_0-1609947208581.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20how%20Azure%20Databricks%20can%20be%20used%20to%20ingest%20and%20process%20data%20of%20any%20type%2C%20any%20size%2C%20and%20at%20any%20speed%20into%20a%20curated%20data%20lake.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1932258%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAnalytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Databricks%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDelta%20Lake%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMachine%20Learning%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMlFlow%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 06 2021 07:33 AM