<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Ilse Epskamp on Medium]]></title>
        <description><![CDATA[Stories by Ilse Epskamp on Medium]]></description>
        <link>https://medium.com/@ilse-epskamp?source=rss-e1dde29db640------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*KtCm2nZmgsqk2BZ97I9nVw.png</url>
            <title>Stories by Ilse Epskamp on Medium</title>
            <link>https://medium.com/@ilse-epskamp?source=rss-e1dde29db640------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 20:01:51 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@ilse-epskamp/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[SQL operations you might need one day]]></title>
            <link>https://ilse-epskamp.medium.com/sql-operations-you-might-need-one-day-6a924cca1bb8?source=rss-e1dde29db640------2</link>
            <guid isPermaLink="false">https://medium.com/p/6a924cca1bb8</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Ilse Epskamp]]></dc:creator>
            <pubDate>Thu, 02 May 2024 20:35:09 GMT</pubDate>
            <atom:updated>2025-06-22T14:46:39.881Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*31BTMtWUM_W-pmhn" /><figcaption>Photo by <a href="https://unsplash.com/@fabioha?utm_source=medium&amp;utm_medium=referral">fabio</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>If you work with data you will most likely use SQL every now and then. If data is not populated in your SQL tables according to basic normalization standards, you might find yourself struggling to find the correct SQL syntax to handle the data properly. For example, multiple values could be stored in a column separated by a comma, or you need to do some date conversions or calculations. In my work as a data engineer I’ve encountered several scenarios like this and thought of sharing a somewhat random collection of SQL operations to handle such scenarios.</p><p>In this blog:</p><ul><li>Use SUBSTRING() to split values on whitespace and take the value of the first position</li><li>Use ROWNUMBER() to select the latest version of an entity by last update date</li><li>Use COALESCE() to assign a true/false indicator based on occurrence of a value in another table</li><li>Use STRING_AGG() to convert a 1-to-many structure into a single column with comma separated values</li></ul><p><strong>Use SUBSTRING() to split values on whitespace and take the value of the first position</strong></p><p>Scenario: The first and last name of a person is stored in a single column. You need to select the first name. NB in this example I assume the first name has no whitespace.</p><p><em>Table data:</em></p><pre>+-----+----------------+<br>| ID  |      NAME      |<br>+-----+----------------+<br>| 123 | Keanu Reeves   |<br>| 456 | Sandra Bullock |<br>+-----+----------------+</pre><p><em>SQL:</em></p><pre>SELECT<br>ID,<br>NAME,<br>SUBSTRING(NAME,0,CHARINDEX(&#39; &#39;, NAME)) as FIRST_NAME<br>FROM table</pre><p><em>Returns:</em></p><pre><br>+-----+----------------+------------+<br>| ID  |      NAME      | FIRST_NAME |<br>+-----+----------------+------------+<br>| 123 | Keanu Reeves   | Keanu      |<br>| 456 | Sandra Bullock | Sandra     |<br>+-----+----------------+------------+</pre><p><strong>Use ROWNUMBER() to select the latest version of an entity by last update date</strong></p><p>Scenario: An item has an owner, which changed over time. You need to select the current owner based on latest update date.</p><p><em>Table data:</em></p><pre>+---------+------------+------------------+<br>| ITEM_ID | OWNER_NAME | DATE_LAST_UPDATE |<br>+---------+------------+------------------+<br>|       1 | James      |         20220101 |<br>|       1 | Noah       |         20220305 |<br>|       1 | Kelly      |         20230512 |<br>|       2 | William    |         20230601 |<br>|       2 | Patricia   |         20231001 |<br>|       2 | David      |         20240206 |<br>+---------+------------+------------------+</pre><p><em>SQL:</em></p><p>First rank by date:</p><pre>SELECT<br>ITEM_ID,<br>OWNER_NAME,<br>DATE_LAST_UPDATE,<br>ROW_NUMBER() OVER (PARTITION BY ITEM_ID ORDER BY DATE_LAST_UPDATE DESC) as DATE_LAST_UPDATED_RANKED<br>FROM table</pre><p><em>Returns:</em></p><pre>+---------+------------+------------------+-------------------------+<br>| ITEM_ID | OWNER_NAME | DATE_LAST_UPDATE | DATE_LAST_UPDATE_RANKED |<br>+---------+------------+------------------+-------------------------+<br>|       1 | James      |         20220101 |                       3 |<br>|       1 | Noah       |         20220305 |                       2 |<br>|       1 | Kelly      |         20230512 |                       1 |<br>|       2 | William    |         20230601 |                       3 |<br>|       2 | Patricia   |         20231001 |                       2 |<br>|       2 | David      |         20240206 |                       1 |<br>+---------+------------+------------------+-------------------------+</pre><p>Then select the records where DATE_LAST_UPDATE_RANKED=1. <br>Full query:</p><pre>WITH<br><br>item_owners_ranked_by_date AS (<br>  SELECT<br>  ITEM_ID,<br>  OWNER_NAME,<br>  DATE_LAST_UPDATE,<br>  ROW_NUMBER() OVER (PARTITION BY ITEM_ID ORDER BY DATE_LAST_UPDATE DESC) as DATE_LAST_UPDATED_RANKED<br>  FROM table<br>)<br><br>SELECT<br>ITEM_ID,<br>OWNER_NAME,<br>DATE_LAST_UPDATE<br>FROM item_owners_ranked_by_date<br>WHERE DATE_LAST_UPDATED_RANKED=1</pre><p><em>Returns:</em></p><pre>+---------+------------+------------------+<br>| ITEM_ID | OWNER_NAME | DATE_LAST_UPDATE |<br>+---------+------------+------------------+<br>|       1 | Kelly      |         20230512 |<br>|       2 | David      |         20240206 |<br>+---------+------------+------------------+</pre><p><strong>Use COALESCE() to assign a true/false indicator based on occurrence of a value in another table</strong></p><p>Scenario: let’s say you have a table with IDs called table_valid_ids. The IDs in that table are considered to be valid. You have another table called all_ids, and need to add a valid indicator column. In case the ID occurs in table_valid_ids, you assign 1, else 0.</p><p><em>Table data:</em></p><pre>all_ids<br><br>+----+<br>| ID |<br>+----+<br>|  1 |<br>|  2 |<br>|  3 |<br>|  4 |<br>|  5 |<br>|  6 |<br>|  7 |<br>|  8 |<br>|  9 |<br>| 10 |<br>| 11 |<br>| 12 |<br>| 13 |<br>| 14 |<br>| 15 |<br>| 16 |<br>+----+<br><br><br>valid_ids<br><br>+----+<br>| ID |<br>+----+<br>|  1 |<br>|  2 |<br>|  5 |<br>|  6 |<br>|  8 |<br>|  9 |<br>| 10 |<br>| 12 |<br>| 16 |<br>+----+</pre><p><em>SQL:</em></p><pre><br>SELECT<br>ID, <br>COALESCE((SELECT DISTINCT(&#39;1&#39;) FROM valid_ids WHERE ID = a.ID), &#39;0&#39;) as VALID_INDICATOR<br>FROM all_ids a</pre><p><em>Returns:</em></p><pre>+----+-----------------+<br>| ID | VALID_INDICATOR |<br>+----+-----------------+<br>|  1 |               1 |<br>|  2 |               1 |<br>|  3 |               0 |<br>|  4 |               0 |<br>|  5 |               1 |<br>|  6 |               1 |<br>|  7 |               0 |<br>|  8 |               1 |<br>|  9 |               1 |<br>| 10 |               1 |<br>| 11 |               0 |<br>| 12 |               1 |<br>| 13 |               0 |<br>| 14 |               0 |<br>| 15 |               0 |<br>| 16 |               1 |<br>+----+-----------------+</pre><p><strong>Use STRING_AGG() to convert a 1-to-many structure into a single column with comma separated values</strong></p><p>Scenario: you have a table with a 1-many relation and need to convert this structure into a list, so the result returns a single records per ID.</p><p><em>Table data:</em></p><pre>+---------+-------+<br>| ITEM_ID | USER  |<br>+---------+-------+<br>|       1 | Jane  |<br>|       1 | Kevin |<br>|       2 | John  |<br>|       2 | David |<br>|       3 | Kat   |<br>|       3 | Lucas |<br>|       3 | Noah  |<br>+---------+-------+</pre><p><em>SQL:</em></p><pre>SELECT <br>ITEM_ID,<br>STRING_AGG([USER], &#39;,&#39;) WITHIN GROUP (ORDER BY [USER]) AS userList <br>FROM users_per_item<br>GROUP BY ITEM_ID</pre><p><em>Returns:</em></p><pre>+---------+----------------+<br>| ITEM_ID |    userList    |<br>+---------+----------------+<br>|       1 | Jane,Kevin     |<br>|       2 | David,John     |<br>|       3 | Kat,Lucas,Noah |<br>+---------+----------------+</pre><p>NB: The equivalent function for STRING_AGG() in SparkSQL is array_join():</p><pre> array_join(collect_set(USER), &#39;,&#39;) userList</pre><p>Happy data engineering!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6a924cca1bb8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Run integration test between Data Factory and Databricks in Azure DevOps CI pipeline]]></title>
            <link>https://ilse-epskamp.medium.com/run-integration-test-between-data-factory-and-databricks-in-azure-devops-ci-pipeline-c8b73d118dd0?source=rss-e1dde29db640------2</link>
            <guid isPermaLink="false">https://medium.com/p/c8b73d118dd0</guid>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[ci-cd-pipeline]]></category>
            <category><![CDATA[integration-testing]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[azure-devops]]></category>
            <dc:creator><![CDATA[Ilse Epskamp]]></dc:creator>
            <pubDate>Thu, 02 May 2024 19:31:33 GMT</pubDate>
            <atom:updated>2024-05-02T19:31:33.674Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*pLS1FCw3SNgpNAXq" /><figcaption>Photo by <a href="https://unsplash.com/@timmossholder?utm_source=medium&amp;utm_medium=referral">Tim Mossholder</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>A common setup for Azure data workflows is using Data Factory as orchestrator for running Databricks notebooks. For example, using Blob event triggers pipelines are executed on receival of data in Blob/ADLS, after which the pipeline passes relevant information to Databricks*. Next, in Databricks some logic is being executed. To ensure a successful workflow, notebooks that you are referring to in your Data Factory pipelines should obviously exist in Databricks. Now you could say that is a no-brainer, however when time passes after the initial development, and when your application is growing, you might loose sight on the connections between your pipelines and notebooks. Imagine cleaning up rendundant notebooks, not realizing some pipelines were still referencing it…your pipeline will fail, which could cause a chain of unwanted events. In this blog I demonstrate an approach to test the integration of Data Factory and Databricks as part of your CI pipeline, so as a developer you can catch this potential issue before deployment.</p><p>*Want to learn how to pass parameters between Data Factory and Databricks? Check out <a href="https://medium.com/azure-tutorials/how-to-pass-parameters-between-your-data-factory-pipeline-and-databricks-notebook-e59b4ca6b015">this blog</a>!</p><p><strong>Run Powershell script with integration test during CI</strong></p><p>I am using Powershell to create the integration test. This script is running during the CI. The test takes input from the Azure DevOps repository, where pipeline definitions and notebook files are stored in separate directories:</p><pre>$work_dir=$env:SYSTEM_DEFAULTWORKINGDIRECTORY<br>$dir_adb=$work_dir + &#39;\src\databricks\notebooks&#39;<br>$dir_adf_pipelines=$work_dir + &#39;\src\datafactory\pipelines&#39;</pre><p>Now let’s build up the script. First we want to collect all the pipelines and notebooks from the repository in an array.</p><pre>$arr_notebooks=@()<br>$arr_pipelines=@()<br><br>#list all adb notebooks, adf pipeline sourcefiles and adf datasets<br>$arr_notebooks=@(Get-ChildItem -Path $dir_adb)<br>$arr_pipelines=@(Get-ChildItem -Path $dir_adf_pipelines)</pre><p>In the notebooks array ($arr_notebooks) the names of the notebooks contain the file extension, for example “.py”. However, when a data factory pipeline refers to a notebook, it refers to the notebook filename without the extension. So next, we remove the extension from the notebook names and save in a new array:</p><pre>$arr_notebooks_rm_ext=[System.Collections.ArrayList]@()<br><br>#remove extensions from notebook filenames<br>    foreach($notebook in $arr_notebooks)<br>    {<br>        $notebookName=$notebook.ToString().Split(&quot;.&quot;)[0]<br>        $arr_notebooks_rm_ext.Add($notebookName) &gt; $null<br>    }</pre><p>Now we iterate all the pipeline definitions, to find where Databricks notebook activities are referenced. In this example, I check for activity type “DatabricksNotebook” which can be referenced from the root of the pipeline, or within a Switch activity. When found, I assess if the referenced notebook name** exists in the notebooks array we extracted from our repo. In case the notebook is not found, we add an error to our errorlog variable. Additionally, we print which pipeline is causing the issue, including the name of the notebook that was not found.</p><p>**You should extract the notebook name from the notebook path. The way to achieve this depends on the format of your path.</p><pre>$errorlog=[System.Collections.ArrayList]@()<br><br>#read pipeline json files and perform checks<br>foreach($pipeline in $arr_pipelines)<br>{<br>    $dir_pipeline=$dir_adf_pipelines+&#39;\&#39;+$pipeline<br>    $pipelinejson=Get-Content -Raw -Path $dir_pipeline | ConvertFrom-Json<br>   <br>    foreach($activity in $pipelinejson.properties.activities)<br>    {<br><br>        #get notebookPath from pipeline activity<br>        if($activity.type -eq &quot;DatabricksNotebook&quot;)<br>        {<br>            $notebookPath=$activity.typeProperties.notebookPath<br>        }<br>        elseif($activity.type -eq &quot;Switch&quot;)<br>        {<br>            foreach($case in $activity.typeProperties.cases)<br>            {<br>                foreach($case_activity in $case.activities)<br>                {<br>                    if($case_activity.type -eq &quot;DatabricksNotebook&quot;)<br>                    {<br>                        $notebookPath=$case_activity.typeProperties.notebookPath<br>                    }                        <br>                }<br>            }<br>        }<br><br>        $notebookPath_split=$notebookPath.Split(&quot;/&quot;)<br>        $notebookFolder=$notebookPath_split[1]<br>        $notebookName=$notebookPath_split[2]   <br>        <br>        <br>        #check if notebook triggered in adf pipeline exists in src/databricks<br>        if(-not ($arr_notebooks_rm_ext.Contains($notebookName)))<br>        {   <br>            $error_msg=&quot;An activity in pipeline &quot;+$pipeline+ &quot; refers to a non existing notebook: &quot;+ $notebookName<br>            $errorlog.Add($error_msg) &gt; $null <br>            Write-Host $error_msg <br>        }           <br>    }        <br>}</pre><p>We create a variable “testresult” and assign it value 0. Next, we assess if the errorlog variable contains any errors. If not, there were no issues found and the test passed. If we did find any errors, we assign a 1 to our “testresult” variable.</p><pre>$testresult=0<br><br>#update output variable $testresult in case of findings<br>if($errorlog.count -gt 0)<br>{<br>    $testresult=1<br>    Write-Host $errorlog.count &quot;error(s) found. Details are provided above.&quot;<br>}</pre><p>Lastly, we return the “testresult” variable to our CI pipeline, which is either 0 or 1.</p><pre>#return $testresult to ci pipeline<br>&quot;##vso[task.setvariable variable=integrationTestResult;isOutput=true]$testresult&quot;</pre><p>The CI pipeline fails in case the testresult of the Integration test equals 1:</p><pre>- stage: &#39;IntegrationTest_Databricks_DataFactory&#39;<br>  pool:<br>    vmImage: &#39;windows-2019&#39;<br>  jobs:<br>  - job: &#39;Run_integration_test&#39;<br>    steps:<br>    - task: PowerShell@2<br>      name: &#39;runTest&#39;<br>      inputs:<br>        targetType: filePath<br>        filePath: &#39;./src/powershell/ci_test_adb_adf.ps1&#39;<br><br>  - job: &#39;Assess_test_result&#39;<br>    variables:<br>      testresult: $[dependencies.Run_integration_test.outputs[&#39;runTest.integrationTestResult&#39;]]<br>    dependsOn: &#39;Run_integration_test&#39;<br>    steps:<br>    - powershell: |       <br>        if($env:testresult -eq 1)<br>        {<br>          Write-Host &quot;Integration test failed. Please refer to job runTest for more details.&quot;<br>          exit 1<br>        }<br>      displayName: &#39;Assess test result&#39;</pre><p>That’s it! Additionally you can add more checks, for example:</p><ul><li>assess if certain naming conventions are applied on the names of pipeline activities, or on variable names</li><li>assess if certain activities are used</li><li>include checks on datasets</li></ul><p>Please refer to the full script below:</p><pre># ----------------------------------------------------------------------------------------------------------<br># Description: Integration test between Data Factory and Databricks code in CI<br># # -----------------------------------------------------------------------------------------------------------<br><br>try{<br><br>    #define directories and output variables<br>    $work_dir=$env:SYSTEM_DEFAULTWORKINGDIRECTORY<br>    $dir_adb=$work_dir + &#39;\src\databricks\notebooks&#39;<br>    $dir_adf_pipelines=$work_dir + &#39;\src\datafactory\pipelines&#39;<br>    $dir_adf_datasets=$work_dir + &#39;\src\datafactory\datasets&#39;<br>    $errorlog=[System.Collections.ArrayList]@()<br>    $arr_notebooks=@()<br>    $arr_pipelines=@()<br>    $arr_notebooks_rm_ext=[System.Collections.ArrayList]@()<br>    $testresult=0<br><br>    #list all adb notebooks, adf pipeline sourcefiles and adf datasets<br>    $arr_notebooks=@(Get-ChildItem -Path $dir_adb)<br>    $arr_pipelines=@(Get-ChildItem -Path $dir_adf_pipelines)<br>    $arr_datasets=@(Get-ChildItem -Path $dir_adf_datasets)<br><br>    #remove extensions from notebook filenames<br>    foreach($notebook in $arr_notebooks)<br>    {<br>        $notebookName=$notebook.ToString().Split(&quot;.&quot;)[0]<br>        $arr_notebooks_rm_ext.Add($notebookName) &gt; $null<br>    }<br><br>    #read pipeline json files and perform checks<br>    foreach($pipeline in $arr_pipelines)<br>    {<br>        $dir_pipeline=$dir_adf_pipelines+&#39;\&#39;+$pipeline<br>        $pipelinejson=Get-Content -Raw -Path $dir_pipeline | ConvertFrom-Json<br>       <br>        foreach($activity in $pipelinejson.properties.activities)<br>        {<br><br>            #get notebookPath from pipeline activity<br>            if($activity.type -eq &quot;DatabricksNotebook&quot;)<br>            {<br>                $notebookPath=$activity.typeProperties.notebookPath<br>            }<br>            elseif($activity.type -eq &quot;Switch&quot;)<br>            {<br>                foreach($case in $activity.typeProperties.cases)<br>                {<br>                    foreach($case_activity in $case.activities)<br>                    {<br>                        if($case_activity.type -eq &quot;DatabricksNotebook&quot;)<br>                        {<br>                            $notebookPath=$case_activity.typeProperties.notebookPath<br>                        }                        <br>                    }<br>                }<br>            }<br><br>            $notebookPath_split=$notebookPath.Split(&quot;/&quot;)<br>            $notebookFolder=$notebookPath_split[1]<br>            $notebookName=$notebookPath_split[2]    <br>            <br>            <br>            #check if notebook triggered in adf pipeline exists in src/databricks<br>            if(-not ($arr_notebooks_rm_ext.Contains($notebookName)))<br>            {   <br>                $error_msg=&quot;An activity in pipeline &quot;+$pipeline+ &quot; refers to a non existing notebook: &quot;+ $notebookName<br>                $errorlog.Add($error_msg) &gt; $null <br>                Write-Host $error_msg <br>            }           <br>        }        <br>    }<br><br>    #update output variable $testresult in case of findings<br>    if($errorlog.count -gt 0)<br>    {<br>        $testresult=1<br>        Write-Host $errorlog.count &quot;error(s) found. Details are provided above.&quot;<br>    }<br><br>    #return $testresult to ci pipeline<br>    &quot;##vso[task.setvariable variable=integrationTestResult;isOutput=true]$testresult&quot;<br>}<br><br>catch{<br>    Write-Host &quot;Error: Script not executed&quot;<br>    Write-Host $_.Exception <br>}</pre><p>Thanks for reading!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c8b73d118dd0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Save Excel as CSV with custom delimiter]]></title>
            <link>https://medium.com/azure-tutorials/save-excel-as-csv-with-custom-delimiter-fa516ccb509b?source=rss-e1dde29db640------2</link>
            <guid isPermaLink="false">https://medium.com/p/fa516ccb509b</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[csv-file]]></category>
            <dc:creator><![CDATA[Ilse Epskamp]]></dc:creator>
            <pubDate>Thu, 08 Jun 2023 16:27:47 GMT</pubDate>
            <atom:updated>2023-06-08T16:27:47.140Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*e5eBuZG35P6H_Zss" /><figcaption>Photo by <a href="https://unsplash.com/@epicuros?utm_source=medium&amp;utm_medium=referral">Vasilis Caravitis</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Let’s hope that you as a Data Engineer never need this article, because that means the data you are working with is by default read-friendly. However, life of a Data Engineer is not always simple and straightforward, and you will most likely be confronted data structures that can give you a headache. One of these can be working with delimiters in Excel and or CSV file, combined with other special characters in your file. In this article I will discuss the scenario where you need to convert an Excel into a CSV with a custom delimiter, for example pipe.</p><p><strong>Case example</strong><br>I was confronted with a file that was extracted from a source system and saved as Excel file. My task was to load the data into an Azure SQLDB, using the automated ingestion workflow that we have setup. In this <a href="https://medium.com/@ilse-epskamp/building-a-scalable-metadata-driven-data-ingestion-framework-684a67706eed">automated workflow</a>, a Databricks notebook reads a csv file from ADLS Gen 2 using the file delimiter that is set in the metadata description of the source file.</p><p>When opening the Excel file, the default delimiter of the file is a comma. Using the Text to Columns feature of Excel to display the data I noticed the data values were not correctly assigned to their columns. A deeper investigation showed that some columns contained a comma separated list of values, creating additional columns. How inconvenient!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/219/1*ZBK9JYmts0D3ePEPsEvfJw.png" /><figcaption>colC contains a comma separated list of values.</figcaption></figure><p>The first thought was to switch to semicolon as delimiter. However, also semicolon was already represented as a character in some values in the file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/337/1*ZwAHG2O-T0jQ74yRbp79sg.png" /><figcaption>colC contains a comma separated list of values, and colD contains a semicolon separated list of values.</figcaption></figure><p>To be able to correctly read the file in our automated ingestion workflow, we need a custom delimiter, other than comma or semicolon.</p><p><strong>Save an Excel File with a custom delimiter</strong><br>You cannot simply select a delimiter in Excel when saving the file. You have to configure the default “List separator” in your system’s settings after which Excel will use it when saving the file. Let’s go through it step-by-step how to save your Excel file with a custom set delimiter.</p><ol><li>Go to Control Panel &gt; Region Settings &gt; Change date, time or number formats</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/773/1*dfBD6Pb0rwM2LZfDeO2VMA.png" /></figure><p>2. In tab Formats click on Additional Settings</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/479/1*g85u5pNNxuUP1vvNxqTsYg.png" /></figure><p>3. In tab Numbers, find the option for List separators, and input the delimiter of your choice. NB this is a dropdown list, however if the delimiter of your choice is not in the list you can simply type it in and hit Apply.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/460/1*ClstRBz-OtG8LKXrRwGQPA.png" /><figcaption>Update the List separator with your delimiter of choice.</figcaption></figure><p>That’s it! Now go back to your Excel, hit Save As, and save the file as CSV (Comma delimited) (*.csv).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/718/1*Fw10VgSde1QkV9TcozoVJQ.png" /></figure><p>Happy data engineering!</p><p><em>Azure Tutorials frequently publishes tutorials, best practices, insights or updates about Azure Services, to contribute to the Azure Community. Azure Tutorials is driven by two enthusiastic Azure Cloud Engineers, combining over 15 years of IT experience in several domains. Stay tuned for blog updates and follow us if you are interested!</em><br><a href="https://www.linkedin.com/company/azure-tutorials">https://www.linkedin.com/company/azure-tutorials</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fa516ccb509b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/azure-tutorials/save-excel-as-csv-with-custom-delimiter-fa516ccb509b">Save Excel as CSV with custom delimiter</a> was originally published in <a href="https://medium.com/azure-tutorials">Azure Tutorials</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a scalable metadata-driven data ingestion framework]]></title>
            <link>https://ilse-epskamp.medium.com/building-a-scalable-metadata-driven-data-ingestion-framework-684a67706eed?source=rss-e1dde29db640------2</link>
            <guid isPermaLink="false">https://medium.com/p/684a67706eed</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[database-development]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Ilse Epskamp]]></dc:creator>
            <pubDate>Tue, 18 Oct 2022 19:12:43 GMT</pubDate>
            <atom:updated>2022-10-22T18:12:52.198Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5RL2KfSeXjPwELQP" /><figcaption>Photo by <a href="https://unsplash.com/@theshubhamdhage?utm_source=medium&amp;utm_medium=referral">Shubham Dhage</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>A typical task for a data engineer is to pick up data, validate the data, if required apply transformations such as an aggregation, or combine the data with other sources, and finally store the data into a final location such as a storage folder or a database. When you capture all these tasks in an automated flow of activities, you get an ingestion framework, allowing you to process many files without manually having to check, move and ingest each one of them. Moreover, having a reusable scalable framework enables you to efficiently and effectively handle all your data requirements. Metadata plays a key role in building any scalable ingestion framework, and in this blog I will deepdive into the concepts of such a framework, provide some practical examples and key takeaways for building a metadata-driven data ingestion framework, aiming to provide food for thought for how you can make use of metadata when processing high volumes of data.</p><p>In this blog I deliberately don’t talk about tooling. The concept of a scalable, metadata-driven data ingestion framework are tooling-independent, and your choice of tooling depends on many project or organization specific elements such as cost, contracts, strategy and experience.</p><p><strong>How it started</strong></p><p>Our development team was given the requirement to build an application that addresses the business and technical data lineage requirements of the organization. Business data lineage shows for example the data consumption by consuming applications in the organization, purpose of usage, risks associated to this usage and the connection of all these elements to different organizational aspects such as regulations, policies, and the organizational structure. Technical data lineage shows how data flows from any source application to all it’s consuming applications and all the data transformations that are applied along the way. You can imagine that to build such an application you need to integrate many data sources, such as data about the applications running in the organization, the datasets that they produce, the consuming applications of those datasets, the IT teams working on the related applications, and all the information related to usage and transformations of data in the (providing and consuming) applications. Considering the amount of sources we could expect, their refresh schedules (most of them daily) and the announcement that many more sources could be in scope in the near future, we knew instantly that we needed to build a data processing framework that was capable of handling all the data requirements that come along with all these different datasets. We designed a metadata-driven data ingestion framework, which is a flexible and highly scalable framework to automate your data engineering activities.</p><p><strong>The role of metadata in your data ingestion framework</strong></p><p>Metadata in a data ingestion framework holds the information that describes what steps to execute when processing a datafile. Introducing metadata into your data processing framework has many benefits:</p><p>- <strong>Scalability</strong>: easily increase or decrease the amount of data flowing through your system, and easily add, modify or remove features to/from your framework. It’s plug and play!</p><p>- <strong>Reusability</strong>: features are easily reused by other sources, even by other applications, because of the generic design.</p><p>- <strong>Maintainability</strong>: only one set of code to maintain, which you can apply on all (or a selection of) sources.</p><p>- <strong>Decreased development time</strong>: by writing generic workflows the amount of required custom code decreases drastically. This saves a lot of time!</p><p><strong>Blueprint of ingestion framework and example flow</strong></p><p>Figure 1 shows an example blueprint of an ingestion framework. In this example validated and, if required, transformed data is ingested in either SQLDB, NoSQL DB (Graph), both or none. The sample framework is able to apply three types of validations;</p><ul><li>snapshot validation (ensure the date of the received file is the latest date);</li><li>rowcount anomaly (sanity check on the amount of records in the file);</li><li>datatype validation (ensure the datatype of a column is the expected datatype).</li></ul><p>In reality you could have many more validation or quality checks that you want to apply. However, it’s not a given that all checks apply to all sources. Some sources might be eligible for more (or less) validation checks, depending on file-specific features like sensitivity, governance etc.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1004/1*U5DQJ-O9AqPmvplKYqAL-g.png" /><figcaption>Figure 1: Example of a data ingestion framework. Image by Ilse Epskamp.</figcaption></figure><p>Let’s introduce some datafiles that we want to process with the framework. The requirements for these files are as follows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/778/1*qATQr4BYpZrDXOa05OAajQ.png" /><figcaption>Sample data requirements for files. Table by the author.</figcaption></figure><p>You see that with only four files you already have a lot of possible combinations of framework features that you need to apply. Some files require multiple validation checks, while others only require one. Some files have transformation requirements, while others don’t. And one is ingested only in the Graph, one only in SQL, one in both and one in none. You can imagine that writing a custom flow for every source not only takes a lot of time, but also results in a lot of duplicate code since even though not all sources require exactly the same handling, there is a lot of overlap in applied features. You want to have a scalable processing framework which allows you to on the fly only select the features that you want to apply to your sources, without having to write all workflows by hand.</p><p><strong>Three pillars of a scalable ingestion framework</strong></p><p>A scalable ingestion framework is build on 3 pillars:</p><ul><li>data (the subject);</li><li>metadata (the instructions);</li><li>code (the execution engine).</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/747/1*LeZXKRMhT5reaq6gsA0fQA.png" /><figcaption>Figure 2: Data, metadata and code drive any scalable ingestion framework. Image by Ilse Epskamp.</figcaption></figure><p><strong>Practical example: datatype validation with and without metadata</strong></p><p>Let’s get some code into the story. We will apply a datatype validation on two sample datasets, once without and once with use of metadata, to demonstrate the role of metadata in the code. Let’s assume we have two files; an Employee file, and an Accounts file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/950/1*owFHoYo4gBaUdCuLJtH3aw.png" /><figcaption>Figure 3: Sample data Employees and Accounts. Image by Ilse Epskamp.</figcaption></figure><p>For the Employees file, we want to verify if the ID column is of datatype <em>integer</em>. For the Accounts file, we want to verify if the ACTIVE column is of type <em>boolean</em>. Only then we accept the files. We could write a custom validation for both files. That could look something like this:</p><pre>if file==&quot;employees&quot;:<br>   isInteger=False<br>   if(df.schema(&quot;ID&quot;).datatype.typeName==&quot;integer&quot;):<br>     isInteger=True<br>   result=isInteger</pre><pre>if file==&quot;accounts&quot;:<br>   isBoolean=False<br>   if(df.schema(&quot;ACTIVE&quot;).datatype.typeName==&quot;boolean&quot;):<br>     isBoolean=True<br>   result=isBoolean</pre><p>This would work fine. However, imagine you are loading 100 files per day and you want to apply this validation check to half of them. Or imagine you want to check multiple columns for the same file. Think of the amount of custom workflows you would need to write! Writing custom workflows for every scenario is not scalable and difficult to maintain. Let’s do this again, but now with use of metadata.</p><p>First we define metadata for both sources. In this example, each source has a dedicated JSON file* containing all the relevant metadata key-value pairs. Specifically, we add a section for validation checks, where we enable the datatype validation on a specific column, with a specific expected outcome.</p><p>*In this example the metadata for the ingestion is configured with JSON files. Alternatively you can use any format or tool that fits your application.</p><p>employees.json:</p><pre>&quot;filename&quot;: &quot;employees&quot;,<br>&quot;validation&quot;: {<br>   &quot;validateColumnDataType&quot;: {<br>   &quot;enabled&quot;:  true,<br>   &quot;colname&quot;:  &quot;ID&quot;<br>   &quot;datatype&quot;: &quot;integer&quot;<br>   }<br>}</pre><p>accounts.json:</p><pre>&quot;filename&quot;: &quot;accounts&quot;,<br>&quot;validation&quot;: {<br>   &quot;validateColumnDataType&quot;: {<br>   &quot;enabled&quot;:  true,<br>   &quot;colname&quot;:  &quot;ACTIVE&quot;<br>   &quot;datatype&quot;: &quot;boolean&quot;<br>   }<br>}</pre><p>Now we have the metadata ready. Next step is to write a generic function which we can call when processing a datafile.</p><pre>def validate_col_datatype(data,col,type):<br>   passed=False<br>   if(data.schema(col).datatype.typeName==type):<br>     passed=True<br>   return passed</pre><p>All that is left is to add some code which reads the metadata and executes the validation function if it’s enabled for the source.</p><pre>md=read_metadata(filename)</pre><pre>#run validation if enabled<br>if md[&quot;validation&quot;][&quot;validateColumnDataType&quot;][&quot;enabled&quot;]:<br>   colname = md[&quot;validation&quot;][&quot;validateColumnDataType&quot;][&quot;colname&quot;]<br>   datatype = md[&quot;validation&quot;][&quot;validateColumnDataType&quot;][&quot;datatype&quot;]<br>   result = validate_col_datatype(data,colname,datatype)</pre><p>If you want to run this datatype validation on multiple columns, you simply add additional sections to your metadata configuration file. If you don’t need to apply the validation check to a source, you set enabled to <em>false</em>, and the check will be ignored for that source.</p><p>When using this pattern, you need to think about how you want to handle the result. Do you want to raise a warning and proceed, or break the flow if a validation fails? Do you want to automatically inform the development team or stakeholders group? Designing your workflow strategy is important to leverage the benefits such a framework can provide.</p><p><strong>Additional features</strong></p><p>You can think of many more features you can include in your framework. For example:</p><ul><li>More validation and quality checks. For example, you can validate filesize, filetype, file origin, value format, …</li><li>SCD-type. In each metadata configuration specify if you want to store the data in your database with SCD1 or SCD2, so if you want to build up and keep historical data in your databases (SCD2) or not (SCD1). Design your ingestion logic in such a way that if SCD1 is enabled, the data is overwritten in your database, and if SCD2 is enabled, history is retained. All the records in your databases should have custom attributes reflecting their version, startdate, enddate and date last update. For a SQL database you can think of using stored procedures to update the records versions, or depending on your database type use any out-of-the-box feature that is available. Alternatively, in you ingestion logic you can handle the records versions on the fly.</li></ul><p><strong>Metadata configuration file blueprint</strong></p><p>Below an example of a metadata configuration file for the sample Employees file, with generic file details and sections for validation, transformations and ingestion.</p><pre>&quot;description&quot;: &quot;This is the metadatafile for Employees data.&quot;,<br>&quot;sourcename&quot;: &quot;HRSYSTEM&quot;,<br>&quot;tablename&quot;: &quot;all_employees&quot;,<br>&quot;cardinality&quot;: &quot;1-to-1&quot;,<br>&quot;primary_key&quot;: &quot;EMPLOYEE_ID&quot;,<br>&quot;daily_delivery&quot;: true,<br>&quot;file_delimiter&quot;: &quot;;&quot;,<br>&quot;validation&quot;&quot;: {<br>  &quot;validate_snapshotdate&quot; : true,<br>  &quot;validate_rowcount_anomaly&quot; : false,<br>  &quot;validate_col_datatype&quot;: {<br>    &quot;enabled&quot;:  true,<br>    &quot;colname&quot;:  &quot;EMPLOYEE_ID&quot;<br>    &quot;datatype&quot;: &quot;integer&quot;<br>  }<br>},<br>&quot;transformations&quot;: {<br>  &quot;preprocessing_description&quot;: &quot;Join with file XYZ.&quot;,<br>  &quot;preprocessing_flag&quot;: true,<br>  &quot;preprocessing_logic&quot;: &quot;join_HRSYSTEM_all_employees_with_XYZ()&quot;<br>},<br>&quot;ingestion&quot;: {<br>  &quot;sqldb&quot;: {<br>    &quot;ingestion_enabled&quot;: true,<br>    &quot;ingestion_environment&quot;: &quot;dtap&quot;,<br>    &quot;scd_type&quot;: &quot;2&quot;,<br>    &quot;sql_tablename&quot;: &quot;employees&quot;<br>  },<br>  &quot;csmsdb&quot;: {<br>    &quot;ingestion_enabled&quot;: true,<br>    &quot;ingestion_environment&quot;: &quot;dtap&quot;,<br>    &quot;scd_type&quot;: &quot;1&quot;,<br>    &quot;vertex_label&quot;: &quot;EMPLOYEES&quot;,<br>    &quot;is_mapping_file&quot;: false   <br>  }<br>}</pre><p><strong>Takeaways</strong></p><p>When designing and building a metadata-driven data ingestion framework, I suggest to take at least the following points into account:</p><p><strong>1. Don’t cut corners</strong>. We all know the work pressure you can experience when fixing bugs or writing solutions under tight timelines. For many reasons it can be tempting to focus on the problem at hand without looking at the generic implementation. However spend that little extra effort to make your workflow generic and reusable. You will definitely benefit from it later!</p><p><strong>2. Think about your naming conventions</strong>. Your framework should be able to dynamically find all kinds of resources, from a storage accounts and its directories and files, to databases and their tablenames. Think about this and define a standard.</p><p><strong>3. Framework monitoring.</strong> Automation is great, but stay in control. Find a way to stay on top of everything that is happening in your framework. For example, we build a monitoring layer on top of the framework which logs every decision and every outcome to a centralized event log, which we use to build operational reports.</p><p><strong>4. Keep it requirements driven.</strong> Once you start you will think of so many features that might of use. Keep it requirements driven, to make sure that any feature that you add to the framework directly contributes to your customers, or to your development team.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=684a67706eed" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[CosmosDB (Gremlin) database design: positioning of edges in partitions]]></title>
            <link>https://medium.com/azure-tutorials/cosmosdb-gremlin-database-design-positioning-of-edges-in-partitions-e5117c002e2d?source=rss-e1dde29db640------2</link>
            <guid isPermaLink="false">https://medium.com/p/e5117c002e2d</guid>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[cosmosdb]]></category>
            <category><![CDATA[graph]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[database-design]]></category>
            <dc:creator><![CDATA[Ilse Epskamp]]></dc:creator>
            <pubDate>Mon, 18 Jul 2022 07:01:36 GMT</pubDate>
            <atom:updated>2022-07-18T07:01:36.208Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*utHd5F0KtGFXQn0I" /><figcaption>Photo by <a href="https://unsplash.com/@gferla?utm_source=medium&amp;utm_medium=referral">Guillermo Ferla</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>When designing and maintaining CosmosDB with Gremlin API for your workload you will need to decide on the approach for various topics, such as:</p><ul><li>Request Units provisioning strategy</li><li>Database consistency</li><li>Partition strategy</li><li>Data versioning</li><li>Data patterns</li><li>…</li></ul><p>In the current blog we will focus on CosmosDB data patterns, and will look specifically at the position and usage of edges in your graph database.</p><p><strong>Nodes and edges in database partitions</strong><br>When creating a vertex in CosmosDB, you must specify a value for the assigned partition key, to indicate to what partition the vertex belongs. Assume your partition key is called PARTITION, and you want to assign a record with primary key 123 to partition abc, the query for ingestion could look like this:</p><pre>g.addV({label}).property(&#39;primary_key&#39;, &#39;123&#39;).property(&#39;PARTITION&#39;,&#39;abc&#39;)</pre><p>Contrary to nodes, when creating edges you do not have to specify the partition for that edge. By default the edge is stored in the partition of their source vertex, in the outgoing direction. If both nodes are in separate partitions, it will look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/521/1*yEp2PZMntMH1y-8cJ4QYeQ.png" /><figcaption>Storage of vertices and edges in logical partitions in CosmosDB. Image by Azure Tutorials.</figcaption></figure><p>From this storing behavior we can state that traversing to a neighbor node using the out() query (from A to B) will be relatively quick and cheap, while traversing using the in() query (from B to A) will be an expensive query, since in the first scenario you are already in the partition where the edge resides, while in the second scenario you have to run (possible) multi cross-partition queries to locate the edge.</p><p><strong>Example: employees, stores and regions</strong><br>Let’s look at an example. Assume 3 entities: an employee, a store, and a region. Each entity is assigned toa dedicated partition. So in your database, you will have a partition per employee, a partition per store and a partition per region. NB we are not implying that partitioning the data based on entity is best practice. The partition strategy suitable for your data is depending on the distribution and volume of the data, and common query patterns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/403/1*i58QzSV2wFwRnEjkKK3Amw.png" /></figure><p>The employee can be manager of the store, and the store is located in a specific region. If you model your data exactly like this, it will look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/440/1*gJjNyjzKYkskf0ASwqptMw.png" /></figure><p>Plotting partitions per entity, you will get:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/544/1*1evob5DLFAuu6SpzMlRkbg.png" /></figure><p>Now let’s assume that a common query pattern is “give me all the stores, their regions and store managers. With above model, the query to fetch this information could look like this:</p><pre>g.V()<br>.hasLabel(&#39;STORES&#39;)<br>.project(&#39;storeName&#39;,&#39;storeManager&#39;,&#39;region&#39;)<br>  .by(&#39;store_name&#39;)<br>  .by(inE(&#39;is_manager_of&#39;).outV().values(&#39;employee_name&#39;))<br>  .by(outE(&#39;store_located_in&#39;).inV().values(&#39;region_name&#39;))</pre><p>See how to be able to retrieve the storemanagers, you have to use the incoming relation ‘is_manager_of’. Following the recommendation to use outgoing edges when possible, a better implementation would be a relation from store to employee with label ‘has_storemanager’. The model would look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/511/1*eBpFrzr0tsBrRSm6mx0CVg.png" /></figure><p>Now you can use both outgoing relations from the store entity. Of course this is just a simple and small example of handling edges in CosmosDB. In practice we have seen scenarios with large volumes of data where the execution time and amount of provisioned Request Units for a query dropped by 50–80% because of providing efficient traversal paths!</p><p><strong>Designing CosmosDB database patterns<br></strong>When starting with CosmosDB in a project, the exact common query patterns might not be known yet. Therefore consider:</p><ul><li>building flexible ingestion logic so you can easily add/remove/update edges in your physical implementation, adapting to consumer’s common query patterns</li><li>actively involve your consumers in the database design to understand their data requirements, to be able to design fit database patterns</li><li>in the Azure Portal, use Logs to analyze querying behaviour to find improvement areas for your database design. For example, you can analyse the top queries by RU usage:</li></ul><pre>AzureDiagnostics | <br>where Category == &quot;GremlinRequests&quot; | <br>where TimeGenerated &gt; ago(6h) | <br>project piiCommandText_s, activityId_g,  operationType_s, requestCharge_s, TimeGenerated | <br>order by requestCharge_s desc | <br>take 100</pre><p><em>Azure Tutorials frequently publishes tutorials, best practices, insights or updates about Azure Services, to contribute to the Azure Community. Azure Tutorials is driven by two enthusiastic Azure Cloud Engineers, combining over 15 years of IT experience in several domains. Stay tuned for weekly blog updates and follow us if you are interested!</em><br><a href="https://www.linkedin.com/company/azure-tutorials">https://www.linkedin.com/company/azure-tutorials</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e5117c002e2d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/azure-tutorials/cosmosdb-gremlin-database-design-positioning-of-edges-in-partitions-e5117c002e2d">CosmosDB (Gremlin) database design: positioning of edges in partitions</a> was originally published in <a href="https://medium.com/azure-tutorials">Azure Tutorials</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Add/Remove Azure Resource Locks with Powershell]]></title>
            <link>https://medium.com/azure-tutorials/add-remove-azure-resource-locks-with-powershell-773bba1c8b?source=rss-e1dde29db640------2</link>
            <guid isPermaLink="false">https://medium.com/p/773bba1c8b</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[azure-devops]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[security]]></category>
            <dc:creator><![CDATA[Ilse Epskamp]]></dc:creator>
            <pubDate>Mon, 30 May 2022 07:02:24 GMT</pubDate>
            <atom:updated>2022-05-30T07:02:24.214Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*QwHS8cuUdaqqZoro" /><figcaption>Photo by <a href="https://unsplash.com/@flyd2069?utm_source=medium&amp;utm_medium=referral">FLY:D</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Resource locks are a powerful mechanism to protect your resources from unauthorized operations. For example, you can lock your storage account to prevent files and directories are deleted. Or you can lock you Data Factory resource so pipelines cannot be deleted manually. The lock policy might differ per environment; in a development environment you might want to give your developers more freedom than in a controlled production environment. In addition, you would want to control who is allowed to remove/add locks (eg personal vs. non-personal accounts).</p><p>It is advisable to clearly define your “lock strategy” on resource group as well as resource level, to benefit from this feature from the moment you provision resources.</p><p>You can add and remove locks with Powershell.</p><p><strong>Remove lock by lock name:</strong></p><pre>Remove-AzResourceLock -LockName &#39;{}&#39; -ResourceName &#39;{}&#39;  -ResourceGroupName &#39;{}&#39; -ResourceType &#39;{}&#39; -Force</pre><p><strong>Remove lock by lock id:</strong></p><pre>Remove-AzResourceLock -LockId &#39;{}&#39; -Force</pre><p><strong>Don’t know your lock id? Fetch all lock ids with:</strong></p><pre>Get-AzResourceLock -ResourceGroupName &#39;{}&#39; -ResourceType &#39;{}&#39;-ResourceName &#39;{}&#39;</pre><p><strong>Add lock, example CanNotDelete:</strong></p><pre>New-AzResourceLock -LockName &#39;{}&#39; -ResourceName &#39;{}&#39;  -ResourceGroupName &#39;{}&#39; -ResourceType &#39;{}&#39; -LockLevel &quot;CanNotDelete&quot; -Force</pre><p><em>Azure Tutorials frequently publishes tutorials, best practices, insights or updates about Azure Services, to contribute to the Azure Community. Azure Tutorials is driven by two enthusiastic Azure Cloud Engineers, combining over 15 years of IT experience in several domains. Stay tuned for weekly blog updates and follow us if you are interested!</em><br><a href="https://www.linkedin.com/company/azure-tutorials">https://www.linkedin.com/company/azure-tutorials</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=773bba1c8b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/azure-tutorials/add-remove-azure-resource-locks-with-powershell-773bba1c8b">Add/Remove Azure Resource Locks with Powershell</a> was originally published in <a href="https://medium.com/azure-tutorials">Azure Tutorials</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Use dataset parameters to copy data to dynamically defined source and sink directories with ADF]]></title>
            <link>https://medium.com/azure-tutorials/use-dataset-parameters-to-copy-data-to-dynamically-defined-source-and-sink-directories-with-adf-3ec632d548df?source=rss-e1dde29db640------2</link>
            <guid isPermaLink="false">https://medium.com/p/3ec632d548df</guid>
            <category><![CDATA[azure-data-factory]]></category>
            <category><![CDATA[azure-devops]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[azure]]></category>
            <dc:creator><![CDATA[Ilse Epskamp]]></dc:creator>
            <pubDate>Mon, 02 May 2022 07:02:38 GMT</pubDate>
            <atom:updated>2022-05-02T07:02:38.698Z</atom:updated>
            <content:encoded><![CDATA[<h3>Use dataset parameters to copy data to dynamically defined source and sink directories with Data Factory</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*cit51IoxvuxXTtTO" /><figcaption>Photo by <a href="https://unsplash.com/@emilymorter?utm_source=medium&amp;utm_medium=referral">Emily Morter</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>When building an automated workflow you need to spend time making your workflow dynamic to be able to scale up quickly and be able to handle large volumes of files without manual work. In this blog we show how to configure dynamic source and sink directories for your Data Factory workflows, enabling you to copy data from and to dynamically defined directories.</p><p><strong>Approach</strong><br>We use the Copy Data activity in Data Factory to move data from location A to location B in ADLS gen 2. The source and sink directories are parameterized, where the values for these variables are populated during runtime.</p><p><strong>Step 1: Create dataset ‘source’</strong><br>Create a dataset in Data Factory called <em>source</em> (or any other name that suits your application). This represents the sourcefile involved in your copy activity. Go to Datasets &gt; New Dataset &gt; Azure Data Lake Storage Gen 2 &gt; Binary. Choose the linked service to connect to your ADLS gen 2 resource. For now, leave the file path blank and press OK.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/545/1*DCQDK8QnZhJ5ER8uPcHOkg.png" /><figcaption>Create a Binary dataset for ADLS gen 2.</figcaption></figure><p><strong>Step 2: Create Dataset Parameters</strong><br>In tab Parameters, you create 3 parameters:</p><p><em>Container </em>— represents the container in ADLS where the file is located.</p><p><em>Directory </em>— represents the directory/directories where the file is located.</p><p><em>Filename </em>— represents the name of the file.</p><p>To make the source location dynamic, parameterize the default values of these parameters. For example, if you received the filename via a trigger event you can refer to the <em>triggerFileName </em>as</p><pre>@pipeline().parameters.triggerFileName</pre><p>Or, if you defined the container name in a pipeline variable called <em>source_container</em>, you can refer to this variable as</p><pre>@variables(&#39;source_container&#39;)</pre><p>Find out what works for your requirement and populate the default value accordingly.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/648/1*FP4yIcZqVFQgpUpWWhmKPw.png" /><figcaption>Parameterize the default value of your dataset parameters.</figcaption></figure><p><strong>Step 3: Configure the Dataset Connection details</strong><br>In tab Connection, refer the dataset parameters you just created in the file path as</p><pre>@<strong>dataset</strong>().Container</pre><pre>@<strong>dataset</strong>().Directory</pre><pre>@<strong>dataset</strong>().Filename</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3JxJkmThf0BnuLsG2v-GCA.png" /><figcaption>Refer to your dataset parameters when configuring the Connection details of your dataset.</figcaption></figure><p><strong>Step 4: Create dataset ‘sink’<br></strong>Repeat steps 1–3, but now create a sink dataset, representing the target location for your file.</p><p><strong>Step 5: Create a pipeline</strong><br>Create a data factory pipeline and add the Copy Data activity.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/213/1*ivWLo8U8tXifIs_xs1AxoA.png" /><figcaption>Copy data activity in Data Factory.</figcaption></figure><p>In tab Source, select your <em>source </em>dataset as Source dataset. You will see that the parameters you defined in your dataset will be displayed in the Dataset properties. In tab Sink, select your <em>sink</em> dataset as Sink dataset.</p><p>That’s it! This configuration enables you to dynamically pass a Container, Directory and Filename to your datasets so you can use this to move data from one location to another without hardcoding any file specific information. You can decide to use this feature in an existing pipeline, feeding the dataset parameters directly from there, or creating a separate “move_data” pipeline which takes source and sink parameters as input on trigger event, from any pipeline.</p><p><em>Azure Tutorials frequently publishes tutorials, best practices, insights or updates about Azure Services, to contribute to the Azure Community. Azure Tutorials is driven by two enthusiastic Azure Cloud Engineers, combining over 15 years of IT experience in several domains. Stay tuned for weekly blog updates and follow us if you are interested!</em><br><a href="https://www.linkedin.com/company/azure-tutorials">https://www.linkedin.com/company/azure-tutorials</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3ec632d548df" width="1" height="1" alt=""><hr><p><a href="https://medium.com/azure-tutorials/use-dataset-parameters-to-copy-data-to-dynamically-defined-source-and-sink-directories-with-adf-3ec632d548df">Use dataset parameters to copy data to dynamically defined source and sink directories with ADF</a> was originally published in <a href="https://medium.com/azure-tutorials">Azure Tutorials</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Do’s and Don’ts when working with CosmosDB Gremlin API]]></title>
            <link>https://medium.com/azure-tutorials/dos-and-don-ts-when-working-with-cosmosdb-gremlin-api-cbca6d84067d?source=rss-e1dde29db640------2</link>
            <guid isPermaLink="false">https://medium.com/p/cbca6d84067d</guid>
            <category><![CDATA[azure-devops]]></category>
            <category><![CDATA[cosmosdb]]></category>
            <category><![CDATA[data-engineer]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Ilse Epskamp]]></dc:creator>
            <pubDate>Mon, 18 Apr 2022 07:03:40 GMT</pubDate>
            <atom:updated>2022-04-18T07:03:40.391Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*07LtYVUQV1f4zl66" /><figcaption>Photo by <a href="https://unsplash.com/@kellysikkema?utm_source=medium&amp;utm_medium=referral">Kelly Sikkema</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>In one of our <a href="https://medium.com/azure-tutorials/connect-azure-databricks-to-cosmosdb-gremlin-api-and-run-queries-f2c29522a72f">previous blogs</a> we described how to connect Azure Databricks to CosmosDB with Gremlin API and run queries on the database. Based on our experience with using CosmosDB (Gremlin API) in an automated workflow we share our view on Do’s and Don’ts when setting up the database and ingestion scripts.</p><p><strong>Don’t….</strong></p><ul><li>configure the amount of Request Units based on the largest file your workflow will process. For this file it will be sufficient, but for other files it will be overkill thus costly. <br>NB: the largest file is not necessarily the file with most rows. Rows simply indicate file length, while the amount of columns determine the load on the operation to create or edit vertices. For example, querying or creating a node with 5 properties requires way less Request Units than querying or creating a node with 50 properties.</li><li>postpone your decision on the partition strategy. Set is set.</li></ul><p><strong>Do….</strong></p><ul><li>Autoscale the Request Units if it meets your requirement and business policy. As an alternative you can use provisioned throughput with a custom autoscale mechanism, where you determine the required Request Units based on your file details and increase/decrease the Request Unit by triggering a powershell script in your pipeline, for example in <a href="https://medium.com/azure-tutorials/how-to-trigger-azure-devops-release-pipeline-from-databricks-ba016a46f70f">an Azure DevOps release pipeline</a>. When doing this, make sure you <a href="https://medium.com/azure-tutorials/custom-queue-mechanism-for-data-factory-pipelines-a1b0809ec790">queue your data ingestion pipeline</a>, since the Request Units are pinpointed to a specific file. Example flow:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/921/1*r-EKuTRAX69MG7JeSHxdQw.png" /><figcaption>Image by Azure Tutorials.</figcaption></figure><ul><li>When adding properties to a vertex, add them in a list so you can easily add more properties for that key. This is usable for all properties, for example not for the primary key and the partition key.</li></ul><pre>g.addV(&#39;VERTEX_LABEL&#39;)<br>.property(&#39;{partition_key}&#39;,&#39;{partition_value}&#39;)<br>.property(&#39;primary_key&#39;,&#39;{primary_key_value}&#39;)<br>.property(list, &#39;{property-name}&#39;,&#39;{property-value}&#39;)</pre><ul><li>When querying data, try to query the partition which holds that data element. This will improve performance and decrease cost since the required RU is less. For example, when you partitioned your data based on its vertex label you are looking for a node in a particular label, you can do:</li></ul><pre>g.V().hasLabel(&#39;{vertex_label}&#39;)<br>.has(&#39;{partition_key}&#39;,&#39;{partition_value}&#39;)<br>.has(&#39;{primary_key}&#39;,&#39;{primary_key_value}&#39;)</pre><ul><li>Keep queried data in memory to reduce the amount of required read operations on your database.</li></ul><p><em>Azure Tutorials frequently publishes tutorials, best practices, insights or updates about Azure Services, to contribute to the Azure Community. Azure Tutorials is driven by two enthusiastic Azure Cloud Engineers, combining over 15 years of IT experience in several domains. Stay tuned for weekly blog updates and follow us if you are interested!</em><br><a href="https://www.linkedin.com/company/azure-tutorials">https://www.linkedin.com/company/azure-tutorials</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cbca6d84067d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/azure-tutorials/dos-and-don-ts-when-working-with-cosmosdb-gremlin-api-cbca6d84067d">Do’s and Don’ts when working with CosmosDB Gremlin API</a> was originally published in <a href="https://medium.com/azure-tutorials">Azure Tutorials</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Connect Azure Databricks to CosmosDB Gremlin API and run queries]]></title>
            <link>https://medium.com/azure-tutorials/connect-azure-databricks-to-cosmosdb-gremlin-api-and-run-queries-f2c29522a72f?source=rss-e1dde29db640------2</link>
            <guid isPermaLink="false">https://medium.com/p/f2c29522a72f</guid>
            <category><![CDATA[cosmosdb]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[graph]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Ilse Epskamp]]></dc:creator>
            <pubDate>Mon, 04 Apr 2022 07:03:23 GMT</pubDate>
            <atom:updated>2022-04-04T07:03:23.637Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*hXUw4agmj9RIDxJu" /><figcaption>Photo by <a href="https://unsplash.com/@markusspiske?utm_source=medium&amp;utm_medium=referral">Markus Spiske</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>CosmosDB with Gremlin API is a graph database on Azure. You can interact with the database directly in the portal, however when ingestion data as part of an automated flow this is not desirable. In that scenario you want to be able to create and run queries dynamically based on variable input data. One approach is to use Databricks to ingest data in CosmosDB. In this blog we describe how to connect Databricks to CosmosDB with Gremlin API and run queries on the database. Also a scenario for usage in an automated end to end flow is provided.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xfos8LcCepkw5VNm-K-yPg.png" /><figcaption>Data Explorer for Cosmos DB Gremlin API in Azure Portal.</figcaption></figure><p><strong>Import the gremlin_python package</strong></p><pre>pip install gremlinpython</pre><pre>from gremlin_python import statics<br>from gremlin_python.process.anonymous_traversal import traversal<br>from gremlin_python.process.graph_traversal import __<br>from gremlin_python.process.strategies import *<br>from gremlin_python.structure.graph import Graph<br>from gremlin_python.driver.driver_remote_connection import DriverRemoteConnection<br>from gremlin_python.process.traversal import T<br>from gremlin_python.process.traversal import Order<br>from gremlin_python.process.traversal import Cardinality<br>from gremlin_python.process.traversal import Column<br>from gremlin_python.process.traversal import Direction<br>from gremlin_python.process.traversal import Operator<br>from gremlin_python.process.traversal import P<br>from gremlin_python.process.traversal import Pop<br>from gremlin_python.process.traversal import Scope<br>from gremlin_python.process.traversal import Barrier<br>from gremlin_python.process.traversal import Bindings<br>from gremlin_python.process.traversal import WithOptions<br>from gremlin_python.driver import client, serializer, protocol<br>statics.load_statics(globals())</pre><p><strong>Setup your credentials</strong></p><pre>ENDPOINT = {ENDPOINT}<br>DATABASE = {DATABASENAME}<br>COLLECTION = {COLLECTIONNAME}<br>PRIMARY_KEY = {PRIMARY MASTER KEY}</pre><p><strong>Open an connection from Databricks to CosmosDB</strong></p><pre># Initalize Cosmos Database Connection<br>def connect_cosmosdb(ENDPOINT,DATABASE,COLLECTION,PRIMARY_KEY):</pre><pre>  gremlin_client = client.Client(<br>    &#39;wss://&#39; + ENDPOINT + &#39;:443/&#39;, &#39;g&#39;,<br>    username=&quot;/dbs/&quot; + DATABASE + &quot;/colls/&quot; + COLLECTION,<br>    password=PRIMARY_KEY,<br>    message_serializer=serializer.GraphSONSerializersV2d0()<br>    )<br>  <br>  return gremlin_client</pre><pre>gremlin_client=connect_cosmosdb(ENDPOINT,DATABASE,COLLECTION,PRIMARY_KEY)</pre><p><strong>Create a function to run any query</strong></p><pre>def run_gremlin_query(query):<br>  run = gremlin_client.submit(query).all() <br>  result=run.result()  <br>  return result</pre><p><strong>Execute a query and handle the (JSON) result</strong></p><p>For example:</p><p><em>Get details for a specific vertex ID</em></p><ul><li>pass vertex-id</li></ul><pre>query=&quot;g.V(&#39;{vertex-id}&#39;)&quot;</pre><pre>result=run_gremlin_query(query)</pre><p><em>Get vertices of a given label and with specific properties</em></p><ul><li>pass vertex label</li><li>pass filter properties</li></ul><pre>query=&quot;g.V().hasLabel(&#39;{vertex-label&#39;})<br>.has(&#39;{propertyname}&#39;,&#39;{property-value}&#39;)     # ==<br>.has(&#39;{propertyname}&#39;,gt(&#39;{value}&#39;))          # &gt;<br>.has(&#39;{propertyname}&#39;,lt(&#39;{value}&#39;))          # &lt;<br>.has(&#39;{propertyname}&#39;,containing(&#39;{value}&#39;))&quot; # contains</pre><pre>result=run_gremlin_query(query)</pre><p><em>Create a new vertex</em></p><ul><li>add vertex-label</li><li>add name of the partition to which the vertex must be assigned to</li><li>add properties, either in or outside a list</li></ul><pre>query=&quot;g.addV(&#39;{vertex-label}&#39;)<br>.property(&#39;{partition-name}&#39;,&#39;{partition-value}&#39;)<br>.property(&#39;{property-name}&#39;,&#39;{property-value}&#39;)<br>.property(list, &#39;{property-name}&#39;,&#39;{property-value}&#39;)&quot;</pre><pre>result=run_gremlin_query(query)</pre><p><strong>Scenario for an automated end to end flow</strong></p><ol><li>New data is pushed or pulled to Storage account.</li><li>Data Factory Blob Trigger is activated and <a href="https://medium.com/azure-tutorials/how-to-pass-parameters-between-your-data-factory-pipeline-and-databricks-notebook-e59b4ca6b015">passes the triggerFileName and triggerFolderPath to Databricks</a>.</li><li><a href="https://medium.com/azure-tutorials/best-practices-when-using-databricks-notebooks-in-an-automated-workflow-6e599892fc22">Read data from Storage account into spark dataframe</a>. Example file:</li></ol><pre>primary_key || label   || name<br>1           || persons || Ilse<br>2           || persons || Sagar</pre><pre>filepath={path}</pre><pre>dataframe= spark.read.option(&quot;header&quot;,&quot;true&quot;)<br> .option(&quot;multiline&quot;,&quot;true&quot;)<br> .option(&quot;sep&quot;,&quot;||&quot;).csv(filepath)</pre><p>4. Collect rows of dataframe so you can iterate rows:</p><pre>rows=dataframe.collect()</pre><p>5. Create vertex for every record in the partition of the vertex label</p><pre>for row in rows:<br>  partitionname=&quot;PARTITION&quot;<br>  partitionvalue=row[&quot;label&quot;]</pre><pre>  query=&quot;g.addV(&#39;{}&#39;)<br>   .property(&#39;PARTITION&#39;,&#39;{}&#39;),<br>   .property(&#39;primary_key&#39;,&#39;{}&#39;),<br>   .property(&#39;name&#39;,&#39;{}&#39;)&quot;<br>     .format(<br>       row[&quot;label&quot;],<br>       partitionvalue,<br>       row[&quot;primary_key&quot;],<br>       row[&quot;name&quot;]<br>     )<br>  <br>  run_gremlin_query(query)</pre><p>Let’s make it more dynamic by parameterizing the keys as well, so your script is not customized for a source but generic for any source. Example:</p><pre>for row in rows:<br>  partitionname=&quot;PARTITION&quot;<br>  partitionvalue=row[&quot;label&quot;]</pre><pre>  rowdict=row.asDict()<br>  keys=list(rowdict.keys())</pre><pre>  query=&quot;g.addV(&#39;{}&#39;)<br>   .property(&#39;PARTITION&#39;,&#39;{}&#39;)&quot;.format(row[&quot;label&quot;],partitionvalue)</pre><pre>  for key in keys:<br>    value=row[key]<br>    query+=&quot;.property(&#39;{}&#39;,&#39;{}&#39;)&quot;.format(key,value)</pre><pre>  run_gremlin_query(query)</pre><p><strong>Parallel vs. sequential ingestion</strong></p><p>To increase ingestion speed, you can break up your dataframe in several pieces and start N parallel ingestion instead of row-by-row ingestion. This will require more Request Units for your database which is more costly, so deciding on an ingestion strategy is finding a balance between cost and performance.</p><p><em>Azure Tutorials frequently publishes tutorials, best practices, insights or updates about Azure Services, to contribute to the Azure Community. Azure Tutorials is driven by two enthusiastic Azure Cloud Engineers, combining over 15 years of IT experience in several domains. Stay tuned for weekly blog updates and follow us if you are interested!</em><br><a href="https://www.linkedin.com/company/azure-tutorials">https://www.linkedin.com/company/azure-tutorials</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f2c29522a72f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/azure-tutorials/connect-azure-databricks-to-cosmosdb-gremlin-api-and-run-queries-f2c29522a72f">Connect Azure Databricks to CosmosDB Gremlin API and run queries</a> was originally published in <a href="https://medium.com/azure-tutorials">Azure Tutorials</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Best practices when using Databricks notebooks in an automated workflow]]></title>
            <link>https://medium.com/azure-tutorials/best-practices-when-using-databricks-notebooks-in-an-automated-workflow-6e599892fc22?source=rss-e1dde29db640------2</link>
            <guid isPermaLink="false">https://medium.com/p/6e599892fc22</guid>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[azure-devops]]></category>
            <dc:creator><![CDATA[Ilse Epskamp]]></dc:creator>
            <pubDate>Mon, 21 Mar 2022 08:03:46 GMT</pubDate>
            <atom:updated>2022-03-21T08:07:32.981Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Qht8dRyhFzAiZt8-" /><figcaption>Photo by <a href="https://unsplash.com/@waldemarbrandt67w?utm_source=medium&amp;utm_medium=referral">Waldemar Brandt</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>With Azure Databricks in your resource group you have a powerful tool to handle your data and analytics use cases. The platform provides several great features, and as a data engineer I feel Databricks combined with Data Factory gives you the tools to handle any data requirement that is raised. In this blog we describe a few best practices or lessons learned that we encountered when designing automated workflows with Databricks, and provide some useful generic (Python) functions which hopefully come in handy when working on your automated workflow.</p><ol><li><strong>Generalize functions</strong></li></ol><p>This seems logical right? However when handling many data sources with different file formats, delivery timestamps, and validation requirements the pitfall to write custom functions for specific scenarios is just around the corner. Don’t! Spend a little more effort to keep your functions generic so you can reuse them, even though when writing you don’t have sight on a second requirement for such a function.</p><p>Let’s look at an example. As part of your validation process, you run several generic functions on your incoming data to assess if the incoming data meets the set standards (e.g. file is latest file). Based on experience only file ABC has proven issues with column Y. Therefore you write a specific validation function for this file, which checks the content of column Y. When writing this function, don’t limit the function to only handle file ABC and column Y, but ensure any file and any column can be passed.</p><p>Not generic:</p><pre>def validate_file_abc_col_y(file_abc):<br>  file_abc.select(&quot;Y&quot;)  </pre><p>Generic:</p><pre>def validate_col(file, columnname):<br>  file.select(columnname)</pre><p><strong>2. Centralize functions</strong></p><p>What is the use of generic functions if you don’t centralize them and make them available for other notebooks? For example create a separate notebook where you keep all the functions so it serves as library for your other notebooks. In every notebook that needs the library, run this to load the content of the lib (or edit the path accordingly):</p><pre>%run ./library_notebook</pre><p>Another important benefit of centralizing functions is the reduced maintainance efforts. If a function requires a fix or update, you only have to do this once!</p><p><strong>3. Assign a separate cell to each function</strong></p><p>By assigning each function to it’s own cell, your notebook not only looks better, it is also easier to troubleshoot in case of any code errors.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/340/1*bA1Y4y3u_Z7aD1YPoOlAOg.png" /><figcaption>One function per cell.</figcaption></figure><p><strong>4. Ensure variables are consistent across notebooks and across environments</strong></p><p>It shouldn’t matter if you are working in notebook A, B or C or in DTA or P environment, common variables such as the name of a sourced datafile passed by Data Factory, or the name of the first dataframe that you create should be the same in all the notebooks. This way you don’t get confused, and more importantly, you can speed up the development. As a bonus, your notebook will have the same look and feel which makes it easier for others to understand.</p><p>For example, in every notebook name the variable that takes the triggerFileName from Data Factory also triggerFileName.</p><pre>triggerFileName=dbutils.widgets.get(&quot;triggerFileName&quot;)</pre><p>When using CI/CD to deploy your code to DTAP the variable names will be consistent cross environment.</p><p><strong>Generic functions: some examples</strong></p><p><em>Get the current date in format yyyymmdd and current timestamp in hhmmss, usable when generating current processing datetime</em></p><pre>import datetime</pre><pre>#returns yyyymmdd<br>def get_yyyymmdd():<br>    <br>    now = datetime.datetime.now().strftime(&#39;%Y%m%d&#39;)<br>    return now</pre><pre>#return hhmmss<br>def get_hhmmss():<br>    now = datetime.datetime.now()<br>    currenthour=str(now.hour)<br>    currentminute=str(now.minute)<br>    currentsecond=str(now.second)<br>    <br>    if len(currenthour)==1:<br>      currenthour=&quot;0&quot;+currenthour</pre><pre>    if len(currentminute)==1:<br>      currentminute=&quot;0&quot;+currentminute<br>    <br>    if len(currentsecond)==1:<br>      currentsecond=&quot;0&quot;+currentsecond<br>                     <br>    currenttime=currenthour+currentminute+currentsecond</pre><pre>    return currenttime</pre><p><em>Create a spark dataframe for csv, txt, parquet, json and orc files with columnnames in uppercase by passing the filepath and file extension</em></p><pre>#first fetch the file extension</pre><pre>def get_file_extension(filename):<br>  return filename.split(&quot;.&quot;)[-1]</pre><pre>#then pass the filepath and filetype to create a dataframe</pre><pre>def create_dataframe(filepath,filetype):<br>  <br>  if filetype==&quot;csv&quot; or filetype==&quot;txt&quot;:</pre><pre>    file_body = spark.read.option(&quot;header&quot;,&quot;true&quot;)<br>      .option(&quot;multiline&quot;,&quot;true&quot;)<br>      .option(&quot;sep&quot;,&quot;,&quot;).csv(filepath) </pre><pre>  elif filetype==&quot;parquet&quot;:<br>    file_body=spark.read.parquet(filepath)</pre><pre>  elif filetype==&quot;json&quot;:<br>    file_body = json.loads(spark.read.option(&quot;multiline&quot;,&quot;true&quot;)<br>      .json(filepath).toJSON().first())   </pre><pre>  else:<br>    file_body = spark.read.option(&quot;inferSchema&quot;,True)<br>      .option(&quot;escape&quot;,&#39;&quot;&#39;)<br>      .option(&#39;&quot;&#39;,&#39;&#39;)<br>      .option(&quot;multiLine&quot;,True).orc(filepath)<br>    <br>  #convert column names into uppercase<br>  for col in file_body.columns:<br>    file_body = file_body.withColumnRenamed(col, col.upper())<br>  <br>  return file_body</pre><p><em>Azure Tutorials frequently publishes tutorials, best practices, insights or updates about Azure Services, to contribute to the Azure Community. Azure Tutorials is driven by two enthusiastic Azure Cloud Engineers, combining over 15 years of IT experience in several domains. Stay tuned for weekly blog updates and follow us if you are interested!</em><br><a href="https://www.linkedin.com/company/azure-tutorials">https://www.linkedin.com/company/azure-tutorials</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6e599892fc22" width="1" height="1" alt=""><hr><p><a href="https://medium.com/azure-tutorials/best-practices-when-using-databricks-notebooks-in-an-automated-workflow-6e599892fc22">Best practices when using Databricks notebooks in an automated workflow</a> was originally published in <a href="https://medium.com/azure-tutorials">Azure Tutorials</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>