What are the key steps in data acquisition?

Identifying sources involves understanding where your data originates, the format of the data, and how it can be accessed. Here is an example of when I need to identify data sources: - Security and Compliance Check: To ensure that we can access data properly in terms of laws and regulations, and also to address security concerns. - Selecting Tools/Libraries/Drivers: Choose appropriate tools, libraries, or drivers to access the data, considering their format and the upstream system to ensure compatibility. - Evaluating Data Reliability and Quality: Assess the reliability and quality of data from these sources.

We need to identify the sources and decide which tool or technology we will use to extract the data. 💯 For example. Extracting the oracle database data currently not supported by databricks. Here we can use the python script to connect with the oracle database using the JDBC connector and pull the data. ✅ Similarly , we need to use sensors to check the data arrival which is supported by Airflow , AWS , Azure and many others. 🌨️

Expect web scraping all other methods of data is reliable as source. If the third party have the data source it can be accessed with an appropriate licence terms instead of web scrapping.

💡One thing I found helpful in this step is to establish a clear framework for assessing: - Availability: ensures data is accessible when needed. - Accessibility: evaluating the ease with which the data can be retrieved and integrated into the pipeline. - Security: ensure data is accessible without any security concerns related to authorization or dealing with sensitive information. 📝 By creating a matrix (Source, Type, Format, Volume, Quality, Availability, Accessibility, Security) that rates each available source based on these criteria helped in making data-based descensions, and regularly revisiting and updating this assessment when introducing new data sources to make sure the data pipeline still be agile and effective.

When you identify data sources that are origin data, you will get details related with data acquisition 👇🏻: 💾 Storage: what tools use to store extracted data based in generating features, and align it to consume from ETL. You can use Block storage, databases or event streaming platform like Kafka or redpanda. 💻 Processing: Tool use to connect it to extract your data. Differents connector are required your SaaS, Web scrapying software or database based in business cases. 📄 Structure: You can verify what file format; like csv, sql, or parquet files, data types it supports and how generate relationship if apply.

To define a database schema, start by categorizing data into tables, each will represent a distinct entity. 👇🏻 ✅ Within these tables, define columns to capture specific attributes (e.g., name, price). ✅ Define a primary key for uniqueness within each table and foreign keys for relationships between tables. ✅ Define constraints to enforce rules like uniqueness or mandatory values, ensuring data integrity. ✅ Use normalization techniques to minimize redundancy and improve efficiency. ✅ Documentation should be done for the schema's structure for future reference. ✅ Regularly review and refine the schema to align with evolving data requirements and maintain an organized and efficient database structure.

Define schema based in requirements of access to transforming processes; so you can use 2 focus on it 👇🏻: 🔠 Flat schema: common example is normalized tables; in this focus each column from extracted data need to be assigned to specified column in staging table (staging table can have same schema of extracted data). Your metadata is separated in other independent file. Some examples are dimensional or normalized data modeling. 🆓 Typed schema: each columns of extracted data is row in staging tables and fill metadata related this field like loading timestamp or business view of it. Some examples are hyper-agility and focal data modeling.

If you are an orphan, relational schemas may often feel overwhelming. In this case, just remember that foreign keys and check constraints are your friend.

Define schema based on the requirements of accessing and transforming processes, with a focus on two approaches: Flat Schema: In this schema focus, the data is organized into normalized tables, where each column from the extracted data is assigned to a specified column in a staging table. The staging table can have the same schema as the extracted data. Metadata is separated into another independent file. Examples of this approach include dimensional or normalized data modeling. Typed Schema: In this schema focus, each column of the extracted data becomes a row in staging tables, and metadata related to each field is filled, such as loading timestamp or business view. Examples of this approach include hyper-agility and focal data modeling

Data extraction involves retrieving data from various sources, and the process can vary based on the type of source. For databases, it typically involves querying using SQL or other database-specific languages. APIs provide a structured way to request and receive data programmatically. Web scraping involves parsing HTML or other markup languages to extract information from websites. File-based sources require reading and importing data from files in formats such as CSV, JSON, or Excel. This step is crucial because it lays the foundation for acquiring the raw data needed for analysis. The accuracy and efficiency of the extraction process directly impact the quality of the data used in subsequent stages of the data pipeline.

Python scripting helps a lot to connect easily with any kind of data source for data extraction. Let's see some examples here . 👇🏻 Certainly! Here are four methods for data extraction using Python: ✅ CSV Files: We can use the `csv` module to read and extract data from csv files. ✅ Web Scraping with bs4: Use the `requests` library along with `BeautifulSoup` for parsing HTML or XML and extracting required information from web pages/websites url. ✅ Database Extraction with SQLAlchemy: We should use the 'SQLAlchemy' library to interact with different databases and pull data using SQL queries. ✅ APIs with Requests: Utilize the `requests` library to make HTTP requests and retrieve data from APIs, often in JSON format.

👀 Each case of "using this extracting tool to get data from this data source" have their own limitations; so preparing extraction process to avoid these limitations as long as possible. ✅ Web scrapying: Limitation of I/O networking, amount process running, unexpected change in web structure or anti-scraping mechanisms. ✅ Database: Performance of database, connector limitations and not supported fields to extract. ✅ API: Limitation of I/O networking, amount process running and Response size.

Data extraction involves retrieving data from various sources, with the process differing based on the type of source. For databases, extraction typically entails querying using SQL or other database-specific languages. APIs offer a structured method for requesting and receiving data programmatically. Web scraping involves parsing HTML or other markup languages to extract information from websites. Filebased sources require reading and importing data from files in formats such as CSV, JSON, Excel. This step is crucial as it establishes the groundwork for acquiring the raw data essential for analysis. The accuracy and efficiency of the extraction process directly impact the quality of the data used in subsequent stages of the data pipeline

Extracting data from several source systems is the first step towards data engineering and performing your transformation and business logics on it. We should always extract only data which is relevant from business perspective and which is needed in your downstream jobs . We can use multiple extract methods to get the data like ETL , using Spark , reading from queues , and tools should be effectively used to extract data keeping in mind the business needs

Databricks can serve as a powerful ETL (Extract, Transform, Load) tool by leveraging its Apache Spark-based platform. ⚙️ ✅ Begin by connecting Databricks to your data sources, such as databases like snowflake or Azure blob or files like csv etc. ✅ Use Spark's data processing capabilities to perform transformations like filtering or aggregations by using PySpark with notebooks. ✅ Then schedule jobs to automate ETL workflows, ensuring regular data updates. ✅ Utilize Spark's parallel processing for scalable performance. ✅ Databricks simplifies ETL with a collaborative environment, powerful Spark engine, and seamless integration with various data sources, making it an effective solution for data engineering tasks in big data processing.

💥 Choose right tools based in strategy of integration and transformation processes, some cases are: 🤖 Extract derived data using machine learning models to integrated, so you can use ray cluster computing in real time consuming. 💱 Convert data to specified format or data type can be worked in event focus serverless computing or cluster computing, it based in data volumen and size. 🚀 Enrich transforming data with external data or previos data to get new insights from it, so you can use cluster computing and streaming storage.

Data can easily be transformed into garbage by the judicious application of enums in front end code. If all else fails, we can run the transform --> null operator to clean it up. The easiest way to transform data into gold, is to sell lots of expensive tools that are simply rebadged opensource.

Data transformation is the pillar of DATA ENGINEERING and you perform all your data quality checks , data processing and business rules in this layer. You can use multiple tools for performing this and it’s imperative you decide where you want to run the transformation. For example , if your data volume Is less we need not spend cost on a Hadoop or Spark distribution, if we are dealing with record level data we can go with real time depending on business use cases and so on. Data transformation essentially helps you with the gold data which is shared with data virtualization team and business stakeholders and help them make critical decisions

After data extraction, it's common to encounter inconsistencies, errors, or missing values. The transformation and cleansing step involves standardizing the format, cleaning up errors, handling missing data, and converting data types. This process ensures that the data is in a consistent and usable state for analysis. Clean and well-structured data is essential for accurate and meaningful analysis. Transforming and cleansing the data is critical for resolving inconsistencies and preparing the data for integration, enabling better decision-making based on reliable information.

As a Data Engineer selecting the right storage solution is most important. 💯 We should consider the structure of data and volume to find relational databases for structured data, NoSQL for flexible schema and cloud storage for scalability. 🌨️ Prioritize security measures to safeguard sensitive information. ✅ Opt for reliable and scalable systems like data warehouses for analytical processing. ✅ Leverage cloud services for accessibility and cost-effectiveness.✅ Try for a balance between performance and cost and also aligning the storage architecture with the requirement of analysis and decision-making processes. Regularly check and adapt storage solutions to find evolving data requirements and follow best practices.

Storage is one of the most critical choices in a data architecture. Factors such as price, availability, and security related to storage must be taken into account, and all these decisions should be made from the perspective of business requirements. In the end, data will serve as a product and should be managed as such.

When monitoring your data pipelines, report following topics: 📈 Performance of each component in RAM, CPU and I/O networking, as well general flow process. ✅ Data quality rules applied to achieve high value data. 🚨 What your error handling strategies was applied to continue work: error type, line of code, component failed and so on. ⚠️ How many times the process crashed: time and date of the event, errors that caused it, recovery plan Your alerts will use these parameters to notify your team.

If data monitoring is not performed, all the work of storing, transforming, and loading data can be lost, as data serves no purpose if it is not of quality. This is where the importance of monitoring comes in, which can be analyzed from the perspective of metrics, alerts and accuracy, concluding the data lifecycle effectively.

Data integration is the process of combining data from different sources into a unified dataset. This involves resolving schema differences, handling duplicates, and ensuring that the integrated data is coherent. Integration can occur in a staging area before loading into a data warehouse or directly within the data storage solution. Many organizations deal with data from multiple sources, and integrating this data provides a holistic view, enabling comprehensive analysis. Without proper integration, inconsistencies and discrepancies between datasets may lead to inaccurate insights. It is a crucial step for achieving a unified and coherent data environment.

There’s probably a dataset for what you’re looking for, be create and look at the following sources: - HuggingFace.co - Kaggle - GitHub - Research Papers (Google Scholar and ArXiv.org) - Hackathon pages It may also exist through some obscure YouTube tutorials and Colab notebook.

At times when working on new type of sources and large data sets it is best to identify the sources and start storing them. With modern data engineering techniques we can analyse and visualise the data to understand the business value before putting transformation into schema etc. That is the reason Extract Loading and then Transformation(ELT) is useful when working with big data sets instead of plain ETL .

💥 Part of data acquisition is defined what is your strategy for data loading! Some question will help to design a robust and scaling data loading process are: 📊 How size extract data: in batch, chunk, event or streaming? ⏰ How many time will apply this process? 🚀 Will load your data in one operations or many parallel operations? Some question are applied to extracting data phase!