IntermediatePipeline Architecture · 20 min · 1 challenge
Design a Pipeline: Intermediate
You are 25 minutes into a system design round at a ride-sharing company. The staff engineer across the table has spent 10 minutes on your resume and now turns to the whiteboard.
"Our drivers send location pings every 5 seconds. That is about 2 billion events a day. Product wants a dashboard showing average pickup times by neighborhood, refreshed every hour. The ML team wants those same raw events for ETA model training, the full history with no aggregation. Design the pipeline."
She puts down the marker. There are 2 consumers with fundamentally different shapes, a concrete volume, and no mention of latency tolerance beyond "every hour" for one of them. The obvious move is to start sketching Kafka into Spark into Snowflake, and it is the move that caps the score at "hire," because every box drawn before requirements are confirmed is a box defended without knowing what it defends.
What she is testing is whether the design addresses each layer's tradeoffs with numbers before committing to a single technology.
What you will be able to do
Decompose any pipeline prompt into 5 technical layers in the first 60 seconds
Choose between file, API, and CDC ingestion by matching pattern to source constraints
Justify storage layout, file format, and partitioning with query cost arithmetic
Design separate serving paths for consumers with different freshness and shape needs
Name failure modes, idempotency keys, and monitoring metrics before asked
Ingestion Patterns
Choose an ingestion pattern and defend it
Three ingestion patterns dominate: file drops, API pulls, and Change Data Capture. Each carries a different latency floor and a different failure shape. The choice between them is the first design decision, and the interviewer expects it justified by the source system's constraints rather than by familiarity with a tool.
A 5-layer pipeline from source to consumer
File Drops
A source system exports files to cloud storage on a schedule, and the pipeline picks them up. CSV and JSON from legacy vendors, Parquet from modern ones. This is the default for any source you do not control: third-party data feeds, SaaS exports, partner integrations. Latency is measured in hours, which is acceptable when the requirement is a morning report or a daily training run. The risk worth naming unprompted is duplicate delivery. A vendor re-uploads yesterday's file, or a retry fires twice, and without deduplication the daily counts double.
TIP
Say 'I track processed files in a manifest table keyed on filename and SHA-256 checksum, so a duplicate upload is idempotently skipped.' This is an idempotency signal interviewers score on mid-to-senior rubrics.
API Pulls
When the source is a SaaS platform like Salesforce or Stripe, you pull data through REST or GraphQL endpoints. The trap is a naive full refresh. Pulling 10 million records every hour when only 500 changed wastes compute and collides with rate limits within a week. The fix is a high-water mark: store the latest modified_at timestamp from the last successful run and request only records newer than it. Combine this with cursor-based pagination so a restart resumes from the last page rather than the beginning of the result set.
Rate limits are a production concern that interviewers test directly. Stripe allows 100 requests per second in live mode. At 100 records per page, extracting 5 million records takes 50,000 requests, finishing in about 8 minutes at full throughput. An exponential backoff with jitter handles transient 429 responses. Name these numbers, because they show you have run this rather than read about it.
Change Data Capture
CDC reads the database transaction log (binlog in MySQL, WAL in Postgres) and streams row-level changes as insert, update, and delete events. Debezium on Kafka Connect, Fivetran, and AWS DMS all implement this pattern. CDC is the strongest choice for replicating operational databases: sub-second latency, minimal load on the source, and a complete change history including deletes that a timestamp-based extract would miss. The cost is operational. A schema change in the source can break the CDC connector, and an unmonitored replication slot in Postgres keeps WAL segments alive until the disk fills.
2B/day
Events from a ride-sharing location service, requiring streaming or micro-batch ingestion
~23K/sec
Peak event rate at 2 billion per day, sustained across partitioned Kafka consumers
✓Batch File Ingestion
- Simple to implement and straightforward to debug
- Works with any source that can produce an export
- Latency measured in hours; fine for daily reporting
- Duplicate delivery requires manifest-table deduplication
✗Change Data Capture
- Sub-second latency from source change to lake
- Captures deletes that timestamp-based extracts miss
- Minimal read load on the source database
- Schema changes in the source can break the connector
Every pipeline prompt provides at least 2 inputs that determine the ingestion pattern: the volume and the latency requirement. 1 GB per day from a vendor feed is a file drop on a cron. 2 billion events per day from a mobile app needs a streaming platform with partitioned consumers. Ask for both numbers before committing. The interview answer is then one sentence: 'For this use case I would choose CDC because the risk dashboard requires hourly freshness and CDC delivers sub-minute latency with minimal source load.' Anchor the pattern to the numbers the interviewer gave you, because a pattern named without a justification is a guess.
Common Follow-Up Probes
- ▸'What if the vendor sends the same file twice?' Tests whether you have an idempotency strategy for file ingestion.
- ▸'The API rate limit is 100 req/min and you need 5M records.' Tests pagination, backoff, and high-water mark strategy.
- ▸'The source Postgres has 200 tables. How do you replicate them?' Expects CDC, not 200 scheduled API calls.
Storage Architecture
Justify a storage layout with query cost math
Saying 'I would store it in S3' carries no information. The interviewer needs to hear the organizational model, the file format, the table format, and the partitioning key, because each of those decisions carries a cost that scales with the data.
Medallion Architecture
Medallion organizes the lake into 3 quality tiers. Bronze holds raw data exactly as ingested, byte-for-byte, including duplicates and malformed records. Silver holds cleaned, typed, and deduplicated records. Gold holds business-ready aggregates shaped for a specific consumer. The critical property is that bronze is immutable. If a transformation bug corrupts gold, the fix is to re-run the transform on bronze without re-extracting from the source. That rebuild capability is the entire reason the layers exist, and naming it unprompted tells the interviewer you have debugged a production pipeline rather than only designed one.
Each medallion layer serves a different consumer at a different quality level
| Layer | Contents | Owner | Consumers |
|---|---|---|---|
| Bronze | Raw JSON events with duplicates | Ingestion pipeline | Data engineers only |
| Silver | Parsed, typed, deduplicated events | Spark / dbt models | Engineers and scientists |
| Gold | Daily active users by country | dbt models | Analysts and dashboards |
File Format
Parquet and Avro are the 2 formats worth discussing. Parquet is columnar: it stores each column contiguously, so a query that reads 3 columns out of 200 skips 98.5% of the I/O. Avro is row-oriented and self-describing, carrying its schema in the file header. The rule: Parquet for analytics (read-heavy, column-selective), Avro for streaming and CDC (write-heavy, schema-evolution-friendly). A 1 TB JSON dataset compresses to roughly 100 GB in Parquet with Snappy, and query engines skip unneeded columns entirely.
✓Parquet
- Columnar: reads only the columns the query needs
- 10x compression over JSON with Snappy or Zstd
- Preferred by Spark, BigQuery, and Athena for analytics
- Schema must match at write time; no built-in evolution
✗Avro
- Row-oriented: efficient for write-heavy append workloads
- Schema in file header enables safe schema evolution
- Preferred by Kafka and CDC connectors for streaming
- Smaller per-record overhead for high-throughput writes
State the format choice and the reason in one sentence: 'I would land raw events as Avro in bronze for schema flexibility, then convert to Parquet at the silver layer for columnar analytics.' That sequence combines the strengths of both. Avro handles the streaming write path where schemas evolve. Parquet handles the analytical read path where column pruning cuts I/O by orders of magnitude.
Table Formats
Iceberg, Delta Lake, and Hudi add ACID transactions, time travel, and schema evolution on top of Parquet files in object storage. Without a table format, a Parquet data lake is a folder of files with no atomicity: a writer crash mid-job leaves half-written partitions that downstream consumers read as valid. Iceberg and Delta solve this with a metadata layer that tracks which files belong to each snapshot. Name one by name: 'I would use Iceberg on S3' carries more signal than 'I would store data in the lake,' because it shows you understand the atomicity problem that raw Parquet cannot solve.
The follow-up interviewers use is 'what happens if a write fails halfway through?' With raw Parquet on S3, consumers see partial data until someone cleans up the orphaned files manually. With Iceberg, the commit never becomes visible because the metadata pointer was never advanced. That distinction shows you understand the failure mode rather than the feature list.
Partitioning Strategy
Partitioning determines whether a query scans 10 TB or 10 GB. Partition by the column that appears in the WHERE clause of the most common query, almost always a date column. If analysts query by date range and country, partition by event_date and cluster by country within each partition. State the numbers: a table with 365 daily partitions means a query for a single day scans 1/365th of the data. In BigQuery, scanning 10 TB costs $50 at $5 per TB; scanning the 27 GB in one daily partition costs $0.14.
Interviewers test partitioning with a volume follow-up: 'The table gets 500 million rows per day across 200 countries, and analysts query one country within one day.' The answer is to partition by date (isolating the daily folder) and cluster by country within that partition (allowing the engine to skip country blocks without reading them). Spark's bucketing and BigQuery's clustering both implement this second-level optimization. Name the mechanism, because 'partitioned by date' is the minimum and 'clustered by country within the partition' is what scores.
TIP
Over-partitioning creates a small-files problem. A table partitioned by user_id with 50 million distinct users produces 50 million directories, each holding tiny files that overwhelm the metastore. Partition by low-cardinality columns (date, region) and cluster by high-cardinality ones (user_id, country).
$0.14
Cost of a partitioned BigQuery query scanning one day out of a 10 TB table
10x
Compression ratio of Snappy Parquet over raw JSON on the same dataset
Numbers Worth Memorizing
- ▸Target 128 MB to 1 GB per Parquet file for optimal S3 read throughput
- ▸Snappy Parquet compresses JSON roughly 10x; Zstd achieves 12 to 15x
- ▸$5 per TB scanned in BigQuery; partitioning routinely cuts cost by 99%
- ▸Iceberg metadata overhead: under 1% of data size for tables below 100 TB
Transformation Strategy
Argue ELT over ETL with reprocessing math
Where the transformation runs and how much data each run processes are the 2 decisions the interviewer probes most heavily. The answers reveal whether you understand data modeling rather than data movement.
ELT Is the Modern Default
ETL transforms data before loading it into the warehouse. ELT loads raw data first, then transforms it inside the warehouse using SQL or dbt. The real difference is where raw data lives after ingestion. In ETL, the warehouse holds only processed data, so reprocessing a historical period requires re-extracting from the source. In ELT, the raw data sits in the bronze layer, so reprocessing means re-running the transform on data already stored. Storage on S3 costs roughly $0.023 per GB per month. Re-extracting 1 TB of historical data from a SaaS API costs hours of engineering time, burns rate-limit budget, and sometimes is impossible because the source no longer retains old records. The economic argument for ELT is that retaining raw data is cheaper than re-acquiring it.
✓ETL
- Transforms data before loading into the warehouse
- Lower warehouse storage: only processed data lands
- Reprocessing requires re-extraction from the source
- Common in legacy Informatica and SSIS architectures
✗ELT
- Loads raw data first, transforms inside the warehouse
- Higher storage cost, but raw data is always available
- Reprocessing is a re-run, not a re-extraction
- Standard in modern Snowflake, BigQuery, Databricks stacks
Say: 'I would land raw data in the lake and transform in the warehouse, because storage at $0.023 per GB per month is cheaper than re-extracting from the source, which may be rate-limited or may no longer retain historical records.' That sentence carries 3 facts and is the kind of density interviewers reward.
Full Refresh vs Incremental
A full-refresh transform reads the entire source and rewrites the entire target on every run. An incremental transform reads only new or changed records since the last run and merges them into the target. Full refresh is simpler and guarantees correctness because there is no state to manage, but it becomes expensive as the table grows. A 10 GB table that refreshes daily costs 10 GB of compute per run. An incremental transform that processes the day's 100 MB of changes costs 1% as much. The crossover where incremental becomes worth the complexity is roughly when full refresh exceeds the compute budget or the SLA window.
MERGE (or INSERT ... ON CONFLICT in Postgres) is the mechanism that makes incremental transforms idempotent. If the same record arrives twice, the matched branch updates the existing row rather than inserting a duplicate. Name this explicitly: 'The incremental load is idempotent because the MERGE deduplicates on event_id, so a retry produces no duplicates.' Interviewers listen for the word 'idempotent' here, not only in the ingestion layer.
The choice between full refresh and incremental is also a maturity signal. Naming full refresh alone shows safety awareness. Naming incremental alone shows efficiency awareness. The answer that scores is naming both and stating the condition: 'Tables under a few GB get full refresh because the simplicity is worth the compute. Above that threshold, incremental with MERGE, because the cost of scanning the full table on every run exceeds the complexity cost of maintaining a high-water mark.'
Late-Arriving Data
This is the most common follow-up on transformation. The interviewer says: 'A mobile device was offline for 3 hours and sends its events at 4 PM with timestamps from 1 PM. Your hourly pipeline already processed the 1 PM partition. What happens?' A weak answer is 'reprocess the partition.' A strong answer names the mechanism: a lookback window that re-runs the transform for the current partition plus the previous N partitions on every run. With N set to 6, the hourly pipeline always re-transforms the last 6 hours, so events arriving up to 6 hours late are absorbed automatically. Events arriving later than the window route to a dead letter queue or a manual backfill.
TIP
Name the window with a number: 'I set a lookback of 6 hours, so the hourly transform always reprocesses the last 6 partitions. That covers 99.7% of late arrivals based on the latency distribution.' The percentage does not need to be exact. What matters is that a measurement exists rather than a guess.
Schema Evolution
Sources change schemas without warning. A Postgres table gains a column, a vendor renames a JSON field, an API response adds a nested object. The pipeline needs to handle additive changes automatically and alert on breaking changes. Iceberg supports column addition, renaming, and reordering without rewriting data files. Delta Lake allows additions and type widening. State the policy: 'Additive schema changes propagate automatically through bronze and silver. Breaking changes halt the pipeline and alert the on-call engineer, because a silent column rename is worse than a stopped pipeline.' That sentence shows a deliberate strategy rather than a hope that schemas stay stable.
Transformation Follow-Ups That Score
- ▸'The transform takes longer than its schedule interval. What now?' Tests overlap safety and idempotency under concurrent runs.
- ▸'A business rule changed. Restate 6 months of data.' Tests whether ELT retained raw data for reprocessing.
- ▸'Where do you validate: before or after the transform?' Tests quality-gate placement in the DAG.
Serving the Consumer
Design serving for distinct consumers
Most candidates spend 25 minutes on ingestion and transformation, then say 'analysts query it in Snowflake.' That sentence is where the score shifts, because how data is consumed drives every upstream decision. The consumer's query pattern, latency tolerance, and freshness requirement are inputs to the pipeline design, not afterthoughts. Name the consumer before naming the table.
Consumer-Driven Design
Different consumers need fundamentally different data shapes. Analysts writing dashboard SQL need denormalized, pre-aggregated gold tables partitioned by date so the BI tool scans a single partition. Data scientists building ML features need wide tables with point-in-time-correct snapshots, because training on data that includes future information introduces leakage. Reverse ETL pushes pipeline output back to SaaS tools like Braze or Salesforce. These consumers need narrow tables keyed on user_id, refreshed frequently, and shaped to match the destination API's schema. Each consumer gets its own gold table built from the shared silver layer, not a different query on the same table.
| Consumer | Data Shape | Freshness | Example |
|---|---|---|---|
| BI Dashboards | Denormalized aggregates | Hourly | daily_active_users_by_country |
| ML Training | Wide point-in-time snapshots | Daily batch | user_features_snapshot |
| Reverse ETL | Narrow user-keyed rows | Minutes | user_segments_for_braze |
| Ad Hoc Analysis | Silver-layer detail rows | Varies | silver.parsed_events |
TIP
When the prompt names 2 consumers with different needs, say 'I would build 2 gold tables: one denormalized aggregate for the dashboard, one wide snapshot for the ML team. They share the same silver layer but diverge at gold.' That proves you know sharing one table across conflicting access patterns degrades performance for both.
Reverse ETL has become a standard interview topic since 2024. Census, Hightouch, and warehouse-native approaches all implement the pattern. The key detail is that the reverse ETL stage needs its own error handling isolated from the forward pipeline. A failure to sync user segments to Braze should not block the dashboard refresh that other consumers depend on. Design the reverse path as a separate DAG branch from the same gold table, with its own retry policy and its own alerting.
Pre-Computed Tables vs Materialized Views
When a dashboard needs sub-second response on an aggregate that would otherwise scan 500 million rows, you have 2 options. A materialized view lets the warehouse compute and cache the result automatically on a configured refresh schedule. A pre-computed table is a pipeline stage that writes the aggregated output to a dedicated table, with validation between the computation and the exposure. Materialized views are simpler. Pre-computed tables give you a quality gate: validate that revenue is non-negative and that row counts are within 10% of yesterday before exposing the numbers to executives. Say: 'I prefer pre-computed tables for executive dashboards because I run quality checks before the numbers are visible.' That shows you think about data trust, not data movement.
✓Materialized Views
- Warehouse manages refresh on a configured schedule
- Simple to create with a single DDL statement
- No quality gate between computation and exposure
- Refresh timing controlled by warehouse settings
✗Pre-Computed Tables
- Pipeline manages refresh as an explicit DAG stage
- Quality checks run before data is exposed to users
- More operational overhead to build and maintain
- Refresh timing matches the pipeline SLA precisely
Freshness and SLAs
Every serving layer has an implicit SLA, and naming it explicitly is what separates the strong answer. 'The gold table must be refreshed by 6 AM UTC so the US executive standup at 9 AM Eastern has current numbers.' 'The ML feature table must be ready by 11 PM so the nightly training run at midnight uses today's data.' Each SLA flows backward through the pipeline: if gold must be ready by 6 AM, the silver transform must finish by 5:30, and ingestion must complete by 5:00. Stating this backward chain proves you think about the pipeline as a system with a deadline rather than a sequence of steps that run whenever resources are available.
When Batch Falls Short
The follow-up that catches candidates is 'a downstream team needs near-real-time data, but your pipeline is batch.' The answer is not to convert the entire pipeline to streaming. A Lambda architecture adds a streaming path alongside the existing batch pipeline: the streaming layer produces approximate, low-latency views while the batch layer produces accurate, complete aggregates on schedule. The batch output eventually replaces the streaming approximation, so the fast path sacrifices accuracy for speed and the slow path provides the corrective truth. Name the tradeoff: 'The streaming path is less accurate but sub-minute. The batch path is the source of truth and corrects the streaming output every hour.' That sentence demonstrates you understand the cost of real-time data rather than treating it as a free upgrade.
Serving Layer Probes
- ▸'How would analysts query this?' Tests whether the gold table was shaped for the consumer's access pattern.
- ▸'The dashboard takes 30 seconds. Fix it.' Expects pre-aggregation, materialized views, or partitioning.
- ▸'An ML engineer needs the same data but in a different shape.' Tests whether you build a separate gold table.
- ▸'What if the downstream team needs real-time but you are batch?' Wants Lambda architecture or a streaming sidecar.
Reliability Under Failure
Name every failure mode before asked
A pipeline that works on a clean day is not what the interview is scoring. The probes in this layer test what happens when a source sends duplicates, a transform fails mid-run, the warehouse is unreachable for 20 minutes, or a deployment introduces a bug that corrupts 3 days of output. Reliability separates 'hire' from 'strong hire,' because it requires operational experience that a whiteboard cannot fake.
Idempotency
An idempotent pipeline produces the same output whether it runs once or 5 times on the same input. This is the single most important property in pipeline design, because every other reliability guarantee depends on it. Retries are safe only if the write is idempotent. Backfills are safe only if re-running a date range does not duplicate data. The implementation is straightforward: use MERGE or INSERT ... ON CONFLICT keyed on a natural or deterministic key, so duplicate inputs produce updates rather than inserts.
Name the key and the guarantee in one sentence: 'The pipeline is idempotent on event_id, so a retry or a backfill for the same date range produces no duplicates.' That sentence, stated unprompted, is worth more than the preceding 10 minutes of architecture, because it proves you have thought about what happens when the pipeline runs twice.
The subtlety that catches candidates is that idempotency requires a stable key. If the pipeline assigns a UUID at ingestion time, re-ingesting the same source record generates a new UUID, and the MERGE treats it as a new row. The fix is to derive the deduplication key from the source record's natural fields through a deterministic hash, so the same source record always maps to the same key regardless of when or how many times it is ingested.
Retries and Dead Letter Queues
Every pipeline stage fails. The question is what happens next. A retry with exponential backoff handles transient issues: network timeouts, API 503 responses, temporary warehouse unavailability. 3 retries with delays of 1 second, 4 seconds, and 16 seconds cover most transient failures. Records that exhaust all retries route to a dead letter queue: a separate storage location where failed records accumulate for investigation without blocking the healthy 99.99% of the pipeline. A pipeline without a DLQ halts entirely when one malformed record arrives. A pipeline with a DLQ processes what it can and surfaces the exceptions.
Failed records route to DLQ with alerting rather than halting the pipeline
Say 'records that fail 3 retries route to a dead letter queue on S3, and an alert fires when the DLQ is non-empty' before the interviewer asks about failure handling. Nobody who has only designed on a whiteboard mentions the DLQ, because the failure path is invisible until you have operated a pipeline in production and discovered that one unparseable record at 3 AM stopped the entire batch.
99.99%
Records a well-designed pipeline processes successfully per run
3 retries
Standard count before routing to DLQ, with exponential backoff
Orchestration
A production pipeline is a directed acyclic graph of tasks with dependencies, retries, and alerting. Airflow and Dagster are the standard tools. The concept interviewers test is dependency management: if ingestion fails, the transform must not run on stale data. If the transform fails, the validation task should still execute to capture what the partial output looks like. If validation fails, the publish step must not expose bad data to consumers. State the dependency chain explicitly.
Interviewers test orchestration by adding a constraint: 'The pipeline runs 4 times a day but the source refreshes only twice.' The answer is to separate the ingestion schedule from the transformation schedule. Ingestion runs on the source's cadence. Transformation runs on the consumer's cadence but checks whether new data has arrived since the last run, short-circuiting if not. In Airflow this is a sensor task; in Dagster a freshness policy. The separation prevents wasted compute while meeting the downstream SLA.
The most important property of the DAG is recoverability. If the pipeline fails at 3 AM, the on-call engineer should be able to clear the failed task and restart it without side effects. This is only possible when every task is idempotent, because restarting a non-idempotent task from the point of failure either duplicates data or skips records. Idempotency at the task level and DAG-level retry are the same design decision. Naming that connection shows the interviewer you see reliability as a system property rather than a per-task checkbox.
Monitoring and Data Quality
A pipeline without monitoring fails silently. 3 categories are worth naming. Operational metrics: task duration, success rate, records processed per run. Data quality metrics: null rates per column, row count deltas between consecutive runs, schema conformance between bronze and silver. Freshness metrics: minutes since the last successful run, minutes since the last record landed in the gold table. Great Expectations, dbt tests, and Monte Carlo implement the quality layer. Name at least one tool, because the tool choice shows you have evaluated the options rather than hand-rolling validation scripts.
Task duration per stage, with alerting on 3x regression from baseline
Row count delta between runs, alert when change exceeds 5%
Null rate per column, alert when it exceeds the historical baseline
Pipeline freshness: minutes since last record in the gold table
DLQ depth: number of records awaiting manual investigation
TIP
Open with monitoring before the interviewer asks. Saying 'the pipeline publishes 3 operational metrics to CloudWatch and alerts on duration regression' signals operational maturity. Waiting for the interviewer to ask 'how do you know if this breaks?' signals that reliability was an afterthought.
✓Do
- Make every write idempotent on a natural or deterministic key
- Route failed records to a dead letter queue with alerting
- State the freshness SLA and trace it backward through each stage
- Name monitoring metrics before the interviewer asks about failure
- Use MERGE or ON CONFLICT for all incremental loads
✗Don't
- Jump to tools before asking about volume, latency, and consumers
- Say 'Kafka' without naming the latency requirement it satisfies
- Assume exactly-once delivery without naming the mechanism
- Treat monitoring and alerting as a nice-to-have afterthought
- Build one gold table for consumers with different access patterns
❯❯❯PUTTING IT ALL TOGETHER
> A payments company processes 40 million card authorizations daily from 3 processors. The risk team needs hourly decline-rate dashboards by card issuer. The ML team needs the raw transaction history for fraud model training. Regulatory audit requires 7-year retention of unmodified source records.
Ingestion uses CDC from each processor's database because the risk dashboard requires hourly freshness and CDC delivers sub-minute latency with minimal source impact.
Bronze stores raw authorization events in Iceberg on S3, satisfying the 7-year audit retention without re-extraction, while silver deduplicates across the 3 processors on authorization_id.
Two gold tables serve the 2 consumers: one pre-aggregated by issuer and hour for the risk dashboard, one wide snapshot for the ML fraud model, both built from the shared silver layer.
Every write is idempotent on authorization_id, so a processor re-sending a batch or a pipeline retry does not inflate decline rates. Failed records route to a DLQ with PagerDuty alerting.
The 6 AM UTC dashboard SLA traces backward: gold refresh by 5:45, silver transform by 5:15, CDC lag under 5 minutes, monitored by a freshness alert that fires when the last gold record exceeds 90 minutes.
KEY TAKEAWAYS
Decompose every pipeline prompt into 5 layers before touching architecture: ingestion, storage, transformation, serving, reliability.
Choose the ingestion pattern from the source's constraints. File drops, API pulls, and CDC each solve a different latency and volume shape.
ELT with medallion is the modern default. Retaining raw data in bronze costs $0.023/GB/month; re-extracting from the source costs engineering hours.
Different consumers need different gold tables. Name the consumer, state their SLA, and trace the deadline backward through every pipeline stage.
Idempotency is the foundation. Retries, backfills, and every reliability guarantee depend on writes that produce the same result when repeated.
Structure a pipeline answer so every layer carries a tradeoff the interviewer can probe
- Category
- Pipeline Architecture
- Difficulty
- intermediate
- Duration
- 20 minutes
- Challenges
- 1 hands-on challenges
Topics covered: Ingestion Patterns, Storage Architecture, Transformation Strategy, Serving the Consumer, Reliability Under Failure
Lesson Sections
- Ingestion Patterns (concepts: paFileIngestion, paApiIngestion, paCdc, paBatchVsStreaming)
Three ingestion patterns dominate: file drops, API pulls, and Change Data Capture. Each carries a different latency floor and a different failure shape. The choice between them is the first design decision, and the interviewer expects it justified by the source system's constraints rather than by familiarity with a tool. File Drops A source system exports files to cloud storage on a schedule, and the pipeline picks them up. CSV and JSON from legacy vendors, Parquet from modern ones. This is the
- Storage Architecture (concepts: paMedallion, paPartitioning, paColumnarVsRow, paTableFormats, paSmallFiles, paDataLake)
Saying 'I would store it in S3' carries no information. The interviewer needs to hear the organizational model, the file format, the table format, and the partitioning key, because each of those decisions carries a cost that scales with the data. Medallion Architecture Medallion organizes the lake into 3 quality tiers. Bronze holds raw data exactly as ingested, byte-for-byte, including duplicates and malformed records. Silver holds cleaned, typed, and deduplicated records. Gold holds business-re
- Transformation Strategy (concepts: paEltVsEtl, paFullVsIncremental, paLateData, paSchemaEvolution, paDeduplication)
Where the transformation runs and how much data each run processes are the 2 decisions the interviewer probes most heavily. The answers reveal whether you understand data modeling rather than data movement. ELT Is the Modern Default ETL transforms data before loading it into the warehouse. ELT loads raw data first, then transforms it inside the warehouse using SQL or dbt. The real difference is where raw data lives after ingestion. In ETL, the warehouse holds only processed data, so reprocessing
- Serving the Consumer (concepts: paBatchProcessing, paStreamProcessing, paCostOptimization, paLambdaArch)
Most candidates spend 25 minutes on ingestion and transformation, then say 'analysts query it in Snowflake.' That sentence is where the score shifts, because how data is consumed drives every upstream decision. The consumer's query pattern, latency tolerance, and freshness requirement are inputs to the pipeline design, not afterthoughts. Name the consumer before naming the table. Consumer-Driven Design Different consumers need fundamentally different data shapes. Analysts writing dashboard SQL n
- Reliability Under Failure (concepts: paIdempotency, paRetryHandling, paDeadLetterQueue, paDagOrchestration, paMonitoring, paDataQuality)
A pipeline that works on a clean day is not what the interview is scoring. The probes in this layer test what happens when a source sends duplicates, a transform fails mid-run, the warehouse is unreachable for 20 minutes, or a deployment introduces a bug that corrupts 3 days of output. Reliability separates 'hire' from 'strong hire,' because it requires operational experience that a whiteboard cannot fake. Idempotency An idempotent pipeline produces the same output whether it runs once or 5 time