Build a Channel Performance KPI Mart with dbt and SQL
Group: Use Case
Product Category: Data Engineering
Sub Category: Analytics Engineer
Solution Available
Source code, datasets, and other related files for the solution will be provided.
Prerequisites
About this Product
Meridian Assurance Group assembles channel performance KPIs manually from spreadsheet exports before monthly reviews, a process that is slow, error-prone, and produces numbers nobody can trace back to agreed definitions.
What you build
- dbt project on local DuckDB: seeds, staging, dimensional, and mart layers all running on an embedded file-based database with no cloud account required.
- Seeds and staging layer: load three source extracts (customers, agents, policies); rename, cast, and validate against mandatory-field, enum, and range rules.
- Dimensional layer: build a simple star schema with one agent dimension (carrying the channel attribute) and one policy-grain fact table with referential-integrity filtering.
- Channel KPIs mart: compute total policy count, active policy count, total premium, average premium per active policy, and distinct active customers per distribution channel (AGENT,BROKER, DIRECT).
- Data validation tests: write dbt schema tests (not_null, unique, accepted_values, relationships) plus a singular test to catch negative premiums; document KPI formulas with dbt docs.
Skills you practise
- dbt fundamentals: project structure, seeds, the ref() macro, YAML configuration, and dbt docs generation.
- SQL for analytics: joins, conditional aggregation, filtering; staging and mart query logic.
- Dimensional modeling: building a simple star schema with dimensions and facts at the correct grain.
Outcome
- Documented dbt project: fully tested, with dbt docs and a README explaining setup and results for your portfolio.
- Analytics engineering foundation: hands-on experience modeling and testing data for business intelligence.
Interview Q&A Included
1 practice set bundled with this lab
-
Channel Performance KPI Mart with dbt and SQL Assessment
Category: Data Engineering Subcategory: Analytics Engineer 25 questionsThis assessment checks what you learned while building the channel performance KPI mart for Meridian Assurance Group. It is split into four groups: the business scenario and assumptions, business rules …
Resources
A complete, tested dbt project to compare with your own SQL when you finish or get stuck.
- Every rule implemented, with all 14 dbt tests passing in a full build from a clean database
- Same models as the starter, fully written: staging views, the policy fact, the agent dimension and the KPI mart
- KPI values for the Agent, Broker and Direct channels that match the BRD exactly
- Shows one clean way to filter invalid rows and join facts to dimensions in plain SQL
- README with the exact build and test commands for Windows and macOS
- Use it to check your approach, not to copy
Topics
Skills
Languages
English
Level
Beginner
Business Domain
Insurance and Customer Distribution
Project Mentors
Labs • Projects • Practice
Similar Products
> more hands-on labs picked for you_
Build an Enterprise AI Gateway with Model Routing and FastAPI
Plant teams call model vendors directly, so there is no shared cost control, no safety screening and no audit trail …
Level
Expert
Time
48.0 hrs
Language
English
Build a RAG Freight Pricing Copilot with Chroma and FastAPI
Pricing analysts at freight brokerages spend hours manually searching fragmented contracts, rate sheets and quote history to answer simple questions …
Level
Intermediate
Time
30.0 hrs
Language
English
Build a Farm Lending RAG Copilot with Reranking and Guardrails
A farm lender's credit officers spend about 20 minutes searching policy versions and one farmer's records for every mixed question, …
Level
Advanced
Time
36.0 hrs
Language
English
Build a Governed Promotion Pricing Workflow with LangGraph and FastAPI
A retail team runs 40 to 60 promotional campaigns a month, yet nobody can prove whoapproved which price.What you buildPlanner …
Level
Advanced
Time
30.0 hrs
Language
English
Build a Usage Rating and Billing Pipeline with Python and PostgreSQL
Cobalt Mobile needs a repeatable way to convert voice call detail records into billable charges, but today an analyst does …
Level
Beginner
Time
2.0 hrs
Language
English
Build a Stock Availability Model Release Pipeline with MLflow and Docker
Build a Stock Availability Model Release Pipeline with MLflow and Docker is a MLOps use case lab that builds a …
Level
Intermediate
Time
6.0 hrs
Language
English
Build a Farm Water RAG Copilot with Qdrant and FastAPI
Build a Farm Water RAG Copilot with Qdrant and FastAPI is a GenAI business requirement lab that builds Rillmark Water …
Level
Intermediate
Time
12.0 hrs
Language
English
Build an Insurance Claims Platform with Node.js, RabbitMQ and Redis
A mid size insurer needs first notice of loss to reach settlement without duplicate payments, even when a downstream service …
Level
Advanced
Time
60.0 hrs
Language
English
Build a Governed Student Intervention Agent Team with Python
Build a Governed Student Intervention Agent Team with Python is an advanced, GenAI use case lab that builds Beacon, a …
Level
Advanced
Time
10.0 hrs
Language
English
Build a Grounded RAG Copilot for Insurance Claims with Qdrant
Health insurance examiners need to resolve denied claims by finding the right policy version and citing the specific paragraph, but …
Level
Intermediate
Time
8.0 hrs
Language
English
Design a Shipment Tracking Data Model and Pipeline with PostgreSQL
Shipment Tracking Data Model and Pipeline with PostgreSQL is a beginner-level data engineering project for a fictional freight logistics company. …
Level
Beginner
Time
24.0 hrs
Language
English
Build a No Show Prediction Model and Deploy with FastAPI
Clinics lose revenue to no-show appointments; a notebook model can predict them, but it needs to become a governed service.What …
Level
Beginner
Time
4.0 hrs
Language
English
Build a Property Pricing Data Warehouse with DuckDB and Python
Build a Property Pricing Data Warehouse with DuckDB and Python is a data engineering product that builds CompVantage — a …
Level
Intermediate
Time
24.0 hrs
Language
English
Build a Governed Fleet Route Data Platform with Python and DuckDB
Build a Governed Fleet Route Data Platform with Python and DuckDB is a data engineering product that builds the complete …
Level
Beginner
Time
16.0 hrs
Language
English
Build a Visit Duration Prediction Model with scikit learn
Healthcare practices over-book appointments into fixed 15-minute slots, causing visits to overrun and waiting rooms to back up. What you buildFeature …
Level
Intermediate
Time
4.0 hrs
Language
English
Build a Real Time Retail Sales Pipeline with Kafka Python and Google BigQuery
Build a Real Time Retail Sales Pipeline with Kafka, Python and Google BigQuery is a data engineering product that builds …
Level
Intermediate
Time
4.0 hrs
Language
English
Build an AI Banking Concierge with Guarded Multi Model Orchestration
A retail bank's onboarding and servicing teams handle a growing stream of applications and requests such as address changes, card …
Level
Advanced
Time
18.0 hrs
Language
English
Build a Banking Service Agent with Tool Calling and Human Approval
A savings bank's servicing clerks spend much of each day reading customer requests to change an address, mobile number or …
Level
Beginner
Time
24.0 hrs
Language
English
Build a Real Time Retail Sales Pipeline with Kafka PySpark and PostgreSQL
Build a Real Time Retail Sales Pipeline with Kafka, PySpark and PostgreSQL is an intermediate-level data engineering product that builds …
Level
Intermediate
Time
4.0 hrs
Language
English
InterviewLens — AI powered Interview Scorer and Analyzer
InterviewLens is an advanced, full-stack capstone project that turns a recorded technical interview into structured meeting notes, a confidence score, …
Level
Advanced
Time
20.0 hrs
Language
English
Build a Real Time Retail Sales Pipeline with Kafka Python and PostgreSQL
Build a Real Time Retail Sales Pipeline with Kafka, Python and PostgreSQL is an intermediate-level data engineering product that builds …
Level
Intermediate
Time
4.0 hrs
Language
English
Build Governed AI Agents for Lease Workflows with Python and FastAPI
A property team gets hundreds of tenant messages a month, and an AI agent that acts on them without controls …
Level
Advance
Time
48.0 hrs
Language
English
Build a Multi Agent Citizen Services Workflow with Tool Governance
Tessmoor Borough Council's Resident Support Team manually handles hundreds of messages each month about grants, causing delays and errors.What you …
Level
Advanced
Time
16.0 hrs
Language
English
Build a Procurement Analytics Mart with dbt, DuckDB and Star Schema Design
Build a Procurement Analytics Mart with dbt, DuckDB and Star Schema Design is a beginner-level product that builds a governed …
Level
Beginner
Time
6.0 hrs
Language
English
Build Tenancy Microservices with FastAPI, PostgreSQL and Redis
A residential property manager needs its tenancy, rent and renewal processes to stay correct when services fail, events arrive twice …
Level
Advance
Time
20.0 hrs
Language
English
Build a Lease Document Copilot with RAG, ChromaDB and Evaluation
A residential property manager with about 4,000 apartments relies on leasing agents to answertenant and applicant questions about lease terms, …
Level
Intermediate
Time
6.0 hrs
Language
English
AI Powered Government Permit Application Assistant with RAG and Vector Search
AI Powered Government Permit Application Assistant with RAG and Vector Search is an intermediate-level, Python CLI product that builds a …
Level
Intermediate
Time
5.0 hrs
Language
English
Build a Grounded Banking Policy Copilot with RAG and Vector Search
Service agents at Cedarline Bank spend long minutes searching policy documents to answer questions about KYC documents, account opening, address …
Level
Intermediate
Time
8.0 hrs
Language
English
Build a Banking Account Onboarding Application with Next.js and FastAPI
Harbourline Savings Union, a fictional retail bank, onboards new customers on paper at branch desks. Branch officers re-key the same …
Level
Beginner
Time
4.0 hrs
Language
English
Intelligent Citizen Support and Service Automation System
Intelligent Citizen Support and Service Automation Application is a beginner-level, Python CLI product that simulates a real-world government service triage …
Level
Intermediate
Time
10.0 hrs
Language
English
Mapleford Request Triage
Project OverviewMapleford Residential relies on manual processes to categorize tenant service requests and assess urgency, often resulting in inconsistent prioritization …
Level
Beginner
Time
2.0 hrs
Language
English
Build a Citizen Request Tracker with React, FastAPI and PostgreSQL
Larkmoor City Council's Neighbourhood Services team handles potholes, broken streetlights, missed bin collections, graffiti and dumped waste. Citizens report issues …
Level
Beginner
Time
3.0 hrs
Language
English
Build a Leasing Management Application with React, FastAPI and PostgreSQL
Project OverviewBuild a full-stack Leasing Management Application to automate rental operations, including tenant applications, lease creation, rent collection, renewals, and …
Level
Beginner
Time
10.0 hrs
Language
English
Build a Production Booking Service with FastAPI, PostgreSQL and Redis
Ferncross Council's licensing counter faces double booking and lost confirmations during demand surges and service failures. They need a booking …
Level
Advanced
Time
6.0 hrs
Language
English
Build a Tool Calling AI Agent for Warehouse Stock Transfers
Ironvale Manufacturing Co. operates two warehouses that continuously move raw materials and finished components between production lines. Today, coordinators submit …
Level
Beginner
Time
6.0 hrs
Language
English
Build a Governed Shopper Segmentation Pipeline with Python
NovaCart Retail Inc. is an online marketplace for home and lifestyle goods. Its Marketing & Personalization team wants to run …
Level
Beginner
Time
4.0 hrs
Language
English
OrderStream : Real Time Order Processing Pipeline
Build a production-style, real-time order processing pipeline with Apache Kafka and PostgreSQL — from event generation through validation, dead-letter handling, …
Level
Intermediate
Language
English
QuickBite — Online Food Ordering & Delivery Platform
Design and build a complete food-delivery domain in pure Python, applying every core OOP concept to a realistic, extensible business …
Level
Intermediate
Language
English
QuickCart — Inventory & Order Management System
Analyze real-world e-commerce requirements, practice BRD authoring, and build a production-ready Python inventory & order system from a structured specificationBusiness …
Level
Intermediate
Language
English
SmartHR — Employee Management & HR Analytics Platform
Analyze real-world HR requirements, practice BRD authoring, and build a production-grade Python HR system from a structured specificationBusiness Overview:The SmartHR …
Level
Intermediate
Language
English
Enterprise Data Security & Governance Implementation using Snowflake
Enterprise Data Security & Governance Implementation using Snowflake is a practical implementation guide that teaches you how to build a …
Level
Advanced
Language
English
Enterprise Sales Analytics Data Warehouse Design with Kimball Modeling & SCD Implementation
Enterprise Sales Analytics Data Warehouse Design with Kimball Modeling & SCD Implementation is a practical implementation guide that teaches you …
Level
Advanced
Language
English
Enterprise Customer 360 Data Platform Implementation using Natural Keys, Row Hashes & Medallion Architecture
Enterprise Customer 360 Data Platform Implementation using Natural Keys, Row Hashes & Medallion Architecture is a practical implementation guide that …
Level
Advanced
Language
English
Dimensional Data Modelling & Star Schema Design for Retail Sales Analytics Using SQL and Python
Dimensional Data Modelling & Star Schema Design for Retail Sales Analytics is a practical implementation guide that teaches you how …
Level
Intermediate
Time
10.0 hrs
Language
English
Historical Dimension Tracking & SCD Pipeline Implementation Using PySpark and PostgreSQL
Historical Dimension Tracking & SCD Pipeline Implementation Using PySpark and PostgreSQL is a practical implementation guide that teaches you how …
Level
Intermediate
Time
10.0 hrs
Language
English
Real Time Kafka Consumer Data Ingestion into RAW Layer Using PySpark
Real-Time Kafka Consumer Data Ingestion into RAW Layer Using PySpark is a practical implementation guide that teaches you how to …
Level
Intermediate
Language
English
Automated Data Ingestion from Google Drive CSV Files Using PySpark
Implementation of Google Drive CSV Data Extraction & Ingestion using PySpark is a practical implementation guide that teaches you how …
Level
Intermediate
Language
English
Implementation of Enterprise API Data Extraction & Ingestion using PySpark
Implementation of REST API Data Extraction & Ingestion using PySpark is a practical implementation guide that teaches you how to …
Level
Intermediate
Language
English
Implementation of Schema Wide Database Data Extraction & Ingestion using PySpark
Implementation of Schema-Wide Database Data Extraction & Ingestion using PySpark is a practical implementation guide that teaches you how to …
Level
Intermediate
Language
English
Healthflow Analytics Platform with Snowflake & Medallion Architecture
Healthflow Analytics Platform with Snowflake & Medallion Architecture is a healthcare data engineering capstone project that demonstrates how to build …
Level
Intermediate
Time
8.0 hrs
Language
English
FinTech Banking ETL Pipeline with PySpark Delta Lake and Medallion Architecture
NovaPay ETL Pipeline is an advanced data engineering capstone project that builds a production-grade ETL pipeline for a fictional digital …
Level
Advanced
Language
English
Large Scale E Commerce Log Processing Pipeline with PySpark & Spark Architecture
E-Commerce Log Processing Pipeline with PySpark is an advanced data engineering capstone project that builds a production-grade PySpark pipeline on …
Level
Advanced
Language
English
IPL Analytics Power BI Dashboard with Cricket Intelligence and DAX Reporting
IPL Analytics Power BI Dashboard is an advanced Business Intelligence capstone project that connects directly to a normalized PostgreSQL database …
Level
Intermediate
Language
English
Full Stack IPL Cricket Analytics Dashboard and Statistics Platform
IPL Analytics Dashboard is an advanced, full-stack capstone project that transforms 17 years of Indian Premier League ball-by-ball match data …
Personal Finance Tracker with Full Stack Bank Statement Analysis
Personal Finance Analyzer is an intermediate, full-stack capstone project that helps individuals understand their spending habits by uploading bank statement …
Retail Banking EDA & Transaction Analytics Platform
Retail Banking EDA & Transaction Analytics Platform is an advanced, full-stack capstone project that takes you through the complete data …
AI Powered Meeting Notes Generator
AI Powered Meeting Notes Generator is an intermediate-level, full-stack capstone project that transforms raw meeting recordings and transcripts into clean, …
Full Stack Service Booking Marketplace with Consultant Subscription Model
BookMyService is a beginner-friendly, full-stack capstone project that simulates a real-world two-sided service marketplace — where users can discover and …
Retail Banking Transaction Processing
Business Overview:Retail banking systems generate large volumes of structured JSON transaction data. However, raw JSON files are not directly usable …
Level
Basic
Time
4.0 hrs
Language
English
Advanced SQL Engineering with Adventure Works: End to End Analytics & Operations
Develop enterprise-grade SQL skills by exploring sales trends, customer segmentation, and product performance in the AdventureWorks dataset.Business Overview:The Advanced SQL …
Level
Advanced
Language
English
Multi Restaurant Food Delivery Analytics and Engineering Capstone Project with SQL
Master real-world SQL by analyzing restaurant performance, delivery efficiency, and customer behavior using production-grade datasets.Business Overview:This SQL Capstone simulates a …
Level
Intermediate
Language
English
Political Donation Dashboard
Business Questions to Solve:Who are the top individual contributors in terms of amount?Which employers are associated with the highest donation …
Level
Intermediate
Language
English
Revenue & Subscription Analytics Dashboard of a music streaming app Using Power BI
Unlock monetization insights and subscription trends using interactive dashboards.Business Overview:Revenue & Subscription Analytics in Power BI equips finance teams, growth …
Level
Intermediate
Language
English
User Behavior & Engagement Analytics Dashboard Using Power BI
Discover platform usage trends and behavioral insights with rich Power BI visualizations.Business Overview:User Behavior & Engagement Analytics Dashboard Using Power …
Level
Intermediate
Language
English
Payment & Financial Analytics
Business Overview:Payment & Financial Analytics Use Cases empower you to explore core financial and payment processing questions using real-world SQL …
Level
Intermediate
Language
English
Revenue and Retention Metrics Using DAX
Gain a comprehensive view of customer value, churn, and recurring revenue with this financial metrics toolkit powered by DAX.Business Overview:Revenue …
Level
Intermediate
Language
English
Engagement Intelligence through Behavioral Analysis in Music Streaming with SQL
Uncover platform stickiness and consumption intent through SQL-powered behavioral signal analysis.Business Overview:Engagement Signals in Music Streaming helps streaming platforms understand …
Level
Intermediate
Language
English
Playlist Dynamics and Curation Behavior Dataset
Understand playlist behavior, track user curation trends, and analyze music discovery patterns with structured, SQL-ready playlist datasets.Business Overview:This dataset captures …
Level
Intermediate
Language
English
Content Performance & Music Catalog Analytics Dataset
Evaluate content performance, measure listener preferences, and optimize music catalogs with structured, SQL-ready analytics datasets.Business Overview:This dataset focuses on catalog …
Level
Intermediate
Language
English
Subscription Lifecycle & Monetization Metrics Dataset
Monitor subscription trends, reduce churn, and optimize revenue streams with structured, SQL-ready subscription datasets.Business Overview:This dataset focuses on subscription activity …
Level
Intermediate
Language
English
User Engagement Dataset
Track listening trends, discover power users, and personalize experiences with structured, SQL-ready user engagement datasets.Business Overview:The User Engagement Dataset captures …
Level
Intermediate
Language
English
Product Performance Dataset
Monitor sales trends, evaluate product success, and optimize retail strategy with structured performance datasetsBusiness Overview:The Product Performance Dataset offers a …
Level
Intermediate
Language
English
Enhanced Product Dataset with Reviews
Understand customer sentiment, track product feedback, and drive intelligent merchandising with review-enriched product datasets.Business Overview:The Enhanced Product Dataset with Reviews …
Level
Intermediate
Language
English
Product Dataset
Track product trends, optimize inventory, and uncover sales insights with structured, SQL-ready datasets.Business Overview:The Product Performance Dataset offers a clean, …
Level
Intermediate
Language
English
Product Performance and Inventory Management
Optimize product strategy and inventory flow with advanced SQL analysis on real e-commerce data.Business Overview:Product Performance and Inventory Management is …
Level
Advanced
Language
English
Customer Analysis and Segmentation
Understand your customers like never before using SQL-driven segmentation and behavior analysis.Business Overview:Customer Analysis and Segmentation empowers you to explore …
Level
Advanced
Language
English
Employment Relationship Analysis
Analyze employer-employee relationships and workplace distribution with real-world SQL exercises.Business Overview:Employment Relationship Analysis helps you uncover how professionals are connected …
Level
Intermediate
Language
English
Basic Professional Data Analysis
Get started with real-world SQL by analyzing professional data quality, completeness, and structure.Business Overview:Basic Professional Data Analysis is the perfect …
Level
Beginner
Language
English
Risk and Compliance Monitoring
Strengthen compliance strategies by uncovering risk trends in professional behavior using real SQL queries.Business Overview:Get hands-on with risk profiling and …
Level
Intermediate
Language
English
Professional Qualifications and Development
Analyze certifications, exam trends, and career growth with SQL in this professional qualifications deep dive.Business Overview:Step into the world of …
Level
Intermediate
Language
English
Registration and Compliance Analysis
Strengthen your SQL expertise with practical registration and compliance analytics for financial professionals.Business Overview:Step into the regulatory core of the …
Level
Advanced
Language
English
Employment and Organizational Analysis
Apply your SQL skills on real-world regulatory datasets to do the analysis of finance professionals.Business Overview:Explore workforce dynamics and organizational …
Level
Advanced
Language
English
Basic Professional Profile Analysis
Apply your SQL skills on real-world regulatory data with this hands-on analysis of financial professionals.Business Overview:Delve into the critical world …
Level
Advanced
Language
English
Restaurant Performance & Menu Optimization
OverviewThis product guides learners through advanced SQL-based analysis focused on restaurant performance and menu optimization. Built on a real-world food …
Level
Advanced
Language
English
Customer Acquisition and Behaviour Analysis
OverviewIn this module, you’ll learn how to use SQL to understand customer behavior and make smart business decisions. You’ll work …
Level
Intermediate
Language
English
Regulatory Compliance Insights with SQL
Transform raw regulatory data into strategic insights with real-world SQL use cases.Business Overview:Regulatory Compliance Insights with SQL provides a practical …
Level
Intermediate
Language
English
Financial Professional Insights and Regulatory KPI Analysis using SQL
Turn regulatory data into actionable intelligence with hands-on SQL analytics for financial oversight.Business Overview:Financial Professional Insights and Regulatory KPI Analysis …
Level
Intermediate
Language
English
Expert Guidance
Similar Services
1:1 help from industry experts who can guide you through labs like this one.
Finding the best experts for you...
No Services Yet
Expert services for this product will appear here once available.
Learner Feedback
Top User Reviews
Loading reviews...
Be the first to review this product!
Please try refreshing the page.