Describe a data pipeline you built or would build, from source to dashboard.

What the interviewer wants to hear

• Names the sources and how data is extracted
• Explains loading into a warehouse or lake
• Describes transformation layers such as staging and marts
• Mentions orchestration and scheduling
• Mentions tests and monitoring

Model answer

For an e-commerce company I would pull orders and customers from the PostgreSQL app database using change data capture, and marketing spend from ad platform APIs using a managed connector. Raw data lands in the warehouse, say Snowflake, in a raw schema without changes. Then dbt builds staging models that rename columns, cast types and deduplicate, followed by mart models such as fct_orders and dim_customers in a star schema. Airflow schedules the whole flow: extract, then dbt run, then dbt test. Tests check primary key uniqueness, not-null columns, accepted values and freshness. If a test fails, the dashboard refresh is blocked and the team gets a Slack alert. Finally, Power BI or Looker reads from the marts. I document models in dbt so analysts can see lineage and definitions.

More easy Data Engineer questions

• What is the difference between an OLTP database and an OLAP data warehouse?
• Explain a star schema and why it is used in data warehouses.
• What data quality checks would you add to a pipeline?
• Why use an orchestration tool like Airflow instead of cron jobs?
• What is a slowly changing dimension, and how do SCD Types 1, 2 and 3 differ?
• Why would you store analytics data as Parquet rather than CSV or JSON?
• What is change data capture (CDC), and why do data teams use it?
• Explain Kafka topics, partitions, offsets and consumer groups to a new teammate.
• What does dbt do in a modern data stack, and what are its main features?