Data Engineering for Beginners: Learn SQL, Python & Spark

Set up postgres and pgadmin to revise sql from a data engineering view, build application tables, and practice basic and advanced queries, including joins, aggregations, cumulative totals, and ranking.

Explain how RDBMS and SQL power a typical web or mobile retail app, detailing how application servers route transactions from users to databases.

Explore major rdbms and data warehouse technologies, from Oracle and Postgres to Snowflake and Redshift, and revise sql across vendors using Postgres as the learning focus.

Explore how purpose built databases drive data engineering, using RDBMS, data warehouses, and MPP platforms alongside NoSQL, search-based databases, and graph databases as diverse data sources.

Explore how data warehouses and data lakes, built on MPP and purpose-built databases, store and compute enterprise data to power reports, dashboards, and analytics with BI tools.

Contrast the uses of RDBMS and data warehouse technologies for transactional versus analytical needs, highlighting real-time operations, reports, and executive insights.

Compare RDBMS and data warehouse technologies, noting table structures, data types, and OLTP versus OLAP use cases, with SQL-driven validation and ETL workflows.

Install and integrate VS Code, Python 3.9, Postgres, and PgAdmin, verify access to the Perseus server, and prepare your environment for SQL, Python, and Spark.

Install and set up Visual Studio Code on Windows 11, with notes for Windows 10. Use Visual Studio Code as the main ide for Java, Python, and Scala in this course.

Install Python 3.9 on Windows from python.org, select the 64-bit installer, verify with hello world, and launch Python from the app and PowerShell; next lecture covers path configuration.

Configure the Windows path to run Python 3.9 from PowerShell by locating the installation and updating the system path. Validate by launching Python and printing hello world.

Learn Python via the PowerShell CLI to practice basics like printing strings and arithmetic with A and B. See why IDEs like Jupyter and VS Code boost learning and integration.

Explore integrating Python 3.9 on Windows with VS Code by installing the Python extension, setting up a workspace, and running a hello world script in the terminal.

Install Postgres 14 on Windows 11 by downloading the latest Postgres 14.5 installer, running the exe, and setting a system password; plan to connect with pgadmin in the next lecture.

Install Postgres and pgAdmin on Windows, set a separate master password, and connect pgAdmin to the Postgres server to review databases.

Study how to validate a PostgreSQL setup on mac using pgAdmin, configure the master password, connect to databases, and run queries via the SQL editor against information schema.

Set up and validate VS Code, Python 3.9 on Windows, PowerShell, Postgres, and Pgadmin to prepare the Data Engineering Essentials course in SQL, Python, and Spark.

Set up a Postgres database server and pgadmin to manage databases like postgres and retail_db with retail_user, using localhost and port 5432 to prepare for creating tables and running queries.

Learn how Postgres database server and pgAdmin connect on localhost, using 127.0.0.1 and DNS aliases, with port 5432, and register a server with a username and password for local development.

Connect to external Postgres databases with pgadmin, using server IP or DNS, credentials, and port; run standard queries across local, test, and production environments.

Use pgadmin to create a postgres database and user, grant all permissions to varsity_retail_db for varsity_retail_user, and connect to set up tables in the new database.

Clone the git repository to access datasets and scripts that set up tables in the underscore db, then run the two sql scripts via pgadmin to configure the database.

Register a dedicated non-super user server in pgAdmin on localhost, connect to the application database varsity__db with varsity__user, and practice running scripts while avoiding the superuser Postgres account.

Register and connect to the application database, run create and load scripts to build and populate Postgres tables, then validate data with count queries and view first 100 rows.

Master SQL queries in Postgres with PgAdmin by exploring the query editor, running statements, and using query history to accelerate data engineering skills.

Explore a six-table data model, including customers, orders, order items, products, departments, and categories, with primary and foreign keys. Understand one-to-many relationships to craft SQL queries.

Define the problem statement for sql queries by identifying order date, order item product id, and order item subtotal to compute daily product revenue from complete or closed orders.

Explore filtering data with SQL queries to retrieve complete or closed orders from the orders table, using distinct statuses, uppercase comparisons, and the in operator for flexible conditions.

Explore global aggregations in SQL, using count, sum, min, max, avg, and distinct to compute totals such as revenue for a given order ID in orders and order_items.

learn group by aggregations in sql to count by order status and order date, and compute revenue per order id with sum and round, using aliases and order by.

Explore the SQL execution order: from clause to memory, then where, group by, and order by, with derived columns and aliases and a focus on the select clause.

Learn the correct order of writing and executing SQL queries, and apply group by rules, alias restrictions in where clauses, and ordering by derived or aggregate fields.

Explore filtering data by aggregated results with the having clause on a group by, using aggregate functions or aliases, and understand the execution order for revenue queries.

Learn to write inner joins to compute daily product revenue by joining orders and order_items, using aliases and on conditions, with a primer on outer joins.

Explore outer joins in SQL, including left outer join, right outer join, and full outer join, contrasting them with inner joins and driving tables like orders and order items.

Filter join results to include complete or closed orders, group by order date and order item product ID, sum subtotals, round to two decimals, and sort by date and revenue.

Create views to encapsulate complex joins, such as orders joined with order items. Understand that views are non-physical, hold no data, and can be replaced when requirements change.

Learn common table expressions (ctes) for modular sql using with and definitions. They are not stored like views; redefine them per query.

Explore outer joins to identify products never sold by joining products with the order details view, filtering by 2014 January, and placing conditions in the join to avoid bugs.

Explore how outer joins and filters reveal products not sold in January 2014. Show why moving the filter from the where clause to the on clause fixes the query.

Explore how to compute cumulative aggregations and ranking in SQL queries using daily revenue and daily product revenue data, by creating views and tables.

Explore how to use CTAs to create tables from query results, build stage tables from orders, and apply cumulative aggregations and ranking for daily revenue analysis.

Create daily revenue and daily product revenue tables using ctas, then validate results with select queries and optional order by on order_date and revenue for later cumulative aggregations and ranking.

Learn how to use the over clause with partition by and order by to compute monthly revenue over daily data, producing raw data with cumulative and monthly aggregates.

Learn to compute total order revenue alongside raw daily data using sum over partition by, alias it as total_order_revenue, and apply order by to sort by date.

Explore rank and dense_rank windowing functions in SQL, using global ranking and partitioned ranking on daily product revenue to understand cumulative aggregations and analytics.

Filter daily prod revenue for 2014-01-01, project order date, order item, product ID, and order revenue, then compute ranks with rank or dense_rank over order by order revenue desc.

Compute ranks in SQL by using partition by and order by to rank daily revenue within each date, exploring global and per-day rankings with PostgreSQL syntax.

Understand the SQL order of execution for ranking data and learn how to filter by rank using nested queries or common table expressions.

Master how to filter data by global ranks in SQL using nested queries and common table expressions, including where clause use, from clause techniques, and dense rank considerations.

Explore filtering top five daily products by rank with partition by and order by, using nested queries and ctes, and optimize by removing order by from nested queries.

Create a student_scores table with student_id and score, insert ten records, then sort by score in descending order to assign ranks, exploring the difference between rank and dense rank.

Explore the difference between rank and dense_rank in SQL using a student scores table, showing how duplicates affect ranking and when to use each function for top performers.

Explore how to troubleshoot and debug sql issues, including connectivity problems, syntax and semantic errors, and bugs in queries, using a three-category framework.

Troubleshoot and debug database connectivity by detailing Postgres setup steps and configuring JDBC or ODBC connections with hostname, port, database name, and credentials to resolve timeouts and access issues.

Learn how to validate and set up telnet on macOS or Windows to diagnose database connectivity issues, including connection timeouts and host unreachable errors.

Learn to validate database connectivity using Telnet by testing localhost and external servers, understanding port numbers like 5432, and troubleshooting DNS, IP, and port issues.

Learn to troubleshoot database connectivity by verifying the database server is up and testing host and port with telnet. Assess firewall blocks with a Windows Postgres demo and PgAdmin.

Examine the Postgres database server setup, including databases such as postgres, retail_db, and underscore_db, and how the superuser Postgres and other users have and grant permissions.

Troubleshoot database credentials and permissions by diagnosing authentication errors, ensuring the correct database exists, and granting appropriate permissions to the right user to access tables.

Learn how SQL queries are compiled with syntax and semantic checks, and roles of DDL, DML, and DQL. Master clause order and troubleshooting syntax and semantic issues using pgadmin.

Diagnose and fix syntax and semantic errors in sql queries by distinguishing between issue types, checking keyword spelling and order, interpreting error messages, and applying correct query structure.

Learn to troubleshoot semantic errors in SQL queries by checking table and column names, using information_schema, and validating queries with pgadmin to distinguish semantic from syntax errors.

Diagnose and fix bugs in SQL queries by evaluating output against requirements, distinguishing bugs from errors, and using order by to sort order counts by status in descending order.

Apply development best practices to troubleshoot SQL bugs by identifying root causes through requirements, design, and data model understanding, then validate with unit and functionality testing.

Learn to troubleshoot and debug sql queries by understanding bugs versus errors, reviewing the data model, and testing a ctas solution for orders_completed where status is complete or closed.

Troubleshoot SQL queries by testing the orders_completed table against requirements, validating structure and data with test cases, and uppercasing order_status to resolve case-sensitivity issues.

Follow a structured debugging approach: understand requirements and data model, review and normalize status values, create the orders_completed table with complete or closed statuses, and verify results with tests.

Learn how to tune SQL query performance by generating and interpreting explain plans, identifying bottlenecks, and applying indexing or query rewrites to optimize backend database performance.

Learn how SQL queries are compiled and executed, including syntax and semantics checks, explain plan generation, selecting an optimal plan, and running the query to fetch data.

Learn to generate explain plans and explain analyze for Postgres SQL queries using Pgadmin or SQL, and interpret readable outputs for simple and joined queries.

Explore performance tuning of SQL queries by examining orders and order items tables, their primary keys, foreign keys, and indexes, and practice explain plans to identify bottlenecks.

Explore how tables and indexes organize data, including primary keys, row identifiers, and ascending order, and learn why indexes speed searches and improve explain plans.

Learn to generate explain plans for sql queries, review tables and indexes, and interpret the tree-structured explain plan to troubleshoot performance bottlenecks.

Interpret explain plans in pgAdmin to identify sql query bottlenecks. Compare index only scan, index scan, and sequence scan, and review root, branch, and leaf terms.

Explore performance tuning of SQL queries in a retail application, analyze explained plans, and optimize indexes on key fields to improve common user tasks like reviewing orders and items.

Explore performance tuning of SQL queries with explain plans, identifying bottlenecks in joining orders, order items, and customers within a one-to-many relationship.

Analyze explain plans to identify bottlenecks like full table scans and hash joins, then tune with a stored procedure and indexing to run queries for customer ids and improve performance.

Identify bottlenecks in SQL queries using explain plans and improve performance by adding required indexes on orders and order items, achieving index scans and dramatic speed gains.

Tune SQL performance by adding indexes on orders and order items for faster joins. Balance read speed with write overhead and use plans to guide index decisions.

Interpret SQL explain plans by tracing the root and nested loop execution, using index scans on orders and order items (order id) to understand join performance.

Master SQL performance tuning by reviewing queries, generating explain plans, and addressing bottlenecks with targeted indexes, testing repeatedly, and balancing read performance with write overhead.

Engage in sql exercises to filter and aggregate data using select, from, where, group by, and order by on the courses table, covering status, python and scala, and authors.

Master basic sql queries with select from, join on, group by, and order by through practical exercises on joins and aggregations using the retail database.

Explore solutions for filtering and aggregations in SQL using a sample courses table, including setup, inserts, and queries to filter by status and search for Python or Scala courses.

Learn practical filtering and aggregations on a courses table, counting by status and author, using group by, where, and having with SQL concepts and Python/Scala context.

Validate the database readiness by counting records across departments, categories, products, customers, orders, and order items, and review the data model diagram to guide SQL queries.

Compute January 2014 customer order count by joining orders and customers, grouping by id and name, and sorting by count descending, then id ascending.

Demonstrates identifying dormant customers for January 2014 by left outer joining customers with orders, filtering by date, projecting all customer columns, and validating results with counts.

Compute revenue per customer by left outer join between customers and orders in January (complete or closed), coalescing nulls to zero, and sorting by revenue descending and customer id ascending.

Compute category revenue for January 2014 by joining categories, products, order items, and orders; group by category id, name, department; sort by category; apply two-decimal rounding to order item subtotal.

Learn to compute the product count per department by joining departments, categories, and products, and validate results while addressing data quality issues and following a three-step process.