<?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 Abdeslam EL BENNOURI on Medium]]></title>
        <description><![CDATA[Stories by Abdeslam EL BENNOURI on Medium]]></description>
        <link>https://medium.com/@aelbennouri?source=rss-8b6ccdb900c8------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*zZ0DMRZnfKVxhqq2Xh9hbQ.jpeg</url>
            <title>Stories by Abdeslam EL BENNOURI on Medium</title>
            <link>https://medium.com/@aelbennouri?source=rss-8b6ccdb900c8------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 07:04:52 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@aelbennouri/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[Deploying on Microsoft Fabric: Methods, Challenges, and Best Practices]]></title>
            <link>https://medium.com/@aelbennouri/deploying-on-microsoft-fabric-methods-challenges-and-best-practices-349206e1ba88?source=rss-8b6ccdb900c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/349206e1ba88</guid>
            <category><![CDATA[cicd]]></category>
            <category><![CDATA[api]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <category><![CDATA[deployment]]></category>
            <dc:creator><![CDATA[Abdeslam EL BENNOURI]]></dc:creator>
            <pubDate>Sat, 02 May 2026 10:03:58 GMT</pubDate>
            <atom:updated>2026-09-18T08:42:01.788Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>When Microsoft Fabric was first released, deployment was essentially a manual process. Moving items between environments meant copying them by hand from one workspace to another — no automation, no versioning, no consistency. It was primitive, error-prone, and simply not scalable for real production projects.</p><p>Over time, Fabric evolved. Microsoft introduced built-in Deployment Pipelines as a first step toward structured deployments. Then came fabric-cicd, an open-source Python library maintained by Microsoft, which unlocked proper CI/CD workflows through Azure DevOps or GitHub Actions.</p><p>But even today, deployment remains one of the most challenging aspects of working with Fabric — and the gap between what was promised and what you actually have to deal with in practice is still significant.</p><p>In this article, we’ll walk through the three available methods for deploying in Fabric, their real limitations, and what you actually need to know before choosing one.</p><h3><strong>Built-In Deployment Pipelines in Fabric</strong></h3><p>Microsoft Fabric provides a built-in deployment solution accessible directly from the <strong>Deployment Pipelines</strong> section in the Fabric workspace. From there, you can view existing pipelines or create new ones.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/461/1*uUWjhjJllsbDMQ7aNtZiIg.png" /></figure><p>When creating a new deployment pipeline, you can configure:</p><ul><li><strong>The pipeline name</strong></li><li><strong>The number of stages/environments</strong> (minimum of 2, typically Dev, Test, and Prod)</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*e23NsdIj733Z0En_idG5Gg.png" /></figure><p>Once the stages are defined, you assign a <strong>workspace to each environment</strong>. Note that the user performing this assignment must have <strong>admin rights on all the workspaces</strong> being assigned.</p><p><strong>Comparing environments</strong></p><p>The interface allows you to <strong>compare items between two environments</strong> before triggering a deployment, so you can review what has changed and avoid unintended overwrites</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hT2aPIAXZAUmptzxLiDCWQ.png" /></figure><p><strong>Deployment Rules</strong></p><p>Deployment Rules allow you to override certain parameters or data sources for specific item types — such as <strong>Dataflows, Semantic Models, and Notebooks</strong> — when promoting content across environments. This is useful for pointing each environment to its own data source without modifying the items themselves.</p><p>🔗 <a href="https://learn.microsoft.com/en-us/fabric/cicd/deployment-pipelines/create-rules?tabs=new-ui">Microsoft Docs — Create deployment rules</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/557/1*UQ0bepR_BWOy9O2YMI1PNA.png" /></figure><p><strong>The Core Problem</strong></p><p>The built-in deployment pipeline works by cloning items from one environment to the next. However, this introduces a <strong>critical issue with item references</strong>.</p><p>For example, imagine you have a Data Pipeline in Dev that copies data from one Lakehouse to another — both Lakehouses exist in the Dev workspace. When that pipeline is deployed to Test or Prod, it will <strong>still reference the Dev Lakehouses</strong>, not the Test or Prod equivalents. This means your Test and Prod pipelines are silently running against Dev storage — a serious data integrity risk.</p><p>To mitigate this, you must add a <strong>parameterization layer</strong> to any item that references environment-specific resources, particularly storage solutions like Lakehouses. Without this, deployed items in Test and Prod will continue to operate against Dev, defeating the purpose of environment separation.</p><h3><strong>The </strong><strong>fabric-cicd Library</strong></h3><p>The second method for deploying in Microsoft Fabric is to use Azure DevOps Pipelines (or GitHub Actions) combined with the official <a href="https://microsoft.github.io/fabric-cicd/0.1.34/">fabric-cicd</a> Python library.</p><p><strong>Prerequisites: Git Integration</strong></p><p>Before using this method, your <strong>DEV workspace must be connected to Git</strong> via Fabric’s built-in Git Integration. You can use either GitHub or Azure DevOps as your Git provider. During the setup, you’ll be asked to specify a <strong>Git folder</strong> — this is the folder in your repository where Fabric will sync your workspace items. In the examples from this article, this folder is named src, which is exactly what you&#39;ll find in the GitHub repository. This is also where the parameter.yml file lives.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/629/1*Z7Dfv5xaI9zpIf-OM2k8YQ.png" /></figure><p>Once Git Integration is configured, any change made in your DEV workspace must be <strong>manually committed</strong> to the repository using the Git Integration panel in Fabric. These changes are synced under the src/ folder — which is exactly what fabric-cicd reads from when deploying to QAC or Prod.</p><h4>Setup</h4><p>The <a href="https://microsoft.github.io/fabric-cicd/0.1.34/">fabric-cicd</a> library requires <strong>Python 3.9 to 3.12 </strong>, and is particularly well-suited for <strong>larger, more complex projects</strong> where the built-in Deployment Pipelines start showing their limitations.</p><p>The typical setup involves three key files:</p><ul><li><a href="https://github.com/abdeslam272/Fabric-deployment/blob/main/deploy.py"><strong>deploy.py</strong></a> — the Python script that handles the deployment logic, including argument parsing, authentication, and item promotion.</li><li><a href="https://github.com/abdeslam272/Fabric-deployment/blob/main/azure-pipelines.yml"><strong>An Azure Pipeline YAML file</strong></a> — which defines the release pipeline in Azure DevOps, triggering deploy.py as part of the CI/CD workflow.</li><li><a href="https://github.com/abdeslam272/Fabric-deployment/blob/main/workspace_config.yml"><strong>workspace_config.yml</strong></a> — maps each environment (staging, main) to its corresponding Fabric workspace ID.</li></ul><p>Together, these three files form a <strong>release pipeline</strong> that automates the promotion of Fabric items from one workspace (e.g. Dev) to another (e.g. Test or Prod).</p><p>You can find example scripts and templates in this <a href="https://github.com/abdeslam272/Fabric-deployment"><strong>GitHub repository</strong></a>.</p><p><strong>The Key Principle: Service Principal</strong></p><p>The cornerstone of this setup is creating an <strong>Azure AD Service Principal</strong> and granting it <strong>Admin</strong> rights (Member is also possible, but Admin is recommended) on the QAC and Prod workspaces. This Service Principal is then used by the Azure DevOps pipeline to authenticate and deploy Fabric items automatically, without any human intervention.</p><p><strong>Good news:</strong> since all deployments are performed by the Service Principal rather than a named individual data engineer, scheduled items in QAC and Prod will run under the Service Principal identity — which solves a very common problem where scheduled pipelines or semantic model refreshes fail after a team member leaves or their permissions change.</p><p><strong>Handling Secrets Securely</strong></p><p>A question that comes up frequently is: <em>how do you handle secrets and credentials in this setup?</em></p><p>The answer is straightforward. In your Azure DevOps pipeline, you link a <strong>Variable Group</strong> to an <strong>Azure Key Vault</strong> where your secrets are stored — in our case, the Service Principal’s client_id and client_secret. These variables are then injected securely into the pipeline at runtime and passed as arguments to deploy.py:</p><pre>variables:<br>  - group: build-fabric-pipeline-secret</pre><p>This means <strong>no secrets ever touch your codebase</strong>. The Key Vault is the single source of truth for credentials, and rotating a secret only requires updating it in one place — the Key Vault — without touching any pipeline or script.</p><p><strong>The limitation — and how to fix it:</strong></p><p>Just like with the built-in Deployment Pipelines, the fabric-cicd approach faces the same <strong>item reference problem</strong>. For example, a Semantic Model linked to a Warehouse, or a Data Pipeline referencing a Lakehouse, will still point to the <strong>Dev</strong> storage layer after deployment — unless you explicitly handle this.</p><p>The solution is to add a <a href="https://github.com/abdeslam272/Fabric-deployment/blob/main/src/parameter.yml"><strong>parameter.yml</strong></a> file in your source directory (e.g. src), where you define <strong>find-and-replace rules</strong> that swap out Dev-specific references with the correct QAC or Prod equivalents at deployment time. This file is natively supported by the fabric-cicd library and is the recommended way to handle environment-specific configurations.</p><pre>find_replace:<br>  - find_value: &quot;Lakehouse_Id_DEV&quot;<br>    replace_value:<br>      staging: &quot;Lakehouse_Id_QAC&quot;<br>      main: &quot;Lakehouse_Id_PROD&quot;<br><br>  - find_value: &quot;Warehouse_Id_DEV&quot;<br>    replace_value:<br>      staging: &quot;Warehouse_Id_QAC&quot;<br>      main: &quot;Warehouse_Id_PROD&quot;</pre><p>The diagram below illustrates the full deployment architecture described in this section.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/915/1*i8jYNAV-dLCNLoawU6Asfw.png" /></figure><p>The two cornerstones of this setup are the <strong>Service Principal</strong> — which handles authentication and deployment to QAC and Prod — and the <strong>Git connection</strong> between the DEV workspace and the develop branch on Azure Repos.</p><p>One thing I didn’t cover in detail above, but which is worth mentioning for teams with multiple developers working in parallel: in Fabric, <strong>each feature branch can be represented by its own temporary workspace</strong>. The typical workflow looks like this:</p><ul><li>A developer creates a feature branch from develop</li><li>A new Fabric workspace is created and connected to that feature branch via Git Integration</li><li>The smallest available Fabric capacity is assigned to this workspace to minimize costs</li><li>Once development is complete, a <strong>Pull Request</strong> is opened to merge the feature branch back into develop</li><li>After the PR is merged, the temporary workspace is deleted</li></ul><p>This approach keeps your develop branch clean, enables parallel development without conflicts, and ensures that every change goes through a proper review process before reaching the shared DEV environment — let alone QAC or Prod.</p><h3><strong>A Common Misconception: Lakehouses and Warehouses Are Deployed Without Their Data</strong></h3><p>One point I see many people struggle with is the behavior of <strong>Lakehouses</strong> and <strong>Warehouses</strong> during deployment. When these items are deployed — regardless of the method you use — only the <strong>structure</strong> is promoted, not the data itself.</p><p>For a <strong>Warehouse</strong>, this deserves an important nuance: depending on the deployment method, you may not even get the structure. fabric-cicd promotes only the Warehouse item shell — table definitions and stored procedures require a separate DACPAC deployment, covered in the next section. Either way, the data stays behind.</p><p>In a separate article, I explored <a href="https://medium.com/@aelbennouri/how-to-handle-ingestion-versioning-in-a-microsoft-fabric-project-8569dff220d1">how to handle ingestion versioning in a Microsoft Fabric project</a> and how to approach this problem in a multi-workspace setup — worth a read alongside this one <em>(though I’ll admit it needs an update!)</em>.</p><h3>Warehouse Deployment: The Item Is Not the Database</h3><p>Among all Fabric items, the Warehouse deserves its own section — because it behaves differently from everything else, and this difference will silently break your deployments if you’re not aware of it.</p><p><strong>What actually gets deployed</strong></p><p>fabric-cicd deploys the Warehouse <strong>item shell</strong> — and nothing more. The item is created in your target workspace, it appears in the UI, it has a name and an ID. But it is empty: no tables, no views, no stored procedures.</p><p>This is documented behaviour, not a bug. The <a href="https://microsoft.github.io/fabric-cicd/latest/how_to/item_types/">official fabric-cicd documentation</a> states it explicitly: Warehouse content is not deployed, only the item shell — the DDL must be deployed separately, using a DACPAC or a tool such as dbt.</p><p>The symptom is easy to spot and easy to miss. In DEV:</p><pre>SELECT * FROM sys.procedures;  -- 106 rows</pre><p>In TEST, after a “successful” deployment:</p><pre>SELECT * FROM sys.procedures;  -- 0 rows</pre><p>The pipeline reports success. Nothing failed. The Warehouse is simply empty.</p><h4><strong>Why the Warehouse is a special case</strong></h4><p>Most Fabric items are, at their core, configuration files. A Notebook is code plus metadata. A Data Pipeline is a JSON definition. Deploying them means copying a definition from one workspace to another — and fabric-cicd does this very well.</p><p>A Warehouse is not a configuration file. It is a live <em>SQL database engine</em>, with tables, views that depend on those tables, and stored procedures that depend on both. Promoting it is not a copy operation — it is a schema migration, with dependency ordering, incremental diffs, and drop/create logic. That is a different job, and it needs a different tool.</p><p><strong>The tool: DACPAC + SqlPackage</strong></p><p>A DACPAC is a compiled snapshot of a database schema. SqlPackage is the CLI that builds it and applies it to a target. The Fabric Warehouse exposes a standard SQL endpoint (&lt;workspace-id&gt;.datawarehouse.fabric.microsoft.com, port 1433), so it can be targeted exactly like any Azure SQL database.</p><p>Note that the server name is <strong>per workspace</strong>, not per warehouse. Several warehouses in the same workspace share the same endpoint and are distinguished by the Initial Catalog value.</p><p><strong>Initial setup in Azure DevOps</strong></p><p>The Warehouse schema deployment runs as additional steps <strong>after</strong> the fabric-cicd step, in the same pipeline:</p><pre>- script: |<br>    dotnet tool update -g microsoft.sqlpackage<br>    echo &quot;##vso[task.setvariable variable=PATH]$PATH:$HOME/.dotnet/tools&quot;<br>  displayName: &#39;Install SqlPackage&#39;<br>- script: |<br>    dotnet build src/db_gold.Warehouse/db_gold.sqlproj -o $(Build.ArtifactStagingDirectory)<br>  displayName: &#39;Build DACPAC from repo&#39;<br>- script: |<br>    sqlpackage /Action:Publish \<br>      /SourceFile:&quot;$(Build.ArtifactStagingDirectory)/db_gold.dacpac&quot; \<br>      /TargetConnectionString:&quot;Server=tcp:$(targetServer),1433;Initial Catalog=$(targetDb);Encrypt=True;Authentication=Active Directory Service Principal;User Id=$(clientId);Password=$(clientSecret)&quot; \<br>      /p:ExcludeObjectTypes=&quot;Users;Logins;RoleMembership;Permissions&quot; \<br>      /p:DropObjectsNotInSource=True<br>  displayName: &#39;Publish schema to target warehouse&#39;</pre><p>Two parameters carry most of the weight here:</p><p>ExcludeObjectTypes is not optional. Without it, the deployment fails with CREATE USER is not a supported statement type — the schema carries the DEV database users, and tries to recreate them in the target. Access rights should be managed per environment anyway, never promoted.</p><p>DropObjectsNotInSource=True is what makes deletions propagate. SqlPackage is non-destructive by default: it adds and modifies, but never removes. Drop a schema in DEV without this flag, and it will quietly survive in TEST forever. Enable it with your eyes open — it will also delete anything created directly in TEST or PROD outside the Git flow. Which, arguably, is the point.</p><p><strong>Where the schema comes from: state vs. source control</strong></p><p>There are two ways to produce the DACPAC, and the difference matters more than it looks.</p><p>SqlPackage /Action:Extract connects live to the DEV warehouse and snapshots whatever is in it at that moment. This works, and it is what most tutorials show. But it means your <strong>Git repository is not the source of truth</strong> — DEV&#39;s live state is. A pull request can be reviewed, approved and merged, and deploy absolutely nothing, because the pipeline never reads the .sql files in your branch. The PR only triggers the run; it does not determine its content.</p><p>dotnet build on a .sqlproj compiles the DACPAC from the .sql files committed in the repository. What is in the PR is what gets built, and what gets built is what gets deployed. This is the approach worth investing in, and it aligns Warehouse deployment with how every other item in your pipeline already works.</p><p>The good news: you probably do not need to create that .sqlproj yourself. Fabric&#39;s <a href="https://learn.microsoft.com/en-us/fabric/data-warehouse/source-control">Source Control integration for Warehouses</a> generates and maintains one natively under src/&lt;name&gt;.Warehouse/, alongside the .sql files organised by schema. Check your repository before reaching for the SQL Database Projects extension in VS Code — it is likely already there.</p><p><strong>Expect friction</strong></p><p>Warehouse Git integration is a recent, still-maturing feature, and building the .sqlproj in a pipeline surfaces things the Fabric UI hides. Some of what you are likely to hit:</p><ul><li>Ad hoc queries saved from the SQL editor are synced into the same folder as real schema objects, and break the build. Restrict the build to the schema folders (&lt;Build Include=&quot;dbo/**/*.sql&quot; /&gt;), or upgrade the warehouse to the <a href="https://learn.microsoft.com/en-us/fabric/data-warehouse/warehouse-system-file-version-history">current system file version</a>, which moves them to a separate .sharedqueries/ folder.</li><li>Objects referencing their own warehouse with three-part names (db_gold.dbo.MyTable) fail to resolve. Three-part naming is meant for <em>other</em> warehouses; use dbo.MyTable for local references.</li><li>UNIQUE and PRIMARY KEY constraints must be declared NOT ENFORCED — Fabric Warehouse treats them as informational only.</li><li>Cross-warehouse references (gold reading from silver) produce <em>warnings</em>, not errors. Ignore them.</li></ul><p>Microsoft’s <a href="https://learn.microsoft.com/en-us/fabric/data-warehouse/troubleshoot-git-integration">Git integration troubleshooting page</a> documents most of these, including a known validation bug the product team is tracking. Reading it before you start will save you a few hours.</p><h3>Combining Fabric API with Built-in Deployment Pipelines</h3><p>The third method combines automation with the built-in Deployment Pipelines by triggering them programmatically via the <strong>Fabric REST API</strong>. I’ll walk through a concrete example using Postman.</p><p><strong>Step 1 — Generate a Bearer Token</strong></p><p>Before making any API call, you need to authenticate and retrieve a Bearer token using your Service Principal credentials:</p><pre>POST https://login.microsoftonline.com/{tenant-id}/oauth2/v2.0/token</pre><p>Pass client_id, client_secret, grant_type=client_credentials, and scope=https://api.fabric.microsoft.com/.default in the request body.</p><p><strong>Step 2 — List your Deployment Pipelines</strong></p><p>Using the Bearer token, retrieve all deployment pipelines available in your Fabric tenant:</p><pre>GET https://api.fabric.microsoft.com/v1/deploymentPipelines</pre><p><strong>Step 3 — Get the Stages of a Pipeline</strong></p><p>Retrieve the stages (Dev, QAC, Prod) of a specific pipeline. The IDs returned here are what you&#39;ll use in the deploy call:</p><pre>GET https://api.fabric.microsoft.com/v1/deploymentPipelines/{deployment_pipeline_id}/stages</pre><p><strong>Step 4 — List Items in a Workspace</strong></p><p>To find the itemId of the specific item you want to deploy:</p><pre>GET https://api.fabric.microsoft.com/v1/workspaces/{workspace_id}/items</pre><p><strong>Step 5 — Trigger the Deployment</strong></p><p>Now trigger the deployment for a specific item from Dev to QAC. Note this is a POST call, not GET:</p><pre>POST https://api.fabric.microsoft.com/v1/deploymentPipelines/{deployment_pipeline_id}/deploy</pre><p>With the following JSON body:</p><pre>{<br>  &quot;sourceStageId&quot;: &quot;stage-id-of-dev-returned-in-step-3&quot;,<br>  &quot;targetStageId&quot;: &quot;stage-id-of-qac-returned-in-step-3&quot;,<br>  &quot;items&quot;: [<br>    {<br>      &quot;sourceItemId&quot;: &quot;item-id-returned-in-step-4&quot;,<br>      &quot;itemType&quot;: &quot;SemanticModel&quot;<br>    }<br>  ],<br>  &quot;note&quot;: &quot;Deployment DEV to QAC via API&quot;<br>}</pre><p>Once triggered, the deployment will appear in the <strong>Deployment History</strong> of your pipeline in the Fabric interface — just as if you had triggered it manually.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/658/1*B_WCn4hnuC-uvZpuZy9sUQ.png" /></figure><p>This allows you to trigger a deployment pipeline for specific items only, rather than deploying the entire workspace — giving you more granular control over what gets promoted between environments.</p><p><strong>Personal take:</strong></p><p>I haven’t seen this method used in practice yet, but the concept itself is genuinely interesting. My view is that this approach works best as a <strong>complement</strong> to an already established deployment setup — once your pipelines and workspaces are fully configured, you can call the API to push targeted changes without going through the full CI/CD pipeline. Think of it as a <strong>lightweight trigger</strong> on top of an existing infrastructure, rather than a standalone deployment strategy.</p><h3>Conclusion</h3><p>Fabric is a product that is evolving rapidly — you can follow its official roadmap at <a href="https://roadmap.fabric.microsoft.com/">roadmap.fabric.microsoft.com</a> — and deployment remains one of its biggest ongoing challenges. The good news is that the options are improving, and choosing the right approach ultimately comes down to your team and project context.</p><p>Here is a simple decision guide:</p><p><strong>Use the Built-in Deployment Pipelines if:</strong></p><ul><li>Your team is Data/BI oriented, not DevOps</li><li>Your project lives entirely within Fabric, with no external code</li><li>You don’t need complex approval workflows</li><li>You want to move fast and keep things simple</li></ul><p><strong>Use the </strong><strong>fabric-cicd library with Azure DevOps if:</strong></p><ul><li>You are working across multiple workspaces</li><li>Your team includes developers who are comfortable with CI/CD practices</li><li>Your project mixes Fabric with other Azure services (Data Factory, Synapse, APIs, etc.)</li><li>You need full traceability, audit logs, and strict approval gates before every PROD deployment</li><li>You manage multiple teams working in parallel</li></ul><p><strong>Use the Fabric REST API if:</strong></p><ul><li>Your deployment infrastructure is already fully in place</li><li>You need to trigger targeted, item-level deployments without running a full pipeline</li></ul><p>No matter which method you choose, the <strong>parameterization challenge</strong> — ensuring that each environment points to its own storage layer — remains something you must address explicitly. It is the one thing Fabric does not solve for you automatically, regardless of the approach.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=349206e1ba88" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Handle Ingestion Versioning in a Microsoft Fabric Project]]></title>
            <link>https://medium.com/@aelbennouri/how-to-handle-ingestion-versioning-in-a-microsoft-fabric-project-8569dff220d1?source=rss-8b6ccdb900c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/8569dff220d1</guid>
            <category><![CDATA[microsoft-fabric]]></category>
            <category><![CDATA[ingestion]]></category>
            <category><![CDATA[lakehouse-architecture]]></category>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Abdeslam EL BENNOURI]]></dc:creator>
            <pubDate>Sat, 12 Jul 2025 08:24:00 GMT</pubDate>
            <atom:updated>2025-07-12T08:24:00.549Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>In any data engineering project, <strong>ingestion</strong> is a critical phase — especially the <em>Extract and Load</em> (EL) part of the ETL pipeline. During this phase, several questions typically arise:</p><ul><li>How frequently should we ingest data?</li><li>How many versions of the source files should we retain?</li><li>How can we optimize both <strong>storage</strong> and <strong>compute</strong> in a scalable way?</li></ul><p>In this article, I’ll walk you through how we can handle file versioning in the ingestion phase using <strong>Microsoft Fabric</strong>, particularly by leveraging new features like <a href="https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-overview"><strong>Lakehouse</strong></a>, <a href="https://learn.microsoft.com/en-us/fabric/data-engineering/how-to-use-notebook"><strong>Notebooks</strong></a>, and <a href="https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts"><strong>Shortcuts</strong></a>. I’ll also share a practical solution for retaining a defined number of file versions and managing outdated data efficiently.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*I7mNEzTwGd7KZtWL" /></figure><h3>Ingestion &amp; Version Control in a Single Lakehouse</h3><p>When we talk about ingestion in ELT, especially in modern architectures, the trend is often to <strong>dump all incoming data into a Data Lake</strong>. Microsoft Fabric introduces the <strong>Lakehouse</strong> concept — a powerful combination of the data lake and data warehouse.</p><p>Let’s simplify things and assume we are working with a <strong>single Lakehouse in one workspace</strong>. Whether you ingest into <strong>Tables</strong> or <strong>Files</strong>, there are three main versioning challenges:</p><ol><li><strong>How many versions of each file should be kept?</strong></li><li><strong>How to clean up outdated file versions?</strong></li><li><strong>How to do all this while optimizing storage and compute usage?</strong></li></ol><h4>Option 1: Ingestion with Files</h4><p>If you choose to ingest using <strong>Files</strong>, a common approach is to use a path structure that includes a <strong>date-based versioning system</strong>, such as:</p><pre>/Files/DATABASE_NAME/ENV/YYYYMMDD/SOURCE_FILE</pre><p>Each ingestion run creates a new dated folder. To clean up old versions and retain only the latest N versions, we can use a <strong>PySpark Notebook</strong> within a pipeline:</p><h4>Notebook: Delete Old Versioned Folders</h4><pre>from pyspark.sql.functions import col<br>import os<br><br># Parameters<br>max_versions_to_keep<br>lakehouse_folder_path<br># List date-based folders<br>items = mssparkutils.fs.ls(lakehouse_folder_path)<br>date_folders = [item.name for item in items if item.name.isdigit()]<br>date_folders.sort()<br># Delete oldest folders if too many<br>if len(date_folders) &gt; max_versions_to_keep:<br>    folders_to_delete = date_folders[:len(date_folders) - max_versions_to_keep]<br>    for folder in folders_to_delete:<br>        full_path = f&quot;{lakehouse_folder_path}/{folder}&quot;<br>        mssparkutils.fs.rm(full_path, recurse=True)<br>        print(f&quot;Deleted folder: {full_path}&quot;)<br>else:<br>    print(&quot;No deletion required.&quot;)</pre><blockquote><em>⚠️ As of now, Fabric Pipelines don’t support dynamic folder deletion via a built-in activity — hence the need for a notebook.</em></blockquote><h4>Pros and Cons of Using Files</h4><pre>| Advantages                                                  | Disadvantages                                         |<br>| ----------------------------------------------------------- | ----------------------------------------------------- |<br>| Supports structured, semi-structured, and unstructured data | Requires manual version control (e.g., via notebooks) |<br>| Simple folder structure enables basic versioning            | Cannot be directly queried using stored procedures    |<br>| Historical data is retained in OneLake                      | Limited integration with certain transformation tools |</pre><h3>Option 2: Ingestion with Tables</h3><p>When ingesting into <strong>Lakehouse Tables</strong>, Fabric stores data as <strong>Parquet files in OneLake</strong> behind the scenes — making tables easy to query and transform.</p><p>Advantages include:</p><ul><li>Tables are queryable directly in <strong>Notebooks, Stored Procedures</strong>, and <strong>Warehouse Queries</strong></li><li>Built-in support for <strong>UPSERTs</strong> during ingestion</li><li>Better compatibility with transformation pipelines</li></ul><p>But what about <strong>retention and cleanup</strong> of old versions?</p><p>Fabric supports <strong>Delta Lake optimization commands</strong> like VACUUM and OPTIMIZE:</p><ul><li><a href="https://learn.microsoft.com/en-us/fabric/data-engineering/delta-table-maintenance">Delta Table Maintenance in Fabric</a></li><li><a href="https://learn.microsoft.com/en-us/fabric/data-engineering/delta-table-optimize">Delta Lake Optimization &amp; V-Order</a></li></ul><blockquote><em>⚠️ Note: These commands can be </em><strong><em>resource-intensive</em></strong><em> and are best executed </em><strong><em>periodically</em></strong><em> using a notebook that targets all tables.</em></blockquote><h4>Notebook: Optimize All Tables in a Lakehouse</h4><pre>import os<br>from delta.tables import DeltaTable<br><br># Parameters<br>lakehouse_name = &quot;your_lakehouse&quot;<br># Mount lakehouse<br>lakehouse = mssparkutils.lakehouse.get(lakehouse_name)<br>mssparkutils.fs.mount(lakehouse[&quot;properties&quot;][&quot;abfsPath&quot;], &quot;/optimize_lakehouse&quot;)<br>mount_path = mssparkutils.fs.getMountPath(&quot;/optimize_lakehouse&quot;)<br># List and optimize tables<br>tables = os.listdir(f&quot;{mount_path}/Tables&quot;)<br>for table_name in tables:<br>    delta_table = DeltaTable.forPath(spark, f&quot;/{lakehouse[&#39;id&#39;]}/Tables/{table_name}&quot;)<br>    delta_table.vacuum()<br>    delta_table.optimize().executeCompaction()</pre><h4>Pros and Cons of Using Tables</h4><pre>| Advantages                                    | Disadvantages                    |<br>| --------------------------------------------- | ---------------------------------- |<br>| Ready-to-use in Stored Procedures and Queries | Cannot store unstructured formats  |<br>| Optimized storage through Delta capabilities  | `VACUUM` can be heavy on resources |<br>| Supports upsert and Delta features            | -                                  |</pre><h3>Handling Multi-Workspace Architectures</h3><p>Things get more complex in <strong>multi-workspace environments</strong>, where compute and storage usage can multiply quickly.</p><p>To handle this, Microsoft Fabric offers two powerful features:</p><ol><li><strong>OneLake</strong>: Centralized storage across workspaces</li><li><strong>Shortcuts</strong>: Virtual links to reuse data from other Lakehouses</li></ol><h4>Best Practice: Centralized Ingestion Workspace</h4><p>You can create a <strong>dedicated workspace</strong> for ingestion where all source data is stored and maintained. Then, use <strong>Shortcuts</strong> to expose selected tables or files in downstream workspaces.</p><p>This approach enables:</p><ul><li><strong>Centralized control</strong> over file versions and cleanup</li><li>Efficient usage of VACUUM/OPTIMIZE commands in one place</li><li>Seamless integration with transformation tools (Notebooks, Dataflows, etc.)</li></ul><blockquote><em>🔁 After ingestion, tables provide greater flexibility for downstream use in nearly every scenario. In my experience, Lakehouse </em><strong><em>Tables</em></strong><em> are the superior choice post-ingestion — for both performance and integration.</em></blockquote><h3>Final Summary: Tables vs. Files</h3><pre>| Feature                    | Lakehouse Tables                                  | Lakehouse Files                            |<br>| -------------------------- | ------------------------------------------------- | ------------------------------------------ |<br>| Data Types                 | Structured &amp; semi-structured                      | Structured, semi-structured &amp; unstructured |<br>| Query Compatibility        | Fully queryable via SQL, notebooks, dataflows     | Not directly queryable in SQL (Not Yet)    |<br>| Versioning Strategy        | Built-in (VACUUM, OPTIMIZE, time travel)          | Folder-based versioning + custom notebooks |<br>| Performance &amp; Optimization | Optimized via Delta Lake (e.g., V-Order, Z-Order) | Manual performance management              |<br>| OneLake Integration        | Fully supported                                   | Fully supported                            |</pre><h3>Conclusion</h3><p>This article is a reflection of <strong>my personal experience</strong> working on ingestion workflows in Microsoft Fabric. It’s not an official guide, but rather a set of insights based on real-world usage.</p><p>The goal is simple: <strong>share knowledge</strong> and help others design better ingestion pipelines.</p><p>I’d love to hear your thoughts — how do <em>you</em> handle ingestion versioning in Fabric? Let’s start a conversation.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8569dff220d1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Migrating Oracle Tables to Lakehouse with Data Factory in Microsoft Fabric: Feedback and Critical…]]></title>
            <link>https://medium.com/@aelbennouri/migrating-oracle-tables-to-lakehouse-with-data-factory-in-microsoft-fabric-feedback-and-critical-1e3667a8d65c?source=rss-8b6ccdb900c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/1e3667a8d65c</guid>
            <category><![CDATA[data-factory]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[pipeline]]></category>
            <category><![CDATA[error-handling]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <dc:creator><![CDATA[Abdeslam EL BENNOURI]]></dc:creator>
            <pubDate>Tue, 29 Apr 2025 13:55:14 GMT</pubDate>
            <atom:updated>2025-04-29T13:55:14.526Z</atom:updated>
            <content:encoded><![CDATA[<h3>Migrating Oracle Tables to Lakehouse with Data Factory in Microsoft Fabric: Feedback and Critical Error Resolution</h3><h3>1- Introduction :</h3><p>As part of a data migration project from an Oracle database to Microsoft Fabric Lakehouses, I encountered several <strong>blocking errors</strong> when copying tables automatically using <strong>Data Factory in Fabric</strong>.</p><p>In this article, I share the key issues, what they mean, and the <strong>concrete solutions</strong> I applied — especially around handling NUMBER types, building dynamic SQL queries, and fixing Delta Lake compatibility problems.</p><h3>2- The Error :</h3><p>The main error I faced was:</p><pre>DeltaInvalidBigDecimalPrecisionScale</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/561/1*Vw_B73_4xZrGgRfrkxIJGg.png" /></figure><p>This error occurred <strong>multiple times</strong>. If you search the Microsoft documentation, you’ll find it’s a <strong>known issue</strong> they’re currently working on.</p><p>Recently, Microsoft added this workaround to the official documentation:</p><p><a href="https://learn.microsoft.com/en-us/fabric/known-issues/known-issue-757-copy-activity-oracle-lakehouse-fails-number-type">Known issue - Copy activity from Oracle to lakehouse fails for Number data type - Microsoft Fabric</a></p><blockquote>Solutions and workaround</blockquote><blockquote>You can work around this issue by using a query to explicitly cast the column to <em>NUMBER(p,s)</em> or other types like <em>BINARY_DOUBLE</em>. When using <em>NUMBER(p,s)</em>, ensure <em>p &gt;= s</em> and <em>s &gt;= 0</em>. Meanwhile, the range defined by <em>NUMBER(p,s)</em> should cover the range of the values stored in the column. If not, you receive an error similar to <em>ORA-01438: value larger than specified precision allowed for this column</em>. Here&#39;s a sample query: <em>SELECT CAST(ColA AS BINARY_DOUBLE) AS ColB FROM TableA</em></blockquote><p>This advice is useful <strong>when migrating a single table</strong>, but as data engineers, we often deal with <strong>hundreds of tables</strong> with different schemas. Manually modifying each one is <strong>not scalable</strong>, and clearly doesn’t reflect a robust engineering approach.</p><h3>3- the Solution:</h3><p>The idea was to <strong>automate the detection and casting</strong> of problematic columns and generate a <strong>dynamic SQL query</strong> for each table.</p><p>Here’s how I approached it using <strong>Fabric Data Factory</strong>:</p><ol><li><strong>Start with a Lookup activity: <br></strong>This reads a <strong>configuration file</strong> that contains the list of tables to migrate (source and destination info).</li><li><strong>Use a ForEach activity:<br></strong>This loops through each row in the config and processes each table dynamically.</li><li><strong>Inside the loop</strong>:</li></ol><ul><li>A <strong>Lookup activity</strong> generates a valid SQL SELECT query for the current table.</li><li>This query is used in the <strong>source of the Copy Activity</strong>.</li></ul><p>The query generation logic (within the Lookup activity) looked like this:</p><pre>@concat(<br>  &#39;WITH columns_info AS (&#39;,<br>    &#39;SELECT COLUMN_NAME, DATA_TYPE, DATA_PRECISION, DATA_SCALE &#39;,<br>    &#39;FROM ALL_TAB_COLUMNS &#39;,<br>    &#39;WHERE OWNER = &#39;&#39;&#39;, item().SOURCE_SCHEMA, &#39;&#39;&#39; &#39;,<br>    &#39;AND TABLE_NAME = &#39;&#39;&#39;, item().SOURCE_NAME, &#39;&#39;&#39;&#39;,<br>  &#39;), formatted_columns AS (&#39;,<br>    &#39;SELECT CASE &#39;,<br>      &#39;WHEN DATA_TYPE = &#39;&#39;NUMBER&#39;&#39; &#39;,<br>        &#39;AND (DATA_PRECISION IS NULL OR DATA_SCALE IS NULL OR DATA_SCALE &gt; DATA_PRECISION) &#39;,<br>        &#39;THEN &#39;&#39;CAST(&quot;&#39;&#39; || COLUMN_NAME || &#39;&#39;&quot; AS NUMBER(38,18)) AS &quot;&#39;&#39; || COLUMN_NAME || &#39;&#39;&quot;&#39;&#39; &#39;,<br>      &#39;ELSE &#39;&#39;&quot;&#39;&#39; || COLUMN_NAME || &#39;&#39;&quot;&#39;&#39; &#39;,<br>    &#39;END AS column_expr, COLUMN_NAME &#39;,<br>    &#39;FROM columns_info &#39;,<br>  &#39;) &#39;,<br>  &#39;SELECT &#39;,<br>    &#39;&#39;&#39;SELECT &#39;&#39; || LISTAGG(column_expr, &#39;&#39;,&#39;&#39;) WITHIN GROUP (ORDER BY COLUMN_NAME) || &#39;,<br>    &#39;&#39;&#39; FROM &#39;, item().SOURCE_SCHEMA, &#39;.&#39;, item().SOURCE_NAME, &#39;&#39;&#39; AS final_query &#39;,<br>  &#39;FROM formatted_columns&#39;<br>)</pre><h4>What’s happening here:</h4><ul><li>First, it extracts all column metadata (DATA_TYPE, DATA_PRECISION, DATA_SCALE).</li><li>Then it <strong>formats each column</strong> based on its type:<br>If the column is NUMBER and has NULL precision/scale or invalid scale &gt; precision → <strong>apply a CAST</strong>.<br>Otherwise, keep the column as is.</li><li>Finally, it generates a full SELECT statement for that table with proper column formatting.</li></ul><p><strong>Result</strong>:<br> This dynamic query worked perfectly, and I was able to <strong>automate the copy of many Oracle tables</strong> without hitting the Delta precision error again.</p><h3>4 — Conclusion</h3><p>This kind of migration may seem straightforward, but it often hides <strong>complex technical challenges</strong>.</p><p>By sharing this experience, I hope it saves you time, helps you avoid common errors, and gives you a <strong>robust and scalable method</strong> to handle Oracle to Delta migration using ADF and Microsoft Fabric.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1e3667a8d65c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Containerization in Data Engineering: The Role of Docker and Kubernetes]]></title>
            <link>https://medium.com/@aelbennouri/containerization-in-data-engineering-the-role-of-docker-and-kubernetes-2217cc31be28?source=rss-8b6ccdb900c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/2217cc31be28</guid>
            <category><![CDATA[kubernetes]]></category>
            <category><![CDATA[docker]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[containers]]></category>
            <category><![CDATA[architecture]]></category>
            <dc:creator><![CDATA[Abdeslam EL BENNOURI]]></dc:creator>
            <pubDate>Sat, 11 Jan 2025 10:23:39 GMT</pubDate>
            <atom:updated>2025-01-11T10:26:24.539Z</atom:updated>
            <content:encoded><![CDATA[<h3>Containerization in Data Engineering: Why Docker Matters?</h3><h3>1-Introduction :</h3><p>Containerization was born to solve the notorious ‘It works on my machine’ problem, where applications fail to run consistently across different environments. Inspired by the idea of virtual machines, containers provide lightweight, isolated environments that package applications with their dependencies. This innovation ensures consistency, portability, and efficiency. Over time, tools like Docker and Kubernetes have become industry standards for managing containerized applications, enabling seamless scalability and orchestration. In this article, we explore why containerization, particularly through Docker, has become indispensable and how it empowers data engineering tasks to be more scalable, reliable, and manageable.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/422/0*gwavbT2ZMn-cTiAY" /></figure><h3>2-What is Containerization and Docker:</h3><p>Traditionally, companies hosted applications on servers, which involved extensive configuration and scaling challenges. Developers often created applications with dependencies tightly linked to the underlying systems, making management complex. To address this, Virtual Machines (VMs) were introduced, offering scalability but still faced compatibility issues across environments due to differing configurations.</p><p>Containers emerged as a lightweight alternative to VMs. They package applications and their dependencies into “container images,” ensuring that the same code runs identically across environments. This approach simplifies deployments with <strong>immutable releases</strong>, where the deployed application remains consistent across testing, staging, and production environments. Configurations are injected externally, further enhancing portability.</p><p>Containers offer several benefits:</p><ul><li><strong>Ease of setup</strong>: Unlike VMs, containers can be quickly configured and run locally.</li><li><strong>Simplified testing</strong>: Entire test environments can be spun up locally, enabling end-to-end (e2e) and integration tests with minimal effort.</li><li><strong>Efficiency</strong>: Containers use fewer resources (RAM and CPU) compared to VMs, making them faster and more cost-effective.</li></ul><p>Tools like Docker streamline the creation and running of containers. On top of this, <strong>Kubernetes</strong> serves as an orchestration platform, managing deployments, scaling, networking, and monitoring the health of containers, ensuring robust application management.</p><p>In essence:</p><ul><li>Docker simplifies running applications in containers without needing extensive setup.</li><li>Kubernetes manages and scales these containers efficiently.</li></ul><h3>3-Use Cases of Docker in Data Engineering:</h3><h4>3.1- ETL/ELT Workflows</h4><p>Docker plays a vital role in running containerized ETL/ELT jobs, often orchestrated with schedulers like Airflow or Kubernetes. For instance, Airflow tasks can be containerized to isolate resource usage and streamline task execution. Similarly, cron jobs can be used for simpler scheduling needs, running tasks in lightweight, scalable Docker containers. This approach ensures smooth orchestration while maintaining flexibility and efficiency.</p><p>imagine if you’re going to install the airflow locally For example, if you wanted to deploy Airflow, you could just have a VM, install Python, Postgres and Airflow. But, what if you also needed a different version of Postgres for a different app? What if you also have a whole bunch of other things that are running on that VM? What if some of them need Python 2 instead of Python 3?</p><p>that’s why docker help us a lot, it’s simplifies and isolates process</p><h4>3.2 Deployment of Tools and Frameworks</h4><p>Many popular data engineering tools and frameworks, such as dbt, Spark, and Python scripts, are deployed as Docker containers. Containerizing these tools simplifies setup, ensures consistency across environments, and accelerates deployment. For example, deploying a dbt project in Docker enables faster builds and easier management, making it a preferred practice in data pipelines.</p><p>we talked about the problems we faced with airflow as for etl workloads While Airflow isn’t the heaviest piece of software, imagine something like Spark with Python. You’d need Python, some version of the JVM, some version of Spark, as well as other dependencies. If you have a laptop, there’s a very high likelihood you’ll run into some dependency conflicts.</p><h4>3.3- Microservices architecture:</h4><p>The world is increasingly adopting microservices architectures, especially at scale, because having all developers work on the same codebase and modify the same files becomes unmanageable in large teams. While microservices might not be ideal for small teams, they become essential for handling complex, large-scale applications.</p><p>In microservices architecture, where multiple services function independently, containerization is a crucial enabler. Each service is encapsulated within its own container, ensuring isolation and independence. For example, in this <a href="https://github.com/abdeslam272/microservices-project">microservices project</a>, containerization is used to manage the following services:</p><ul><li><strong>Zookeeper:</strong> Manages and coordinates the Kafka cluster.</li><li><strong>Kafka:</strong> Acts as the message broker for streaming data between services.</li><li><strong>Cassandra:</strong> Serves as the primary database for storing processed data.</li><li><strong>Ingestion Service:</strong> Produces data messages to Kafka.</li><li><strong>Processing Service:</strong> Consumes messages from Kafka, processes them, and saves them to Cassandra.</li><li><strong>Data API Service:</strong> Provides an HTTP API for querying data stored in Cassandra.</li></ul><p><strong>Key Note:</strong><br>If each microservice is in a separate repository, a dedicated docker-compose file will be needed for each service to manage dependencies. However, if all microservices reside within a single repository, one centralized docker-compose file can be placed in the root directory. Each service will have its own Dockerfile to build and run its specific container. This structure simplifies development and deployment by providing clarity and modularity for the entire system.</p><h4>4.1-CI/CD Pipelines:</h4><p>Docker plays a critical role in streamlining CI/CD pipelines by enabling consistent environments across all stages, from development to production. Typically, developers use Docker Compose for local development environments, creating setups that mirror production environments running on Kubernetes clusters. Using the same container image throughout ensures that the tested version is deployed to production, avoiding discrepancies caused by dependency updates.</p><p>In a CI/CD pipeline, every code push triggers automated tests and builds. For example, successful builds in a “develop” branch generate a Docker image tagged with the branch name and the specific Git revision. This image is then deployed to the development environment for further testing. Upon merging to the master branch, another image is created, tagged as production-ready, and deployed to staging for approval before reaching production. Tools like Jenkins or Drone CI often automate these workflows, building, testing, and deploying Docker images while handling dependencies and versioning efficiently.</p><p>Docker also supports debugging and environment differentiation through configuration files like .env, enabling tailored settings such as database connections. This unified approach minimizes errors, enhances collaboration, and accelerates deployment cycles, making Docker indispensable for modern CI/CD pipelines.</p><p>For example, this <a href="https://github.com/nickjj/docker-rails-example">production-ready Rails app</a> demonstrates how to use the same Dockerfile and docker-compose.yml file across all environments. It leverages an .env file to manage environment-specific settings such as database connection strings, showcasing Docker&#39;s flexibility and reliability in CI/CD workflows.</p><h3>5- Challenges and Limitations of Docker in Data Engineering:</h3><p>Docker is a great tool that makes many tasks easier, but it does come with its challenges. For one, containers are temporary, so anything not saved to a volume disappears when the container stops, which can cause problems if you’re not careful. Security is another concern because containers share the host system’s kernel. If one container gets hacked, it could put your whole system at risk. There’s also the issue of trusting third-party images — there have been cases where people slipped in malicious code, like crypto mining. Managing network ports can get tricky, especially if you’re running multiple services on the same machine. Plus, Docker can slow things down a bit if the host system isn’t very powerful. Lastly, it can be hard to learn at first, which might discourage some people.</p><h3>6- Conclusion:</h3><p>To directly address the question, “Does a Data Engineer need to learn Docker?” — the answer is that while Docker is not strictly mandatory, its benefits make it an invaluable tool in a data engineer’s toolkit. <strong>Docker’s ability to ensure environment reproducibility and consistency significantly simplifies workflows, reducing the complexities and headaches associated with alternative solutions.</strong> For data engineers aiming to streamline deployments, enhance collaboration, and improve scalability, learning Docker is a worthwhile investment.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2217cc31be28" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Medallion Architecture: The Number of Layers Used and Integration with Microsoft Fabric]]></title>
            <link>https://medium.com/@aelbennouri/the-medallion-architecture-the-number-of-layers-used-and-integration-with-microsoft-fabric-cd540e1f8b4b?source=rss-8b6ccdb900c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/cd540e1f8b4b</guid>
            <category><![CDATA[data-lakehouse]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[medallion-architecture]]></category>
            <dc:creator><![CDATA[Abdeslam EL BENNOURI]]></dc:creator>
            <pubDate>Sat, 07 Sep 2024 10:00:16 GMT</pubDate>
            <atom:updated>2024-09-07T10:00:16.707Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>1-Introduction</strong></h3><p>The Medallion Architecture is a framework designed to organize data within a data lake into distinct layers, each serving a specific purpose. By categorizing data into multiple layers, organizations can streamline their data processing workflows, improve data quality, and enable more efficient data analysis.</p><p><strong><em>The Medallion Architecture is a structured and sophisticated design pattern integral to Data Lakehouses, meticulously organizing data into distinct, purposeful layers.(</em></strong><a href="https://dview.io/blog/data-lakehouse-architecture-2023"><strong><em>this</em></strong></a><strong><em>)</em></strong></p><p>In the world of data warehousing, the Medallion Architecture is considered one of its foundational pillars. It is used primarily for monitoring data quality across layers, allowing us to collect data from various sources, transform and standardize it, and then serve it to end users. Typically, three layers are used: the Bronze, Silver, and Gold layers. However, there is no fixed rule regarding the number of layers. The key question is: <strong><em>how do we determine the optimal number of layers and the scenarios in which additional layers should be added or omitted?</em></strong> <em>Additionally, we’ll explore how this architecture can be practically implemented, with Microsoft Fabric as a potential choice.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*QpUnpcgBFoE43Lhb.png" /></figure><h3>2- The Principle of The Medallion Architecture</h3><p>The Medallion Architecture is built on the principle of organizing data into <strong><em>multiple, distinct layers to progressively refine and improve its quality and usability.</em></strong> Each layer represents a stage in the data processing pipeline, ensuring that data is systematically cleansed, transformed, and enriched as it moves through the architecture.</p><p>Starting with raw, unprocessed data from various sources, the data is stored in its original form to preserve integrity and provide a historical record. As it progresses through the layers, transformations such as data cleansing, standardization, and enrichment are applied to improve its structure and quality. By the time the data reaches the final layer, it is highly refined and optimized for specific use cases like business intelligence, reporting, and advanced analytics.</p><p>This structured methodology ensures efficient and consistent data handling, making it easier for data scientists, analysts, and business users to access and utilize the data effectively. The Medallion Architecture is particularly beneficial for managing large volumes of data, as it allows for systematic, scalable, and reliable processing.</p><p>In <a href="https://www.databricks.com/glossary/medallion-architecture">the documentation provided by Databricks</a>, several key advantages of the Lakehouse architecture are highlighted:</p><ul><li><strong>Simplified Data Model</strong>: The Lakehouse architecture offers a straightforward data model that is easy to understand and implement.</li><li><strong>Incremental ETL</strong>: It supports incremental ETL processes, allowing data to be updated and processed efficiently.</li><li><strong>Data Reproducibility</strong>: You can easily recreate tables from raw data at any time, ensuring flexibility and data integrity.</li><li><strong>ACID Transactions and Time Travel</strong>: The architecture supports ACID transactions and time travel, enabling reliable data operations and the ability to query historical data states</li></ul><h3>3- The Number of Layers Used</h3><p>The number of layers used in the Medallion Architecture depends on the project’s nature, client demands, and the data sources involved. For typical projects like E-Commerce Customer Analytics, where data sources include website logs, product inventory, and customer reviews, and where data volume, velocity, and variety are moderate to high, three layers (Bronze, Silver, Gold) are usually sufficient.</p><p>However, <strong><em>for projects with very high data volume, velocity, and variety — such as real-time data ingestion from IoT devices — a pre-Bronze layer may be necessary</em></strong>. This layer acts as <strong><em>a buffer</em></strong>, allowing raw data to be ingested rapidly without immediate processing, preventing <strong><em>bottlenecks </em></strong>and ensuring that no data is lost during ingestion.</p><p>When deciding whether to include a Platinum layer, <strong><em>you should consider the project’s processing requirements.</em></strong> If advanced data enrichment, ultra-low latency, and high precision are needed, a Platinum layer is beneficial. For instance, in financial trading platforms where real-time stock market data is analyzed to make split-second trading decisions, the Platinum layer ensures <strong><em>ultra-refined data quality and specialized insights</em></strong>, safeguarding against inaccuracies that could impact critical decisions.</p><h3>4. The Layers of Medallion Architecture and Microsoft Fabric</h3><p>The Medallion Architecture, with its structured layering approach, aligns seamlessly with modern data platforms like Microsoft Fabric. By integrating Medallion Architecture principles within Microsoft Fabric, organizations can manage and optimize their data workflows, ensuring high-quality, scalable, and efficient data processing across all layers.</p><p>Microsoft Fabric provides a robust environment for implementing the Medallion Architecture, allowing organizations to optimize their data processing workflows by leveraging both <strong><em>the Polaris and Spark engines</em></strong>. Each engine has unique strengths that can be harnessed to ensure efficient, scalable, and high-performance data processing across different layers of the architecture. Here’s how you can implement the Medallion Architecture within Microsoft Fabric:</p><ol><li><strong>Pre-Bronze Layer (if needed):</strong><br>The Pre-Bronze layer <strong><em>acts as an initial staging area for raw data, capturing and holding it before any processing occurs</em></strong>. This layer is particularly useful in scenarios where data is ingested at high velocity or from a variety of sources, such as IoT devices or real-time analytics. In Microsoft Fabric, a Lakehouse is an ideal choice for this layer due to its flexibility and capability to manage diverse data types. The Lakehouse also supports shortcuts, which can streamline data management by referencing existing data without duplicating it, making the Pre-Bronze layer efficient and scalable.</li><li><strong>Bronze Layer:</strong><br>The Bronze layer is where raw data is first ingested and stored in its original form, preserving its fidelity for future processing. The decision to use a Warehouse or Lakehouse at this stage depends on <strong><em>the specific requirements of the project and the team’s expertise</em></strong>. While a Warehouse offers structured data storage and optimized performance for certain types of data, <strong><em>a Lakehouse is generally recommended for the Bronze layer due to its ability to handle unstructured or semi-structured data and its seamless integration with Spark for processing.</em></strong> The Lakehouse allows for a more flexible and comprehensive approach to data management, making it suitable for projects with complex data needs.</li><li><strong>Silver Layer:</strong><br>At the Silver layer, data undergoes significant transformation and cleansing, preparing it for more refined analysis. This layer benefits greatly from <strong><em>the power of the Spark engine</em></strong>, which is highly efficient in processing large datasets and performing complex transformations. Using a Lakehouse in this layer allows you to leverage Spark’s capabilities directly within notebooks, facilitating interactive data exploration, batch processing, and real-time analytics. The Lakehouse’s scalability and performance make it the ideal choice for the Silver layer, where the focus is on enhancing data quality and consistency.</li><li><strong>Gold Layer:</strong><br>The Gold layer is where the most refined and processed data resides, ready for reporting and business intelligence use cases. In Microsoft Fabric, a Warehouse is the optimal choice for the Gold layer, as it allows for the creation of highly optimized, structured datasets. By utilizing stored procedures and <strong><em>the Polaris engine</em></strong>, you can achieve high-performance query execution, enabling fast and reliable access to critical business data. The Warehouse also supports advanced indexing and data partitioning, which further enhances performance for analytical queries.</li><li><strong>Platinum Layer (if needed):</strong><br>The Platinum layer is reserved for scenarios that require ultra-refined data with the highest levels of precision, often used for advanced analytics or mission-critical applications. This layer is crucial in industries where decision-making relies on near-zero latency and the utmost data accuracy, such as financial trading or high-frequency market analysis. In Microsoft Fabric, a Warehouse is again the preferred choice for the Platinum layer. The Polaris engine’s advanced features, such as distributed query processing and real-time data ingestion, ensure that the Platinum layer delivers exceptional performance and reliability, making it suitable for high-stakes environments.</li></ol><h3>5- Conclusion</h3><p>The Medallion Architecture is a highly beneficial framework for data processing, offering flexibility and scalability. We’ve seen how additional layers, such as a <strong><em>pre-Bronze layer for high-velocity data ingestion or a Platinum layer for advanced analytics, can be added based on specific project requirements.</em></strong> These layers ensure the architecture can handle various data processing needs, from high-speed ingestion to ultra-precise analytics, making it a robust solution for modern data challenges.</p><h3>6- Final Thoughts</h3><p>In today’s data-driven world, working with the Medallion architecture has become almost inevitable due to its numerous benefits and ease of use. As more companies and businesses move to the cloud, mastering the Medallion architecture is essential. This mastery goes beyond understanding its basics — it involves knowing how to determine the optimal number of layers for your specific needs.</p><p>Integrating Microsoft Fabric with the Medallion architecture can be incredibly advantageous. However, deciding whether to use Lakehouses or Warehouses for your layers can be confusing. Often, this choice depends on the team’s expertise and the desired outcomes. Finding the right balance is key to unlocking the full potential of your data architecture.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cd540e1f8b4b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exploring Apache Spark: Insights from Articles and Research]]></title>
            <link>https://medium.com/@aelbennouri/revolutionizing-big-data-processing-unveiling-apache-sparks-power-and-potential-0309478a031d?source=rss-8b6ccdb900c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/0309478a031d</guid>
            <category><![CDATA[apache-spark]]></category>
            <category><![CDATA[mapreduce]]></category>
            <category><![CDATA[scala]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[dataframes]]></category>
            <dc:creator><![CDATA[Abdeslam EL BENNOURI]]></dc:creator>
            <pubDate>Sun, 12 May 2024 15:02:14 GMT</pubDate>
            <atom:updated>2024-05-13T17:17:20.174Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>1-Introduction:</strong></h3><p>In the realm of data engineering, the exponential growth of global data production has necessitated innovative solutions to address the challenges posed by the 4Vs: Volume, Velocity, Variety, and Veracity. During my journey of learning Apache Spark, one of the most widely used and highly sought-after technologies in the field, I’ve encountered numerous articles, papers, and blogs discussing various aspects of Apache Spark. In this compilation, I aim <strong>to showcase and summarize the most efficient and informative articles</strong> I have come across, providing insights into the capabilities, applications, and advancements of Apache Spark. Among these solutions, Apache Spark emerges as a significant advancement, providing exceptional performance and flexibility in distributed data processing. This report delves into the evolution from MapReduce to the introduction of RDDs, DataFrames, and datasets, as well as the core concepts of Apache Spark, elucidating its transformative impact in the domain of big data processing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kcW7PxXjVyhnSHk9tziTZw.png" /></figure><h3>2- Introduction of Map Reduce</h3><p>Let’s begin our exploration with one of the most impactful scientific papers in the realm of big data, focusing on MapReduce: “<a href="https://static.googleusercontent.com/media/research.google.com/fr//archive/mapreduce-osdi04.pdf">MapReduce: Simplified Data Processing on Large Clusters</a>,” authored by Jeffery Dean and Sanjay Ghemawat and published by Google in 2004.</p><p>In this paper we got the introduction of Map Reduce as a programming model and an associated implementation for processing and generating large data sets. in this paper the most valuable information that give us is :</p><p>MapReduce offers a programming model that simplifies distributed data processing, primarily utilizing <strong>two key functions: Map and Reduce</strong>. Users of the MapReduce library express computations through these functions, enabling streamlined handling of vast datasets.</p><p>This framework is engineered for scalability, fault tolerance, and optimal resource utilization across distributed clusters of commodity hardware.</p><p>MapReduce has revolutionized distributed computing by providing scalable and fault-tolerant solutions for processing massive datasets, with applications spanning from web indexing to data mining. Its implementation encompasses a master-worker architecture, fault tolerance mechanisms, and efficient data distribution and task execution strategies.</p><p>Moreover, the paper provides an in-depth overview of the execution process in MapReduce that used in the computing environment in wide use at Google (at 2004) : elucidating several crucial steps:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*cTeUTEBiiWM095mzSmCv4w.png" /></figure><ol><li>Input Data Splitting: The input data is divided into fixed-size pieces called input splits, which are processed independently by different worker nodes in the cluster.</li><li>Map Task Execution: Each worker node processes its assigned input split using the Map function, which generates intermediate key-value pairs. These intermediate pairs are buffered in memory and periodically written to disk to manage memory usage.</li><li>Shuffle and Sort: The intermediate key-value pairs are partitioned by key and distributed across the cluster. All values associated with the same key are grouped together. The partitioned data is then sorted by key to facilitate efficient processing in the Reduce phase.</li><li>Reduce Task Execution: Each worker node processes a subset of the partitioned data using the Reduce function. The Reduce function aggregates values associated with the same key, producing the final output.</li><li>Output Writing: The output of the Reduce tasks is written to the distributed file system, typically Hadoop Distributed File System (HDFS), to make it available for further processing or analysis.</li></ol><p>This comprehensive execution overview provides a detailed understanding of the operational intricacies of MapReduce, underpinning its pivotal role in the domain of distributed computing.</p><h3>2- Introduction Of RDDs:</h3><p>For a significant period, MapReduce served as the predominant tool for processing Big Data. However, a pivotal moment arrived in 2011 with the publication of a paper by researchers from Berkeley, led by Matei Zaharia. This seminal paper, titled “<a href="https://www.usenix.org/system/files/conference/nsdi12/nsdi12-final138.pdf">Resilient Distributed Datasets: A Fault-Tolerant Abstraction for In-Memory Cluster Computing</a>,” introduced RDDs (Resilient Distributed Datasets) and the Spark system, which incorporated RDDs.</p><p>RDDs represent a significant advancement in cluster computing, offering <strong>an efficient, fault-tolerant abstraction for data sharing in distributed applications</strong>. Unlike traditional storage abstractions that rely on data replication for fault tolerance, <strong>RDDs employ lineage-based recovery</strong>, enabling efficient recovery of data using coarse-grained transformations. Implemented in the Spark system, <strong>RDDs outperform Hadoop</strong> significantly, particularly in iterative applications, achieving up to a <strong>20x</strong> performance improvement. Spark’s in-memory computing capability enhances performance by orders of magnitude, enabling interactive querying of large datasets and speeding up real-world data analytics reports substantially.</p><p>Moreover, <strong>RDDs facilitate efficient data reuse</strong> through <strong>coarse-grained</strong> <strong>transformations </strong>such as map, filter, and join operations. <strong>Spark’s RDD abstraction, implemented in approximately 14,000 lines of Scala</strong>, offers a language-integrated API similar to <strong>DryadLINQ</strong>, combining conciseness and efficiency. The Spark interpreter provides an interactive shell for exploratory data analysis and processing, making it particularly useful for handling large traces and exploring datasets stored in Hadoop Distributed File System (HDFS). Spark provides various storage options for persistent RDDs, including in-memory storage as deserialized or serialized data and on-disk storage, ensuring flexibility and scalability.</p><p>Additionally, RDDs empower Spark with the ability to express diverse programming models, including MapReduce, DryadLINQ, SQL, and Pregel, making it a powerful tool for <strong>interactive data mining and analytics</strong>.</p><h3>3- Apache Spark’s Evolution and Versatility</h3><p>Let’s now delve into the final paper highlighted in this report, one that lays the foundation for Apache Spark. This report, authored by the same group of engineers who introduced RDDs, led by Matei Zaharia, is titled: “<a href="https://people.eecs.berkeley.edu/~matei/papers/2016/cacm_apache_spark.pdf">Apache Spark: A Unified Engine for Big Data Processing</a>.”</p><p>The exponential growth in data volume has outpaced the capabilities of individual machines, leading to a burgeoning demand for systems capable of distributing computations across multiple nodes. This demand coincided with the emergence of diverse cluster programming models, including MapReduce, Dremel, and Pregel, signaling a departure from the conventional <strong>“one-size-fits-all”</strong> approach, even within the realm of relational databases.</p><p>The inherent complexity and diversity of big data necessitated innovative solutions. Apache Spark rises to these challenges by leveraging a programming model <strong>similar to MapReduce</strong> while introducing a groundbreaking <strong>data-sharing abstraction</strong> (RDDs). Spark’s adaptability offers several advantages, streamlining application development through a unified API and enhancing processing efficiency by seamlessly integrating tasks.</p><p>To illustrate this unification, consider the evolution from separate portable devices to smartphones, emphasizing the value of integrated solutions. At <strong>the core of Spark’s architecture are RDDs</strong>, fault-tolerant collections of partitioned objects across clusters, accessible through functional programming <strong>APIs in Scala, Java, Python, and R</strong>. This <strong>data-sharing</strong> capability sets Spark apart from its predecessors and significantly accelerates interactive queries and iterative algorithms.</p><p>Furthermore, <strong>RDDs </strong>enable automatic <strong>recovery from failures</strong> through <strong>lineage-based mechanisms</strong>, surpassing traditional replication methods in efficiency. Spark’s versatility is evident in its extensive libraries, encompassing SQL and DataFrames, Spark Streaming, GraphX, and MLlib, catering to diverse big data processing needs.</p><p>The performance, scalability, and adaptability of Spark have been demonstrated across various applications, from batch processing to real-time analytics, and even scientific endeavors such as large-scale spam detection and image processing. With widespread adoption and continuous enhancements, Spark continues to redefine the landscape of big data processing, offering unmatched performance and flexibility across a myriad of use cases.</p><h3>4- Spark’s Evolution: Introducing DataFrames for Enhanced Performance and Usability:</h3><p>The core Spark API was based on functional programming over distributed collections that contain arbitrary types of Scala, Java, or Python objects. While this approach was highly expressive, it also made programs more difficult to automatically analyze and optimize.</p><p>In response to the challenges, Spark introduced <strong>DataFrames </strong>in 2015. These DataFrames provide a declarative API based on relational algebra, facilitating operations on structured data akin to database tables. Despite their familiarity in Python and R, DataFrames offer <strong>advanced functionalities like filtering and aggregation through a restricted expression API</strong>. Leveraging Spark SQL’s query planner, DataFrames automatically parallelize and optimize computations, enhancing performance. This innovation quickly gained popularity, with 60% of respondents adopting DataFrames in a 2015 survey. To further streamline usage, Spark developed <strong>Datasets</strong>, a type-safe interface enabling <strong>Java and Scala programmers to view DataFrames</strong> as statically typed collections while benefiting from relational optimizations, thus bridging the gap between usability and performance.</p><h3>5- Conclusion:</h3><p>In summary, Apache Spark has emerged as a transformative force in the realm of big data processing. From the foundational concepts introduced in the MapReduce paper to the innovative RDDs and Apache Spark, each step represents a significant advancement in distributed computing. Spark’s in-memory computing capability, versatile programming model, and comprehensive libraries have revolutionized data processing, driving innovation across various industries. With its scalability, fault tolerance, and adaptability, Apache Spark continues to redefine the landscape of big data processing, offering unmatched performance and flexibility for a myriad of use cases.</p><h3>6 -Continuing the Journey: Exploring Apache Spark Further:</h3><p>In this project, I aimed to provide a theoretical overview of Apache Spark. If you have any remarks or ideas, I would be happy to discuss them further. In the coming days, I plan to delve deeper into the world of big data processing and undertake an end-to-end project on large datasets to demonstrate the practical application of Apache Spark.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0309478a031d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Barcelona’s Journey: Navigating Challenges, Analyzing Performances, and Welcoming Vitor Roque With…]]></title>
            <link>https://medium.com/@aelbennouri/barcelonas-journey-navigating-challenges-analyzing-performances-and-welcoming-vitor-roque-with-6a371ef9a0ba?source=rss-8b6ccdb900c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/6a371ef9a0ba</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[web-scraping]]></category>
            <category><![CDATA[fc-barcelona]]></category>
            <dc:creator><![CDATA[Abdeslam EL BENNOURI]]></dc:creator>
            <pubDate>Mon, 01 Jan 2024 19:38:47 GMT</pubDate>
            <atom:updated>2024-01-01T20:19:10.092Z</atom:updated>
            <content:encoded><![CDATA[<h3>Barcelona’s Journey: Navigating Challenges, Analyzing Performances, and Welcoming Vitor Roque With Web Scraping and python</h3><h3>1. Introduction</h3><p>In the realm of football, my unwavering loyalty lies with FC Barcelona. The recent years, however, have tested the resilience of fans like myself. We weathered a devastating 8–2 loss to Bayern Munich, the unforgettable remontada against Liverpool at Anfield, and the departure of Lionel Messi, widely regarded as the greatest player of all time. The club also grappled with administrative turmoil and corruption, and to add to our woes, Barcelona was eliminated from the Champions League group stage for the first time in over two decades. For loyal Barcelona fans, this series of setbacks felt like a painful nightmare.</p><p>Hope dawned with the arrival of Xavi Hernandez as the coach, a beacon of optimism. Xavi, the best midfielder in Barcelona’s history and one of the finest in the game’s history, has always been synonymous with the best version of Barcelona.</p><p>In the initial 18 months of Xavi’s tenure, he steered Barcelona from the 14th position in La Liga to an impressive second position in the 2021/2022 season, trailing only behind the champions, Real Madrid. He orchestrated stellar performances in La Liga and the Europa League, securing wins against Real Madrid in the Santiago Bernabéu and Valencia in the Mestalla. The 2022/2023 season, marked by a successful transfer window, saw Xavi guide the team to victory in La Liga and the Spanish Super Cup. However, on the European stage, the team faced challenges, resulting in their elimination from the Champions League group stage once again.</p><p>During the previous season, on their way to winning La Liga, Barcelona exhibited an outstanding defensive performance, showcasing an impressive ability to secure points and maintain clean sheets. Conceding only 20 goals in 38 games while keeping 26 clean sheets demonstrated the team’s defensive prowess.</p><p>As we approach the end of this year, the current season paints a different picture. Our defense has not been as sturdy, conceding 21 goals in 18 games.</p><p>Meanwhile, as I strive to master my skills in web scraping, Python, and other technologies, I saw an opportunity to merge my passion for FC Barcelona with my quest for proficiency. In this report, I will take you on a journey into the realms of web scraping and visualization to uncover the dynamics at play in Barcelona</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/1*TrHTJZfAyP69nrLebF7KVw.jpeg" /></figure><h3>2. Data Collection Through Web Scraping</h3><p>For the initial phase of data collection, I have opted to utilize the Understat website. This platform offers extensive statistics crucial for generating valuable and accurate analyses. The primary objective is to compare the current year with the previous one across various criteria. Specifically, this website allows us to extract data for Barcelona over these two years, as well as data for other clubs.</p><p>In the first stage, I extracted data pertaining to the last season, encompassing game-related information such as results, goals, and expected goals. Additionally, I collected team statistics, including formation, gameState, timing, and shot zone. Furthermore, I gathered individual player data for Barcelona, including games played, time on the field, goals, expected goals (xG), assists, expected assists (xA), shots, key passes, yellow cards, red cards, and player position.</p><h3><strong>3. Data Transformation: Shaping Insights from Raw Numbers</strong></h3><p>In the data transformation process, I executed several steps to enhance the structure and usability of the datasets for the years 2022 and 2023.</p><p>Firstly, I separated the ‘goals’ and ‘xG’ columns into distinct components, breaking down home (‘h’) and away (‘a’) goals and expected goals for each team. This restructuring allows for a more granular analysis of scoring and expected goal metrics.</p><p>Following that, I extracted the ‘title’ information from the ‘h’ and ‘a’ columns, representing the home and away teams, respectively. This simplifies team identification in the dataset.</p><p>Next, I converted the ‘datetime’ column to a datetime format, facilitating time-based analyses.</p><p>For clarity and to streamline the dataset, I removed unnecessary columns, including ‘goals’, ‘xG’, ‘h’, ‘a’, ‘forecast’, ‘isResult’, and ‘id’.</p><p>The same series of transformations were then applied to the dataset for the year 2023, ensuring consistency and comparability between the two datasets.</p><p>Moving on to the situational data, I focused on specific situations like ‘OpenPlay,’ ‘FromCorner,’ ‘DirectFreekick,’ ‘SetPiece,’ and ‘Penalty.’ For each situation, I extracted relevant metrics such as shots, goals, and xG for both the team and against the team.</p><p>In the player data transformation, I standardized numeric columns such as ‘time,’ ‘goals,’ ‘assists,’ ‘shots,’ ‘games,’ ‘key_passes,’ ‘yellow_cards,’ ‘red_cards,’ ‘xG,’ and ‘xA.’ This ensures numerical accuracy and uniformity in the dataset.</p><p>In summary, these transformations were carried out to create more structured, standardized, and comparable datasets for in-depth analysis and reporting.</p><h3>4. Data Visualisation</h3><p>After careful consideration, I have decided to work on the same notebook for both seasons. For visualization, I will combine the two to understand the differences and make comparisons. Additionally, to avoid making this report too extensive with code implementations, I will put it on GitHub. You can find the link at the end of this report.</p><p>Now, let’s delve into our team’s performance last year by calculating some key indicators. First, we’ll examine the players who played the most minutes, starting with the total minutes played by Barcelona players.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5rFGk16OmuIkm_0Kcv7mpg.png" /></figure><p>For the last season, our analysis reveals that players with a share of total minutes exceeding 50% include Ter Stegen, Araujo, Christensen, Kounde, Busquets, Gavi, Pedri, De Jong, Raphinha, Lewandowski, and Balde. This lineup, well-known among Barcelona fans, formed the starting 11, often transitioning from a 4–3–3 to a 3–2–2–3 formation, with adjustments necessitated by injuries, such as those to Dembélé.</p><p>However, in the current season, changes have occurred with Busquets’ departure and injuries affecting Gavi, Ter Stegen, Pedri, and Raphinha. Additionally, new arrivals like Gundogan, Cancelo, and Felix have influenced player dynamics, especially with Gundogan dominating the midfield. This marks a significant shift in the players receiving the most playing time.</p><p>Now, let’s shift our focus to the attacking side, examining goals and expected goals (xG) for players who have scored at least one goal in the first 18 games of the current season.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HucGBPcQcATBhSIXHsDp8g.png" /></figure><p>An initial observation is that xG values consistently surpass the actual goals scored, suggesting potential inefficiencies in both seasons. To gain deeper insights, we’ll analyze the percentage and cumulative sum of these statistics, aiming to uncover additional patterns and trends.</p><h4>The Offensive Side:</h4><p>In total, last year Barcelona scored 69 goals. This season, in the 18 games played, Barcelona scored 32. The Total Goals per Game is presented in this figure:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/617/1*VbmHM4MF26OjaKQxd2Jv2w.png" /></figure><p>We scored approximately the same number of goals per match. The difference is 0.03801169590643294 per game.</p><p>For the xG, in this figure, we can see the Total xG per Game:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/611/1*UWZ1aRCxq15jbj7Tqswm9A.png" /></figure><p>We can clearly observe that this year we have created more chances, resulting in a higher xG of 0.19457841771573126 per game. This can be attributed to the contributions of Joao Cancelo and Joao Felix, who have significantly increased our ability to generate scoring opportunities.</p><p>In the Total Goals / Total xG Ratio by seasons, we obtain this figure:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/611/1*UNf9bOr1WNO8h7S0tx8yPg.png" /></figure><p>We can clearly observe that last year, our efficiency was higher at 85.42%, whereas this year it stands at 76.62%. The difference is 8.8%.</p><blockquote><strong><em>Note:</em></strong></blockquote><p>This year, on the offensive side, we are creating more chances compared to last year. Logically, the goal-per-game indicator should be higher than last year’s. However, it isn’t, indicating that it’s not just our strikers who are performing below par this season. The number of chances we create is greater than last year, the very year we won the title. With the arrival of Vitor Roque, I hope this situation will change.</p><h4>The Defensive Side:</h4><p>Last year, we conceded only 20 goals in 38 games. This year, in just 18 games, we have already conceded 21 goals. What a joke!</p><p>in this figure, we present the Total Conceded per game :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/611/1*LxhkZV72OO3REUJH4pNPjg.png" /></figure><p>The goals we conceded per game this year are more than double compared to last year.</p><p>Let’s examine the xG to understand whether we conceded these goals due to our poor performance or if they are simply exceptions resulting from luck.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/611/1*yZG_MruTG_tocSx9Wa1WYg.png" /></figure><p>Well, it’s not just that the goals conceded haven’t doubled, but the performance is still subpar this season. We are conceding 1.175 goals per game compared to 0.877 last season, indicating a significant difference of 0.297. This is noteworthy.</p><p>Now, let’s examine the ratio between the goals conceded and the xG generated against us.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/611/1*v4fMo1eutFUNvdPf3V9ESw.png" /></figure><p>That’s alarming, and it’s logically unsound. This year, we’re conceding 0.9928 goals for every 1 expected goal against us, indicating an efficiency of defense at 0.72%. It’s perplexing — it seems like every shot against us is turning into a goal. What’s going on?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OMUsYyjfRUkLXawtsRoDAw.jpeg" /></figure><blockquote><strong><em>Note :</em></strong></blockquote><p>This season’s defensive performance is a stark contrast to the last. In just 18 games, we’ve conceded 21 goals, surpassing last year’s total. Goals per game have more than doubled, rising from 0.877 to 1.175. The alarming efficiency ratio of 0.72% — conceding 0.9928 goals for every expected goal — suggests a critical need for defensive analysis and improvement.</p><h3>Comparing Barcelona’s Seasonal Performances: A Tale of Home and Away Challenges:</h3><p>Last season, Barcelona secured 88 points and emerged as champions. In the 2021 season, Real Madrid claimed the title with 86 points, while in 2020, Atletico Madrid were crowned champions, also with 86 points. Typically, champions tend to accumulate around this range of points, with some exceptions in certain seasons.</p><p>Now, let’s compare our results against the teams we’ve played so far this season to last year. Keep in mind that we are focusing on the first 18 games of this current season.</p><p>Results of Barcelona at home (Camp Nou) last season:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/713/1*MySZmUX5XurcdTec4FnRoA.png" /></figure><p>Barcelona accumulated a total of 48 points in 19 home games with just one loss and three draws, resulting in a loss of 9 points.</p><p>This season, in 10 home games at home (Montjuic Stadium):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/713/1*7ECzPcazIN7Irm5ivfAAxA.png" /></figure><p>This season, in 10 home games, Barcelona has already lost twice (to Girona and Real Madrid, the leaders this season). Losing twice means dropping 3 points to the leaders and 3 points to a direct competitor.</p><p>To reach the same 48 points as last season, Barcelona can afford to lose just one more game.</p><p>Results of Barcelona away last season:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/713/1*-TvQULYRDrKJ0uVsmZHgvg.png" /></figure><p>Unlike our home performance where we gathered 40 points in 19 games, our away record was less impressive, with 5 losses and one draw, resulting in a loss of 17 points.</p><p>This season, in 8 away games:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/713/1*bf5SS9QHmPIqoNAiDfuZaQ.png" /></figure><p>This season, in 8 away games, we have drawn 5 times, resulting in a loss of 10 points. However, we still have 10 games to play away this season.</p><p>To reach the same amount of points as last season away, we can afford to lose just 7 points (allowing for one loss and two draws) in the remaining 10 games. We are set to face Real Madrid, Atlético Madrid, Girona, Sevilla, and Athletic Bilbao away; that is a tough mission.</p><h3>Lewandowski’s Goal Scoring Analysis: A Tale of Declining Efficiency:</h3><p>This chart presents the goals per 90 minutes for Lewandowski this season and last season.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/1*0yv776c4M-6J8uvbivLEog.png" /></figure><p>It was already disappointing last year for a striker like Robert, who has scored more than 600 goals in his career, but this year, it’s catastrophic.</p><p>Let’s examine the xG (Expected Goals):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/1*8IcuncVtSjpKIP2_uDFyLw.png" /></figure><p>it’s approximately the same, but he isn’t scoring as he did last year.</p><p>Now, let’s look at npxG (Non-Penalty Expected Goals):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/1*9pFNkRHYcnz9NV--x_wGRA.png" /></figure><p>It’s less than last year, but it’s higher than the goals scored by Robert per 90 minutes currently. It’s a significant efficiency problem.</p><h3>Vitor Roque Arrival maybe he can help solve this efficiency problem:</h3><p>Vitor Roque is an 18-year-old Brazilian striker who recently joined Barcelona from Atletico Paranaense. I came across some data for him on the website <a href="https://fbref.com/fr/joueurs/cb00f1e6/Vitor-Roque">https://fbref.com/fr/joueurs/cb00f1e6/Vitor-Roque</a>. The data is focused on the games he played in the Brazilian league and Copa Libertadores. Let’s proceed with web scraping and data visualization.</p><p>First, we obtain our data using web scraping with the libraries BeautifulSoup and requests. Now, let’s compare the Goals per 90 minutes for both Robert Lewandowski and Vitor Roque:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/1*ZI9pAH2og8Iv38dFPrmvtg.png" /></figure><p>There’s not a significant difference, and it’s crucial to note that Roque played exclusively in the Brazilian league and Copa Libertadores. The data gathered for Vitor Roque is solely based on these two competitions. However, let’s delve into the xG per 90 Minutes to better understand their efficiency.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/1*ZhqZOwg5UYs__Xrf91lQdQ.png" /></figure><p>Now, there’s a noticeable difference, and it’s substantial. Vitor Roque has approximately half of the expected goals but roughly the same goals per game. Let’s examine the ratio:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/1*CrfzdJ4U_dRIGjzyd74GwA.png" /></figure><p>The difference is now significant, and Vitor Roque is significantly more efficient this year compared to Robert Lewandowski. It’s worth noting that Robert Lewandowski’s statistics include penalties.</p><p>Let’s explore the ratio of non-penalty goals and non-penalty expected goals since Vitor Roque doesn’t take penalties.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/1*-_-UOdvM1G1daOS8NZtb6Q.png" /></figure><p>Remarkably, Vitor Roque, exhibits more than double the efficiency of Lewandowski this season</p><blockquote><strong><em>Note:</em></strong></blockquote><p>Vitor Roque’s arrival at Barcelona signals a notable shift in attacking efficiency compared to Robert Lewandowski. With a substantial increase in non-penalty goals and expected goals per 90 minutes, Roque showcases remarkable effectiveness. His ability to deliver such performances without taking penalties is a distinctive trait, setting him apart from Lewandowski. While the analysis is based on Roque’s performances in the Brazilian league and Copa Libertadores, the impact of his arrival on Barcelona’s season appears promising. The team can potentially benefit from Roque’s heightened efficiency, adding a new dimension to their attacking strategy and diversifying goal-scoring options. The adaptation of this young talent to a higher level of competition could bode well for Barcelona’s aspirations in La Liga.</p><h3>5. Conclusion:</h3><p>In the intricate world of football, FC Barcelona’s recent journey has been a rollercoaster of emotions for fans. Despite facing setbacks, the arrival of Xavi Hernandez as coach injected hope, steering the team to remarkable performances. As we delve into the data, it’s evident that the current season presents both challenges and opportunities.</p><p>The defensive solidity witnessed in the previous season has wavered, leading to a higher number of conceded goals. On the offensive side, while creating more chances, the team’s efficiency has dipped. The analysis of player performances, including the decline in Lewandowski’s efficiency, sets the stage for an intriguing exploration.</p><p>The signings of young talent like Vitor Roque bring a fresh perspective. Through web scraping and Python, we uncovered Roque’s efficiency, which significantly surpasses that of Lewandowski. His arrival adds a promising dimension to Barcelona’s attacking strategy.</p><p>You can find all the code, notebooks, and data in this <a href="https://github.com/abdeslam272/FC_Barcelona_WebScraping_Analysis_with_Python">GitHub repository</a>. Go check it out, and don’t forget: <strong>Visca Barça, Visca Catalunya, per Sempre!</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6a371ef9a0ba" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Azure Synapse Analytics: The all-in-one Azure service]]></title>
            <link>https://medium.com/@aelbennouri/azure-synapse-analytics-the-all-in-one-azure-service-1ce3b239b852?source=rss-8b6ccdb900c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/1ce3b239b852</guid>
            <category><![CDATA[azure-synapse-analytics]]></category>
            <category><![CDATA[azure]]></category>
            <dc:creator><![CDATA[Abdeslam EL BENNOURI]]></dc:creator>
            <pubDate>Wed, 22 Nov 2023 15:30:59 GMT</pubDate>
            <atom:updated>2023-12-19T10:10:20.271Z</atom:updated>
            <content:encoded><![CDATA[<h3>1. Introduction:</h3><p>On November 4, 2019, Microsoft Azure made a groundbreaking announcement, introducing a service known as Azure Synapse Analytics. At that time, it was described as a limitless analytics solution, blending the worlds of enterprise data warehousing and Big Data analytics. Azure Synapse is the next evolution of Azure SQL Warehouse.</p><p>To comprehend the true significance of Azure Synapse Analytics, it’s essential to grasp the common dilemma faced by many businesses and projects. Traditionally, they had to maintain two separate analytical systems: data warehouses and data lakes. Data warehouses were crucial for structured data analytics, enabling organizations to efficiently query and analyze structured data. On the other hand, Azure data lakes played a pivotal role in handling unstructured and semi-structured data, offering a flexible and scalable repository for diverse data types.</p><p>Both data warehouses and data lakes play critical roles in data analysis, but they’ve often operated in isolation, leading to complexities and fragmented insights. Azure Synapse Analytics emerged as the answer to this challenge, offering a unified platform that seamlessly integrates both structured and unstructured data analytics.</p><p>In this article, we’ll dive deep into the world of Azure Synapse Analytics, exploring its capabilities and understanding why it’s dubbed ‘the all-in-one Azure service.’ We’ll address the common dilemma faced by businesses and projects, and how Azure Synapse Analytics bridges the gap between these two analytical systems with a unified experience to ingest, prepare, manage, and serve data for immediate business intelligence and machine learning needs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/537/1*b_F61DNilEDW1O6iGaiVAg.png" /></figure><h3><strong>2. What is Azure Synapse Analytics?</strong></h3><p>Azure Synapse Analytics is an enterprise analytics service that expedites the time to gain insights across data warehouses and big data systems. It amalgamates the finest SQL technologies utilized in enterprise data warehousing, Apache Spark technologies for big data, and Azure Data Explorer for log and time series analytics. (Source: Azure documentation, see <a href="https://azure.microsoft.com/en-us/products/synapse-analytics/">here</a>)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/737/1*u1qvoVeQ_2BrLyelaZDbGg.png" /><figcaption>Architecture of Azure Synapse</figcaption></figure><p>The architecture of Azure Synapse is based on several key components that work together to enable data collection, storage, processing, analysis and visualization. Here are the main elements of the Azure Synapse architecture:</p><p><strong><em>Data Ingestion</em></strong>: Azure Synapse can collect and import data from various sources. This includes gathering data from on-premises systems, retrieving data stored in cloud environments, integrating data from Software as a Service (SaaS) solutions, and managing data in streaming formats.</p><p><strong><em>Data Storage</em></strong>: The data ingested into Azure Synapse is stored in Azure Data Lake Storage Gen2, which serves as the data storage solution. Data Lake Storage Gen2 functions as a cloud-based file system with high scalability and tight integration with other Azure services. It accommodates data of various structures, including structured, semi-structured, and unstructured data, allowing for the storage of data in various forms and sizes.</p><p><strong><em>Data Integration</em></strong>: Data from diverse sources is ingested and stored, and it can be collected and merged. Data integration supports connections to different sources, including traditional data warehouses, relational databases, cloud services, and file systems. This component also provides data transformation and cleaning capabilities using Extract, Transform, Load (ETL) tools and data pipelines.</p><p><strong><em>Analytics Processing</em></strong>: Once data is cleaned, collected, and stored in Azure Data Lake Storage Gen2, the Analytics Processing component enables running queries and transformations on the stored data. Spark offers high flexibility and processing power for large-scale data analysis. It supports multiple programming languages, including SQL, Python, and Scala, allowing users to perform advanced data operations.</p><p><strong><em>Data Exploration</em></strong>: To explore data, tools such as Azure Synapse Studio can be used. It offers a user-friendly graphical interface for creating queries, visualizations, and reports. Additionally, popular libraries like Pandas, Matplotlib, and Seaborn can be used to conduct analyses and generate custom visualizations.</p><p><strong><em>Machine Learning</em></strong>: Azure Synapse facilitates the integration of machine learning features into the data analysis process. Popular libraries such as TensorFlow, PyTorch, and scikit-learn can be used to design, train, and deploy machine learning models at scale. These models can be directly incorporated into data pipelines to automate machine learning tasks.</p><p><strong><em>Data Visualization</em></strong>: Azure Synapse provides integrated visualization tools like Power BI, which is integrated into Azure Synapse. There is a dedicated section in the Azure Synapse interface for creating pages with Power BI.</p><p><strong><em>Security and Governance</em></strong>: Azure Synapse offers advanced security features to protect data, including data encryption, identity and access management. Governance policies can be implemented to control data access, define permissions, and ensure compliance with regulations.</p><h3>3. The All-in-One Aspect:</h3><p>The idea of an all-in-one solution was derived from Azure documentation, particularly from the fact that it’s possible to build an entire project using only Azure Synapse Analytics. For example, in a project I published on Medium <a href="https://medium.com/@abdeslam1999elbennouri/an-end-to-end-azure-data-engineering-project-azure-data-factory-azure-databricks-azure-data-62a1c6bdddaf">here</a>, I utilized Azure Data Factory for data ingestion, Azure Data Lake Gen2 for storage, Azure Databricks for data transformation, Azure Synapse for modeling, and Power BI for visualization.</p><p>However, this project prompted a question: Is it not possible to handle several planning aspects using Azure Synapse alone? The answer is, indeed, yes!</p><p>For the ingestion part, you can achieve the same results by using the Integrate hub within the Azure Synapse workspace, eliminating the need for separate pipelines. As for storage, we can utilize Azure Data Lake Gen2, which is automatically created when you set up Azure Synapse. For data transformation, you can make use of Notebooks in the Develop hub. Of course, Azure Synapse remains an integral part of modeling, and for visualization, you can create Power BI reports directly from the Develop hub. I will show you later different Synapse Studio and its various components.</p><h3>4. Benefits of Azure Synapse Analytics:</h3><p>Azure Synapse is a limitless analytics service that brings together data integration, enterprise data warehousing, and Big Data analytics.</p><p>It gives you the freedom to query data on your terms, using either serverless or dedicated resources-at scale. Azure Synapse brings these worlds together with a unified experience to ingest, explore, prepare, manage, and serve data for immediate BI and machine learning needs.</p><p>Key service capabilities include:</p><ul><li>Unified analytics platform</li><li>Serverless and dedicated options</li><li>Enterprise data warehouse</li><li>Data lake exploration</li><li>Code-free hybrid data integration</li><li>Deeply integrated Apache Spark and SQL engines</li><li>Cloud-native HTAP</li><li>Choice of language (T-SQL, Python, Scala, SparkSQL, &amp; .NET)</li><li>Integrated AI and BI</li></ul><h3>5. Azure synapse Workspace:</h3><p>When we want to create a Synapse workspace, we need to choose our subscription and resource group. We provide workspace details such as the workspace name, region, and select our Azure Data Lake Storage Gen2, along with configuring the security options for the workspace.</p><p>Once the setup is complete, we can open our Synapse Studio, where we’ll see something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wGiJItv4TQWFQS_Gm5rsDg.png" /></figure><p>In the Synapse Workspace interface, you’ll find various hubs on the left-hand side, including:</p><ol><li>Home Hub</li><li>Data Hub</li><li>Develop Hub</li><li>Integrate Hub</li><li>Monitor Hub</li><li>Manage Hub</li></ol><blockquote><strong><em>Home Hub:</em></strong></blockquote><p>In the Home Hub, Within the “Ingest” option, you can seamlessly ingest data from a wide range of sources, categorized as follows:</p><p>From Azure:</p><ul><li>Azure Blob Storage</li><li>Azure Cosmos DB for MongoDB</li><li>Azure Cosmos DB for NoSQL</li><li>Azure Data Explorer (Kusto)</li><li>Azure Data Lake Storage Gen1</li><li>Azure Data Lake Storage Gen2</li><li>Azure Database for MariaDB</li><li>Azure Database for MySQL</li><li>Azure Database for PostgreSQL</li><li>Azure Databricks Delta Lake</li><li>Azure File Storage</li><li>Azure SQL Database</li><li>Azure SQL Database Managed Instance</li><li>Azure Synapse Analytics</li><li>Azure Synapse dedicated SQL pool</li><li>Azure Table Storage</li></ul><p>From Databases:</p><ul><li>Amazon RDS for Oracle</li><li>Amazon RDS for SQL Server</li><li>Amazon Redshift</li><li>Apache Impala</li><li>DB2</li><li>Drill</li><li>Google Ads</li><li>Google BigQuery</li><li>Greenplum</li><li>HBase</li><li>Hive</li><li>Informix</li><li>MariaDB</li><li>Microsoft Access</li><li>MySQL</li><li>Netezza</li><li>Oracle</li><li>Phoenix</li><li>PostgreSQL</li><li>Presto</li><li>SAP BW Open Hub</li><li>SAP BW via MDX</li><li>SAP HANA</li><li>SAP Table</li><li>SQL server</li><li>Spark</li><li>Sybase</li><li>Teradata</li><li>Vertica</li></ul><p>From Files:</p><ul><li>Amazon S3</li><li>Amazon S3 Compatible</li><li>FTP</li><li>File system</li><li>Google Cloud Storage</li><li>HDFS</li><li>Microsoft Fabric Lakehouse (Preview)</li><li>Oracle Cloud Storage (S3 API)</li><li>SFTP</li></ul><p>Using Generic Protocols:</p><ul><li>HTTP</li><li>ODBC</li><li>OData</li><li>REST</li></ul><p>From NoSQL Databases:</p><ul><li>Cassandra</li><li>Couchbase (Preview)</li><li>MongoDB</li><li>MongoDB Atlas</li></ul><p>And Integration with Services and Apps Such as:</p><ul><li>Amazon Marketplace Web Service</li><li>Concur (Preview)</li><li>Dataverse (Common Data Service for Apps)</li><li>Dynamics 365</li><li>Dynamics AX</li><li>Dynamics CRM</li><li>HubSpot</li><li>Jira</li><li>Magento (Preview)</li><li>Marketo (Preview)</li><li>Microsoft 365 (Office 365)</li><li>Oracle Eloqua (Preview)</li><li>Oracle Responsys (Preview)</li><li>Oracle Service Cloud (Preview)</li><li>PayPal (Preview)</li><li>QuickBooks (Preview)</li><li>SAP Cloud For Customer</li><li>SAP ECC</li><li>Salesforce</li><li>Salesforce Marketing Cloud</li><li>Salesforce Service Cloud</li><li>ServiceNow</li><li>SharePoint Online List</li><li>Shopify (Preview)</li><li>Snowflake</li><li>Square (Preview)</li><li>Web Table</li><li>Xero</li><li>Zoho (Preview)</li></ul><p>This comprehensive array of sources and protocols ensures that Azure Synapse Analytics is well-equipped for data ingestion from various origins, establishing itself as a versatile tool for data integration and analysis.</p><p>In the “Explore and Analysis” option, users are directed to the tutorial “Explore and Analyze data lakes with serverless SQL pool” for a detailed guide. The “Visualise” option allows the creation of Power BI reports directly within Azure Synapse.</p><blockquote><strong><em>Data Hub:</em></strong></blockquote><p>Within the ‘Data Hub,’ Azure Synapse serves as a central hub for viewing and interacting with data sources, facilitating cross-source querying. Users can create SQL databases on either Serveless SQL Pool or dedicated SQL Pool, as well as Lake databases. SQL Database in Synapse suits structured and transactional data, while Data Lake database is optimal for unstructured and analytical data. Synapse workspace can be linked to external data sources such as Azure Blob Storage, Azure Cosmos DB for MongoDB, Azure Cosmos DB for NoSQL, Azure Data Explorer (Kusto), and Azure Data Lake Storage Gen2. Once connected, the underlying data becomes available for analysis in the Data Hub or for pipeline activities in the Integrate Hub. Additionally, users can create Integration datasets from a diverse range of sources, as showcased in the ingest part.</p><p>and in the browse gallery we can see find databses templates, Datasets, Notebooks, Pipelines</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/238/1*h9FKDR3onMODUl28o4zvkA.png" /></figure><blockquote><strong><em>Develop hub:</em></strong></blockquote><p>In Azure Synapse Studio, the Develop hub is where you manage SQL scripts, Synapse notebooks, data flows, and Power BI reports.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/0*MMANEMDFPs8XQMpW.png" /></figure><p>The Develop hub in our sample environment contains examples of the following artifacts:</p><ul><li><strong>SQL scripts</strong> contains T-SQL scripts that you publish to your workspace. Within the scripts, you can execute commands against any of the provisioned SQL pools or on-demand SQL serverless pools to which you have access.</li><li><strong>Notebooks</strong> contains Synapse Spark notebooks used for data engineering and data science tasks. When you execute a notebook, you select a Spark pool as its compute target.</li><li><strong>Data flows</strong> are powerful data transformation workflows that use the power of Apache Spark but are authored using a code-free GUI.</li><li><strong>Power BI</strong> reports can be embedded here, giving you access to the advanced visualizations they provide without ever leaving the Synapse workspace.</li></ul><p>For more detailed information on the Develop hub, I recommend referring to this article <a href="https://davidalzamendi.com/azure-synapse-analytics-develop-hub/">here</a>.</p><blockquote><strong><em>Integrate Hub :</em></strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/224/1*VEqfLoMiZaJ7hO0jtmW3Pw.png" /></figure><p>The Integrate Hub centers around data integration and orchestration tasks, providing a range of capabilities. This includes the creation of data pipelines, akin to the functionality available in the pipelines section of Azure Data Factory. Linked services facilitate analytical processing on operational data. The Copy Data tool mirrors the data ingestion option in the Home Hub, allowing for seamless data movement. The Browse gallery feature enables users to select and work with existing pipelines from a gallery, while the Import option facilitates the importation of pipelines from external devices. Within the Integrate Hub, users can effectively create and manage data pipelines, orchestrating intricate data movement and transformation tasks.</p><blockquote><strong><em>Monitor Hub:</em></strong></blockquote><p>Azure Synapse Analytics Studio Monitor Hub allows you to monitor your workspace pipeline orchestration, activities and SQL or Apache Spark resource usage. like in Integration we can have the same ORCHESTRATION AS IN AZURE DATA FACTORY including : Pipeline runs, Trigger runs, Integration runtimes and Link connections. and also ACTIVITIES such as Apache Spark applications ,SQL requests, KQL requests and data flow debug. and also, the analytics pools. we can monitor SQL Pools ,Apache Spark Pools and Data Explorer pools (preview).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/438/1*rZx8yERIJRq5dJxaJTs_-w.png" /></figure><blockquote><strong>Synapse Serverless SQL Pool vs Dedicated SQL Pool:</strong></blockquote><p>In this section, I’ll discuss two fundamental concepts in Synapse that you’ll frequently encounter: Synapse Serverless SQL Pool and Dedicated SQL Pool. I’ll provide their definitions, highlight why one is chosen over the other, and introduce the differences. For a comprehensive exploration of this topic, I recommend referring to the insightful article available at <a href="https://medium.com/@rafa94el/synapse-sql-serverless-pool-vs-dedicated-sql-pools-7fb1be7d7642">this link</a>.</p><p><strong>The Serverless SQL Pool</strong> serves as a query service within your data lake, enabling users to seamlessly access data. It proves valuable in Synapse for querying files from Azure Data Lake Storage (ADLS) or Cosmos DB using the familiar T-SQL syntax.</p><p>On the other hand, <strong>the Dedicated SQL Pool</strong> is a robust big data solution that offers data storage in a relational table format with columnar storage. This architecture enhances query performance and effectively manages storage costs.</p><p>The choice between the two depends on usage scenarios. For Serverless, the cost is contingent on the data processed, priced at $5 for every 1TB. In contrast, Dedicated SQL Pool costs are measured in data warehousing units. Optimal use cases hinge on the nature of the data at hand. If the system is predictable, with static and known data, Dedicated SQL is preferable due to its predictable costs. However, in situations where the system is dynamic and data varies significantly, Serverless emerges as the preferred choice.</p><blockquote><strong><em>Manage hub:</em></strong></blockquote><p>The Manage Hub encompasses administrative functionalities for Synapse Analytics resources. Users can manage access control, configure security settings, and monitor costs associated with the usage of Azure Synapse Analytics. This hub enables users to effectively govern their resources and optimize the management of their analytics environment.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/422/1*Hz0FgXUeFhzhdKCKzOog7A.png" /></figure><h3>6. Comparison Between Azure Synapse Services and Others in Azure:</h3><blockquote><strong>Spark in Synapse Notebook vs Spark in Azure Databricks:</strong></blockquote><p>Azure Databricks and Synapse Notebooks leverage Apache Spark, albeit with distinct approaches. <strong>Synapse employs an open-source Spark version with integrated support for .NET. In contrast, Databricks utilizes an optimized Spark version, offering enhanced performance.</strong> This optimization enables users to choose GPU-enabled clusters, significantly accelerating data processing and concurrency.</p><p>When comparing these services, it’s essential to examine specific feature availability. Notably, Databricks allows the use of multiple notebooks within the same session, a functionality currently absent in Synapse. Another significant divergence lies in how these platforms handle data storage. Databricks provides a static mount path for storage accounts, facilitating intuitive navigation akin to a traditional filesystem. Conversely, Synapse necessitates the provision of a ‘job id’ when reading data from a mount, an identifier subject to change with each new job run.</p><p>For a more in-depth exploration of these differences, I recommend referring to the article available <a href="https://etlia.fi/synapse-vs-databricks-a-comparison/#:~:text=Synapse%20has%20an%20open%2Dsource,data%20processing%20and%20higher%20concurrency">here</a>.</p><blockquote><strong>Azure Synapse Analytics vs Azure Data Factory:</strong></blockquote><p>Azure Synapse Analytics can help you turn big, unstructured data into actionable insights, while Data Factory ensures numerous integrations without the use of code. The main difference between the two services is that Synapse Analytics is an analytics service, and Data Factory is a hybrid data integration service that simplifies the ETL at scale. Data Factory offers the integration of different data sources, but Synapse Analytics serves as a platform from which you can manage, prepare and serve data for BI and Machine Learning purposes with reporting capabilities.</p><p>Azure Data Factory offers features such as:</p><ul><li>real-time integration</li><li>parallel processing</li><li>data chunker</li></ul><p>On the other hand, Azure Synapse provides:</p><ul><li>Complete T-SQL-based analytics</li><li>deeply integrated Apache Spark</li><li>hybrid data integration</li></ul><p>Synapse Analytics offers all the possibilities of Data Factory such as the integration of different data sources, but with added functionalities of monitoring, management, alerting, and security in one place.</p><p><strong>If you only want to connect and transform data without writing code, you should embrace Azure Data Factory. However, it doesn’t allow you to customize your data pipelines beyond its no-code capabilities. In case you are looking for more flexibility while working with data, you must embrace Azure Synapse Analytics.</strong></p><p>to read more see this article <a href="https://hevodata.com/learn/azure-synapse-vs-data-factory/">here</a>.</p><h3><strong>7. Best Practices:</strong></h3><p>In this paragraph, we will explore the best practices when using Synapse:</p><p><strong><em>Partition and Index Tables:</em></strong></p><ul><li>Organize your tables with appropriate partitioning based on usage patterns.</li><li>Utilize indexes strategically to speed up data retrieval.</li></ul><p><strong><em>Distribution Strategies:</em></strong></p><ul><li>Choose appropriate distribution methods for tables to minimize data movement during query execution.</li><li>Use hash distribution for large fact tables and round-robin distribution for small dimension tables.</li></ul><p><strong><em>Data Compression:</em></strong></p><ul><li>Implement data compression to reduce storage costs and enhance query performance.</li><li>Consider columnstore indexes for analytical workloads.</li></ul><p><strong><em>Query Optimization:</em></strong></p><ul><li>Design queries to be selective and avoid unnecessary data scans.</li><li>Leverage materialized views for frequently used aggregations or joins.</li></ul><p><strong><em>Use Proper Data Types:</em></strong></p><ul><li>Choose the most suitable data types to minimize storage requirements.</li><li>Avoid using MAX data types unless necessary.</li></ul><p><strong><em>Resource Scaling:</em></strong></p><ul><li>Adjust resources (DWUs — Data Warehouse Units) based on workload requirements.</li><li>Use auto-pause and auto-resume features during periods of inactivity to save costs.</li></ul><p><strong><em>Monitoring and Performance Tuning:</em></strong></p><ul><li>Regularly monitor query performance using tools like Azure Monitor.</li><li>Utilize query execution plans to identify and resolve performance bottlenecks.</li></ul><p><strong><em>Data Movement Best Practices:</em></strong></p><ul><li>Minimize data movement between distributions during joins and aggregations.</li><li>Optimize data loading using COPY command for bulk inserts.</li></ul><p><strong><em>Security Best Practices:</em></strong></p><ul><li>Implement the principle of least privilege for access to Synapse Analytics resources.</li><li>Enable auditing and logging for security and compliance.</li></ul><p><strong><em>Backup and Restore:</em></strong></p><ul><li>Regularly backup your data warehouse to ensure data durability.</li><li>Practice restoring from backups to validate the recovery process.</li></ul><p><strong><em>Documentation and Training:</em></strong></p><ul><li>Maintain thorough documentation for data models, schemas, and ETL processes.</li><li>Provide training for users and administrators on best practices and optimization techniques.</li></ul><p><strong><em>Regular Updates and Patching:</em></strong></p><ul><li>Keep your Synapse Analytics instance up-to-date by applying regular updates and patches.</li><li>Monitor the Azure Synapse Analytics release notes for new features and improvements.</li></ul><p>By following these best practices, you can enhance the performance, reliability, and cost-effectiveness of your solutions implemented with Azure Synapse Analytics.</p><h3>8. conclusion:</h3><p>In summary, Azure Synapse Analytics is a game-changer, seamlessly merging traditional data warehousing and advanced big data analytics. Its unified platform addresses the challenges of managing structured and unstructured data, offering businesses a streamlined solution for data integration, preparation, and analysis. With a comprehensive architecture and integration with various Azure components, Azure Synapse Analytics emerges as an all-in-one service, simplifying complex data tasks. It stands as a key player in the evolving landscape of data analytics, providing organizations with an efficient and unified solution within the Azure ecosystem.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1ce3b239b852" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[An end to end Azure data Engineering Project : Azure Data Factory, Azure Databricks, Azure Data…]]></title>
            <link>https://medium.com/@aelbennouri/an-end-to-end-azure-data-engineering-project-azure-data-factory-azure-databricks-azure-data-62a1c6bdddaf?source=rss-8b6ccdb900c8------2</link>
            <guid isPermaLink="false">https://medium.com/p/62a1c6bdddaf</guid>
            <category><![CDATA[azure-data-factory]]></category>
            <category><![CDATA[azure-databricks]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[azure-synapse-analytics]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Abdeslam EL BENNOURI]]></dc:creator>
            <pubDate>Tue, 31 Oct 2023 16:48:21 GMT</pubDate>
            <atom:updated>2023-11-22T15:34:40.886Z</atom:updated>
            <content:encoded><![CDATA[<h3>An end to end Azure data Engineering Project : Azure Data Factory, Azure Databricks, Azure Data Lake Gen2, Azure Synapse Analytics, Power BI.</h3><h3>1- Introduction.</h3><p>In this project, we will demonstrate an end-to-end data engineering project based on data from the COVID-19 epidemic. We will use Azure Data Factory for data ingestion, Azure Data Lake Gen2 for storage, Azure Databricks for data transformation, Azure Synapse for modeling, and Power BI for visualization.</p><h3>2- Project Planning and Definition.</h3><p>In the following project we will use the following schema with the following order :</p><ol><li>Data Ingestion (Azure Data Factory):</li></ol><ul><li>Azure Data Factory serves as our data ingestion platform. It enables us to collect COVID-19 data from various sources, including government databases, APIs, and web scraping. Data Factory’s data connectors and scheduling capabilities are invaluable for automated ingestion.</li></ul><p>2. Data Storage (Azure Data Lake Gen2):</p><ul><li>Processed COVID-19 data finds its home in Azure Data Lake Gen2. This storage solution provides scalable, secure, and cost-effective storage, which is critical for accommodating the increasing volume of epidemic data.</li></ul><p>3. Data Transformation (Azure Databricks):</p><ul><li>We utilize Azure Databricks for data transformation and processing tasks. Databricks clusters allow us to perform data cleansing, normalization, and feature engineering, preparing the COVID-19 data for analysis.</li></ul><p>4. Data Modeling (Azure Synapse):</p><ul><li>Azure Synapse serves as our data modeling and analytics platform. We employ it to build data models, perform SQL-based queries, and conduct complex analytical tasks on the COVID-19 dataset.</li></ul><p>5. Data Visualization (Power BI):</p><ul><li>Power BI is the tool of choice for data visualization. We create interactive dashboards and reports to present the COVID-19 data insights, enabling stakeholders to make informed decisions.</li></ul><p>By orchestrating data flow, transformation, storage, modeling, and visualization using Azure services, we aim to provide actionable insights from this critical dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L8R5-wGQpi3_TM31YAgAGA.png" /><figcaption>Architectural overview</figcaption></figure><p>This architectural overview encapsulates our approach in this data engineering project, emphasizing the role of Azure services in processing and analyzing COVID-19 data. The architecture ensures that the data pipeline is efficient, secure, and capable of handling the evolving requirements of epidemic data analysis.</p><p>For this project, we initiated by establishing our resource group, within which we proceeded to create the essential resources.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DkmnsRKp97yG6d9zAd62NA.png" /><figcaption>Resources</figcaption></figure><h3>3- Data Collection and Ingestion.</h3><p>We will extract the data from GitHub, which contains information about victims and cases infected by the COVID epidemic, as well as other related data.</p><p>the data will be look :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/996/1*P537kYtVVNLg6YFDr7Zsvw.png" /><figcaption>Raw Data</figcaption></figure><p>In this phase, we harnessed the power of Azure Data Factory to seamlessly ingest data from the “GitHub” source into our Azure environment.</p><p>In the next picture we will see the pipeline where I made the ingestion :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HNU4g4f8HNy8BBCFcI9xvA.png" /><figcaption>the pipeline of copy all data</figcaption></figure><p>We commence by establishing our source dataset, configured as an HTTP source. This step encompasses the setup of the Linked service and the definition of the BASE URL for all our files. In the case of the sink datasets, we select Delimited Text (CSV) format, and for the Linked service, we designate the destination container as “raw-data” within our Storage account. Since the data remains unaltered and requires no transformations, the primary task revolves around copying the files. We apply minor modifications to the file names to improve their clarity and comprehensibility.</p><h3>4- Data Storage.</h3><p>In this phase, we ensure that our data is meticulously organized within our Azure Data Lake Storage. We have designated a specific container, which we refer to as “bronze-data,” for this purpose. This staging area acts as the initial repository for our raw data files, providing a secure and organized location for the data to reside.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mbr-LJtIpaB4QOtoF1zMZw.png" /><figcaption>Raw-data in ADLS-Gen2</figcaption></figure><h3>5- Data Transformation.</h3><p>In the data transformation phase, we leverage Azure Databricks with a notebook environment to write and execute our Spark code. To initiate this process, we’ve provisioned Azure Databricks by creating the required resources and compute instances. However, before delving into coding, it is crucial to establish a secure connection between Azure Databricks and the Azure Storage accounts that house our raw data.</p><p>To achieve this, we have developed an application using the ‘App registrations’ resource provided by Azure. Within this application, we’ve generated an ‘Application (client) ID’ and a ‘Directory (tenant) ID.’ For clarity, this application is named ‘App01.’</p><p>Subsequently, within the ‘Certificates &amp; secrets’ section of application management, we have generated a client secret key (as illustrated in the figure below). This secret key plays a pivotal role in maintaining a secure and robust connection between Azure Databricks and the Azure Storage accounts, enabling seamless data transformation and processing.</p><p>Finally, we must configure role assignments for ‘Storage Blob Data Contributor.’ This assignment allows Azure Databricks to read, write, and delete access to Azure Storage blob containers and data, facilitating efficient data management and processing.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xUjlgfdptoG3qO_bEouGwA.png" /><figcaption>the secret Key</figcaption></figure><p>So now, we have commenced writing our Spark code in the notebook, beginning with the establishment of the connection between the container in the storage accounts and our notebook.</p><pre>configs = {&quot;fs.azure.account.auth.type&quot;: &quot;OAuth&quot;,<br>&quot;fs.azure.account.oauth.provider.type&quot;: &quot;org.apache.hadoop.fs.azurebfs.oauth2.ClientCredsTokenProvider&quot;,<br>&quot;fs.azure.account.oauth2.client.id&quot;: &quot;client ID&quot;,<br>&quot;fs.azure.account.oauth2.client.secret&quot;: &#39;Secret Key&#39;,<br>&quot;fs.azure.account.oauth2.client.endpoint&quot;: &quot;https://login.microsoftonline.com/Directory (tenant) ID/oauth2/token&quot;}<br><br><br>#raw data = bronze data<br>dbutils.fs.mount(<br>source = &quot;abfss://bronze-data@&lt;Storage Account name&gt;.dfs.core.windows.net&quot;, # contrainer@storageacc<br>mount_point = &quot;/mnt/bronze-data&quot;,<br>extra_configs = configs)<br><br><br>#transformed data<br>dbutils.fs.mount(<br>source = &quot;abfss://transformed-data@&lt;Storage Account name&gt;.dfs.core.windows.net&quot;, # contrainer@storageacc<br>mount_point = &quot;/mnt/transformed-data&quot;,<br>extra_configs = configs)</pre><p>After this, we can list the files and directories in the ‘bronze data’ container by using the following command:</p><pre>%fs<br>ls &quot;/mnt/raw-data&quot;</pre><p>After that, we should configure our Spark session using the command: Spark. Now, we can begin our actual data work by first reading the data:</p><pre>#read the data<br>admissions_hospital = spark.read.format(&quot;csv&quot;).option(&quot;header&quot;,&quot;true&quot;).load(&quot;/mnt/covid-data/admissions_hospital&quot;)<br>death_uk_ind_only = spark.read.format(&quot;csv&quot;).option(&quot;header&quot;,&quot;true&quot;).load(&quot;/mnt/covid-data/death_uk_ind_only&quot;)<br>death_world = spark.read.format(&quot;csv&quot;).option(&quot;header&quot;,&quot;true&quot;).load(&quot;/mnt/covid-data/death_world&quot;)<br>response_country = spark.read.format(&quot;csv&quot;).option(&quot;header&quot;,&quot;true&quot;).load(&quot;/mnt/covid-data/response_country&quot;)<br>test = spark.read.format(&quot;csv&quot;).option(&quot;header&quot;,&quot;true&quot;).load(&quot;/mnt/covid-data/test&quot;)</pre><p>We can utilize various PySpark SQL DataFrame methods, such as .show() and .printSchema(), to view and gain a better understanding of the data.</p><p>After reviewing the data and considering our requirements, we have decided to extract two dimension tables from our dataset. The first one, named ‘dim_country,’ will contain information about countries, including details such as country code, continent, and population. this table will be extracted from our ‘death_world’ file.</p><pre>#let&#39;s process and transform the death world data<br>death_world.createOrReplaceTempView(&quot;raw_death_world&quot;) #to query the files<br>dim_country = spark.sql(&quot;&quot;&quot;select distinct country<br>                                          , country_code<br>                                          , continent<br>                                          , population <br>                                  from raw_death_world<br>                                  &quot;&quot;&quot;)</pre><p>The data in the ‘dim_country’ table will be structured as follows:</p><pre>dim_country.show(6)<br><br>+--------------+------------+---------+----------+<br>|       country|country_code|continent|population|<br>+--------------+------------+---------+----------+<br>|       Algeria|         DZA|   Africa|  43851043|<br>|       Andorra|         AND|   Europe|     76177|<br>|        Angola|         AGO|   Africa|  32866268|<br>|Africa (total)|        NULL|   Africa|1339423921|<br>|       Albania|         ALB|   Europe|   2862427|<br>|   Afghanistan|         AFG|     Asia|  38928341|<br>+--------------+------------+---------+----------+<br>only showing top 6 rows</pre><p>The second dimension table that we will use is ‘dim_date,’ which contains information structured as follows: date_key, date, year, month, day, day_name, day_of_year, week_of_month, week_of_year, month_name, year_month, and year_week.</p><p>This table will include data for the date range from January 1, 2020, to December 30, 2022. The table will contain 1,095 records.</p><p>the table will look like that:</p><pre>dim_date.show(5)<br><br>+--------+----------+----+-----+---+---------+-----------+-------------+------------+----------+----------+---------+<br>|date_key|      date|year|month|day| day_name|day_of_year|week_of_month|week_of_year|month_name|year_month|year_week|<br>+--------+----------+----+-----+---+---------+-----------+-------------+------------+----------+----------+---------+<br>|20200101|2020-01-01|2020|    1|  1|Wednesday|          1|            1|           1|   January|    202001|   202001|<br>|20200102|2020-01-02|2020|    1|  2| Thursday|          2|            1|           1|   January|    202001|   202001|<br>|20200103|2020-01-03|2020|    1|  3|   Friday|          3|            1|           1|   January|    202001|   202001|<br>|20200104|2020-01-04|2020|    1|  4| Saturday|          4|            1|           1|   January|    202001|   202001|<br>|20200105|2020-01-05|2020|    1|  5|   Sunday|          5|            2|           2|   January|    202001|   202002|<br>+--------+----------+----+-----+---+---------+-----------+-------------+------------+----------+----------+---------+<br>only showing top 5 rows</pre><p>For the fact tables, we’re going to extract three fact tables: fact_cases_death, fact_response_country, and fact_admissions_hospital.</p><p>fact_cases_death will contain measures related to countries, date, indicator, rate_14_day, daily_count, and source. Regarding the date column, we need to change the format to facilitate our analytics on Azure Synapse, making it more convenient.</p><pre>#let&#39;s go now for the fact table of cases death<br>death_world.createOrReplaceTempView(&quot;death_world&quot;) #pour que on puisse faire des requetes<br>fact_cases_death = spark.sql(&quot;&quot;&quot;select country, date_format(date,&#39;yyyyMMdd&#39; ) as date_key,<br>                                indicator, rate_14_day, daily_count, source <br>                                from death_world<br>                             &quot;&quot;&quot;)</pre><p>So, in it, the data looks like this :</p><pre>fact_cases_death.show(6)<br>+-----------+--------+---------------+-----------+-----------+--------------------+<br>|    country|date_key|      indicator|rate_14_day|daily_count|              source|<br>+-----------+--------+---------------+-----------+-----------+--------------------+<br>|Afghanistan|20200102|confirmed cases|       NULL|          0|Epidemic intellig...|<br>|Afghanistan|20200103|confirmed cases|       NULL|          0|Epidemic intellig...|<br>|Afghanistan|20200104|confirmed cases|       NULL|          0|Epidemic intellig...|<br>|Afghanistan|20200105|confirmed cases|       NULL|          0|Epidemic intellig...|<br>|Afghanistan|20200106|confirmed cases|       NULL|          0|Epidemic intellig...|<br>|Afghanistan|20200107|confirmed cases|       NULL|          0|Epidemic intellig...|<br>+-----------+--------+---------------+-----------+-----------+--------------------+<br>only showing top 6 rows</pre><p>fact_response_countrywill contain measures related to Country, Response_measure, change, date_start, and date_end. Regarding the date_end and date_start columns, we also need to change the format to facilitate our analytics on Azure Synapse, making it more convenient.</p><pre>response_country.createOrReplaceTempView(&quot;response_country&quot;)<br>fact_response_country = spark.sql(&quot;&quot;&quot;select Country<br>                                            , Response_measure<br>                                            , change<br>                                            , date_format(date_start,&#39;yyyyMMdd&#39; ) as date_start<br>                                            , date_format(date_end,&#39;yyyyMMdd&#39; ) as date_end<br>                                      from response_country<br>                                  &quot;&quot;&quot;)</pre><p>fact_admissions_hosptalwill contain measures related to Country, indicator, date, value, source, and URL.</p><pre>admissions_hospital.createOrReplaceTempView(&quot;admissions_hospital&quot;)<br>fact_admissions_hosptal = spark.sql(&quot;&quot;&quot;select country<br>                                              , indicator<br>                                              , date_format(date,&#39;yyyyMMdd&#39; ) AS date_key<br>                                              , value <br>                                              , source<br>                                              , url <br>                                        from admissions_hospital  <br>                                    &quot;&quot;&quot;)</pre><p>Now that we know the dimensions table and the fact table, we should write these tables into our container named ‘transformed data’ :</p><pre>#Write our files<br>dim_date.write.format(&quot;com.databricks.spark.csv&quot;).option(&quot;header&quot;,&quot;true&quot;).option(&quot;delimiter&quot;, &quot;,&quot;).mode(&quot;overwrite&quot;).save(&quot;/mnt/transformed-data/dim_date&quot;)<br>dim_country.write.format(&quot;com.databricks.spark.csv&quot;).option(&quot;header&quot;,&quot;true&quot;).option(&quot;delimiter&quot;, &quot;,&quot;).mode(&quot;overwrite&quot;).save(&quot;/mnt/transformed-data/dim_country&quot;)<br>fact_cases_death.write.format(&quot;com.databricks.spark.csv&quot;).option(&quot;header&quot;,&quot;true&quot;).option(&quot;delimiter&quot;, &quot;,&quot;).mode(&quot;overwrite&quot;).save(&quot;/mnt/transformed-data/fact_cases_death&quot;)<br>fact_response_country.write.format(&quot;com.databricks.spark.csv&quot;).option(&quot;header&quot;,&quot;true&quot;).option(&quot;delimiter&quot;, &quot;,&quot;).mode(&quot;overwrite&quot;).save(&quot;/mnt/transformed-data/fact_response_country&quot;)<br>fact_admissions_hosptal.write.format(&quot;com.databricks.spark.csv&quot;).option(&quot;header&quot;,&quot;true&quot;).option(&quot;delimiter&quot;, &quot;,&quot;).mode(&quot;overwrite&quot;).save(&quot;/mnt/transformed-data/fact_admissions_hosptal&quot;)</pre><p>So far, so good. Now we can locate our files in our sink container named ‘transformed-data’:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bgj-fs4d9bNuBNdMJ_arpA.png" /><figcaption>Transformed-data Container</figcaption></figure><h3>6- Data Modeling.</h3><p>Now that we have our data in the tables, we will proceed to load it into the Lake Database in Azure Synapse Analytics, enabling us to create our models.</p><p>First, we need to set up our Azure Synapse workspace. By creating our Synapse Studio, we also create another Storage Account: Azure Data Lake Storage Gen2 (ADLS Gen2).</p><p>To use Azure Synapse for working with this data, we should copy the files from the ‘Transformed-data’ container into our ADLS Gen2. For this purpose, we will utilize a pipeline containing a copy activity from our source with the linked service: AzureBlobStorage, to our destination with the linked service: Default Storage account for our Synapse workspace (ADLS Gen2).</p><p>Another tip: to copy all the files in the ‘transformed-data’ container, rather than one file at a time, we can utilize the ‘Wildcard File Path’ option with the input as ‘transformed-data/*’.</p><p>Now, in the data part of the Synapse workspace, we add a Lake Database named ‘CovidDB.’ Following this, we create external tables from the data lake. To do this, we specify the External table name (which will be the same as ‘dim_country,’ ‘dim_date,’ etc.), the Linked service (which will be ‘ADLS Gen2’), and the Input file or folder. This input will specify the path to the files.</p><p>In the phase of creating our tables in the Lake database, we have recently discovered that the files in the “fact_cases_death” folder have been duplicated four times due to their size, as demonstrated in the picture below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nZysTEqSnIr567KLyJvBQg.png" /><figcaption>folder fact_cases_death</figcaption></figure><p>We will now implement a different pipeline to consolidate the files within the “fact_cases_death” folder. This new pipeline will consist of a single activity: data copying. In this pipeline, we will use the wildcard path option directly targeting our “fact_cases_death” folder. Additionally, we will modify the sink settings by choosing the “merge files” copy behavior.</p><p>This will make it easier for us to add our tables, as illustrated in the picture below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/296/1*Te4ucKuJbQ_Zhg9ohh7sKQ.png" /><figcaption>the Database CovidDB</figcaption></figure><p>We will now establish relationships between the tables. A relationship is a connection that defines how the data in these tables should be associated or correlated.</p><p>We chose the “To table” option for the fact tables, as these tables serve as the parent tables for the dimension tables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/963/1*EqKd0uRNOZILpvweQxAtQg.png" /><figcaption>Data model</figcaption></figure><h3>7- Data Visualization.</h3><p>In this section, we will leverage data visualization to gain valuable insights from the dataset. The following measures will be visualized:</p><p><em>Total Daily Hospital Admissions per Country Over time:</em></p><ul><li>To comprehend the daily hospital admissions on a country level, we execute the following query. It calculates the sum of daily admissions, providing a pivotal metric to monitor the impact of events like COVID-19.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/462/1*TIpywSWU8IGKjSvv4uBZaQ.png" /><figcaption>the daily hospital admissions in Mars</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/376/1*s2rYLSGfSeyl8n-A7cZrZg.png" /><figcaption>total daily admissions by month</figcaption></figure><p>for See the changing in time, we will use The “Play Axis” in our presentation, allowing the creation of a time series animation to illustrate how variations between countries change over time.</p><pre>-- the query of Total Daily Hospital Admissions per Country Over time<br>SELECT<br>    fd.date,<br>    dc.country,<br>    SUM(fh.value) AS total_daily_admissions<br>FROM [CovidDB].[dbo].fact_admissions_hospital fh<br>JOIN [CovidDB].[dbo].dim_date fd ON fh.date_key = fd.date_key<br>JOIN [CovidDB].[dbo].dim_country dc ON fh.country = dc.country<br>GROUP BY fd.date, dc.country<br>ORDER BY fd.date, dc.country;</pre><p><em>Trends Over Time:</em></p><ul><li>Understanding trends over time is crucial for assessing the evolution of daily hospital admissions. We utilize a line chart, utilizing the result from the query above. The date is placed on the x-axis and the total daily admissions on the y-axis. Tracking trends over time helps in identifying significant fluctuations.</li></ul><p><em>Variations Between Countries:</em></p><ul><li>Comparing variations between countries offers critical insights into the disparities in daily hospital admissions. We opt for a Bar Chart visualization, which enables side-by-side comparisons of different countries.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*mx9o_5cTW3GFAOZmpoQ0cQ.png" /><figcaption>Comparaison in Mars</figcaption></figure><p>also here we will use The “Play Axis” feature.</p><p><em>Seasonal Patterns:</em></p><ul><li>Identifying seasonal patterns can provide essential context. To achieve this, we employ a query that calculates a seven-day moving average. This moving average is pivotal in recognizing recurring trends in hospital admissions. It can be instrumental in resource allocation and preparedness.</li></ul><p>These visualizations are instrumental in uncovering patterns, trends, and disparities in the data.</p><p>This screen is just a part of the visualization files for a specific moment, similar to the comparison — it’s only on Mars. It’s more like an animation.</p><p>Here you can find all files in this project: <a href="https://github.com/abdeslam272/Covid-project-Azure/blob/main/Covid%20Project%20Azure.pbix">https://github.com/abdeslam272/Covid-project-Azure/blob/main/Covid%20Project%20Azure.pbix</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=62a1c6bdddaf" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>