Greenfield Build for Six Sources

A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain
Pipeline Design
Difficulty
hard
Seniority
L5

Problem

Six sources (a CRM, a card-payments feed, a product database, a budget spreadsheet and two weekly file drops) land about 750K records a day on a new Databricks platform, each declared in config rather than code. Design the end-to-end platform that refines them through medallion layers into one semantic layer for the analysts' dashboards.

Infrastructure as code. Meaning as a service.

Business requirements

Worked solution and explanation

What this really is

This is a question about ownership dressed up as a source-ingestion exercise. Six connectors into Databricks is the easy half. The real test is whether you separate source connectivity from KPI meaning. Config-driven ingestion feeds a medallion lakehouse. An orchestrator owns the clock. A semantic layer owns each metric's definition. The platform, not the analyst, owns column access. Most candidates draw six arrows into the warehouse and stop. The result is three MRR numbers in three meetings, a finance analyst reading raw card data, and blank 8am dashboards with nobody paged.

Walk the requirements

Step 1: Make the orchestrator the spine

Every ingest and transform is an Airflow task with declared dependencies, sized so the slowest source still lands before 7:30am. A landing-time check pages on-call while there is still time to act, not when an analyst sees a blank chart.

Step 2: Onboard sources by config, then refine in layers

One ingest job reads a config entry per source, so a seventh source is a new file, not new code. Raw rows land in bronze as received. Silver cleans, types and dedupes. Gold rolls up the business tables the metrics read.

Step 3: Publish each KPI once

The semantic layer holds the one canonical SQL for MRR and Net Revenue Retention, version-controlled and reviewed. Every dashboard reads the named metric from it. A new variant ships as a pull request, not a private query.

Step 4: Route every read through governance

The column policy sits between the serving layer and the dashboards. Finance gets masked card fields no matter which table or query they use.

The reference design

Airflow schedules ingest and both transform stages and raises the at-risk alert before 7:30am. Gold lands in the Databricks warehouse, `semantic_layer_one_definition_per_kpi` publishes each KPI once, and `column_access_governance` enforces column access before any dashboard reads.
  • six_sources (source)
  • orchestration_dag (transform, Airflow); alerting: page if a source lands late for 7:30am
  • at_risk_alerts (consumer, alerting)
  • config_driven_ingest (transform, Spark)
  • bronze_tables (storage, Delta Lake)
  • silver_cleaning (transform, dbt)
  • silver_tables (storage, Delta Lake)
  • gold_rollups (transform, dbt)
  • gold_warehouse (storage, Databricks)
  • semantic_layer_one_definition_per_kpi (transform, dbt)
  • column_access_governance (quality gate, custom)
  • analyst_dashboards (consumer, dashboard)
  • six_sources -> config_driven_ingest
  • orchestration_dag -> config_driven_ingest
  • orchestration_dag -> silver_cleaning
  • orchestration_dag -> gold_rollups
  • orchestration_dag -> at_risk_alerts
  • config_driven_ingest -> bronze_tables
  • bronze_tables -> silver_cleaning
  • silver_cleaning -> silver_tables
  • silver_tables -> gold_rollups
  • gold_rollups -> gold_warehouse
  • gold_warehouse -> semantic_layer_one_definition_per_kpi
  • semantic_layer_one_definition_per_kpi -> column_access_governance
  • column_access_governance -> analyst_dashboards

Common follow-up questions