<?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[Litmus7 Systems Consulting - Medium]]></title>
        <description><![CDATA[We Are Artists, Leaders and Problem Solvers. The world’s leading retailers trust us to accelerate their digital growth. Our teams are experts in technology, product, marketing and strategy. They are supported by a wide eco-systems of partners and commerce start-ups. - Medium]]></description>
        <link>https://medium.com/litmus7?source=rss----d47509a7187---4</link>
        <image>
            <url>https://cdn-images-1.medium.com/proxy/1*TGH72Nnw24QL3iV9IOm4VA.png</url>
            <title>Litmus7 Systems Consulting - Medium</title>
            <link>https://medium.com/litmus7?source=rss----d47509a7187---4</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 02:37:40 GMT</lastBuildDate>
        <atom:link href="https://medium.com/feed/litmus7" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[File Transfer from Azure BLOB to AWS S3 : Step-by-Step Guide]]></title>
            <link>https://medium.com/litmus7/file-transfer-from-azure-blob-to-aws-s3-step-by-step-guide-9be4b033b8ea?source=rss----d47509a7187---4</link>
            <guid isPermaLink="false">https://medium.com/p/9be4b033b8ea</guid>
            <category><![CDATA[azure-blob]]></category>
            <category><![CDATA[aws-to-azure]]></category>
            <category><![CDATA[file-transfer]]></category>
            <category><![CDATA[aws-s3]]></category>
            <dc:creator><![CDATA[Sarath Chandran]]></dc:creator>
            <pubDate>Fri, 22 Sep 2023 11:29:58 GMT</pubDate>
            <atom:updated>2023-09-22T11:29:58.366Z</atom:updated>
            <content:encoded><![CDATA[<h3>File Transfer from Azure BLOB to AWS S3 : Step-by-Step Guide</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/756/1*xNsYQzhGTSBXG5vU_Lp2dg.jpeg" /></figure><p>Now-a-days, many organizations are following a Multi-cloud approach - a strategy which leverages cloud computing services from at least two different cloud providers. This gives organizations the flexibility to optimize performance, control cost and avoid vendor lock-in. In such Multi-cloud scenarios, we often reach a point where we need to transfer data from one cloud platform to another.</p><p>Here, we will be discussing about such an approach where we need to transfer data from Azure BLOB storage to AWS S3 bucket.</p><p>On a high level, below are the steps:</p><ul><li>Create a Python script to transfer file from BLOB to S3 (Script is given at the end of this post).</li><li>Create an Azure Batch account and configure the batch pool.</li><li>Create an ADF pipeline with a custom activity, and connect to Azure batch to run the data transfer script.</li></ul><p>Now, on a detailed level, below are the steps that could be followed:</p><ul><li>Create an Azure Batch account by providing necessary details.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/728/1*kIb7K3iPmaIX7p8NShzwxQ.png" /><figcaption>Fig 1: Create Azure Batch Account</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VGg2LgAsQ6cOETuH_0A6bA.png" /><figcaption>Fig 2: Create Azure Batch Account</figcaption></figure><ul><li>Go to the resource, and under Pools tab, create a new pool by providing required details as shown below:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/878/1*DU1KuL0A4MHWggkYDOSO3g.png" /><figcaption>Fig 3: Create new Pool</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/880/1*4EZAURuPUzuGYC2icdOQBA.png" /><figcaption>Fig 4: Create new Pool</figcaption></figure><ul><li>In the next step, make sure to provide at least 2 target dedicated nodes.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/888/1*IwQCtELMjAuRbnBx12qYuw.png" /><figcaption>Fig 5: Create new Pool</figcaption></figure><ul><li>Next, configure the start task. Follow the below link to configure the start task: <a href="https://techcommunity.microsoft.com/t5/azure-paas-blog/install-python-on-a-windows-node-using-a-start-task-with-azure/ba-p/2341854">https://techcommunity.microsoft.com/t5/azure-paas-blog/install-python-on-a-windows-node-using-a-start-task-with-azure/ba-p/2341854</a></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/890/1*TVmdXANO6ltVH1_2jLMlRw.png" /><figcaption>Fig 6: Start task configuration</figcaption></figure><ul><li>After this, submit the details.</li><li>Once the nodes are up and running, and status becomes <strong>steady</strong>, we are good to use the Azure batch.</li><li>From the keys section of Azure batch created, copy the primary access key.</li><li>Now, in ADF, create a <strong>Custom</strong> activity and provide the Batch account details. Key and end point details are available in the <strong>Key</strong> tab in Azure batch window.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6uYmGiP2u5s1_XEjSTLkLA.png" /><figcaption>Fig 7: ADF Custom activity</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*V0nsrlZTYt7L4ET_IVvYfA.png" /><figcaption>Fig 8: ADF Custom activity Keys</figcaption></figure><ul><li>Place the python code for transferring file to S3 inside a container in Azure ADLS or Blob, and specify this linked service and path in the custom activity settings tab. Also provide the command to trigger the Python script as shown below:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/656/1*M82VdasY8VfxDPtjigQmPg.png" /><figcaption>Fig 9: ADF Custom activity Settings</figcaption></figure><ul><li>Note that for the data copy script, additional python packages related to azure, aws and boto3 also will be required. In this case, modify the start task command as shown below:</li></ul><p><em>cmd /c “python-3.11.4-amd64.exe /quiet InstallAllUsers=1 PrependPath=1 Include_test=0 </em><strong><em>&amp;&amp;</em></strong><em> pip install azure-storage-blob </em><strong><em>&amp;&amp;</em></strong><em> pip install s3fs </em><strong><em>&amp;&amp;</em></strong><em> pip install pandas </em><strong><em>&amp;&amp;</em></strong><em> pip install boto3”</em></p><ul><li>Also, if the above start task modification is made to an existing pool in Azure batch, make sure that the compute nodes are restarted for the changes to take effect.</li><li>Job run status can be tracked from <strong>Jobs</strong> tab in Azure Batch.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/856/1*yPR6XDwSFgXvNFF-HvaxHQ.png" /><figcaption>Fig 10: Azure Batch Job status</figcaption></figure><ul><li>Error logs if any will be available in <strong>stderr</strong> file and standard output streams like print statement will be available in <strong>stdout</strong> file.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/1*OmSlb3XWCUAoT1Hyttl7vQ.png" /><figcaption>Fig 11: Azure Batch Job stdout</figcaption></figure><h3><strong>Python script</strong></h3><pre>from azure.storage.blob import BlobServiceClient, ContainerClient <br><br>import pandas as pd <br><br>from io import BytesIO <br><br>from io import StringIO <br><br>import boto3 <br><br> <br><br>print(&quot;Reading data from Azure Blob...&quot;) <br><br>src_storage_accnt = &quot;storage_accnt_name&quot; <br><br>src_container = &quot;container_name&quot; <br><br>src_file = &quot;source_file_name&quot; <br><br>connect_str = &quot;DefaultEndpointsProtocol=https;AccountName=&lt;account_name&gt;;AccountKey=&lt;*********&gt;==;EndpointSuffix=core.windows.net&quot; <br><br>blob_service_client = ContainerClient.from_connection_string(conn_str=connect_str, container_name=src_container) <br><br>try: <br><br>    blob_data = blob_service_client.download_blob(src_file) <br><br>    src_blob = blob_data.readall() <br><br>except Exception as e: <br><br>    print(&quot;Exception occurred while reading data from blob&quot;) <br><br>    raise Exception(&quot;Data read exception&quot;) <br><br> <br><br>print(&quot;Data read from Azure Blob successfully...&quot;) <br><br>print(&quot;Writing data to AWS S3...&quot;) <br><br>tgt_bucket = &quot;target_bucket_name&quot; <br><br>tgt_directory = &quot;target_directory_name&quot; <br><br>tgt_file = &quot;target_file_name&quot; <br><br>tgt_user = &quot;target_user_name&quot; <br><br>try: <br><br>    access_key = &quot;***********&quot; <br><br>    secret_key = &quot;*************************&quot; <br><br>    aws_object_key = tgt_directory + &quot;/&quot; + tgt_file <br><br>    s3 = boto3.client(&quot;s3&quot;, aws_access_key_id=access_key, aws_secret_access_key=secret_key) <br><br>    s3.put_object(Body=src_blob, Bucket=tgt_bucket, Key=aws_object_key) <br><br>except Exception as e: <br><br>    print(&quot;Exception occurred while writing data to bucket&quot;) <br><br>    raise Exception(&quot;Data write exception&quot;) <br><br>print(&quot;Data written to AWS S3 successfully...&quot;)</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9be4b033b8ea" width="1" height="1" alt=""><hr><p><a href="https://medium.com/litmus7/file-transfer-from-azure-blob-to-aws-s3-step-by-step-guide-9be4b033b8ea">File Transfer from Azure BLOB to AWS S3 : Step-by-Step Guide</a> was originally published in <a href="https://medium.com/litmus7">Litmus7 Systems Consulting</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Harnessing the Power of Modern ETL: Spotlight on Fivetran, Matillion, Gathr and Keboola]]></title>
            <link>https://medium.com/litmus7/harnessing-the-power-of-modern-etl-spotlight-on-fivetran-matillion-gathr-and-keboola-defb9a86c7?source=rss----d47509a7187---4</link>
            <guid isPermaLink="false">https://medium.com/p/defb9a86c7</guid>
            <category><![CDATA[fivetran]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[gathr]]></category>
            <dc:creator><![CDATA[Vaisakh D]]></dc:creator>
            <pubDate>Thu, 21 Sep 2023 16:45:01 GMT</pubDate>
            <atom:updated>2023-09-22T11:33:25.008Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wIPyoFLfWGqvmHDy4TZymQ.jpeg" /></figure><p>The world of ETL is witnessing major changes with many modern cloud based and transaction driven ETL tools available in the industry. These new age tools have quickly become the tool of choice across enterprises replacing the stalwarts of the industry like Informatica, Ab initio , Data Stage, Talend. The ETL landscape is vast and continually evolving, with new age tools designed to address different business needs. The choice of an ETL tool hinges upon the organization’s unique needs. Here we’ll discuss the widely accepted ETL tools such as Fivetran, Matillion, Gathr and Keboola.</p><p>There are several key factors to consider when selecting an ETL Tool such as data connectivity, data transformation, performance, usability, scalability, integration with other systems, vendor support, cost, compliance and security, real time processing capability and many more.</p><p>Let’s go through each ETL tool and find their features, strengths, and weaknesses.</p><p><strong>Fivetran</strong></p><p>Fivetran is a cloud based fully managed data integration service. Once the setup is done it automatically extracts, loads, and refreshes data from the source into the target warehouses.</p><p>FiveTran users can leverage connectors available for more than 100 data sources . Also, Fivetran is well known for data extraction and loading rather than complex transformations. And the main advantage for Fivetran are ease of use and simplicity. Fivetran in conjunction with dbt ( Database Build tool) is fast becoming a hit pair seeing adoption in small and medium business that want to minimize administration and management of complex data platforms.</p><p><strong>Matillion</strong></p><p>Matillion is designed for cloud-based data warehouses. Especially for the widely used data warehouses such as Redshift, Snowflake, matilion is a good tool for optimizing the data transformation process in cloud.</p><p>It is a drag and drop interface in which users can design data transformation jobs with minimal coding. Also, the key feature of matillion is its ability to fetch data from any web accessible source.</p><p><strong>Gathr</strong></p><p>As compared to other ETL tools gather is new tool in the list. Gathr provides advanced features in the modern ETL Era. Gathr supports Data integration, Machine Learning, Analytics, Devops, Process automation etc. Nowadays Gathr is widely used in largest enterprises in the industry, and it is well known as “data to outcome platform”. Gathr helps businesses to turn data into actionable insights and gives outcomes faster. Gathr has many advantages as we consider it as an ETL tool such as Faster time to value, increased agility, reduced cost etc.</p><p><strong>Keboola</strong></p><p>Keboola is also designed to process beyond ETL, including analytics and machine learning. Keboola provides tools for data streams to collaborate effectively. Keboola allows users to do data transformations using SQL or other scripting languages with in Keboola.</p><p>While Keboola primarily focuses on data operations, it has integrations with various BI and analytics tools, making it easier to visualize and derive insights from the processed data.</p><p>Let’s compare each tool based on certain features which are applicable for the current big data ETL processing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/696/1*b_mNTYvE7yQEchubTyolcg.png" /><figcaption>ETL comparison</figcaption></figure><p>We have discussed the main ETL tools which are widely used in big data cloud environments. Selecting the right tool depends on the Organization’s specific requirements. Even though each tool offers reliable data integration and transformation capabilities differences in their features, scalability, pricing and eco system integrations makes each one unique.</p><p><strong>References</strong></p><p><a href="https://help.keboola.com/">https://help.keboola.com/</a></p><p><a href="https://fivetran.com/docs/getting-started">https://fivetran.com/docs/getting-started</a></p><p><a href="https://docs.matillion.com/">https://docs.matillion.com/</a></p><p><a href="https://www.gathr.one/blog/">https://www.gathr.one/blog/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=defb9a86c7" width="1" height="1" alt=""><hr><p><a href="https://medium.com/litmus7/harnessing-the-power-of-modern-etl-spotlight-on-fivetran-matillion-gathr-and-keboola-defb9a86c7">Harnessing the Power of Modern ETL: Spotlight on Fivetran, Matillion, Gathr and Keboola</a> was originally published in <a href="https://medium.com/litmus7">Litmus7 Systems Consulting</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[“Breaking Barriers in Data Privacy: How Federated Learning Empowers Secure Machine Learning”]]></title>
            <link>https://medium.com/litmus7/breaking-barriers-in-data-privacy-how-federated-learning-empowers-secure-machine-learning-1e8f49a3906b?source=rss----d47509a7187---4</link>
            <guid isPermaLink="false">https://medium.com/p/1e8f49a3906b</guid>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[retail]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[federated-learning]]></category>
            <category><![CDATA[data-privacy]]></category>
            <dc:creator><![CDATA[Midhun Mohan]]></dc:creator>
            <pubDate>Mon, 19 Jun 2023 08:19:20 GMT</pubDate>
            <atom:updated>2023-09-22T11:34:32.997Z</atom:updated>
            <content:encoded><![CDATA[<h3>“Empowering Secure Machine Learning in Retail with Federated Learning”</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IxPIpVQkg1sDKv9IXjlm1Q.jpeg" /></figure><p><strong>Introduction</strong></p><p>AI has been a buzzword in the industry for quite a while and has widespread applications and implementations across all industries. And Data is the raw ingredient on which AI is built. Knowingly or unknowingly we produce petabytes of data everyday.</p><p>Data - <em>personally identifiable information</em> - is a double-edged sword, it can certainly make businesses understand their stakeholders and drive growth but at the same time if not managed and secure it can turn out to be the biggest of threats.</p><p>There is an increased importance given to data privacy and security. Governments all over are taking this seriously by bringing into effect various laws and regulations with varying degrees of constraint and restraint - GDPR and CCPA being some of the foremost ones</p><p>For Retailers this is all the more pertinent since it involves tapping into consumer behavior, understanding them better and tracking their ever-changing needs. Armed with this data is what enables a retailer to empower a customer’s decision-making journeys and guide them towards better product discovery and eventually master clienteling <strong>[1]</strong></p><p>Now, <strong>Federated learning</strong>, is one of the recent technology paradigms that enables AI models deployed at the edge for eg: mobile/wearables where the model does not have to share data or “talk” to a central server to deliver insights but can do so by executing on the edge server itself.<strong>[2]</strong></p><p><strong>Distributed Learning</strong></p><p>Distributed machine learning, is one of the most critical components in the ML stack - a multi-node ML system, boosts accuracy, handles larger input data volumes, and improves performance. It further reduces machine errors, assists with data analysis and decision-making and is capable of handling large data sets.</p><p>Large amounts of data can usually be challenging because machine learning techniques have scalability and efficiency problems.</p><p>Large-scale learning requires distributed ML algorithms where they can distribute learning operations across a number of workstations generating a large amount of data. And because data is so large, programmers frequently retrain data to avoid interfering with workflow and use parallel loading.<strong>[3]</strong></p><p><strong>What is federated learning?</strong></p><p>Federated learning allows for a way to train AI models without sharing private user data.</p><p>The objective of federated learning is the transfer of computations to where the data resides. When a globally shared model is purchased and the data is, for instance, on smartphones. Transferring a model to the apparatus allows us to train it collectively.<strong>[3]</strong></p><p>Anyone can take part in federated learning on their devices directly or indirectly. And where computationally constrained devices become a bottleneck, edge devices like smartphones and IoT devices can benefit from on-device data without the data ever leaving the device.[<strong>5</strong>]</p><p>It’s a great idea to move calculations to data in order to create any kind of intelligent system while protecting user privacy. It is actually the decentralized form of machine learning.</p><p><strong>How Federated Learning Works?</strong></p><p>Federated learning enables learning at the periphery, enabling model training to be applied to data that is dispersed among millions of devices. Additionally, it gives you the ability to enhance results obtained from outlying areas.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MiWfEO-SGjaHAO3BJZH0MQ.jpeg" /></figure><p>You will initially choose a model for the capability launch that has either never been trained or has already been trained on the main server. The dissemination of the initial model to the clients would be the subsequent phase in the process.</p><p>Each client continues to use local data to train it locally. It is crucial that this training data, which could include privileged emails, chat logs, private images, and health measurements, be kept secret. It might be challenging or even impossible to collect this data in cloud-based environments.</p><p>After being locally trained, the updated models are transmitted via encrypted communication channels to the main server. It’s crucial to keep in mind that in this scenario, the server only receives trained model parameters rather than actual data. The updates from all clients are averaged and combined to increase the shared model’s accuracy. This model is then returned to all computers and servers.</p><p>The exciting aspect of federated learning is its iterative training process. As a result, the participants can still communicate while the server and clients pass back and forth the updated parameters without jeopardizing its privacy.<strong>[5]</strong></p><p><strong>How different is Federated learning from classical distributed/centralized learning?</strong></p><p>The primary distinction between federated learning and distributed learning is the assumptions made about the properties of the local datasets -</p><ul><li>distributed learning’s original goal is to parallelize computing power</li><li>federated learning’s initial goal is to train on heterogeneous datasets</li></ul><p>In conventional machine learning, the participant data are independent and uniformly distributed. On the other hand, federated learning makes the non-i.i.d assumption because different users have various data types.</p><p>In conventional machine learning, all of the training is done on a single server after the data has been gathered. This training presents numerous privacy issues when the data is shared with the primary cloud server.</p><p>Federation learning has surpassed distributed data training in importance. Users can collectively train local models on local data using federated learning, which protects users’ private information from being shared with a central cloud server. It allows for continuous learning on end-user devices while guaranteeing that no end-user data leaves the device.</p><p>In traditional machine learning, a centralized environment uses all training data to build a single ML model. This function works without a hitch when a central server is available to deliver the predictions. [<strong>3</strong>]</p><p>Federated learning can be implemented on the end-user device, but continuous learning is difficult because models need to be trained on a large dataset to which the end-user device does not have access.</p><p><strong>Role of Federated Learning in Personalization while maintaining data privacy</strong></p><p>Federated learning uses decentralized edge devices or servers to maintain distributed data and applies machine learning techniques to them. To a centralized server, the original data is never ever moved. It persists on the gadget.</p><p>The results are sent back to the server once the training algorithm has finished processing the data. The supplied results have been encrypted. This makes it impossible for anybody to examine the outcomes and recreate the original data. To increase security, the findings can be encrypted using a key which the server doesn’t know, making it nearly hard to decrypt any data. Each device is capable of producing a high-quality model by sending many packages of training data over time. After then, the training algorithm may remove itself from the machine it was using. To provide the fastest upload speeds possible, these updates are quantized and rotated randomly.</p><p>The server only uses the average results from the updates when using the federated averaging technique. Utilizing secure aggregation is an alternative strategy. The server can only decode the aggregate data at that point because this method aggregates the encrypted results from any number of edge devices. This adds another layer of security, making it even more difficult to recover the original data. Each edge device that will transmit its training results first adds zero-sum masks to the results. The secure aggregation protocol is used for this. The results are then presented in an obscured format. The masks, on the other hand, precisely cancel out when the training results are added up.</p><p>Even though the server cannot access any edge device’s results, there is still a privacy concern about what would happen if one device had provided special information that stood out from the other findings gathered. The question is whether sharing the data from that one device with the other results on the server might compromise its source’s right to privacy. That it might happen is the unsettling response. To prevent this from happening, any anomalous data is removed. The argument is that in order for machine learning to function at its best, it must recognize and utilize common patterns in the data.</p><p>An alternative strategy that could be used is differential privacy. This limits the amount of data that can come from a single-edge device and be used in the model. Additionally, noise can be added to mask any uncommon data. This prevents one device from supplying too much data and having a big impact on the model being built. Model memorization is the term for this.[<strong>4</strong>]</p><p><strong>Model Size and Federated Learning</strong></p><p>Model size describes the amount of storage and processing power needed to store and use a machine-learning model. Larger models usually have more parameters and can capture more intricate patterns in the data, but they also take more time and money to train and use.</p><p>The model size is a crucial consideration when using federated learning, a distributed machine learning technique that enables multiple parties to train a shared model without sharing their data.</p><p>Each participant in federated learning trains a local version of the model on their own data, and the updates are then combined to enhance the overall model. The parties may find it difficult to train the model locally when the model size is large because it might need an excessive amount of memory and processing power. As a result, the training period may be prolonged and the updates to the global model may communicate slowly.</p><p>It is possible to reduce the size of the model without noticeably degrading its performance using methods like model compression or pruning to alleviate this problem. In order to design a model architecture that can be effectively trained and deployed on end devices, it is also crucial to take into account the device and network constraints.</p><p>Utilizing methods like Federated Learning by Co-gradient Descent, which enables the parties to train using a subset of the global model parameters, is another option that can help to lower the memory and processing demands on the local devices.</p><p>Large models can be trained on distributed data by combining federated learning and model size, which can enhance the efficiency and scalability of machine learning systems. The design and implementation of federated learning systems must carefully address these challenges as they are presented by this strategy, including the communication and privacy issues it raises.</p><p><strong>How to achieve Personalization in FL Models?</strong></p><p>With the freedom to store data on the user’s device for further processing, the potential for personalizing the user experience has increased dramatically. Generally, there are three stages in creating a personalized federated learning experience.</p><ul><li><strong>Initial Stage</strong></li></ul><p>The initial stage marks the first step in creating a communication channel between different participants in a network. This phase refers to the initial exchange of information. While edge computing encrypts end-user data, locally trained models are then shared across a network based on federated learning. So the initial set of communications here is offloaded to the next layer <strong>[7] [8]</strong></p><ul><li><strong>Training Stage</strong></li></ul><p>In the training phase, a local model is built using the end-user&#39;s data. In edge computing, this happens at the edge and in federated learning on the device itself. It is an iterative process where the global model converged on the central server is updated collectively, improving accuracy for each learning cycle <strong>[7] [8]</strong></p><ul><li><strong>Personalization Stage</strong></li></ul><p>This phase is the continuation of the learning phase. The personal information is used for creating a local model on the local device. In federated learning, this phase addresses the end-user device, the source of custom data, and enables the local models to more efficiently align with user information, creating an overall personalized experience <strong>[7] [8]</strong></p><p><strong>What motivates Personalization in Federated Learning?</strong></p><p>Federated Learning enables the development of the local model on the user’s device. However, no two devices or users are the same. There are a number of variations in on-premise models built across multiple edge devices. This leads to heterogeneity of the data on different levels. This is the main driver for the implementation of personalization concepts in the entire communication network. Thus, the motivation to implement personalization is categorized below. <strong>[8]</strong></p><ul><li><strong>Device Heterogeneity</strong></li></ul><p>The increasing use of IoT devices and the constant improvement in the communication capabilities of end devices have led to a multitude of options. Edge devices now range from data storage and processing capabilities to communications and hardware capabilities. All of these features directly impact the data processing and communications costs of running these devices locally. It becomes particularly challenging when devices of varying complexity are subjected to the same iterative process of model training through global updates. Therefore, communication problems with heterogeneous devices can only be addressed by implementing custom federated learning techniques <strong>[7] [8]</strong></p><ul><li><strong>Data Heterogeneity</strong></li></ul><p>The key term in this category is non-IID data distribution. It refers to the non-identical distribution of independent data. Each user has their device commit environment, resulting in a unique result for each device. There exists a variety of different data types in each device and also the number of samples taken to train the local model. This problem leads to the divergence of the global model, which is the exact opposite of the end result.</p><p>Although, there are solutions in the form of federated averaging (FedAvg) to deal with data heterogeneity <strong>[9]</strong>, this may lead to reduced performance and can lead to insignificant results. Hence, it’s very much required to implement personalized Federated Learning models <strong>[7] [8]</strong></p><ul><li><strong>Model Heterogeneity</strong></li></ul><p>When multiple devices are connected over a network, users typically agree on a predefined communication architecture. This enables an efficient environment for the convergence of local models and eases the deployment of the global model to all edge devices. However, with IoT devices, the adaptability and need for a more personalized communication model have come to light. Due to the variety of environments and resource constraints, each device searches for the model that best suits its operation, while maintaining the privacy of its architecture. Therefore, an adaptation of the FL model is required to create a solution in which different model architectures can be connected and communicated via a global standard model.</p><p>While these heterogeneous problems increase the complexity of the communication network, the challenges have initiated the search for likely solutions. Therefore, these have become the main motivating factors that have led to the rise of personalized federated learning <strong>[7] [8]</strong></p><p><strong>Techniques Used in Federated Learning for Personalization</strong></p><ul><li><strong>Meta-Learning</strong></li></ul><p>The concept of meta-learning is focused on increasing the adaptability of the local model by introducing the learning algorithm to a variety of functions <strong>[10]</strong>. Meta-learning offers several variations of data for the local model by improving the ability to train and adapt to new data. This increases the local model’s ability to train variations of the global model.</p><p>Some examples of meta-learning algorithms are Model-Agnostic Meta-Learning (MAML) <strong>[11]</strong> and Reptile<strong> [12]</strong>. They are quite popular for their fast data processing and swift adaptability. MAML lets data processing in two series: meta-training and meta-testing. While the training phase focuses on building the global model using a variety of tasks, the testing phase redesigns global learning for local models. MAML functions are based on the concept of federated averaging, with the Reptile algorithm playing a role similar to FedAvg during the meta-testing phase.</p><p>So the idea is to improve the accuracy of the global model so that it can be easily adjusted for the local endpoint enabling meta-learning to address personalization both at the core and at the end users</p><ul><li><strong>Transfer Learning [7] [8]</strong></li></ul><p>As the name says, this technique is based on transferring machine learning results from one source to another source. It allows the use of previously trained learning models and speeds up the process at the local end. With federated learning, the idea revolves around sharing the global model with edge devices, so they can customize the update at the local end.</p><p>The process is mainly carried out through two approaches.</p><p>In the first approach, the global model is first trained via the traditional FL approach, and the output is shared with the end-user devices. Post that, Each device then uses the global model and its custom data to create a local model. Only a few selected parameters are retrained with the local data to avoid training challenges. At the same time, the lower layers of the global model are transferred. and re-used.</p><p>The second approach divides learning into two layers: foundation and personalization. The foundation layer is common and trained collectively using traditional Federated Learning approaches. These Custom layers are trained locally with customer-specific data. Each end-user device trains these custom layers using the typical global model leading to improved incorporation of personalized learning techniques into FL models</p><ul><li><strong>Multi-Task Learning</strong></li></ul><p>This technique focuses on identifying the relationship between different local models during the training process. This learns specific tasks from multiple devices at the same time without compromising the privacy of the input data resulting in personalized models and allows each device to gain from other devices by training their initial models.</p><p>The central server handles the similarity of model parameters between different clients, allowing the end devices to update their models based on the identified data relationships. As a result, the issue of data heterogeneity is addressed along with the improved quality of personalization at the local end. MOCHA <strong>[13]</strong> is a standard algorithm for implementing multitasking learning</p><p><strong>Conclusion</strong></p><p>Personalization has become significant in the rapidly evolving market of digital communications. So, with the progressive idea of edge computing and FL, the idea of personalization evolved, which led to personalized federated learning. User experience can be improved by implementing the three major personalization stages; Initial, Training, and Personalization.</p><p>The key challenge for personalization remains the diversity of different stakeholders within a single network. This is called heterogeneity, which can be multi-fold in federated learning. Spread across variations of edge devices, data types, and model architectures; Heterogeneity creates diverse challenges. Having said that, these challenges are the important motivating factors to develop custom FL models</p><p>The standard mechanism that is implemented to achieve Personalized incorporates Meta-Learning, Transfer Learning, and Mult-Tasking learning. All the techniques focus on generating a personalized experience for the end device users without compromising on privacy and solving various heterogeneity issues. <strong>[7] [8]</strong></p><p><strong>References</strong></p><p>[1] <a href="https://www.analyticsvidhya.com/blog/2021/05/federated-learning-a-beginners-guide/">https://</a>pwc.com/future-of-cx</p><p>[2] <a href="https://www.analyticsvidhya.com/blog/2021/05/federated-learning-a-beginners-guide/">https://www.analyticsvidhya.com/blog/2021/05/federated-learning-a-beginners-guide/</a></p><p>[3] <a href="https://research.ibm.com/blog/what-is-federated-learning">https://research.ibm.com/blog/what-is-federated-learning</a></p><p>[4] <a href="https://analyticsindiamag.com/distributed-machine-learning-vs-federated-learning-%20%20%20%20%20%20%20%20which-is-better/">https://analyticsindiamag.com/distributed-machine-learning-vs-federated-learning- which-is-better/</a></p><p>[5] <a href="https://federated.withgoogle.com/">https://federated.withgoogle.com/</a></p><p>[6] <a href="https://research.aimultiple.com/federated-learning/">https://research.aimultiple.com/federated-learning/</a></p><p>[7] Towards Personalized Federated Learning Alysa Ziying Tan, Han Yu∗, Lizhen Cui∗, and Qiang Yang∗, Fellow, IEEE</p><p>[8] <a href="https://blog.nimbleedge.ai/personalized-fl-101/">https://blog.nimbleedge.ai/personalized-fl-101/</a></p><p>[9] S. P. Karimireddy, S. Kale, M. Mohri, S. J. Reddi, S. U. Stich, and A. T. Suresh, “SCAFFOLD: Stochastic Controlled Averaging for Federated Learning,” in ICML, 2020, pp. 5132–5143</p><p>[10] T. Hospedales, A. Antoniou, P. Micaelli, and A. Storkey, “Metalearning in neural networks: A survey,” IEEE TPAMI, no. 01, pp. 1–1, 2020</p><p>[11] C. Finn, P. Abbeel, and S. Levine, “Model-agnostic meta-learning for fast adaptation of deep networks,” in ICML, 2017, pp. 1126–1135</p><p>[12] A. Nichol, J. Achiam, and J. Schulman, “On First-Order Meta-Learning Algorithms,” arXiv:1803.02999, 2018</p><p>[13] V. Smith, C.-K. Chiang, M. Sanjabi, and A. Talwalkar, “Federated Multi-Task Learning,” in NeurIPS, vol. 30, 2017, pp. 4427–4437</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1e8f49a3906b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/litmus7/breaking-barriers-in-data-privacy-how-federated-learning-empowers-secure-machine-learning-1e8f49a3906b">“Breaking Barriers in Data Privacy: How Federated Learning Empowers Secure Machine Learning”</a> was originally published in <a href="https://medium.com/litmus7">Litmus7 Systems Consulting</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Metadata Driven Data Quality Engine]]></title>
            <link>https://medium.com/litmus7/metadata-driven-data-quality-engine-4ddfbf674afe?source=rss----d47509a7187---4</link>
            <guid isPermaLink="false">https://medium.com/p/4ddfbf674afe</guid>
            <category><![CDATA[data-quality-tool]]></category>
            <category><![CDATA[data-quality]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Sarath Chandran]]></dc:creator>
            <pubDate>Fri, 16 Jun 2023 17:19:28 GMT</pubDate>
            <atom:updated>2023-06-20T07:55:11.351Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E8s9t4Sj7T6TuT7Ywjkz-g.jpeg" /></figure><p>Organizations have evolved substantially, either being data driven or having their entire business models based on capture and utilization of data. However, though, crucial to the business of most companies, there are a lot of issues associated with the quality of data that is available for insights and decision making. <strong>According to Forbes, Insights and KPMG, 84% of CEOs are concerned about the quality of the data they’re basing their decisions on.</strong></p><h3>Importance of Data Quality</h3><p>For businesses, bad data can have serious financial repercussions. Substandard data quality is frequently blamed for operational blunders, incorrect analytics, and poorly thought-out company initiatives. Added costs when goods are delivered to the wrong customer addresses, missed sales opportunities as a result of inaccurate or incomplete customer records, and fines for improper financial or regulatory compliance reporting are a few examples of the economic harm that poor data quality can bring about. Use of business intelligence (BI) and analytics to boost the decision-making process in businesses is frequently stated as being hindered by corporate leaders and business managers’ lack of trust in the data. The general presumption that a data scientist spends bulk of their time in cleaning the data is also a testament in itself to the expected issues with data quality.</p><p>According to a survey by Gartner, <em>“Organizations believe poor data quality to be responsible for an average of $15 Million per year in losses.” </em>Gartner also found that nearly 60% of those surveyed didn’t know how much bad data costs their businesses because they don’t measure it in the first place.</p><h3>Why does the quality of data degrade ?</h3><p>The data being collected could be inaccurate in a variety of ways. It is a fact that we cannot anticipate 100% accuracy even when machines are utilized to collect data. We can anticipate certain inaccuracies when it is entered manually by people. Sometimes, because the manner the data was collected and gathered, now when the data is given for entry it already contains a number of inaccuracies. This further leads to incomplete or inaccurate information being collected.</p><p>There is also the situation of data that is unclear or that conflicts with other information gathered. We also have a problem with duplicate data. As the name implies, this occurs when a record in a database shares data with another record. The usage of this data for analytics results in erroneous reporting, lost productivity, and unnecessary expenditures. All of this makes it even more challenging to integrate when working with massive amounts of data (big data), and it also makes it challenging to assess the data’s quality. For data quality, no precise standards have been established. This is yet another factor making it challenging to guarantee data quality.</p><h3>Existing DQ Solutions</h3><p>Today, there are various data engineering tools that may be used to evaluate the quality of data. Every year, brand-new models with cutting-edge features are released. Tools are also available with functionality for data matching, data privacy, and cataloging. All of them are really effective tools that make data integration simple. The key drawback of these solutions is that proper user training is necessary to ensure that the business requirements are implemented correctly. It probably will not function effectively if the user is not confident in the specificity of the need. These technologies also have the drawback of requiring extensive programming and possibly even knowledge of specific computer languages. This is made worse by the fact that data analysts are typically the ones that utilize data quality evaluation tools, and that is almost at the far end of the data lifecycle whereas ideally one should aspire to capture the issue at source or when it enters the data processing systems through data pipelines. Additionally, the outputs these systems produce after doing a data quality review are challenging to comprehend or decipher.</p><h3>Metadata Driven Data Quality Engine</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wd8M1_22B4kJEkhLo6N-YQ.png" /><figcaption>Figure 1: Data Quality Framework</figcaption></figure><p>The metadata driven data quality engine is a pluggable library which verifies the quality of data once plugged into the ETL pipeline. The incoming data is validated against the schema as well as other configurable checks. Users can upload a sample data extract of the file which they would like to validate, into the portal. Schema (data type of each column, pattern of the date etc.) is inferred automatically and is displayed to the user. They can edit the values as needed. This schema is received as a JSON file from the simple UI created for collecting the different checks to be performed on the data. The JSON file is saved as a document with Unique File Identifier as the key. This includes validation based on, but not limited to, data type check, nullability and specific pattern for a particular column. There is an option to configure a threshold limit for the valid records received after the assessment. If the data contains valid records greater than the configured threshold limit, the file is marked as DQ certified and is moved to a success folder. If it does not contain sufficient valid records, the file is marked as DQ Rejected and ETL process is stopped. The failed records are saved to a failure folder. Further logs are available on the records which shows which are the checks that each column failed. Valid records from the succeeded file are then persisted to the cleansed layer for further analytics.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wtMPwh1MvlxFwaKuLw7CqA.png" /><figcaption>Figure 2: UI Wireframe</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dHzNvcszB25TphtNgFrjkg.png" /><figcaption>Figure 3: Editable Metadata Wireframe</figcaption></figure><p>The engine reads the metadata by passing the unique metadata id. This is followed by reading of the data and applying quality controls to each record. For every record, a validation result is generated based upon the filtering of valid records. The information regarding the audit shall be stored in the format as in Fig. 4 when there is an invalid record.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*4ePsjmsoKdCQwhl3rAozuw.png" /><figcaption>Figure 4: Audit log structure</figcaption></figure><p>The audit file is loaded into a Postgres table on top of which analysis queries are run to retrieve useful information like records for success and failures per file, most often failed column, most frequently unsuccessful constraint etc. These derived insights are also persisted in Postgres database tables which can be accessed via the UI.</p><p>Both Python Pandas and Spark dataframe are used by the DQ engine. When the Data Quality framework function is invoked, the user can specify the engine to be used — either Python or Spark. Accordingly, the corresponding execution framework is leveraged.</p><h3>Wrap Up</h3><p>The fact that anyone could use this to evaluate data quality through configurable checks is one of the reasons why you should use this pluggable library. This DQ engine has a lot of potential in the future. A more complex user interface could be provided, which would enable users to perform additional checks. The DB integration can also be documented using it. Additionally, this library can be enhanced with new functions and made available to users as a service. We can further provide the fields that need to be ingested into the final destination through the UI. This service can additionally include an ingestion facility where the data can be evaluated after it has been ingested.</p><p><strong>In the modern world, where businesses use data to gain insights, the quality of the data is a key component.</strong> From the initial data collection to data ingestion to data storage, there are a wide variety of ways in which data may get inaccurate. There is no escaping this. The use of tools for determining data quality and processing them in line with that quality is the best way to solve this problem. The tools and techniques that are now available for evaluating data quality are too complicated and complex for commercial users. Our Data Quality Engine is intended to be a straightforward pluggable library that can be easy and quick to implement specific checks for evaluating the quality of the data. Depending on how many records exceed the user-specified threshold limit, the data evaluation will either be a success or a failure. The existing DQ solution developed has a working version of Python engine and Spark engine for Data Quality Assessment and report generation.</p><p><strong>References</strong></p><p>[1]<a href="https://www.forbes.com/sites/forbespr/2017/05/31/poor-quality-data-imposes-costs-and-risks-on-businesses-says-new-forbes-insights-report/?sh=7c906a94452b">https://www.forbes.com/sites/forbespr/2017/05/31/poor-quality-data-imposes-costs-and-risks-on-businesses-says-new-forbes-insights-report/?sh=7c906a94452b</a></p><p>[2]<a href="https://www.forbes.com/sites/forbestechcouncil/2021/10/14/flying-blind-how-bad-data-undermines-business/?sh=2eae9a9e29e8">https://www.forbes.com/sites/forbestechcouncil/2021/10/14/flying-blind-how-bad-data-undermines-business/?sh=2eae9a9e29e8</a></p><p>[1] <a href="https://www.collibra.com/us/en/blog/what-is-data-quality">https://www.collibra.com/us/en/blog/what-is-data-quality</a></p><p>[2] <a href="https://www.confessionsofadataguy.com/data-quality-great-expectations-for-data-engineers/">https://www.confessionsofadataguy.com/data-quality-great-expectations-for-data-engineers/</a></p><p>[3] <a href="https://www.collibra.com/us/en/blog/the-7-most-common-data-quality-issues">https://www.collibra.com/us/en/blog/the-7-most-common-data-quality-issues</a></p><p>[4] <a href="https://www.precisely.com/blog/data-quality/data-quality-problems-errors">https://www.precisely.com/blog/data-quality/data-quality-problems-errors</a></p><p>[5] <a href="https://www.techtarget.com/searchdatamanagement/tip/Top-data-quality-management-tools">https://www.techtarget.com/searchdatamanagement/tip/Top-data-quality-management-tools</a></p><p>[6] Elouataoui, W.; El Alaoui, I.; El Mendili, S.; Gahi, Y. An Advanced Big Data Quality Framework Based on Weighted Metrics. <em>Big Data Cogn. Comput.</em> <strong>2022</strong>, <em>6</em>, 153. <a href="https://doi.org/10.3390/bdcc6040153">https://doi.org/10.3390/bdcc6040153</a></p><p>[7] Cai, L. and Zhu, Y., 2015. The Challenges of Data Quality and Data Quality Assessment in the Big Data Era. Data Science Journal, 14, p.2. DOI: <a href="http://doi.org/10.5334/dsj-2015-002">http://doi.org/10.5334/dsj-2015-002</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4ddfbf674afe" width="1" height="1" alt=""><hr><p><a href="https://medium.com/litmus7/metadata-driven-data-quality-engine-4ddfbf674afe">Metadata Driven Data Quality Engine</a> was originally published in <a href="https://medium.com/litmus7">Litmus7 Systems Consulting</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Change Date Capture (CDC) using Debezium]]></title>
            <link>https://medium.com/litmus7/change-date-capture-cdc-using-debezium-8113a1309d06?source=rss----d47509a7187---4</link>
            <guid isPermaLink="false">https://medium.com/p/8113a1309d06</guid>
            <dc:creator><![CDATA[Emmanuel]]></dc:creator>
            <pubDate>Wed, 15 Mar 2023 06:23:19 GMT</pubDate>
            <atom:updated>2023-03-15T06:23:19.591Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CUN3UHx2Xo7vqkrAYapzYQ.jpeg" /></figure><p><strong>What is CDC?</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*9-X8er_1ARJTaETTm9PDFA.png" /></figure><ul><li>Change Data Capture, CDC is a software process that identifies and tracks changes done to a database and extracts those changes in a manner that they can be replicated to downstream systems.</li><li>A few use cases for CDC, <em>but are not limited to are -</em></li></ul><blockquote>Invalidating a cache (An event driven cache invalidation by capturing change events in logs and processing them.)</blockquote><blockquote>Real time data loading to Data Warehouse. (from OLTP systems to OLAP to run analytical queries)</blockquote><blockquote>For maintaining an audit log</blockquote><p><strong>Different Approaches for CDC</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*je-iQL5nerCPbcpTe3qtWg.png" /></figure><p><strong>Log Based CDC</strong></p><blockquote>Depends on the binary log of source databases - impact on source systems is low</blockquote><blockquote>Many tools are available in the market — Keboola, Oracle Golden Gate, Qlik Replicate, FiveTran and etc</blockquote><blockquote><strong>Debezium</strong> is an open source CDC tool which leverages the power of Kafka</blockquote><p><strong>Environment Setup</strong></p><ul><li>Download and unzip the kafka service</li><li>Download and unzip the Debezium-Mysql plugin</li><li>Enable Zookeeper and Kafka using the below commands</li></ul><blockquote>sudo systemctl enable zookeeper</blockquote><blockquote>sudo systemctl enable kafka</blockquote><p><strong>Debezium-Mysql Plugin Setup</strong></p><ul><li>Install MySQL</li><li>MySQL has a binary log (binlog) that records all operations in the order in which they are committed to the database. This includes changes to table schemas as well as changes to the data in tables. MySQL uses the binlog for replication and recovery.</li><li>The Debezium MySQL connector reads the binlog, produces change events for row-level INSERT, UPDATE, and DELETE operations, and for the schema changes. It then emits the change events to Kafka topics, Client applications then read those Kafka topics.</li><li>Download and unzip the debezium-mysql plugin.</li></ul><p><strong>Setting up the connector in Debezium-Mysql Plugin</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/649/0*MqR_I2foM68k4nqO" /><figcaption>connector.txt</figcaption></figure><p><strong>Setting up the worker in Debezium-Mysql Plugin</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/701/0*xWDJFldHvXAIxWD5" /><figcaption>worker.txt</figcaption></figure><p><strong>Starting the Connection</strong></p><blockquote>sh connect-standalone.sh /home/kafka/kafka/plugins/debezium-connector-mysql/worker.txt /home/kafka/kafka/plugins/debezium-connector-mysql/connector.txt</blockquote><p><strong>Debezium-Postgres Plugin Setup</strong></p><ul><li>Install MySQL</li><li>Debezium uses logical decoding feature available in PostgreSQL to extract all persistent changes to the database in an easy to understand format which can be interpreted without detailed knowledge of the database’s internal state.</li><li>The connector produces a <em>change event</em> for every row-level insert, update, and delete operation that was captured and sends change event records for each table in a separate Kafka topic. Client applications read the Kafka topics.</li><li>Download and unzip the debezium-postgres plugin.</li></ul><p><strong>Setting up the connector in Debezium-Postgres Plugin</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/671/0*6Y2epPTMHo6uJXAE" /><figcaption>connector.txt</figcaption></figure><p><strong>Setting up the worker in Debezium-Postgres Plugin</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/0*QWXzjU7MLNkNKi6R" /><figcaption>worker.txt</figcaption></figure><p><strong>Starting the Connection</strong></p><blockquote>sh connect-standalone.sh /home/kafka/kafka/plugins/debezium-connector-postgres/worker.txt /home/kafka/kafka/plugins/debezium-connector-postgres/connector.txt</blockquote><p><strong>Note:</strong></p><blockquote>In Mysql both DDL and DML operations are captured from two different Kafka topics.</blockquote><blockquote>But for postgres it only captures DML operation logs.</blockquote><blockquote>In Mysql database, when you issue a DDL statement it doesn’t record an event for each pre-existing record that might be altered by the change; instead, databases simply record that a DDL change has occurred and what that DDL change was composed of.</blockquote><p><strong>References</strong></p><ul><li><a href="https://debezium.io/documentation/reference/0.9/connectors/postgresql.html">Debezium connector for PostgreSQL</a></li><li><a href="https://debezium.io/documentation/reference/2.1/connectors/mysql.html">Debezium connector for MySQL</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8113a1309d06" width="1" height="1" alt=""><hr><p><a href="https://medium.com/litmus7/change-date-capture-cdc-using-debezium-8113a1309d06">Change Date Capture (CDC) using Debezium</a> was originally published in <a href="https://medium.com/litmus7">Litmus7 Systems Consulting</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Transformations With DBT]]></title>
            <link>https://medium.com/litmus7/data-transformations-with-dbt-cb7ded876627?source=rss----d47509a7187---4</link>
            <guid isPermaLink="false">https://medium.com/p/cb7ded876627</guid>
            <dc:creator><![CDATA[George Saju]]></dc:creator>
            <pubDate>Mon, 17 Oct 2022 01:17:32 GMT</pubDate>
            <atom:updated>2022-10-17T01:17:32.510Z</atom:updated>
            <content:encoded><![CDATA[<h3>What is DBT?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*itcxrr_DMYd-d4OYCvi-Lw.jpeg" /></figure><h3>Data Build Tool(DBT) is a SQL based powerful transformation workflow tool that marries conventional SQL based transformations with ‘software engineering best practices’ like modularity, portability, CI/CD, and documentation.</h3><p>With DBT, anyone who knows how to write SQL SELECT statements has the power to build blocks of SQL based functions (called as models), carry out functional and technical tests in a structured manner, and schedule jobs to produce reliable, actionable datasets for analytics. The tool acts as an orchestration layer on top of the data warehouse to improve and accelerate data transformations and integrations.</p><p>DBT (data build tool) makes data engineering activities easier for people with data analyst skills to transform the data in the warehouse using simple select statements, effectively creating the entire transformation process with code. We can write custom business logic using SQL, automate data quality testing, deploy code, and create data documentation side-by-side with the code. This is more important today because there is a great shortage of data engineering professionals in the marketplace and the end destination of most current data engineering pipelines are still data warehouses and hence SQL based transformation would work. Anyone who knows SQL can now build production-grade data pipelines, reducing the barrier to entry that previously limited staffing capabilities for legacy technologies. However when adopting a SQL based transformation engine special care has to be given to ensure that the queries are optimized from a CPU impact/compute standpoint , especially given that most of the modern day data warehouses are “rent-seeking” cloud platforms where whilst storage is cheap compute is weighed in gold</p><h3>Connecting DBT with Bigquery</h3><ol><li>Click BigQuery to set up your connection.</li></ol><p>2. Click Upload a Service Account JSON File in BigQuery settings.</p><p>3. Select the JSON file you downloaded in Generate Bigquery Credentials. dbt Cloud will fill in all the necessary fields.</p><p>4. Click Test at the top. This verifies that DBT Cloud can access your BigQuery account.</p><p>5. If you see “<strong>Connection test Succeeded</strong>!” then click Continue. If it fails, you might need to go back and regenerate your BigQuery credentials.</p><p><strong>6. </strong>The connection details will be visible in the settings as follows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/661/0*JRZ9g8W78x85SlN6" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/678/0*LduLBvzNI2Ltr1Kd" /></figure><h3>DBT Models</h3><p>A model is basically a select statement. Models are defined in .sql files typically in the models directory. Models properties can be declared in .yml files in the models/ directory.</p><p>Here is a sample model we created for obtaining the department_name,department_id,product_id in models/staging (dbt_department_test.sql)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*BgOVjw4TM6AK_xzb" /></figure><h3>DBT Test Cases</h3><p>schema.yml file can be used to define assertions that the data should adhere to . For eg : here we want to ensure that the attribute , department_id, has to be unique and not null. Setting these via a configuration file ensures that in the future if we want to modify these properties then it can be done without having to touch the main SQL block and hence minimizing chances of regression issues being introduced.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*aButDhvuotTP-SKM" /></figure><h3>DBT Common Commands</h3><p>Some of the commonly used DBT commands are as follows : [2]</p><ol><li><strong>dbt run</strong> executes compiled sql model files against the current target database.bt connects to the target database and runs the relevant SQL required to materialize all data models using the specified materialization strategies.</li></ol><p><strong>2. dbt compile</strong> generates executable SQL from source model, test, and analysis files.</p><p><strong>3. dbt init</strong> helps get you started using dbt Core</p><p><strong>4. dbt build</strong> command will run models,test tests,snapshot snapshots,seed seeds.</p><p><strong>5. dbt docs</strong> has two supported subcommands: <strong>generate and serve</strong></p><p><strong>dbt docs generate </strong>is responsible for generating your project’s documentation which contains metadata about the tables and views produced by the models in your project.</p><p><strong>dbt docs serve </strong>command starts a webserver on port 8000 to serve your documentation locally</p><h3>Jinja Templating in DBT</h3><p>Jinja is a templating engine used for improving code readability and for promoting re-usable SQL queries. Templating as a coding standard is followed in core software programming coding that helps to separate data, logic and presentation layers in MVC[1] Using Jinja turns the dbt project into a programming environment for SQL that aren’t normally possible in SQL like using control structures and environment variables in the dbt project for production deployments.</p><p>We can recognize Jinja based on the delimiters and the language uses, which we refer to as “curlies”:</p><ul><li>Expressions {{ … }}: Expressions are used when we want to output a string. We can use expressions to reference variables and call macros.[2]</li><li>Statements {% … %}: Statements are used for control flow, for example, to set up for loops and if statements, or to define macros.[2]</li><li>Comments {# … #}: Jinja comments are used to prevent the text within the comment from compiling[2]</li></ul><p>Here’s an example of a dbt model that leverages Jinja we used :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*EUUskCaXtigEthth" /></figure><p>The below screenshot shows how we can write software engineering style if- then- else based control statements</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*GaWd6KtdB77hjQ4d" /></figure><h3>DBT Documentation</h3><p>DBT provides a way to generate documentation for the dbt project and render it as a website.Here is an example of our model documentation. This provides an interface for data analyst , business analysts etc to search of existing columns , their descriptions and also the code base used to populate the table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9Qvb09wMaXvBDLTY" /></figure><h3>DBT Lineage</h3><p>In DBT Documentation we can see the lineage at the bottom right .Here represented the dbt_department_test lineage graph.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*qFgyngG6G3Veg7OE" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*LXJR6BouIbQ4hlIM" /></figure><h3>DBT Core Vs DBT Cloud</h3><p>DBT provides two options for implementation (1) dbt Core and (2) dbt Cloud</p><ul><li>dbt Core includes a command line interface (CLI) so that users can execute dbt commands using a terminal program.dbt core switch between the terminal and the user interface of the data warehouse which makes analytics engineering practices difficult.</li><li>On other hand, Dbt cloud integrates analytics engineering workflows in better way.</li></ul><p>In dbt core we need extra scheduling tools like jenkins and airflow whereas in dbt cloud we can schedule by using dbt commands.</p><h3>References</h3><p>[1] <a href="https://medium.com/analytics-and-data/jinja-the-sql-way-of-the-ninja-9a64fc815564">https://medium.com/analytics-and-data/jinja-the-sql-way-of-the-ninja-9a64fc815564</a></p><p>[2] <a href="https://docs.getdbt.com/docs/building-a-dbt-project/documentation">https://docs.getdbt.com/docs/building-a-dbt-project/documentation</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cb7ded876627" width="1" height="1" alt=""><hr><p><a href="https://medium.com/litmus7/data-transformations-with-dbt-cb7ded876627">Data Transformations With DBT</a> was originally published in <a href="https://medium.com/litmus7">Litmus7 Systems Consulting</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Delta Lake — 3rd generation data analytics platforms for enterprises  Backdrop]]></title>
            <link>https://medium.com/litmus7/delta-lake-3rd-generation-data-analytics-platforms-for-enterprises-backdrop-5e0a94187ce?source=rss----d47509a7187---4</link>
            <guid isPermaLink="false">https://medium.com/p/5e0a94187ce</guid>
            <category><![CDATA[delta-lake]]></category>
            <dc:creator><![CDATA[Vinod Indira]]></dc:creator>
            <pubDate>Fri, 30 Sep 2022 04:12:35 GMT</pubDate>
            <atom:updated>2022-10-26T09:50:01.809Z</atom:updated>
            <content:encoded><![CDATA[<h4>Delta Lake — 3rd generation data analytics platforms for enterprises <strong>Backdrop</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*zz5u_11CHNzfdJ4ge13FLQ.jpeg" /></figure><p>Back in the day when enterprises started with business intelligence reporting and descriptive analytics, Data warehouse evolved as the go to solution for this purpose. However with the digital revolution, data started to increase tremendously, not just in terms of volume but also in terms of the type of data (variety) and the speed at which data was generated (velocity) and thus arose the need for data lakes. Data lakes (Apache Hadoop based) are file based low cost storage systems that holds the data in query enabled file formats such as Apache Parquet, ORC and sometimes as AVRO. Data lakes with its ‘schema-on-read’ approach proved to be the best approach for storing data at speed and quantity at relatively cheaper cost. Raw format of data storage also enabled machine learning and AI. However for conventional BI reporting, data still needed to be transformed and loaded from data lakes to a data warehouse. Towards the latter part of the last decade cloud data stores (object stores like S3 , Azure Blob , GCS) started to be preferred over on premise Hadoop based data lakes.</p><h4>Why Data warehouse and Data Lakes won’t suffice?</h4><h4>Challenges with Data warehouse</h4><ul><li>Supports structured data</li><li>Data warehouse ETL used for batch processing of data, minimal support for streaming</li><li>SQL friendly or limited to it, no direct support for advanced analytics like ML</li><li>Poor performance and cannot be scaled easily with increasing workloads</li><li>Data warehouses are costly</li></ul><h4><strong>Challenges of Data lake</strong></h4><ul><li>There is no consistency guarantee because of job failure due to corrupt data and hardware or software failure</li><li>Creation of small file is another challenge, when we dump files in small intervals it leads to creation of smaller files</li><li>Difficult to perform operations like update, delete and merge operations</li><li>Data validation and quality enforcement are poor</li><li>Cloud data lakes on object stores like a S3, Azure blob storage or GCS stores data as key-value pairs and hence difficult to achieve ACID. transactions [<a href="https://en.wikipedia.org/wiki/ACID">https://en.wikipedia.org/wiki/ACID</a>]</li></ul><p>To circumvent these problems, most organizations follow a two tier approach (2nd generation of data analytics platforms) where the initial ingestion happens onto a data lake and further on an “ETL” process pushes the data into a Data warehouse. However even this approach has its fair share of issues especially with respect to reliability, data staleness and data pipeline complexity increasing the number of fault points and entry points for data issues.</p><h4><strong>How do we address these issues?</strong></h4><p>Around 2020, the concept of Lakehouse started becoming common, we needed a new paradigm that was combining the best of both worlds — Data Lakes and Datawarehouses. The paper by Michael Armburst, Ai Ghodsi, Reynold Xin, and Matel Zaharai (Databricks and UC Berkeley and Stanford University) [3] covered this concept in detail explaining the need for such a solution and how this solves for the issues that are current in today’s data platforms. Delta and Apache Iceberg are two software systems that help to implement a Lakehouse. Lakehouses are enabled by a new system design: implementing similar data structures and data management features to those in a data warehouse, directly on the kind of low cost storage used for data lakes [9]</p><p>Databricks provides integration with Delta.io [https://delta.io/] that helps achieve the lakehouse paradigm. In our blog we will cover Delta Lakes in Databricks environment where data can be “stored” as Delta tables.</p><p><strong>How does Delta lake support ACID properties</strong></p><p>Deltalog is the key factor of delta tables and act as a transactional log which ensures atomicity, consistency, isolation, and durability of user-initiated transactions.</p><p>Delta lake enables ACID support, so if a job fails during the “overwrite” operation, data is not lost as changes won’t be committed to the log file of _delta_log_ directory. Also, since Delta Lake does not remove old files in the “overwrite operation”, old state of data is maintained and there is no data loss.</p><p><strong>Atomicity &amp; Consistency:</strong></p><p>DDL/DML operations that happen with the delta table are logged onto a transaction log json file created in _delta_log directory which maintains the latest commit info. Failed transactions will never have the data written to the tables. This approach ensures that data will not be corrupted and hence ensures atomicity Also, delta lake’s strong schema check ensures consistency.</p><p><strong>Isolation &amp; Durability:</strong></p><p>Isolation means the ability to support multiple transactions where the transactions don’t interfere with each other. In delta lake, when a transaction starts it takes a snapshot of the current delta log and when complete ‘delta lake’ checks the delta log updated for any other transactions. The audit log files are created with sequence, based on the order in which the transactions were completed.</p><p>All of the transactions made on Delta Lake tables are stored directly to the disk. This process satisfies the ACID property of durability, meaning it will persist even in the event of system failure.</p><h4><strong>Find below Comparison:</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/625/1*OmkNDtJRl5wAJigk4aH5Ww.png" /></figure><p><strong>Delta Lake Demonstration</strong></p><p>For the demo purpose we have extracted sample datasets from Kaggle.com. Details of the dataset is as follows :</p><p>Departments, Products and Orders are the dataset used.</p><p><strong>Departments data</strong></p><blockquote>Storing to DBFS from batch processing.</blockquote><blockquote>Schema: department_id (int), department (str)</blockquote><p><strong>Products data</strong></p><blockquote>Storing to DBFS from batch processing.</blockquote><blockquote>Schema: product_id (int), product_name(str), Price(int), department_id(int)</blockquote><p><strong>Orders data</strong></p><blockquote>Storing to DBFS from structured streaming.</blockquote><blockquote>Schema: order_id(int), product_id(int), add_to_cart_order(int), reordered(int)</blockquote><p>. Moving on to the folder structure with Bronze, Silver, Gold layers are created as delta layers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/798/0*c0vGQY4gumGaXDbj" /></figure><p>. Raw data from the source is loaded into Bronze layer tables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/548/0*gM6Ydt8AEEFdFK0r" /></figure><ul><li>Silver layer tables are created with the combination of all datasets and also contain cleansed data</li><li>Gold layer tables houses the data in the final format needed to address functional requirements</li></ul><blockquote>For our demo what we tried to do is have product and department wise total orders from the raw data that is created.</blockquote><p>On the product table we have inserted data twice, with different order dates. It created two version of files. Delta inturn creates transaction logs for each load</p><p>Below is the snapshot of _delta_log_ folder which contain json files for each version.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*88ZqPa-ZBkSWp_o_" /></figure><p>Another important feature that a delta lake provides is the ability to traverse older data versions or in other words time travel. The snippet below shows the time travel functionality of the delta table.</p><p><strong>Version 1</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/623/0*aCpVxAo1ReBraTsy" /></figure><p><strong>Version 2</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/648/0*8Xps3lxq9oaJsGlS" /></figure><p><strong>Schema Evolution</strong></p><p>To address situations where columns change over a period of time, delta table have schema evolution feature. In RDBMS if there is changes to the schema of incoming data then the loading process would fail unless the table structure is changed prior to the load.</p><p>Below the example for schema evolution feature with simple dataset.</p><p>Here we are creating two dataset with different schema and storing into same location as Delta table.</p><p>df1 is created with the below schema.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/569/1*pbfdeft5B43V-VBSPA9r0g.png" /></figure><p>Below the result of df1</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/572/1*K4q22umLYnzcG3RgtwWRyA.png" /></figure><p>Saving df1 as Delta</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/845/1*POXRs2etyIJ5G8Rl_fJcAg.png" /></figure><p>Df2 created with different schema and number of columns are also different.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/686/1*0GUaUkLPxAm67mNyoQ5lAA.png" /></figure><p>Now lets append df2 to same path with option mergeSchema.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/753/1*IT5R3tFopCRaW-D-Xo8dCA.png" /></figure><p>As a final stage creating table on top of the location where we stored df1 and df2.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/897/1*7YdawsImCg5SSp5yYalSkQ.png" /></figure><p>When we run select query on top of the delta table, can see the schema evolved and captured both df1 and df2 data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*AetCU-np9RdzrI1x0B_vCQ.png" /></figure><p><strong>Delta Lake Index</strong></p><p>One of the critical auxiliary data elements in a RDBMS is indexes. Indexes are used for improving query performance and data access. Since Delta tables are going to function primarily as data warehouse entities , query performance is of importance. In Delta Lakes index are available at table level and stored as key/value metadata in JSON.</p><p>One of the index types provided in Delta tables is Bloom filter index</p><p>A Bloom filter index is a space-efficient data structure that enables data skipping on chosen columns, particularly for fields containing arbitrary text.</p><p>Configuration for index</p><p>SET spark.databricks.io.skipping.bloomFilter.enabled = true;</p><p>set delta.bloomFilter.enabled = true;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*4g8kPf7lv1kR-hWj5e9qJg.png" /></figure><p>It will create delta_index folder for delta indexing and part file save as parquet file as you can see in the screenshot below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/933/1*UlsvzTWcjAk5lcfRG2vSgA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/891/1*S_L0lnmNaBOWjcj8bAqdhw.png" /></figure><p>References</p><p>[1] <a href="https://towardsdatascience.com/delta-lake-with-spark-what-and-why-6d08bef7b963">https://towardsdatascience.com/delta-lake-with-spark-what-and-why-6d08bef7b963</a></p><p>[2] <a href="https://www.chaossearch.io/blog/why-data-lakes-fail">https://www.chaossearch.io/blog/why-data-lakes-fail</a></p><p>[3] <a href="https://www.cidrdb.org/cidr2021/papers/cidr2021_paper17.pdf">https://www.cidrdb.org/cidr2021/papers/cidr2021_paper17.pdf</a></p><p>[4] <a href="https://www.databricks.com/wp-content/uploads/2020/08/p975-armbrust.pdf">https://www.databricks.com/wp-content/uploads/2020/08/p975-armbrust.pdf</a></p><p>[5] <a href="https://www.netapp.com/data-storage/storagegrid/what-is-object-storage/">https://www.netapp.com/data-storage/storagegrid/what-is-object-storage/</a></p><p>[6] <a href="https://resources.useready.com/blog/s3-vs-hdfs-comparing-technologies-in-the-big-data-ecosystem/">https://resources.useready.com/blog/s3-vs-hdfs-comparing-technologies-in-the-big-data-ecosystem/</a></p><p>[7] <a href="https://www.firebolt.io/blog/the-real-meaning-of-a-data-lake">https://www.firebolt.io/blog/the-real-meaning-of-a-data-lake</a></p><p>[8] <a href="https://www.databricks.com/wp-content/uploads/2020/08/p975-armbrust.pdf">https://www.databricks.com/wp-content/uploads/2020/08/p975-armbrust.pdf</a></p><p>[9] <a href="https://www.databricks.com/blog/2020/09/10/diving-deep-into-the-inner-workings-of-the-lakehouse-and-delta-lake.html">https://www.databricks.com/blog/2020/09/10/diving-deep-into-the-inner-workings-of-the-lakehouse-and-delta-lake.html</a></p><p>[10] <a href="https://www.databricks.com/discover/data-lakes/introduction">https://www.databricks.com/discover/data-lakes/introduction</a></p><p>[11] <a href="https://dbdb.io/db/delta-lake#:~:text=Delta%20Lakes%20have%20indexes%20on,of%20the%20column%20and%20partition">https://dbdb.io/db/delta-lake#:~:text=Delta%20Lakes%20have%20indexes%20on,of%20the%20column%20and%20partition</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5e0a94187ce" width="1" height="1" alt=""><hr><p><a href="https://medium.com/litmus7/delta-lake-3rd-generation-data-analytics-platforms-for-enterprises-backdrop-5e0a94187ce">Delta Lake — 3rd generation data analytics platforms for enterprises  Backdrop</a> was originally published in <a href="https://medium.com/litmus7">Litmus7 Systems Consulting</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Quality — Data testing and documentation using Great Expectations’ Spark Engine]]></title>
            <link>https://medium.com/litmus7/data-quality-data-testing-and-documentation-using-great-expectations-spark-engine-3e616f07cc17?source=rss----d47509a7187---4</link>
            <guid isPermaLink="false">https://medium.com/p/3e616f07cc17</guid>
            <dc:creator><![CDATA[Vaisakh D]]></dc:creator>
            <pubDate>Tue, 27 Sep 2022 03:20:17 GMT</pubDate>
            <atom:updated>2022-10-06T16:29:57.512Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Data Quality — Data testing and documentation using Great Expectations’ Spark Engine</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*tjCVcf5D12LdTKXahzk_2w.jpeg" /></figure><p>In continuation with our earlier blog on Data quality and Great Expectations , <a href="https://medium.com/litmus7/data-quality-relevance-in-modern-day-data-engineering-pipelines-765c0ce4c4e5">https://medium.com/litmus7/data-quality-relevance-in-modern-day-data-engineering-pipelines-765c0ce4c4e5</a></p><p>In this blog we look at how Great Expectation can be used with a Spark environment , this becomes particularly handy when we have a huge dataset to evaluate and we have to depend on Pyspark for our analysis.</p><p>Like we mentioned in the earlier post within Great Expectations the term Expectations refers to assertions for data, covering all kinds of common data issues.</p><p>Great Expectations library has wide acceptance to perform data quality check and with the Spark integration now we can execute Expectations natively within pandas DataFrames AND SQL AND Spark DataFrames. As we know Apache spark is a lighting fast cluster computing designed for fast processing. When we integrate Spark along with Great Expectations, it has several benefits as outline by them [1]</p><p>· Spark Expectations run considerably faster than they would have with redundant queries.</p><p>· Some Spark Expectations also run considerably faster for pandas and SQLAlchemy.</p><p>· Even less boilerplate required for adding new Expectations. Expectation argument validation logic is more centralized than in the past.</p><p>For doing a quick POC we have used Databricks Spark cluster and integrated Great Expectations</p><p>Let’s take an example for great expectation with spark using a data bricks community edition subscription.</p><p>To Install great expectations library in data bricks cluster using</p><pre>dbutils.library.installPyPI(&quot;great_expectations&quot;)</pre><p>Then using the following code snippet, we can create a spark data frame from the data uploaded in the dbfs. We are using a sample supermarket sales data to perform expectations library.</p><pre>import great_expectations as ge</pre><pre>sales_df = park.read.option(&quot;header&quot;,True).csv(&quot;dbfs:/FileStore/tables/supermarket_sales.csv&quot;)</pre><p>SparkDFDataset inherits the PySpark DataFrame and allows you to validate expectations against it.</p><pre>from great_expectations.dataset import SparkDFDataset</pre><pre>sales_ge = ge.dataset.SparkDFDataset(sales_df)</pre><p>We can use the following function to check the column list of the data as expected in the given list.</p><pre>sales_ge.expect_table_columns_to_match_ordered_list (column_list=[&#39;Invoice ID&#39;, &#39;Branch&#39;, &#39;City&#39;, &#39;Customer type&#39;, &#39;Gender&#39;, &#39;Product line&#39;, &#39;Unit price&#39;, &#39;Quantity&#39;, &#39;Tax 5%&#39;, &#39;Total&#39;, &#39;Date&#39;, &#39;Time&#39;, &#39;Payment&#39;, &#39;cogs&#39;, &#39;gross margin percentage&#39;, &#39;gross income&#39;, &#39;Rating&#39;])</pre><p>And we are getting the result as below.</p><pre>Out[7]: { &quot;result&quot;: { &quot;observed_value&quot;: [ &quot;Invoice ID&quot;, &quot;Branch&quot;, &quot;City&quot;, &quot;Customer type&quot;, &quot;Gender&quot;, &quot;Product line&quot;, &quot;Unit price&quot;, &quot;Quantity&quot;, &quot;Tax 5%&quot;, &quot;Total&quot;, &quot;Date&quot;, &quot;Time&quot;, &quot;Payment&quot;, &quot;cogs&quot;, &quot;gross margin percentage&quot;, &quot;gross income&quot;, &quot;Rating&quot; ] }, &quot;exception_info&quot;: { &quot;raised_exception&quot;: false, &quot;exception_traceback&quot;: null, &quot;exception_message&quot;: null }, &quot;meta&quot;: {}, &quot;success&quot;: true }</pre><p>Inorder to execute the expectations function for null check , it would be executed as spark jobs and gives the following results.</p><p><strong>sales_ge.expect_column_values_to_not_be_null</strong>(column=’Invoice ID’,result_format=”COMPLETE”)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Fqr4ojrJFK4HP1LXMVrcgw.png" /></figure><p>Great expectations results can be seen sparkDFdataset as well similar to Pandas.</p><p>Using the below code snippet, we can write the result as HTML format like normal Great Expectations Result.</p><pre>from great_expectations.profile.basic_dataset_profiler import BasicDatasetProfiler</pre><pre>from great_expectations.render.renderer import *</pre><pre>from great_expectations.render.view import DefaultJinjaPageView</pre><pre>validation_result = sales_ge.validate()</pre><pre>document_model = ValidationResultsPageRenderer().render(validation_result)</pre><pre>displayHTML(DefaultJinjaPageView().render(document_model))</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HQNFbr6lUc3CGgBlurKOYg.png" /></figure><p>Great expectations with Spark support most of functions which normal great expectation library supports.</p><p>Following are the main functions which supports in Spark.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/642/1*mmtYd3CNau8hZ2aG2WaGgw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/1*03bDIkPheW3L1zkePN2pAA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/641/1*5wLsM4ow1pLXH7UfGEFHyQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/641/1*pFok17NcvmnFhddn_wBKoA.png" /></figure><p>References<strong>:</strong></p><p>(1) <a href="https://greatexpectations.io/blog/great-expectations-now-supports-executions-in-spark-a-blog-with-much-clapping/">https://greatexpectations.io/blog/great-expectations-now-supports-executions-in-spark-a-blog-with-much-clapping/</a></p><p>(2) <a href="https://towardsdatascience.com/data-quality-unit-tests-in-pyspark-using-great-expectations-e2e2c0a2c102">https://towardsdatascience.com/data-quality-unit-tests-in-pyspark-using-great-expectations-e2e2c0a2c102</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3e616f07cc17" width="1" height="1" alt=""><hr><p><a href="https://medium.com/litmus7/data-quality-data-testing-and-documentation-using-great-expectations-spark-engine-3e616f07cc17">Data Quality — Data testing and documentation using Great Expectations’ Spark Engine</a> was originally published in <a href="https://medium.com/litmus7">Litmus7 Systems Consulting</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data quality -Relevance in modern day data engineering pipelines]]></title>
            <link>https://medium.com/litmus7/data-quality-relevance-in-modern-day-data-engineering-pipelines-765c0ce4c4e5?source=rss----d47509a7187---4</link>
            <guid isPermaLink="false">https://medium.com/p/765c0ce4c4e5</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[great-expectations]]></category>
            <category><![CDATA[data-quality]]></category>
            <dc:creator><![CDATA[Iswarya K]]></dc:creator>
            <pubDate>Thu, 14 Jul 2022 10:49:09 GMT</pubDate>
            <atom:updated>2022-10-06T16:31:32.567Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*uVzGQtbMhcPDHE3UJAs7qw.jpeg" /></figure><p>“<em>Data is the new oil. It’s valuable, but if unrefined it cannot really be used. It has to be changed into gas, plastic, chemicals, etc. to create a valuable entity that drives profitable activity; so data must be broken down, analyzed for it to have value.”</em> –Clive Humby, 2006</p><p>Often times we have seen that data contains inaccurate information, missing information, is out of bounds or doesn’t match the expected semantics, doesn’t represent the population that it is intended to represent [1]. However, having bad data in our systems becomes quite detrimental as this is used as the “raw ingredient” for all the analytics, machine learning and AI based decisioning systems that enterprises use. Due to this, the emphasis on data quality is more important now than ever before. Data quality should ideally be the core component of Data Ops or any data engineering pipeline.</p><p>Gartner predicts that by 2022, <strong>70% </strong>of organizations will rigorously track data quality levels via metrics, improving it by <strong>60%</strong> to significantly reduce operational risks and costs. As per Gartner’s 2020 Magic Quadrant for Data Quality Solutions, poor data quality costs organizations an <strong>average of $12.8 million every year</strong> [2]</p><p><strong>How do we measure data quality ?</strong></p><p>So what exactly is quality data, how do we assess whether the data that we have is of good quality. At a very high level data is of good quality if the consumers of it can carry out their activities and achieve their intended goals with satisfaction and ease; also better the quality and better the value that can be driven from the data. However, rather than the consumers identifying data issues, how do we, as data engineers and data architects assess data quality. For this there are 5 main criteria that can be used to measure data quality [3][4]</p><ul><li><strong>Accuracy</strong> : Is the data correct, does it represent real world scenarios?</li><li><strong>Relevancy</strong> : Do we need this information and meet requirements?</li><li><strong>Completeness</strong> : Is the data comprehensive and cover all necessary details?</li><li><strong>Timeliness</strong> : How up to date the information is?</li><li><strong>Consistency </strong>: Is the data cross referenceable?</li></ul><p>In line with the “12 Actions to Improve DQ” by Gartner [2], these are some of the steps that a data engineering team can adopt to measure, alert and action on data quality early in the pipeline.</p><p><strong>Data profiling and control of incoming data</strong></p><ul><li>Data format and data patterns</li><li>Data consistency</li><li>Data value distributions and anomalies</li><li>Completeness of the data</li></ul><p><strong>Careful data pipeline design to avoid duplicate data</strong></p><p><strong>Enforcement of Data integrity</strong></p><p><strong>Integration of data lineage into the data pipeline</strong></p><p>With this premise we come to the crux of the blog. We were looking at some of the new open source data quality products and packages available and couple of the ones of interest were <strong>Great Expectations</strong> and <strong>Deequ</strong>. There are others like <strong>Flyte </strong>and <strong>Pandera</strong>.</p><p>We have tried out two of these — <strong>Great Expectations</strong> with P<strong>ython and PySpark</strong> and <strong>Deequ</strong> in P<strong>ySpark </strong>on some Kaggle datasets (infused with some incorrect and inaccurate data). In this blog we will cover Great Expectations and how to use it in a Python-based pipeline.</p><p><strong>What is Great Expectations?</strong></p><p>Great Expectations is a data quality tool and validation tool for automated data profiling and identifying issues with the data with respect to certain “expectations” or constraints that we set. Great expectations also helps with regular data documentation and generally it is not updated regularly by many teams which makes the data documentation outdated and this is solved by Great Expectation. It also has various functions for data validation.</p><p>Great Expectations can be used both with Python and PySpark but it works great with Python.</p><p><strong>Integrations</strong></p><p>Supported Data sources — Pandas, Spark data frames,<strong> </strong>SQL databases via SQLAlchemy.</p><p>It allows us to store the relevant metadata related to expected data type of the column, expectations i.e. the rules we want to execute on each of the columns, validation results in File systems, database backends, as well as cloud storage such as S3 and Google Cloud Storage, by configuring metadata stores.</p><p><strong>Key features:</strong></p><p><strong>Expectations -</strong>These are rules or constraints that we want to define about our data. In the backend, these assertions are declared in an expression language which are simple python methods.</p><p><strong>Automated data profiling</strong> — Great Expectations makes it easier to write pipeline tests by providing automated data profiling. This library profiles the data to obtain basic statistics about data and automatically generates an expectation suite based on the observed data.</p><p><strong>Data validation</strong> — Once the expectations are created, data can be loaded in a batch or several batches to validate the expectation suite. Great Expectations produces output which tells whether each expectation in the expectation suite has passed or failed and returns unexpected values which resulted in test failure. This feature helps in debugging data issues.</p><p><strong>Data docs </strong>— Great Expectations delivers Expectations in clean, human-readable documentation which is called as Data Docs. These HTML docs contain both expectation suites and the data validation results.</p><p>Great expectations can be installed by using <strong>pip install great_expectations</strong></p><p>To import the library use</p><pre><strong>import great_expectations as ge</strong></pre><p>Let’s create a data frame using the Supermarket Sales data</p><pre>import pandas as pd</pre><pre>sales_ge=ge.read_csv(&quot;supermarket_sales_1.csv&quot;)</pre><pre>sales_ge.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/725/1*DTUJVq3bZzBA1Igla1p9uw.png" /></figure><p>If we want to check whether the source columns are as expected we can define the expected number of columns and column name in the Meta data and then write a rule in Great Expectations as below :</p><pre>sales_ge.expect_table_columns_to_match_ordered_list(column_list=[&#39;Invoice ID&#39;, &#39;Branch&#39;, &#39;City&#39;, &#39;Customer type&#39;, &#39;Gender&#39;, &#39;Product line&#39;, &#39;Unit price&#39;, &#39;Quantity&#39;, &#39;Tax 5%&#39;, &#39;Total&#39;, &#39;Date&#39;, &#39;Time&#39;, &#39;Payment&#39;, &#39;cogs&#39;, &#39;gross margin percentage&#39;, &#39;gross income&#39;, &#39;Rating&#39;])</pre><p>Output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/335/1*ZY-Z4TBWeQd5rqZar4I8bg.png" /></figure><p>The function of Great Expectations returns an output in JSON format. In this case we should look for the success, whether it is true or false. If it’s false we need to look for the reasons.</p><p>We can do a record count check . Consider, we expect the row count to be between 1 and 1000. Let’s check the data frame that matches our expectations</p><pre>sales_ge.expect_table_row_count_to_be_between(min_value=1,max_value=1000)</pre><p>Output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/320/1*HB2mANyiQf7swz0LMtpfDw.png" /></figure><p>Here we have received 1006 records in the source where as we have given range to be 1 to 1000. Hence you see in the output “Success” is false and the observed row count is 1006 and it is greater than the maximum value.</p><p>We can do unique check. In our data set let us assume the <strong>“Invoice ID” </strong>column should always be unique and duplicate values might create problems. We can check the uniqueness of the column by</p><pre>sales_ge.expect_column_values_to_be_unique(column=&#39;Invoice ID&#39;,result_format=&quot;BOOLEAN_ONLY&quot;)</pre><p>Output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/282/1*df0UQz6tj5e_M9V-vFyjcA.png" /></figure><p>Here we have infused the dataset with some duplicate records. Hence you can see that “Success” is false.</p><p>The output result that we see above is a default view which just lets us know if the test or the expectation that we set has succeeded or not. However if we want to know further details then</p><p>Great Expectations supports four values for <strong>result_format</strong>:</p><ul><li>BOOLEAN_ONLY</li><li>BASIC</li><li>SUMMARY</li><li>COMPLETE</li></ul><p>Each successive value includes more detail and so can support different use cases for working with Great Expectations, including interactive exploratory work and automatic validation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S-4H6OwQky5FYfhhDW9MGw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/622/1*cmQ8CRd91yXsI4J68Ik5rw.png" /></figure><p>To get the detailed information in the output, we can change the value of <strong>result_format=’’COMPLETE” .</strong>Result is generated with all available justification for why an expectation was met or not.</p><pre>sales_ge.expect_column_values_to_be_unique(column=&#39;Invoice ID&#39;,result_format=&quot;COMPLETE&quot;)</pre><p>Note: Output is not displayed as its lengthy.</p><p>Most of the commonly occurring checks in a data engineering pipeline are available within GE , some of them are as below :</p><p>(1) We can also check if the Invoice ID column matches the given regular expression or not</p><pre>sales_ge.expect_column_values_to_match_regex(column=&#39;Invoice ID&#39;, regex=&#39;[0-9]{3}-[0-9]{2}-[0-9]{4}&#39;,result_format=&quot;COMPLETE&quot;)</pre><p>From the partial unexpected list, we can identify the invoice ids which does not follow the pattern.</p><p>(2) We can check if the values in a categorical column are in a given value set</p><pre>sales_ge.expect_column_values_to_be_in_set(column=&#39;Branch&#39;, value_set=[&#39;A&#39;, &#39;B&#39;, &#39;C&#39;],result_format=&quot;SUMMARY&quot;)</pre><p>(3) Missing values can reduce the statistical power of the study and it can lead to improper decisions. We can check the missing values for any columns using the function available in great expectations.</p><pre>sales_ge.expect_column_values_to_not_be_null(column=&#39;Customer type&#39;)</pre><p>(4) We can also check the column entries to be strings with a length between the minimum and maximum value.</p><pre>sales_ge.expect_column_value_lengths_to_be_between(column=&quot;Customer type&quot;,max_value=10,result_format=&quot;COMPLETE&quot;)</pre><p>Great expectation is a handy tool when it comes to analysing and profiling the data and we have covered some basics into how to leverage it within a python based pipeline. In our next blog we will cover how Great Expectation can be used in a Spark environment. Spark being increasingly the choice of technology in modern day data engineering and machine learning pipelines. To explore more on various functions of Great Expectations please look into</p><p><a href="https://legacy.docs.greatexpectations.io/en/latest/reference/glossary_of_expectations.html#expectation-glossary">Glossary of Expectations - great_expectations documentation</a></p><p><strong>References:</strong></p><p>(1) <a href="https://towardsdatascience.com/is-data-really-the-new-oil-in-the-21st-century-17d014811b88#:~:text=%E2%80%9CData%20is%20the%20new%20oil,Clive%20Humby%2C%202006">https://towardsdatascience.com/is-data-really-the-new-oil-in-the-21st-century-17d014811b88#:~:text=%E2%80%9CData%20is%20the%20new%20oil,Clive%20Humby%2C%202006</a></p><p>(2) <a href="https://www.gartner.com/smarterwithgartner/how-to-improve-your-data-quality">https://www.gartner.com/smarterwithgartner/how-to-improve-your-data-quality</a></p><p>(3) <a href="https://towardsdatascience.com/7-steps-to-ensure-and-sustain-data-quality-3c0040591366">https://towardsdatascience.com/7-steps-to-ensure-and-sustain-data-quality-3c0040591366</a></p><p>(4) <a href="https://www.precisely.com/blog/data-quality/5-characteristics-of-data-quality">https://www.precisely.com/blog/data-quality/5-characteristics-of-data-quality</a></p><p>(5) <a href="https://www.gartner.com/smarterwithgartner/how-to-improve-your-data-quality">https://www.gartner.com/smarterwithgartner/how-to-improve-your-data-quality</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=765c0ce4c4e5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/litmus7/data-quality-relevance-in-modern-day-data-engineering-pipelines-765c0ce4c4e5">Data quality -Relevance in modern day data engineering pipelines</a> was originally published in <a href="https://medium.com/litmus7">Litmus7 Systems Consulting</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Online grocery — is there light at the end of the tunnel?]]></title>
            <link>https://medium.com/litmus7/online-grocery-is-there-light-at-the-end-of-the-tunnel-3f52b80620ef?source=rss----d47509a7187---4</link>
            <guid isPermaLink="false">https://medium.com/p/3f52b80620ef</guid>
            <category><![CDATA[grocery-home-delivery]]></category>
            <category><![CDATA[grocery-shopping]]></category>
            <category><![CDATA[retail]]></category>
            <category><![CDATA[grocery-delivery]]></category>
            <category><![CDATA[startup]]></category>
            <dc:creator><![CDATA[somak roy]]></dc:creator>
            <pubDate>Mon, 09 Sep 2019 08:28:16 GMT</pubDate>
            <atom:updated>2019-09-12T13:06:43.516Z</atom:updated>
            <content:encoded><![CDATA[<h3>Online grocery — is there light at the end of the tunnel?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Rc5yysDg9FGUGJNwwygh9Q.jpeg" /></figure><p>It has been a quarter century since the dawn of ecommerce, and online grocery is still — to put it mildly — an unsolved problem. Estimates on penetration in the US vary between 3 and 5%. The number, even at the upper end of the range, is an order of magnitude less than that in fashion or consumer electronics. It has not been for want of trying. Those with long memories will recall Louis H. Border’s Webvan experiment in 1997. And Webvan was an experiment that happened under the aegis of the luminaries of epoch making technology shifts, <a href="https://knowledge.wharton.upenn.edu/article/what-webvan-could-have-learned-from-tesco/">Sequoia and Softbank</a>. It didn’t work. Things have gotten better, certainly; but still, online grocery is not accretive to margins for most omnichannel retailers. It is a defensive strategy at best.</p><p>What ails grocery ecommerce?</p><p>Low online penetration of grocery is due to a single inalienable truth: In a business already challenged by low single digit margins, digital replaces two free line items in the cost structure by paid equivalents. These two line items are picking and delivery. Picking costs are 5 to 10% of sales, and delivery costs are <a href="https://www.mckinsey.com/industries/retail/our-insights/reviving-grocery-retail-six-imperatives">10–12% of sales</a>.</p><p>Picking is a paid activity in both BOPIS and home delivery scenarios. Also, grocery is a high purchase frequency category and therefore the number of picking trips per unit dollar of goods sold is high. One might think automated picking is the answer. But distribution center-based fulfillment does not really work in low population density markets like the US, because of the need to be closer to the customer. Delivery from a multi-purpose, omnichannel store, and not a warehouse, precludes picking with a high degree of automation.</p><p>In addition to costs, store-based delivery is beset with all kinds of problems. You need up to the minute inventory data, a nightmare of technology, process, and people nobody has solved yet. The state of the art thus far — in Litmus7’s experience — is 12 minutes. The lag impedes the ability to present inventory information with any degree of confidence to the online buyer. Due to the problem of up to the minute inventory, there are often errors in the order, and substitutions.</p><p>What’s more — store-based delivery hurts in-store experience. It has been said when fulfillment of online delivery becomes 10% of the store’s operations, shopping becomes a drag for the store’s primary constituency — customers physically shopping. “Over-congestion” has become a real problem in Europe.</p><p>One solution often proposed is that of carving out a 8,000–10,000 square feet micro-fulfillment center out an existing store. This capacity serves, and only serves, the online customer. Picking is automated, enabled by one of the robotics companies that have emerged in the last several years — the likes of Ocado and Commonsense Robotics. Does the idea stand to scrutiny? It isn’t clear. First, experts are divided on whether automated picking can happen to any meaningful degree without a square footage of at least 25,000. Second, the actual efficacy of picking automation and its impact on number is apparently still up for debate. Two sets of data points need to be summoned — a comparison of Ocado’s number’s versus manual picking numbers, and second, Ocado’s margins.</p><p>In 2016, the pick rate at an Ocado facility measured in units per labor hour was exactly the same as a manual picking rate at Albert Heijin, a Dutch supermarket chain. And, the Ocado facility cost GBP 200 million for GBP 1 billion (annual sales capacity) and the Albert Heijin facility cost EURO 10 million for <a href="https://www.brickmeetsclick.com/managing-online-grocery-profitability--what-we-can-learn-from-europe">EURO 180 million</a> (annual sales capacity). So the benefits in accuracy and operational cost savings would most likely be offset by an order of magnitude increase in CAPEX. And there would be no significant improvement in order processing rate. And this after 16 years in operation.</p><p>Coming to Ocado’s margins, EBITDA was 5.55% in 2018, which isn’t much of an improvement on the typical offline grocery margins of 4 to 8%. However, the number is much better than the typical online grocery margin of <a href="https://www.mckinsey.com/industries/retail/our-insights/reviving-grocery-retail-six-imperatives">0.5 to 1.5%</a>. Of course, few incumbent retailers can invest the way Ocado can, which is valued more like a tech company.</p><p>That was just picking. The other big impediments to online grocery is delivery costs, currently addressed by crowdsourcing. Instacart, Shipt (now a part of Target), and Doordash solve the labor cost problem arguably, and allegedly, through questionable means. Replacing full-time workers with benefits by gig workers pushes effective hourly payouts to close to minimum wage. A recent study found that the average Instacart worker is paid, after insurance and mileage, (which the “independent contractors” pay out of pocket) a mere <a href="https://payup.wtf/instacart/delivering-inequality">US$7.66 an hour</a>. This will become more problematic as retail wages rise. Amazon hiked its minimum wage to US$15 in 2018, which was followed by a similar move by Walmart. The din is getting louder on the idea that the gig economy is fundamentally exploitative, and legislation could put at risk the full time employee-crowdsourced partner arbitrage. In California, a law that came into effect in 2018 mandated cannabis delivery workers to be recognized as full-time employees and <a href="https://www.theatlantic.com/technology/archive/2018/09/gig-economy-independent-contractors/570307/">not contractors</a>.</p><p>The two levers on delivery costs are the ability to charge a delivery fee (and/or a subscription) and increase drop density — which follows from a small set of delivery slots. Expectations on delivery costs continue to hunkered down by Amazon. Most retailers charge an annual fee in the range of $90 to $120 dollars and offer free delivery for value exceeding $30 to $60. However, it’s also true that most make losses through this model. About drop density, a Dutch grocery delivery company called Picnic offers only a couple of delivery slots a week, which has raised drop density to 14, 7 times the UK average, and makes possible free delivery above order value of <a href="https://medium.com/@cberta/cracking-the-toughest-nut-in-ecommerce-grocery-retail-6cdb3f663d6a">EURO 25</a>.</p><p>Due to the range of problems described above, online grocery buyers currently accept a standard of customer experience that is lower than that in other categories. The situation has led to a kind of truce — a cessation of hostilities, and an equilibrium among retailers. However, this is a ceasefire that can be broken any minute, and an unstable equilibrium at best. Any retailer — possibly Amazon — that builds a critical mass of highly automated points of presence across the US, would force everybody to shape up.</p><p>The challenges are numerous, but the prize for solving them is commensurately high. Your grocery customer is likely to stick with you during your offline to online transformation — in a survey study by Bain &amp; Company, <a href="https://www.bain.com/insights/omnichannel-grocery-is-open-for-business-and-ready-to-grow/">85% of customers</a> said if they were to buy groceries online, they would choose their current retailer.</p><p>There are indeed a few more levers to pull up margins at least a few basis points higher. The silver lining in grocery ecommerce is basket size, which at just over US$90, is 26% higher than that of curbside pickup, and a whopping <a href="https://www.skulocal.com/insights/how-online-grocery-has-impacted-basket-size-and-frequency-among-shoppers/">64% higher than</a> the brick and mortar grocery basket. This gives the online grocery some room to nudge the buyer to a few higher margin items — with better data, better data science, and better recommendation engines. A successful nudge could spell the difference between positive unit economics and negative unit economics.</p><p>Another, contentious lever is dynamic pricing. While no grocer would want to come across as unfair or profiteering, and most wouldn’t want to come across as expensive — dynamic pricing has gathered enough momentum to warrant serious evaluation. The perception problem can be managed, by careful benchmarking of prices on key value items (whose prices are well known among customers). There are now published cases of dynamic pricing increasing <a href="https://www.mckinsey.com/industries/retail/our-insights/how-retailers-can-drive-profitable-growth-through-dynamic-pricing">margins by over 4%</a>.</p><p>Despite all of the above, online grocery might remain a margin dilutive, even cannibalising channel. For many, it would be a defensive strategy, pursued while keeping an eye on the technology because things could change rapidly with a disruptive move by Amazon. It’s best to continue doing whatever it takes to draw crowds to the store, and keep experimenting with automation, various fulfillment formats, pricing, and delivery strategies.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3f52b80620ef" width="1" height="1" alt=""><hr><p><a href="https://medium.com/litmus7/online-grocery-is-there-light-at-the-end-of-the-tunnel-3f52b80620ef">Online grocery — is there light at the end of the tunnel?</a> was originally published in <a href="https://medium.com/litmus7">Litmus7 Systems Consulting</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>