Data Engineering for Beginner using Google Cloud & Python

Define data engineering as the tasks that make data available to business users for analytics, reporting, and machine learning, illustrated by the data hierarchy of needs from collection to AI.

Observe how data engineering coordinates with software engineers and data scientists to design product and user tables and perform ETL from raw interaction data for a recommender.

Explore data modelling as the blueprint that structures data and relationships, guiding how to gather goals, assess data availability, and design conceptual models before coding.

Explore how databases organize related data, from spreadsheets to cloud-scale stores, and distinguish relational and nosql models. Learn why databases power ecommerce transactions, healthcare research, and device data synchronization.

Learn how relational databases organize data into tables with rows and columns, using primary and foreign keys to model one-to-many relationships and run sql queries.

Explore when relational databases fall short for massive data and high throughput, due to single-machine limits and challenging horizontal scaling. Prepare for NoSQL as an alternative.

Explore NoSQL databases, a non-relational, not only SQL approach to data storage, offering document, key-value, wide-column, and graph stores with flexible schemas, scalability, and fast operations.

Explore a practical PostgreSQL setup demo by installing and connecting a PostgreSQL database locally or on Google Cloud, using tools like DBeaver or pgAdmin and understanding cloud costs.

Install Python 3 and set environment variables, install Jupyter Notebook and the PostgreSQL adapter with pip, then run the provided notebook to connect, create tables, insert data, and query.

Explore Elasticsearch as a NoSQL database for storing, searching, and analyzing large data volumes. Guide local installation and cloud deployment with Kibana and a REST API.

Connect Python to Elasticsearch with a PostgreSQL client via pip, storing documents as JSON in hero-index. Query with Elasticsearch's JSON language and explore Kibana discovery and index patterns.

Explore the relational data model, its tables, and sql as the standard language, and learn how a good database model ensures data integrity and prevents garbage in, garbage out.

Differentiate OLTP and OLAP to understand real-time transaction processing and historical data analysis. Learn how OLTP captures and maintains individual transactions while OLAP aggregates data for analytics and business intelligence.

Apply database normalization to reduce redundancy and improve integrity by moving from 1st to 3rd normal forms and linking smaller tables via relationships.

Explore first normal form by splitting into teams and members with team name as primary key, then connect to a headquarters table, moving toward second normal form by reducing redundancy.

Explore 2nd and 3rd normal forms, focusing on surrogate versus natural keys, foreign keys, and join tables. Use customer–purchase and many-to-many superpower examples to reveal transitive dependencies.

Learn how transitive dependencies across columns A, B, and C hinder 3rd normal form and how to remove them by creating a separate homeland table linked by homeland id.

Demonstrate Python and PostgreSQL normalization to third normal form by building teams, headquarters, members, homeland, and superpowers, with foreign keys and a many-to-many join.

Learn 3rd normal form basics: when to separate location into its own table versus a descriptive field, and how header-detail lookups with start and end active dates manage lookups.

Denormalization adds data copies to improve read performance by reducing joins in OLAP, after normalization for OLTP. It trades slower writes for faster reads, and requires keeping copies consistent.

Denormalize data from normalized models to denormalized PostgreSQL tables to enable fast analytics; the demo covers two reports, minimal joins, and ETL from OLTP to OLAP.

Explore denormalized OLAP with fact and dimension tables, where facts hold metrics and dimensions describe attributes. Learn about star and snowflake schemas, data marts, and access control for department data.

Explore the star schema, featuring a central fact table linked to denormalized dimension tables for simplified joins, fast aggregations, and end-user filtering in data warehousing.

Demonstrates building a star schema in PostgreSQL using dimension and fact tables, with pandas and matplotlib for optional visualization, and basic aggregations to reveal revenue by store and product.

Learn how snowflake schema builds on the star schema with deeper dimension relationships and normalization, offering less storage and better data integrity at the cost of complexity and joins.

Galaxy schema combines multiple star schemas with shared dimensions to reduce redundancy and improve data integrity in large data warehouses, with trade-offs in design and data load complexity.

Learn to design ETL and ELT pipelines that extract data from OLTP sources, stage and clean data in staging tables, and load into denormalized OLAP fact and dimension tables.

Demonstrates end-to-end etl from csv to a normalized database using four staging tables and python-based transform, loading data into a normalized data model, with kaggle dataset context.

Download and extract movie datasets, read csv files with pandas, create varchar staging tables via sqlalchemy, and run an daily etl loading data into staging after deleting today’s records.

Analyze raw data and build a dataframe from staging tables in Python to guide ETL transformation, validating nulls, duplicates, normalization, and currency handling for movies and names datasets.

Learn how to upsert data by using unique constraints to decide insert or update, with created and updated timestamps for auditability in PostgreSQL.

Learn an ETL workflow from staging to load using PostgreSQL, transforming movies, names, and ratings datasets, handling nulls, currency, dates, and list fields before bulk loading.

Explore etl and staging tables with a python demo that loads movies data via batch upserts, builds country, language, genre lookups, and creates many-to-many joins for directors, writers, and actors.

Speed up etl with multi-core parallel loading and chunked dataframes, and run near the database in the same region; use Talend, Informatica, Embulk, Logstash, or Kafka stream.

Explore basic NoSQL concepts, including data partitioning and replication across three nodes A, B, C, creating four shards with a replication factor of two for high availability and eventual consistency.

Explore the CAP theorem for distributed data stores, explaining how only two of three guarantees—consistency, availability, and partition tolerance—can be achieved, with examples of behavior across nodes.

Elasticsearch is a nosql database optimized for search and uses indexes with no joins. Denormalization is essential to fit relational data, favoring one index per requirement for performance and availability.

Elasticsearch enables fast full-text search and analytics for applications like e-commerce, with auto-completion, typo correction, highlighting matches, adjusting relevance, and powerful filtering, sorting, and aggregation.

Explore how Elasticsearch stores data in an index, uses explicit or dynamic mappings, and indexes JSON documents via rest api or clients, with auto-generated or self-defined IDs and update semantics.

Learn how to design an Elasticsearch ETL workflow by selecting relevant movie and people fields, choosing a staging area (PostgreSQL), and building an ETL pipeline from staging to Elasticsearch.

Explore Elasticsearch query DSL as a powerful JSON-based tool for searching data, using leaf and compound clauses to combine conditions with boolean logic in Python.

The lecture demonstrates an Elasticsearch ETL pipeline from a normalized database to staging, then to Elasticsearch, with optional index creation, data aggregation, and basic search validation via Kibana.

Learn how business teams rely on multiple oltp systems, why analysts use olap and data warehouses, and how etl pipelines unify data for actionable insights and secure reporting.

Explore data warehousing concepts, including three-tier architecture, Kimball vs Inmon models, ETL, data marts, and secure access controls, to support fast, secure analytical querying.

Classify fields as facts or dimensions and explore transactional, periodic snapshot, and accumulating fact tables with examples. Learn slow and changing dimensions, SCD types 1–6, and mini-dimensions with surrogate keys.

Explore OLAP cubes in data warehouses and data marts that use facts and dimensions for fast analysis, with roll up, drill down, slicing, and dicing.

Explore the choice between on premise and cloud data warehouses, weighing construction costs, maintenance, and speed to insight, with cloud providers like Google Cloud and Redshift.

Explore ETL techniques for loading data into a data warehouse, from database links and stored procedures to python scripts and csv from cloud storage, plus kafka streaming.

See how to perform etl from oltp to data warehouse using dummy procurement data; apply Kimball approach to build a data mart in BigQuery and visualize with Data Studio.

Explore building a PostgreSQL data warehouse with a star schema, ETL pipelines in Python, staging, and data marts. Learn to use views and materialized views for secure, efficient data access.

Use BigQuery as a data warehouse with Logstash for incremental streaming loading from PostgreSQL. Learn setup steps for credentials and required streaming prerequisites.

Explore olap cube operations such as roll up, drill down, slicing, and dicing. Use BigQuery or PostgreSQL to analyze vendor distribution by invoice month, vendor name, and invoice payment status.

Explore the three v's of big data and how CPU, memory, and storage govern processing of vast, varied data, with a focus on minimizing data shuffling in distributed systems.

Filter e-commerce logs by city to build campaign insights and reveal why splitting data across coworkers fails due to environment and timing, advocating single-machine processing.

Process large 200 gb logs by loading data in chunks with pandas, filter by city, and count category occurrences to build a compact dictionary.

Explore the Hadoop ecosystem and Spark, highlighting HDFS, MapReduce, YARN, and Hadoop Common. Describe MapReduce steps (map, shuffle, reduce), in-memory Spark processing, and higher-level abstractions like Pig Latin and Hive.

Explore Spark as a fast big data framework with libraries for data analysis, machine learning, graph analytics, and streaming, and see when ETL with Spark suits large datasets over Hadoop.

Learn Spark programming with Scala on the Java Virtual Machine, focusing on functional programming, pure functions, and lazy evaluation with dag for distributed data processing.

Discover common data formats for transfer—csv, json, jsonl, xml, and avro—along with schemas and line-delimited records that enable real-time etl with kafka.

Learn to set up pyspark, install java, create a spark session named Hello Spark, read csv and jsonl with header and multiLine, disable truncation, and select fields.

Explore a spark dataframe demo with a jsonl sample, reading into a spark session, inspecting schema, and using udf to derive new fields, while comparing with pandas.

Discover Spark SQL to process data with SQL syntax familiar to RDBMS users. Compare it with PySpark dataframes, create temp views, and run SQL queries via Spark SQL.

Learn to set up a Spark and BigQuery environment on Google Cloud using Python, including Dataproc, Cloud Storage, authentication with a service account, and the required connectors and APIs.

Build a Spark-based etl pipeline that reads gzip json lines from cloud storage, processes movie data with Spark and udf, and loads it into a BigQuery dataset, including movie_details.

Assess the network bottlenecks that slow ETL when transferring spark-based payment history from Google Cloud to BigQuery, and its impact on SLA.

Explore how Spark clusters run on multiple worker nodes coordinated by a master, enabling parallel data processing in cloud environments such as Google Dataproc.

Process big data using a Spark cluster, loading 15 gigabytes of Kaggle csv.gz files via Google Cloud Storage and a Spark Dataproc job that writes to BigQuery.

Explore Google Dataproc, a managed big data cluster with autoscaling for Spark and Hadoop, supporting Solr and Presto, enabling dynamic resource growth, cost control, and easy PySpark job submission.

Data lakes complement data warehouses by storing unstructured data in raw form. Apply schema on read with big data tools like Spark to enable flexible analytics across diverse formats.

Explore schema on read with spark by loading two csv files, one with header and one without, compare inferred and explicit schemas, ensuring correct date and integer types.

Avoid turning data lake into a data swamp by applying metadata, governance, and data quality controls while ingesting, organizing, and securing data for analytics and loading into a data warehouse.

Learn how to use Google data catalog to tag, index, and discover data across BigQuery, PostgreSQL, and cloud storage, with custom tags and metadata.