Module 2: Homework – Fivetran BigQuery ELT

Homework: Fivetran Essentials — Multi-Stage Sync (Google Sheets → Fivetran → Snowflake)

What you’ll practice

  • Creating a Snowflake trial (choose GCP cloud provider) and connecting to Fivetran via Partner Connect.
  • Creating your Fivetran account from Snowflake Partner Connect.
  • Adding a Google Sheets connector (Fivetran forces source selection first).
  • Setting Destination schema and Destination table names (and respecting name rules).
  • Running three sync stages:
    1. Initial load
    2. New rows + edited value
    3. Schema change (add a new column)
  • Verifying results inside Fivetran (no SQL): use Connector → Logs (Rows written) and Connector → Schema (columns).

📦 Deliverables (submit all in one file)

Screenshots (4 total):

  • A. Fivetran connector after Stage 1: Status = Synced and Last sync time visible.
  • B. Connector → Schema after Stage 1: shows your table with 5 columns.
  • C. Fivetran connector after Stage 2 (Synced + Last sync).
  • D. Connector → Schema after Stage 3: shows new sales_channel column (6 columns).

Short answers (1–2 lines each):

  • Exact landing location: Destination name • Schema • Table • Region.
  • Rows written after Stage 1, 2, 3 (from Connector → Logs → latest sync).
  • In Stage 2, which order’s amount_usd changed and to what value?
  • Name of the new column added in Stage 3.
  • (Optional) Explain “auto schema change” in one sentence.

Submit a single PDF/Doc with screenshots + answers.

Files (download, don’t copy/paste)

Import tip (prevents “all in one column”):
In Google Sheets → File → Import → Upload the CSV → set Separator type = Comma and Convert text to numbers and dates = ON → Import data.
If you already pasted and it’s one column: select the column → Data → Split text to columns → Separator: Comma.

00) Create Snowflake (GCP) and connect to Fivetran via Partner Connect (5–10 min)

  1. Sign up for a Snowflake 30-day trial. When asked, choose Google Cloud Platform (GCP) (region can be any classroom-appropriate US region).
  2. In Snowflake’s left nav, go to Data Products → Partner Connect → Fivetran.
  3. Click Connect and fill first name / last name / email for Fivetran.
  4. Snowflake will create these objects in your account and pass details to Fivetran (you’ll see a confirmation):
    • Database: PC_FIVETRAN_DB
    • Warehouse: PC_FIVETRAN_WH (X-Small)
    • User: PC_FIVETRAN_USER (random password)
    • Role: PC_FIVETRAN_ROLE (grants as listed in Snowflake)
  5. Click the Partner Connect “Activate” / “Continue to Fivetran” button to open your new Fivetran account that’s linked to this Snowflake.

If you repeat the lab later for free: use a new Snowflake trial (different email) and repeat these steps.

01) Fivetran onboarding: pick Google Sheets as your Source (2–3 min)

Fivetran will force you to select a source connector first.

  1. Choose Google Sheets.
  2. Click Authorize and sign in with your Google account so Fivetran can read your spreadsheets.

You’ll land on the Google Sheets connector setup screen (the one you described).

02) Prepare the Google Sheet & named range (5–10 min)

  1. Open Google Sheets → Create a spreadsheet named fivetran_lab_<lastname>.
  2. Add a sheet/tab named orders.
  3. Import orders_stage1.csv (File → Import → Upload → Separator=Comma).
  4. Select the full table (headers + data) → Data → Named ranges → name it orders_rng.

You must have a named range. Fivetran syncs exactly that rectangle into one table.

03) Finish the Google Sheets connector form in Fivetran (3–5 min)

On the Google Sheets connector screen:

  • Spreadsheet URL / picker: paste or pick your fivetran_lab_<lastname> sheet.
  • Range selector: choose orders_rng.
  • Destination schema: enter a valid schema name (e.g., google_sheets or class_google_sheets).
    • Must be letters/numbers/underscores only; case may show uppercased in Snowflake.
  • Destination table: enter a valid table name (e.g., orders).
    • Must be letters/numbers/underscores, cannot start with a number.
    • If you see “Invalid table name”, fix characters/leading digits and try again.
  • Authentication method:
    • Authorize with User account (simplest) — lets Fivetran read any sheet your user can access.
    • Or Authorize Service Account — then share the sheet with the service account email Fivetran shows.
  • Data processing location: US (fine).
  • Fivetran processing cloud provider: GCP (fine).
  • Click Authorize (if not already), then Save & Test, then Start initial sync.

04) Verify the Snowflake destination is linked (Partner Connect) (1–2 min)

Because you came from Partner Connect, your Fivetran Destination should already exist and be Snowflake. You do not need to manually add a second destination.

If you do open Destinations → (Snowflake) and see a blank form asking for:

  • Host, Port (443), User, Database, Auth, Role…

…that’s the manual path. Skip it if Partner Connect already linked your account.
If you must fill it manually (edge case), use the Partner Connect objects:

  • Host: your Snowflake URL (e.g., abc-xy123.gcp.snowflakecomputing.com)
  • User: PC_FIVETRAN_USER
  • Database: PC_FIVETRAN_DB
  • Role (optional): PC_FIVETRAN_ROLE
  • Auth: choose PASSWORD and paste the autogenerated password (preferably from the Partner Connect handoff).
    • If the form insists on KEY_PAIR, switch the dropdown to PASSWORD. Only use key-pair auth if you know how to generate and load keys.

05) Stage 1 — Initial sync (5 min)

  • In the Google Sheets connector, wait until Status: Synced.
  • Go to Connector → Logs → latest sync and record Rows written = 12.
  • Go to Connector → Schema and confirm your table shows 5 columns:
    • order_id, order_date, customer, region, amount_usd

📸 Screenshot A: Connector (Synced + Last sync)
📸 Screenshot B: Connector → Schema showing 5 columns

06) Stage 2 — Add rows + edit a value, then Sync now (5–10 min)

  1. In Google Sheets, replace the tab data with orders_stage2.csv (use Import with Separator=Comma).
  2. Confirm the named range orders_rng still covers all rows.
  3. In Fivetran, click Sync now.
  4. In Connector → Logs → latest sync, confirm Rows written = 18.
  5. You also changed order O-1003’s amount_usd → 179.00 — include that in your answers.

📸 Screenshot C: Connector after Stage 2 (Synced + Last sync)

07) Stage 3 — Add a new column + more rows (schema change), then Sync now (10 min)

  1. In Google Sheets, replace the tab data with orders_stage3.csv (this file adds sales_channel).
  2. Important: Edit the named range orders_rng so it includes the new rightmost column.
  3. In Fivetran, click Sync now.
  4. In Connector → Logs → latest sync, confirm Rows written = 24.
  5. In Connector → Schema, confirm the new sales_channel column appears (now 6 columns).

📸 Screenshot D: Connector → Schema showing the sales_channel column

08) What you proved

  • Initial load creates a Snowflake table from your Sheet header.
  • Edits & new rows in Sheets replicate on the next sync.
  • Auto schema change: adding a column (and expanding the named range) adds the column to Snowflake on the next sync.
  • You can Sync now, Pause/Resume, and adjust frequency from the connector.

Troubleshooting (fast fixes)

  • “Invalid table name”: Use only letters/numbers/underscores; don’t start with a number (e.g., orders_2025, not 2025_orders).
  • Everything landed in one column in Sheets: re-import the CSV via File → Import with Separator=Comma, or use Data → Split text to columns.
  • New column didn’t show (Stage 3): expand orders_rng to include the new column, then Sync now.
  • No row count change: confirm you replaced the data with the correct Stage file (including header) and that the named range covers all rows.
  • Destination asking for Host/User/Auth: you likely opened the manual destination page. Because you used Partner Connect, your destination is already linked—go back to the connector. If you truly must fill it: Host = your Snowflake URL; User = PC_FIVETRAN_USER; Database = PC_FIVETRAN_DB; Role = PC_FIVETRAN_ROLE; Auth = PASSWORD with the Partner Connect password.
  • Service Account auth for Sheets: if you pick that method, share the Sheet with the service account email Fivetran shows you.

Stage CSVs (reference only)

Students should download the files above and import them—no copy/paste required.
For convenience, here are the shapes:

  • Stage 1: 12 rows, 5 columns
  • Stage 2: 18 rows, 5 columns (O-1003 amount changed)
  • Stage 3: 24 rows, 6 columns (adds sales_channel)