5 Analytics Engineering Portfolio Projects for Data Engineers

Here are five analytics engineering portfolio projects you can ship in public. Each one names the dataset, the stack (warehouse, dbt, orchestration), and the deliverables (github, docs, and a dashboard). Do these well and you prove you can design a data pipeline, model a data warehouse, apply testing, and communicate with stakeholders. Keep scope tight, production-like, and end-to-end data from raw data to BI.

The stack a data engineer portfolio should show

What catches a reviewer’s eye: public github, dbt models with tests, a cloud data warehouse, a scheduler, and a dashboard link. Use the table to pick where to start.

Project 1: Batch rideshare analytics mart (dbt + data warehouse)

Goal: build an end-to-end data mart from a large public dataset and publish a dashboard.

Dataset: NYC TLC yellow/green trips (CSV/Parquet). Expect big data volume; a single year can exceed 40M rows.

Stack: BigQuery or Snowflake as the data warehouse; dbt Core; scheduler (GitHub Actions). Optional: stage CSVs in a cloud bucket as a lightweight data lake.

Deliverables:

• Models: stg_trips, dim_date, dim_location, fct_trip_metrics (incremental).
• Tests: not_null, unique, accepted_values, and freshness checks.
• Dashboard: daily trips, avg fare, pct cash vs card, pickup density bands.
• README with lineage diagram, assumptions, and performance notes.

dbt model (incremental partitioned by pickup_date):

-- models/marts/fct_trip_metrics.sql
{{
  config(
    materialized='incremental',
    unique_key='trip_id',
    partition_by={'field': 'pickup_date', 'data_type': 'date'},
    cluster_by=['pickup_borough']
  )
}}

with base as (
  select
    trip_id,
    cast(pickup_datetime as date) as pickup_date,
    pickup_borough,
    dropoff_borough,
    passenger_count,
    fare_amount,
    total_amount,
    payment_type
  from {{ ref('stg_trips') }}
  {% if is_incremental() %}
    where pickup_datetime >= date_sub(current_date, interval 7 day)
  {% endif %}
)

select
  trip_id,
  pickup_date,
  pickup_borough,
  dropoff_borough,
  passenger_count,
  fare_amount,
  total_amount,
  case when payment_type = 'Cash' then 1 else 0 end as is_cash
from base;

Tests and source freshness:

# models/schema.yml
version: 2

sources:
  - name: tlc
    tables:
      - name: trips
        freshness:
          warn_after: {count: 2, period: day}
          error_after: {count: 5, period: day}

models:
  - name: fct_trip_metrics
    tests:
      - not_null: {column_name: trip_id}
    columns:
      - name: pickup_borough
        tests:
          - accepted_values:
              values: ['Manhattan','Brooklyn','Queens','Bronx','Staten Island']

Tip: If you need a dbt refresher, skim the dbt Tutorial for Beginners. For test depth, see dbt Tests: Complete Guide.

Project 2: Real-time events analytics (GitHub Archive → BigQuery → dbt)

Goal: build a real-time data pipeline and a simple live dashboard.

Dataset: GitHub Archive hourly events. Treat it as streaming by ingesting each hour as it lands. This simulates real-time data without Kafka.

Stack: Ingest with a lightweight script or managed connector into a partitioned BigQuery table; dbt incremental models; scheduler to run every 15 minutes. This captures real-time constraints and cost control.

Deliverables:

• Bronze: raw events table partitioned by event_time.
• Silver: stg_events with typed columns and schema drift handling.
• Gold: fct_repo_activity per repo/org per hour.
• Dashboard: pushes/issues/PRs by org, top languages today, 24h trend.

Incremental model filtering recent partitions:

-- models/marts/fct_repo_activity.sql
{{
  config(
    materialized='incremental',
    unique_key='repo_id||event_hour',
    partition_by={'field': 'event_hour', 'data_type': 'timestamp'}
  )
}}

with events as (
  select
    repo.id as repo_id,
    repo.name as repo_name,
    org.login as org,
    cast(timestamp_trunc(created_at, hour) as timestamp) as event_hour,
    type as event_type
  from {{ ref('stg_events') }}
  {% if is_incremental() %}
    where created_at >= timestamp_sub(current_timestamp, interval 2 day)
  {% endif %}
)

select
  repo_id,
  repo_name,
  org,
  event_hour,
  countif(event_type = 'PushEvent') as pushes,
  countif(event_type = 'IssuesEvent') as issues,
  countif(event_type = 'PullRequestEvent') as prs
from events
group by 1,2,3,4;

Call it a real-time data pipeline in your README and diagram the flow. Mention late-arriving events and idempotent loads as data challenges.

Project 3: Marketing analytics with GA4 public dataset

Goal: sessionize events and build simple attribution. This is classic data analytics adjacent to a data engineering role supporting growth.

Dataset: GA4 public sample in BigQuery (semi-structured JSON). It includes ecommerce-like events.

Stack: BigQuery; dbt for session stitching and channels mapping; BI for funnel/ROAS; scheduler nightly.

Deliverables:

• stg_events with normalized keys (user, session, event).
• dim_channel with UTM and referrer logic.
• fct_sessions and fct_attribution (first touch vs last non-direct).
• Dashboard: sessions, conversion rate, revenue by channel and campaign.

Example channel mapping logic:

-- models/marts/dim_channel.sql
select
  session_id,
  case
    when utm_medium in ('cpc','ppc') then 'Paid Search'
    when regexp_contains(referrer, r'facebook|instagram') then 'Paid Social'
    when utm_medium = 'email' then 'Email'
    when source = '(direct)' then 'Direct'
    else 'Organic/Other'
  end as channel
from {{ ref('stg_events') }}
where event_name = 'session_start';

Basic attribution join:

-- models/marts/fct_attribution.sql
with s as (
  select session_id, user_id, min(event_timestamp) as session_start
  from {{ ref('stg_events') }}
  where event_name = 'session_start'
  group by 1,2
),
first_touch as (
  select user_id, min(event_timestamp) as ft_ts
  from {{ ref('stg_events') }}
  group by 1
)
select
  s.user_id,
  s.session_id,
  d.channel as last_touch_channel,
  ft.user_id is not null as is_first_touch_user
from s
join {{ ref('dim_channel') }} d using (session_id)
left join first_touch ft using (user_id);

Focus your write-up on data transformation choices (windowing, dedupe) and trade-offs. Call out schema drift, sparse UTM tags, and null user_ids as data challenges.

Project 4: CDC into a warehouse with dbt snapshots

Goal: replicate a transactional Postgres into Snowflake, then model type-2 history. This mirrors common end-to-end data work.

Dataset: Postgres Pagila (DVD rental). Use a connector (e.g., Airbyte/Open Source) to land tables into Snowflake raw schema.

Stack: Snowflake; dbt sources and snapshots; scheduler daily.

Deliverables:

• sources.yml with loaded raw tables.
• snapshots for customer and inventory.
• marts: dim_customer_scd2, fct_rentals.
• Dashboard: active customers, churned customers by month, on-time returns.

Snapshot example:

-- snapshots/customer_scd2.sql
{% snapshot customer_scd2 %}
{{
  config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='last_update_ts'
  )
}}

select *
from {{ source('pagila_raw', 'customer') }}

{% endsnapshot %}

Use the snapshot to build an SCD2 dimension. If snapshots are new, see dbt Snapshots: Playbook.

Project 5: Reliability, docs, and CI/CD on top of the 5 projects

Goal: show discipline beyond modeling—validation, documentation, and deploy hygiene. This is where you highlight data quality, lineage, and ownership.

Scope: pick any project above and add:

• Robust tests (schema and data), plus an audit model.
• Auto-generated docs with exposures tied to a dashboard.
• CI/CD that runs dbt build on PRs and blocks merges on failures.

Example tests and exposure:

# models/marts/schema.yml
version: 2

models:
  - name: fct_trip_metrics
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns: [trip_id]
    columns:
      - name: total_amount
        tests:
          - not_null
          - relationships:
              to: ref('dim_location')
              field: borough

exposures:
  - name: trips_dashboard
    type: dashboard
    maturity: high
    url: https://your-bi-tool.example/trips
    depends_on:
      - ref('fct_trip_metrics')
    owner:
      name: Analytics
      email: analytics@example.com

For an overview of observability concepts and why they matter, read Data Observability Explained. To wire PR checks and deployments, start with CI/CD for Analytics Projects.

How to package your work so hiring managers notice

• One repo per project. Keep the dbt project at the root with a clear README and a /docs section. Link the dashboard, an ERD, and a short walkthrough.
• Name it clearly: de-nyc-taxi-dbt-bq is better than project1. Include the term data pipeline in the description.
• Pin a top-level summary README that lists the 5 data engineering projects with stack diagrams.
• Use issues/PRs to show process. Keep the repository tidy and changes reviewable.
• Add a HOW_TO_RUN.md to the github repository and a recorded demo link in each project.

Getting started fast (templates and datasets)

• Templates: clone your own minimal dbt starter with models/, seeds/, and a profiles.yml example. If you’re new to dbt, start here: dbt Tutorial for Beginners.
• Public datasets: NYC TLC trips, GA4 public sample, GitHub Archive. These cover batch, semi-structured JSON, and near-real-time.
• Local-first option: DuckDB + dbt Core can power a serious data engineering project on your laptop; later swap DuckDB for a cloud warehouse.

FAQ: beginners, tooling, and what actually gets interviews

Analytics engineer and want to start my 1st portfolio project—how should I begin?

Pick Project 1. Load one month of trips, create one staging model, one mart, one dashboard. Ship in two days. Then scale to a full year with incremental logic.

Analytics engineer and want to start my 1st portfolio project—how should I begin??

Same approach, even simpler: land 7 days, build a single incremental fact, publish a dashboard, and document assumptions. Iterate weekly.

Any guided projects, templates, or capstone repos out there for analytics engineering?

Use this article’s specs as your capstone. Pair with the dbt beginner tutorial and the dbt tests guide for structure.

Any public datasets that make for a solid project?

NYC TLC (high volume), GA4 public sample (semi-structured), and GitHub Archive (event stream). Together they cover batch, JSON, and near real-time.

Are cloud tools like AWS and Google BigQuery necessary for data engineering projects?

No. Start local to practice data processing and modeling. When ready, move to a cloud data warehouse for scale and IAM patterns. Control cost with partitions, clustering, and incremental models.

Hiring managers: What kinds of projects actually catch your eye in a portfolio?

Clear scope, production-like touches (tests, incremental, scheduler), and a dashboard that answers stakeholder questions. Bonus: a README section on trade-offs and performance.

How can data engineering projects help in building my portfolio?

They prove you can take raw data to insight. Your engineer portfolio should show orchestration, modeling, and presentation—not just SQL scripts.

How do I choose the right data engineering project for my skill level?

Use the table: start with Project 1 (batch), then add streaming (Project 2) or CDC (Project 4). Each adds a new competency without boiling the ocean.

How do observability tools like Monte Carlo improve the reliability of data pipelines?

They detect anomalies in freshness, volume, and schema, and trace lineage to impacted assets. If you cannot use a tool, emulate basics with dbt tests, audit queries, and alerts. See Data Observability Explained.

How many projects do I need for a data engineering portfolio?

Three solid ones are enough. Ship 5 projects only if you can keep quality high.

Why these 5 work (and what to highlight on GitHub)

These cover batch, streaming, CDC, and reliability. They use dbt, a scheduler, and a BI tool—hallmarks of the modern data stack. In each README, add:

• System diagram: data sources → staging (lake/landing) → warehouse → dbt → BI.
• Model diagram: facts/dimensions and grain.
• Perf notes: partitions, clustering, incremental filters.
• Risks: schema drift, late-arriving events, duplication.
• Future work: add source freshness, backfills, or a bronze layer.

Common code snippets hiring managers like to see

Source definitions with freshness:

# models/sources.yml
version: 2

sources:
  - name: raw
    tables:
      - name: events
        loaded_at_field: _ingested_at
        freshness:
          warn_after: {count: 6, period: hour}

Incremental merge (warehouse-specific macros not shown):

-- models/marts/fct_orders.sql
{{ config(materialized='incremental', unique_key='order_id') }}
select * from {{ ref('stg_orders') }}
{% if is_incremental() %}
  where updated_at > (select coalesce(max(updated_at), '1900-01-01') from {{ this }})
{% endif %}

A lightweight audit:

-- models/monitoring/audit_row_counts.sql
select
  current_date as run_date,
  'fct_trip_metrics' as table_name,
  count(*) as row_count
from {{ ref('fct_trip_metrics') }}

Notes on scope, realism, and storytelling

Keep scope tight, but realistic. For example: “The orders table has 40M rows; I used partitioning and incremental upserts to keep daily builds under control.” Avoid huge ambitions that never land. Your work should solve real-world data problems with pragmatic trade-offs.

Use terminology recruiters map to the role: analytics engineer, data engineer, data engineering project, data pipeline, data warehouse, real-time, data transformation. Mention adjacent data science only if it supports business outcomes.

Next steps and resources

If interviews are close, skim the dbt interview questions and the interview mistakes guide. For broader planning, visit the career topic hub.

Keywords to weave once in your READMEs where natural so screens find you: data engineering portfolio, data engineering portfolio projects, data engineering role, big data, real-time data, real-time, real-time data pipeline, end-to-end data, modern data stack.

Publish your projects on GitHub, link the dashboards, and keep explanations crisp. Across these 5 projects you’ll demonstrate data processing at scale and reliable delivery.

Want hands-on drills to sharpen these skills? Try our free graded practice exercises at /practice.