Skip to main content

Data Sources

Premium

In ETL system design interviews, understanding the data sources is essential. Before diving into the design process, it’s crucial to clarify with your interviewer which data sources you’ll be working with, as this will impact the extraction, transformation, and loading phases. Asking about data sources early on not only shows initiative but also ensures you fully understand the requirements and limitations of the system you’re designing.

By clarifying the data sources:

  • You can better align the ETL design with specific data characteristics.
  • You ensure the chosen extraction methods are compatible with each source type.
  • You avoid assumptions that might lead to a design mismatch or performance issues.

A good question to ask the interviewer when discussing operational data stores is:

“Given that these are operational data stores, are there any specific limitations or considerations for extracting data, such as restrictions on the frequency or timing of extractions to avoid putting undue pressure on the operational systems?”

This question shows that you’re aware of the potential impact data extraction can have on the performance of live systems and are thinking proactively about how to minimize disruptions while maintaining efficient ETL processes.

Determine data sources

When discussing data sources with your interviewer, consider asking questions like:

  1. What are the primary data sources in this system? (e.g., databases, files, APIs)
  2. How often is the data updated or generated? (e.g., real-time vs. periodic updates)
  3. Are there any specific access restrictions or compliance requirements?

These questions help you determine the necessary extraction methods, storage, and processing techniques for each source. Remember to jot the answers down for later reference!

Common data sources

In ETL systems, data may originate from various sources, each with different characteristics. Here’s a table of data sources you may encounter in an ETL interview, ordered according to how likely it is that you’ll encounter them.

Data SourceDescriptionTypical Use Case
Relational DatabasesStructured data stored in tables with predefined schemas (e.g., MySQL, PostgreSQL).Operational systems, transactional data.
NoSQL DatabasesSchema-less databases, often document or key-value stores (e.g., MongoDB, Cassandra).Semi-structured data, flexible schemas.
Flat FilesData stored in file formats like CSV, JSON, or XML.Data exports, batch processing.
Log FilesUnstructured or semi-structured logs generated by applications or systems.Monitoring, auditing, analysis.
APIs (REST or SOAP)Data accessed via web services, often in JSON or XML format.Real-time data feeds, external sources.
Streaming PlatformsReal-time data streams from platforms like Apache Kafka or AWS Kinesis.Event-driven architectures, live data.
Cloud Object StorageLarge-scale data storage in cloud environments (e.g., Amazon S3, Google Cloud Storage).Data lakes, raw data storage.
Message QueuesSystems for asynchronous message delivery (e.g., RabbitMQ, ActiveMQ).Event handling, decoupling applications.
Data WarehousesCentralized storage for structured, historical data (e.g., Snowflake, Redshift, BigQuery).Analytical queries, business reporting.
ERP/CRM SystemsEnterprise systems containing operational data (e.g., SAP, Salesforce).Customer, financial, and operational data.
Next Lesson