DEV Community: Abdelrahman Adnan

Part 3: Testing, Documentation & Deployment 🚀

Abdelrahman Adnan — Mon, 16 Feb 2026 22:46:01 +0000

DataEngineeringZoomcamp #dbt #AnalyticsEngineering #DataModeling

Macros - Reusable SQL Functions 🔧

Macros are like functions in Python - write once, use everywhere.

Why Use Macros?

Without macros, you repeat code:

-- ❌ Repeated everywhere
CASE 
    WHEN payment_type = 1 THEN 'Credit card'
    WHEN payment_type = 2 THEN 'Cash'
    WHEN payment_type = 3 THEN 'No charge'
    WHEN payment_type = 4 THEN 'Dispute'
    WHEN payment_type = 5 THEN 'Unknown'
    ELSE 'Unknown'
END as payment_type_description

With macros, write it once:

-- macros/get_payment_type_description.sql
{% macro get_payment_type_description(payment_type) %}
    CASE {{ payment_type }}
        WHEN 1 THEN 'Credit card'
        WHEN 2 THEN 'Cash'
        WHEN 3 THEN 'No charge'
        WHEN 4 THEN 'Dispute'
        WHEN 5 THEN 'Unknown'
        ELSE 'Unknown'
    END
{% endmacro %}

Use it in any model:

-- models/staging/stg_green_tripdata.sql
select
    payment_type,
    {{ get_payment_type_description('payment_type') }} as payment_type_description
from {{ source('staging', 'green_tripdata') }}

Jinja Templating

dbt uses Jinja - a Python templating language. You'll recognize it by {{ }} and {% %}:

Syntax	Purpose	Example
`{{ }}`	Output expression	`{{ ref('my_model') }}`
`{% %}`	Logic/control flow	`{% if is_incremental() %}`
`{# #}`	Comments	`{# This is a comment #}`

dbt Packages - Community Libraries 📦

Packages let you use macros and models built by others.

Popular Packages

Package	What it Does
dbt_utils	Common SQL helpers (surrogate keys, pivot, etc.)
dbt_codegen	Auto-generate YAML and SQL
dbt_expectations	Great Expectations-style tests
dbt_audit_helper	Compare model outputs when refactoring

Installing Packages

Create packages.yml:

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1

Run dbt deps:

dbt deps

Use the macros:

-- Using dbt_utils to generate surrogate keys
select
    {{ dbt_utils.generate_surrogate_key(['vendorid', 'pickup_datetime']) }} as trip_id,
    *
from {{ source('staging', 'green_tripdata') }}

Testing in dbt 🧪

Tests ensure your data meets expectations. dbt has several test types:

1. Generic Tests (Most Common)

Built-in tests you apply in YAML:

# models/staging/schema.yml
version: 2

models:
  - name: stg_green_tripdata
    columns:
      - name: trip_id
        tests:
          - unique       # No duplicate values
          - not_null     # No null values

      - name: payment_type
        tests:
          - accepted_values:
              values: [1, 2, 3, 4, 5, 6]  # Only these values allowed

      - name: pickup_location_id
        tests:
          - relationships:  # Referential integrity
              to: ref('dim_zones')
              field: location_id

The four built-in tests:
| Test | What it Checks |
|------|----------------|
| unique | No duplicate values in column |
| not_null | No NULL values in column |
| accepted_values | Values must be in specified list |
| relationships | Values must exist in another table |

2. Singular Tests

Custom SQL tests in the tests/ folder:

-- tests/assert_positive_fare_amount.sql
-- Test FAILS if any rows are returned

select
    trip_id,
    fare_amount
from {{ ref('fct_trips') }}
where fare_amount < 0  -- Find negative fares (bad data!)

3. Source Freshness Tests

Check if your source data is up to date:

sources:
  - name: staging
    tables:
      - name: green_tripdata
        freshness:
          warn_after: {count: 24, period: hour}
          error_after: {count: 48, period: hour}
        loaded_at_field: pickup_datetime

Running Tests

# Run all tests
dbt test

# Run tests for specific model
dbt test --select stg_green_tripdata

# Run tests and models together
dbt build

Documentation 📝

dbt generates beautiful documentation automatically!

Adding Descriptions

In your schema YAML:

version: 2

models:
  - name: fct_trips
    description: >
      Fact table containing all taxi trips (yellow and green).
      One row per trip with fare details and zone information.

    columns:
      - name: trip_id
        description: Unique identifier for each trip (surrogate key)

      - name: service_type
        description: Type of taxi service - 'Yellow' or 'Green'

      - name: total_amount
        description: Total trip cost including fare, tips, taxes, and fees

Generating Docs

# Generate documentation
dbt docs generate

# Serve locally (opens browser)
dbt docs serve

This creates an interactive website with:

Model descriptions
Column definitions
Dependency graph (visual DAG)
Source information

Essential dbt Commands 💻

The Big Four

Command	What it Does
`dbt run`	Build all models (create views/tables)
`dbt test`	Run all tests
`dbt build`	Run + test together (recommended!)
`dbt compile`	Generate SQL without executing

Other Useful Commands

# Check connection
dbt debug

# Load seed files
dbt seed

# Install packages
dbt deps

# Generate docs
dbt docs generate

# Retry failed models
dbt retry

Selecting Specific Models

Use --select (or -s) to run specific models:

# Single model
dbt run --select stg_green_tripdata

# Model and all upstream dependencies
dbt run --select +fct_trips

# Model and all downstream models
dbt run --select stg_green_tripdata+

# Both directions
dbt run --select +fct_trips+

# All models in a folder
dbt run --select staging.*

# Multiple models
dbt run --select stg_green_tripdata stg_yellow_tripdata

Target Environments

# Development (default)
dbt run

# Production
dbt run --target prod

Materializations - Views vs Tables 📊

Materialization controls how dbt persists your models in the warehouse.

Types of Materializations

Type	What it Creates	Use Case
view	SQL view (query stored, runs on access)	Staging models, frequently changing logic
table	Physical table (data stored)	Final marts, large datasets, performance
incremental	Appends new data only	Very large tables, event data
ephemeral	Not created (CTE in downstream)	Helper models, intermediate steps

Setting Materializations

In the model file:

{{ config(materialized='table') }}

select * from {{ ref('stg_trips') }}

In dbt_project.yml (project-wide):

models:
  my_project:
    staging:
      materialized: view
    marts:
      materialized: table

View vs Table Decision

┌─────────────────────────────────────────────────────────────┐
│                 Should I use view or table?                  │
└─────────────────────────────────────────────────────────────┘
                            │
                            ▼
              ┌──────────────────────────┐
              │ Is the query expensive?  │
              └──────────────────────────┘
                     │            │
                    Yes          No
                     │            │
                     ▼            ▼
               ┌─────────┐  ┌─────────┐
               │  TABLE  │  │  VIEW   │
               └─────────┘  └─────────┘

Use VIEW when:

Staging models (simple transformations)
Logic changes frequently
Storage cost is a concern

Use TABLE when:

Final marts queried often
Complex joins/aggregations
Query performance matters

Putting It All Together - The NYC Taxi Project 🚕

In this module, we build a complete dbt project for NYC taxi data:

What We Build

┌──────────────────────────────────────────────────────────────┐
│                      RAW DATA                                 │
│  green_tripdata (GCS/BigQuery) │ yellow_tripdata (GCS/BigQuery)│
└───────────────────┬─────────────────────┬────────────────────┘
                    │                     │
                    ▼                     ▼
┌──────────────────────────────────────────────────────────────┐
│                    STAGING LAYER                              │
│      stg_green_tripdata    │    stg_yellow_tripdata          │
│      (cleaned, renamed)    │    (cleaned, renamed)           │
└───────────────────┬─────────────────────┬────────────────────┘
                    │                     │
                    └──────────┬──────────┘
                               │
                               ▼
┌──────────────────────────────────────────────────────────────┐
│                  INTERMEDIATE LAYER                           │
│                   int_trips_unioned                           │
│            (green + yellow combined)                          │
└───────────────────────────────┬──────────────────────────────┘
                                │
                                ▼
┌──────────────────────────────────────────────────────────────┐
│                      MARTS LAYER                              │
│  ┌─────────────┐  ┌───────────────┐  ┌─────────────────────┐ │
│  │ dim_zones   │  │   fct_trips   │  │fct_monthly_zone_rev │ │
│  │ (dimension) │  │    (fact)     │  │     (report)        │ │
│  └─────────────┘  └───────────────┘  └─────────────────────┘ │
└──────────────────────────────────────────────────────────────┘

The Models We Create

Model	Type	Description
`stg_green_tripdata`	Staging	Cleaned green taxi data
`stg_yellow_tripdata`	Staging	Cleaned yellow taxi data
`int_trips_unioned`	Intermediate	Combined yellow + green trips
`dim_zones`	Dimension	Zone lookup table
`fct_trips`	Fact	One row per trip
`fct_monthly_zone_revenue`	Report	Monthly revenue by zone

Setup Options 🔧

Option 1: Local Setup (DuckDB + dbt Core)

Pros: Free, no cloud account needed
Cons: Limited to your machine's power

# 1. Install dbt with DuckDB adapter
pip install dbt-duckdb

# 2. Clone the project
git clone https://github.com/DataTalksClub/data-engineering-zoomcamp
cd data-engineering-zoomcamp/04-analytics-engineering/taxi_rides_ny

# 3. Create profiles.yml in ~/.dbt/
# 4. Run dbt debug to test connection
dbt debug

# 5. Build the project
dbt build --target prod

Option 2: Cloud Setup (BigQuery + dbt Cloud)

Pros: Powerful, team collaboration, scheduler
Cons: Requires GCP account (free tier available)

Create dbt Cloud account (free)
Connect to your BigQuery project
Clone the repo in dbt Cloud IDE
Run dbt build --target prod

Troubleshooting Common Issues 🔍

"Profile not found"

Check dbt_project.yml profile name matches profiles.yml
Ensure profiles.yml is in ~/.dbt/

"Source not found"

Verify database/schema names in sources.yml
Check your data is actually loaded in the warehouse

"Model depends on model that was not found"

Check for typos in ref() calls
Ensure referenced model exists

DuckDB Out of Memory

Add memory settings to profiles.yml:

settings:
  memory_limit: '2GB'

Key Takeaways 🎓

Analytics Engineering bridges data engineering and data analysis
dbt brings software engineering best practices to SQL transformations
Dimensional modeling organizes data into facts (events) and dimensions (attributes)
Three layers - staging (raw copy), intermediate (transformations), marts (final)
ref() and source() are your main functions for building dependencies
Testing ensures data quality - use unique, not_null, accepted_values, relationships
Documentation is auto-generated from YAML descriptions
dbt build runs and tests everything in dependency order

Additional Resources 📚

Part 2: dbt Project Structure & Building Models 📁

Abdelrahman Adnan — Mon, 16 Feb 2026 22:45:11 +0000

DataEngineeringZoomcamp #dbt #AnalyticsEngineering #DataModeling

Why Model Data? 📐

Raw data is messy and hard to query. Dimensional modeling organizes data into a structure that's:

Easy to understand
Fast to query
Flexible for different analyses

Fact Tables vs Dimension Tables

This is the core of dimensional modeling (also called "star schema"):

Fact Tables (fct_)

Contain measurements or events
One row per thing that happened
Usually have many rows (millions/billions)
Contain numeric values you want to analyze

Examples:

fct_trips - one row per taxi trip
fct_sales - one row per sale
fct_orders - one row per order

-- Example fact table
CREATE TABLE fct_trips AS
SELECT
    trip_id,           -- unique identifier
    pickup_datetime,   -- when it happened
    dropoff_datetime,
    pickup_zone_id,    -- foreign keys to dimensions
    dropoff_zone_id,
    fare_amount,       -- numeric measures
    tip_amount,
    total_amount
FROM transformed_trips;

Dimension Tables (dim_)

Contain attributes or descriptive information
One row per entity
Usually fewer rows
Provide context for fact tables

Examples:

dim_zones - one row per taxi zone
dim_customers - one row per customer
dim_products - one row per product

-- Example dimension table
CREATE TABLE dim_zones AS
SELECT
    location_id,       -- primary key
    borough,           -- descriptive attributes
    zone_name,
    service_zone
FROM zone_lookup;

The Star Schema ⭐

When you join facts and dimensions, you get a star shape:

                    ┌──────────────┐
                    │  dim_zones   │
                    │  (pickup)    │
                    └───────┬──────┘
                            │
┌──────────────┐    ┌───────┴──────┐    ┌──────────────┐
│  dim_vendors │────│  fct_trips   │────│  dim_zones   │
│              │    │  (center)    │    │  (dropoff)   │
└──────────────┘    └───────┬──────┘    └──────────────┘
                            │
                    ┌───────┴──────┐
                    │ dim_payment  │
                    │    types     │
                    └──────────────┘

Why it's powerful:

-- Easy to answer business questions!
SELECT 
    z.borough,
    COUNT(*) as trip_count,
    SUM(f.total_amount) as total_revenue
FROM fct_trips f
JOIN dim_zones z ON f.pickup_zone_id = z.location_id
GROUP BY z.borough
ORDER BY total_revenue DESC;

dbt Project Structure

A dbt project has a specific folder structure. Understanding this helps you navigate any project:

taxi_rides_ny/
├── dbt_project.yml      # Project configuration (most important!)
├── profiles.yml         # Database connection (often in ~/.dbt/)
├── packages.yml         # External packages to install
│
├── models/              # ⭐ YOUR SQL MODELS LIVE HERE
│   ├── staging/         # Raw data, minimally cleaned
│   ├── intermediate/    # Complex transformations
│   └── marts/           # Final, business-ready tables
│
├── seeds/               # CSV files to load as tables
├── macros/              # Reusable SQL functions
├── tests/               # Custom test files
├── snapshots/           # Track data changes over time
└── analysis/            # Ad-hoc queries (not built)

The `dbt_project.yml` File

This is the most important file - dbt looks for it first:

name: 'taxi_rides_ny'
version: '1.0.0'
profile: 'taxi_rides_ny'  # Must match profiles.yml!

# Default configurations
models:
  taxi_rides_ny:
    staging:
      materialized: view  # Staging models become views
    marts:
      materialized: table # Mart models become tables

The Three Model Layers

dbt recommends organizing models into three layers:

1. Staging Layer (staging/)

Purpose: Clean copy of raw data with minimal transformations

What happens here:

Rename columns (snake_case, clear names)
Cast data types
Filter obviously bad data
Keep 1:1 with source (same rows, similar columns)

-- models/staging/stg_green_tripdata.sql
{{ config(materialized='view') }}

with tripdata as (
    select * 
    from {{ source('staging', 'green_tripdata') }}
    where vendorid is not null  -- filter bad data
)

select
    -- Rename and cast columns
    cast(vendorid as integer) as vendor_id,
    cast(lpep_pickup_datetime as timestamp) as pickup_datetime,
    cast(lpep_dropoff_datetime as timestamp) as dropoff_datetime,
    cast(pulocationid as integer) as pickup_location_id,
    cast(dolocationid as integer) as dropoff_location_id,
    cast(passenger_count as integer) as passenger_count,
    cast(trip_distance as numeric) as trip_distance,
    cast(fare_amount as numeric) as fare_amount,
    cast(total_amount as numeric) as total_amount
from tripdata

2. Intermediate Layer (intermediate/)

Purpose: Complex transformations, joins, business logic

What happens here:

Combine multiple staging models
Apply business rules
Heavy data manipulation
NOT exposed to end users

-- models/intermediate/int_trips_unioned.sql
with green_trips as (
    select *, 'Green' as service_type
    from {{ ref('stg_green_tripdata') }}
),

yellow_trips as (
    select *, 'Yellow' as service_type
    from {{ ref('stg_yellow_tripdata') }}
)

select * from green_trips
union all
select * from yellow_trips

3. Marts Layer (marts/)

Purpose: Final, business-ready tables for end users

What happens here:

Final fact and dimension tables
Ready for dashboards and reports
Only these should be exposed to BI tools!

-- models/marts/fct_trips.sql
{{ config(materialized='table') }}

select
    t.trip_id,
    t.service_type,
    t.pickup_datetime,
    t.dropoff_datetime,
    t.pickup_location_id,
    t.dropoff_location_id,
    z_pickup.zone as pickup_zone,
    z_dropoff.zone as dropoff_zone,
    t.passenger_count,
    t.trip_distance,
    t.fare_amount,
    t.total_amount
from {{ ref('int_trips_unioned') }} t
left join {{ ref('dim_zones') }} z_pickup 
    on t.pickup_location_id = z_pickup.location_id
left join {{ ref('dim_zones') }} z_dropoff 
    on t.dropoff_location_id = z_dropoff.location_id

Sources and the `source()` Function 📥

What are Sources?

Sources tell dbt where your raw data lives in the warehouse. They're defined in YAML files:

# models/staging/sources.yml
version: 2

sources:
  - name: staging           # Logical name (you choose)
    database: my_project    # Your GCP project or database
    schema: nytaxi          # BigQuery dataset or schema
    tables:
      - name: green_tripdata
      - name: yellow_tripdata

Using the `source()` Function

Instead of hardcoding table names, use source():

-- ❌ Bad - hardcoded path
SELECT * FROM my_project.nytaxi.green_tripdata

-- ✅ Good - using source()
SELECT * FROM {{ source('staging', 'green_tripdata') }}

Benefits:

Change database/schema in one place (YAML file)
dbt tracks dependencies automatically
Can add freshness tests on sources

The `ref()` Function - Building Dependencies 🔗

This is the most important dbt function!

`source()` vs `ref()`

Function	Use When	Example
`source()`	Reading raw/external data	`{{ source('staging', 'green_tripdata') }}`
`ref()`	Reading another dbt model	`{{ ref('stg_green_tripdata') }}`

How `ref()` Works

-- models/marts/fct_trips.sql
select *
from {{ ref('int_trips_unioned') }}  -- References the int_trips_unioned model

What ref() does:

✅ Resolves to the correct schema/table name
✅ Builds the dependency graph automatically
✅ Ensures models run in the correct order

The DAG (Directed Acyclic Graph)

dbt builds a dependency graph from your ref() calls:

┌──────────────────┐     ┌──────────────────┐
│ stg_green_trips  │     │ stg_yellow_trips │
└────────┬─────────┘     └────────┬─────────┘
         │                        │
         └──────────┬─────────────┘
                    │
                    ▼
         ┌──────────────────┐
         │ int_trips_unioned│
         └────────┬─────────┘
                  │
                  ▼
         ┌──────────────────┐
         │    fct_trips     │
         └──────────────────┘

When you run dbt build, models run in dependency order automatically!

Seeds - Loading CSV Files 🌱

Seeds let you load small CSV files into your warehouse as tables.

When to Use Seeds

✅ Good use cases:

Lookup tables (zone names, country codes)
Static mappings (vendor ID → vendor name)
Small reference data that rarely changes

❌ Not good for:

Large datasets (use proper data loading)
Frequently changing data

How to Use Seeds

Put CSV files in the seeds/ folder:

seeds/
└── taxi_zone_lookup.csv

locationid,borough,zone,service_zone
1,EWR,Newark Airport,EWR
2,Queens,Jamaica Bay,Boro Zone
3,Bronx,Allerton/Pelham Gardens,Boro Zone
...

Run dbt seed:

dbt seed

Reference in models using ref():

-- models/marts/dim_zones.sql
select
    locationid as location_id,
    borough,
    zone,
    service_zone
from {{ ref('taxi_zone_lookup') }}

# Module 4 Summary - Analytics Engineering with dbt

Abdelrahman Adnan — Mon, 16 Feb 2026 22:44:26 +0000

DataEngineeringZoomcamp #dbt #AnalyticsEngineering #DataModeling

Part 1: Introduction to Analytics Engineering & dbt Fundamentals 🎯

What is Analytics Engineering?

The Evolution of Data Roles

Traditionally, there were two main roles in data:

Role	Focus	Skills
Data Engineer	Building pipelines, infrastructure, data movement	Python, Spark, Airflow, cloud services
Data Analyst	Creating reports, dashboards, insights	SQL, Excel, BI tools

But there was a gap! Who transforms the raw data into clean, analysis-ready tables? Enter the Analytics Engineer.

What Does an Analytics Engineer Do?

An Analytics Engineer sits between Data Engineering and Data Analytics:

┌─────────────────┐     ┌──────────────────────┐     ┌─────────────────┐
│  Data Engineer  │ ──► │  Analytics Engineer  │ ──► │   Data Analyst  │
│                 │     │                      │     │                 │
│  • Pipelines    │     │  • Transform data    │     │  • Dashboards   │
│  • Infrastructure│    │  • Data modeling     │     │  • Reports      │
│  • Data movement│     │  • Quality tests     │     │  • Insights     │
└─────────────────┘     │  • Documentation     │     └─────────────────┘
                        └──────────────────────┘

Key responsibilities:

📊 Transform raw data into clean, modeled datasets
🧪 Write tests to ensure data quality
📝 Document everything so others can understand
🔗 Build the "T" in ELT (Extract, Load, Transform)

The Kitchen Analogy 🍳

Think of a data warehouse like a restaurant:

Restaurant	Data Warehouse	Who accesses it
Pantry (raw ingredients)	Staging area (raw data)	Data Engineers
Kitchen (cooking happens)	Processing area (transformations)	Analytics Engineers
Dining Hall (served dishes)	Presentation area (final tables)	Business users, Analysts

Raw ingredients (data) come in, get processed (transformed), and are served as polished dishes (analytics-ready tables).

What is dbt? 🛠️

dbt stands for data build tool. It's the most popular tool for analytics engineering.

The Problems dbt Solves

Before dbt, data transformation was messy:

❌ SQL scripts scattered everywhere with no organization
❌ No version control (changes got lost)
❌ No testing (errors discovered too late)
❌ No documentation (nobody knew what anything meant)
❌ No environments (changes went straight to production!)

dbt brings software engineering best practices to analytics:

✅ Version control - Your SQL lives in Git
✅ Modularity - Reusable pieces instead of copy-paste
✅ Testing - Automated data quality checks
✅ Documentation - Generated from your code
✅ Environments - Separate dev and prod

How dbt Works

dbt follows a simple principle: write SQL, dbt handles the rest.

┌─────────────────────────────────────────────────────────────┐
│                     Your dbt Project                        │
│                                                             │
│   ┌───────────────┐    ┌───────────────┐    ┌────────────┐ │
│   │  models/*.sql │───►│   dbt compile │───►│ SQL Queries│ │
│   │  (your logic) │    │   dbt run     │    │ (executed) │ │
│   └───────────────┘    └───────────────┘    └────────────┘ │
│                              │                              │
│                              ▼                              │
│                    ┌──────────────────┐                     │
│                    │  Data Warehouse  │                     │
│                    │  (views/tables)  │                     │
│                    └──────────────────┘                     │
└─────────────────────────────────────────────────────────────┘

You write SQL files (called "models")
dbt compiles them (adds warehouse-specific syntax)
dbt runs them against your data warehouse
Views/tables are created automatically!

dbt Core vs dbt Cloud

Feature	dbt Core	dbt Cloud
Cost	Free (open source)	Free tier + paid plans
Where it runs	Your machine/server	Cloud-hosted
Setup	Manual installation	Browser-based IDE
Scheduling	Need external tool	Built-in scheduler
Best for	Local development, cost savings	Teams, ease of use

💡 For this course: You can use either! Local setup uses DuckDB + dbt Core (free). Cloud setup uses BigQuery + dbt Cloud.

Part 3: Partitioning & Clustering for Performance 🚀

Abdelrahman Adnan — Mon, 09 Feb 2026 23:09:31 +0000

This is where BigQuery optimization gets really powerful! These two techniques can reduce your query costs by 90% or more.

The Problem We're Solving 🤔

Imagine you have a table with 5 years of taxi trip data - about 500 million rows. Every time you query:

SELECT * FROM taxi_trips WHERE pickup_date = '2024-01-15';

Without optimization, BigQuery scans ALL 500 million rows just to find trips from one day. That's:

Slow (lots of data to read)
Expensive (you pay for all data scanned)
Wasteful (you only needed 0.05% of the data!)

Partitioning and clustering solve this problem!

Partitioning: Dividing Your Table into Sections 📁

Think of partitioning like organizing a filing cabinet. Instead of one giant drawer with all documents, you have:

Drawer for January
Drawer for February
Drawer for March
...and so on

When you need something from March, you ONLY open the March drawer!

How Partitioning Works in BigQuery

-- Create a table partitioned by date
CREATE OR REPLACE TABLE `project.dataset.taxi_partitioned`
PARTITION BY DATE(pickup_datetime) AS
SELECT * FROM `project.dataset.taxi_external`;

Now your table looks like this internally:

taxi_partitioned/
├── 2024-01-01/    (all trips from Jan 1)
├── 2024-01-02/    (all trips from Jan 2)
├── 2024-01-03/    (all trips from Jan 3)
│   ... 
├── 2024-06-30/    (all trips from Jun 30)
└── [metadata]

When you query with a date filter:

SELECT * FROM taxi_partitioned 
WHERE DATE(pickup_datetime) = '2024-03-15';

BigQuery ONLY reads the 2024-03-15 partition! The other 180+ partitions are never touched.

Types of Partitioning

1. Time-based partitioning (most common)

-- Partition by day (default)
PARTITION BY DATE(pickup_datetime)

-- Partition by month (for less granular data)
PARTITION BY DATE_TRUNC(pickup_datetime, MONTH)

-- Partition by year
PARTITION BY DATE_TRUNC(pickup_datetime, YEAR)

2. Integer range partitioning

-- Partition by customer ID ranges
PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 1000000, 10000))

3. Ingestion time partitioning

-- Partition by when data was loaded
PARTITION BY _PARTITIONDATE

Partitioning Rules to Remember ⚠️

Rule	Details
Max partitions	4,000 per table
Min partition size	Aim for at least 1GB per partition
One column only	Can only partition on ONE column
Column types	DATE, TIMESTAMP, DATETIME, or INTEGER

💡 When NOT to use partitioning:

If you'd have < 1GB per partition (use clustering instead)
If you'd exceed 4,000 partitions
If you rarely filter on the partition column

Clustering: Organizing Data Within Partitions 🗂️

If partitioning is like having separate drawers in a filing cabinet, clustering is like organizing the folders WITHIN each drawer alphabetically.

How Clustering Works

-- Create table with partitioning AND clustering
CREATE OR REPLACE TABLE `project.dataset.taxi_optimized`
PARTITION BY DATE(pickup_datetime)
CLUSTER BY vendor_id, payment_type AS
SELECT * FROM `project.dataset.taxi_external`;

Now within each date partition, data is sorted by vendor_id, then by payment_type:

taxi_optimized/
├── 2024-01-15/
│   ├── vendor_id=1, payment_type=1, ...
│   ├── vendor_id=1, payment_type=2, ...
│   ├── vendor_id=2, payment_type=1, ...
│   └── vendor_id=2, payment_type=2, ...
├── 2024-01-16/
│   └── (similarly organized)

When you query:

SELECT * FROM taxi_optimized 
WHERE DATE(pickup_datetime) = '2024-01-15'
  AND vendor_id = 1;

BigQuery:

Goes directly to the 2024-01-15 partition (thanks to partitioning)
Reads only the vendor_id=1 blocks (thanks to clustering)

Even more data skipped = even faster and cheaper!

Clustering Rules 📏

Rule	Details
Max columns	Up to 4 clustering columns
Order matters	Put most filtered column first
No cost for re-clustering	BigQuery automatically re-clusters as data is added
Works with partitioning	Best used together!
Minimum table size	Most effective for tables > 1GB

Good clustering column candidates:

Columns you frequently filter on (WHERE clause)
Columns you frequently group by (GROUP BY clause)
High-cardinality columns (many distinct values)

Partitioning vs Clustering: When to Use What? 🤷

Scenario	Recommendation
Always filter by date	Partition by date
Filter by date AND other columns	Partition by date, cluster by other columns
Filter by multiple non-date columns	Cluster by those columns
Need to know query cost upfront	Must use partitioning (clustering doesn't show estimates)
Less than 1GB per potential partition	Use clustering instead
Would have > 4,000 partitions	Use clustering instead
Data is rarely filtered	Maybe neither - analyze your query patterns first

Real-World Performance Comparison 📊

I ran tests on the NYC taxi dataset (about 20 million rows). Here are the results:

Test 1: Filtering by Date Range

SELECT DISTINCT vendor_id 
FROM [table] 
WHERE DATE(pickup_datetime) BETWEEN '2024-03-01' AND '2024-03-15';

Table Type	Data Scanned	Cost
Non-partitioned	310 MB	$0.00155
Partitioned by date	27 MB	$0.000135
Savings	91% less!	91% cheaper!

Test 2: Filtering by Date AND Vendor

SELECT COUNT(*) 
FROM [table] 
WHERE DATE(pickup_datetime) BETWEEN '2024-06-01' AND '2024-06-30'
  AND vendor_id = 1;

Table Type	Data Scanned
Partitioned only	1.1 GB
Partitioned + Clustered	865 MB
Additional savings	21% less!

Combined savings: Over 90% reduction in costs! 💰

Step-by-Step: Creating an Optimized Table

Here's the full workflow:

-- Step 1: Create external table pointing to your data in GCS
CREATE OR REPLACE EXTERNAL TABLE `my-project.dataset.taxi_external`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://my-bucket/yellow_taxi_2024/*.parquet']
);

-- Step 2: Check how many records we have
SELECT COUNT(*) FROM `my-project.dataset.taxi_external`;
-- Result: 20,332,093 records

-- Step 3: Create optimized table with partitioning and clustering
CREATE OR REPLACE TABLE `my-project.dataset.taxi_optimized`
PARTITION BY DATE(tpep_dropoff_datetime)
CLUSTER BY VendorID AS
SELECT * FROM `my-project.dataset.taxi_external`;

-- Step 4: Verify partitions were created
SELECT 
  table_name, 
  partition_id, 
  total_rows
FROM `dataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'taxi_optimized'
ORDER BY partition_id;

Best Practices Cheat Sheet ✅

For Reducing Costs 💵

❌ Never use SELECT *
✅ Only query columns you need
✅ Use partitioned tables
✅ Add clustering for frequently filtered columns
✅ Check estimated bytes before running
✅ Use table previews instead of SELECT for quick looks

For Better Performance ⚡

✅ Filter early - apply WHERE before JOINs
✅ Put largest table first in JOINs
✅ Use ORDER BY at the end of query
✅ Consider approximate functions (APPROX_COUNT_DISTINCT) when exact precision isn't needed
✅ Avoid JavaScript UDFs when possible
✅ Don't over-partition (keep partitions > 1GB)

Quick Reference: Common SQL Patterns for Beginners 📝

Here are the most useful BigQuery SQL commands you'll need:

Basic Queries

-- Count all records in a table
SELECT COUNT(*) FROM `project.dataset.table`;

-- Count distinct values in a column
SELECT COUNT(DISTINCT vendor_id) FROM `project.dataset.taxi`;

-- Get first 10 rows (but remember - this still scans the whole table!)
SELECT * FROM `project.dataset.taxi` LIMIT 10;

-- Better way to preview - use table preview in BigQuery console instead!

Filtering Data

-- Filter by exact value
SELECT * FROM `project.dataset.taxi`
WHERE vendor_id = 1;

-- Filter by date range
SELECT * FROM `project.dataset.taxi`
WHERE DATE(pickup_datetime) BETWEEN '2024-01-01' AND '2024-01-31';

-- Filter with multiple conditions
SELECT * FROM `project.dataset.taxi`
WHERE vendor_id = 1 
  AND fare_amount > 10
  AND DATE(pickup_datetime) = '2024-03-15';

Aggregations

-- Sum, average, min, max
SELECT 
  SUM(fare_amount) as total_fares,
  AVG(fare_amount) as avg_fare,
  MIN(fare_amount) as min_fare,
  MAX(fare_amount) as max_fare,
  COUNT(*) as trip_count
FROM `project.dataset.taxi`;

-- Group by
SELECT 
  vendor_id,
  COUNT(*) as trips,
  AVG(fare_amount) as avg_fare
FROM `project.dataset.taxi`
GROUP BY vendor_id;

Creating Tables

-- Create external table from GCS
CREATE OR REPLACE EXTERNAL TABLE `project.dataset.taxi_external`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://bucket-name/folder/*.parquet']
);

-- Create native table from external
CREATE OR REPLACE TABLE `project.dataset.taxi_native` AS
SELECT * FROM `project.dataset.taxi_external`;

-- Create partitioned + clustered table
CREATE OR REPLACE TABLE `project.dataset.taxi_optimized`
PARTITION BY DATE(pickup_datetime)
CLUSTER BY vendor_id
AS SELECT * FROM `project.dataset.taxi_external`;

Checking Table Info

-- View partition information
SELECT 
  table_name, 
  partition_id, 
  total_rows
FROM `dataset.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'your_table_name'
ORDER BY partition_id;

-- Check table schema
SELECT column_name, data_type 
FROM `dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'your_table_name';

Glossary for Beginners 📚

Term	Simple Explanation
Data Warehouse	A big database designed for analyzing historical data, not for running apps
OLTP	Databases for running applications (fast, small transactions)
OLAP	Databases for analysis (complex queries, lots of data)
BigQuery	Google's cloud data warehouse service
GCS	Google Cloud Storage - where you store files in the cloud
External Table	A table that reads data from GCS without copying it
Native Table	A table with data stored in BigQuery itself
Partitioning	Splitting a table into smaller pieces by date or number
Clustering	Sorting data within partitions by specific columns
Columnar Storage	Storing data by column instead of row (faster for analytics)
Slot	A unit of compute power in BigQuery
Data Scanned	How much data BigQuery reads to answer your query (you pay for this!)

Common Mistakes to Avoid ⚠️

Using SELECT * everywhere - Always specify columns you need!
Thinking LIMIT reduces cost - It doesn't! BigQuery scans first, limits after.
Not using partitions - Always partition time-series data by date.
Wrong partition column - Partition by columns you ALWAYS filter on.
Too many partitions - Keep it under 4,000, aim for >1GB per partition.
Ignoring the query validator - Always check estimated bytes before running!
Not using clustering with partitioning - They work best together!

Resources for Learning More 📖

📊 BigQuery Official Documentation
🎥 DE Zoomcamp Video: Data Warehouse and BigQuery
🎥 DE Zoomcamp Video: Partitioning vs Clustering
🎥 DE Zoomcamp Video: Best Practices
🎥 DE Zoomcamp Video: Internals of BigQuery
📝 Course SQL Examples
📑 Course Slides

Summary: Key Takeaways 🎯

Data warehouses are for analysis, not running apps - that's why they exist!
BigQuery is serverless - no servers to manage, just write SQL.
Columnar storage = only reads columns you request = faster + cheaper.
External tables = data in GCS, slower but flexible.
Native tables = data in BigQuery, faster but costs more storage.
Partitioning = split table by date, only scan relevant dates.
Clustering = sort data within partitions, skip irrelevant blocks.
Always check estimated bytes before running queries!
Never use SELECT * - specify only the columns you need.
Combine partitioning + clustering for maximum optimization!

DataEngineeringZoomcamp #BigQuery #DataWarehouse #GCP #SQL #CloudComputing

Part 2: BigQuery Deep Dive 🔍

Abdelrahman Adnan — Mon, 09 Feb 2026 23:08:43 +0000

What is BigQuery?

BigQuery is Google's data warehouse in the cloud. It's one of the most popular choices for storing and analyzing large amounts of data because it's:

Serverless - You don't manage any servers. No installing software, no worrying about disk space, no maintenance. Google handles everything.
Fully managed - Google takes care of security, backups, scaling, and updates.
Petabyte-scale - Can handle absolutely massive datasets (1 petabyte = 1,000 terabytes = 1,000,000 gigabytes!)
SQL-based - You just write SQL queries. No need to learn a new programming language!

Why BigQuery is Great for Beginners 🌟

☁️ No setup headaches - Create a project, load data, start querying. That's it!
💰 Free tier - 1TB of queries and 10GB storage free per month
📊 Familiar SQL - If you know basic SQL, you can use BigQuery
🔗 Works with everything - Google Sheets, Data Studio, Python, R, etc.
🤖 Built-in ML - Train machine learning models using just SQL!

How BigQuery Works Under the Hood 🔧

Understanding the architecture helps you write better queries and save money. Don't worry, I'll keep it simple!

The Secret: Separation of Storage and Compute

Traditional databases store data and process queries on the same machine. BigQuery does something clever - it separates them:

┌─────────────────────────────────────────────────────────┐
│                    YOUR SQL QUERY                        │
└─────────────────────────┬───────────────────────────────┘
                          │
                          ▼
┌─────────────────────────────────────────────────────────┐
│                DREMEL (Compute Engine)                   │
│                                                         │
│   Your query gets broken into tiny pieces and           │
│   thousands of workers process them in parallel         │
└─────────────────────────┬───────────────────────────────┘
                          │
                          │  Jupiter Network (super fast!)
                          │  1 Terabyte per second
                          │
                          ▼
┌─────────────────────────────────────────────────────────┐
│                 COLOSSUS (Storage)                       │
│                                                         │
│   Your data lives here in COLUMNAR format               │
│   (organized by columns, not rows)                      │
└─────────────────────────────────────────────────────────┘

What Does "Columnar Storage" Mean? 📋

This is SUPER important for understanding BigQuery performance!

Traditional databases (row-oriented):
Stores data like this:

Row 1: [John, 25, New York, $50000]
Row 2: [Jane, 30, Chicago, $60000]
Row 3: [Bob, 35, Miami, $55000]

To find all salaries, it reads EVERY row, even though you only need one column.

BigQuery (column-oriented):
Stores data like this:

Names column:    [John, Jane, Bob]
Ages column:     [25, 30, 35]
Cities column:   [New York, Chicago, Miami]
Salaries column: [$50000, $60000, $55000]

To find all salaries, it ONLY reads the salary column! Much faster and cheaper!

💡 This is why SELECT * is expensive in BigQuery - it has to read EVERY column. Always specify only the columns you need!

The Dremel Execution Engine 🚀

When you run a query, here's what happens:

Root Server receives your query
Query is broken into smaller pieces
Mixers distribute work to thousands of Leaf Nodes
Each Leaf Node processes a small chunk of data in parallel
Results flow back up through Mixers to Root
You get your final result!

                    ┌──────────┐
                    │   ROOT   │  ← Your query comes here
                    └────┬─────┘
                         │
           ┌─────────────┼─────────────┐
           ▼             ▼             ▼
      ┌────────┐    ┌────────┐    ┌────────┐
      │ MIXER  │    │ MIXER  │    │ MIXER  │
      └───┬────┘    └───┬────┘    └───┬────┘
          │             │             │
    ┌─────┼─────┐ ┌─────┼─────┐ ┌─────┼─────┐
    ▼     ▼     ▼ ▼     ▼     ▼ ▼     ▼     ▼
   [L]   [L]   [L][L]   [L]   [L][L]   [L]   [L]

   L = Leaf nodes (thousands of them!)

Why this matters: A query that would take hours on your laptop can run in seconds because thousands of machines work on it simultaneously!

External Tables vs Native Tables 📦

You have two ways to work with data in BigQuery:

Option 1: External Tables (Data stays in GCS)

Your data remains in Google Cloud Storage, BigQuery just reads it when you query.

-- Create external table pointing to files in GCS bucket
CREATE OR REPLACE EXTERNAL TABLE `my-project.my_dataset.taxi_external`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://my-bucket/taxi_data/*.parquet']
);

When to use External Tables:

✅ You want to save on storage costs (GCS is cheaper than BigQuery storage)
✅ One-time or occasional analysis
✅ Data is updated frequently in source system
✅ Quick exploration before committing to load

Downsides:

❌ Slower queries (data needs to be read from GCS each time)
❌ No cost estimation before running queries
❌ Can't partition or cluster (limited optimization)

Option 2: Native Tables (Data loaded into BigQuery)

Data is copied into BigQuery's own storage (Colossus).

-- Create native table from external table
CREATE OR REPLACE TABLE `my-project.my_dataset.taxi_native` AS
SELECT * FROM `my-project.my_dataset.taxi_external`;

When to use Native Tables:

✅ Frequently queried data
✅ Need best query performance
✅ Want to use partitioning and clustering
✅ Need accurate cost estimates before running queries

Downsides:

❌ Higher storage costs
❌ Data duplication (exists in both GCS and BigQuery)

💡 Pro tip: Start with external tables for exploration, then load into native tables once you know what data you actually need!

Understanding BigQuery Costs 💰

BigQuery has two main pricing models:

On-Demand Pricing (Pay per query)

$5 per TB of data scanned
Good for: Occasional users, unpredictable workloads
You pay for how much data your queries read

Flat-Rate Pricing (Monthly commitment)

~$2,000/month for 100 "slots" (compute units)
Good for: Heavy users, predictable workloads
Unlimited queries within your slot capacity

How to Estimate Query Cost 🧮

Before running a query, BigQuery shows you how much data it will scan:

┌────────────────────────────────────────────────┐
│  Query Editor                                  │
│  ─────────────────────────────────────────────│
│  SELECT * FROM my_table WHERE date = '2024-01'│
│                                                │
│  [This query will process 2.5 GB when run]    │ ← Check this!
└────────────────────────────────────────────────┘

Cost calculation:

2.5 GB = 0.0025 TB
0.0025 TB × $5 = $0.0125 (about 1 cent)

But if you run that query 100 times a day... costs add up!

Cost Optimization Tips 💡

NEVER use SELECT * unless you absolutely need every column

   -- ❌ Bad - reads ALL columns
   SELECT * FROM taxi_data;

   -- ✅ Good - reads only what you need
   SELECT pickup_time, dropoff_time, fare_amount FROM taxi_data;

Use partitioned tables (covered in Part 3)
Preview before running - Always check the estimated bytes
Use LIMIT wisely - It doesn't reduce data scanned! The filtering happens AFTER reading.

   -- ❌ Still scans the whole table!
   SELECT * FROM huge_table LIMIT 10;

   -- ✅ Better - add a WHERE clause first
   SELECT * FROM huge_table WHERE date = CURRENT_DATE() LIMIT 10;

Cache results - BigQuery caches query results for 24 hours (free!)

BigQuery Caching 🗄️

When you run the same query twice:

First run: Scans data, costs money
Second run: Returns cached result, FREE!

Cache is invalidated when:

Underlying table data changes
24 hours pass
You disable caching in query settings

DataEngineeringZoomcamp #BigQuery #DataWarehouse #GCP #SQL #CloudComputing

Module 3 Summary - Data Warehousing & BigQuery

Abdelrahman Adnan — Mon, 09 Feb 2026 23:07:36 +0000

DataEngineeringZoomcamp #BigQuery #DataWarehouse #GCP

Part 1: Understanding Data Warehouses & OLAP vs OLTP 🏢

Why Do We Need Data Warehouses? 🤔

Imagine you run an online store. Your website has a database that handles:

Customer sign-ups
Product orders
Payment processing
Inventory updates

This database needs to be FAST because customers are waiting. Every millisecond counts!

Now, your boss asks: "What were our top-selling products last year by region, and how did that compare to the year before?"

Running that query on your production database would:

Slow down your website (bad for customers!)
Take forever because the database isn't designed for such complex analysis
Potentially crash things if the query is too heavy

This is exactly why data warehouses exist! They're a separate place to store your data, specifically designed for answering complex analytical questions without affecting your live applications.

OLTP vs OLAP - The Two Worlds of Databases

These acronyms sound scary, but they're simple concepts:

OLTP = Online Transaction Processing (Your everyday app databases)
OLAP = Online Analytical Processing (Data warehouses for analysis)

Aspect	OLTP (Transactional)	OLAP (Analytical)
What it's for	Running your app - orders, logins, updates	Answering business questions - reports, dashboards
Type of queries	Simple: "Get user #123's info"	Complex: "Show sales trends by region for 5 years"
Speed	Super fast for small operations	Can take minutes for huge analyses
Data freshness	Real-time, always up-to-date	Usually updated daily/hourly (batch)
How data is organized	Normalized (split into many tables, no duplicates)	Denormalized (fewer tables, some duplication OK)
Data size	Gigabytes (current data)	Terabytes/Petabytes (years of history)
Who uses it	Your application, customers	Data analysts, managers, executives
Examples	MySQL for your website, PostgreSQL for your app	BigQuery, Snowflake, Amazon Redshift

Real-World Example 🛒

OLTP scenario (your app database):

-- A customer places an order - needs to be FAST
INSERT INTO orders (customer_id, product_id, quantity, price) 
VALUES (123, 456, 2, 29.99);

OLAP scenario (data warehouse):

-- Your CEO wants to know Q4 performance - can take a minute, that's fine
SELECT 
    region,
    product_category,
    SUM(revenue) as total_revenue,
    COUNT(DISTINCT customer_id) as unique_customers
FROM sales_data
WHERE order_date BETWEEN '2023-10-01' AND '2023-12-31'
GROUP BY region, product_category
ORDER BY total_revenue DESC;

💡 Key insight: OLTP is like a cashier at a store - fast, handles one customer at a time. OLAP is like the accounting department - takes time to analyze all the receipts and produce reports.

What Exactly is a Data Warehouse? 🏗️

A data warehouse is a centralized repository where you collect data from ALL your different systems and store it in a way that's optimized for analysis.

Think of it like this:

Imagine a company with multiple departments:

Sales team uses Salesforce
Marketing uses HubSpot
Website runs on PostgreSQL
Inventory managed in SAP

Each system has its own database. But your CEO wants a report combining data from ALL of them. This is where a data warehouse comes in!

┌─────────────┐   ┌─────────────┐   ┌─────────────┐
│  Salesforce │   │   HubSpot   │   │  PostgreSQL │
│   (Sales)   │   │ (Marketing) │   │  (Website)  │
└──────┬──────┘   └──────┬──────┘   └──────┬──────┘
       │                 │                 │
       │    ETL/ELT      │                 │
       │   (Extract,     │                 │
       │   Transform,    │                 │
       │    Load)        │                 │
       ▼                 ▼                 ▼
┌──────────────────────────────────────────────────┐
│              DATA WAREHOUSE (BigQuery)            │
│                                                  │
│   All your data, cleaned, organized, ready       │
│   for analysis!                                  │
└──────────────────────────────────────────────────┘
                        │
                        ▼
         ┌──────────────────────────┐
         │  Reports, Dashboards,    │
         │  Machine Learning, etc.  │
         └──────────────────────────┘

Key characteristics of a data warehouse:

📊 Subject-oriented - Organized by business topics (sales, customers, products)
🔗 Integrated - Data from multiple sources combined together
📅 Time-variant - Keeps historical data (years worth!)
🔒 Non-volatile - Data doesn't change once loaded (it's a historical record)

Modern Cloud Data Warehouses ☁️

Traditional data warehouses (like Oracle, Teradata) required:

Buying expensive hardware
Hiring DBAs to manage servers
Months of setup time
Huge upfront costs

Modern cloud data warehouses (BigQuery, Snowflake, Redshift) changed everything:

✅ No servers to manage (serverless)
✅ Pay only for what you use
✅ Scales automatically
✅ Set up in minutes
✅ Access from anywhere

Module 2 Summary - Workflow Orchestration with Kestra Part 3

Abdelrahman Adnan — Tue, 03 Feb 2026 01:02:10 +0000

Part 3: AI Integration & Best Practices

Using AI for Data Engineering

AI tools help data engineers by:

Generating workflows faster - Describe tasks in natural language
Avoiding errors - Get syntax-correct code following best practices

Key Insight: AI is only as good as the context you provide.

Context Engineering with LLMs

Problem: Generic AI assistants (like ChatGPT without context) may produce:

Outdated plugin syntax
Incorrect property names
Hallucinated features that don't exist

Why? LLMs are trained on data up to a knowledge cutoff date and don't know about software updates.

Solution: Provide proper context to AI!

Kestra AI Copilot

Kestra's built-in AI Copilot is designed specifically for generating Kestra flows with:

Full context about latest plugins
Correct workflow syntax
Current best practices

Setup Requirements:

Get Gemini API key from Google AI Studio
Configure in docker-compose.yml with GEMINI_API_KEY
Access via sparkle icon (✨) in Kestra UI

Retrieval Augmented Generation (RAG)

RAG is a technique that:

Retrieves relevant information from data sources
Augments the AI prompt with this context
Generates responses grounded in real data

RAG Process in Kestra:

Ingest documents (documentation, release notes)
Create embeddings (vector representations)
Store embeddings in KV Store or vector database
Query with context at runtime
Generate accurate, context-aware responses

RAG Best Practices:

Keep documents updated regularly
Chunk large documents appropriately
Test retrieval quality

Deployment & Production

For production deployment:

Deploy Kestra on Google Cloud
Sync workflows from Git repository
Use Secrets and KV Store for sensitive data
Never commit API keys to Git

Troubleshooting Tips

Issue	Solution
Port conflict with pgAdmin	Change Kestra port to 18080
CSV column mismatch in BigQuery	Rerun entire execution including re-download
Container issues	Stop, remove, and restart containers

Recommended Docker Images:

kestra/kestra:v1.1 (stable version)
postgres:18

Additional Resources

Kestra Documentation
Blueprints Library - Pre-built workflow examples
600+ Plugins
Kestra Slack Community

Key Takeaways

Workflow orchestration is essential for managing complex data pipelines
Kestra provides a flexible, scalable solution with YAML-based flows
ETL is ideal for local processing; ELT leverages cloud computing power
Scheduling and backfills enable automated and historical data processing
AI Copilot accelerates workflow development with proper context
RAG eliminates AI hallucinations by grounding responses in real data #dezoomcamp

Module 2 Summary - Workflow Orchestration with Kestra Part 2

Abdelrahman Adnan — Tue, 03 Feb 2026 01:01:18 +0000

Part 2: Building ETL & ELT Data Pipelines

ETL Pipeline (Local Postgres)

ETL = Extract → Transform → Load

The local pipeline workflow:

Extract CSV data from GitHub (partitioned by year and month)
Transform data using Python
Load data into PostgreSQL database

Key steps in the flow:

Create tables
Load data to monthly staging table
Merge data to final destination table

Dataset Source: NYC Taxi and Limousine Commission (TLC) Trip Record Data available in CSV format from the DataTalksClub GitHub repository.

Scheduling and Backfills

Kestra provides powerful scheduling capabilities:

Schedule Trigger - Run pipelines at specific times (e.g., daily at 9 AM UTC)
Backfill - Process historical data by running workflows for past dates

Example: Backfill green taxi data for year 2019.

ELT Pipeline (Google Cloud Platform)

ELT = Extract → Load → Transform

When working with large datasets in the cloud, ELT is often preferred:

Step	Description
Extract	Get dataset from source (GitHub)
Load	Upload to data lake (Google Cloud Storage)
Transform	Create tables in data warehouse (BigQuery) using data from GCS

Advantage: Leverage cloud's performance for transforming large datasets much faster than local machines.

GCP Setup for Kestra

Required KV Store values:

GCP_PROJECT_ID - Your Google Cloud project
GCP_LOCATION - Region for resources
GCP_BUCKET_NAME - GCS bucket name
GCP_DATASET - BigQuery dataset name
GCP_CREDS - Service account credentials (keep secure!)

GCP Pipeline Flow

Extract CSV from GitHub
Upload to Google Cloud Storage (data lake)
Create external table in BigQuery from GCS
Create partitioned table in BigQuery
Schedule with timezone support (e.g., America/New_York)

dezoomccamp

Module 2 Summary - Workflow Orchestration with Kestra Part 1

Abdelrahman Adnan — Tue, 03 Feb 2026 01:00:26 +0000

Part 1: Introduction to Workflow Orchestration & Kestra Fundamentals

What is Workflow Orchestration?

Think of a music orchestra with various instruments that need to work together. The conductor helps them play in harmony. Similarly, a workflow orchestrator coordinates multiple tools and platforms to work together.

A workflow orchestrator typically:

Runs workflows containing predefined steps
Monitors and logs errors with additional handling when they occur
Automatically triggers workflows based on schedules or events

In data engineering, we often need to move data from one place to another with modifications. The orchestrator manages these steps while providing visibility into the process.

What is Kestra?

Kestra is an open-source, event-driven, infinitely-scalable orchestration platform. Key features include:

Feature	Description
Flow Code (YAML)	Build workflows with code, no-code, or AI Copilot
1000+ Plugins	Integrate with virtually any tool
Multi-language Support	Use Python, SQL, or any programming language
Flexible Triggers	Schedule-based or event-based execution

Core Kestra Concepts

Flow - A container for tasks and orchestration logic (defined in YAML)
Tasks - Individual steps within a flow
Inputs - Dynamic values passed at runtime
Outputs - Data passed between tasks and flows
Triggers - Mechanisms that automatically start flow execution
Execution - A single run of a flow with a specific state
Variables - Key-value pairs for reusable values across tasks
Plugin Defaults - Default values applied to tasks of a given type
Concurrency - Control how many executions can run simultaneously

Installing Kestra

Kestra runs via Docker Compose with two main services:

Kestra server container
PostgreSQL database container

cd 02-workflow-orchestration
docker compose up -d

Access the UI at: http://localhost:8080

Running Python Code in Kestra

Kestra can execute Python code either:

From a dedicated file
Written directly inside the workflow YAML

This allows you to pick the right tools for your pipelines without limitations.

dezoomcamp

Data Engineering ZoomCamp Module 1 Notes Part 1

Abdelrahman Adnan — Tue, 27 Jan 2026 01:22:08 +0000

Module 1: Docker, SQL & Terraform

This is my notes and walkthrough for Module 1 of the Data Engineering Zoomcamp. If you're new to data engineering, this should help you understand the basics.

What is Data Engineering?

Data Engineering is basically about building systems that collect, store, and analyze data at scale. Think of it as the plumbing that makes data flow from point A to point B so analysts and data scientists can do their thing.

A data pipeline is just a service that takes data in, does something with it, and outputs more data. Simple example: read a CSV file, clean it up, store it in a database.

Part 1: Docker Basics

Why Docker?

Docker lets you package your application and all its dependencies into a "container". This solves the classic "it works on my machine" problem.

Main benefits:

Reproducibility - same environment everywhere
Isolation - apps run independently, won't mess with your system
Portability - works on any machine with Docker installed

Containers are different from virtual machines - they're much lighter because they share the host OS kernel.

Getting Started with Docker

First, check if Docker is installed:

docker --version

Run your first container:

docker run hello-world

Try running Ubuntu:

docker run -it ubuntu

The -it flag means interactive mode with a terminal. Without it, the container just starts and exits.

Important: Containers are Stateless

This tripped me up at first. Any changes you make inside a container are lost when the container stops. For example:

docker run -it ubuntu
apt update && apt install python3
exit
# Run it again
docker run -it ubuntu
python3  # Error! Python is not installed

This is actually a feature, not a bug. It means you can always start fresh.

Managing Containers

See all containers (including stopped ones):

docker ps -a

Clean up old containers:

docker rm $(docker ps -aq)

Better approach - use --rm to auto-delete when container stops:

docker run -it --rm ubuntu

Using Different Base Images

You can use pre-built images with software already installed:

# Python image - starts Python interpreter
docker run -it --rm python:3.13

# If you want bash instead of Python:
docker run -it --rm --entrypoint=bash python:3.13-slim

Volumes - Persisting Data

Since containers are stateless, we need volumes to save data. There are two types:

Named volumes (Docker manages them):

docker run -it -v my_data:/app/data ubuntu

Bind mounts (map to a folder on your computer):

docker run -it -v $(pwd)/my_folder:/app/data ubuntu

Part 2: Creating a Dockerfile

A Dockerfile is a recipe for building your own Docker image.

Simple Example

Create a file called pipeline.py:

import sys
import pandas as pd

print(sys.argv)
day = sys.argv[1]
print(f'Job finished for day = {day}')

Create a Dockerfile:

FROM python:3.13-slim

RUN pip install pandas pyarrow

WORKDIR /app
COPY pipeline.py pipeline.py

ENTRYPOINT ["python", "pipeline.py"]

What each line does:

FROM - base image to build on
RUN - execute commands during build
WORKDIR - set the working directory
COPY - copy files from your machine to the image
ENTRYPOINT - the command that runs when container starts

Build and run:

docker build -t test:pandas .
docker run -it test:pandas some_argument

Part 3: Running PostgreSQL with Docker

Now let's do some real data engineering. We'll run Postgres in a container.

docker run -it --rm \
  -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:17

Breaking this down:

-e sets environment variables (username, password, database name)
-v creates a named volume so data persists
-p 5432:5432 maps the container port to your machine

Connecting to Postgres

Install pgcli (a nice command-line client):

pip install pgcli
# or with uv:
uv add --dev pgcli

Connect:

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

Try some SQL:

\dt                              -- list tables
CREATE TABLE test (id INTEGER);
INSERT INTO test VALUES (1);
SELECT * FROM test;
\q                               -- quit

Data Engineering ZoomCamp Module 1 Notes Part 2

Abdelrahman Adnan — Tue, 27 Jan 2026 01:20:18 +0000

Part 4: Data Ingestion with Python

We're going to load the NYC Taxi dataset into Postgres.

Setting Up

Install dependencies:

pip install pandas sqlalchemy psycopg2-binary jupyter

Or with uv:

uv add pandas sqlalchemy psycopg2-binary
uv add --dev jupyter

The Dataset

We use the NYC Taxi trip data. Download it:

wget https://github.com/DataTalksClub/nyc-tlc-data/releases/download/yellow/yellow_tripdata_2021-01.csv.gz

Loading Data into Postgres

Here's the basic approach:

import pandas as pd
from sqlalchemy import create_engine

# Create connection
engine = create_engine('postgresql://root:root@localhost:5432/ny_taxi')

# Read CSV in chunks (it's a big file)
df_iter = pd.read_csv('yellow_tripdata_2021-01.csv.gz', 
                       iterator=True, 
                       chunksize=100000)

# Create table from first chunk
first_chunk = next(df_iter)
first_chunk.head(0).to_sql(name='yellow_taxi_data', con=engine, if_exists='replace')

# Insert first chunk
first_chunk.to_sql(name='yellow_taxi_data', con=engine, if_exists='append')

# Insert remaining chunks
for chunk in df_iter:
    chunk.to_sql(name='yellow_taxi_data', con=engine, if_exists='append')
    print(f'Inserted {len(chunk)} rows')

The key things here:

chunksize prevents loading the whole file into memory
if_exists='replace' creates the table (first time)
if_exists='append' adds rows (subsequent chunks)

Part 5: Docker Compose

Running multiple docker run commands is annoying. Docker Compose lets you define everything in one file.

Create docker-compose.yaml:

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

  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:
  ny_taxi_postgres_data:
  pgadmin_data:

Now just run:

docker-compose up      # start everything
docker-compose up -d   # start in background
docker-compose down    # stop everything
docker-compose down -v # stop and remove volumes

Docker Compose automatically creates a network so containers can talk to each other using their service names (e.g., pgdatabase instead of localhost).

Connecting to Postgres from pgAdmin

Open http://localhost:8080 in browser
Login with the email/password from docker-compose
Right-click Servers > Create > Server
Name it whatever you want
Under Connection tab:
- Host: pgdatabase (the service name, not localhost!)
- Port: 5432
- Username: root
- Password: root

Part 6: SQL Refresher

Quick review of SQL queries we'll use a lot.

JOINs

There are two ways to write an INNER JOIN:

-- Implicit join (old style)
SELECT t.*, z."Zone"
FROM yellow_taxi_data t, zones z
WHERE t."PULocationID" = z."LocationID";

-- Explicit join (preferred)
SELECT t.*, z."Zone"
FROM yellow_taxi_data t
JOIN zones z ON t."PULocationID" = z."LocationID";

For multiple joins:

SELECT 
    t.total_amount,
    zpu."Zone" AS pickup_zone,
    zdo."Zone" AS dropoff_zone
FROM yellow_taxi_data t
JOIN zones zpu ON t."PULocationID" = zpu."LocationID"
JOIN zones zdo ON t."DOLocationID" = zdo."LocationID";

GROUP BY and Aggregations

Count trips per day:

SELECT 
    CAST(tpep_dropoff_datetime AS DATE) AS day,
    COUNT(1) AS trip_count
FROM yellow_taxi_data
GROUP BY CAST(tpep_dropoff_datetime AS DATE)
ORDER BY day;

Multiple aggregations:

SELECT 
    CAST(tpep_dropoff_datetime AS DATE) AS day,
    COUNT(1) AS trips,
    MAX(total_amount) AS max_amount,
    SUM(total_amount) AS total_revenue
FROM yellow_taxi_data
GROUP BY 1
ORDER BY trips DESC;

Data Quality Checks

Find NULL values:

SELECT COUNT(*) FROM yellow_taxi_data
WHERE "PULocationID" IS NULL;

Find values not in lookup table:

SELECT * FROM yellow_taxi_data
WHERE "PULocationID" NOT IN (SELECT "LocationID" FROM zones);

Part 7: Terraform & GCP

Terraform is Infrastructure as Code (IaC). Instead of clicking around in a cloud console, you write config files describing what you want, and Terraform creates it.

Why Terraform?

Version control your infrastructure
Reproducible environments
Easy to replicate across dev/staging/production
Works with AWS, GCP, Azure, and many more

GCP Setup

Create a Google Cloud account (free tier gives you $300 credits)
Create a new project
Create a service account:
- Go to IAM & Admin > Service Accounts
- Create new service account
- Give it these roles: Storage Admin, BigQuery Admin
Download the JSON key file
Set the environment variable:

export GOOGLE_APPLICATION_CREDENTIALS="/path/to/your/key.json"

Terraform Basics

Main files:

main.tf - main configuration
variables.tf - variable definitions

Basic main.tf example:

terraform {
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "5.6.0"
    }
  }
}

provider "google" {
  project = "your-project-id"
  region  = "us-central1"
}

resource "google_storage_bucket" "data_lake" {
  name          = "your-unique-bucket-name"
  location      = "US"
  force_destroy = true
}

resource "google_bigquery_dataset" "dataset" {
  dataset_id = "trips_data"
  location   = "US"
}

Terraform Commands

The workflow is always:

# 1. Initialize (download providers)
terraform init

# 2. Preview changes
terraform plan

# 3. Apply changes
terraform apply

# 4. When you're done, destroy resources
terraform destroy

For auto-approving (skips confirmation):

terraform apply -auto-approve
terraform destroy -auto-approve

Common Terraform Flags

-auto-approve - don't ask for confirmation
-var="name=value" - pass variables
-var-file="file.tfvars" - use a variables file

Useful Tips

Docker Cleanup Commands

# Remove all stopped containers
docker container prune

# Remove unused images
docker image prune

# Remove unused volumes
docker volume prune

# Nuclear option - remove everything unused
docker system prune -a

Checking Ports

If a port is already in use:

# Find what's using port 5432
lsof -i :5432
# or
netstat -tulpn | grep 5432

Docker Networking

When containers need to talk to each other:

In Docker Compose: use service names as hostnames
Manual setup: create a network with docker network create

docker network create my_network
docker run --network=my_network --name=container1 ...
docker run --network=my_network --name=container2 ...
# container2 can reach container1 using hostname "container1"

Summary

What we covered:

Docker - containerization for reproducible environments
PostgreSQL - relational database running in Docker
Data Ingestion - loading data with Python/pandas/SQLAlchemy
Docker Compose - orchestrating multiple containers
SQL - querying and aggregating data
Terraform - infrastructure as code for GCP

The main takeaway: these tools help you build reproducible, scalable data pipelines. Docker ensures your code runs the same everywhere, and Terraform ensures your infrastructure is consistent and version-controlled.

Resources

# Medical RAG Architecture Overview #llmszoomcamp

Abdelrahman Adnan — Sat, 04 Oct 2025 18:26:50 +0000

This document provides a comprehensive explanation of the Retrieval-Augmented Generation (RAG) system architecture, breaking down each component and showing how they work together to deliver accurate medical information.

1. What is RAG?

RAG combines the power of:

Information Retrieval: Finding relevant documents from a knowledge base
Language Generation: Using LLMs to synthesize coherent answers
Context Grounding: Ensuring answers are based on retrieved evidence

2. High-Level Architecture Flow

[User Question] 
    ↓
[Hybrid Search: Vector + BM25]
    ↓
[Context Assembly & Prompt Building]
    ↓
[LLM Generation (GPT-4o-mini/GPT-4o)]
    ↓
[Answer Evaluation & Quality Assessment]
    ↓
[Metrics Calculation & Response Packaging]

3. Detailed Processing Pipeline

Step 1: Query Processing

Clean and normalize the input medical question
Prepare query for both semantic and lexical search

Step 2: Hybrid Retrieval

Vector Search: Semantic similarity using 384-dimensional embeddings
BM25 Search: Keyword-based exact matching
RRF Fusion: Combines both approaches using Reciprocal Rank Fusion

Step 3: Context Assembly

Select top-k most relevant medical cases
Format retrieved documents into structured context
Apply medical domain-specific scoring enhancements

Step 4: Answer Generation

Build specialized medical prompt with retrieved context
Generate response using OpenAI models with controlled parameters
Apply medical safety guidelines

Step 5: Quality Assurance

Evaluate answer relevance using LLM-as-a-judge
Calculate confidence scores and metadata
Track performance metrics and costs

4. Core System Components

Component	File Location	Primary Responsibility
RAG Orchestrator	`src/core/rag.py`	Main pipeline coordination
Vector Database	`src/database/vector_db.py`	Hybrid search + RRF fusion
Data Ingestion	`scripts/ingest.py`	Document processing & indexing
API Layer	`src/api/main_api.py`	REST endpoints & async processing
Web Interface	`src/api/web_interface.py`	Interactive Streamlit UI
Monitoring	`src/services/s3_service.py`	Logging & metrics collection

5. Advanced Search Mechanism

Hybrid Search Strategy

Our system implements a sophisticated hybrid approach that combines:

Semantic Vector Search (Cosine Similarity)

   # src/core/rag.py
   def search(query: str, top_k: int = 5) -> List[Dict]:
       """Search medical knowledge base using hybrid search"""
       return hybrid_query_rrf(query, top_k=top_k)

BM25 Keyword Search (Exact Token Matching)
- Handles medical terminology and acronyms
- Captures exact drug names and dosages
- Preserves clinical precision

Reciprocal Rank Fusion (RRF) Algorithm

RRF combines multiple ranking approaches using the formula:

RRF_score = Σ(1 / (k + rank_i))

Where k=60 (tuning parameter) and rank_i is the position in each ranking list.

Medical Domain Scoring Enhancements

Severity Weighting: Life-threatening conditions get priority
Department Relevance: Matches medical specialties
Symptom Alignment: Boosts exact symptom matches
Treatment Precision: Enhances therapeutic recommendations

6. Medical Prompt Engineering

Structured Prompt Architecture

Our prompts are carefully designed for medical accuracy and safety:

System Instruction Design

Role Definition: "You are a knowledgeable medical assistant"
Evidence Constraint: "Answer based solely on provided CONTEXT"
Factual Grounding: "Use only facts from the CONTEXT"

Context Formatting Strategy

Each retrieved medical case follows a structured template:

# src/core/rag.py
PROMPT_TEMPLATE = """You are a knowledgeable medical assistant. Answer the QUESTION based solely on the information provided in the CONTEXT from the medical database.

Use only the facts from the CONTEXT when formulating your answer.

QUESTION: {question}

CONTEXT:
{context}""".strip()

ENTRY_TEMPLATE = """Medical Case:
Question: {question}
Answer: {answer}
Relevance Score: {score:.3f}""".strip()

# src/core/rag.py
def build_prompt(query: str, search_results: List[Dict]) -> str:
    context = ""
    for doc in search_results:
        context += (
            ENTRY_TEMPLATE.format(
                question=doc.get("question", "N/A"),
                answer=doc.get("answer", "N/A"),
                score=doc.get("score", 0.0),
            ) + "\n\n"
        )
    return PROMPT_TEMPLATE.format(question=query, context=context.strip())

8. Language Model Integration

Model Selection Strategy

# src/core/rag.py
def llm(prompt: str, model: str = "gpt-4o-mini") -> Tuple[str, Dict]:
    """Generate response using OpenAI LLM with medical-optimized parameters"""
    response = client.chat.completions.create(
        model=model,
        messages=[{"role": "user", "content": prompt}],
        max_tokens=1000,        # Sufficient for comprehensive medical answers
        temperature=0.1,        # Low temperature for consistency and accuracy
    )

Parameter Optimization for Medical Use

Temperature (0.1): Ensures deterministic, conservative responses
Max Tokens (1000): Balances comprehensiveness with cost
Model Choice: GPT-4o-mini provides 91.11% relevance vs GPT-4o's 64.75%

Cost-Performance Analysis

Model	Relevance Rate	Cost per 1K tokens	Use Case
GPT-4o-mini	91.11%	$0.00015 (input)	Primary model
GPT-4o	64.75%	$0.03 (input)	Complex cases only

Response Processing

token_stats = {
    "prompt_tokens": response.usage.prompt_tokens,
    "completion_tokens": response.usage.completion_tokens,
    "total_tokens": response.usage.total_tokens,
}
return answer, token_stats

9. Comprehensive Answer Evaluation

LLM-as-a-Judge Methodology

We implement automated quality assessment using a specialized evaluation prompt:

# src/core/rag.py
EVALUATION_PROMPT_TEMPLATE = """You are an expert medical reviewer evaluating the quality and relevance of AI-generated medical responses.

You will be given a medical question and a generated answer. Based on the relevance of the generated answer, you will classify it as "NON_RELEVANT", "PARTLY_RELEVANT", or "RELEVANT".

Question: {question}
Generated Answer: {answer}

Provide evaluation in JSON format:
{{
  "Relevance": "NON_RELEVANT" | "PARTLY_RELEVANT" | "RELEVANT",
  "Explanation": "[Brief explanation for evaluation]"
}}"""

Quality Assessment Metrics

Relevance Categories:
- RELEVANT: Direct, accurate medical information
- PARTLY_RELEVANT: Partially helpful but incomplete
- NON_RELEVANT: Off-topic or potentially harmful
Evaluation Processing:

   def evaluate_relevance(question: str, answer: str) -> Tuple[Dict, Dict]:
       prompt = EVALUATION_PROMPT_TEMPLATE.format(question=question, answer=answer)
       evaluation, tokens = llm(prompt, model="gpt-4o-mini")
       try:
           json_eval = json.loads(evaluation)
           return json_eval, tokens
       except json.JSONDecodeError:
           return {"Relevance": "UNKNOWN", "Explanation": "Parse failed"}, tokens

Medical Safety Considerations

Conservative Evaluation: Strict relevance criteria
Explanation Tracking: Maintains audit trail for quality decisions
Error Handling: Graceful degradation for parsing failures
Dual Model Use: Separate evaluation model reduces bias

10. Cost Optimization & Monitoring

Transparent Cost Calculation

# src/core/rag.py
def calculate_openai_cost(model: str, tokens: Dict) -> float:
    """Calculate OpenAI API cost with model-specific pricing"""
    cost = 0.0
    if model == "gpt-4o-mini":
        cost = (
            tokens.get("prompt_tokens", 0) * 0.00015 +      # Input cost
            tokens.get("completion_tokens", 0) * 0.0006     # Output cost
        ) / 1000
    elif model == "gpt-4o":
        cost = (
            tokens.get("prompt_tokens", 0) * 0.03 +         # Higher input cost
            tokens.get("completion_tokens", 0) * 0.06       # Higher output cost
        ) / 1000
    return cost

Cost Performance Metrics

Average Cost per Query: $0.003
Token Efficiency: ~23 seconds response time
Cost Breakdown: RAG generation + evaluation costs tracked separately

11. Complete Pipeline Integration

Master RAG Function

# src/core/rag.py
def rag(query: str, model: str = "gpt-4o-mini") -> Dict:
    """Complete RAG pipeline with comprehensive response data"""
    t0 = time()  # Start timing

    # Step 1: Hybrid search
    search_results = search(query)

    # Step 2: Prompt assembly
    prompt = build_prompt(query, search_results)

    # Step 3: LLM generation
    answer, token_stats = llm(prompt, model=model)

    # Step 4: Quality evaluation
    relevance, rel_token_stats = evaluate_relevance(query, answer)

    # Step 5: Metrics calculation
    took = time() - t0
    total_cost = calculate_openai_cost(model, token_stats) + \
                calculate_openai_cost("gpt-4o-mini", rel_token_stats)

    # Step 6: Response packaging
    return {
        "answer": answer,
        "model_used": model,
        "response_time": took,
        "relevance": relevance.get("Relevance", "UNKNOWN"),
        "relevance_explanation": relevance.get("Explanation", "None"),
        "total_cost": total_cost,
        "token_stats": {...},  # Comprehensive token tracking
        "search_results_count": len(search_results),
        "search_results": search_results[:5]  # Top results for audit
    }

12. Architecture Design Principles

Medical Safety First

Evidence-Based: All answers must cite retrieved medical literature
Conservative Generation: Low temperature prevents hallucination
Quality Gates: Multi-step evaluation ensures reliability
Audit Trails: Complete logging for medical compliance

Performance Optimization

Hybrid Retrieval: Combines semantic understanding with exact matching
Model Selection: Cost-effective model choice based on performance data
Caching Ready: Architecture supports future caching implementations
Scalable Design: Async processing and background tasks

Production Considerations

Error Handling: Graceful degradation at each step
Monitoring Integration: Built-in metrics and logging hooks
Cost Control: Transparent pricing with usage tracking
Extensibility: Modular design supports feature additions

13. Future Enhancement Opportunities

Immediate Improvements

Citation Generation: Add source document references in answers
Query Preprocessing: Medical entity recognition and normalization
Response Caching: Cache frequent queries to reduce costs
Batch Processing: Optimize multiple simultaneous queries

Advanced Features

Multi-Modal Support: Integrate medical images and charts
Specialized Models: Fine-tuned models for specific medical domains
Real-Time Learning: Incorporate user feedback into model updates
Clinical Integration: EMR system compatibility and FHIR support

Research Directions

Retrieval Enhancement: Advanced embedding models for medical text
Generation Improvement: Medical-specific language model fine-tuning
Evaluation Evolution: Automated medical accuracy assessment
Safety Advancement: Enhanced harm detection and prevention

DEV Community: Abdelrahman Adnan

Part 3: Testing, Documentation & Deployment 🚀

DataEngineeringZoomcamp #dbt #AnalyticsEngineering #DataModeling

Macros - Reusable SQL Functions 🔧

Why Use Macros?

Jinja Templating

dbt Packages - Community Libraries 📦

Popular Packages

Installing Packages

Testing in dbt 🧪

Running Tests

Documentation 📝

Adding Descriptions

Generating Docs

Essential dbt Commands 💻

The Big Four

Other Useful Commands

Selecting Specific Models

Target Environments

Materializations - Views vs Tables 📊

Types of Materializations

Setting Materializations

View vs Table Decision

Putting It All Together - The NYC Taxi Project 🚕

What We Build

The Models We Create

Setup Options 🔧

Option 1: Local Setup (DuckDB + dbt Core)

Option 2: Cloud Setup (BigQuery + dbt Cloud)

Troubleshooting Common Issues 🔍

"Profile not found"

"Source not found"

"Model depends on model that was not found"

DuckDB Out of Memory

Key Takeaways 🎓

Additional Resources 📚

Part 2: dbt Project Structure & Building Models 📁

DataEngineeringZoomcamp #dbt #AnalyticsEngineering #DataModeling

Why Model Data? 📐

Fact Tables vs Dimension Tables

The Star Schema ⭐

dbt Project Structure

The dbt_project.yml File

The Three Model Layers

Sources and the source() Function 📥

What are Sources?

Using the source() Function

The ref() Function - Building Dependencies 🔗

source() vs ref()

How ref() Works

The DAG (Directed Acyclic Graph)

Seeds - Loading CSV Files 🌱

When to Use Seeds

How to Use Seeds

# Module 4 Summary - Analytics Engineering with dbt

DataEngineeringZoomcamp #dbt #AnalyticsEngineering #DataModeling

Part 1: Introduction to Analytics Engineering & dbt Fundamentals 🎯

What is Analytics Engineering?

The Evolution of Data Roles

What Does an Analytics Engineer Do?

The Kitchen Analogy 🍳

What is dbt? 🛠️

The Problems dbt Solves

How dbt Works

dbt Core vs dbt Cloud

Part 3: Partitioning & Clustering for Performance 🚀

The Problem We're Solving 🤔

Partitioning: Dividing Your Table into Sections 📁

How Partitioning Works in BigQuery

Types of Partitioning

Partitioning Rules to Remember ⚠️

Clustering: Organizing Data Within Partitions 🗂️

How Clustering Works

Clustering Rules 📏

Partitioning vs Clustering: When to Use What? 🤷

Real-World Performance Comparison 📊

Test 1: Filtering by Date Range

Test 2: Filtering by Date AND Vendor

Step-by-Step: Creating an Optimized Table

Best Practices Cheat Sheet ✅

The `dbt_project.yml` File

Sources and the `source()` Function 📥

Using the `source()` Function

The `ref()` Function - Building Dependencies 🔗

`source()` vs `ref()`

How `ref()` Works