azure

Airbyte: The ultimate workhorse for all your ELT pipelines
Ateeq ur Rehman
| January 27, 2023

Data Science Dojo is offering Airbyte for FREE on Azure Marketplace packaged with a pre-configured web environment enabling you to quickly start the ELT process rather than spending time setting up the environment. 

 

What is an ELT pipeline?  

An ELT pipeline is a data pipeline that extracts (E) data from a source, loads (L) the data into a destination, and then transforms (T) data after it has been stored in the destination. The ELT process that is executed by an ELT pipeline is often used by the modern data stack to move data from across the enterprise into analytics systems.  

 

ELT process
ELT process

 

In other words, in the ELT approach, the transformation (T) of the data is done at the destination after the data has been loaded. The raw data that contains the data from a source record is stored in the destination as a JSON blob. 

 

Airbyte’s architecture: 

Airbyte is conceptually composed of two parts: platform and connectors. 

The platform provides all the horizontal services required to configure and run data movement operations, for example, the UI, configuration API, job scheduling, logging, alerting, etc., and is structured as a set of microservices. 

Connectors are independent modules that push/pull data to/from sources and destinations. Connectors are built under the Airbyte specification, which describes the interface with which data can be moved between a source and a destination using Airbyte. Connectors are packaged as Docker images, which allows total flexibility over the technologies used to implement them. 

 

Obstacles for data engineers & developers  

Collection and maintenance of data from different sources is itself a hectic task for data engineers and developers. Building a custom ELT pipeline for all of the data sources is a nightmare on top that not only consumes a lot of time for the engineers but also costs a lot. 

In this scenario, a unified environment to deal with the quick data ingestions from various sources to various destinations would be great to tackle the mentioned challenges.  

 

Methodology of Airbyte 

 Airbyte leverages DBT (data build tool) to manage and create SQL code that is used for transforming raw data in the destination. This step is sometimes referred to as normalization. An abstracted view of the data processing flow is given in the following figure: 

Airbyte methodology
Airbyte methodology

 

It is worth noting that the above illustration displays a core tenet of ELT philosophy, which is that data should be untouched as it moves through the extracting and loading stages so that the raw data is always available at the destination. Since an unmodified version of the data exists in the destination, it can be re-transformed in the future without the need for a resync of data from source systems. 

 

Major features

Airbyte supports hundreds of data sources and destinations including:  

  • Apache Kafka  
  • Azure Event Hub  
  • Paste Data  
  • Other custom sources  

By specifying credentials and adding extensions you can also ingest from and dump to:  

  • Azure Data Lake  
  • Google Cloud Storage  
  • Amazon S3 & Kinesis  

 

Other major features that Airbyte offers: 

  • High extensibility: Use existing connectors to your needs or build a new one with ease. 
  • Customization: Entirely customizable, starting with raw data or from some suggestion of normalized data. 
  • Full-grade scheduler: Automate your replications with the frequency you need. 
  • Real-time monitoring: Logs all the errors in full detail to help you understand better. 
  • Incremental updates: Automated replications are based on incremental updates to reduce your data transfer costs. 
  • Manual full refresh: Re-syncs all your data to start again whenever you want. 
  • Debugging: Debug and Modify pipelines as you see fit, without waiting. 

 

 

What does Data Science Dojo provide?   

Airbyte instance packaged by Data Science Dojo serves as a pre-configured ELT pipeline that makes data integration pipelines a commodity without the burden of installation. It offers efficient data migration and supports a variety of data sources and destinations to ingest and dump data.  

Features included in this offer:   

  • Airbyte service that is easily accessible from the web and has a rich user interface. 
  • Easy to operate and user-friendly. 
  • Strong community support due to the open-source platform. 
  • Free to use. 

 

Conclusion  

There are a ton of small services that aren’t supported on traditional data pipeline platforms. If you can’t import all your data, you may only have a partial picture of your business. Airbyte solves this problem through custom connectors that you can build for any platform and make them run quickly. 

Install the Airbyte offer now from the Azure Marketplace by Data Science Dojo, your ideal companion in your journey to learn data science! 

Click on the button below to head over to the Azure Marketplace and deploy Airbyte for FREE by clicking below:

CTA - Try now 

Apache Airflow: Monitor and manage the data pipelines and complex workflows
Ali Mohsin
| December 3, 2022

Data Science Dojo is offering Apache Airflow for FREE on Azure Marketplace packaged with a pre-configured web environment of Airflow with various data analytics features.  

  

Introduction:  

In this era of tighter data restrictions, it is more important than ever to understand, analyze, and manage your data throughout its lifecycle. It is harder than ever as data volumes rise, and data pipelines get more complicated. A solution is needed Organizations or Individuals must have a complete, scalable, easy-to-analyze platform to manage and monitor the complex workflows and support several integrations. 

 

What is Apache Airflow?  

Apache Airflow, a powerful open-source tool for authoring, scheduling, and monitoring data and computational workflows. It provides a method that makes it easier to manage, schedule, and coordinate complicated data pipelines from several sources. 

 

What is DAG? 

A DAG, or Directed Acyclic Graph, in Airflow is a list of all the jobs you wish to execute, arranged to reflect their connections and dependencies. A Python script that expresses the DAG’s structure as code defines a DAG. Researchers’ priori ideas about the connections between and among variables in causal structures are encoded using DAGs. It contains directed edges (arrows), linking nodes (variables), and their paths. Hence A workflow is represented as a DAG, which consists of discrete units of work called Tasks that are ordered considering relationships and data flows. 

 

Apache Airflow Architecture: 

This powerful and scalable workflow scheduling software is made up of four key parts: 

  • Scheduler: The scheduler keeps track of all DAGs and the jobs they are connected to. To start, it frequently checks the list of open tasks. 
  • Web server: The user interface for Airflow is the web server (The default port Apache Airflow listens to is 8080). It displays the status of the jobs, gives the user access to the databases, and lets them read log files from other remote file stores like Microsoft Azure blobs. 
  • Database: To make sure the schedule retains metadata information, the state of the DAGs and the tasks they are connected to, are saved in the database. The scheduler scans each DAG and records essential data, including schedule intervals, run-by-run statistics, and task instances. 
  • Executors: There are various kinds of executors for different use cases. Few examples of Executors are  SequentialExecutor, LocalExecutor, CeleryExecutor, and KubernetesExecutor 

  

(With SequentialExecutor, just one task may be carried out at once. No parallel processing is possible. It is useful when testing or debugging. LocalExecutor supports hyperthreading and parallelism. It is excellent for using Airflow on a single node or a local workstation. CeleryExecutor is usually used for managing a distributed Airflow cluster. While using the Kubernetes API, the KubernetesExecutor creates temporary pods for each of the task instances to run in.) 

 

Key features Apache Airflow provides: 

  • Dynamic Pipelines can be constructed by Airflow dynamic, also as it is constructed in the form of code which gives an edge to dynamic behavior. 
  • Apache Airflow has a rich User Interface that helps the user to manage their workflow easily 
  • It gives a separate code view pallet that enables users to view their DAGs code as well.  
  • Allows users to visualize their DAGs in different forms like Gantt chart, Tree, and Graph. 
  • With ready to use operators in airflow, users can work with various cloud platforms like Microsoft Azure, AWS (Amazon Web Services) etc. 
  • Allows role-based user management to maintain Security and Accessibility.

 

Apache Airflow with Azure services: 

Apache Airflow leverages the power of Azure services to make the procedure of monitoring and managing complex workflows intuitively. Also with Azure, Airflow made it a more scalable data warehousing platform. Airflow enables users to work in a scalable environment. 

 

Conclusion:  

Other open-source Data Engineering solutions put intense competition on Apache Airflow. But it is one of the most robust platforms used by Data Engineers for orchestrating workflows or pipelines. Users can easily visualize your data pipelines’ dependencies, progress, logs, code, trigger tasks, and success status all in a single package.  

At Data Science Dojo, we deliver data science education, consulting, and technical services to increase the power of data. We therefore know the importance of data and the encapsulated insights. Through this offer, we are confident that you can analyze, visualize, and query your data in a collaborative environment with greater ease. 

Install the Apache Airflow offer now from the Azure Marketplace by Data Science Dojo, your ideal companion in your journey to learn data science! 

Click on the button below to head over to the Azure Marketplace and deploy Apache Airflow for FREE by clicking on “Try now.”  

 

CTA - Try now

 

Note: You’ll have to sign up to Azure, for free, if you do not have an existing account. 

Saad Shaikh
| October 6, 2022

Data Science Dojo is offering SnowSQL for FREE on Azure Marketplace packaged with pre-configured CLI for data manipulation at Snowflake warehouse 

 

What is Snowflake? 

Snowflake is a cloud computing-based data cloud platform. It is a user-friendly data warehousing product that supports both ETL and ELT functionalities. It supports multiple data workloads from data warehouses and data lakes to enable data storage, data engineering, processing, and analytics. Further, using Snowflake can help you with better data warehouse jobs in the near future.

It is relatively new, flexible, easier and provides a pure cloud SQL based warehouse. It is not created upon any database tech and has a high affordability rate. 

Challenges for developers 

Execution issues while attempting to load and query information, shortcomings in dealing with a variety of data, absence of central source causing conflicting corrupt data, and unfortunate data sharing were a few big obstacles encountered by data engineers and developers to look forward to. 

Therefore, a data cloud warehouse capable of storing variable-length records at vast scale maybe having some fault tolerance or availability or fast processing can reduce the task overhead by a big margin. Not to forget, the data able to be transformed using any standard open-source language would suffice. 

Snowflake: SnowSQL 

Using data engineers’ one of the favorite languages, SQL, developers can now load, transform and unload data at their Snowflake cloud. SnowSQL is an interactive command line scripting-cum-query tool that allows users to perform DML and DDL operations at the CLI level. It is produced with increased protection standards and has strong integration with Snowflake core architecture.

With this tool, you can connect to your Snowflake account and configure your databases, schemas, and warehouses using simple SQL. Users can import existing databases of the data cloud into local machines and then perform various transform operations. It also allows you to unload or dump your transformed data back into the database containers. 

Snowflake architecture - SnowSQL
Snowflake architecture – Data Science Dojo

What we provide 

Snowflake: SnowSQL packaged by Data Science Dojo serves as a pre-installed CLI environment for the Snowflake data cloud so it provides an ease to the developers to perform SQL operations on the data from the warehouse without the burden of installation. Offer listed by Data Science Dojo, provides the following elements: 

  • A Command Line Interface (CLI) installed with SnowSQL which can be connected to your Snowflake account 
  • Support for standard SQL 
  • Robust integrations 
  • Ability load and unload data having CSV, XML, JSON, Avro etc formats 
  • Includes syntax highlighting and auto-complete 

Significant characteristics of SnowSQL 

  • Centralization and Democratization: Snowflake combines warehouses and data lakes into a focal data storage and democratizes it to enable clients for better processing and analytics 
  • Smart Data Handling: Snowflake is able to manage exponential volumes, variety, and velocity of data. Using SnowSQL we can perform ETL and ELT operations using ANSI SQL 
  • Secure and Fault Tolerant: SnowSQL secures connections to Snowflake using TLS with OCSP checks. Your data is available even if one or more nodes go down as source is fault-tolerant 
  • Recovery and Time Travel: The undrop table command is unique as it restores the table back again. The time travel feature allows the recovery of the original version of an object to reverse the updates 
  • High Elastic Performance: With SnowSQL you create multiple virtual warehouses of variable sizes which ensures your ETL process go smoothly 

Conclusion 

SnowSQL leverages the power of Azure services and Snowflake Cloud to load and process large volumes of data with continuous availability, high scalability, and data distribution from the comfort of CLI. In this way, Azure increases the fault tolerance of Snowflake clusters.

The power of Azure ensures maximum performance and high throughput for Snowflake nodes by providing a low latency network. Since SnowSQL mirrors Snowflake which stores and computes data, the elastic nature of the cloud will allow it to load data faster or run a high volume of queries. 

Recovery of data has never been this easy. It also provides optimized query parsing and strong integration. Install the SnowSQL offer now from the Azure Marketplace by Data Science Dojo, your ideal companion in your journey to learn data science! 

Click on the button below to head over to the Azure Marketplace and deploy SnowSQL for FREE by clicking on “Get it now”. 

 

SnowSQL Package

Note: You’ll have to sign up to Azure, for free, if you do not have an existing account. 

 

 

Phuc Duong
| September 22, 2016

Learn how companies like Zillow predict the value of your home. Build a predictive model using azure machine learning that estimates the real estate sales price of a house.

Ames housing dataset includes 81 features and 1460 observations. Each observation represents the sale of a home and each feature is an attribute describing the house or the circumstance of the sale.

Follow along, clone this experiment

A full copy of this experiment has been posted to the Cortana Intelligence Gallery.Go to the link and click on “open in Studio.”

Preprocessing & data exploration

Drop low value columns

Begin by identifying features (columns) that add little-to-no value for predictive modeling. These columns will be dropped using the “select columns from dataset” module.

The following columns were chosen to be “excluded” from the dataset:

Id, Street, Alley, PoolQC, Utilities, Condition2, RoofMatl, MiscVal, PoolArea, 3SsnPorch, LowQualFinSF, MiscFeature, LandSlope, Functional, BsmtHalfBath, ScreenPorch, BsmtFinSF2, EnclosedPorch.

These low quality features were removed to improve the model’s performance. Low quality includes lack of representative categories, too many missing values, or noisy features.

Azure-machine-learning-real-estate-sales-price

 

Define categorical variables

We must now define which values are non-continuous by casting them as categorical. Mathematical approaches for continuous and non-continuous values differ greatly. Nominal categorical features were identified and cast to categorical data types using the meta data editor to ensure proper mathematical treatment by the machine learning algorithm.

The first edit metadata module will cast all strings. The column “MSSubClass” uses numeric integer codes to represent the type of building the house is, and therefore should not be treated as a continuous numeric value but rather a categorical feature. We will use another metadata editor to cast it into a category.

 

azure-machine-learning-clean-missing-data

Clean missing data

Most algorithms are unable to account for missing values and some treat it inconsistently from others. To address this, we must make sure our dataset contains no missing, “null,” or “NA” values.

Replacement of missing values is the most versatile and preferred method because it allows us to keep our data. It also minimizes collateral damage to other columns as a result of one cell’s bad behavior. In replacement, numerical values can easily be replaced with statistical values such as mean, median, or mode.

While categories can be commonly dealt with by replacing with the mode or a separate categorical value for unknowns.

For simplicity, all categorical missing values were cleaned with the mode and all numeric features were cleaned using the median. To further improve a model’s performance, custom cleaning functions should be tried and implemented on each individual feature rather than a blanket transformation of all columns.

 

Azure-machine-learning-edit-metadata

Machine learning – Model building

Statistical feature selection

Not every feature in its current form is expected to contain predictive value to the model and may mislead or add noise to the model. To filter these out we will perform a Pearson correlation to test all features against the response class (sales price) as a quick measure of their predictive strength, only picking the top X strongest features from this method, the remaining features will be left behind.

This number can be tuned for further model performance increases.

 

filter-based-feature-selection - algorithm

 

Select an algorithm

First, we must identify what kind of machine learning problem this is: classification, regression, clustering, etc. Since the response class (sales price) is a continuous numeric value, we can tell that it is a regression problem. We will use a linear regression model with regularization to reduce over-fitting of the model.

  • To ensure a stable convergence of weight and biases, all features except the response class must be normalized to be placed into the same range.

 

Reguarlization

 

Model training and evaluation

The method of cross validation will be used to evaluate the predictive performance of the model as well as that performance’s stability in regard to new data. Cross validation will build ten different models on the same algorithm but with different and non-repeating subsets of the same dataset. The evaluation metrics on each of the ten models will be averaged and a standard deviation will infer to the stability of the average performance.

 

Model-training-and-evalualtion--cross-validation-for-azure-machine-learning

 

 

cross-validation_pkmeou

Parameter tuning

This experiment will build a regression model which minimizes mean RMSE of the cross validation results with the lowest variance possible(but also consider bias-variance trade-offs).

  1. The first regression model was built using default parameters and produced a very inaccurate model ($124,942 mean RMSE) and was very unstable (11,699 standard deviation).
  2. The high bias and high variance of the previous model suggest the model is over-fitting to the outliers and is under-fitting the general population.
  3. The L2 regularization weight will be decreased to lower the penalty of higher coefficients. After lowering the L2 regularization weight, the model is more accurate with an average cross validation RMSE of $42,366.
  4. The previous model is still quite unstable with a standard deviation of $8,121. Since this is a dataset with a small number of observations (1460), it may be better to increase the number of training epochs so that the algorithm has more passes to reach convergence.
  5. This will increase training times but also increase stability. The third linear model had the number of training epochs increased and saw a better mean cross validation RMSE of $36,684 and a much more stable standard deviation of $3,849.
  6. The final model had a slight increase in the learning rate which improved both mean cross validation RMSE and the standard deviation.

 

parameter-tuning

Deployment

The algorithm parameters that yielded the best results will be the one that is shipped. The best algorithm (the last one) will be retrained using 100% of the data since cross validation leaves 10% out each time for validation.

 

Train Model

Further improve this model

Feature engineering was entirely left out of this experiment. Try engineering more features from the existing dataset to see if the model will improve. Some columns that were originally dropped may become useful when combined with other features. For example, try bucketing the years in which the house was built by decade. Clustering the data may also yield some hidden insights.

Albar Wahab
| January 12, 2022

Data Science Dojo has launched Grafana’s offering to the azure marketplace to help you harvest insights from your data. It leverages the power of Microsoft Azure services to visualize, query, and set alerts for your data while promoting teamwork and transparency.

Excel stopped working
Excel is Not Responding

Does the above visual seem familiar? How many times are you trying to meet your deadlines only to be met bng? After all, spreadsheets can deal with complex calculations only up to a certain threshold.

Drawbacks of spreadsheets

Spreadsheets offer you a lot of cool features involving data entry, calculations, and manipulation. But dealing with all the cells and formulas can get overwhelming, making it more prone to errors affecting the integrity of the model. 

There is a security and privacy issue when users store data in their individual spreadsheets and drive; elevated levels of collaboration also become a hassle when having data stored in different platforms. It is impossible to keep track of where the entries were altered or updated resulting in multiple versions of the same file undermining the overall confidence in the model. 

Finally, it is not possible to present a stack of spreadsheets to your audience because they require a story to be presented to them which cannot be conveyed via rows and columns of large data. All these problems can be overcome by using it to generate insightful dashboards that summarize all your data into easy-to-read visuals and alerts that make generating actionable items much easier!

What is Grafana?

Grafana Logo
Grafana Logo

Grafana is built on the principle that data should be accessible to everyone, it allows visualizations to be shared, promoting teamwork and transparency. It enables its customers to take any of their existing data and visualize it however they want. It offers services for advanced querying and transformation and enables customers to create customized dashboards and panels, catering to their specific needs. We here at Data Science Dojo deliver data science education, consulting, and technical services to increase the power of data.

Thus, we are adding Grafana’s instance to the azure marketplace to help you harvest insights from your data. It leverages the power of Microsoft Azure services to capture visits, events, and monitor user actions. Install our Grafana’s offering now and get started on your journey towards optimal analysis.

Why Grafana?

Unify your data from various platforms

Grafana offers you the option to integrate your data from various platforms, including both Azure and non-Azure services. That’s right! It doesn’t matter if your data is in google sheets or Azure Cosmos DB. You can connect to any of these sources at once! 

Search & query through your data

Imagine having to go through a thousand spreadsheets just to find one single entry that satisfied your condition. Is sound impossible? Not with Grafana. In its collaborative environment, you can write down your custom data analytics queries to filter out the data that fits your requirements.

Customized visualization & dashboards

Grafana Dashboard
Grafana Dashboard

Grafana offers you the option to generate highly customized visualizations that help you gain tactical insight from data that is often ignored. Leverage the power of Azure to collaborate and share various Grafana Dashboards with different stakeholders within and outside your organization.

Alerts

It can be difficult to constantly monitor your crucial KPIs and metrics and sometimes you may not realize your KPI has dipped below the margin before it is too late. Grafana lets you set up custom alerts to monitor these metrics and drop notifications on platforms such as slack and teams when it is the right time to act.

Try Grafana

Related Topics

Web Development
Top
Statistics
Software Testing
Programming Language
Podcasts
Natural Language
Machine Learning
Hypothesis Testing
High-Tech
Events
Discussions
Demos
Data Visualization
Data Security
Data Science
Data Mining
Data Engineering
Data Analytics
Conferences

Up for a Weekly Dose of Data Science?

Subscribe to our weekly newsletter & stay up-to-date with current data science news, blogs, and resources.