Interview Questions
Basic Level
1. What is Power BI, and why is it used?
Power BI is a business analytics tool by Microsoft that enables users to visualize
data and share insights. It is used to transform raw data into meaningful
information using dashboards, reports, and datasets.
2. Explain the difference between Power Query and DAX in Power BI.
Power Query: Used for data cleaning, transformation, and loading.
DAX (Data Analysis Expressions): Used for calculations and data analysis
within Power BI models.
3. What is a calculated column, and how does it differ from a measure?
Calculated Column: A new column added to a table, calculated row by row
using a DAX expression.
Measure: A calculation evaluated at the aggregation level, optimized for
better performance.
4. What are the key components of Power BI?
Power BI Desktop
Power BI Service
Power BI Report Server
Power BI Mobile
5. What is the importance of relationships in Power BI models?
Relationships connect tables in a Power BI model, allowing data to be analyzed
together in a single visualization. They enable filtering, lookups, and aggregations
across multiple tables.
Basic Power BI Q&A
1. What is Power BI, and why is it used?
Power BI is a business intelligence tool by Microsoft used to transform raw data
into meaningful insights through interactive dashboards and reports. It enables
data analysis and visualization, making it easier for stakeholders to make
informed decisions.
2. What are the main components of Power BI?
Power BI Desktop: For designing reports and dashboards.
Power BI Service: For sharing and collaborating on reports.
Power BI Report Server: For hosting reports on-premise.
Power BI Mobile: For accessing reports on mobile devices.
3. What is the purpose of relationships in Power BI?
Relationships connect tables in a Power BI model, allowing for data to be
analyzed together. For example, linking sales and product tables enables you to
analyze which products are performing well.
4. What is DAX in Power BI?
DAX (Data Analysis Expressions) is a formula language used in Power BI for
creating custom calculations, such as measures and calculated columns, to
analyze data effectively.
5. What are measures and calculated columns in Power BI?
Measures: Perform calculations dynamically based on user interaction, e.g.,
Total Sales = SUM(Sales[Amount]).
Calculated Columns: Static calculations at the row level, e.g., Profit Margin =
[Profit] / [Revenue].
6. How does Power Query help in data preparation?
Power Query is used for data extraction, cleaning, and transformation. For
instance, handling missing data, merging tables, and formatting columns before
loading into Power BI.
7. What is the difference between Import and DirectQuery modes in
Power BI?
Import Mode: Loads data into Power BI for faster performance; suitable for
smaller datasets.
DirectQuery Mode: Queries the data source directly; ideal for large
datasets or real-time data.
8. How can you ensure data accuracy in your Power BI reports?
Validate data against the source system.
Conduct regular checks for refresh failures.
Apply transformation and cleansing rules in Power Query.
9. What types of visualizations have you used in Power BI?
Based on your resume, you can mention:
Bar charts for sales trends.
Line charts for performance tracking.
Tables and matrices for detailed data insights.
Cards for displaying key metrics.
10. How do you share reports and dashboards with stakeholders?
Reports are shared via the Power BI Service by publishing them to workspaces.
Permissions can be assigned to allow stakeholders access to view or edit the
reports.
Intermediate Level
1. Explain the ETL process you implemented using Informatica IICS.
The ETL process involved extracting data from flat files and SQL Server,
transforming it using regex for standardization, and loading it into Snowflake and
Databricks for further processing. Transformation steps included data cleansing,
deduplication, and applying client-specific business rules.
2. What is Snowflake, and how have you used it in your projects?
Snowflake is a cloud-based data warehousing solution known for its scalability
and performance. In my projects, I used Snowflake to store and process
transformed data for analytics. I optimized queries to handle large datasets
efficiently and integrated it with visualization tools.
3. What are your primary use cases for Python in data analytics?
Automating ETL processes (e.g., scheduling tasks in IICS).
Performing data cleansing using libraries like Pandas.
Creating scripts for advanced analytics and reporting.
Automating email communications with Gmail for client updates.
4. How do you optimize SQL queries for better performance?
Use indexed columns in WHERE clauses.
Avoid SELECT *.
Write subqueries instead of complex joins when appropriate.
Optimize table joins with proper indexing and data structure design.
5. What is Databricks, and how have you used it?
Databricks is a collaborative platform for data engineering, machine learning, and
analytics. I used it to process large datasets after transformation, leveraging its
scalability to handle complex data workflows and integrating it with visualization
tools.
6. How do you ensure data security and integrity in ETL processes?
Implement access controls and encryption for sensitive data.
Validate data at each ETL stage with automated scripts.
Perform regular audits and maintain error logs for debugging.
7. How do you handle version control for ETL scripts?
I use GitHub for version control to maintain, track, and manage changes in ETL
scripts. This ensures collaboration across teams and rollback capabilities if
required.
Advanced Level
8. How did you automate client communication in your Python
automation project?
I developed a Python script to log into IICS automatically, execute tasks using
RunID, and generate output files. The script then used the Gmail API to send
emails with the output attached, ensuring timely communication. This script was
hosted on AWS Lightsail and scheduled to run every five hours.
9. Explain the advantages of using AWS Lightsail for hosting
automation scripts.
AWS Lightsail offers a cost-effective and scalable environment for hosting small
applications and scripts. It supports automation, has a user-friendly interface,
and integrates seamlessly with other AWS services.
10. How do you approach designing scalable data pipelines?
Use cloud-based solutions like Snowflake and Databricks for storage and
processing.
Break down ETL steps into reusable and modular components.
Implement parallel processing for large datasets.
Monitor pipelines using automated tools for error tracking.
11. What is the importance of regex in data transformation, and how
have you applied it?
Regex is essential for pattern matching and data cleaning. I used it in ETL
processes to standardize formats (e.g., phone numbers, dates) and extract
meaningful patterns from unstructured data.
12. What challenges have you faced while migrating data from on-
premise systems to the cloud?
Ensuring data consistency and integrity during migration.
Addressing latency issues for large datasets.
Configuring security protocols for sensitive data in the cloud.
13. How do you ensure compliance with data quality standards in
your projects?
Apply validation rules at the transformation stage.
Conduct audits to identify and rectify data anomalies.
Ensure alignment with guidelines like HEDIS for healthcare data.
14. How does Databricks differ from traditional ETL tools like
Informatica?
Databricks is designed for big data and machine learning workflows, offering
native integration with Spark for distributed processing. Informatica, on the other
hand, focuses on traditional ETL workflows with a GUI-driven approach, making
it suitable for structured data.
15. How do you troubleshoot errors in your ETL pipelines?
Review error logs generated at each stage.
Perform root cause analysis by isolating the error-prone segment.
Use test datasets to replicate and resolve issues.
