AI-Assisted Data Engineering: Planning and Design with GitHub Spec Kit

What happens when a Data Engineer gives an AI coding assistant a high-level business idea and asks it to produce an implementation-ready technical plan?

In this hands-on experiment, I used GitHub Spec Kit with GitHub Copilot CLI to document the early stages of an end-to-end Microsoft Fabric analytics project. The business scenario was CarPro, a fictional motor insurance company whose customer and quotation data was stored in Azure SQL Database.

The objective was not simply to ask Copilot to generate documents. I wanted to evaluate whether a specification-driven AI workflow could transform a high-level requirement into a project constitution, functional specification, clarified requirements, technical design, source-informed data model, and actionable implementation tasks.

The experiment also revealed an equally important lesson: an AI agent can generate a convincing completion message even when the underlying artifact is incomplete. Human review remained essential throughout the process.

Prerequisites

Before practicing, you have to prepare the following tools:

• Visual Code
• Github Copilot & Github Copilot CLI
• Github Copilot Pro license
• Speckit (refer https://github.com/github/spec-kit)

How to practice: I will provide the prompt for each speckit skill, you copy and paste them to the Github Copilot CLI

We will use GPT-5 mini with medium reasoning. And will build demo as ‘Customer + Quotation’

Initialize a new Spec Kit project

Read speckit documentation to know how to initialize a new Spec Kit project. When you are done, the initial folder structure likes the screenshot below, I named the project as ‘PracticeAI’, and my current Copilot Pro Credits is 31% remaining.

speckit-constitution

The first step was to run /speckit-constitution. This command creates or updates .specify/memory/constitution.md, which defines the main principles and rules for the project.

We can check the constitution template as the screenshot below

Copy the prompt below and paste it to Github Copilot CLI:

/speckit-constitution Create governing principles for the CarPro Insurance Analytics project:

1. Follow a specification-driven workflow with traceability from requirements to design, tasks, implementation, and tests.
2. Use Microsoft Fabric medallion architecture with separate Bronze, Silver, and Gold layers.
3. Model Gold as a business-ready star schema with declared grain, facts, dimensions, keys, and relationships.
4. Never store Azure SQL usernames, passwords, connection strings, tokens, or secrets in prompts, source files, Markdown documents, or Git.
5. Reuse the trainer-provided Microsoft Fabric connection to the CarPro Azure SQL Database.
6. Make ingestion and transformations repeatable, idempotent, observable, and safe to rerun.
7. Define data-quality checks and tests for every data layer, the semantic model, DAX measures, and the Power BI report.
8. Require human approval after specification, technical design, Gold model design, and final validation.
9. Keep the two practice tracks independent: Customer with Quotation, or Policy with Payment.
10. Prefer small, reviewable changes. Do not implement the solution until the specification and technical plan are approved.

After the GHCP is done, it created a file named ‘constitution.md’ in the memory folder as the screenshot below

It is necessary to read through the constitution.md file. I saw some points that were unnecessary and out of scope: for example:

• This workshop just builds end-to-end practice solution, not production deployment, this could make the later tasks breakdown incurring unnecessary tasks.
• It doesn’t require CI/CD as well.

so decided to prompt it again:

/speckit-constitution Update the existing constitution only. Preserve its current principles and structure, but make these corrections:

1. Set the ratification date to 2026-09-10 and keep version 1.0.0 because this is the initial ratification.
2. Scope governance to the workshop solution. Replace production deployment requirements with workshop delivery and report publication requirements.
3. State that tests must be executable and documented, while CI execution is recommended but outside the mandatory workshop scope.
4. Allow approval to be recorded by a participant, peer reviewer, workshop facilitator, or designated business representative. Do not require two formal reviewers.
5. State that the workshop database must contain synthetic or appropriately anonymized data and that no real customer PII is permitted.
6. Remove the emergency production-fix requirement.
7. Do not introduce new requirements, technologies, deliverables, or project scope.
8. Update the Sync Impact Report to reflect these changes.

Below is the result

speckit-specify

After establishing the project Constitution, the next step was to run /speckit-specify. The specification describes what we are going to build and why we need to build it. It defines the business problem, project scope, users, expected outcomes, and acceptance criteria without going deeply into how the solution will be technically implemented.

The initial Specify prompt was detailed because GitHub Copilot did not yet have a functional specification for the CarPro scenario. I provided the business context, objectives, users, scope, expected outcomes, and important constraints so that Copilot could generate a more relevant specification and avoid making unnecessary assumptions.

The goal was not to make the prompt as long as possible. The goal was to make it structured and complete enough for the first specification draft. A shorter prompt could still work, but it would probably produce a more generic specification and require additional clarification or correction later.

/speckit-specify Define the functional specification for the Customer and Quotation track of the CarPro Insurance Analytics workshop.

Business context:
CarPro is a motor insurance company. Customer and quotation data is stored in an Azure SQL Database. Data engineers need to deliver a trusted analytics solution that helps business users understand customer activity and quotation performance.

Business objectives:
1. Provide a reliable analytical view of customers and their motor insurance quotations.
2. Allow users to monitor the number of customers and quotations over time.
3. Analyze quotation status and quotation outcomes.
4. Analyze quoted premium or quotation value when those fields are available.
5. Identify customer and quotation trends using only attributes available in the source data.
6. Provide business-ready data for a semantic model and Power BI report.
7. Allow participants to validate data quality and reconcile analytical results with the Azure SQL source.

Users:
- Data engineers who build and validate the analytics solution.
- Business analysts who explore quotation performance.
- Workshop reviewers who assess traceability, data quality, and business correctness.

Required outcomes:
- Customer and quotation data can be ingested from the trainer-provided Azure SQL connection.
- Source records are preserved for audit and reconciliation.
- Invalid, duplicate, or incomplete records are identified and handled according to documented rules.
- Customer and quotation information is transformed into business-ready analytical data.
- Quotation metrics and business definitions are documented.
- A semantic model can consume the approved analytical data.
- A Power BI report can present agreed customer and quotation insights.
- Tests and reconciliation results demonstrate that the delivered data is trustworthy.
- Human approval is required before implementation and final publication.

Scope:
- Include Customer and Quotation only.
- Exclude Policy and Payment from this specification.
- Use synthetic or appropriately anonymized workshop data.
- Reuse the trainer-provided Fabric connection.
- Do not include credentials or secrets in any project artifact.

Specification rules:
- Focus on business requirements, user scenarios, functional requirements, success criteria, assumptions, and acceptance criteria.
- Do not define the detailed technical architecture or implementation yet.
- Do not invent source columns, quotation statuses, business formulas, thresholds, or data-retention requirements.
- Record unknown information as assumptions or clarification questions.
- Make each acceptance criterion measurable and testable.

Along with spec.md, Spec Kit created checklists/requirements.md. This checklist does not contain a second set of requirements. Instead, it evaluates whether the specification is complete, clear, testable, measurable, and free from unresolved clarification markers.

speckit-clarify

When I ran /speckit-clarify, Spec Kit asked three focused questions to resolve missing information and then recorded my answers directly in spec.md file.

Click on each screenshot to see the detailed questions

speckit-plan

The first time we just simply prompt /speckit-plan without additional information.

Although Spec Kit reported that the plan had been prepared successfully, human review showed that the main plan.md file still contained unresolved template placeholders and a generic application structure. The generated data model also did not yet define the required Fabric medallion architecture or Gold star schema. This demonstrated that successful agent execution does not guarantee a production-ready technical design.

Now prompt the second time

/speckit-plan Revise the existing planning artifacts for the CarPro Customer and Quotation Analytics feature.

The previous run created the files but left plan.md with unresolved template placeholders and a generic application project structure. Replace every placeholder with concrete, workshop-appropriate technical decisions.

Use this architecture:
- Source: Azure SQL Database through the existing trainer-provided Fabric connection.
- Ingestion: Microsoft Fabric Data Pipeline with Copy activity.
- Storage: Fabric Lakehouse using Delta tables.
- Bronze: preserve source records and ingestion metadata.
- Silver: Fabric notebooks using PySpark and Spark SQL for validation, standardization, duplicate detection, quarantine handling, and conformance.
- Gold: business star schema.
- Semantic model: Power BI semantic model over approved Gold tables, using Direct Lake when supported.
- Reporting: Power BI report.
- Testing: source reconciliation, Bronze/Silver/Gold data-quality tests, referential-integrity tests, semantic-model and DAX validation, report validation, and UAT.

For the Gold logical model, define:
- FactQuotation with one row per quotation.
- DimCustomer.
- DimDate with role-playing dates where required.
- DimQuotationStatus if supported by discovered source data.
- Surrogate keys, relationships, unknown-member handling, and a workshop-appropriate history strategy.
- Quoted amount must remain optional until the source field is discovered.

Update:
- plan.md with a complete summary, technical context, constitution checks, and a concrete Fabric repository structure.
- research.md with Fabric architecture decisions and unresolved source-schema discovery.
- data-model.md with Bronze, Silver, and Gold models, declared grain, facts, dimensions, keys, and relationships.
- contracts/README.md with dataset contract expectations.
- quickstart.md with artifact-level validation steps and expected results.

Do not connect to Azure SQL or Fabric during planning.
Do not invent physical source table or column names.
Do not create or deploy Fabric artifacts.
Keep Policy and Payment out of scope.

After the second time was done, I suddenly realize that this stage we are able to provide it some knowledge about SQL Data source schema, so created the ‘customer-quotation-source.sql’ as the screenshot below and prompted the third time. Please note to save token, the sql script doesn’t include sample records, just column metadata, PK and FK definitions.

/speckit-plan Update the existing technical planning artifacts using docs/source-schema/customer-quotation-source.sql as the confirmed CarPro source schema.

Do not regenerate generic content. Make the plan implementation-ready for the workshop.

Confirmed MVP source tables:
- customers, primary key customer_id
- quotation, primary key quotation_id
- agents, primary key agent_id
- insurance_providers, primary key provider_code

Confirmed quotation relationships:
- quotation.customer_id references customers.customer_id
- quotation.agent_id references agents.agent_id
- quotation.provider_code references insurance_providers.provider_code

Confirmed fields:
- quotation_date and quotation_expiry_date are the quotation dates
- quotation_status is the status field
- premium_amount is the quoted-premium field
- package_code is the quotation package
- confirmed sample statuses are CONVERTED, ACCEPTED, REJECTED, EXPIRED, and QUOTED

Design Gold with:
- FactQuotation at one row per quotation_id
- DimCustomer
- DimAgent
- DimInsuranceProvider
- DimDate with quotation-date and expiry-date roles
- DimQuotationStatus
- premium_amount and quotation_count as fact values

Keep vehicle and quotation_item as optional extensions and exclude them from MVP implementation. Exclude insurance_policy_db, policy_info, payment, and cancellation completely.

Because the source has no reliable modified timestamp for every table, use full-snapshot Bronze ingestion for the workshop, with ingestion_run_id, ingested_at, source_database, and source_table. Silver must select the latest record per primary key and use a documented canonical row-hash rule for change detection.

Retain orphan quotations in FactQuotation with customer_key = 0 and report them as data-quality exceptions. Remove invoice_date and any unrelated sales terminology.

Update plan.md, research.md, data-model.md, contracts/README.md, and quickstart.md. Replace resolved schema-discovery placeholders with confirmed mappings, but keep unresolved data-profile findings clearly identified.

Do not connect to Azure or Fabric, do not deploy artifacts, and do not include credentials or sample PII.

I see that the result was still unreasonable:

• Repository structure still keeps old artifact “notebooks/ingest-notebook.py” or “contracts/README_UPDATED.md”. It should be “Fabric Data Pipeline Copy activity for ingestion”
• Missing “Testing at every layer”

so continue to improve it by new prompt:

Perform a final consistency correction of the existing planning artifacts only. Do not regenerate them and do not add new architecture.

Update plan.md:
1. Make DimQuotationStatus a required MVP dimension because quotation_status is confirmed.
2. Expand Constitution Check to explicitly cover traceability, idempotency, observability, testing at every layer, specification approval, technical-design approval, Gold-model approval, final-publication approval, secrets exclusion, and track independence.
3. Replace ingest-notebook.py in the repository structure with a Fabric Data Pipeline Copy activity artifact.
4. Remove README_UPDATED.md from the documented repository structure.
5. Replace remaining schema-discovery wording with read-only data profiling.
6. Use ingested_at to select the latest snapshot; ingestion_run_id only identifies the ingestion execution.

Update quickstart.md:
7. Replace quoted_amount with premium_amount.
8. State that premium_amount exists in the confirmed schema but individual values may be null.

Update Customer.dataset.contract.md:
9. Confirm natural_customer_id is a string mapped from VARCHAR(20).
10. Exclude full_name, email, phone_number, and raw dob from Gold and Power BI. Allow non-identifying analytical attributes such as gender, city, district, and derived age_band.

Update Quotation.dataset.contract.md:
11. State that premium_amount exists in the source schema and is nullable; it is not an optional source column.

Do not connect to Azure or Fabric. Do not modify spec.md. Report exactly which files and sections were changed.

This command will create data-model.md, plan.md, research.md, quickstart.md, Quotation.dataset.contract.md, Customer.dataset.contract.md

speckit-tasks

The /speckit-tasks command reads the approved specification and technical plan, then creates tasks.md file, which breaks the solution into actionable implementation tasks with dependencies, execution order, parallel opportunities, and links to the relevant user stories.

There were some problems with the first results: duplicate tasks, or use pbix file…as 2 screenshots below

I then prompted it the second time:

Revise only the existing specs/001-customer-quotation/tasks.md. Do not regenerate the specification or technical plan.

Correct the task breakdown as follows:

1. Move all read-only profiling tasks into the Foundational phase before Bronze, Silver, and Gold implementation:
   - row counts
   - premium_amount profile
   - quotation_status catalog
   - date ranges and anomalies
   - duplicate analysis
   - customer, agent, and provider orphan analysis

2. Remove tasks that only create empty placeholder files.

3. Define one Fabric Data Pipeline for the four confirmed source tables:
   customers, quotation, agents, and insurance_providers.
   Include four Copy activities using the trainer-provided connection.
   Do not require Copilot to generate unverified pipeline JSON from scratch.
   Create or configure the pipeline in Fabric, then save/export its validated definition for Git review.

4. Keep the hybrid architecture:
   - Data Pipeline for Azure SQL to Bronze ingestion
   - Fabric Notebook for Silver
   - Fabric Notebook for Gold
   - validation scripts/notebook
   - optional Notebook activities in the pipeline for orchestration after notebooks are validated

5. Add exact implementation tasks for:
   - Silver data-quality rules and quarantine
   - Bronze, Silver, and Gold reconciliation
   - idempotency and observability
   - PII exclusion validation
   - Gold star-schema approval

6. Change the existing Customer and Quotation contract task from create to finalize and validate using profiling results.

7. Add the missing end-to-end tasks:
   - Power BI semantic model over Gold
   - relationships and date roles
   - DAX measures
   - semantic-model validation
   - Power BI report pages and visuals
   - report testing and UAT
   - final publication approval

8. Correct dependencies:
   profiling before transformations;
   Bronze before Silver;
   Silver before Gold;
   Gold before semantic model;
   semantic model before report;
   report before UAT.
   Mark [P] only when tasks modify different files and have no dependency.

9. Give every implementation task one exact artifact or file path. Remove ambiguous paths such as "notebooks or scripts".

10. Preserve requirement traceability and user-story labels. Report the revised total task count and critical execution path.

Within the scope of this blog and demo, I stopped at the /speckit-tasks stage and did not proceed to implementation, as the current agentic tooling does not yet provide fully reliable support for automating an end-to-end Microsoft Fabric solution and requires further hands-on evaluation.

AI Credit Usage

For each prompt, I used the /usage prompt to evaluate the AI Credit Usage (AIC)

Complete the demo, my AI Credits changed from 31% to 35% used

Conclusion:

No matter how capable AI becomes, human review remains essential. AI can accelerate documentation, planning, and development, but people are still responsible for validating business meaning, technical accuracy, security, and the final quality of the solution. As AI tools continue to evolve, we must keep learning, experimenting, and improving how we work with them so that we can use AI effectively, responsibly, and with confidence.

Refer more:

AI model comparison: https://docs.github.com/en/copilot/reference/ai-models/model-comparison