Introduction to Data Engineering using Generative AI

Explore csv and json files in data engineering. Learn how csv uses comma separators and header rows for human-readable tabular data, while json uses key-value pairs for flexible, semi-structured structures.

Explore how command line utilities handle csv data, using awk to remove a column from a weather history dataset and learn how large language models aid scripting.

Learn to combine two awk-based filters into a single bash shell script, with a shebang, executable permission, and error handling, to generate high humidity data from weather history.

Write a Python script to randomly sample 10% of a CSV, taking input file as a parameter, and illustrate weather history data showing high humidity while noting memory concerns.

Modify a shell script to add a Python sampling step and start/end time logs, creating a weather data pipeline that yields a high humidity sample.

Schedule automated data pipeline runs with cron by editing the crontab file to create cron jobs and specify minute, hour, day, month, and day of week for your script.

Demonstrates creating a cron job to run a weather script at 2 a.m. daily, editing crontab with nano, and verifying with crontab -l as a foundational data engineering automation tool.

Install jq on Windows, Linux, and macOS by downloading the binary or using package managers like apt-get, dnf, pacman, or Homebrew, and ensure the binary is on your PATH.

Learn to filter JSON data with jq by applying a map-based filter to select hotels with ratings 4.85 or higher, demonstrated on a JSON file and terminal workflow.

Explore data quality checks across completeness, consistency, accuracy, timeliness, uniqueness, and constraints, with CSV and JSON validation, distribution analyses, descriptive statistics, and cross-source consistency.

Build a dimensional model in Postgres using sales data to populate a fact table with units and price, and dimensions date, product, and location, with surrogate keys loaded via elt.

Add documentation to a script that checks csv files for missing values, outlining its purpose, major steps, and how it iterates rows and cells to report missing data and statistics.

Learn how range checks ensure data quality by flagging temperatures outside defined bounds in a CSV, using a Python script with parameterized input and zero-based column indexing.

Learn to handle dates and times by tracking time zones, adopting UTC as a common reference, and managing date formats and timestamp precision to avoid inconsistencies when merging datasets.

Learn to assess data quality with quick visualizations using pandas and matplotlib, including temperature histograms and summary pie charts. Group rare categories into other to sharpen data quality overviews.

discover how pandas uses series and data frames to load and manipulate data from CSV, JSON, Excel, and SQL, with grouping, merging, and summary statistics.

Generate json data with makuru, including id, first name, last name, email, favorite color, and ip address, and use a language model to create a json schema for quality checks.

Validate json data by applying a json schema to each record. Collect validation errors and print all failing rows, while switching from validating all data to single-record validation.

Explore how relational databases use schemas and SQL to store, query, and manage data with ACID properties and support analytical queries and transactional queries.

NoSQL databases are non-relational data stores with flexible schemas designed for large-scale applications. They trade some acid properties for availability, scalability, and performance, including key-value, document, column, and graph types.

Discover PostgreSQL, an open-source relational database with acid compliance and native json support for embedded NoSQL documents, plus extensibility and indexing for scalable queries.

install Postgres on mac using the official installer, configure Pgadmin, create a local Postgres database, and verify the setup by exploring the public schema and query tool.

Explore PostgreSQL schemas by creating a staging schema, moving tables into staging, and planning data loading from staging into a dimensional model for elt workflows.

Create and verify three Postgres tables—sales, product, and location—using sample csv data, with date, varchar, integer, and decimal columns, and verify that the csv data is reflected in the tables.

Learn dimensional modeling with a star schema to optimize analytic queries in data warehouses, using fact and dimension tables, surrogate keys, and etl or elt loading with joins.

Load sales data from a CSV into staging.sales using a Python script with psycopg2, mapping CSV to the table, and using an env var for the DB password.

Create a DM products dimension table with product id, source system id, name, and category, adjust the create table to serial, run inserts, and verify via select star in pgadmin.

Create a time dimension by building a dim dates table with date ID, day, month, quarter, and year, and generate daily inserts from 2024-01-01 to 2024-12-31.

Design a dm fact sales table by converting staging sales into a fact table with date_id, product_id, and location_id as foreign keys to dim dates, dim products, and dim locations.

Delete the old facts table and build a query against dm_staging.sales, group by date of sale, product, and location to yield sum(quantity) and averages of unit price and total price.

Load data in json format into a Postgres jsonb table, extract the title into the title column, and store the full json in the review column.

Learn to query json values in PostgreSQL to retrieve the title, price value, and price period from a hotels table. Navigate json with arrow notation in select statements.