Data Modelling with erwin and Databricks

One project from business brief to running pipeline: conceptual, logical and physical models, star schemas, building the model in erwin, and running its DDL with PySpark on Databricks.

1. What Data Modelling Is

A data model is an agreed plan for how data is organised: which things the business tracks, what is recorded about each one, how they relate, and which rules the data must follow. It does for a data platform what a blueprint does for a building: everyone can argue over the plan while changes are still cheap.

This guide follows one project from start to finish, so each part leads to the next: an online retailer asks for a sales mart, we model it at three levels, draw it in erwin, generate a DDL script, run that script on Databricks, and load the tables with PySpark.

The brief for our project

The sales team asks: "Show revenue per day, per product category and per customer city. If a customer moves city, old orders must still count under the old city." That last sentence will shape the design, so keep it in mind.

2. The Three Levels of a Model

Modelling moves from business language to technical detail in three steps. Each level answers a different question for a different audience, and each is built from the one before it.

Level 1: conceptual

We talk to the sales team and write down the nouns they use. A customer places orders, an order contains products. There are no data types or keys yet.

CUSTOMER ── places ──> ORDER ── contains ──> PRODUCT

Level 2: logical

Now we ask what is recorded about each thing. "Contains" turns out to be many-to-many, since an order holds many products and a product appears in many orders. A many-to-many relationship needs a table in the middle, so ORDER LINE appears. It is also where quantity and price belong, because they describe one product on one order.

CUSTOMER 1 ───< ORDER 1 ───< ORDER LINE >─── 1 PRODUCT

CUSTOMER        ORDER            ORDER LINE      PRODUCT
----------      ------------     -----------     ------------
Customer Id PK  Order Id PK      Order Id PK,FK  Product Id PK
Name            Customer Id FK   Line No PK      Name
City            Order Time       Product Id FK   Category
Segment                          Quantity        List Price
                                 Unit Price

Level 3: physical

Finally we fit the design to the platform. On Databricks that means Delta tables, types such as DECIMAL(18,2), lowercase names, and clustering on the columns queries filter by. It is also where the brief forces a change: analysts need a star schema, not this normalised shape, as the next two sections explain.

3. Building Blocks

Those diagrams used a small vocabulary that every modelling tool shares, and erwin uses the same terms later on.

Identifying and non-identifying relationships

This distinction matters in erwin, because it changes where the foreign key goes and how the line is drawn.

Why surrogate keys matter here

Remember the brief: a customer who moves must keep old orders under the old city. Customer C100 will then need two rows, one per city, so customer_id can no longer be unique. A surrogate key gives each version its own identity, and each order points to the exact version that was true at the time.

4. Choosing a Modelling Approach

The logical model above is normalised, which suits systems that write data. Analysts run the opposite workload, reading millions of rows at once, so the shape of a table should depend on who uses it.

On a lakehouse these are often combined, with one approach per medallion layer. This is the layout our project uses:

For normal forms and for star vs snowflake schemas in depth, see the Data Warehouse guide.

5. Designing the Star Schema

Since our gold layer is dimensional, we follow Kimball's four design steps. Each step is decided from the brief, and the order matters because every step depends on the one before it.

1. Pick the business process. The brief is about sales, so the process is order placement.

2. Declare the grain. Grain means what one fact row represents. We choose one row per order line, the lowest level available, since detail can always be summed up but never recovered once it is lost.

3. Identify the dimensions. The brief says per day, per category and per city. Those answer "by what?", so they become dim_date, dim_product and dim_customer.

4. Identify the facts. The numbers being added up are quantity and net_amount, measured at the grain chosen in step 2.

                     dim_date
                        |
                        | order_date
                        |
 dim_customer ---- fact_order_line ---- dim_product
 customer_sk       order_id  (PK)       product_sk
 customer_id       line_number (PK)     product_id
 city              order_date           category
 valid_from        customer_sk (FK)
 valid_to          product_sk  (FK)
 is_current        quantity, net_amount

Handling the moved customer

The brief's last sentence now has a precise design: dim_customer is a Slowly Changing Dimension Type 2. A change closes the old row and adds a new one, and each row has its own surrogate key. dim_product is Type 1, meaning changes overwrite the old value, because nobody asked for product history.

customer_sk  customer_id  city    valid_from  valid_to    is_current
1            C100         Pune    2025-01-01  2026-03-01  false
6            C100         Mumbai  2026-03-01  9999-12-31  true

A February order stores customer_sk = 1 and a March order stores 6, so "revenue by city" is correct for both without any date logic in the report.

The design is finished, so who writes it down?

At this point the model lives in meeting notes and a whiteboard, which cannot be reviewed, versioned or turned into tables. That gap is what a modelling tool such as erwin fills.

6. What erwin Does

erwin Data Modeler is a desktop tool for designing data models visually. It is now part of Quest. You draw entities and relationships once, and erwin keeps the logical and physical versions together and generates the DDL script for your target database. Databricks has been a supported target since release 12.0, with support for forward engineering, reverse engineering and comparing changes.

Alternatives include ER/Studio, SqlDBM, PowerDesigner and dbt for code-first teams. The ideas in this guide carry over; only the screens differ.

Menu names below come from the erwin Data Modeler documentation and can differ slightly between releases.

7. Using erwin: Our Model, Step by Step

Here is the sales mart built in erwin. Each step uses the output of the one before it, ending with a script ready for Databricks.

1. Create the model. Choose a Logical/Physical model and set the target database to Databricks. This single file holds both views, and the target decides which data types and options erwin offers. You can then switch between Logical and Physical mode.

2. Define domains first. Before drawing, create the reusable definitions, so that every attribute using them agrees and a later change happens in one place.

3. Draw entities and attributes in Logical mode. Add CUSTOMER, PRODUCT, ORDER and ORDER LINE, using the business names from section 2. Fill in the definitions now: they feed the data dictionary, and column comments where your generation options include them.

4. Connect them with relationships. Use an identifying relationship (solid line) from ORDER to ORDER LINE, so Order Id migrates into the child's key. Use non-identifying relationships (dashed lines) from CUSTOMER to ORDER and from PRODUCT to ORDER LINE. erwin migrates the foreign keys for you, which is why you never type them by hand.

5. Apply naming standards. The business says "Customer Identifier" but tables need customer_id. A naming standard (NSM) file holds a glossary of words and abbreviations and the rules for logical and physical names, so names convert automatically.

Naming standards only apply while the physical name still inherits from the logical name. Typing over a physical name overrides that link, and later standard changes will skip it.

6. Switch to Physical mode and shape the gold tables. Add the star schema from section 5 as dim_customer, dim_product and fact_order_line, with the surrogate keys and SCD columns (valid_from, valid_to, is_current). Set data types and mark NOT NULL. Many teams keep these in a separate dimensional model; a subject area works for a smaller project like this one.

7. Organise with subject areas. A subject area is a view of part of the model. Create Sales Mart for gold tables and Source for silver. This matters for the next step, because erwin generates schema for the current subject area. If you see "No Schema to Generate", you are probably in a subject area without those objects.

8. Review, then publish. Walk the sales team through the diagram and generate a data dictionary report. Fixing a wrong grain here costs a conversation. Fixing it after go-live costs a reload of every table.

9. Forward engineer the DDL. This is the step that connects the model to the project:

On a real project, stop at Preview and Save. Running Generate from a laptop would change the database with no pull request, no test and no record, which is exactly what the next sections avoid.

10. Handle change with Complete Compare. Weeks later the business adds a loyalty tier. Add the attribute to the model, then run Complete Compare between the model and the database or script. It lists the differences and can generate an alter script containing just the change. We use exactly this in section 11.

11. Share the model through erwin Mart. Once several modellers are involved, a local .erwin file stops working. Mart stores models centrally with versions, and can hold the shared naming standards too.

Starting from an existing database

Projects often start with tables that already exist and were never modelled. Reverse engineering reads a database or a script and builds the model from it. From release 12.5.1 this includes Unity Catalog: you create a compute cluster, generate an access token, and connect using the JDBC string. Check the foreign keys afterwards, because a database can only give erwin the relationships that were declared.

8. From erwin Script to Databricks DDL

The script saved in step 9 is a strong first draft, not the finished file. A data engineer reviews it before it reaches Git, because the model describes the tables while the project needs them to deploy safely and repeatedly.

Exact output depends on your erwin release and options, so check the Preview to see what yours generates. If a Databricks feature is missing, put it in the reviewed script and record it in the model's notes, so the next regeneration does not lose it.

The final DDL, one file per change

Each file is numbered, because order matters: schemas before tables, dimensions before the fact that references them.

-- ddl/V001__schemas.sql
CREATE SCHEMA IF NOT EXISTS {catalog}.silver;
CREATE SCHEMA IF NOT EXISTS {catalog}.gold;

-- ddl/V002__dim_customer.sql
CREATE TABLE IF NOT EXISTS {catalog}.gold.dim_customer (
  customer_sk  BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
               COMMENT 'Surrogate key, -1 = unknown customer',
  customer_id  STRING    NOT NULL COMMENT 'Business key from the CRM',
  full_name    STRING,
  city         STRING,
  segment      STRING    COMMENT 'Retail, SMB or Enterprise',
  valid_from   TIMESTAMP NOT NULL COMMENT 'SCD2: version start',
  valid_to     TIMESTAMP NOT NULL COMMENT 'SCD2: version end',
  is_current   BOOLEAN   NOT NULL COMMENT 'SCD2: latest version',
  CONSTRAINT pk_dim_customer PRIMARY KEY (customer_sk)
)
USING DELTA
COMMENT 'One row per version of a customer (SCD Type 2)';

-- ddl/V003__dim_product.sql
CREATE TABLE IF NOT EXISTS {catalog}.gold.dim_product (
  product_sk    BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
                COMMENT 'Surrogate key, -1 = unknown product',
  product_id    STRING NOT NULL COMMENT 'Business key',
  product_name  STRING,
  category      STRING,
  CONSTRAINT pk_dim_product PRIMARY KEY (product_sk)
)
USING DELTA
COMMENT 'One row per product (SCD Type 1)';

-- ddl/V004__fact_order_line.sql
CREATE TABLE IF NOT EXISTS {catalog}.gold.fact_order_line (
  order_id     STRING        NOT NULL COMMENT 'Degenerate dimension',
  line_number  INT           NOT NULL,
  order_date   DATE          NOT NULL,
  customer_sk  BIGINT        NOT NULL,
  product_sk   BIGINT        NOT NULL,
  quantity     INT           NOT NULL,
  unit_price   DECIMAL(18,2) NOT NULL,
  net_amount   DECIMAL(18,2) NOT NULL COMMENT 'quantity x unit_price',
  CONSTRAINT pk_fact_order_line PRIMARY KEY (order_id, line_number),
  CONSTRAINT fk_fact_customer FOREIGN KEY (customer_sk)
    REFERENCES {catalog}.gold.dim_customer (customer_sk),
  CONSTRAINT fk_fact_product FOREIGN KEY (product_sk)
    REFERENCES {catalog}.gold.dim_product (product_sk)
)
USING DELTA
CLUSTER BY (order_date)
COMMENT 'Grain: one row per order line';

ALTER TABLE {catalog}.gold.fact_order_line
  ADD CONSTRAINT chk_quantity CHECK (quantity > 0);
ALTER TABLE {catalog}.gold.fact_order_line
  ADD CONSTRAINT chk_net_amount CHECK (net_amount >= 0);

Why the keys are defined this way

• BIGINT GENERATED BY DEFAULT AS IDENTITY generates surrogate keys for us. The values are unique but can have gaps. We use BY DEFAULT, not ALWAYS, because ALWAYS would block the -1 row below.
• CLUSTER BY (order_date) is liquid clustering, because most queries filter by date. It replaces partitioning for this table; a table cannot use both.
• order_id stays in the fact as a degenerate dimension: it has no attributes of its own, so it needs no dimension table.

Seeding the unknown members

The fact's foreign keys are NOT NULL, yet an order can arrive before its customer does. We need a real row to point to, so each dimension gets a -1 "Unknown" row up front. The fact load in section 10 uses it.

-- ddl/V005__seed_unknown_members.sql
INSERT INTO {catalog}.gold.dim_customer
  (customer_sk, customer_id, full_name, city, segment,
   valid_from, valid_to, is_current)
VALUES (-1, 'UNKNOWN', 'Unknown', 'Unknown', 'Unknown',
        TIMESTAMP'1970-01-01', TIMESTAMP'9999-12-31', true);
INSERT INTO {catalog}.gold.dim_product
  (product_sk, product_id, product_name, category)
VALUES (-1, 'UNKNOWN', 'Unknown', 'Unknown');

Use a valid_from of 1970 rather than 1900. In our local test on Spark 3.5, inserting TIMESTAMP'1900-01-01' into Delta failed with INCONSISTENT_BEHAVIOR_CROSS_VERSION. Spark guards timestamps before 1900 UTC, and in a time zone ahead of UTC that local value falls just before the cut-off.

9. What Databricks Actually Enforces

The DDL declares many rules, but they do not all behave the same way. This is the most important difference from a traditional database, and it decides what our pipeline has to check for itself.

So why declare primary and foreign keys at all? They document the model in Unity Catalog, they show relationships in Catalog Explorer and BI tools, and with the RELY option the optimizer may use them. RELY is a promise from you, though: if the data breaks it, queries can return wrong results.

Because nothing stops duplicates, the pipeline must guarantee uniqueness itself. That is why the fact load in the next section uses MERGE on the primary key, not a plain append.

Tested behaviour

Primary keys, foreign keys, identity columns and tags need Databricks: PRIMARY KEY and FOREIGN KEY need Unity Catalog. They could not be run on local open-source Delta, so that behaviour comes from the Databricks documentation. In the local tests, surrogate keys were assigned by hand in place of IDENTITY.

10. Running the DDL and Loading with PySpark

Our DDL files are in Git and the rules are clear. Two jobs remain: apply the files to Databricks, then load data into the tables they created. The key idea is that DDL creates the table once, and PySpark only ever loads it.

The project layout

sales_mart/
  databricks.yml                  bundle definition
  ddl/
    V001__schemas.sql
    V002__dim_customer.sql        from the erwin script, reviewed
    V003__dim_product.sql
    V004__fact_order_line.sql
    V005__seed_unknown_members.sql
  src/
    apply_ddl.py                  runs the DDL files
    contract.py                   checks a DataFrame against a table
    load_dim_customer.py
    load_fact_order_line.py

Step 1: apply the DDL

A small runner applies each file once, in order, and records it in a ledger table. On the next run it skips whatever is already recorded, so the job is safe to run on every deployment. This is the same idea as Flyway or Liquibase.

# src/apply_ddl.py
import argparse, re
from pathlib import Path
from pyspark.sql import SparkSession

def split_statements(text):
    text = re.sub(r"--.*$", "", text, flags=re.M)  # drop comments
    parts = re.split(r";\s*$", text, flags=re.M)  # ; at end of a line
    return [s.strip() for s in parts if s.strip()]

def run_migrations(spark, folder, catalog):
    spark.sql(f"CREATE SCHEMA IF NOT EXISTS {catalog}.meta")
    ledger = f"{catalog}.meta.schema_migrations"
    spark.sql(f"""CREATE TABLE IF NOT EXISTS {ledger}
                  (version STRING NOT NULL, applied_at TIMESTAMP NOT NULL)
                  USING DELTA""")
    rows = spark.table(ledger).select("version").collect()
    applied = {r.version for r in rows}

    for path in sorted(Path(folder).glob("V*.sql")):
        version = path.stem.split("__")[0]  # V002__dim_customer -> V002
        if version in applied:
            print(f"skip {version}")
            continue
        sql = path.read_text().replace("{catalog}", catalog)
        for statement in split_statements(sql):
            spark.sql(statement)
        spark.sql(f"INSERT INTO {ledger} "
                  f"VALUES ('{version}', current_timestamp())")
        print(f"applied {version}")

if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument("--catalog", required=True)
    args = parser.parse_args()
    spark = SparkSession.builder.getOrCreate()
    run_migrations(spark, Path(__file__).parent.parent / "ddl", args.catalog)

The splitter cuts at a semicolon at the end of a line, so keep semicolons inside strings away from line ends. A failed file is not recorded, so it runs again from its first statement. That is why section 11 keeps each ALTER in its own file.

Step 2: check the DataFrame against the model

Delta already rejects some bad writes, but this check fails earlier with a clear message that lists every problem at once. It treats the table as a contract: the model owns the schema, and the code must match it.

# src/contract.py
def assert_contract(df, table):
    """Fail before writing if df does not match the table."""
    target = {f.name: f.dataType for f in df.sparkSession.table(table).schema}
    source = {f.name: f.dataType for f in df.schema}
    shared = target.keys() & source.keys()
    problems = []
    problems += [f"missing: {c}" for c in target.keys() - source.keys()]
    problems += [f"not in model: {c}" for c in source.keys() - target.keys()]
    problems += [f"type of {c}: {source[c]} should be {target[c]}"
                 for c in shared if source[c] != target[c]]
    if problems:
        raise ValueError(f"{table} contract failed: {sorted(problems)}")

Step 3: load the SCD2 dimension

Dimensions load first, because the fact needs their surrogate keys. The two steps below turn the Pune-to-Mumbai move into two rows. Note <=>, the null-safe comparison: with a plain <>, a city changing from NULL to Kochi would be missed.

# src/load_dim_customer.py -- silver.customer has one row per changed customer
dim = f"{catalog}.gold.dim_customer"
stg = f"{catalog}.silver.customer"

# Step 1: close the current row of customers whose attributes changed
spark.sql(f"""
  MERGE INTO {dim} d
  USING {stg} s
  ON d.customer_id = s.customer_id AND d.is_current = true
  WHEN MATCHED AND NOT (d.city <=> s.city
                    AND d.segment <=> s.segment
                    AND d.full_name <=> s.full_name) THEN
    UPDATE SET d.valid_to = s.changed_at, d.is_current = false
""")

# Step 2: insert a new version for anyone who now has no current row
spark.sql(f"""
  INSERT INTO {dim}
    (customer_id, full_name, city, segment, valid_from, valid_to, is_current)
  SELECT s.customer_id, s.full_name, s.city, s.segment,
         s.changed_at, TIMESTAMP'9999-12-31', true
  FROM {stg} s
  LEFT ANTI JOIN {dim} d
    ON d.customer_id = s.customer_id AND d.is_current = true
""")

Tested with a move, a new customer and a NULL-to-value change: 6 rows after the first run and 6 after the second, so a rerun adds nothing. The source must hold one row per customer, or the changes will conflict.

Step 4: load the fact

The fact load uses everything designed so far: the point-in-time join uses the SCD2 dates, the -1 row catches missing customers, the contract protects the schema, and MERGE on the primary key provides the uniqueness Databricks does not enforce.

# src/load_fact_order_line.py
from delta.tables import DeltaTable
from pyspark.sql import functions as F
from contract import assert_contract

target = f"{catalog}.gold.fact_order_line"
lines = spark.table(f"{catalog}.silver.order_line")
customers = spark.table(f"{catalog}.gold.dim_customer")
products = spark.table(f"{catalog}.gold.dim_product")

unknown = F.lit(-1).cast("bigint")

fact = (lines.alias("o")
  # point-in-time join: the customer version valid when the order was placed
  .join(customers.alias("c"),
        (F.col("o.customer_id") == F.col("c.customer_id"))
        & (F.col("o.order_ts") >= F.col("c.valid_from"))
        & (F.col("o.order_ts") < F.col("c.valid_to")), "left")
  .join(products.alias("p"),
        F.col("o.product_id") == F.col("p.product_id"), "left")
  .select(
     "o.order_id", "o.line_number",
     F.to_date("o.order_ts").alias("order_date"),
     F.coalesce("c.customer_sk", unknown).alias("customer_sk"),
     F.coalesce("p.product_sk", unknown).alias("product_sk"),
     "o.quantity", "o.unit_price",
     (F.col("o.quantity") * F.col("o.unit_price"))
       .cast("decimal(18,2)").alias("net_amount")))

assert_contract(fact, target)  # stop here if the code drifted from the model

(DeltaTable.forName(spark, target).alias("t")
   .merge(fact.alias("s"),
          "t.order_id = s.order_id AND t.line_number = s.line_number")
   .whenMatchedUpdateAll()
   .whenNotMatchedInsertAll()
   .execute())

order_id  order_date  city     category  net_amount
O1        2026-02-10  Pune     Home          200.00   C100, before the move
O2        2026-03-05  Mumbai   Home           50.00   C100, after the move
O3        2026-03-05  Unknown  Unknown        70.00   C999, not known yet

That is the brief delivered: C100's old order still counts under Pune and the new one under Mumbai. The table held 3 rows after two runs, so a retry creates no duplicates.

Which write method keeps the model safe?

Because DDL owns the table, the way you write matters. These were all tested against a Delta table:

The rule that follows: in a modelled table, never let overwriteSchema or mergeSchema change the structure from a job. Structure changes go through a migration file.

The same idea on the read side

A schema can protect the start of the pipeline as well. A DDL-style string fixes the types when reading raw files, so a change upstream stops the job instead of creating wrong data. Tested: a date column containing "not-a-date" stopped the read with FAILFAST.

raw_schema = ("customer_id STRING, full_name STRING, "
              "city STRING, signup_date DATE")

df = (spark.read
        .schema(raw_schema)  # no inferSchema: the model decides the types
        .option("header", "true")
        .option("mode", "FAILFAST")      # a bad value stops the job
        .csv(landing_path))

11. Changing the Model Safely

A model is never finished. The business now wants a loyalty tier on each customer, and this is how the change travels through everything built so far.

1. Change erwin first. Add Loyalty Tier to CUSTOMER, so the model stays the source of truth.

2. Run Complete Compare. Compare the model with the database or the last script. It finds the single new column and generates an alter script.

3. Save it as a new migration. Never edit a file that has already been applied. The ledger has recorded it, so the edit would never run.

-- ddl/V006__add_loyalty_tier.sql
ALTER TABLE {catalog}.gold.dim_customer
  ADD COLUMN loyalty_tier STRING COMMENT 'Bronze, Silver or Gold'
  AFTER segment;

4. Open a pull request. The reviewer sees exactly one new column, and CI can run it on a dev catalog.

5. Deploy. The runner skips V001 to V005 and applies V006. Then update the load code, because the contract check will now fail until the DataFrame includes loyalty_tier. The check is doing its job: code and model can no longer disagree silently.

Which changes are easy?

ALTER TABLE gold.dim_customer SET TBLPROPERTIES (
  'delta.columnMapping.mode' = 'name',
  'delta.minReaderVersion'   = '2',
  'delta.minWriterVersion'   = '5');

ALTER TABLE gold.dim_customer RENAME COLUMN city TO town;

Renames also break every query, view and dashboard using the old name. Check the lineage in Unity Catalog first, and update erwin in the same pull request.

12. Deploying on Databricks

The last piece runs everything in the right order. The DDL task comes first, so tables always exist and match the model before any load starts. Task dependencies in the job guarantee that order.

# databricks.yml
bundle:
  name: sales_mart

variables:
  catalog:
    description: Target Unity Catalog catalog

targets:
  dev:
    default: true
    variables:
      catalog: dev_sales
  prod:
    variables:
      catalog: prod_sales

resources:
  jobs:
    sales_mart:
      name: sales_mart
      tasks:
        - task_key: apply_ddl
          spark_python_task:
            python_file: src/apply_ddl.py
            parameters: ["--catalog", "${var.catalog}"]
        - task_key: load_dim_customer
          depends_on: [{ task_key: apply_ddl }]
          spark_python_task:
            python_file: src/load_dim_customer.py
            parameters: ["--catalog", "${var.catalog}"]
        - task_key: load_fact_order_line
          depends_on: [{ task_key: load_dim_customer }]
          spark_python_task:
            python_file: src/load_fact_order_line.py
            parameters: ["--catalog", "${var.catalog}"]

Add your compute (a job cluster or serverless environment) to each task. The load_dim_product task follows the same pattern, and the load scripts read --catalog the same way apply_ddl.py does. The bundle, tags and GRANT statements need a Databricks workspace, so run them on dev first; they were not part of the local tests.

databricks bundle validate
databricks bundle deploy -t dev
databricks bundle run sales_mart -t dev
databricks bundle deploy -t prod

After deployment: tags and permissions

Governance belongs in migrations too, so that dev and prod match. Our dimension holds personal data, so we tag it and give analysts read access only.

ALTER TABLE {catalog}.gold.dim_customer
  ALTER COLUMN full_name SET TAGS ('pii' = 'true');

GRANT USE SCHEMA ON SCHEMA {catalog}.gold TO `analysts`;
GRANT SELECT ON TABLE {catalog}.gold.dim_customer TO `analysts`;
GRANT SELECT ON TABLE {catalog}.gold.fact_order_line TO `analysts`;

The whole journey in one picture

Business brief
  └─► Conceptual model: customer, order, product
  └─► Logical model: attributes, keys, ORDER LINE
  └─► Grain and star schema: fact + dimensions, SCD2
  └─► erwin: domains, naming standards, subject areas
  └─► Actions > Schema: Preview, then Save the .sql file
  └─► Engineer review: IF NOT EXISTS, {catalog}, Delta features
  └─► Git pull request
  └─► CI/CD deploys the bundle
  └─► apply_ddl task creates or alters the tables
  └─► PySpark loads dimensions, then the fact
  └─► Sales report

Each step exists because of the one before it: the brief sets the grain, the grain sets the star schema, the moved customer forces SCD2 and surrogate keys, erwin turns the design into DDL, Git and the runner make it repeatable, and PySpark loads tables whose shape it does not control.

13. Common Mistakes

14. Common Interview Questions