A new cohort runs roughly January–April every year. For the current cohort's exact start date and registration link, check the course repo README.

• Register via the link in the course repo before the cohort starts.
• Join the course Telegram channel for announcements.
• Join DataTalks.Club's Slack and the #course-data-engineering channel.

To get the most out of this course, you should have:

• Basic coding experience
• Familiarity with SQL
• Experience with Python (helpful but not required)

No prior data engineering experience is necessary. See Readme on GitHub.

Yes, even if you don't register, you're still eligible to submit the homework.

Be aware, however, that there will be deadlines for turning in homeworks and the final projects. So don't leave everything for the last minute.

You don't need a confirmation email. You're accepted. You can start learning and submitting homework without registering. Registration was just to gauge interest before the start date.

Get the basic environment ready ahead of time:

• Google Cloud account (free trial — see the GCP setup FAQ).
• Google Cloud SDK (gcloud CLI).
• Python 3 — install via your OS package manager or uv (recommended for managing Python versions and project venvs).
• Terraform.
• Git.

Then look over the prerequisites and syllabus to see if you're comfortable with the topics.

DataTalks.Club runs several Zoomcamps every year. The roster and approximate timing:

• Data Engineering: Jan – Apr
• Stock Market Analytics: Apr – May
• MLOps: May – Aug
• LLM: Jun – Sep
• Machine Learning: Sep – Jan

For the up-to-date list and current dates, see the DataTalks.Club guide to free courses.

Each Zoomcamp has one "live" cohort per year — that's the only window in which you can earn the certificate. Outside the live cohort you can still take the course self-paced (materials stay open), but no certificate.

Most of the syllabus stays consistent year to year, but the tooling for some modules evolves between cohorts — especially the orchestrator (Module 2) and analytics engineering (Module 4) tools. For the current cohort's exact tooling and any new lessons, check the course repo README and the cohorts/ folder — it lists what changed for each year.

Past tool changes have included Airflow → Prefect → Mage → Kestra for orchestration. Old cohort materials remain available, so you can still follow them if you find an older video easier to learn from, but the homework is graded against the current cohort's stack.

Yes, we will keep all the materials available, so you can follow the course at your own pace after it finishes.

You can also continue reviewing the homeworks and prepare for the next cohort. You can also start working on your final capstone project.

Yes, the Slack channel remains open and you can ask questions there. However, always search the channel first and check the FAQ, as most likely your questions are already answered here.

You can also tag the bot @ZoomcampQABot to help you conduct the search, but don’t rely on its answers 100%.

The canonical place to find the videos is the DATA ENGINEERING ZOOMCAMP main playlist. It always points at the current cohort's lessons.

The course repo README embeds the up-to-date video links per module — that's the easiest way to navigate to the right video for each lesson.

Per-cohort playlists (with office-hour recordings and any cohort-specific extras) are linked from the cohorts/ folder in the repo.

It depends on your background and previous experience with modules. It is expected to require about 5 - 15 hours per week.

You can also calculate it yourself using this data and then update this answer.

Yes! Every "Office Hours" will be recorded and available a few minutes after the live session is over; so you can view (or rewatch) whenever you want.

The display name listed on the leaderboard is an auto-generated randomized name. You can edit it to be a nickname or your real name if you prefer. Your entry on the Leaderboard is the one highlighted in light green.

The Certificate name should be your actual name that you want to appear on your certificate after completing the course.

The "Display on Leaderboard" option indicates whether you want your name to be listed on the course leaderboard.

No, as long as you complete the peer-reviewed capstone projects on time, you can receive the certificate. You do not need to do the homeworks if you join late, for example.

No, you can only get a certificate if you finish the course with a “live” cohort. We don't award certificates for the self-paced mode. The reason is you need to peer-review capstone(s) after submitting a project. You can only peer-review projects at the time the course is running.

Deadlines are published per cohort. Find them in:

• The current cohort's folder in the course repo.
• The course website (link is in the cohort folder's README).
• The course Google Calendar (also linked from the cohort folder).

Also keep an eye on @Au-Tomator posts in Slack for extension announcements. The submission form may show an updated deadline if instructors have changed it.

No, late submissions are not allowed. However, if the form is still open after the due date, you can still submit the homework. Confirm your submission by checking the date-timestamp on the Course page. Ensure you are logged in.

Answer: In short, it’s your repository on GitHub, GitLab, Bitbucket, etc.

In long, your repository or any other location where you have your code, and a reasonable person would look at it and think, yes, you went through the week and exercises. Think of it like a portfolio you could present to an employer.

When you set up your account, you're automatically assigned a random display name (like "Lucid Elbakyan"). To see your display name, change it, or set your certificate name:

1. Go to your enrollment / profile page on the cohort's course site (https://courses.datatalks.club/de-zoomcamp-<year>/enrollment). The exact URL for the current cohort is linked from the cohort's folder in the course repo.
2. Log in.
3. Your display name is shown — edit it if you want.
4. Set your certificate name to your real name (this is what will appear on the certificate).

We will probably have some calls during the Capstone period to clear some questions, but it will be announced in advance if that happens.

See Google Calendar

• Star the repository.
• Share it with friends if you find it useful.
• Create a pull request (PR) if you can improve the text or structure of the repository.
• Update this FAQ.

Yes to both! Check out this document: Awesome Data Engineering Resources

You will have two attempts for a project.

• If the first project deadline is over and you’re late, or you submit the project and fail the first attempt, you have another chance to submit the project with the second attempt.

After you create a GitHub account, clone the course repo to your local machine using the process outlined in this video:

Git for Everybody: How to Clone a Repository from GitHub.

Having this local repository on your computer will make it easy to access the instructors’ code and make pull requests if you want to add your own notes or make changes to the course content.

You will probably also create your own repositories to host your notes and versions of files. Here is a great tutorial that shows you how to do this:

How to Create a Git Repository.

Remember to ignore large databases, .csv, and .gz files, and other files that should not be saved to a repository. Use .gitignore for this:

.gitignore file.

Important:

NEVER store passwords or keys in a git repo (even if the repo is set to private). Put files containing sensitive information (.env, secret.json, etc.) in your .gitignore.

This is also a great resource: Dangit, Git!?!

There will be two announcements in the course Telegram and Slack channels:

1. To verify your name is correct on the certificate (edit it in your course profile under "Edit Course Profile" if not).
2. When grading is complete and the certificate is ready.

After the second announcement, log into your enrollment page on the cohort's course site (https://courses.datatalks.club/de-zoomcamp-<year>/enrollment — the current cohort's URL is in the course repo) and follow the instructions in certificates.md to generate the certificate document.

After you submit a homework, it's graded based on the number of questions in that assignment. The points you earn appear at the top of the homework page. The leaderboard for the current cohort sums up:

• Homework points (varies per assignment by number of questions).
• FAQ contributions (max 1 point per week).
• Learning in Public (1 point per link, max 7 per week).

The leaderboard URL for the current cohort is at https://courses.datatalks.club/de-zoomcamp-<year>/leaderboard — the exact link is in the cohort's folder in the course repo.

For a walkthrough, see this explainer video.

Python 3.10 or 3.11 is a safe default — it works with the libraries used across the course (pandas, SQLAlchemy, dbt, dlt, PySpark with recent Spark releases, etc.).

If you're following older recorded videos that use Python 3.9, that still works for everything except the very latest library versions; troubleshooting against the videos is easier on the version they use.

If a specific module uses a stricter requirement, the course repo's module README will say so.

You have three good options. Pick whichever suits you:

1. Local machine (laptop / PC). Easiest if you're already comfortable with Docker locally. Windows users should use WSL2 from the start.
2. GitHub Codespaces. A free Linux dev environment with Docker, Python, and many CLI tools pre-installed. Useful if your laptop is underpowered, or if you switch between home and office machines. Ports for things like Kestra/pgAdmin are exposed via Codespaces' forwarded URL — not http://localhost.
3. Google Cloud VM. The course videos demonstrate this setup. Useful if you want a persistent remote environment to SSH into, especially while staying logged in across machines.

You don't need both Codespaces and a GCP VM — pick one. You will need a GCP account regardless because the course uses BigQuery (in Module 3 and the project), but GCP for compute is optional.

This issue occurs when attempting to connect to a GCP VM using VSCode on a Windows machine. You can resolve it by changing a registry value in the registry editor.

Open the Run command window:

• Use the shortcut keys Windows + R, or
• Right-click "Start" and click "Run".

Open the Registry Editor:

• Type regedit in the Run command window, then press Enter.

Change the registry value:

• Navigate to HKEY_CURRENT_USER\Software\Microsoft\Command Processor.
• Change the "Autorun" value from "if exists" to a blank.

Alternatively, you can delete the saved fingerprint within the known_hosts file:

In Windows, locate the file at C:\Users\<your_user_name>\.ssh\known_hosts and remove the entry for the server.

For uniformity across the cohort. The course uses BigQuery, which is GCP-only, and most students already have a Google account that works for sign-up. The concepts (data warehouse, object storage, IaC) translate to AWS/Azure, but the lessons are recorded against GCP.

You can use a different cloud for your project — see the AWS/Snowflake/Azure FAQ for the tradeoffs.

Several modules use shell scripts (*.sh) for setup or runtime tasks. Most Windows users running them outside WSL will hit issues — Git Bash and MINGW64 are not always sufficient. Set up a WSL environment from the start to avoid getting blocked partway through the course.

Try to solve it yourself first

• Read the error message carefully — it usually includes a line number, a stack trace, and a description of what went wrong.
• Search the message: copy the most specific part of the error (not the whole stack trace) into Google. The format <tool> <error message> works well, e.g. pgcli error column c.relhasoids does not exist.
• Check the official documentation of the tool you're using.
• Use Ctrl+F in this FAQ and in Slack channel pinned messages.
• Restart the process / container / shell / VM and try once more — many transient errors resolve this way.
• If you suspect the install is broken, uninstall first, then reinstall. Reinstalling on top of a broken install rarely helps.

Asking for help in Slack / forums

When the troubleshooting steps don't help and you need another pair of eyes, include enough info that someone can actually help without going back and forth:

• Operating system and version (e.g. Windows 11 + WSL Ubuntu 24.04, Mac M2, Linux Ubuntu 22.04).
• Which lesson / video you're following, and which command failed.
• The exact command and the exact error — paste both as text inside triple-backtick code blocks. Don't paste screenshots of text.
• What you've already tried. If you skip this, helpers' first suggestions will be the things you already tried.
• Stay in one thread. Reply to your own question; don't open a new post for a follow-up.

If the same problem recurs, post in the same thread with what changed in your environment since last time.

Help others by contributing back

If your problem isn't yet covered in this FAQ, consider opening a PR so the next student doesn't have to debug it from scratch.

For those who wish to use the backslash as an escape character in Git Bash for Windows, type the following in the terminal:

bash.escapeChar=\

(Note: There is no need to include this in your .bashrc file.)

Error:

Makefile:2: *** missing separator.  Stop.

Solution:

Tabs in documents should be converted to Tab instead of spaces. Follow this stack.

If you’re running Linux on Windows Subsystem for Linux (WSL) 2, you can open HTML files from the guest (Linux) with any Internet Browser installed on the host (Windows). Just install wslu and open the page using wslview:

wslview index.html

You can customize which browser to use by setting the BROWSER environment variable first. For example:

export BROWSER='/mnt/c/Program Files/Firefox/firefox.exe'

Instruction on how to store secrets that will be available in GitHub Codespaces. See Managing your account-specific secrets for GitHub Codespaces - GitHub Docs.

This tutorial shows you how to set up the Chrome Remote Desktop service on a Debian Linux virtual machine (VM) instance on Compute Engine. Chrome Remote Desktop allows you to remotely access applications with a graphical user interface.

Running pgAdmin in Docker behind GitHub Codespaces reverse proxy can result in a blank screen after logging in. This is typically due to session or proxy issues when running behind Codespaces' reverse proxy. Resolve with two options:

Option 1: Set the following environment variables to configure the proxy handling:

PGADMIN_CONFIG_PROXY_X_HOST_COUNT: 1
PGADMIN_CONFIG_PROXY_X_PREFIX_COUNT: 1

This allows pgAdmin to work properly with Codespaces' reverse proxy.

Option 2 (if the gray screen persists): Try disabling enhanced cookie protection and CSRF checks, and adjusting cookie settings:

PGADMIN_CONFIG_ENHANCED_COOKIE_PROTECTION: "False"
PGADMIN_CONFIG_WTF_CSRF_CHECK_DEFAULT: "False"
PGADMIN_CONFIG_WTF_CSRF_ENABLED: "False"
PGADMIN_CONFIG_SESSION_COOKIE_SAMESITE: "'None'"
PGADMIN_CONFIG_SESSION_COOKIE_SECURE: "True"

This configuration relaxes session and CSRF settings and is known to resolve rendering issues when using pgAdmin in Docker inside Codespaces.

Notes:

• Option 1 is safer as it preserves CSRF protection.
• Option 2 should be used only if the blank screen persists after Option 1.
• Always restart the pgAdmin container after changing environment variables.

• Delete intermediate data you saved on the VM during ETLs (raw extracts, parquet outputs you've already pushed elsewhere, downloaded archives).
• Kill processes still holding deleted files (their disk space isn't reclaimed until the process exits — lsof | grep deleted shows them).
• Install ncdu (sudo apt install ncdu) and use it to walk the filesystem visually:

  sudo ncdu /
  

• Common culprits: Docker images and volumes (docker system prune -af --volumes), pipeline working/cache directories, and old logs (sudo journalctl --vacuum-time=7d).
• If a pipeline tool's cache keeps regrowing (e.g. orchestrator working dir, dbt target/, dlt staging), consider disabling caching or pruning it on a schedule rather than only when the disk fills.

You need to redefine the Python environment variable to that of your user account.

To resolve this, ensure that your config file is in C/User/Username/.ssh/config.

Install and upgrade nbconvert

First, ensure nbconvert is installed and upgraded:

pip install nbconvert --upgrade

Resolve 'Failed to spawn: nbconvert' error (uv-based workflow)

If the issue persists, add nbconvert support to uv and then run nbconvert:

uv add jupyter nbconvert
uv run jupyter nbconvert --to=script notebook.ipynb

Alternative: Convert Jupyter Notebook to Python Script (nbconvert)

You can also convert directly using nbconvert without uv:

python3 -m jupyter nbconvert --to=script <your_notebook.ipynb>

Replace <your_notebook.ipynb> with the actual notebook filename, e.g. notebook.ipynb.

If you keep getting errors with nbconvert after executing:

jupyter nbconvert --to script <your_notebook.ipynb>

You could try converting your Jupyter notebook using another tool called Jupytext. Jupytext is an excellent tool for converting Jupyter Notebooks to Python scripts, similar to nbconvert.

1. Install Jupytext

   pip install jupytext
   

2. Convert your Notebook to a Python script

   jupytext --to py <your_notebook.ipynb>
   

No. The officially recommended way now is uv for both installing Python and managing project dependencies.

Quick start:

# Install uv (one-line installer; see https://docs.astral.sh/uv/ for your OS):
curl -LsSf https://astral.sh/uv/install.sh | sh

# Install a Python version:
uv python install 3.11

# Create a project venv:
uv venv --python 3.11
source .venv/bin/activate    # Linux / macOS
.venv\Scripts\activate       # Windows

# Add packages:
uv add pandas sqlalchemy "psycopg[binary]"

uv replaces the parts of Anaconda we previously used: Python version management, virtual environments, and dependency installation. It's faster, smaller, and has no licensing concerns.

Linux, macOS, and Windows all work. Students in the most recent cohorts have completed the course on all three. Linux is the smoothest by default.

On Windows, install WSL2 and run everything inside a WSL distro from the start. Git Bash and MINGW64 are not always sufficient for the shell scripts used in later modules.

Yes. The capstone project is graded on creating a data pipeline and producing a visualization — it doesn't mandate any specific cloud. A few things to keep in mind:

• The lessons are recorded against GCP, so you'll need to translate steps yourself.
• You may need to explain your choice during peer review.
• Fewer fellow students will be using AWS/Azure, so help in Slack may be slower.

If you only want to run the course locally without any cloud, you can do that for everything except Module 3's BigQuery homework, which requires GCP.

No. GCP offers a free trial with $300 in credits for new accounts. The course materials fit comfortably within that budget if you destroy unused resources (VMs, datasets, buckets) after each module. Check your billing dashboard daily, especially after spinning up Compute Engine VMs.

To sign up for the free trial you need a valid credit/debit card; GCP uses it to verify identity but doesn't charge it without your consent.

GCP has two free options. They are not equivalent for this course:

• Free Trial ($300 credit, 90 days). Required for the course — gives you VMs, GCS buckets, and full BigQuery functionality.
• Sandbox (free, no credit card). Limited services. It does not include VMs or GCS, and BigQuery features are restricted, so you cannot complete the course on Sandbox alone.

Use the Free Trial.

GCP isn't available in some countries, and some cards are rejected even where it is. Workarounds students have used:

• Try a different card. Cards from some banks (e.g. Kazakhstan-based Kaspi) sometimes don't work; cards from other banks/countries (e.g. TBC in Georgia) do.
• Pyypl and similar virtual cards have worked for some.
• If you can't get a GCP account at all, you can still complete most of the course locally — only Module 3's homework strictly requires BigQuery.

When attempting to download the 2021 data from the TLC website, you may encounter the following error:

ERROR 403: Forbidden

We have a backup, so use it instead: nyc-tlc-data

So the link should be yellow_tripdata_2021-01.csv.gz.

Note: Make sure to unzip the &quot;gz&quot; file (no, the "unzip" command won’t work for this).

In this video, the data file is stored as output.csv. If the file extension is csv.gz instead of csv, it won't store correctly.

To handle this:

1. Replace csv_name = "output.csv" with the file name extracted from the URL. For example, for the yellow taxi data, use:

   url = "https://github.com/DataTalksClub/nyc-tlc-data/releases/download/yellow/yellow_tripdata_2021-01.csv.gz"
   csv_name = url.split("/")[-1]
   

2. When you use csv_name with pandas.read_csv, it will work correctly because pandas.read_csv can directly read files with the csv.gz extension.

Example:

import pandas as pd

url = "https://github.com/DataTalksClub/nyc-tlc-data/releases/download/yellow/yellow_tripdata_2021-01.csv.gz"
csv_name = url.split("/")[-1]

data = pd.read_csv(csv_name)

Yellow Trips: Data Dictionary

Green Trips: Data Dictionary - LPEP Trip Records May 1, 2018

You can gunzip the downloaded csv.gz file from the command line. The result is a CSV file which can be imported with pandas using pd.read_csv() as shown in the videos.

gunzip green_tripdata_2019-09.csv.gz

or you can read the gzipped file directly with pandas

Solution for Using Parquet Files Directly in Python Script ingest_data.py

1. In the def main(params), add this line:

   parquet_name = 'output.parquet'
   

2. Edit the code which downloads the files:

   os.system(f"wget {url} -O {parquet_name}")
   

3. Convert the downloaded .parquet file to CSV and rename it to csv_name to keep it relevant to the rest of the code:

   df = pd.read_parquet(parquet_name)
   df.to_csv(csv_name, index=False)
   

If you encounter the error "wget is not recognized as an internal or external command," wget needs to be installed.

This error may also cause messages like "No such file or directory: 'output.csv.gz'."

Installation Instructions:

• On Ubuntu:

  sudo apt-get install wget
  

• On macOS:

  Use Homebrew:

  brew install wget
  

• On Windows:

  Use Chocolatey:

  choco install wget
  

  Alternatively, download a binary from GnuWin32 and place it in a location that is in your PATH (e.g., C:/tools/).

Alternative Windows Installation:

1. Download the latest wget binary for Windows from eternallybored.
2. If you downloaded the zip, extract all files (use 7-zip if the built-in utility gives an error).
3. Rename the file wget64.exe to wget.exe if necessary.
4. Move wget.exe to your Git\mingw64\bin\ directory.

Python Alternative:

• Use the Python wget library:

  First, install using pip:

  pip install wget
  

• Use it with Python:

  python -m wget
  

You can also paste the file URL into your web browser to download normally, then move the file to your working directory.

Additional Recommendation:

Consider using the Python library requests for loading gz files.

Firstly, make sure that you add ! before wget if you’re running your command in a Jupyter Notebook or CLI. Then, you can check one of these two things (from CLI):

• Using the Python library wget installed with pip:

  python -m wget <url>
  

• Use the usual command and add --no-check-certificate at the end:

  !wget <website_url> --no-check-certificate
  

The download may fail not because of SSL verification but because the network blocks requests to the CloudFront domain. In some networks, requests to the dataset URL are redirected to a block page such as https://blocked.sbmd.cicc.gov.ph/.

Solution 1 — Skip certificate check (SSL verification disabled)

!wget --no-check-certificate https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-11.parquet

Solution 2 — If your network blocks CloudFront entirely, connect to a VPN and run the original command again:

!wget https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-11.parquet

Using a VPN successfully bypassed the network block.

Make sure you're able to start the Docker daemon. Check the issue immediately as described below:

• Ensure the Docker daemon is running.

• Update WSL in PowerShell with the following command:

  wsl --update
  

If you get this error:

docker: error during connect: In the default daemon configuration on Windows, the docker client must be run with elevated privileges to connect.: Post "http://%2F%2F.%2Fpipe%2Fdocker_engine/v1.24/containers/create": open //./pipe/docker_engine: The system cannot find the file specified.
See 'docker run --help'.

To resolve it on Windows, follow these guidelines based on your version:

Windows 10 Pro / 11 Pro Users:

• Ensure Hyper-V is enabled, as Docker can use it as a backend.
• Follow the Enable Hyper-V Option on Windows 10 / 11 tutorial.

Windows 10 Home / 11 Home Users:

• The 'Home' version doesn't support Hyper-V, so use WSL2 (Windows Subsystem for Linux).
• Refer to install WSL on Windows 11 for detailed instructions.

If you encounter the "WslRegisterDistribution failed with error: 0x800701bc" error:

• Update the WSL2 Linux Kernel by following the guidelines at GitHub: WSL Issue 5393.

Whenever a docker pull is performed (either manually or by docker-compose up), it attempts to fetch the given image name from a repository. If the repository is public, the fetch and download occur without any issues.

For instance:

docker pull postgres:13
docker pull dpage/pgadmin4

Be Advised: The Docker images we'll be using throughout the Data Engineering Zoomcamp are all public, unless otherwise specified. This means you are not required to perform a docker login to fetch them.

If you encounter the message:

docker login': denied: requested access to the resource is denied.

This is likely due to a typo in your image name. For instance:

$ docker pull dbpage/pgadmin4

This command will throw an exception:

Error response from daemon: pull access denied for dbpage/pgadmin4, repository does not exist or may require 'docker login': denied: requested access to the resource is denied

This occurs because the actual image name is dpage/pgadmin4, not dbpage/pgadmin4.

How to fix it:

$ docker pull dpage/pgadmin4

Extra Notes: In some professional environments, the Docker image may be in a private repository that your DockerHub username has access to. In this case, you must:

1. Execute:

   $ docker login
   

2. Enter your username and password.
3. Then perform the docker pull against that private repository.

When you start the Postgres container with a host-bind mount (-v $(pwd)/ny_taxi_postgres_data:/var/lib/postgresql/data), the Postgres process inside the container runs as the postgres user (UID 999) and tries to chown the data dir. If the host filesystem doesn't permit that — common on macOS, Windows file systems mounted into WSL, certain Linux configurations, and when your build context picks up the same dir — you'll see one of:

initdb: error: could not change permissions of directory "/var/lib/postgresql/data": Operation not permitted
chown /path/to/ny_taxi_postgres_data: permission denied
docker: Error response from daemon: error while creating mount source path
docker build error checking context: can't stat '/path/to/ny_taxi_postgres_data'
failed to read dockerfile: error from sender: open ny_taxi_postgres_data: permission denied

You may also be unable to delete the host folder later because it's owned by UID 999.

Recommended fix: use a named Docker volume instead of a host-bind mount

Named volumes are managed by Docker and don't have the cross-OS permission problems:

docker volume create --name dtc_postgres_volume_local
docker run -it \
  -e POSTGRES_USER=root -e POSTGRES_PASSWORD=root -e POSTGRES_DB=ny_taxi \
  -v dtc_postgres_volume_local:/var/lib/postgresql/data \
  -p 5432:5432 \
  postgres:16

In docker-compose.yml:

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: root
      POSTGRES_DB: ny_taxi
    volumes:
      - "pg-data:/var/lib/postgresql/data"
    ports:
      - "5432:5432"

volumes:
  pg-data:

The volume's data lives inside Docker's storage area (find it with docker volume inspect pg-data).

If you must use a host-bind mount (Linux)

Grant the container access to the directory:

sudo chown -R 999:999 ny_taxi_postgres_data/
# or
sudo chmod -R 755 ny_taxi_postgres_data/

Use 777 only as a last resort and only on local dev paths — it makes the dir world-writable.

To delete a folder that Docker created (now owned by UID 999):

sudo rm -rf ny_taxi_postgres_data/

On macOS specifically

If you see the chown error and you're using Rancher Desktop or another Docker alternative, switch to Docker Desktop. Some non-Docker-Desktop runtimes don't handle the chown into bind mounts.

"directory ... exists but is not empty"

initdb: error: directory "/var/lib/postgresql/data" exists but is not empty

This means the volume already has Postgres data from a previous run with different superuser/password settings. Either:

• Clear the volume and let Postgres re-initialise it: docker volume rm dtc_postgres_volume_local (or docker compose down -v for a compose volume).
• Or use the same POSTGRES_USER / POSTGRES_PASSWORD you used the first time the data was initialised. The POSTGRES_* env vars only take effect on first init — after that they're ignored.

"build error checking context"

If docker build fails with can't stat '.../ny_taxi_postgres_data' or "permission denied" on the data folder, the build context (the directory you ran docker build from) includes the data dir, and the build can't read it.

Either move the data folder out of the build context, or add it to .dockerignore:

ny_taxi_postgres_data/

Even better, use a named volume (above) so the data never lives in your project directory in the first place.

Ensure you are running the latest version of Docker for Windows. Download the updated version from Docker's official site. If the upgrade option in the menu doesn't work, uninstall and reinstall with the latest version.

If Docker is stuck on starting, try switching the containers by right-clicking the docker symbol from the running programs, and switch the containers from Windows to Linux or vice versa.

For Windows 10 / 11 Pro Edition:

• Hyper-V Backend: ensure Hyper-V is enabled by following this tutorial.
• WSL2 Backend: follow the steps detailed in this tutorial.

If you're running a Home Edition, you can still make it work with WSL2 (Windows Subsystem for Linux) by following the tutorial here.

If even after making sure your WSL2 (or Hyper-V) is set up accordingly, Docker remains stuck, you can try the following options:

• Reset to Factory Defaults
• Perform a fresh install.

You may encounter this error:

$ docker run -it ubuntu bash
the input device is not a TTY. If you are using mintty, try prefixing the command with 'winpty'

Solution:

• Use winpty before the Docker command:

  $ winpty docker run -it ubuntu bash
  

• Alternatively, create an alias:

  echo "alias docker='winpty docker'" >> ~/.bashrc
  

  or

  echo "alias docker='winpty docker'" >> ~/.bash_profile
  

Source: Stack Overflow

You may encounter this error:

Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'NewConnectionError('<pip._vendor.urllib3.connection.HTTPSConnection object at 0x7efe331cf790>: Failed to establish a new connection: [Errno -3] Temporary failure in name resolution')': /simple/pandas/

Possible solution:

Run the following command:

winpty docker run -it --dns=8.8.8.8 --entrypoint=bash python:3.9

Mounting host paths on Windows is the single biggest source of week 1 confusion. Symptoms:

• ny_taxi_postgres_data appears empty even though Postgres started.
• Docker: invalid reference format: repository name must be lowercase
• Error response from daemon: invalid mode: \Program Files\Git\var\lib\postgresql\data
• A folder with a weird name like ny_taxi_postgres_data;C is created.

Root cause

Git Bash / MINGW64 mangles Unix-style paths into Windows-style paths before passing them to Docker, and the rules differ depending on quoting, leading slashes, and whether your path contains spaces. The cleanest workaround on Windows is to skip the host-bind mount entirely (see the named-volume FAQ) — but if you need the bind mount, here's what tends to work.

Use a path without spaces

Move your project out of any directory with spaces (e.g. from C:\Users\Alexey Grigorev\git\... to C:\git\...). Many of the path-syntax issues simply go away once the path is clean.

Try these -v syntax variants in order

# 1. Forward slashes, with leading slash on the drive letter:
-v "/c/Users/me/project/ny_taxi_postgres_data:/var/lib/postgresql/data"

# 2. Double leading slashes (some MINGW versions need this):
-v "//c/Users/me/project/ny_taxi_postgres_data:/var/lib/postgresql/data"

# 3. With a colon after the drive letter:
-v "/c:/Users/me/project/ny_taxi_postgres_data:/var/lib/postgresql/data"

# 4. Backslashes in quotes:
-v "c:\Users\me\project\ny_taxi_postgres_data:/var/lib/postgresql/data"

# 5. Use $(pwd) — wrap in quotes:
-v "$(pwd)/ny_taxi_postgres_data:/var/lib/postgresql/data"

Also try winpty

If the command appears to do nothing or hangs:

winpty docker run -it ...

If Docker: invalid reference format: repository name must be lowercase

This usually means the shell didn't substitute $(pwd) properly and inserted a literal \Program Files\Git\... into the path. Use one of the explicit paths above instead of $(pwd).

If you see a folder called ny_taxi_postgres_data;C get created

The volume mount string was misparsed. Delete the bogus folder and retry with //c/... (double leading slash) instead of /c/....

On Mac, just wrap $(pwd) in quotes

docker run -it \
  -e POSTGRES_USER=root -e POSTGRES_PASSWORD=root -e POSTGRES_DB=ny_taxi \
  -v "$(pwd)/ny_taxi_postgres_data:/var/lib/postgresql/data" \
  -p 5432:5432 \
  postgres:16

Last resort: use a named volume

If none of the bind-mount syntaxes work, switch to a named volume. The data still persists, you just don't see it in your project folder:

-v ny_taxi_postgres_data:/var/lib/postgresql/data

This is the recommended approach on Windows.

For setting up Docker on macOS, you have two main options:

1. Download from Docker Website:

   • Visit the official Docker website and download the Docker Desktop for Mac as a .dmg file. This method is generally reliable and avoids issues related to licensing changes.

2. Using Homebrew:

   • Be aware that there can be conflicts when installing with Homebrew, especially between Docker Desktop and command-line tools. To avoid issues:

     • Install Docker Desktop first.
     • Then install the command line tools.

   • Commands:

     brew install --cask docker
     

     brew install docker docker-compose
     

   • For more detailed issues related to brew install, refer to this Issue.

For more details, you can check the article on Setting up Docker in macOS.

Method 1: Docker volume backup

List Docker volumes:

docker volume ls

Backup while the container is running:

docker run --rm \
    -v ny_taxi_postgres_data:/data \
    -v $(pwd):/backup \
    ubuntu tar czf /backup/postgres_backup.tar.gz /data

Restore:

docker run --rm \
    -v ny_taxi_postgres_data:/data \
    -v $(pwd):/backup \
    ubuntu tar xzf /backup/postgres_backup.tar.gz -C /

Method 2: Using pg_dump

Backup:

docker exec -t postgres_container pg_dump -U root -d ny_taxi > ny_taxi_backup.sql

Restore:

docker exec -i postgres_container psql -U root -d ny_taxi < ny_taxi_backup.sql

Method 3: Copying the host directory

When using a host-mounted directory in docker-compose.yaml:

cp -r ./ny_taxi_postgres_data ./ny_taxi_postgres_data_backup

You might have installed Docker via snap. Run the following command to verify:

sudo snap status docker

If you receive the response:

error: unknown command "status", see 'snap help'.

Then uninstall Docker and install it via the official website.

Error message: "Bind for 0.0.0.0:5432 failed: port is already allocated."

Get the network name via:

docker network ls

For more details, refer to the Docker network ls documentation.

Sometimes, when you try to restart a Docker container configured with a network name, the error message appears.

To resolve this issue:

1. If the container is in a running state, stop it using:

   docker stop <container_name>
   

2. Then remove the container:

   docker rm pg-database
   

Alternatively, you can use docker start instead of docker run to restart the Docker container without removing it.

This error means your container is looking for another service by name on a Docker network, but they aren't on the same network. Common variants:

sqlalchemy.exc.OperationalError: could not translate host name "pgdatabase" to address: Name or service not known
Unable to connect to server: could not translate host name 'pg-database' to address: Name does not resolve
network <hash> not found

What's happening

Docker network DNS only resolves service names within the same network. Two reasons it might fail:

1. The ingestion container was started with --network <name> but <name> doesn't match the network compose actually created. By default, docker compose creates a network named after the project directory plus _default (e.g. 2docker_default).

2. Your ingestion script is hardcoded to use a host name like pgdatabase, but the compose service is actually called pgdatabase-1, or you're running the script outside Docker entirely.

3. List networks and confirm the actual name compose created:

   docker network ls
   

   Pass that exact name when running the ingestion container:

   docker run --network=<actual_network_name> taxi_ingest:v001 ...
   

   Or pin the network name in your docker-compose.yml so it doesn't depend on the directory name:

   networks:
     pg-network:
       name: pg-network
   

4. Make the host name in your script match the compose service name. If your service is called pgdatabase, the script should use --pg_host=pgdatabase (when running inside Docker) or --pg_host=localhost (when running on the host).

5. Avoid hostnames with dashes when possible — pgdatabase is more reliable than pg-database across some networks/DNS configs.

6. If docker network ls shows a stale network from a previous run, prune it: docker network prune (after stopping the relevant containers).

Working compose snippet

services:
  pgdatabase:
    image: postgres:16
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: root
      POSTGRES_DB: ny_taxi
    volumes:
      - "pg-data:/var/lib/postgresql/data"
    ports:
      - "5432:5432"
    networks:
      - pg-network

  pgadmin:
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@admin.com
      PGADMIN_DEFAULT_PASSWORD: root
    ports:
      - "8080:80"
    networks:
      - pg-network

networks:
  pg-network:
    name: pg-network

volumes:
  pg-data:

Before starting your VM, you need to enable nested virtualization. Run the following commands based on your CPU:

• For Intel CPU:

  modprobe -r kvm_intel
  modprobe kvm_intel nested=1
  

• For AMD CPU:

  modprobe -r kvm_amd
  modprobe kvm_amd nested=1
  

It’s very easy to manage your Docker container, images, network, and compose projects from VS Code.

• Install the official extension and launch it from the left side icon.

  

• It will work even if your Docker runs on WSL2, as VS Code can easily connect with your Linux.

Use the following command:

docker stop <container_id>

On some versions of Ubuntu, the snap command can be used to install Docker.

sudo snap install docker

## Difference between -i and -t in docker run (-it)

When running containers interactively, Docker provides two commonly used flags:

- `-i` (interactive): keeps the container’s STDIN open, even if it is not attached to a terminal. This allows you to send input to the process inside the container.
  Example:
  ```bash
  echo "print(2+2)" | docker run -i python

• -t (tty): allocates a pseudo-terminal (TTY) for the container. This provides proper terminal formatting (line breaks, colors, prompts). Example:

  docker run -t ubuntu date
  

Using both flags together (-it) gives you both an open input stream and a real terminal interface. This is typically what you want for an interactive shell session:

docker run -it ubuntu bash

In short:

• -i = keep STDIN open
• -t = allocate a TTY
• -it = both, for interactive shells

When you log into PgAdmin and see an empty database, the following solution can help:

Run:

docker-compose up

And at the same time run:

docker build -t taxi_ingest:v001 .

# NETWORK NAME IS THE SAME AS THAT CREATED BY DOCKER COMPOSE
docker run -it \
  --network=pg-network \
  taxi_ingest:v001 \
  --user=postgres \
  --password=postgres \
  --host=db \
  --port=5432 \
  --db=ny_taxi \
  --table_name=green_tripdata \
  --url=${URL}

It's important to use the same --network as stated in the docker-compose.yaml file.

The docker-compose.yaml file might not specify a network, as shown below:

services:
  db:
    container_name: postgres
    image: postgres:17-alpine
    environment:
      ...
    ports:
      - '5433:5432'
    volumes:
      - ...
  pgadmin:
    container_name: pgadmin
    image: dpage/pgadmin4:latest
    environment:
      ...
    ports:
      - '8080:80'
    volumes:
      - ...

volumes:
  vol-pgdata:
    name: vol-pgdata
  vol-pgadmin_data:
    name: vol-pgadmin_data

If the network name is not specified, it is generated automatically: The name of the directory containing the docker-compose.yaml file in lowercase + _default.

You can find the network’s name when running docker-compose up:

pg-database Pulling pg-database Pulled 
Network week_1_default  Creating
Network week_1_default  Created

By default, the dpage/pgadmin4 image stores its config (registered servers, query history, etc.) inside the container — so it's lost every time you docker compose down.

To persist it, mount /var/lib/pgadmin to a Docker volume. Use a named volume rather than a host-bind, because pgAdmin runs as user 5050 and host permissions tend to fight you:

services:
  pgadmin:
    image: dpage/pgadmin4
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@admin.com
      - PGADMIN_DEFAULT_PASSWORD=root
    volumes:
      - "pgadmin-data:/var/lib/pgadmin"
    ports:
      - "8080:80"

volumes:
  pgadmin-data:

After this, your pgAdmin servers and dashboards survive docker compose down and docker compose up.

If you really want a host-bind mount

You'll need to fix permissions before mounting. pgAdmin's container user is 5050:

mkdir -p ./pgadmin_data
sudo chown -R 5050:5050 ./pgadmin_data

Then:

volumes:
  - "./pgadmin_data:/var/lib/pgadmin"

If you skip the chown step, pgAdmin will fail to start with a permission error.

On GCP / cloud VMs

Same approach works — use a named volume rather than host-bind to avoid filesystem permission quirks on the cloud disk.

The Docker engine may crash continuously and fail to work after restart. You might see error messages like "docker engine stopped" and "failed to fetch extensions" repeatedly on the screen.

Solution:

• Check if you have the latest version of Docker installed. Update Docker if necessary.
• If the problem persists, consider reinstalling Docker.
  • Note: You will need to fetch images again, but there should be no other issues.

Most "docker-compose" installation problems on Linux/WSL fall into a small handful of categories.

"docker-compose: command not found" / "still not available"

The downloaded file from the docker/compose releases page has a long platform-suffixed name like docker-compose-linux-x86_64. Rename it and put it on your PATH:

sudo mv docker-compose-linux-x86_64 /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose

Modern Docker (20.10+) ships compose v2 as docker compose (with a space). If you have a recent Docker install, you may not need a separate docker-compose binary at all — just use docker compose up.

Picking the right binary for your platform

Use uname to determine which file to download:

uname -s   # operating system, usually 'Linux'
uname -m   # architecture, e.g. 'x86_64' or 'aarch64'

Then download the matching release, e.g.:

sudo curl -L "https://github.com/docker/compose/releases/latest/download/docker-compose-$(uname -s)-$(uname -m)" \
  -o /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose

Pin a specific release if you need reproducibility (substitute latest/download with download/<version>).

"cannot execute binary file: Exec format error"

The architecture doesn't match. ARM64 machines (Apple Silicon, ARM Linux, some VMs) need the aarch64 binary, not x86_64. Re-download with uname -m substituted correctly.

"error getting credentials" / "docker-credential-desktop not found"

Docker is looking for a credential helper that isn't installed. Two fixes:

# Quick: install pass (resolves it on most Linux distros)
sudo apt install pass

Or edit ~/.docker/config.json and rename credsStore to credStore (the helper-less default), or remove the line entirely.

"dial unix /var/run/docker.sock: connect: permission denied"

Your user isn't in the docker group. Add it:

sudo groupadd docker
sudo usermod -aG docker $USER
# log out and back in for the group change to apply

For those experiencing problems with Docker Compose, getting data in PostgreSQL, and similar issues, follow these steps:

• Create a new volume on Docker, either using the command line or Docker Desktop app.
• Modify your docker-compose.yml file as needed to fix any setup issues.
• Set low_memory=False when importing the CSV file using pandas:

df = pd.read_csv('yellow_tripdata_2021-01.csv', nrows=1000, low_memory=False)

• Use the specified function in your upload-data.ipynb for better tracking of the ingestion process.

from time import time

counter = 0
time_counter = 0

while True:
    t_start = time()

    df = next(df_iter)

    df.tpep_pickup_datetime = pd.to_datetime(df.tpep_pickup_datetime)
    df.tpep_dropoff_datetime = pd.to_datetime(df.tpep_dropoff_datetime)

    df.to_sql(name='yellow_taxi_data', con=engine, if_exists='append')

    t_end = time()

    t_elapsed = t_end - t_start

    print('Chunk Insertion Done! Time taken: %.2f seconds' %(t_elapsed))

    counter += 1
    time_counter += t_elapsed

    if counter == 14:
        print('All Chunks Inserted! Total Time Taken: %.2f seconds' %(time_counter))
        break

Order of Execution:

1. Open the terminal in the 2_docker_sql folder and run: docker compose up
2. Ensure no other containers are running except the ones you just executed (pgAdmin and pgdatabase).
3. Open Jupyter Notebook and begin the data ingestion.
4. Open pgAdmin and set up a server. Make sure you use the same configurations as your docker-compose.yml file, such as the same name (pgdatabase), port, and database name (ny_taxi).

This issue arises because the Postgres database is not initialized before executing docker-compose up -d. While there are other potential solutions discussed in this thread, you can resolve it by initializing the database first. Then, the Docker Compose will work as expected.

docker run -it \
  -e POSTGRES_USER="root" \
  -e POSTGRES_PASSWORD="root" \
  -e POSTGRES_DB="ny_taxi" \
  -v $(pwd)/ny_taxi_data:/var/lib/postgresql/data \
  -p 5432:5432 \
  --network=pg-network \
  --name=pg_database \
  postgres:13

Cause:

This error occurs because some applications are not updated. Specifically, check for any pending updates for Windows Terminal, WSL, and Windows Security updates.

Solution:

To update Windows Terminal:

1. Open the Microsoft Store.
2. Go to your library of installed apps.
3. Search for Windows Terminal.
4. Update the app.
5. Restart your system to apply the changes.

For updating Windows Security updates:

1. Go to Windows Updates settings.
2. Check for any pending updates, especially security updates.
3. Restart your system once the updates are downloaded and installed successfully.

If WSL integration keeps stopping with exit code 1, try these in order.

Toggle the DNS cache service

This Reddit fix works for some users:

reg add "HKLM\System\CurrentControlSet\Services\Dnscache" /v "Start" /t REG_DWORD /d "4" /f

Restart Windows, then re-enable it:

reg add "HKLM\System\CurrentControlSet\Services\Dnscache" /v "Start" /t REG_DWORD /d "2" /f

Restart Windows again.

Switch Docker Desktop to Linux containers

Right-click the Docker tray icon and choose "Switch to Linux containers" if it isn't already.

Issue when trying to run the GPC VM through SSH via WSL2, likely because WSL2 isn’t looking for .ssh keys in the correct folder. The command attempted:

ssh -i gpc [username]@[my external IP]

1. Use sudo Command

   Try using sudo before executing the command:

   sudo ssh -i gpc [username]@[my external IP]
   

2. Change Permissions

   Navigate to your folder and change the permissions for the private key SSH file:

   chmod 600 gpc
   

3. Create a .ssh Folder in WSL2

   • Navigate to your home directory:

     cd ~
     

   • Create a .ssh folder:

     mkdir .ssh
     

   • Copy the content from the Windows .ssh folder to the newly created .ssh folder:

     cp -r /mnt/c/Users/YourUsername/.ssh/* ~/.ssh/
     

   • Adjust the permissions of the files and folders in the .ssh directory if necessary.

WSL2 may not be referencing the correct .ssh/config path from Windows. You can create a config file in the home directory of WSL2 by following these steps:

1. Navigate to your home directory:

   cd ~
   

2. Create the .ssh directory:

   mkdir .ssh
   

3. Create a config file in the .ssh folder with the following content:

   HostName [GPC VM external IP]
   User [username]
   IdentityFile ~/.ssh/[private key]
   

Resolution: You need to stop the service using the port.

Run the following:

sudo kill -9 `sudo lsof -t -i:<port>`

Replace <port> with 8080 in this case. This will free up the port for use.

Yes. You can install and run Docker Engine directly inside WSL2 (Ubuntu) without Docker Desktop. This approach works well if you only need core Docker functionality (Docker CLI, Docker Compose, containers).

To resolve the issue, you can try the following solutions:

1. Add the -Y flag to apt-get to automatically agree to install additional packages.

   sudo apt-get install -y zip unzip
   

2. Use the Python ZipFile package, which is included in all modern Python distributions. This can bypass the need to install zip and unzip packages.

   from zipfile import ZipFile
   
   with ZipFile('file.zip', 'r') as zip_ref:
       zip_ref.extractall('destination_folder')
   

The various Postgres connection errors students hit in week 1 almost always trace back to one of three problems. Before changing anything, identify which:

connection failed: connection to server at "localhost" port 5432 failed: Connection refused
FATAL: password authentication failed for user "root"
FATAL: role "root" does not exist
FATAL: database "ny_taxi" does not exist
psycopg2.OperationalError: connection to server at "localhost" (::1), port 5432 failed

Step 1: confirm the right Postgres is reachable

Run:

docker ps

You should see the postgres:13 (or postgres:16/postgres:18) container with port mapping 0.0.0.0:5432->5432/tcp. If not, start it:

docker compose up -d
# or, for the standalone docker run:
docker run -it -e POSTGRES_USER=root -e POSTGRES_PASSWORD=root -e POSTGRES_DB=ny_taxi \
  -v ny_taxi_postgres_data:/var/lib/postgresql/data -p 5432:5432 postgres:16

Step 2: check whether port 5432 is already taken on your host

A locally installed Postgres ("Postgres.app", apt install postgresql, the Windows installer, the Mac Homebrew formula) often listens on 5432. When you map the Docker container to the same port, the connection silently goes to the wrong instance — which is usually the source of "FATAL: password authentication failed for user root" or "role root does not exist" (the local install doesn't know about the root user).

Linux/Mac:

sudo lsof -i :5432

Windows: open Services (services.msc) and look for any postgresql-x64-XX service.

You have two options:

• Stop the local Postgres:

  • Linux: sudo service postgresql stop
  • Mac (Homebrew): launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
  • Windows: stop the postgresql-x64-XX service in Services.

• Or change the Docker mapping to a different host port and connect to that port:

  -p 5433:5432
  # then
  pgcli -h localhost -p 5433 -u root -d ny_taxi
  

Step 3: match host names between the script and the runtime

If the connection error mentions a host name like pgdatabase or pg-database and says "could not translate host name", you are running an ingestion script that points at a Docker service name from outside the Docker network. Pick one:

• Run the script from inside the same Docker network and use the service name (pgdatabase).
• Run the script from your host machine and use localhost (or 127.0.0.1).

For the Dockerized ingestion job the course shows, both the container and the Postgres container must share a Docker network — --network=pg-network on docker run, or the implicit network in docker compose.

Step 4: persistent data corruption / "database ny_taxi does not exist"

If the database existed before but the FATAL: database ny_taxi does not exist error appears now, your Postgres container probably started with an empty data directory. Two common reasons:

• The volume mount path is wrong, so a new empty data dir is being initialised every time.
• Volumes were pruned (e.g. docker compose down -v).

Either restore data from backup, or wipe the volume and re-ingest:

docker compose down -v
docker compose up -d
# then re-run the ingestion script

Step 5: client-side issues (pgcli specifically)

If pgcli prints ImportError: no pq wrapper available, it can't find libpq — install the binary psycopg:

uv add "psycopg[binary]"
# or
pip install "psycopg[binary]"

If pgcli appears to hang at the password prompt on Windows Git Bash, prefix it with winpty:

winpty pgcli -h localhost -p 5432 -u root -d ny_taxi

Or use Windows Terminal / VS Code's integrated terminal instead of Git Bash.

Quick reference

• Connection refused → Postgres isn't running, or it's on a different port. Check docker ps.
• FATAL: password authentication failed for user "root" → almost always a port collision with a locally-installed Postgres.
• FATAL: role "root" does not exist → same as above (local install doesn't have a root user).
• FATAL: database "ny_taxi" does not exist → Postgres init didn't run, or the volume is empty.
• could not translate host name "pgdatabase" → wrong host name for where you're connecting from (host vs container network).

Step 1: Create a common network

Ensure that you have created a common network (pg-network). This allows several containers to communicate with each other. On top of this network you will run:

1. Postgres container
2. The Dockerized ingestion script container
3. pgAdmin container

docker network create pg-network

Step 2: Run the Postgres container

Once you’ve created the network, start running each container one by one. First, run the Postgres container:

docker run -it \
    -e POSTGRES_USER="root" \
    -e POSTGRES_PASSWORD="root" \
    -e POSTGRES_DB="ny_taxi" \
    -v ny_taxi_postgres_data:/var/lib/postgresql \
    -p 5432:5432 \
    --network=pg-network \
    --name pgdatabase \
    postgres:16

If postgres:18 causes issues, use postgres:16 as shown above.

Step 3: Build the Docker container for the pipeline

Ensure your current working directory is /pipeline, then build:

docker build -t taxi_ingest:v001 .

Step 4: Run the ingestion container

docker run -it \
    --network=pg-network \
    taxi_ingest:v001 \
    --pg_user=root \
    --pg_pass=root \
    --pg_host=pgdatabase \
    --pg_port=5432 \
    --pg_db=ny_taxi \
    --year=2021 \
    --month=1 \
    --target_table=yellow_taxi_trips

Make sure that you use the parameters in the command exactly as defined in your script. For example, if your script uses --pg_user then use --pg_user; if it uses --user then change the command accordingly.

Step 5 (Optional): Validate the ingested records

To check if your records reached the Postgres table, run pgcli:

uv run pgcli -h localhost -p 5432 -u root -d ny_taxi

List tables:

\dt

Check row count:

SELECT COUNT(*) FROM yellow_taxi_trips;

In this section of the course, the 5432 port of PostgreSQL is mapped to your computer’s 5432 port. This means you can access the PostgreSQL database via pgcli directly from your computer.

So, no, you don’t need to run it inside another container. Your local system will suffice.

PermissionError: [Errno 13] Permission denied: '/Users/<you>/.config/pgcli'

This means pgcli can't write its config dir. Two common causes.

Cause 1: someone ran pgcli with sudo earlier

Running sudo pgcli ... once creates ~/.config/pgcli owned by root. Subsequent runs as your normal user can't write there. Fix the ownership:

sudo chown -R "$USER" ~/.config/pgcli

Going forward, install and run pgcli without sudo — install it into a project venv (recommended) or pip install --user pgcli so you don't need root.

Cause 2: pgcli installed into a system Python you can't write to

Install pgcli into an isolated environment instead. Recommended path is uv:

uv add pgcli "psycopg[binary]"
uv run pgcli -h localhost -p 5432 -u root -d ny_taxi

Or with a plain venv:

python3 -m venv .venv
source .venv/bin/activate
pip install pgcli "psycopg[binary]"

Either way, run pgcli from inside the activated environment (or via uv run pgcli) — never with sudo.

If you have already installed pgcli but Bash or the Windows Terminal doesn't recognize the command:

• On Git Bash:

  bash: pgcli: command not found
  

• On Windows Terminal:

  pgcli: The term 'pgcli' is not recognized…
  

Try adding the Python path to the Windows PATH variable:

1. Use the command to get the location:

   pip list -v
   

2. Copy the path, which looks like:

   C:\Users\...\AppData\Roaming\Python\Python39\site-packages
   

3. Replace site-packages with Scripts:

   C:\Users\...\AppData\Roaming\Python\Python39\Scripts
   

It might be that Python is installed elsewhere. For example, it could be under:

• c:\python310\lib\site-packages

In that case, you should add:

• c:\python310\lib\Scripts to PATH.

Instructions

• Add the determined path to Path (or PATH) in System Variables.

Stack Overflow Reference

If running pgcli locally causes issues or you do not want to install it on your machine, you can use it within a Docker container instead.

Below is the usage with values used in the course videos for:

• Network name (Docker network)
• Postgres-related variables for pgcli
• Hostname
• Username
• Port
• Database name

docker run -it --rm --network pg-network ai2ys/dockerized-pgcli:4.0.1

Then execute the following pgcli command:

pgcli -h pg-database -U root -p 5432 -d ny_taxi

You'll be prompted for the password for the user root.

Example Output:

Server: PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1)
Version: 4.0.1
Home: [pgcli.com](http://pgcli.com)

To list tables:

root@pg-database:ny_taxi> \dt

+--------+------------------+-------+-------+
| Schema | Name             | Type  | Owner |
|--------+------------------+-------+-------|
| public | yellow_taxi_data | table | root  |
+--------+------------------+-------+-------+

SELECT 1
Time: 0.009s
root@pg-database:ny_taxi>

PULocationID will not be recognized, but "PULocationID" will be. This is because unquoted identifiers are case insensitive. See docs.

When using the command \d <database name> you get the error column c.relhasoids does not exist.

Resolution:

1. Uninstall pgcli.
2. Reinstall pgcli.
3. Restart your PC.

When attempting to start the Docker Postgres container, you may encounter the error message:

Error - postgres port is already in use.

Option 1: Identify and Stop the Service

1. Determine which service is using the port by running:

   sudo lsof -i :5432
   

2. Stop the service that is using the port:

   sudo service postgresql stop
   

Option 2: Map to a Different Port

For a more long-term solution, consider mapping to a different port:

• Map local port 5433 to container port 5432 in your Docker configuration (Dockerfile or docker-compose.yml).
• If using a VM, ensure that port 5433 is forwarded in the host machine configuration.

This approach prevents conflicts and allows the Docker Postgres container to run without interruption.

In join queries, if you mention the column name directly or enclose it in single quotes, you'll encounter an error saying "column does not exist".

Solution: Enclose the column names in double quotes, and it will work correctly.

pgAdmin has a new version. The create server dialog may not appear. Try using Register -> Server instead.

The CSRF session token missing error usually indicates CSRF protection is out of sync between the client and server. If you’re running pgAdmin in Docker, you can fix this with a combination of quick browser steps and container configuration changes.

Immediate browser fixes

• Refresh the page (F5, Ctrl+Shift+R, or Cmd+Shift+R) to regenerate cookies and obtain a new CSRF token.
• Clear the site's cookies/cache for pgAdmin in your browser settings.
• Try an Incognito/Private window to avoid cached credentials.

Docker configuration fixes (apply in your docker-compose.yaml or via environment vars, then restart the container)

• Add these environment variables to the pgAdmin service:

- PGADMIN_DEFAULT_EMAIL=admin@admin.com
- PGADMIN_DEFAULT_PASSWORD=root
- PGADMIN_CONFIG_ENHANCED_COOKIE_PROTECTION=False
- PGADMIN_CONFIG_WTF_CSRF_ENABLED=False
- PGADMIN_CONFIG_WTF_CSRF_CHECK_DEFAULT=False
- PGADMIN_CONFIG_SESSION_COOKIE_SAMESITE='Lax'
- PGADMIN_CONFIG_SESSION_COOKIE_SECURE=False

• Then recreate containers:

docker compose down -v
docker compose up -d --force-recreate

Notes

• Disabling CSRF protection reduces security; use these settings for development or debugging. When possible, fix the underlying cause and re-enable CSRF protections for production environments.
• If you already have a working setup, ensure that your environment variables are applied to the running container and that you’re reconnecting to the correct pgAdmin instance.

I am using a Mac Pro device and connect to the GCP Compute Engine via Remote SSH - VSCode. But when trying to run the PgAdmin container via docker run or docker compose, I couldn't access the PgAdmin address via my browser. After modifications, I was able to access it.

Solution #1:

Modify the docker run command:

docker run --rm -it \
  -e PGADMIN_DEFAULT_EMAIL="admin@admin.com" \
  -e PGADMIN_DEFAULT_PASSWORD="pgadmin" \
  -e PGADMIN_CONFIG_WTF_CSRF_ENABLED="False" \
  -e PGADMIN_LISTEN_ADDRESS=0.0.0.0 \
  -e PGADMIN_LISTEN_PORT=5050 \
  -p 5050:5050 \
  --network=de-zoomcamp-network \
  --name pgadmin-container \
  --link postgres-container \
  -t dpage/pgadmin4

Solution #2:

Modify the docker-compose.yaml configuration and use the docker compose up command:

pgadmin:
  image: dpage/pgadmin4
  container_name: pgadmin-container
  environment:
    - PGADMIN_DEFAULT_EMAIL=admin@admin.com
    - PGADMIN_DEFAULT_PASSWORD=pgadmin
    - PGADMIN_CONFIG_WTF_CSRF_ENABLED=False
    - PGADMIN_LISTEN_ADDRESS=0.0.0.0
    - PGADMIN_LISTEN_PORT=5050
  volumes:
    - "./pgadmin_data:/var/lib/pgadmin/data"
  ports:
    - "5050:5050"
  networks:
    - de-zoomcamp-network
  depends_on:
    - postgres-container

If you follow the video 1.2.2 - Ingesting NY Taxi Data to Postgres and execute the same steps, you will ingest all the data (~1.3 million rows) into the table yellow_taxi_data. However, running the whole script in the Jupyter notebook for a second time from top to bottom will result in missing the first chunk of 100,000 records. This occurs because a call to the iterator appears before the while loop, leading to the second chunk being ingested first.

• Remove the cell df=next(df_iter) located higher up in the notebook than the while loop.
• Ensure the first w(df_iter) call is within the while loop.

📔 Note: The notebook is used to test the code and is not intended to be run top to bottom. The logic is organized in a later step when inserted into a .py file for the pipeline.

Pandas can interpret "string" column values as "datetime" directly when reading the CSV file using pd.read_csv with the parse_dates parameter. This can include a list of column names or column indices, eliminating the need for conversion afterward.

Reference: pandas.read_csv documentation

Example from Week 1:

import pandas as pd

df = pd.read_csv(
    'yellow_tripdata_2021-01.csv',
    nrows=100,
    parse_dates=['tpep_pickup_datetime', 'tpep_dropoff_datetime']
)

df.info()

Output:

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 100 entries, 0 to 99
Data columns (total 18 columns):
 #   Column                 Non-Null Count  Dtype          
---  ------                 --------------  -----          
 0   VendorID               100 non-null    int64          
 1   tpep_pickup_datetime   100 non-null    datetime64[ns] 
 2   tpep_dropoff_datetime  100 non-null    datetime64[ns] 
 3   passenger_count        100 non-null    int64          
 4   trip_distance          100 non-null    float64        
 5   RatecodeID             100 non-null    int64          
 6   store_and_fwd_flag     100 non-null    object         
 7   PULocationID           100 non-null    int64          
 8   DOLocationID           100 non-null    int64          
 9   payment_type           100 non-null    int64          
 10  fare_amount            100 non-null    float64        
 11  extra                  100 non-null    float64        
 12  mta_tax                100 non-null    float64        
 13  tip_amount             100 non-null    float64        
 14  tolls_amount           100 non-null    float64        
 15  improvement_surcharge  100 non-null    float64        
 16  total_amount           100 non-null    float64        
 17  congestion_surcharge   100 non-null    float64        
dtypes: datetime64[ns](2), float64(9), int64(6), object(1)
memory usage: 14.2+ KB

When a CSV file is compressed using Gzip, it is saved with a ".csv.gz" file extension. This file type is also known as a Gzip compressed CSV file. To read a Gzip compressed CSV file using Pandas, you can use the read_csv() function.

Here is an example of how to read a Gzip compressed CSV file using Pandas:

import pandas as pd

df = pd.read_csv('file.csv.gz',
                 compression='gzip',
                 low_memory=False)

Contrary to pandas’ read_csv method, there’s no simple way to iterate through and set chunksize for parquet files. We can use PyArrow (Apache Arrow Python bindings) to resolve that.

import pyarrow.parquet as pq
from sqlalchemy import create_engine
import time

output_name = "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2021-01.parquet"

parquet_file = pq.ParquetFile(output_name)
parquet_size = parquet_file.metadata.num_rows

engine = create_engine(f'postgresql://{user}:{password}@{host}:{port}/{db}')

table_name = "yellow_taxi_schema"

# Clear table if exists
pq.read_table(output_name).to_pandas().head(n=0).to_sql(name=table_name, con=engine, if_exists='replace')

# Default (and max) batch size
index = 65536

for i in parquet_file.iter_batches(use_threads=True):
    t_start = time.time()
    print(f'Ingesting {index} out of {parquet_size} rows ({index / parquet_size:.0%})')
    i.to_pandas().to_sql(name=table_name, con=engine, if_exists='append')
    index += 65536
    t_end = time.time()
    print(f'\t- it took %.1f seconds' % (t_end - t_start))

Symptoms when running from sqlalchemy import create_engine in a notebook:

ImportError: cannot import name 'TypeAliasType' from 'typing_extensions'
ModuleNotFoundError: No module named 'psycopg2'
TypeError: 'module' object is not callable
NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgresql.psycopg

These all come from a few related causes.

Missing or out-of-date typing_extensions

ImportError: cannot import name 'TypeAliasType' from 'typing_extensions'

Upgrade to 4.6+:

pip install --upgrade typing_extensions

Missing the Postgres driver

ModuleNotFoundError: No module named 'psycopg2'

Install one of the binary distributions (avoids needing libpq dev headers):

pip install psycopg2-binary
# or, for SQLAlchemy 2.x with psycopg 3:
pip install "psycopg[binary]"

If pip install psycopg2 fails with pg_config not found, you don't have libpq dev headers installed — use psycopg2-binary (or, on Mac, brew install postgresql).

Connection-string dialect mismatch

create_engine('postgresql://...') works with both psycopg2 and psycopg, but to be explicit:

# psycopg2 (most common)
"postgresql+psycopg2://root:root@localhost:5432/ny_taxi"

# psycopg (v3)
"postgresql+psycopg://root:root@localhost:5432/ny_taxi"

If you see NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgresql.psycopg, your installed psycopg version doesn't match the dialect string — install the matching driver or change the URL prefix.

'module' object is not callable

You probably did import sqlalchemy and then called sqlalchemy(...) instead of sqlalchemy.create_engine(...). Fix:

from sqlalchemy import create_engine
engine = create_engine("postgresql+psycopg://root:root@localhost:5432/ny_taxi")

Stacked virtual environments

If you've nested virtualenvs (for example a PyCharm-generated .venv inside a project that already had its own venv), imports may resolve from a different env than you think. Cleanest fix: rm -rf .venv, create a single venv with uv venv (or python -m venv), and install dependencies there.

First, check the versions of SQLAlchemy and pandas — pip install --upgrade sqlalchemy pandas (or uv add --upgrade sqlalchemy pandas).

Then, try to wrap the query using text:

from sqlalchemy import text

query = text("SELECT * FROM tbl")
df = pd.read_sql_query(query, conn)

For this issue, you can use the following solution:

SELECT * FROM zones AS z WHERE z."Zone" = 'Astoria Zone';

Columns that start with uppercase sometimes need to be enclosed in double quotes.

Additionally, check your dataset for the existence of 'Astoria Zone'. You might find only 'Astoria':

SELECT * FROM zones AS z WHERE z."Zone" = 'Astoria';

It is inconvenient to use quotation marks all the time, so it is better to put the data in the database all in lowercase. In Pandas, after:

import pandas as pd

df = pd.read_csv('taxi+_zone_lookup.csv')

Add the row:

df.columns = df.columns.str.lower()

Postgres 18 changes how the data directory is structured inside Docker containers. While Postgres ≤16 stored data under /var/lib/postgresql/data, Postgres 18 expects the volume to be mounted at /var/lib/postgresql. If an existing volume created with an older Postgres version is reused without updating the mount path, Postgres 18 will detect an incompatible layout and exit during startup. This can appear as DNS resolution errors or failed connections from pgAdmin or ingestion jobs. For Week 1 setups, the fix is to update the volume mount path, remove the old volume, and recreate the containers so Postgres 18 can initialize a new data directory.

Proposed Fix (Week 1 setups)

• Update the volume mount path to /var/lib/postgresql (instead of /var/lib/postgresql/data).
• Remove the old volume that was created with Postgres 16.
• Recreate the containers so Postgres 18 initializes a fresh data directory.

Example commands

• Running with docker run (adjust as needed):

docker run -it \
  -e POSTGRES_USER="root" \
  -e POSTGRES_PASSWORD="admin" \
  -e POSTGRES_DB="ny_taxi" \
  -v "/path/to/ny_taxi_postgres_data":"/var/lib/postgresql" \
  -p 5432:5432 \
  postgres:18

• For docker-compose, mount the volume to /var/lib/postgresql:

volumes:
  ny_taxi_postgres_data:

services:
  postgres:
    image: postgres:18
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: admin
      POSTGRES_DB: ny_taxi
    volumes:
      - ny_taxi_postgres_data:/var/lib/postgresql

• Remove the old volume (list and remove):

docker volume ls
docker volume rm <old-volume-name>

• Recreate containers:

docker-compose down
docker-compose up -d

• Ensure you have a backup of any important data before removing volumes.
• After restarting, verify that Postgres starts and the data directory is initialized under /var/lib/postgresql.

Instead of using “localhost” as the host name/address, try “postgres”, or “host.docker.internal” instead.

Alternative Solution:

• If you have installed Postgres locally and disabled persist data on the Postgres container in Docker (i.e., volume: removed), use a Postgres port other than 5432, such as 5433.

• For the pgAdmin host name/address, if 'localhost', 'postgres', or 'host.docker.internal' are not working, you can use your own IPv4 Address.

  To find your IPv4 Address on Windows OS:

  1. Open Command Prompt.

  2. Run the command:

     ipconfig
     

  3. Look under Wireless LAN adapter WiFi 2 for the IPv4 Address. For example:

     IPv4 Address. . . . . . . . . . . : 192.168.0.148
     

There could be a few reasons for this issue:

• Race Conditions: If you're running multiple processes in parallel.

• Database Connection Issues: The job might not be connecting to the correct PostgreSQL database, or there could be authentication or permission issues preventing table creation.

• Missing Table Creation Logic: The code responsible for creating the table might not be properly included or executed in the job submission process.

As a best practice, it's generally recommended to pre-create tables in PostgreSQL to avoid runtime errors. This ensures the database schema is properly set up before any jobs are executed.

Extra: Use CREATE TABLE IF NOT EXISTS in your code. This will prevent errors if the table already exists and ensure smooth job execution.

When you set up a VM in Google Cloud Platform (GCP), it initially uses an ephemeral IP address, which changes each time you start or stop the VM. If you need a consistent IP for your configuration file, you should set up a static IP address.

Steps to Set Up a Static IP Address

1. Navigate to VPC Network > IP addresses in the GCP console.
2. Allocate a new static IP address.
3. Attach the static IP to your VM instance.

Note: You are charged for a static IP if it is not allocated to a specific VM, so make sure it is attached to avoid extra fees.

For detailed instructions, consult the GCP documentation.

The Google Cloud SDK Windows installer occasionally fails to update PATH automatically:

The installer is unable to automatically update your system PATH. Please add C:\tools\google-cloud-sdk\bin

Add the SDK's bin/ directory to PATH manually:

1. Right-click "This PC" → Properties → Advanced system settings → Environment Variables.

2. Under "User variables" (or "System variables"), select Path → Edit → New.

3. Paste the directory the installer mentioned, e.g. C:\tools\google-cloud-sdk\bin.

4. Click OK in all dialogs.

5. Open a new terminal (existing ones won't pick up the change) and verify:

   gcloud --version
   

Tips for a smoother Windows shell setup

The course's command-line examples assume a Unix-like shell. On Windows the easiest options are:

• WSL2 (recommended). Install via wsl --install, then run all course commands inside the WSL distro.
• Git Bash. During the Git for Windows installer, check "Add Git Bash to Windows Terminal" and "Use Git and optional Unix tools from the command prompt". Then make Git Bash the default profile in Windows Terminal (Settings → Default profile).

Either way, restart your terminal after editing PATH so the new value is picked up.

When creating a project in GCP, you may encounter the following error:

WARNING: Project creation failed: HttpError accessing cloudresourcemanager.googleapis.com: response: {
  'content-type': 'application/json; charset=UTF-8',
  'status': 409
}, content {
  "error": {
    "code": 409,
    "message": "Requested entity already exists",
    "status": "ALREADY_EXISTS"
  }
}

This error occurs when the project ID you are trying to use is already taken. Project IDs are unique across all GCP projects. If any user ever had a project with that ID, you cannot use it.

• Choose a different, more unique project ID. Avoid common names like testproject as they are likely to be already in use.

For more details, refer to the discussion: Stack Overflow

If you receive the error:

Error 403: The project to be billed is associated with an absent billing account., accountDisabled

It is most likely because you did not enter your project ID correctly. The value you enter should be unique to your project. You can find this value on your GCP Dashboard when you log in.

Another possibility is that you have not linked your billing account to your current project.

If Google refuses your credit/debit card, try using a different one. For instance, a card from Kaspi (Kazakhstan) might not work, but a card from TBC (Georgia) does.

Unfortunately, support assistance might not be highly effective in resolving this issue.

Additionally, a Pyypl web-card can be a viable alternative.

ny-rides.json

The ny-rides.json is your private file in Google Cloud Platform (GCP). Here’s how to find it:

• Navigate to GCP and select the project with your instance.
• Go to IAM & Admin.
• Select the Service Accounts tab.
• Click the Keys tab.
• Add a key, choosing JSON as the key type, then click Create.

Note: Once in the Service Accounts, click the email associated to access the KEYS tab where you can add a key as a JSON key type.

You likely didn’t enable the Compute Engine API.

In this lecture, Alexey deleted his instance in Google Cloud. Do I have to do it?

No, do not delete your instance in Google Cloud Platform. Otherwise, you will have to set it up again for the week 1 readings.

Initially, I could not SSH into my VM from my Windows laptop. I thought it was because I did not follow the tutorial exactly. Instead of generating the SSH key using MINGW/git bash with the Linux-style command, I did it in Command Prompt using the Windows-style command. I kept getting a public key error.

Permanent Solution:

It turns out it wasn’t an issue with the key generation at all! The problem was with the username. I had given my SSH key a different username than what appeared in my VM (my Google account username). So, I had been trying to log in with googleacctuser@[ipaddr] instead of mySSHuser@[ipaddr]. Here's how I resolved it:

1. Retraced my steps to check the SSH key setup in the GCP console, where it showed the user and SSH key.
2. Changed the username to the correct one (googleacctuser) in my config file.
3. Updated the config file and used mySSHuser to log in.

Now, the issue was that I had created two users. I made all the installations and permissions on googleacctuser, not accessible from mySSHuser. Since I didn't need mySSHuser, I edited the SSH key to change the username at the end and updated the GCP console and config file accordingly.

Then, I planned to delete the mySSHuser account in the VM terminal to keep things clean (though I got a bit attached, so I skipped this).

Temporary Solution:

Before figuring out my issue, I used a shortcut by SSH'ing into the VM in the browser, which worked nicely for a while. But eventually, I needed to use VSCode.

If you are progressing through the course and find that your VM is starting to become slow, you can run the following commands to inspect and detect areas where you can improve:

Recommended VM Size

• Start with a 60GB machine. A 30GB machine may not be sufficient, as you might need to restart the project with a larger size.

Commands to Inspect the Health of Your VM

• System Resource Usage

  top
  htop
  

  Shows real-time information about system resource usage, including CPU, memory, and processes.

  free -h
  

  Displays information about system memory usage and availability.

  df -h
  

  Shows disk space usage of file systems.

  du -h <directory>
  

  Displays disk usage of a specific directory.

• Running Processes

  ps aux
  

  Lists all running processes along with detailed information.

• Network

  ifconfig
  ip addr show
  

  Shows network interface configuration.

  netstat -tuln
  

  Displays active network connections and listening ports.

• Hardware Information

  lscpu
  

  Displays CPU information.

  lsblk
  

  Lists block devices (disks and partitions).

  lshw
  

  Lists hardware configuration.

• User and Permissions

  who
  

  Shows who is logged on and their activities.

  w
  

  Displays information about currently logged-in users and their processes.

• Package Management

  apt list --installed
  

  Lists installed packages (for Ubuntu and Debian-based systems).

If you’ve got the error:

Error: Error updating Dataset "projects/<your-project-id>/datasets/demo_dataset": googleapi: Error 403: Billing has not been enabled for this project. Enable billing at console.cloud.google.com. The default table expiration time must be less than 60 days, billingNotEnabled

but you’ve set your billing account, try disabling billing for the project and enabling it again. This method has been successful for others.

If you are encountering installation trouble with the Google Cloud SDK on Windows and receiving the following error:

These credentials will be used by any library that requests Application Default Credentials (ADC).

WARNING:

Cannot find a quota project to add to ADC. You might receive a "quota exceeded" or "API not enabled" error. Run $ gcloud auth application-default set-quota-project to add a quota project.

Try these steps:

1. Reinstall the SDK using the unzip file "install.bat".
2. Check the installation by running gcloud version.
3. Run gcloud init to set up your project.
4. Execute gcloud auth application-default login.

For detailed instructions, refer to the following guide: Windows SDK Installation Guide

1. Click on your VM.
2. Create an image of your VM.
3. On the page of the image, tell GCP to create a new VM instance via the image.
4. On the settings page, change the location.

The reason this video about the GCP VM exists is that many students had problems configuring their environment. You can use your own environment if it works for you.

Advantages of using your own environment include:

• Commit Changes: If you are working in a GitHub repository, you will be able to commit changes directly. In the VM, the repo is cloned via HTTPS, so it is not possible to commit directly, even if you are the owner of the repo.

If you encounter an error while trying to create a directory:

User1@DESKTOP-PD6UM8A MINGW64 /

$ mkdir .ssh

mkdir: cannot create directory ‘.ssh’: Permission denied

This error occurs because you are attempting to create the directory in the root folder (/).

To resolve this, create the directory in your home directory instead. Use the following steps:

1. Navigate to your home directory using:

   cd ~
   

2. Create the .ssh directory:

   mkdir .ssh
   

For further guidance, watch this video.

Failed to save '<file>': Unable to write file 'vscode-remote://ssh-remote+de-zoomcamp/home/<user>/data_engineering_course/week_2/airflow/dags/<file>' (NoPermissions (FileSystemError): Error: EACCES: permission denied, open '/home/<user>/data_engineering_course/week_2/airflow/dags/<file>')

To resolve this issue, you need to change the owner of the files you are trying to edit via VS Code. Follow these steps:

1. Connect to your VM using SSH.

2. Run the following command to change the ownership:

   sudo chown -R <user> <path to your directory>
   

Question: I connected to my VM perfectly fine last week (SSH) but when I tried again this week, the connection request keeps timing out.

Answer:

1. Start Your VM: Make sure the VM is running in your GCP console.

2. Update External IP:

   • Copy its External IP once the VM is running.
   • Update your SSH configuration file with this IP.

3. Edit SSH Config:

   cd ~/.ssh
   code config
   

   This command opens the config file in VSCode for editing.

Go to edit your VM.

1. Navigate to the Automation section.

2. Add the following Startup script:

   #!/bin/bash
   sudo ufw allow ssh
   

3. Stop and Start the VM.

You can easily forward the ports of pgAdmin, PostgreSQL, and Jupyter Notebook using the built-in tools in Ubuntu without any additional client:

1. On the VM machine:

   • Launch Docker and Jupyter Notebook in the correct folder using:

     docker-compose up -d
     jupyter notebook
     

2. From the local machine:

   • Execute:

     ssh -i ~/.ssh/gcp -L 5432:localhost:5432 username@external_ip_of_vm
     

   • Execute the same command for ports 8080 and 8888.

3. Accessing Applications Locally:

   • For pgAdmin, open a browser and go to localhost:8080.
   • For Jupyter Notebook, open a browser and go to localhost:8888.
     • If you encounter issues with credentials, you may need to copy the link with the access token from the terminal logs on the VM when you launched the Jupyter Notebook.

4. Forwarding Both pgAdmin and PostgreSQL:

   • Use:

     ssh -i ~/.ssh/gcp -L 5432:localhost:5432 -L 8080:localhost:8080 modito@35.197.218.128
     

If you are using MS VS Code and running gcloud in WSL2, when you first try to login to GCP via the gcloud CLI with gcloud auth application-default login, you may encounter an issue where the terminal prints a long OAuth URL and then shows a series of "not found" errors for browsers:

Your browser has been opened to visit:
    https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=...

/usr/bin/xdg-open: 882: x-www-browser: not found
/usr/bin/xdg-open: 882: firefox: not found
/usr/bin/xdg-open: 882: chromium: not found
...
xdg-open: no method available for opening '...'

VS Code may show a notification: "Your application running on port 8085 is available" with an "Open in Browser" button. Clicking it may lead to an error page.

Solution:

1. Hover over the long OAuth URL in the terminal output.
2. Ctrl + Click the link — VS Code will show a dialog: "Do you want Code to open the external website?" with buttons Open, Copy, Configure Trusted Domains, and Cancel.
3. Click Configure Trusted Domains.
4. A dropdown will appear with options like:
   • "Trust https://accounts.google.com&quot;
   • "Trust google.com and all its subdomains"
   • "Trust all domains (disables link protection)"
   • "Manage Trusted Domains"
5. Pick the first or second entry (e.g., "Trust https://accounts.google.com").

Next time you run gcloud auth, the login page should pop up via the default browser without issues.

This error occurs when your organization has a policy that disables service account key creation. You can disable this policy using either the GCP Console or gcloud CLI.

Option 1: Using GCP Console

1. Go to the IAM dashboard (IAM & Admin > IAM)
2. Press Ctrl + O and make sure you select your organization
3. Click the "Edit Principal" button (pencil icon) next to your account
4. Search for "Organization Policy Administrator" role, add it and click save
5. On the left sidebar, go to Organization Policies
6. Look for the policy named "Disable service account key creation" that is active and select it
7. Click on "Manage policy" then click on the drop down that says "Enforced", set it to "Off"
8. Click "Done" and then the blue "Set policy" button

Option 2: Using gcloud CLI

# Get your organization ID
gcloud organizations list

# Get your account email
gcloud auth list

# Add policy admin role if needed
gcloud organizations add-iam-policy-binding <ORGANIZATION_ID> \
  --member="user:<YOUR_ACCOUNT_EMAIL>" \
  --role="roles/orgpolicy.policyAdmin"

# Delete the policy
gcloud org-policies delete iam.disableServiceAccountKeyCreation \
  --organization=<ORGANIZATION_ID>

Reference: GCP Organization Policies

A common cause is the VM running out of disk space, often from accumulated logs, Docker images/volumes, or pipeline artifacts. When the disk fills up, sshd may fail to write its session files and refuse new connections.

To diagnose and recover:

1. Open a serial console for the VM from the GCP console (Compute Engine → VM details → "Connect to serial console").
2. Check disk usage:

   df -h
   du -sh ~/* /var/log/* 2>/dev/null | sort -h
   

3. Free up space by clearing unused Docker resources and old logs:

   docker system prune -af --volumes
   sudo journalctl --vacuum-time=7d
   

4. If your pipeline tool keeps a local storage/log folder (e.g. dlt's ~/.dlt, dbt's target/, Kestra's mounted volumes), prune the oldest content there too.

Once disk space is freed, restart sshd or reboot the VM and SSH access should work again.

You can try to do these steps:

If you are using Windows, try the following steps to resolve the error:

1. Copy the .ssh folder from the Linux file path to Windows.

2. In the config file, use:

   IdentityFile C:\Users\<username>\.ssh\gcp
   

   Instead of:

   IdentityFile ~/.ssh/gcp
   

3. Ensure the private key file located at C:\Users\<username>\.ssh\gcp has an extra line at the end:

   

This error can occur when Terraform cannot access the online provider registry, which may happen in restricted regions where registry.terraform.io is blocked. The error messages may include things like "Invalid provider registry host" and "Could not query available versions for provider hashicorp/google".

Fix:

• Install and activate a system-wide VPN software (e.g., Proton VPN). Ensure that all traffic from your terminal (where terraform init runs) is routed through the VPN.
• Important: A VPN browser extension is usually not sufficient. Extensions proxy traffic only within the web browser and do not affect terminal/VS Code connections.

After the VPN is active, run terraform init again to retry fetching provider packages.

The issue was related to network restrictions, as Google is not accessible in my country. I used a VPN and discovered that the terminal program does not automatically follow the system proxy, requiring separate proxy configuration settings.

Solution:

1. Open an Enhanced Mode in your VPN application, such as Clash.
2. Run terraform apply again.

If you encounter this issue, consult your VPN provider for assistance with configuration.

You can configure Terraform on Windows 10 using the Linux Subsystem (WSL) by following this guide: Configuring Terraform on Windows 10 Linux Subsystem.

For more information, you can refer to the following issue on GitHub:

HashiCorp Terraform Issue #14513

When running:

terraform apply

on WSL2, you might encounter the following error:

Error: Post "https://storage.googleapis.com/storage/v1/b?alt=json&prettyPrint=false&project=<your-project-id>": oauth2: cannot fetch token: 400 Bad Request

Response: {"error":"invalid_grant","error_description":"Invalid JWT: Token must be a short-lived token (60 minutes) and in a reasonable timeframe. Check your iat and exp values in the JWT claim."}

This issue occurs due to potential time desynchronization on your machine, affecting JWT computation.

To fix this, run the following command to synchronize your system time:

sudo hwclock -s

│ Error: googleapi: Error 403: Access denied., forbidden

Your $GOOGLE_APPLICATION_CREDENTIALS might not be pointing to the correct file. Try the following steps:

1. Set the correct path for your credentials:

   export GOOGLE_APPLICATION_CREDENTIALS=~/.gc/YOUR_JSON.json
   

2. Activate the service account:

   gcloud auth activate-service-account --key-file $GOOGLE_APPLICATION_CREDENTIALS
   

One service account is enough for all the services/resources you'll use in this course. After you get the file with your credentials and set your environment variable, you should be good to go.

All Terraform releases are available at https://releases.hashicorp.com/terraform/.

Pick the version you need (the course README in the data-engineering-zoomcamp repo lists the recommended version) and the matching linux_amd64.zip (or linux_arm64.zip on ARM machines). Then unzip and place the terraform binary somewhere on your PATH, e.g. /usr/local/bin/.

This error occurs when terraform init is run outside the working directory.

To resolve this issue:

1. Navigate to the working directory that contains your Terraform configuration files.
2. Run the terraform init command inside the correct directory.

Make sure your configuration files (e.g., .tf files) are present in the directory before running the command.

The error:

Error: googleapi: Error 403: Access denied., forbidden

Error: Error creating Dataset: googleapi: Error 403: Request had insufficient authentication scopes.

Solution:

1. Verify your credentials by running:

   echo $GOOGLE_APPLICATION_CREDENTIALS
   echo $?
   

2. Ensure you have set the GOOGLE_APPLICATION_CREDENTIALS environment variable correctly, as demonstrated in the environment setup video in week 1:

   export GOOGLE_APPLICATION_CREDENTIALS="<path/to/your/service-account-authkeys>.json"
   

The error:

Error: googleapi: Error 403: terraform-trans-campus@trans-campus-410115.iam.gserviceaccount.com does not have storage.buckets.create access to the Google Cloud project. Permission 'storage.buckets.create' denied on resource (or it may not exist)., forbidden

The solution:

You have to declare the project name as your Project ID, not your Project name, available on the GCP console Dashboard.

To ensure the sensitivity of the credentials file, use the following configuration:

provider "google" {
  project     = var.projectId
  credentials = file("${var.gcpkey}")
  #region      = var.region
  zone = var.zone
}

When running terraform destroy, the following error can occur:

Do you really want to destroy all resources?

Terraform will destroy all your managed infrastructure, as shown above.

There is no undo. Only 'yes' will be accepted to confirm.

Enter a value: yes

google_bigquery_dataset.homework_dataset: Destroying... [id=projects/terraform-demo-449214/datasets/homework_dataset]

╷

│ Error: Error when reading or editing Dataset: googleapi: Error 400: Dataset terraform-demo-449214:homework_dataset is still in use, resourceInUse

This is because the dataset is still in use by a table. To delete the dataset, set the delete_contents_on_destroy property to true in the main.tf file.

Cause: The error occurs because the Google Cloud Storage bucket is under a constraint that requires Uniform Bucket-Level Access (UBLA) to be enabled. If UBLA is not enabled, Terraform apply can fail with 412.

Fix: Enable UBLA in the google_storage_bucket resource by setting uniform_bucket_level_access = true.

Example:

resource "google_storage_bucket" "demo-bucket" {
  name = "demo-bucket"

  uniform_bucket_level_access = true
}

Notes:

• Add this line to the google_storage_bucket resource for the affected bucket.
• After applying, re-run terraform apply to confirm the error is resolved.

In Kestra, concurrency means controlling how many executions of the same flow can run at the same time. It is used to prevent problems such as duplicate processing, data corruption, or excessive resource usage when a flow is triggered multiple times.

Concurrency Limit

concurrency:
  limit: 1

This configuration means that only one execution of the flow can run at a time. If the flow is already running and another execution is triggered, Kestra applies the concurrency behavior defined below.

Concurrency Behavior Options

• Prefect FAQ Document
• Airflow FAQ Document
• Mage FAQ Document

To launch Kestra, follow these instructions:

For Linux

Start Docker with the following command:

docker run \
  --pull=always \
  --rm \
  -it \
  -p 8080:8080 \
  --user=root \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -v /tmp:/tmp \
  kestra/kestra:latest server local

Once it is running, you can log in to the dashboard at localhost:8080.

For Windows

Refer to the Kestra GitHub repository for detailed instructions: https://github.com/kestra-io/kestra

Sample docker-compose for Kestra:

kestra:
  build: .
  image: kestra/kestra:latest
  container_name: kestra
  user: "0:0"
  environment:
    DOCKER_HOST: tcp://host.docker.internal:2375  # for Windows
    KESTRA_CONFIGURATION: |
      kestra:
        repository:
          type: h2
        queue:
          type: memory
        storage:
          type: local
          local:
            basePath: /app/storage
        tasks:
          tmp-dir:
            path: /app/tmp
        plugins:
          repositories:
            - id: central
              type: maven
              url: [repo.maven.apache.org](https://repo.maven.apache.org/maven2)
          definitions:
            - io.kestra.plugin.core:core:latest
            - io.kestra.plugin.scripts:python:1.3.4
            - io.kestra.plugin.http:http:latest
    KESTRA_TASKS_TMP_DIR_PATH: /app/tmp
  ports:
    - "8080:8080"
  volumes:
    - //var/run/docker.sock:/var/run/docker.sock  # Windows path
    - /yourpath/.dbt:/app/.dbt
    - /yourpath/kestra/plugins:/app/plugins
    - /yourpath/kestra/workflows:/app/workflows
    - /yourpath/kestra/storage:/app/storage
    - /yourpath//kestra/tmp:/app/tmp
    - /yourpath//dbt_prj:/app/workflows/dbt_project
    - /yourpath//my-creds.json:/app/.dbt/my-creds.json
  command: server standalone

When running the following Docker command in Bash with Docker and WSL2 installed, you may encounter an error. Running Bash as admin will not resolve the issue:

docker run \
  --pull=always \
  --rm \
  -it \
  -p 8080:8080 \
  --user=root \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -v /tmp:/tmp \
  kestra/kestra:latest server local

latest: Pulling from kestra/kestra
Digest: sha256:af02a309ccbb52c23ad1f1551a1a6db8cf0523cf7aac7c7eb878d7925bc85a62
Status: Image is up to date for kestra/kestra:latest
docker: Error response from daemon: mkdir C:\\Program Files\\Git\\var: Access is denied.
See 'docker run --help'.

To resolve this issue, run Command Prompt as an administrator and use the following command:

docker run \
  --pull=always \
  --rm \
  -it \
  -p 8080:8080 \
  --user=root \
  -v "/var/run/docker.sock:/var/run/docker.sock" \
  -v "C:/Temp:/tmp" \
  kestra/kestra:latest server local

After executing the command as described, the localhost should display the Kestra UI as expected.

I encountered an error where the localhost URL for pgAdmin would just hang (I chose localhost:8080 for my pgAdmin, and made kestra localhost:8090, personal preference).

The associated issue involved permissions. The resolution was to change the ownership of my local directory to the user "5050," which is pgAdmin. Unlike Postgres, pgAdmin requires explicit permission. Apparently, the Postgres user inside the Docker container creates the Postgres volume/dir, so it has permissions already.

This is a useful resource:

Stack Overflow: Permission denied /var/lib/pgadmin/sessions in Docker

Backfilling can blow through a small VM's disk surprisingly fast. The default 30 GB GCP VM disk fills quickly. To find and reclaim space:

Find what's using disk

sudo du -h --max-depth=1 / 2>/dev/null | sort -h | tail -20

Common culprits:

• Docker images and unused volumes (often several GB) — clean up with docker system prune -af --volumes.
• Old backfill artifacts in /tmp or your project working dir.
• Postgres data that's grown over many runs (table bloat from repeated backfills).

Clean up Kestra's stored executions

Kestra keeps execution metadata and logs that grow over time. Use a Purge flow to delete old executions. To purge immediately rather than wait for the scheduled trigger, set endDate to "{{ now() }}" and run it manually. You can choose whether to also remove FAILED-state executions.

Clean up PostgreSQL tables

If you backfilled the same dataset multiple times into different tables, drop the ones you don't need (manually in pgAdmin, or via a Kestra flow). For tables you want to keep but compact, run VACUUM FULL on them.

If after all this you still don't have enough room, increase the boot disk size from the GCP console (Compute Engine → VM details → Edit → Boot disk → Resize).

Never paste the service account JSON directly into a Kestra flow — it ends up in version control if you push the flow to GitHub. Use Kestra secrets or the KV store instead.

Option 1: KV store (simplest, recommended for the course)

1. Open the Namespaces tab in Kestra and select your namespace (e.g. zoomcamp).
2. Go to KV Store → New value.
3. Set the key to GCP_CREDS (or whatever name your flow expects), set the type to JSON, and paste the contents of your service account key file.
4. Reference it in your flow's pluginDefaults:

pluginDefaults:
  - type: io.kestra.plugin.gcp
    values:
      serviceAccount: "{{ kv('GCP_CREDS') }}"
      projectId: "{{ kv('GCP_PROJECT_ID') }}"

All GCP plugin tasks (BigQuery, GCS, Dataproc, etc.) will pick up the credentials automatically.

Option 2: Encoded secret (when you can't use KV store)

1. Base64-encode the service account JSON and store it in .env_encoded (which must be .gitignored):

base64 service-account.json > .env_encoded

2. Pass the encoded value to Kestra via Docker Compose environment and decode it as a Kestra secret. See Kestra's Google credentials guide.

3. Reference in flows with {{ secret('GCP_SERVICE_ACCOUNT') }}.

• Always add .env_encoded (and any local copy of the JSON key) to .gitignore. Base64 is encoding, not encryption — anyone who sees the file can decode it.
• To rotate the credential: generate a new key in GCP, re-run the encoding/upload step, and restart Kestra.
• Both methods cover all GCP plugins — once configured in pluginDefaults, individual tasks (BigQuery, GCS, etc.) inherit the credentials.

Kestra executes tasks independently according to defined dependencies. A task is marked successful if its own execution completes without error, regardless of downstream failures. This makes it possible to inspect intermediate outputs and logs (such as file size computation) even if later database or merge steps fail.

When following the YouTube lesson and then running the gcp_setup flow, you might encounter a permission denied error.

To resolve this:

1. Verify if the bucket already exists using the GCP console.
2. If it exists, choose a different name for the bucket.

Note: The GCP bucket name must be unique globally across all buckets, as the bucket will be accessible by URL.

When following the YouTube lesson and then running the gcp_setup flow, the error occurs during the create_bq_dataset task.

The error is less clear, but it stems from using a dash in the dataset name. To resolve this, change the dataset name to something like "de_zoomcamp" to avoid using a dash. This should resolve the error.

SIGILL in Java Runtime Environment on MacOS M4

Add the following environment variable to your Kestra container: -e JAVA_OPTS="-XX:UseSVE=0":

docker run --rm -it \
  --pull=always \
  -p 8080:8080 \
  --user=root \
  -e JAVA_OPTS="-XX:UseSVE=0" \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -v /tmp:/tmp \
  kestra/kestra:latest server local

The same in a Docker Compose file:

services:
  kestra:
    image: kestra/kestra:latest
    environment:
      JAVA_OPTS: "-XX:UseSVE=0"

host.docker.internal is a Docker Desktop convenience that resolves to the host machine. On native Linux Docker (and many Linux server setups) it doesn't resolve by default, so Kestra tasks that try to reach Postgres or other services via host.docker.internal fail with errors like:

The connection attempt failed.
could not translate host name "host.docker.internal" to address: Name or service not known

You have two options.

Option 1 (recommended): use the container service name

If Kestra and Postgres are in the same docker-compose.yml, just refer to Postgres by its service name. Replace host.docker.internal with the service name (e.g. postgres_zoomcamp) in pluginDefaults:

pluginDefaults:
  - type: io.kestra.plugin.jdbc.postgresql
    values:
      url: jdbc:postgresql://postgres_zoomcamp:5432/postgres-zoomcamp
      username: kestra
      password: k3str4

Apply this in flows like 02_postgres_taxi.yaml and 2_postgres_taxi_scheduled.yaml.

Option 2: add extra_hosts so host.docker.internal resolves on Linux

Add an extra_hosts entry to the Kestra service in docker-compose.yml:

services:
  kestra:
    image: kestra/kestra:latest
    extra_hosts:
      - "host.docker.internal:host-gateway"
    # ...

For the dbt-build task (or any other task using taskRunner with Docker), add extraHosts there too:

taskRunner:
  type: io.kestra.plugin.scripts.runner.docker.Docker
  extraHosts:
    - "host.docker.internal:host-gateway"

Option 1 is cleaner for inter-container communication; Option 2 is needed only when Kestra genuinely needs to reach a service running on the host (not in a sibling container).

type: io.kestra.plugin.scripts.python.Commands
commands:
  - pip install requests kestra
  - python your_script.py
InputFiles:
  your_script.py: |
    # Python script content from before

To use, replace your_script.py with your actual script filename and paste the Python code under InputFiles.

Kestra is designed to orchestrate tasks rather than perform analytical computations. Row counts represent dataset-level analytics that are best computed in the database layer after ingestion, ensuring correctness and separation of responsibilities.

Kestra supports conditional branching within a single workflow to reuse shared orchestration logic while allowing dataset-specific behavior. This approach reduces duplication, keeps related steps together, and makes execution paths explicit within a single workflow definition.

When to use a single-workflow branch:

• Reuse common steps (e.g., data validation, enrichment) across datasets.
• Implement dataset-specific paths (e.g., Yellow vs Green taxi) within the same flow.

How to implement:

• Use a branching mechanism in Kestra (e.g., a condition or switch node) to evaluate a runtime variable (such as the dataset type) and route to the appropriate sub-path.
• Place shared steps before the branch; place dataset-specific steps inside each branch.
• Decide how branches converge: continue with a common tail after the branch or treat branches as separate end states.

Best practices:

• Keep branches modular and minimal; extract shared logic into subflows where possible.
• Centralize error handling and logging to ensure consistency across branches.
• Test each branch with representative inputs and monitor branch-specific failures separately.

Example (illustrative pseudo YAML):

- id: route_by_dataset
  type: branch
  when:
    - condition: ${workflow.variables.dataset_type == 'yellow'}
      then:
        - - id: yellow_tasks
            tasks: [ yellow_validate, yellow_transform ]
    - else:
        - - id: green_tasks
            tasks: [ green_validate, green_transform ]
- id: end
  type: terminate

Note: Adapt the exact branch/task names to your Kestra workflow definitions. The key idea is to place shared logic before the branch, branch on dataset/type, and then execute dataset-specific steps within each branch.

• Kestra orchestrates workflows but does not automatically aggregate dataset-level metrics such as total row counts across multiple executions.
• Questions involving total rows for a full year or specific months require querying the target database after ingestion.
• In Homework 2, accurate row counts were obtained using SQL queries against PostgreSQL tables populated by the Kestra flows.

IANA timezone identifiers account for daylight saving transitions and historical changes. Fixed offsets or abbreviations can produce incorrect schedules during DST changes, which is why Kestra enforces IANA-based timezones.

Kestra distinguishes between execution logs and structured outputs. Values printed to stdout are treated as logs for observability, not as structured data. Explicit output definitions are required to persist or reuse values across tasks.

Kestra isolates task execution contexts. Files are only persisted and made available to subsequent tasks or the UI when explicitly declared under outputFiles. Without this declaration, files may exist temporarily during execution but are not surfaced or retained for inspection.

Kestra runs many tasks inside Docker containers, and Kestra itself is responsible for starting those containers when needed. To do this, Kestra needs access to the host Docker daemon, which is exposed via the Docker socket at /var/run/docker.sock. In the official Docker Compose setup for Kestra, the container runs as root and mounts /var/run/docker.sock:/var/run/docker.sock, because the Docker Compose implementation requires root privileges to access the socket. This configuration is intended for development purposes.

Kestra displays file sizes in the Outputs tab using human-readable units (e.g., MiB), while a Python task using os.path.getsize() returns the raw file size in bytes. For Homework 2, the correct value is the uncompressed file size in bytes (as printed in task logs), not the rounded UI value. The UI size is a convenience display and should not be used for exact comparisons.

Yes, you can integrate dbt with Kestra to combine dbt's transformation capabilities with Kestra's orchestration, monitoring, and Git integration. There are three main capabilities worth highlighting:

• Orchestrating dbt runs as part of Kestra pipelines
• Centralized monitoring and alerting for dbt runs via Kestra
• Git integration for triggering and tracking dbt projects within Kestra pipelines

How to edit Kestra flows locally with Docker Compose

By default, Kestra stores its flow definitions in a database. This means that every time you edit a flow through the web UI, the source of truth lives inside the container, not in your repository. That is fine for quick experiments, but for a real project you want flows version-controlled alongside the rest of the code and editable with your favourite IDE.

The solution is a bind-mount combined with Kestra's built-in file watcher. The idea goes like this: you mount a host directory into the container and tell Kestra to watch it. Any YAML file you create or modify in that folder is automatically synced into Kestra's catalog.

1. Bind-mount your flows directory

In docker-compose.yml, add a volume entry that maps a local folder (here ./flows) to a path inside the container (here /flows):

services:
  kestra:
    image: kestra/kestra:v1.3
    command: server standalone
    volumes:
      - kestra-data:/app/storage
      - /var/run/docker.sock:/var/run/docker.sock
      - /tmp/kestra-wd:/tmp/kestra-wd
      - ./flows:/flows

With this single line, anything you put in ./flows on the host appears at /flows inside the container.

2. Enable the file watcher

Kestra uses Micronaut under the hood. You can activate its file-system watcher through the KESTRA_CONFIGURATION environment variable:

services:
  kestra:
    environment:
      KESTRA_CONFIGURATION: |
        micronaut:
          io:
            watch:
              enabled: true
              paths:
                - /flows

The paths list must point to the container-side path — /flows in our case, not the host path.

3. Write your flows as local YAML files

Create any flow definition inside the ./flows directory on your host. For example, ./flows/dev.hello.yml:

id: hello
namespace: dev
tasks:
- id: say_hi
  type: io.kestra.plugin.core.log.Log
  message: "Hello from a local file!"

As soon as you save the file, the watcher picks up the change and Kestra imports (or updates) the flow. You can verify it in the web UI at http://localhost:8080.

How the sync works

• From Host to Kestra is automatic thanks to the watcher. Every time a YAML file is created or modified in the watched directory, Kestra upserts the corresponding flow.
• From Kestra to Host does not happen. If you edit a flow through the web UI, the change is written to the database only; the YAML file on the host is not updated. To keep things consistent, treat the local files as the single source of truth and avoid editing flows in the UI.

Putting it all together

A minimal docker-compose.yml that includes everything discussed above:

services:
  postgres:
    image: postgres:18
    environment:
      POSTGRES_DB: kestra
      POSTGRES_USER: kestra
      POSTGRES_PASSWORD: k3str4
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -d kestra -U kestra"]
      interval: 30s
      timeout: 10s
      retries: 10

  kestra:
    image: kestra/kestra:v1.3
    command: server standalone
    user: "root"
    volumes:
      - kestra-data:/app/storage
      - /var/run/docker.sock:/var/run/docker.sock
      - /tmp/kestra-wd:/tmp/kestra-wd
      - ./flows:/flows
    environment:
      KESTRA_CONFIGURATION: |
        micronaut:
          io:
            watch:
              enabled: true
              paths:
                - /flows
        datasources:
          postgres:
            url: jdbc:postgresql://postgres:5432/kestra
            driverClassName: org.postgresql.Driver
            username: kestra
            password: k3str4
        kestra:
          repository:
            type: postgres
          queue:
            type: postgres
        tasks:
          tmp-dir:
            path: /tmp/kestra-wd/tmp
    ports:
      - "8080:8080"
    depends_on:
      postgres:
        condition: service_healthy

volumes:
  kestra-data:

Run docker compose up -d, drop a YAML flow into ./flows, and it will appear in Kestra within seconds, fully version-controlled and editable from your host machine.

For more information, check Kestra's official guide to sync local flows.

In Codespaces, localhost in your browser does not point to the Docker container. Kestra may be running correctly inside Docker, but you must access it through the forwarded port URL, not http://localhost:8080.

What to do:

• Open the Codespaces UI and go to the Ports (forwarded ports) section.
• Ensure port 8080 (or the port Kestra is listening on) is forwarded.
• Copy the forwarded URL for port 8080. It will look like a Codespaces-provided URL (not http://localhost:8080).
• In your browser, navigate to that forwarded URL instead of http://localhost:8080.

If you still see ERR_EMPTY_RESPONSE:

• Check that the Kestra container is actually running and that Kestra is listening on the configured port.
• Check the container logs for errors.
• If necessary, restart the Kestra container.

Notes:

• The forwarded URL is provided by Codespaces in the Ports panel.
• Once you access via the forwarded URL, you should be able to reach Kestra as normal.

It seems to be a bug. The current fix is to remove the timezone from triggers in the script. More on this bug is here.

The issue comes down to how Unix processes produce output and how Kestra interprets it:

1. Python's logging module writes to stderr by default. If you, for instance, call logging.basicConfig() without specifying a stream argument, the root handler sends basically everything to stderr.

2. Kestra maps the two standard streams to its own log levels. Anything the container writes to stdout becomes a Kestra DEBUG entry and anything written on stderr becomes ERROR. There is no middle ground.

The fix

Good news is that the fix is simple: redirect Python logging to stdout:

import sys
import logging

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s %(levelname)s %(message)s",
    stream=sys.stdout,
)

That single stream=sys.stdout argument is enough. After the change, your informational messages will show up as DEBUG in Kestra.

Make sure to use Nullable data types, such as Int64 when applicable.

Ultimately, when trying to ingest data into a BigQuery table, all files within a given directory must have the same schema.

When dealing with datasets, such as the FHV Datasets from 2019, you may encounter schema inconsistencies. For example, the files for '2019-05' and '2019-06' have the columns "PUlocationID" and "DOlocationID" as integers, while for the period of '2019-01' through '2019-04', the same columns are defined as floats.

When importing these files as Parquet to BigQuery, the first file will define the table schema. All subsequent files must have the same schema to append data correctly.

To prevent errors like this, enforce the data types for the columns on the DataFrame before serializing/uploading them to BigQuery:

pd.read_csv("path_or_url").astype({"col1_name": "datatype", "col2_name": "datatype", ..., "colN_name": "datatype"})

If you receive the error

gzip.BadGzipFile: Not a gzipped file (b'\n\n')

this is because you have specified the wrong URL to the FHV dataset. Make sure to use:

https://github.com/DataTalksClub/nyc-tlc-data/releases/download/fhv/{dataset_file}.csv.gz

Emphasize the /releases/download part of the URL.

You can use a TSV file with a list of URLs to load data into a GCS bucket. Create a file (e.g., urls.tsv) with the following format:

TsvHttpData-1.0
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-01.parquet
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-02.parquet
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-03.parquet
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-04.parquet
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-05.parquet
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-06.parquet
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-07.parquet
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-08.parquet
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-09.parquet
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-10.parquet
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-11.parquet
https://d37ci6vzurychx.cloudfront.net/trip-data/green_tripdata_2022-12.parquet

Then use gsutil or the GCS Transfer Service to load these URLs into your bucket.

• Check the Schema: Ensure that the schema of your dataset is correctly defined.

• Formatting Issues: You might have incorrect formatting in your files.

• Upload Method: Try uploading the CSV.GZ files without formatting or processing them through pandas. Use wget to download if necessary.

Run the following command to check if "BigQuery Command Line Tool" is installed or not:

gcloud components list

You can also use bq.cmd instead of bq to make it work.

Use BigQuery carefully:

• I created my BigQuery dataset on an account where my free trial was exhausted and received a bill of $80.
• Use BigQuery under free credits and destroy all the datasets after creation.
• Check your billing daily, especially if you've spun up a VM.

BigQuery cannot load data from a GCS bucket into a dataset in a different region — they must match. If your bucket is in EU and your BigQuery dataset is in US (or us-central1 vs asia-south2, etc.), you'll see:

Cannot read and write in different locations: source: <region-A>, destination: <region-B>

1. Check the bucket's region in the GCS console (the "Location" field on the bucket details page).
2. Create a BigQuery dataset in the same region. Click the three-dot menu next to your project → "Create dataset" → set "Data location" to match the bucket exactly (e.g. us-central1, not just US).
3. Load the data into this newly-created dataset.

If you only ever need one region, set up your project resources (bucket and dataset) in the same region from the start.

For dbt-specific region issues (datasets created automatically by dbt build, prod vs dev mismatches, CI), see the corresponding dbt + BigQuery region FAQ in module 4.

It's important to save your progress in the BigQuery SQL Editor frequently.

Here are some tips:

• Save Regularly: Use the save button at the top bar in the BigQuery SQL Editor. Your saved queries will be available on the left panel.

  

• Alternative Method: Copy and paste your queries into a file using a text editor like Notepad++ or VS Code. Save it with a .sql extension to benefit from syntax highlighting.

By following these methods, you can avoid losing your work in case of unexpected browser issues.

While real-time analytics might not be explicitly mentioned, BigQuery has real-time data streaming capabilities, allowing for potential integration in future project iterations.

could not parse 'pickup_datetime' as timestamp for field pickup_datetime (position 2)

This error is caused by invalid data in the timestamp column. To resolve this issue:

1. Define the schema of the external table using the STRING datatype for the timestamp column. This allows queries to execute without errors.
2. Filter out the invalid timestamp rows during data import.
3. Insert the filtered rows into the materialized table, specifying the TIMESTAMP datatype for the timestamp fields.

When you encounter this BigQuery error, it typically relates to how timestamps are stored in Parquet files.

To resolve this issue, you can modify the Parquet writing configuration by adding use_deprecated_int96_timestamps=True to the pq.write_to_dataset function. This setting writes timestamps in the INT96 format, which can be more compatible with BigQuery.

Here’s how you can adjust the function:

pq.write_to_dataset(
    table,
    root_path=root_path,
    filesystem=gcs,
    use_deprecated_int96_timestamps=True  # Write timestamps to INT96 Parquet format
)

• Stack Overflow - Parquet compatibility with PyArrow vs PySpark
• Stack Overflow - Editing Parquet files and datetime format errors
• Reddit - Parquet Timestamp to BQ issues

Use the above configuration to ensure compatibility with Google BigQuery when dealing with timestamps in Parquet files.

When writing Parquet files from a Pandas DataFrame, Pandas may emit timestamp columns as integers (epoch milliseconds) by default. When BigQuery loads those files it sees raw INT64 values and won't auto-promote them to TIMESTAMP.

Solution 1: Coerce timestamps when writing the Parquet file

Use PyArrow with coerce_timestamps='us' so the file carries proper TIMESTAMP(MICROS) logical types:

import pyarrow as pa
import pyarrow.parquet as pq

table = pa.Table.from_pandas(df, preserve_index=False)

pq.write_table(
    table,
    'gs://<bucket>/<path>.parquet',
    # Force TIMESTAMP(MICROS) so BigQuery loads the column as TIMESTAMP
    coerce_timestamps='us',
    filesystem=pa.fs.GcsFileSystem(),
)

Solution 2: Provide an explicit schema

If you want full control over column types, pass an explicit PyArrow schema instead of relying on inference:

schema = pa.schema([
    ('vendor_id', pa.int64()),
    ('lpep_pickup_datetime', pa.timestamp('us')),
    ('lpep_dropoff_datetime', pa.timestamp('us')),
    # ...
])

table = pa.Table.from_pandas(df, schema=schema)

Either approach also works inside an orchestrator's data-export task — adapt the pq.write_table call to fit your task's signature.

Reference:

https://cloud.google.com/bigquery/docs/external-data-cloud-storage

Solution:

from google.cloud import bigquery

# Set table_id to the ID of the table to create
table_id = f"{project_id}.{dataset_name}.{table_name}"

# Construct a BigQuery client object
client = bigquery.Client()

# Set the external source format of your table
external_source_format = "PARQUET"

# Set the source_uris to point to your data in Google Cloud
source_uris = [f'gs://{bucket_name}/{object_key}/*']

# Create ExternalConfig object with external source format
external_config = bigquery.ExternalConfig(external_source_format)

# Set source_uris that point to your data in Google Cloud
external_config.source_uris = source_uris
external_config.autodetect = True

table = bigquery.Table(table_id)

# Set the external data configuration of the table
table.external_data_configuration = external_config
table = client.create_table(table)  # Make an API request.

print(f'Created table with external source: {table_id}')
print(f'Format: {table.external_data_configuration.source_format}')

Stack Overflow - BigQuery Overwrite Table

To check if a BigQuery table exists and possibly delete it, utilize the following Python function before using client.create_table:

from google.cloud import bigquery

# Initialize client
client = bigquery.Client()

def table_exists(table_id, client):
    """
    Check if a table already exists using the tableID.

    :param table_id: str, the ID of the table
    :param client: bigquery.Client instance
    :return: Boolean
    """
    try:
        client.get_table(table_id)
        return True
    except Exception as e:  # NotFound:
        return False

Use this function to check table existence before creating a new table or taking further actions.

To avoid this error, you can upload data from Google Cloud Storage to BigQuery through BigQuery Cloud Shell using the command:

bq load --autodetect --allow_quoted_newlines --source_format=CSV dataset_name.table_name "gs://dtc-data-lake-bucketname/fhv/fhv_tripdata_2019-*.csv.gz"

There are multiple benefits of using Cloud Functions to automate tasks in Google Cloud.

Use the following Cloud Function Python script to load files directly into BigQuery. Set your project ID, dataset ID, and table ID accordingly.

import tempfile
import requests
import logging
from google.cloud import bigquery

def hello_world(request):
    # table_id = <project_id.dataset_id.table_id>
    table_id = 'de-zoomcap-project.dezoomcamp.fhv-2019'

    # Create a new BigQuery client
    client = bigquery.Client()

    for month in range(4, 13):
        # Define the schema for the data in the CSV.gz files
        url = 'https://github.com/DataTalksClub/nyc-tlc-data/releases/download/fhv/fhv_tripdata_2019-{:02d}.csv.gz'.format(month)

        # Download the CSV.gz file from Github
        response = requests.get(url)

        # Create new table if loading first month data else append
        write_disposition_string = "WRITE_APPEND" if month > 1 else "WRITE_TRUNCATE"

        # Defining LoadJobConfig with schema of table to prevent it from changing with every table
        job_config = bigquery.LoadJobConfig(
            schema=[
                bigquery.SchemaField("dispatching_base_num", "STRING"),
                bigquery.SchemaField("pickup_datetime", "TIMESTAMP"),
                bigquery.SchemaField("dropOff_datetime", "TIMESTAMP"),
                bigquery.SchemaField("PUlocationID", "STRING"),
                bigquery.SchemaField("DOlocationID", "STRING"),
                bigquery.SchemaField("SR_Flag", "STRING"),
                bigquery.SchemaField("Affiliated_base_number", "STRING"),
            ],
            skip_leading_rows=1,
            write_disposition=write_disposition_string,
            autodetect=True,
            source_format="CSV",
        )

        # Load the data into BigQuery
        # Create a temporary file to prevent the exception: AttributeError: 'bytes' object has no attribute 'tell'
        with tempfile.NamedTemporaryFile() as f:
            f.write(response.content)
            f.seek(0)

            job = client.load_table_from_file(
                f,
                table_id,
                location="US",
                job_config=job_config,
            )

            job.result()

        logging.info("Data for month %d successfully loaded into table %s.", month, table_id)

    return 'Data loaded into table {}.'.format(table_id)

You need to uncheck cache preferences in query settings

When injecting data into GCS using Pandas, some datasets might have missing values in the DOlocationID and PUlocationID columns. By default, Pandas will cast these columns as float, leading to inconsistent data types between the Parquet files in GCS and the schema defined in BigQuery. You might encounter the following error:

error: Error while reading table: trips_data_all.external_fhv_tripdata, error message: Parquet column 'DOlocationID' has type INT64 which does not match the target cpp_type DOUBLE.

Fix the data type issue in the data pipeline:

1. Before injecting data into GCS, use astype and Int64 (which is different from int64 and accepts both missing values and integers) to cast the columns.

   Example:

   df["PUlocationID"] = df.PUlocationID.astype("Int64")
   df["DOlocationID"] = df.DOlocationID.astype("Int64")
   

2. It is best to define the data type of all the columns in the Transformation section of the ETL pipeline before loading to BigQuery.

The problem occurs when there is a misplacement of content after the FROM clause in BigQuery SQLs. Check to remove any extra spaces or symbols; ensure project IDs are in lowercase, digits, and dashes only.

The most commonly hit BigQuery limits in the course:

• Partition columns per table: 1. You cannot partition by multiple columns. (docs)
• Cluster columns per table: up to 4. You can cluster on a tuple of columns up to that limit. (docs)
• Partitions per table: 10,000 (older docs and the course playlist may say 4,000 — that limit was raised). (docs)
• Partitions modified by a single job: 4,000. A single load/query/copy/DML job can't touch more than 4,000 partitions at once.

Implications for time-based partitioning under the 10,000 partition limit:

• Daily partitions cover ~27 years.
• Hourly partitions cover ~416 days (just over a year).
• Monthly partitions cover over 800 years.

So daily partitioning is fine for almost any workload; hourly partitioning needs a retention strategy if your data goes back more than ~1 year.

To reproduce a BigQuery table's schema and data layout, you can query the INFORMATION_SCHEMA.TABLES to retrieve the DDL needed to recreate the table.

For a normal (native) table in a dataset

SELECT table_name, ddl
FROM zoomcamp.INFORMATION_SCHEMA.TABLES
WHERE table_name = 'yellow_tripdata_parquet';

The result is the CREATE TABLE statement needed to recreate the table, e.g.:

CREATE TABLE `zoomcamp-ingenieria-datos.zoomcamp.yellow_tripdata_parquet`
(
    VendorID INT64,
    tpep_pickup_datetime TIMESTAMP,
    tpep_dropoff_datetime TIMESTAMP,
    passenger_count INT64,
    trip_distance FLOAT64,
    RatecodeID INT64,
    store_and_fwd_flag STRING,
    PULocationID INT64,
    DOLocationID INT64,
    payment_type INT64,
    fare_amount FLOAT64,
    extra FLOAT64,
    mta_tax FLOAT64,
    tip_amount FLOAT64,
    tolls_amount FLOAT64,
    improvement_surcharge FLOAT64,
    total_amount FLOAT64,
    congestion_surcharge FLOAT64,
    Airport_fee FLOAT64
);

For an external table

SELECT table_name, ddl
FROM zoomcamp.INFORMATION_SCHEMA.TABLES
WHERE table_name = 'yellow_tripdata_parquet_ext';