In this post we are going to cover deployment, Git and refresh in Power BI: the PBIP project format, deployment pipelines, Fabric Git integration, scheduled refresh, incremental refresh and the XMLA endpoint. These are 7 real Power BI interview questions, and the last one is a scenario a manager asks: someone published straight to production and broke the CFO's report.
Building a report is half the job. The other half is getting changes safely from your laptop to production, and keeping the data fresh once it is there. Interviewers ask about it because this is where a junior developer and a senior developer sound very different. A senior developer talks about source control, promotion between stages and refresh limits without being asked.
The incremental refresh example below runs on a small sample data set, so you can rebuild it and check the numbers yourself.
Part 9 of the Real Time Power BI Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: What are PBIX, PBIP, PBIR and TMDL, and why do they matter for source control?
- Question 2: How do deployment pipelines work, and what do they deploy?
- Question 3: How does Fabric Git integration work, and how is it different from deployment pipelines?
- Question 4: What are the limits of scheduled refresh, and how do you find out when a refresh fails?
- Question 5: How does incremental refresh work?
- Question 6: What is the XMLA endpoint, and how do you refresh only some tables or partitions?
- Question 7: Scenario - someone published straight to production and broke the CFO's report. How do you make sure it never happens again?
- The full SQL script
The short answer
Save work as a PBIP project (PBIR report + TMDL model) so it lives in Git. Promote with a deployment pipeline and rules. Refresh: 8 a day on Pro, 48 on PPU or capacity, incremental refresh for big tables, XMLA or the API for control.
Keep that summary in mind. Every question below is one piece of it.
Let's create some sample data
We use a tiny shop: 5 products, 4 customers in two regions, a date table for 2025 and 2026, and 12 sales in January to March of each year. The script below is written for PostgreSQL (the date table uses generate_series), and it works on any database with small changes. Load the four tables into Power BI Desktop and relate fact_sales to each dimension on its key.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);Here are the 12 sales: 6 in 2025 and 6 in 2026. Keep that split in mind for incremental refresh in Question 5.
| sale_id | order_date | product_key | customer_key | qty | unit_price |
|---|---|---|---|---|---|
| 1 | 2025-01-10 | 1 | 1 | 2 | 40.00 |
| 2 | 2025-01-22 | 3 | 2 | 1 | 250.00 |
| 3 | 2025-02-05 | 2 | 3 | 5 | 10.00 |
| 4 | 2025-02-18 | 5 | 4 | 1 | 60.00 |
| 5 | 2025-03-03 | 4 | 1 | 2 | 50.00 |
| 6 | 2025-03-27 | 3 | 3 | 1 | 250.00 |
| 7 | 2026-01-08 | 1 | 2 | 3 | 40.00 |
| 8 | 2026-01-25 | 3 | 1 | 1 | 250.00 |
| 9 | 2026-02-11 | 2 | 4 | 4 | 10.00 |
| 10 | 2026-02-20 | 4 | 3 | 2 | 50.00 |
| 11 | 2026-03-09 | 5 | 2 | 2 | 60.00 |
| 12 | 2026-03-30 | 3 | 4 | 2 | 250.00 |
Question 1: What are PBIX, PBIP, PBIR and TMDL, and why do they matter for source control?
A PBIX is one binary file. Git can store it, but it can't show you what changed, and two people can't merge their work. A PBIP, a Power BI project, saves the same content as a folder of text files, and that is what makes real source control possible.
| Format | What it is |
|---|---|
| PBIX | one binary file |
| PBIP | project folder of text files |
| PBIR | report: JSON per page and visual |
| TMDL | model: readable text files |
Here is what a project folder looks like: a report folder, a semantic model folder, and a small PBIP file you open in Desktop.
Sales.pbip
Sales.Report/
definition/
pages/ (PBIR, JSON)
Sales.SemanticModel/
definition/
model.tmdl
tables/Sales.tmdl
.gitignoreThe report uses PBIR, with one JSON file per page and per visual. PBIR is now the default report format. The model uses TMDL, a readable text format. Here is a measure in TMDL:
table Sales
measure 'Total Sales' =
SUMX ( Sales, Sales[Qty] * Sales[Unit Price] )
formatString: #,##0
displayFolder: KPIsThat is why it matters. Every change becomes a readable diff, so you can review it in a pull request, merge branches and automate deployments. Projects became generally available in September 2026.
PBIP files don't support sensitivity labels, and you can't save a project straight to OneDrive or SharePoint.
Question 2: How do deployment pipelines work, and what do they deploy?
A deployment pipeline connects workspaces as stages. The default is Development, Test and Production, and a pipeline can have from 2 to 10 stages. You build in the first stage and deploy forward, one stage at a time.
Deployment rules handle the differences between environments. In the target stage you set a data source rule or a parameter rule, so production points at the production database. The item owner sets the rules, and parameter rules need a text parameter.
The follow-up question is always: what gets deployed, and what doesn't?
| Deployed | Not deployed (do it yourself) |
|---|---|
| Metadata (tables, measures) | Data: refresh after deploying |
| Report visuals | Permissions and RLS role members |
| Parameters | Refresh schedule and credentials |
| App: update it manually |
So the classic answer: after a deployment, you refresh the model in the target stage, check the schedule and credentials, and update the app yourself.
Question 3: How does Fabric Git integration work, and how is it different from deployment pipelines?
Git integration connects a Fabric workspace to a branch in a Git repository, in Azure DevOps or GitHub. You commit from the workspace to the branch, and update the workspace from the branch.
- Connect and sync: only a workspace admin can connect it.
- Commit: workspace to branch.
- Update: branch to workspace.
- Branch out: a new branch and a new workspace to work in.
The usual pattern is a workspace per developer or per feature. You branch out, work in that workspace, or in Desktop with a PBIP project, and then raise a pull request to merge into the main branch.
The difference is the purpose of each tool.
| Git integration | Deployment pipeline | |
|---|---|---|
| PURPOSE | source control: history, review | promote between stages |
| MOVES | item definitions as text | content to Test and Prod |
| CONNECTIONS | no rules | data source and parameter rules |
Git gives you history, review and rollback for the definitions of your items. A pipeline promotes content between environments, with rules for the connections. Most teams use both: Git for development, and a pipeline to promote to Test and Production.
Check the supported items list, because some item types still carry a preview label for Git integration.
Question 4: What are the limits of scheduled refresh, and how do you find out when a refresh fails?
The limits depend on where the model lives.
| Tier | Refreshes a day | Timeout |
|---|---|---|
| Pro (shared) | 8 | 2 hours |
| PPU, Premium, Fabric | 48 | 5 hours |
A few details that show experience:
- Refreshes through the REST API count toward the Pro quota. On-demand refreshes from the UI don't.
- Scheduled refresh pauses after 2 months of inactivity, and is disabled after repeated failures.
- A model built on a OneDrive or SharePoint file syncs the file about every hour, but that does not refresh the sources behind it.
For failures, the model owner gets an email by default, and you can add contacts, like a support group. Only scheduled refreshes send these emails. Then open the refresh history. The usual causes are expired credentials, an offline gateway, or a timeout or quota hit.
Question 5: How does incremental refresh work?
Instead of reloading the whole fact table every time, the service splits it into partitions by date and refreshes only the recent ones. It works on Pro too.
You create two DateTime parameters, named exactly RangeStart and RangeEnd, and filter the date column with them in Power Query. Here is the filter:
let
Source = Sql.Database ( "sqlprod", "TBShop" ),
Sales = Source{[Schema = "dbo", Item = "Sales"]}[Data],
Filtered = Table.SelectRows ( Sales, each
[OrderDate] >= RangeStart
and [OrderDate] < RangeEnd )
in
FilteredNotice greater-or-equal on one side and less-than on the other, so no row lands in two partitions. The filter must also fold to the source.
Then you set the policy: an archive period to keep, and a refresh period to reload. For example, keep two years and refresh only the current year. Here is what that means on our sample.
| partition | row_count | sales | policy |
|---|---|---|---|
| 2025 | 6 | 790.00 | archive |
| 2026 | 6 | 1130.00 | refresh |
Check this result yourself with SQL
Example policy: keep two years, refresh only the current year. The 2025 partition (6 rows, 790) is never reloaded; only 2026 (6 rows, 1130) is.
SELECT d.year AS partition,
COUNT(*) AS row_count,
SUM(f.qty * f.unit_price) AS sales,
CASE WHEN d.year = 2026 THEN 'refresh' ELSE 'archive' END AS policy
FROM fact_sales f
JOIN dim_date d ON d.date = f.order_date
GROUP BY d.year
ORDER BY d.year;The 2025 partition, 6 rows and 790.00 in sales, is kept but never reloaded. Only the 2026 partition, 6 rows and 1130.00, is refreshed.
You can also turn on detect data changes and only refresh complete days. On PPU or a capacity, a hybrid table adds a real-time DirectQuery partition for the newest data.
Republishing from Desktop drops the partitions. In production, deploy metadata only, or use the XMLA endpoint.
Question 6: What is the XMLA endpoint, and how do you refresh only some tables or partitions?
The XMLA endpoint exposes a semantic model the way an Analysis Services database is exposed. It is available on PPU, Premium and Fabric capacity. Tools like SQL Server Management Studio and Tabular Editor connect to it to deploy and refresh models.
Through XMLA you can process one table or one partition instead of the whole model. XMLA refreshes aren't limited to 48 a day, and they bypass the 5-hour timeout.
The enhanced refresh REST API does the same from code, and also needs PPU or a capacity. Here is a request that refreshes only the 2026 partition of Sales and the Date table, with commit mode, parallelism and retry count set.
POST /groups/{ws}/datasets/{id}/refreshes
{
"type": "Full",
"commitMode": "transactional",
"maxParallelism": 2,
"retryCount": 2,
"objects": [
{ "table": "Sales", "partition": "2026" },
{ "table": "Date" }
]
}Then you GET the refresh status and alert on failure. That is how you automate it: your orchestration loads the warehouse first, then calls the API to refresh just the partitions that changed.
Question 7: Scenario - someone published straight to production and broke the CFO's report. How do you make sure it never happens again?
The manager is not asking who to blame. They want a process. Here it is, step by step:
- Lock down production. Nobody publishes to production by hand. Only a small release group has Admin or Member on the production workspace, and business users read through the app.
- Put the work in Git. Developers save PBIP projects and work in their own workspace on a feature branch. Every change goes through a pull request, so a second person reviews the diff before it is merged.
- Promote with a deployment pipeline. Development, Test, Production, with rules that point each stage at the right database. A business owner signs off in Test before anything reaches Production.
- Protect what the CFO depends on. Make it a certified semantic model, and send refresh failure emails to a support group, not just one owner.
- Plan the rollback. If something still goes wrong, revert the commit in Git and redeploy through the pipeline.
Sum it up in one line: controlled access, review, promotion and monitoring. That is the answer a manager wants.
Key points to remember
- PBIP = PBIR report + TMDL model, as text in Git
- Pipelines promote; rules switch connections; data is not copied
- Refresh: 8 a day on Pro (2 h), 48 on PPU or capacity (5 h)
- Incremental refresh: RangeStart / RangeEnd, archive vs refresh
- Prod: restricted roles, pull requests, pipeline, certified model
The full SQL script
If you want to rebuild everything at once, here is the whole script: the sample data and the SQL check behind the incremental refresh result above.
Show the full SQL script (sample data and every check)
-- ========================================================================
-- Power BI Interview Q&A #9 - Deployment, Git and refresh
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA pbi_q09; SET search_path TO pbi_q09;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);
-- Step 1: Incremental refresh: one partition per year (archive vs refresh)
SELECT d.year AS partition,
COUNT(*) AS row_count,
SUM(f.qty * f.unit_price) AS sales,
CASE WHEN d.year = 2026 THEN 'refresh' ELSE 'archive' END AS policy
FROM fact_sales f
JOIN dim_date d ON d.date = f.order_date
GROUP BY d.year
ORDER BY d.year;
Part 10: Real-world Manager Scenarios
The sample data on this page is invented for teaching. The result tables were produced by running the same calculation in SQL on PostgreSQL 16 over that sample data (open "Check this result yourself" under any result), so you can rebuild every number in Power BI Desktop and compare.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.