Data-Engineering

for Interview preparation & for Beginners

The field of Data Engineering concerns itself with the mechanics for the flow and access of data. Its goal is to make quality data available for fact-finding and data-driven decision making.

Data Engineers:

• Designing, Building, Maintaining data infrastructures and platforms
• Develop and optimize data systems
• Make data available for analysis

Data Analysts:

• Analyze data in data systems to report and derive insights

Data Scientists:

• Perform deeper analysis on data
• Develop predictive models to solve more complex data problems

The field of Data Engineering involves:

• Collecting source data (Extracting, integrating, and organizing data from disparate sources)
  • Data acquisition from multiple sources
  • Data architecture for storing source data
• Processing data (Cleaning, transforming, and preparing data to make it usable)
  • Distributed systems for processing data
  • Pipelines for extracting, transforming, and loading data
  • Solutions for safeguarding quality, privacy, and security of data
  • Performance optimization
  • Adherence to compliance guidelines
• Storing data for reliability and easy availability of data
  • Data stores for storage of processed data
  • Scalable systems
  • Ensuring data privacy, security, compliance, monitoring, backup, and recovery
• Making data available to users securely
  • APIs, services, and programs for retrieving data for end-users
  • User access through interfaces and dashboards
  • Checks and balances to ensure data security

Languages needed for Data Engineers

• Query languages:

  • SQL for mostly relational databases we use mostly for:
  • NoSQL or SQL-like query languages for NoSQL databases

• Programming languages

  • Python, R, Java

• Shell and Scripting languages

  • Unix/Linux Shell and PowerShell

• Big Data Processing Tools

  • Hadoop (for Big Data)
  • HIVE
  • Spark Apache

Data

Data Forms:

• Structured
  • Data that follows a rigid format and can be organized into rows and columns.
    • SQL Databases
    • Online Transaction Processing
    • Spreadsheets
    • Online forms
    • Sensors GPS and RFID
    • Network and Web server logs
• Semi-structured
  • Mix of data that has consistent characteristics and data that does not conform to a rigid structure (Row, columns).
  • XML and JSON Allow users to Define Tags Attributes and to store Semi-Structured data
    • E-mails
    • XML and other markup languages
    • Binary executables
    • TCP/IP packets
    • Zipped files
    • Integration of data
• Unstructured
  • Data that is complex and mostly qualitative information that cannot be structured into rows and columns.
  • Does not have an easily identifiable structure
  • Cannot be organized in a mainstream relational database in the form of rows and columns
  • Does not follow any particular format, sequence, semantics, or rules
    • such as Files and Docs
    • Manual Analysis
    • NoSQL
    • Analysis tools

Standard File Formats:

1. Delimited text file formats, or .CSV
   • Delimiter - A sequence of one or more characters for specifying the boundary between independent entities or values (Comma, Tab, Colon, Vertical Bar, Space).
2. Microsoft Excel Open .XML Spreadsheet, or .XLSX
   • Open file format, accessible to most other applications
   • Can use and save all functions available in excel
   • Is a secure file format as it cannot save malicious code
3. Extensible Markup Language, or XML
   • Extensible Markup Language, or XML, is a markup language with set rules for encoding data.
     • Readable by both humans and machines
     • Self-descriptive language
     • Similar to .HTML in some respects
     • Does not use predefined tags like .HTML does
     • Platform independent
     • Programming language independent
     • Makes it simpler to share data between systems
4. Portable Document Format, or .PDF
5. JavaScript Object Notation, or .JSON
   • JavaScript Object Notation, or JSON, is a text-based open standard designed for transmitting structured data over the web.
     • Language-independent data format
     • Can be read in any programming language
     • Easy to use
     • Compatible with a wide range of browsers
     • Considered as one of the best tools for sharing data

DataBases

• Data Repository is a general term used to refer to data that has been collected, organized, and isolated. Data repositories help to isolate data and make reporting and analytics more efficient and credible while also serving as a data archive.
• DataBase: Collection of data for input, storage, search, retrieval, and modification of data.
• DataBase Management System Set of programs for creating and maintaining the database, and storing, modifying, and extracting information from the database.
• Factors governing choice of database include: Data type; Data structure; Querying mechanisms; Latency requirements; Transaction speeds; Intended use of data

Types of Data Repositores:

• Relational DataBase Management System (RDBMS)

  • Languages of RDBMS: SQL Server, MySQL, IBM DB2, Oracle Database, PostgreSQL
  • Data is organized into tables format with rows (Records) and columns (Attributes).
  • Well-defined structure and schema for Structured Data.
  • Optimized for data operations and querying
  • Use SQL as the standard querying language
  • Insert, update, and delete records in a database Create new databases, tables, and views Write stored procedures
  • Cloud relational databases include Amazon Relational Database Service (RDS), Google Cloud SQL, IBM DB2 on Cloud, Oracle Cloud, and SQL Azure.
  • Minimize data redundancy by allowing relationships to be defined between tables
  • Offer export and import options that provide ease of backup and disaster recovery
  • Are ACID compliant, ensuring accuracy and reliability in database transactions
  • Does not work well with semi-structured and unstructured data
  • Migration between two DBMS's is possible only when the source and destination tables have identical schemas and data types

• No-Relational DataBase Management System Database (NoSQL):

  • Languages of non-RDBMS: Redis, MongoDB, Cassandra, Ne04J, HBase
  • Emerged in response to the volume, diversity, and speed at which data is being generated today
  • Built for speed, flexibility, and scale
  • Data can be stored in a schema-less form widely used for processing big data
  • Do not use a traditional row/column/table database design with fixed schemas
  • Types of NoAQL:
    • Key-value store
      • Data in a key-value database is stored as a collection of key-value pairs.
      • A key represents an attribute of the data and is a unique identifier.
      • Both keys and values can be anything from simple integers or strings to complex JSON documents.
      • Great for storing user session data, user preferences, real-time recommendations, targeted advertising, in-memory data caching.
      • Not a great fit if you want to: Query data on specific data value; Need relationships between data values; Need multiple unique keys
      • i.e. Redis, Memcached, and DynamoDB
    • Document Based
      • Document databases store each record and its associated data within a single document.
      • They enable flexible indexing, powerful ad hoc queries, and analytics over collections of documents.
      • Preferred for eCommerce platforms, medical records storage, CRM platforms, and analvtics platforms.
      • Not a great fit if you want to: Run complex search queries; Perform multi-operation transactions

• Data Mart:

  • A data mart is a sub-section of the data warehouse, built specifically for a particular business function, purpose, or community of users.
  • Three basic types of data marts: dependent, independent, and hybrid data marts.
    • Dependent data marts are a sub-section of an enterprise data warehouse. Data mart offers analytical capabilities for a restricted area of the data warehouse, it also provides isolated security and isolated performance. Dependent data marts, pull data from an enterprise data warehouse, where data has already been cleaned and transformed.
    • Independent data marts are created from sources other than an enterprise data warehouse, such as internal operational systems or external data. Independent data marts need to carry out the transformation process on the source data since it is coming directly from operational systems and external sources.
    • Hybrid data marts combine inputs from data warehouses, operational systems, and external systems.

• Data Lakes:

  • a data repository that can store large amounts of structured, semi-structured, and unstructured data in their native format. While a data warehouse stores data that has been cleaned, processed, and transformed for a specific need, you do not need to define the struct