Lesson 1: Exam Orientation and ETL, ELT and ETLT

Welcome: what this course is and is not

This course prepares you for Google Cloud's Associate Data Practitioner certification exam. Before you start, note four points:

• The exam guide belongs to Google. It is free to download from the exam's page on Google Cloud's certification site. This course does not reproduce it. Treat it as your master checklist.
• Check the logistics yourself. Exam length, price, number of questions, languages and delivery options can change. Confirm them on Google's official exam page before you book.
• This course is independent. Google does not endorse it, and it is not affiliated with Google.
• No pass is promised. The course teaches every item in the guide. Your result depends on your own preparation and hands-on practice.

Task 1: Find the official exam guide and exam page

DO this now:

1. Search for "Google Cloud Associate Data Practitioner certification" and open the result on cloud.google.com/learn/certification.
2. On that page, note the current exam length, price, question format and languages.
3. Download the exam guide PDF from the page. The current version is v1.0.
4. Save the guide where you can tick off items as each level covers them.

Task 2: Learn the four sections and their weights

The guide splits the exam into four sections. Spend your study time roughly in line with these weights:

DO: Write the four weights from memory. Section 1 is the largest and Section 3 is the smallest.

The guide also says candidates should have a basic understanding of cloud computing concepts like IaaS, PaaS and SaaS. Task 4 covers this.

Task 3: Compare ETL, ELT and ETLT

Item 1.1 asks you to differentiate between data manipulation methodologies (e.g., ETL, ELT, ETLT). All three use the same building blocks:

• Extract (E): copy data out of a source system, such as an on-premises database or a CSV export.
• Transform (T): clean, reshape, join, mask or aggregate the data.
• Load (L): write the data into the target, such as BigQuery.

The main difference is where and when the transformation happens.

ETL: Extract, Transform, Load

1. Extract data from the source.
2. Transform it before it reaches the target, in a separate processing tool.
3. Load only the finished, clean data.

Google's BigQuery documentation says ETL suits you if you already have a transformation process in place, or if you want to reduce resource usage in BigQuery. It names Cloud Data Fusion as a tool that can help with ETL.

Spreadsheet comparison: you tidy a file in Excel and then paste only the clean result into the shared master workbook.

ELT: Extract, Load, Transform

1. Extract data from the source.
2. Load the raw data straight into the target, such as BigQuery.
3. Transform it inside the target, usually with SQL.

Google generally recommends ELT to most customers who use BigQuery. The raw data stays available, so you can transform it again later when requirements change. The transformation also runs on BigQuery's own scalable compute. Here is an example of the transform step, written in BigQuery SQL:

CREATE OR REPLACE TABLE sales.clean_orders AS
SELECT
  CAST(order_id AS INT64) AS order_id,
  LOWER(TRIM(customer_email)) AS customer_email,
  SAFE_CAST(amount AS NUMERIC) AS amount
FROM sales.raw_orders
WHERE order_id IS NOT NULL;

ETLT: Extract, Transform, Load, Transform

ETLT is a hybrid of the two:

1. Extract data from the source.
2. Apply a light transformation before loading, for example masking personal data, dropping unneeded columns or fixing encodings.
3. Load the lightly processed data into the target.
4. Run the main transformations inside the target, such as joins, aggregations and business logic.

Use ETLT when part of the work must happen before the data lands, often for privacy or compliance reasons, but the heavy work runs better in the warehouse.

Decision checklist

Work through these questions for any scenario:

• Must the data be changed before it enters the cloud target, for example to remove sensitive fields? If so, choose ETL or ETLT.
• Is the change before loading minor, with heavy analytical work best done in BigQuery? Choose ETLT.
• Is there an existing external transformation process, or a need to keep BigQuery compute low? Choose ETL.
• Do you want raw data kept and SQL-based transformation in the warehouse? Choose ELT.

Task 4: Classify IaaS, PaaS and SaaS

The three models differ in how much the provider manages for you:

• Infrastructure as a service (IaaS): the provider supplies virtual machines, storage and networking. You manage the operating system, runtime and software. Example: Compute Engine virtual machines.
• Platform as a service (PaaS): the provider also manages the operating system and runtime. You bring your code or data. Examples: App Engine, and managed databases such as Cloud SQL.
• Software as a service (SaaS): a complete application that you use through a browser, with nothing underneath to manage. Example: Google Workspace (Gmail, Docs, Sheets).

Sources classify fully managed, serverless data services such as BigQuery differently: some call them PaaS and others SaaS. In every case the point is the same: you do not manage servers. DO: for any service, ask yourself who patches the operating system. If you do, it is IaaS.

What the exam asks about this

• Scenario questions describe a constraint, such as privacy rules, an existing toolset, BigQuery cost or a need to keep raw data. You pick ETL, ELT or ETLT. Look for clues about where the transformation must happen.
• Wrong answers are often the right steps in the wrong place, for example masking personal data after loading when the rules forbid it from reaching the cloud unmasked.
• Expect basic questions on cloud models, such as which model leaves you managing the operating system.

Key takeaways

• Download Google's free exam guide and check exam logistics on Google's own exam page.
• Section weights: Preparation and Ingestion ~30%, Analysis and Presentation ~27%, Pipeline Orchestration ~18%, Data Management ~25%.
• ETL transforms before loading. ELT loads raw data and then transforms in the warehouse, and Google generally recommends it for BigQuery. ETLT does a light transformation before loading and the main one after.
• IaaS means you manage the OS (Compute Engine). PaaS means you bring code or data (App Engine, Cloud SQL). SaaS is a finished application (Google Workspace).