10+ Atal Indore City Transport Services Interview Questions and Answers

Improving query performance by optimizing indexes, using proper data types, and minimizing data retrieval.

• Optimize indexes on frequently queried columns

• Use proper data types to reduce storage space and improve query speed

• Minimize data retrieval by only selecting necessary columns

• Avoid using SELECT * in queries

• Use query execution plans to identify bottlenecks and optimize accordingly

Normal cluster is used for interactive workloads while job cluster is used for batch processing in Databricks.

• Normal cluster is used for ad-hoc queries and exploratory data analysis.

• Job cluster is used for running scheduled jobs and batch processing tasks.

• Normal cluster is terminated after a period of inactivity, while job cluster is terminated after the job completes.

• Normal cluster is more cost-effective for short-lived workloads, while job cluster is more cost-effective for...read more

To read a file in Databricks, you can use the Databricks File System (DBFS) or Spark APIs.

• Use dbutils.fs.ls('dbfs:/path/to/file') to list files in DBFS

• Use spark.read.format('csv').load('dbfs:/path/to/file') to read a CSV file

• Use spark.read.format('parquet').load('dbfs:/path/to/file') to read a Parquet file

To create a pipeline in ADF, you can use the Azure Data Factory UI or code-based approach.

• Use Azure Data Factory UI to visually create and manage pipelines

• Use code-based approach with JSON to define pipelines and activities

• Add activities such as data movement, data transformation, and data processing to the pipeline

• Set up triggers and schedules for the pipeline to run automatically

Activities in pipelines include data extraction, transformation, loading, and monitoring.

• Data extraction: Retrieving data from various sources such as databases, APIs, and files.

• Data transformation: Cleaning, filtering, and structuring data for analysis.

• Data loading: Loading processed data into a data warehouse or database.

• Monitoring: Tracking the performance and health of the pipeline to ensure data quality and reliability.

Slowly changing dimensions refer to data warehouse dimensions that change slowly over time.

• SCDs are used to track historical changes in data over time.

• There are three types of SCDs - Type 1, Type 2, and Type 3.

• Type 1 SCDs overwrite old data with new data, Type 2 creates new records for changes, and Type 3 maintains both old and new data in separate columns.

• Example: A customer's address changing would be a Type 2 SCD.

• Example: A product's price changing would be a Type 1 SCD.

To create a workflow in Databricks, use Databricks Jobs or Databricks Notebooks with scheduling capabilities.

• Use Databricks Jobs to create and schedule workflows in Databricks.

• Utilize Databricks Notebooks to define the workflow steps and dependencies.

• Leverage Databricks Jobs API for programmatic workflow creation and management.

• Use Databricks Jobs UI to visually design and schedule workflows.

• Integrate with Databricks Delta for optimized data processing and storage.

getmetadata is used to retrieve metadata information about a dataset or data source.

• getmetadata can provide information about the structure, format, and properties of the data.

• It can be used to understand the data schema, column names, data types, and any constraints or relationships.

• This information is helpful for data engineers to properly process, transform, and analyze the data.

• For example, getmetadata can be used in data pipelines to dynamically adjust processing logic b...read more

SCD type2 table is used to track historical changes in data by creating new records for each change.

• Contains current and historical data

• New records are created for each change

• Includes effective start and end dates for each record

• Requires additional columns like surrogate keys and version numbers

• Used for slowly changing dimensions in data warehousing

Spark performance problems can arise due to inefficient code, data skew, resource constraints, and improper configuration.

• Inefficient code can lead to slow performance, such as using collect() on large datasets.

• Data skew can cause uneven distribution of data across partitions, impacting processing time.

• Resource constraints like insufficient memory or CPU can result in slow Spark jobs.

• Improper configuration settings, such as too few executors or memory allocation, can hinder p...read more

Triggers in databases are special stored procedures that are automatically executed when certain events occur.

• Types of triggers include: DML triggers (for INSERT, UPDATE, DELETE operations), DDL triggers (for CREATE, ALTER, DROP operations), and logon triggers.

• Triggers can be classified as row-level triggers (executed once for each row affected by the triggering event) or statement-level triggers (executed once for each triggering event).

• Examples of triggers include: BEFORE I...read more

List is mutable, tuple is immutable in Python.

• List can be modified after creation, tuple cannot be modified.

• List uses square brackets [], tuple uses parentheses ().

• Lists are used for collections of items that may need to be changed, tuples are used for fixed collections of items.

• Example: list_example = [1, 2, 3], tuple_example = (4, 5, 6)

Datalake 1 and Datalake 2 are both storage systems for big data, but they may differ in terms of architecture, scalability, and use cases.

• Datalake 1 may use a Hadoop-based architecture while Datalake 2 may use a cloud-based architecture like AWS S3 or Azure Data Lake Storage.

• Datalake 1 may be more suitable for on-premise data storage and processing, while Datalake 2 may offer better scalability and flexibility for cloud-based environments.

• Datalake 1 may be more cost-effective...read more

Star schema is denormalized with one central fact table surrounded by dimension tables, while snowflake schema is normalized with multiple related dimension tables.

• Star schema is easier to understand and query due to denormalization.

• Snowflake schema saves storage space by normalizing data.

• Star schema is better for data warehousing and OLAP applications.

• Snowflake schema is better for OLTP systems with complex relationships.

repartition increases partitions while coalesce decreases partitions in Spark

• repartition shuffles data and can be used for increasing partitions for parallelism

• coalesce reduces partitions without shuffling data, useful for reducing overhead

• repartition is more expensive than coalesce as it involves data movement

• example: df.repartition(10) vs df.coalesce(5)

Display in Databricks is used to visualize data in a tabular format or as charts/graphs.

• Display function is used to show data in a tabular format in Databricks notebooks.

• It can also be used to create visualizations like charts and graphs.

• Display can be customized with different options like title, labels, and chart types.

Use Python's 'with' statement to ensure proper resource management and exception handling.

• Use 'with' statement to automatically close files after use

• Helps in managing resources like database connections

• Ensures proper cleanup even in case of exceptions