Smart Factory SQL Dataset – PostgreSQL Database for Industrial Data Analytics

SMART FACTORY SQL SERIES

Smart Factory SQL Dataset

A PostgreSQL training database for industrial data analytics, SQL practice and Smart Factory learning

Learn SQL with a realistic industrial data model covering machines, production, downtime, energy consumption, maintenance and quality inspections.

What is this dataset?

The Smart Factory SQL Dataset is a synthetic but deliberately coherent industrial training database designed for learning and practicing SQL, PostgreSQL and Smart Factory analytics.

Instead of working with generic examples such as customers, invoices or employees, you work with a manufacturing environment containing machines, production lines, production orders, production events, downtime events, energy measurements, maintenance operations and quality inspections.

The database is implemented using PostgreSQL and organized around the smart_factory schema.

Training philosophy
The objective is simple: practice SQL on data that looks and behaves like industrial data rather than learning SQL only with abstract academic examples.

Dataset at a glance

12

Machines

3

Production Lines

5

Products

17,167

Production Events

2,172

Energy Measurements

1,143

Quality Inspections

82

Downtime Events

8

Maintenance Events

Database structure

The database is organized around several interconnected industrial entities. Foreign-key relationships connect machines, production lines, production orders, production events, downtime, energy consumption, maintenance and quality data.

Table	Industrial Data	Typical Analysis
machines	Machine identification, type, manufacturer, production line, installation date, rated power and status.	Machine analysis, capacity and status.
production_lines	Production line codes, names, descriptions and status.	Production-line comparisons.
production_orders	Planned and actual quantities, rejected quantities, planned and actual timestamps and order status.	Production performance and fulfillment analysis.
production_events	Production timestamps, event types, quantities produced, rejected quantities and cycle times.	Throughput, cycle-time and production-event analysis.
downtime_events	Start/end times, downtime categories, reason codes, descriptions and planned/unplanned status.	Downtime and availability analysis.
energy_consumption	Timestamped energy consumption and power measurements.	Energy and power analysis.
maintenance_events	Maintenance type, timestamps, technician, description, parts cost and labor cost.	Maintenance activity and cost analysis.
quality_inspections	Inspected, passed and failed quantities, defect codes, defect categories and inspectors.	Quality and defect-rate analysis.

What can you practice with this database?

The dataset provides a foundation for progressively more advanced SQL exercises.

SQL Fundamentals

SELECT, WHERE, ORDER BY, GROUP BY, HAVING, filtering, aggregation and calculated columns.

JOINs

Connect machines, production lines, production orders, products and industrial events.

Time-Series Analysis

Analyze production, downtime, energy and quality measurements across industrial timestamps.

Window Functions

Rank machines, compare periods, calculate moving averages and identify trends over time.

Industrial KPIs

Explore production fulfillment, reject rates, downtime and quality indicators.

PostgreSQL Analytics

Use PostgreSQL-specific functions, views, aggregations and analytical SQL techniques.

Built-in analytical views

The database also includes analytical views designed to make industrial analysis easier and provide examples of how raw operational data can be transformed into useful information.

• v_production_order_summary — production-order information combined with production lines and products, including fulfillment and reject rates.
• v_quality_by_line — quality quantities and defect rates aggregated by production line.
• v_daily_machine_energy — daily machine energy consumption and average power.
• v_machine_downtime — downtime-event counts, total downtime and average downtime per machine.

EXAMPLE SQL CHALLENGE

Which machines have accumulated the most downtime?

SELECT
    machine_id,
    SUM(EXTRACT(EPOCH FROM (end_time - start_time)) / 60)
        AS downtime_minutes
FROM smart_factory.downtime_events
GROUP BY machine_id
ORDER BY downtime_minutes DESC
LIMIT 5;

This type of query can be extended into more advanced analyses, including rankings, trends, comparisons between periods and machine-level performance indicators.

Who is this dataset for?

• Students learning SQL, PostgreSQL or industrial data analytics.
• Data analysts looking for manufacturing-oriented SQL practice.
• Industrial engineers interested in production and performance analytics.
• Developers building Smart Factory, MES, IoT or industrial dashboards.
• Teachers and trainers looking for a coherent PostgreSQL dataset for practical exercises.
• SQL learners who want to move beyond generic educational datasets.

Educational purpose

This database is intended for educational and training purposes. The data is synthetic and is designed to provide a coherent manufacturing scenario for practicing SQL, PostgreSQL and industrial analytics.

READY TO PRACTICE?

Get the complete Smart Factory SQL Dataset

Download the PostgreSQL database and start exploring industrial data through real SQL challenges and practical analytics exercises.

GET THE DATASET ON GUMROAD →

Educational dataset • PostgreSQL • SQL • Smart Factory Analytics

Stop practicing SQL with toy examples.

Work with an industrial data model and start solving Smart Factory problems.

→ Explore the dataset on Gumroad